Question

In: Computer Science

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 table is a primary key,

supplierName in the jr_supplier table is NOT NULL, etc.); and

➢ All values (i.e., the data).

Example INSERT code for each table

[Note that each of these INSERT INTO statements corresponds to the first row in its respective Excel worksheet!]

Example INSERT code for the xx_customer table

INSERT INTO jr_customer (

customerID, customerLastName, customerFirstName,

customerCity, customerState, customerZIP, customerEmail,

jr_customerPhoneNum ) VALUES (1,'Alexander','Michael',

'Highland Heights','KY','41076','[email protected]',NULL) ;

Example INSERT code for the xx_supplier table

INSERT INTO jr_supplier (

supplierID, supplierName, supplierCity, supplierState,

supplierContactName, supplierContactTitle, supplierContactEmail )

VALUES (1,'Reaper','Denton',’TX’,

'John Franklin','Product Marketing Manager','[email protected]') ;

Example INSERT code for the xx_order table

INSERT INTO jr_order (

orderID, customerID, orderDate, orderStatus, orderShippedDate )

VALUES (1,1,TO_DATE('2018-03-01','yyyy-mm-dd'),'SP',

TO_DATE('2018-03-04','yyyy-mm-dd') ) ;

Example INSERT code for the xx_product table

INSERT INTO jr_product (

productCode, supplierID, productName, suggestedPrice,

unitsInStock, unitsOnOrder, discontinued ) VALUES

('IM511',2,'Cloaked Assassin',4.95,4,1,'N') ;

Example INSERT code for the xx_orderLine table

INSERT INTO jr_orderLine (

orderLineID, orderID, productCode, quantity, unitPrice )

VALUES (1,1,'IM511',1,5.49) ;

MS EXCEL WORKBOOK INFO:

SUPPLIER:

customerID
customerLN
customerFN
customerCity
customerState
customerZIP
customerEmail
customerPhoneNum
INT
VARCHAR2(25)
VARCHAR2(20)
VARCHAR2(25)
VARCHAR2(2)
VARCHAR2(10)
VARCHAR2(25)
VARCHAR2(12)
PRIMARY KEY
NOT NULL
NOT NULL
1 Alexander Michael Highland Heights KY 41706
[email protected]
NULL
2 Clark Franklin Amelia OH 45102
[email protected]
513-293-4435
3 Philips Trevor Newtown OH 45244
[email protected]
NULL
4 Butler Ashley Erlanger KY 42017
[email protected]
859-563-8090
5 Landon Frank Reading OH 45215
[email protected]
NULL
6 Herbert Floyd Cincinnati OH 45211
[email protected]
NULL
7 McGee Martin Cincinnati OH 45238
[email protected]
NULL

ORDER:

orderID customerID orderDate orderStatus orderShippedDate
INT INT DATE VARCHAR2(2) DATE
PRIMARY KEY NOT NULL, foreign key NOT NULL NOT NULL
1 1 2018-03-01 SP 2018-03-04
2 2 2018-03-02 SP 2018-03-04
3 3 2018-03-03 SP 2018-03-05
4 4 2018-03-03 SP 2018-03-05
5 5 2018-03-05 SP 2018-03-07
6 6 2018-03-05 SP 2018-03-08
7 7 2018-03-08 SP 2018-03-10
16 2 2018-03-15 SP 2018-03-17
17 4 2018-03-16 SP 2018-03-19
20 7 2018-03-18 SP 2018-03-20
23 5 2018-03-20 SP 2018-03-22

PRODUCT:

productCode supplierID productName suggestedPrice unitsInStock unitsOnOrder discontinued
VARCHAR2(15) INT VARCHAR2(30) NUMBER(5,2) NUMBER(3) NUMBER(3) VARCHAR2(1)
PRIMARY KEY NOT NULL, foreign key NOT NULL
IM511 2 Cloaked Assassin 4.95 4 1 N
IM514 2 Hooded Female Thief 4.25 3 0 N
IM517 2 Cleric with Staff 4.95 5 2 N
IM519 2 Berserker 3.71 2 1 N
IM527 2 Grand Mystic 4.95 3 0 N
IM537 2 Hydra 29.95 3 0 Y
IM539 2 Umber Hulk 11.59 4 1 N
IM540 2 Wraith 5.25 6 2 N
IM556 2 Heroic Paladin 5.99 7 3 N
IM657 2 Two-Headed Troll 12.99 2 0 Y
R03495 1 Werewolf 9.99 3 0 Y
R03515 1 Dervish Warrior 4.99 4 2 N
R03527 1 Scourge Devil 11.99 1 2 N
R03581 1 Human Ranger 7.49 9 2 N
R03669 1 Dark Elf 7.99 8 0 N

