I need the full code including(create table, insert) and the output Assume you have a table in your database called World' with the following schema:Table: world| Column Name | Type+-| varchar || varchar || int| int| int| name| continent|area|I populationI gdp|+--name is the primary key column for this table.Each row of this table gives information about the name of a country, thecontinent to which it belongs, its area, the population, and its GDP value.It contains the following records of data:Input:World table:+--| name| continent | areaI population | gdp+--| Afghanistan | Asia| Albania| Algeria| Andorra| Angola| 652230| 28748| 2381741 | 37100000| 468| 1246700 | 20609294| 20343000000| 12960000000| 188681000000 || 3712000000| 100990000000 || 25500100| 2831741| Europe| Africa| Europe| Africa| 78115+-Write an SQL query to report the name, population, and area of the big countries.Return the result table in any order.A country is big if:it has an area of at least three million (i.e., 3000000 km2), or• it has a population of at least twenty-five million (i.e., 25000000).The results should be:Output:+-| nameI population | area+--| Afghanistan | 25500100| Algeria| 652230| 2381741 || 37100000+-- +

Answered: Assume you have a table in your…

A Guide to SQL

9th Edition

ISBN:9781111527273

Author:Philip J. Pratt

Publisher:Philip J. Pratt

Chapter2: Database Design Fundamentals

Section: Chapter Questions

Problem 11RQ

See similar textbooks

Similar questions

