Homework: Populate Sales Order tables. Write SQL to : 1. insert 5 records in each table:...

Homework: Populate Sales Order tables.

Write SQL to :

1. insert 5 records in each table: Market, Region, ProductFamily, Manager (all Managers must have different % commissions, Commission is an attribute of the Manger).

2. Insert 5 records in Agent using all Managers

3. Insert 15 records in Product using all ProductFamily

4. Insert 15 records in Customer using various Regions and Markets

5. Insert 50 records in SalesOrder using various Customers, Products, Agents

Notes :

ALL the Names ( Description) must be Unique
SalesOrderDate is a DATE format. Dates must be different,
Years are 2010, 2012, 2015,2017, 2018, 10 orders per year.
No TotalAmount column in your SalesOrder table. It will be calculated

Suggestion: use Excel to create your data, then create and run your Insert statements,

Upload your SQL statements in PDF.

Solutions

Expert Solution

Hi,

I am afraid we do not have the option to attach the pdf files so I have added the insert queries what you can do is just insert these SQL queries in your database and then fetch the with select * tablename; and then choose to export them in pdf format.

insert into Market(market_id,market_name) values (101,'Farmers Market');
insert into Market(market_id,market_name) values (102,'Wholesale Market');
insert into Market(market_id,market_name) values (103,'Collectables');
insert into Market(market_id,market_name) values (104,'Real Esate');
insert into Market(market_id,market_name) values (105,'Advertising');

insert into ProductFamily(ProductFamily_id,ProductFamily_name)values (5001,'Pork and beef products');
insert into ProductFamily(ProductFamily_id,ProductFamily_name)values (5002,'Clothes');
insert into ProductFamily(ProductFamily_id,ProductFamily_name)values (5003,'Shaving collectibles');
insert into ProductFamily(ProductFamily_id,ProductFamily_name)values (5004,'Apartments');
insert into ProductFamily(ProductFamily_id,ProductFamily_name)values (5004,'Jewellery');

insert into Region(Region_id,Region_name)values (10001,'Asia');
insert into Region(Region_id,Region_name)values (10002,'Africa');
insert into Region(Region_id,Region_name)values (10003,'Europe');
insert into Region(Region_id,Region_name)values (10004,'South-Central');
insert into Region(Region_id,Region_name)values (10005,'Latin America');

insert into Manager(Manager_id,Manager_name,Commission)values (15001,'Mitchell Geller',0.20);
insert into Manager(Manager_id,Manager_name,Commission)values (15002,'Phil Dunphy',0.15);
insert into Manager(Manager_id,Manager_name,Commission)values (15003,'Chandler Bing',0.10);
insert into Manager(Manager_id,Manager_name,Commission)values (15004,'Cameron Prichett',0.22);
insert into Manager(Manager_id,Manager_name,Commission)values (15005,'Haley Green',0.5);

insert into Agent(Agent_id,Manager_id,Agent_name)values (201,15001,'Luke Smith');
insert into Agent(Agent_id,Manager_id,Agent_name)values (202,15002,'Carol Tribiani');
insert into Agent(Agent_id,Manager_id,Agent_name)values (203,15003,'Luke Smith');
insert into Agent(Agent_id,Manager_id,Agent_name)values (204,15004,'Joe');
insert into Agent(Agent_id,Manager_id,Agent_name)values (205,15005,'Jim White');
3.

insert into Product(Product_id,ProductFamily_id,Product_name)values (601,5001,'Red meat');
insert into Product(Product_id,ProductFamily_id,Product_name)values (602,5001,'Beef');
insert into Product(Product_id,ProductFamily_id,Product_name)values (603,5001,'chicken');
insert into Product(Product_id,ProductFamily_id,Product_name)values (604,5002,'Shirt');
insert into Product(Product_id,ProductFamily_id,Product_name)values (605,5002,'Trouser');
insert into Product(Product_id,ProductFamily_id,Product_name)values (606,5002,'Hat');
insert into Product(Product_id,ProductFamily_id,Product_name)values (607,5003,'Saving Foam');
insert into Product(Product_id,ProductFamily_id,Product_name)values (608,5003,'Razor');
insert into Product(Product_id,ProductFamily_id,Product_name)values (609,5003,'Aftershave Gel');
insert into Product(Product_id,ProductFamily_id,Product_name)values (610,5004,'Sea View Apartment');
insert into Product(Product_id,ProductFamily_id,Product_name)values (611,5004,'Studio');
insert into Product(Product_id,ProductFamily_id,Product_name)values (612,5004,'co-op');
insert into Product(Product_id,ProductFamily_id,Product_name)values (613,5005,'Earrings');
insert into Product(Product_id,ProductFamily_id,Product_name)values (614,5005,'Pendent');
insert into Product(Product_id,ProductFamily_id,Product_name)values (615,5005,'Bracelet');
4.

