The following tables form part of a database held in a Relational Database Manag

Question

The following tables form part of a database held in a Relational Database Management System for a printing company that handles printing jobs for book publishers Publisher (pubID, publiame, street, city, postcode, telNo, creditcode) BookJob (iobD, ubIR, jobDate, description, jobType) PurchaseOrder (jobID, poID, poDate) PurchaseItem (jobID, poID, itemID, quantity) Item fitemID, description, onHand, price) In the above, primary keys are underlined. where: Publisher contains publisher details and publD is the key BookJob: contains details of the printing jobs (books or part books) and joblD is the key PurchaseOrder : A printing job requires the use of materials, such as paper and ink, which are assigned to a job via purchase orders. This table contains details of the purchase orders for each job and the key is joblD/polD. Each printing job may have several purchase orders assigned to it. Purchaseltem: Each purchase order may contain several PO items. This table contains details of the PO items and joblD/polD/itemlD form the key Item: contains details of the materials which appear in POltem, and the key is itemID Write the following queries using SQL: 1.Display all printing jobs for the publisher Gold Press. List JoblD and Description. Use JOIN...ON 2. Display the names and phone numbers of all publisher who have a rush job (jobType 'R). Use JOIN...ON 3. Display the dates of all the purchase orders for the publisher 'Gold Press List polD and poDate. Use JOIN...ON 4. How many publishers fall into each credit code category? 5. Display all items with a price below the average price of an item. use a subquery 6. Display all job type's with at least three printing jobs. List jobType and the number of printing jobs. 7. Create a view of publisher details for all publisher who have a rush printing job (jobType), excluding their credit code

Explanation / Answer

1. Select jobID,description from BookJob inner join Publisher on BookJob.pubID = Publisher.pubID where Publisher.pubName = 'Gold Press';

2. Select pubName,telNo from Publisher inner join BookJob on Publisher.pubID = BookJob.pubID where jobType ='R';

3. Select poID,poDate from PurchaseOrder inner join BookJob on PurchaseOrder.jobID = BookJob.jobID inner join Publisher on BookJob.pubID = Publisher.pubID where pubName = 'Gold Press';

4. Select count(pubID) from Publisher group by creditCode;

5. Select * from Item where price <(Select avg(price) from Item);

6. Select jobType,count(jobID) from BookJob group by jobType having jobType= 'Printing' and count(jobID) >= 3;

7. Create view Publisher_Rush_Printing as Select pubID,pubName,street,city,postCode,telNo from Publisher inner join BookJob on Publisher.pubID = BookJob.pubID where jobType = 'Rush Printing';

Do ask if any doubt. Please upvote.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

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