OrderLine:

orderLineID orderID productCode quantity unitPrice
INT INT VARCHAR2(15) NUMBER(3) NUMBER(5,2)
PRIMARY KEY NOT NULL, foreign key NOT NULL, foreign key NOT NULL NOT NULL
1 1 IM511 1 5.49
2 1 R03495 1 10.99
3 2 IM514 1 4.99
4 3 IM517 1 4.99
5 4 R03515 1 5.99
6 5 R03669 1 8.99
7 6 IM657 1 12.99
9 7 R03527 1 11.99
8 7 R03581 2 7.49
19 16 IM527 1 4.95
18 16 IM537 1 34.95
20 16 IM540 1 5.99
21 17 IM556 1 6.99
27 20 IM519 1 4.19
31 23 IM539 1 12.49

Solutions

Expert Solution

Here are the SQL statements:

CREATE TABLE jr_customer (customerID number(10) NOT NULL, customerLastName varchar2(20) NOT NULL, customerFirstName varchar2(20) NOT NULL, customerCity varchar2(20) NOT NULL, customerState varchar2(2) NOT NULL, customerZIP varchar2(5) NOT NULL, customerEmail varchar2(25) NOT NULL,jr_customerPhoneNum varchar2(15), CONSTRAINT customers_pk PRIMARY KEY (customerID) );

CREATE TABLE jr_supplier (supplierID number(10) NOT NULL, supplierName varchar2(20) NOT NULL, supplierCity varchar2(20) NOT NULL, supplierState varchar2(2) NOT NULL, supplierContactName varchar2(20) NOT NULL, supplierContactTitle varchar2(20) NOT NULL, supplierContactEmail  varchar2(25) NOT NULL, CONSTRAINT supplier_pk PRIMARY KEY (supplierID));

CREATE TABLE jr_order (orderID number(10) NOT NULL, customerID number(10) NOT NULL, orderDate date, orderStatus varchar2(2) NOT NULL, orderShippedDate date, CONSTRAINT order_pk PRIMARY KEY (orderID), CONSTRAINT fk_cust FOREIGN KEY (customerID) REFERENCES jr_customer(customerID));

CREATE TABLE jr_product (productCode varchar2(10) NOT NULL, supplierID number(10) NOT NULL, productName varchar2(10) NOT NULL, suggestedPrice number(10, 2) NOT NULL, unitsInStock number(10) NOT NULL, unitsOnOrder number(10) NOT NULL, discontinued varchar2(1) NOT NULL, CONSTRAINT product_pk PRIMARY KEY (productCode), CONSTRAINT fk_sup FOREIGN KEY (supplierID) REFERENCES jr_supplier (supplierID));

CREATE TABLE jr_orderLine (orderLineID number(10) NOT NULL, orderID number(10) NOT NULL, productCode  varchar2(10) NOT NULL, quantity number(10) NOT NULL, unitPrice  number(10, 2) NOT NULL, CONSTRAINT orderline_pk PRIMARY KEY (orderLineID), CONSTRAINT o_fk FOREIGN KEY (orderID) REFERENCES jr_order (orderID), CONSTRAINT pc_fk FOREIGN KEY  (productCode) REFERENCES jr_product (productCode));