insert into Customer(Customer_id,Customer_name,Region_id,market_id)values (12101,'Phoebe buffet',10001,101);
insert into Customer(Customer_id,Customer_name,Region_id,market_id)values (12102,'Stella Prichett',10001,101);
insert into Customer(Customer_id,Customer_name,Region_id,market_id)values (12103,'Jay Prichett',10001,101);
insert into Customer(Customer_id,Customer_name,Region_id,market_id)values (12104,'Courtney Cox',10002,102);
insert into Customer(Customer_id,Customer_name,Region_id,market_id)values (12105,'Megan Markle',10002,102);
insert into Customer(Customer_id,Customer_name,Region_id,market_id)values (12106,'David Beckham',10002,102);
insert into Customer(Customer_id,Customer_name,Region_id,market_id)values (12107,'Dean Smith',10003,103);
insert into Customer(Customer_id,Customer_name,Region_id,market_id)values (12108,'Shawn Black',10003,103);
insert into Customer(Customer_id,Customer_name,Region_id,market_id)values (12109,'Nora Green',10003,103);
insert into Customer(Customer_id,Customer_name,Region_id,market_id)values (12110,'Joey Cooper',10004,104);
insert into Customer(Customer_id,Customer_name,Region_id,market_id)values (12111,'Matt Marley',10004,104);
insert into Customer(Customer_id,Customer_name,Region_id,market_id)values (12112,'Rebecca Tribiani',10004,104);
insert into Customer(Customer_id,Customer_name,Region_id,market_id)values (12113,'Stephnie White',10005,105);
insert into Customer(Customer_id,Customer_name,Region_id,market_id)values (12114,'Mark Daniels',10005,105);
insert into Customer(Customer_id,Customer_name,Region_id,market_id)values (12115,'Sheldon Dsouza',10005,105);
5.

insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55501,'CONFIRMED','2018-07-01',12101,601,201);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55502,'COMPLETED','2010-04-01',12101,602,201);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55503,'COMPLETED','2018-05-11',12101,603,202);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55504,'COMPLETED','2015-03-12',12101,604,204);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55505,'COMPLETED','2012-02-17',12101,605,205);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55506,'COMPLETED','2017-07-01',12102,601,203);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55507,'CONFIRMED','2018-07-01',12102,601,201);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55508,'COMPLETED','2010-04-01',12102,602,201);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55509,'COMPLETED','2018-05-11',12102,603,202);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55510,'COMPLETED','2015-03-12',12102,604,204);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55511,'COMPLETED','2012-02-17',12102,605,205);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55512,'COMPLETED','2017-07-01',12102,601,203);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55513,'CONFIRMED','2018-07-01',12103,601,201);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55514,'COMPLETED','2010-04-01',12103,602,201);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55515,'COMPLETED','2018-05-11',12103,603,202);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55516,'COMPLETED','2015-03-12',12104,604,204);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55517,'COMPLETED','2012-02-17',12103,605,205);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55518,'COMPLETED','2017-07-01',12103,601,203);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55519,'CONFIRMED','2018-07-01',12102,601,201);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55520,'COMPLETED','2010-04-01',12103,602,201);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55521,'COMPLETED','2018-05-11',12103,603,202);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55522,'COMPLETED','2015-03-12',12103,604,204);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55523,'COMPLETED','2012-02-17',12104,605,205);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55524,'COMPLETED','2017-07-01',12104,601,203);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55525,'CONFIRMED','2018-07-01',12101,601,201);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55526,'COMPLETED','2010-04-01',12105,602,201);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55527,'COMPLETED','2018-05-11',12101,603,202);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55528,'COMPLETED','2015-03-12',12103,604,204);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55529,'COMPLETED','2012-02-17',12101,605,205);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55530,'COMPLETED','2017-07-01',12102,601,203);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55531,'CONFIRMED','2018-07-01',12102,601,201);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55532,'COMPLETED','2010-04-01',12102,602,201);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55533,'COMPLETED','2018-05-11',12105,603,202);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55534,'COMPLETED','2015-03-12',12102,604,204);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55535,'COMPLETED','2012-02-17',12102,605,205);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55536,'COMPLETED','2017-07-01',12102,601,203);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55537,'CONFIRMED','2018-07-01',12103,601,201);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55538,'COMPLETED','2010-04-01',12104,602,201);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55539,'COMPLETED','2018-05-11',12103,603,202);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55540,'COMPLETED','2015-03-12',12105,604,204);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55541,'COMPLETED','2012-02-17',12103,605,205);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55542,'COMPLETED','2017-07-01',12101,601,203);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55543,'CONFIRMED','2018-07-01',12103,601,201);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55544,'COMPLETED','2010-04-01',12102,602,201);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55545,'COMPLETED','2018-05-11',12103,603,202);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55546,'COMPLETED','2015-03-12',12103,604,204);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55547,'COMPLETED','2012-02-17',12104,605,205);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55548,'COMPLETED','2017-07-01',12104,601,203);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55549,'COMPLETED','2017-07-01',12105,601,203);
insert into SalesOrder(SalesOrder_id,SalesOrderStatus,SalesOrderDate,Customer_id,Product_id,Agent_id)values (55550,'COMPLETED','2017-07-01',12104,601,203);

