Given: using linux and connected to an oracle database that is made up of 6 tabl

Question

Given: using linux and connected to an oracle database that is made up of 6 tables: customer, employee, parts, orders, partOrder, telephone. Complete the following tasks. Reminder that you must use linux and oracle sql

Key Information:

a) Employee Primary Key: eid d) partOrder Primary Key: poid Foreign Keys: pid, oid

b) customer primary key: cid e) part Primary Key: pid

c) order primary Key: oid Foreign keys: cid, eid f) telephone Primary Key: tid Foreign Key: eid

1) Create and execute query for order information joined with customer information.
2) Create and execute query that lists employee information together with the number of orders they have processed.
3) Create and execute a query that deletes all PartOrder records for Orders for which the shipping date is in the past.
4) Add a column called processed to the PartOrder table.
5) 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)

Explanation / Answer

There are the four different types of the JOIN in SQL:

(INNER) JOIN: Returns all records with matching values in both tables
LEFT (OUTER) JOIN: Return all records from the left table, but only matched records from the right table
RIGHT (OUTER) JOIN: Return all records from the right table, but only matched records from the left table
FULL (OUTER) JOIN: Return all records if any of the recordes matches.
So as per your requirement you can change "JOIN" in following queries:

To create view of part information of such parts for which Orders werer received in 2016 use :

CREATE VIEW viewName as SELECT * FROM partOrder WHERE EXISTS(SELECT order.o_id from order where order.o_id = partOrder.o_id and YEAR(order.receivedDate) = '2016');

However, total quantity of such parts is just a single value for 2016 and can be extracted using :

SELECT COUNT(partOrder.p_id) FROM partOrder JOIN order ON order.o_id = partOrder.o_id WHERE YEAR(order.receivedDate) = '2016';

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

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