INSERT INTO jr_customer (customerID, customerLastName, customerFirstName, customerCity, customerState, customerZIP, customerEmail,jr_customerPhoneNum ) VALUES
(1,'Alexander','Michael','Highland Heights','KY','41076','[email protected]',NULL) ;
INSERT INTO jr_customer (customerID, customerLastName, customerFirstName, customerCity, customerState, customerZIP, customerEmail,jr_customerPhoneNum ) VALUES
(2,'Clark','Franklin','Amelia','OH','45102','[email protected]','513-293-4435') ;
INSERT INTO jr_customer (customerID, customerLastName, customerFirstName, customerCity, customerState, customerZIP, customerEmail,jr_customerPhoneNum ) VALUES
(3,'Philips','Trevor','Newtown','OH','45244','[email protected]',NULL) ;             
INSERT INTO jr_customer (customerID, customerLastName, customerFirstName, customerCity, customerState, customerZIP, customerEmail,jr_customerPhoneNum ) VALUES
(4,'Butler','Ashley','Erlanger','KY','42017','[email protected]','859-563-8090') ;          
INSERT INTO jr_customer (customerID, customerLastName, customerFirstName, customerCity, customerState, customerZIP, customerEmail,jr_customerPhoneNum ) VALUES
(5,'Landon','Frank','Reading','OH','45215','[email protected]',NULL) ;          
INSERT INTO jr_customer (customerID, customerLastName, customerFirstName, customerCity, customerState, customerZIP, customerEmail,jr_customerPhoneNum ) VALUES
(6,'Herbert','Floyd','Cincinnati','OH','45211','[email protected]',NULL) ;         
INSERT INTO jr_customer (customerID, customerLastName, customerFirstName, customerCity, customerState, customerZIP, customerEmail,jr_customerPhoneNum ) VALUES
(7,'McGee','Martin','Cincinnati','OH','45238','[email protected]',NULL) ;

INSERT INTO jr_supplier (supplierID, supplierName, supplierCity, supplierState, supplierContactName, supplierContactTitle, supplierContactEmail ) VALUES 
(1,'Reaper','Denton',’TX’,'John Franklin','Product Marketing Manager','[email protected]') ;

INSERT INTO jr_order (orderID, customerID, orderDate, orderStatus, orderShippedDate ) VALUES 
(1,1,TO_DATE('2018-03-01','yyyy-mm-dd'),'SP', TO_DATE('2018-03-04','yyyy-mm-dd') ) ;
INSERT INTO jr_order (orderID, customerID, orderDate, orderStatus, orderShippedDate ) VALUES 
(2,2,TO_DATE('2018-03-02','yyyy-mm-dd'),'SP', TO_DATE('2018-03-04','yyyy-mm-dd') ) ;
INSERT INTO jr_order (orderID, customerID, orderDate, orderStatus, orderShippedDate ) VALUES 
(3,3,TO_DATE('2018-03-03','yyyy-mm-dd'),'SP', TO_DATE('2018-03-05','yyyy-mm-dd') ) ;
INSERT INTO jr_order (orderID, customerID, orderDate, orderStatus, orderShippedDate ) VALUES 
(4,4,TO_DATE('2018-03-03','yyyy-mm-dd'),'SP', TO_DATE('2018-03-05','yyyy-mm-dd') ) ;
INSERT INTO jr_order (orderID, customerID, orderDate, orderStatus, orderShippedDate ) VALUES 
(5,5,TO_DATE('2018-03-05','yyyy-mm-dd'),'SP', TO_DATE('2018-03-07','yyyy-mm-dd') ) ;
INSERT INTO jr_order (orderID, customerID, orderDate, orderStatus, orderShippedDate ) VALUES 
(6,6,TO_DATE('2018-03-05','yyyy-mm-dd'),'SP', TO_DATE('2018-03-08','yyyy-mm-dd') ) ;
INSERT INTO jr_order (orderID, customerID, orderDate, orderStatus, orderShippedDate ) VALUES 
(7,7,TO_DATE('2018-03-08','yyyy-mm-dd'),'SP', TO_DATE('2018-03-10','yyyy-mm-dd') ) ;
INSERT INTO jr_order (orderID, customerID, orderDate, orderStatus, orderShippedDate ) VALUES 
(16,2,TO_DATE('2018-03-15','yyyy-mm-dd'),'SP', TO_DATE('2018-03-17','yyyy-mm-dd') ) ;
INSERT INTO jr_order (orderID, customerID, orderDate, orderStatus, orderShippedDate ) VALUES 
(17,4,TO_DATE('2018-03-16','yyyy-mm-dd'),'SP', TO_DATE('2018-03-19','yyyy-mm-dd') ) ;
INSERT INTO jr_order (orderID, customerID, orderDate, orderStatus, orderShippedDate ) VALUES 
(20,7,TO_DATE('2018-03-18','yyyy-mm-dd'),'SP', TO_DATE('2018-03-20','yyyy-mm-dd') ) ;
INSERT INTO jr_order (orderID, customerID, orderDate, orderStatus, orderShippedDate ) VALUES 
(23,5,TO_DATE('2018-03-20','yyyy-mm-dd'),'SP', TO_DATE('2018-03-22','yyyy-mm-dd') ) ;