venereology answered 10 months ago

Next > < Previous

Related Solutions

Write CREATE TABLE and INSERT INTO statements in order to create and populate five tables in...

Write CREATE TABLE and INSERT INTO statements in order to create and populate five tables in Oracle’s SQL*Plus.The information you need about the database ARE IN THE CHARTS BELOW. Each worksheet includes the following information about its associated table: ➢ Column names (for example, the jr_order table contains the orderID, customerID, orderDate, orderStatus, and orderShippedDate columns); ➢ Column data types (for example, orderID is INT, orderStatus is VARCHAR2(2), etc.); ➢ Column constraints, if any (for example, orderID in the jr_order...

Create three MySQL database tables and write SQL scripts to read, insert, and delete data. The...

Create three MySQL database tables and write SQL scripts to read, insert, and delete data. The first database table will contain the names of at least four movies. The second table will be a list of actors who appear in the movies. The third table will be an associative table that describes the relationship between the actors and their movies (which actors appear in which movies). Actors and movies have a “many-to-many relationship,” meaning an actor can be in multiple...

Create three MySQL database tables and write SQL scripts to read, insert, and delete data. The...

DBMS Create/Insert/Update SQL I need the create, insert, and update SQL statement for this table: Customer...

DBMS Create/Insert/Update SQL I need the create, insert, and update SQL statement for this table: Customer PK Customer ID Text Phone Number int name text address ID int email text FK vendor ID int Vendor is the name of the table the FK comes from.

How to populate an SQL table that has two PK (foreign) that have different number of...

How to populate an SQL table that has two PK (foreign) that have different number of rows? Example: there are 3 tables. student, assigment, and hoursDedicated. there are 5 students, and 3 assigments, and x hoursDedicated. hoursDedicated has 2 Primary Keys, studentID and assigmentID. how do you populate hoursDedicated?? Thank you for your time.

Write the SQL DDL to create the following 5 tables for an App store: Publisher, Category,...

Write the SQL DDL to create the following 5 tables for an App store: Publisher, Category, App, AppVersion, AppVersionReview: A Publisher table where each publisher is identified by an integer id and has a name (up to 40 characters). (1 mark) A Category table where each category has an id (integer), a name (up to 50 characters), and a parentId to identify its parent category. The parentId should be a foreign key to the Category table. (1.5 marks) An App...

Need SQL Tables Final Project should be included ER, NER, Table diagrams and SQL statements. The...

Need SQL Tables Final Project should be included ER, NER, Table diagrams and SQL statements. The final project is about developing an auction Web site. The details are as follows: BA is an online auction Web site. People can buy and sell items in this Web site. Buyers are people who like to buy items, and sellers are people who like to sell items. •Each seller can sell items. •Each item has a bidding start time, an end time, and...

1. Write a SQL statement which joins the rider_student table with the rider_major table and lists...

1. Write a SQL statement which joins the rider_student table with the rider_major table and lists the rider student name and the name of the major (major_name) and the description of the major for which they are currently assigned. (You may use the SQL 'join' subclause, or simply express the join as part of the 'where' clause by indicating that you only want records where the primary key of the child table, rider_major, equals the corresponding foreign key of the...

Create a table in SQL with foreign key reference: 1.Create the three tables without any columns...

Create a table in SQL with foreign key reference: 1.Create the three tables without any columns 2.Alter the tables to add the columns 3.Alter the tables to create the primary and foreign keys

5. Write the SQL query that accomplishes the task in the ZAGI Retail Company Sales Department...

5. Write the SQL query that accomplishes the task in the ZAGI Retail Company Sales Department Database: 5.1.3. Display the CustomerName and CustomerZip for all customers, sort alphabetically by CustomerName. 5.1.4. Display the RegionID of regions where we have stores (use only table STORES and do not display the same information more than once). 5.1.5 Display all the information for all stores whose ReigionID value is C 5.1.8 Display the ProductID, ProductName, ProductPrice, and VendorName for all products. Sort the...

Subjects