We need to create a database for the Circulation Department of a public library. The database will store information about the patrons, books, and circulation. It contains the following: 4 tables for the database: PATRON, BOOK_COPY, BOOK, and CHECKOUT. (Since a book may have multiple copies that may be purchased by the library at different time, it is better to have a BOOK_COPY table to avoid unnecessary data redundancy.) The data in the tables are as follows:    PATRON table records a patron’s ID, name, address, phone number, and email address. BOOK table contains information such as author, title, publication date, subject, language, and a unique identifier (It can be the ISBN of the book) for each book. BOOK_COPY table records a unique identifier for each copy of a book, the date of purchase, and the identifier of the book from the BOOK table. CHECKOUT table records the date of check-out, patron’s ID, the…
We need to create a database for the Circulation Department of a public library. The database will store information about the patrons, books, and circulation. It contains the following: 4 tables for the database: PATRON, BOOK_COPY, BOOK, and CHECKOUT. (Since a book may have multiple copies that may be purchased by the library at different time, it is better to have a BOOK_COPY table to avoid unnecessary data redundancy.) The data in the tables are as follows:    PATRON table records a patron’s ID, name, address, phone number, and email address. BOOK table contains information such as author, title, publication date, subject, language, and a unique identifier (It can be the ISBN of the book) for each book. BOOK_COPY table records a unique identifier for each copy of a book, the date of purchase, and the identifier of the book from the BOOK table. CHECKOUT table records the date of check-out, patron’s ID, the…
We need to create a database for the Circulation Department of a public library. The database will store information about the patrons, books, and circulation. It contains the following: 4 tables for the database: PATRON, BOOK_COPY, BOOK, and CHECKOUT. (Since a book may have multiple copies that may be purchased by the library at different time, it is better to have a BOOK_COPY table to avoid unnecessary data redundancy.) The data in the tables are as follows:    PATRON table records a patron’s ID, name, address, phone number, and email address. BOOK table contains information such as author, title, publication date, subject, language, and a unique identifier (It can be the ISBN of the book) for each book. BOOK_COPY table records a unique identifier for each copy of a book, the date of purchase, and the identifier of the book from the BOOK table. CHECKOUT table records the date of check-out, patron’s ID, the…
In this database we have users that can be two types students or instructors. They have groups and memberships. Any user can post and the post type can be video, photo or text. Here is the table structure: CREATE TABLE USER_ ( USER_ID INT NOT NULL AUTO_INCREMENT, CREATE_DATE DATE NOT NULL, PROF_DESC VARCHAR (100), PROF_PIC VARCHAR(40), LOCATION VARCHAR (40) NOT NULL, PRIMARY KEY (USER_ID) ); CREATE TABLE USER_INFO( USER_ID INT NOT NULL, SU_EMAIL CHAR(18), USER_FNAME VARCHAR(15), USER_LNAME VARCHAR (15), USER_DOB DATE, USER_GENDER CHAR(1), PRIMARY KEY (USER_ID), FOREIGN KEY (USER_ID) REFERENCES USER_(USER_ID) ON UPDATE CASCADE ); CREATE TABLE STUDENT ( SU_ID INT NOT NULL, USER_ID INT NOT NULL, YEAR_ VARCHAR (10), MAJOR VARCHAR (20), LOCATION VARCHAR (40) NOT NULL, PRIMARY KEY (SU_ID), FOREIGN KEY (USER_ID) REFERENCES USER_(USER_ID) ON UPDATE CASCADE ); CREATE TABLE INSTRUCTOR ( SU_ID INT NOT NULL, USER_ID INT NOT NULL, DEPARTMENT VARCHAR (40), INSTR_TYPE VARCHAR (40),…
In this database we have users that can be two types students or instructors. They have groups and memberships. Any user can post and the post type can be video, photo or text. Here is the table structure: CREATE TABLE USER_ ( USER_ID INT NOT NULL AUTO_INCREMENT, CREATE_DATE DATE NOT NULL, PROF_DESC VARCHAR (100), PROF_PIC VARCHAR(40), LOCATION VARCHAR (40) NOT NULL, PRIMARY KEY (USER_ID) ); CREATE TABLE USER_INFO( USER_ID INT NOT NULL, SU_EMAIL CHAR(18), USER_FNAME VARCHAR(15), USER_LNAME VARCHAR (15), USER_DOB DATE, USER_GENDER CHAR(1), PRIMARY KEY (USER_ID), FOREIGN KEY (USER_ID) REFERENCES USER_(USER_ID) ON UPDATE CASCADE ); CREATE TABLE STUDENT ( SU_ID INT NOT NULL, USER_ID INT NOT NULL, YEAR_ VARCHAR (10), MAJOR VARCHAR (20), LOCATION VARCHAR (40) NOT NULL, PRIMARY KEY (SU_ID), FOREIGN KEY (USER_ID) REFERENCES USER_(USER_ID) ON UPDATE CASCADE ); CREATE TABLE INSTRUCTOR ( SU_ID INT NOT NULL, USER_ID INT NOT NULL, DEPARTMENT VARCHAR (40), INSTR_TYPE VARCHAR (40),…
The database has three tables for tracking horse-riding lessons: Horse with columns: ID - primary key RegisteredName Breed Height BirthDate Student with columns: ID - primary key FirstName LastName Street City State Zip Phone EmailAddress LessonSchedule with columns: HorseID - partial primary key, foreign key references Horse(ID) StudentID - foreign key references Student(ID) LessonDateTime - partial primary key Write a SELECT statement to create a lesson schedule for Feb 1, 2020 with the lesson date/time, student's first and last names, and the horse's registered name. Order the results in ascending order by lesson date/time, then by the horse's registered name. Make sure unassigned lesson times (student ID is NULL) appear in the results.
Below is a schema for an HR database:employee(empid, fname, lname, managerid, departmentid, employee_rank)It's an employee table, which has employee id, first name, last name, manager id (which is an employee id), department id, and employee_rank, such as VP, CEO, SVP, etc.Using SQL, answer this question (write a SQL query that answers this question) [tip: use a recursive query]. 10. For employee 42, find the path-of-managers directly to the CEO?
create database GA2gouse GA2gocreate table salesman(salesman_id int primary key, name varchar(30), city varchar(20), commission varchar(30));insert into salesman (salesman_id, name, city, commission) values (5001, 'James Hoog', 'New York', 0.15); insert into salesman (salesman_id, name, city, commission) values (5002, 'Nail Knite', 'Paris', 0.13); insert into salesman (salesman_id, name, city, commission) values (5005, 'Pit Alex', 'London', 0.11); insert into salesman (salesman_id, name, city, commission) values (5006, 'Mc Lyon', 'Paris', 0.14); insert into salesman (salesman_id, name, city, commission) values (5007, 'Paul Adam', 'Rome', 0.13); insert into salesman (salesman_id, name, city, commission) values (5003, 'Lauson Hen', 'San Jose', 0.12); Create table Customer (customer_id int primary key, cust_name varchar(30), city varchar(10), grade varchar(30), salesman_id varchar(30)(select salesman_id from salesman); why does my sql server code not work, at the customer table?
create database GA2gouse GA2gocreate table salesman(salesman_id int primary key, name varchar(30), city varchar(20), commission varchar(30));insert into salesman (salesman_id, name, city, commission) values (5001, 'James Hoog', 'New York', 0.15); insert into salesman (salesman_id, name, city, commission) values (5002, 'Nail Knite', 'Paris', 0.13); insert into salesman (salesman_id, name, city, commission) values (5005, 'Pit Alex', 'London', 0.11); insert into salesman (salesman_id, name, city, commission) values (5006, 'Mc Lyon', 'Paris', 0.14); insert into salesman (salesman_id, name, city, commission) values (5007, 'Paul Adam', 'Rome', 0.13); insert into salesman (salesman_id, name, city, commission) values (5003, 'Lauson Hen', 'San Jose', 0.12); Create table Customer (customer_id int primary key, cust_name varchar(30), city varchar(10), grade varchar(30), salesman_id int,CONSTRAINT FK_salesman_id FOREIGN KEY (salesman_id) REFERENCES salesman(salesman_id));insert into customer…
create database GA2gouse GA2gocreate table salesman(salesman_id int primary key, name varchar(30), city varchar(20), commission varchar(30));insert into salesman (salesman_id, name, city, commission) values (5001, 'James Hoog', 'New York', 0.15); insert into salesman (salesman_id, name, city, commission) values (5002, 'Nail Knite', 'Paris', 0.13); insert into salesman (salesman_id, name, city, commission) values (5005, 'Pit Alex', 'London', 0.11); insert into salesman (salesman_id, name, city, commission) values (5006, 'Mc Lyon', 'Paris', 0.14); insert into salesman (salesman_id, name, city, commission) values (5007, 'Paul Adam', 'Rome', 0.13); insert into salesman (salesman_id, name, city, commission) values (5003, 'Lauson Hen', 'San Jose', 0.12); Create table Customer (customer_id int primary key, cust_name varchar(30), city varchar(10), grade varchar(30), salesman_id varchar(30),CONSTRAINT FK_salesman_id FOREIGN KEY (salesman_id) REFERENCES salesman(salesman_id)); i keep getting…
create database GA2gouse GA2gocreate table salesman(salesman_id int primary key, name varchar(30), city varchar(20), commission varchar(30));insert into salesman (salesman_id, name, city, commission) values (5001, 'James Hoog', 'New York', 0.15); insert into salesman (salesman_id, name, city, commission) values (5002, 'Nail Knite', 'Paris', 0.13); insert into salesman (salesman_id, name, city, commission) values (5005, 'Pit Alex', 'London', 0.11); insert into salesman (salesman_id, name, city, commission) values (5006, 'Mc Lyon', 'Paris', 0.14); insert into salesman (salesman_id, name, city, commission) values (5007, 'Paul Adam', 'Rome', 0.13); insert into salesman (salesman_id, name, city, commission) values (5003, 'Lauson Hen', 'San Jose', 0.12); Create table Customer (customer_id int primary key, cust_name varchar(30), city varchar(10), grade varchar(30), salesman_id int,CONSTRAINT FK_salesman_id FOREIGN KEY (salesman_id) REFERENCES salesman(salesman_id));insert into customer…
create database GA2gouse GA2gocreate table salesman(salesman_id int primary key, name varchar(30), city varchar(20), commission varchar(30));insert into salesman (salesman_id, name, city, commission) values (5001, 'James Hoog', 'New York', 0.15); insert into salesman (salesman_id, name, city, commission) values (5002, 'Nail Knite', 'Paris', 0.13); insert into salesman (salesman_id, name, city, commission) values (5005, 'Pit Alex', 'London', 0.11); insert into salesman (salesman_id, name, city, commission) values (5006, 'Mc Lyon', 'Paris', 0.14); insert into salesman (salesman_id, name, city, commission) values (5007, 'Paul Adam', 'Rome', 0.13); insert into salesman (salesman_id, name, city, commission) values (5003, 'Lauson Hen', 'San Jose', 0.12); Create table Customer (customer_id int primary key, cust_name varchar(30), city varchar(10), grade varchar(30), salesman_id varchar(30),CONSTRAINT FK_salesman_id FOREIGN KEY (salesman) REFERENCES salesman(salesman_id)); i keep getting this…