INSERT INTO jr_product (productCode, supplierID, productName, suggestedPrice, unitsInStock, unitsOnOrder, discontinued ) VALUES
('IM511',2,'Cloaked Assassin',4.95,4,1,'N') ;
INSERT INTO jr_product (productCode, supplierID, productName, suggestedPrice, unitsInStock, unitsOnOrder, discontinued ) VALUES
('IM514',2,'Hooded Female Thief',4.25,3,0,'N') ;
INSERT INTO jr_product (productCode, supplierID, productName, suggestedPrice, unitsInStock, unitsOnOrder, discontinued ) VALUES
('IM517',2,'Cleric with Staff',4.95,5,2,'N') ;
INSERT INTO jr_product (productCode, supplierID, productName, suggestedPrice, unitsInStock, unitsOnOrder, discontinued ) VALUES
('IM519',2,'Berserker',3.71,2,1,'N') ;
INSERT INTO jr_product (productCode, supplierID, productName, suggestedPrice, unitsInStock, unitsOnOrder, discontinued ) VALUES
('IM527',2,'Grand Mystic',4.95,3,0,'N') ;
INSERT INTO jr_product (productCode, supplierID, productName, suggestedPrice, unitsInStock, unitsOnOrder, discontinued ) VALUES
('IM537',2,'Hydra',29.95,3,0,'Y') ;
INSERT INTO jr_product (productCode, supplierID, productName, suggestedPrice, unitsInStock, unitsOnOrder, discontinued ) VALUES
('IM539',2,'Umber Hulk',11.59,4,1,'N') ;
INSERT INTO jr_product (productCode, supplierID, productName, suggestedPrice, unitsInStock, unitsOnOrder, discontinued ) VALUES
('IM540',2,'Wraith',5.25,6,2,'N') ;
INSERT INTO jr_product (productCode, supplierID, productName, suggestedPrice, unitsInStock, unitsOnOrder, discontinued ) VALUES
('IM556',2,'Heroic Paladin',5.99,7,3,'N') ;
INSERT INTO jr_product (productCode, supplierID, productName, suggestedPrice, unitsInStock, unitsOnOrder, discontinued ) VALUES
('IM657',2,'Two-Headed Troll',12.99,2,0,'Y') ;
INSERT INTO jr_product (productCode, supplierID, productName, suggestedPrice, unitsInStock, unitsOnOrder, discontinued ) VALUES
('R03495',1,'Werewolf',9.99,3,0,'Y') ;
INSERT INTO jr_product (productCode, supplierID, productName, suggestedPrice, unitsInStock, unitsOnOrder, discontinued ) VALUES
('R03515',1,'Dervish Warrior',4.99,4,2,'N') ;
INSERT INTO jr_product (productCode, supplierID, productName, suggestedPrice, unitsInStock, unitsOnOrder, discontinued ) VALUES
('R03527',1,'Scourge Devil',11.99,1,2,'N') ;
INSERT INTO jr_product (productCode, supplierID, productName, suggestedPrice, unitsInStock, unitsOnOrder, discontinued ) VALUES
('R03581',1,'Human Ranger',7.49,9,2,'N') ;
INSERT INTO jr_product (productCode, supplierID, productName, suggestedPrice, unitsInStock, unitsOnOrder, discontinued ) VALUES
('R03669',1,'Dark Elf',7.99,8,0,'N') ;

