1) Create and execute a query that deletes all PartOrder records for Orders for

Question

1) Create and execute a query that deletes all PartOrder records for Orders for which the shipping date is in the past.

2) Create a view of Part information together with the total quantity of such parts for which Orders were received in 2016 (use a different year, if there is no corresponding record in your database)

This needs to be done using oracle sql64plus. I have already created the tables and inserted values into them I just need to figure out how these two steps should be done.

CREATE TABLE Employee
( eid INT,
fname VARCHAR(15) NOT NULL,
lname VARCHAR(15) NOT NULL,
ezip INT,
PRIMARY KEY (eid));

CREATE TABLE Telephone
( tid INT,
ttid INT,
num INT,
PRIMARY KEY (tid),
FOREIGN KEY (ttid) REFERENCES Employee(eid));

CREATE TABLE Customer
( cid INT,
fname VARCHAR(15) NOT NULL,
lname VARCHAR(15) NOT NULL,
street VARCHAR(15) NOT NULL,
city VARCHAR(15) NOT NULL,
czip INT,
PRIMARY KEY (cid));

CREATE TABLE Part
( pid INT,
pname VARCHAR(20),
price DECIMAL,
pquantity INT,
PRIMARY KEY (pid));

CREATE TABLE Orders
( oid INT,
rdate DATE,
sdate DATE,
tcid INT,
teid INT,
PRIMARY KEY (oid),
FOREIGN KEY (tcid) REFERENCES Customer(cid),
FOREIGN KEY (teid) REFERENCES Employee(eid));

CREATE TABLE PartOrder
( poid INT,
ppid INT,
pooid INT,
oquantity INT,
PRIMARY KEY (poid),
FOREIGN KEY (ppid) REFERENCES Part(pid),
FOREIGN KEY (pooid) REFERENCES Orders(oid));

INSERT INTO Employee
VALUES (1234, 'Albus', 'Dumbledore', 55376);

INSERT INTO Employee
VALUES (1235, 'Severus', 'Snape', 55376);

INSERT INTO Customer
VALUES (1236, 'Harry', 'Potter', 'Private Drive', 'Little Whinging', 55832);

INSERT INTO Customer
Values (1237, 'Ron', 'Weasley', 'Who Knows', 'Devon', 55432);

INSERT INTO Part
Values (1245, 'Elder Wand', 10000.00, 1);

INSERT INTO Part
Values (1246, 'cloaks', 50.00, 20);

INSERT INTO Telephone
Values (1247, 1234, 7632573222);

INSERT INTO Telephone
Values (1248, 1235, 7632571111);

INSERT INTO PartOrder
Values (1711, 1246, 7891, 3);

INSERT INTO PartOrder
Values (1212, 1245, 7890, 1);

INSERT INTO Orders
Values (2323, '01-FEB-16', '02-FEB-16', 1236, 1234);

INSERT INTO Orders
Values (2376, '01-DEC-17', '02-DEC-17', 1237, 1235);

Explanation / Answer

1. delete from PartOrder where poid in (select oid from Orders where sdate < SYSDATE);

This will delete the PartOrder data which is having shipping date as past than current date.

2. create view part_info as
select pid,sum(pquantity) as total_quantity from Part p,Orders o,PartOrder po where p.pid=po.ppid and po.pooid=o.oid and to_char(rdate,'YYYY')=2016;   

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.