INSERT INTO jr_orderLine (orderLineID, orderID, productCode, quantity, unitPrice ) VALUES 
(1,1,'IM511',1,5.49) ;
INSERT INTO jr_orderLine (orderLineID, orderID, productCode, quantity, unitPrice ) VALUES 
(2,1,'R03495',1,10.99) ;
INSERT INTO jr_orderLine (orderLineID, orderID, productCode, quantity, unitPrice ) VALUES 
(3,2,'IM514',1,4.99) ;
INSERT INTO jr_orderLine (orderLineID, orderID, productCode, quantity, unitPrice ) VALUES 
(4,3,'IM517',1,4.99) ;
INSERT INTO jr_orderLine (orderLineID, orderID, productCode, quantity, unitPrice ) VALUES 
(5,4,'R03515',1,5.99) ;
INSERT INTO jr_orderLine (orderLineID, orderID, productCode, quantity, unitPrice ) VALUES 
(6,5,'R03669',1,8.99) ;
INSERT INTO jr_orderLine (orderLineID, orderID, productCode, quantity, unitPrice ) VALUES 
(7,6,'IM657',1,12.99) ;
INSERT INTO jr_orderLine (orderLineID, orderID, productCode, quantity, unitPrice ) VALUES 
(9,7,'R03527',1,11.99) ;
INSERT INTO jr_orderLine (orderLineID, orderID, productCode, quantity, unitPrice ) VALUES 
(8,7,'R03581',2,7.49) ;
INSERT INTO jr_orderLine (orderLineID, orderID, productCode, quantity, unitPrice ) VALUES 
(19,16,'IM527',1,4.95) ;
INSERT INTO jr_orderLine (orderLineID, orderID, productCode, quantity, unitPrice ) VALUES 
(18,16,'IM537',1,34.95) ;
INSERT INTO jr_orderLine (orderLineID, orderID, productCode, quantity, unitPrice ) VALUES 
(20,16,'IM540',1,5.99) ;
INSERT INTO jr_orderLine (orderLineID, orderID, productCode, quantity, unitPrice ) VALUES 
(21,17,'IM556',1,6.99) ;
INSERT INTO jr_orderLine (orderLineID, orderID, productCode, quantity, unitPrice ) VALUES 
(27,20,'IM519',1,4.19) ;
INSERT INTO jr_orderLine (orderLineID, orderID, productCode, quantity, unitPrice ) VALUES 
(31,23,'IM539',1,12.49) ;

venereology answered 1 year ago
Next > < Previous

Related Solutions

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)...
7. Use the substitution & method of INSERT command to populate EMP_PROJ table. INSERT INTO EMP_PROJ...
7. Use the substitution & method of INSERT command to populate EMP_PROJ table. INSERT INTO EMP_PROJ VALUES (‘&empNo’, ‘&projNo’, &hoursWorked); NOTE: enclose &empNo in ‘ ‘ if the datatype is a string – VARCHAR2 or CHAR If empNo is NUMBER datatype then do not enclose &empNo in ‘ ‘! empNo projNo hoursWorked 1000 30 32.5 1000 50 7.5 2002 10 40.0 1444 20 20.0 1760 10 5.0 1760 20 10.0 1740 50 15.0 2060 40 12.0
Write create table statements to create tables for the Exoproduct employees computers database depicted by the...
Write create table statements to create tables for the Exoproduct employees computers database depicted by the relational schema created in a mini case MC5 in chapter 3. Use insert into statements to insert no fewer than 2 and no more than 10 records per table.
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...
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...
Tables: Create table Item(    &nbs... Bookmark Tables: Create table Item(                 ItemId           &nb
Tables: Create table Item(    &nbs... Bookmark Tables: Create table Item(                 ItemId                 char(5) constraint itmid_unique primary key,                 Decription           varchar2(30),                 Unitcost               number(7,2)); Create table Customer(                 custID                   char(5) constraint cid.unique primary key,                 custName          varchar2(20),                 address                                varchar2(50)); Create table Orderdata( orderID                char(5) constraint oid_uniq primary key,                 orderdate           date,                 shipdate              date,                 ItemId                  char(5) references Item.ItemId,                 No_of_items     number(4),                 Unitcost               number(7,2),                 Order_total        number(7,2),                 custID                   char(5) references customer.custID); Insert Into Item values(‘A123’,’Pencil’,2.5);...
Using MYSQL in terminal, create the tables for your above designed relational schema and populate your...
Using MYSQL in terminal, create the tables for your above designed relational schema and populate your tables with appropriate data. All except time slot and the relationship it participates in. Use the names for the tables and attributes from the ER schema. Use ON DELETE CASCADE for foreign keys. Each basic table (corresponding to an entity in the ER schema developed for Part 1) should have 5-10 rows. Campus can have just 2. Building should have at least 6. At...
DBMS Create/Insert/Update SQL I need the create, insert, and update SQL statement for this table as...
DBMS Create/Insert/Update SQL I need the create, insert, and update SQL statement for this table as if it were being added to MySQL (please give explanations for each line of SQL code and a copy of the code as it would be entered into the query by itself: 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.
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.
Using Python, write a segment of code to populate the table "employee" of the database "EmployeeDB”...
Using Python, write a segment of code to populate the table "employee" of the database "EmployeeDB” with the data below. Import your choice of DB connector (import MySQLdb/sqlite3…) Create the“employee” table with schema = [name, address, age] Insert this employee: John Doe, 7001 E Williams Field, 32
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT