Question 4 Construct SQL Queries [30 marks] Details about the Perilous Print database are given in the file Perilous Pri

[answerhappygod]

Question 4 Construct Sql Queries 30 Marks Details About The Perilous Print Database Are Given In The File Perilous Pri 1 (38.86 KiB) Viewed 61 times

Question 4 Construct Sql Queries 30 Marks Details About The Perilous Print Database Are Given In The File Perilous Pri 2 (42.19 KiB) Viewed 61 times

Question 4 Construct SQL Queries [30 marks] Details about the Perilous Print database are given in the file Perilous Print Database Details.pdf. The file contains: 1. the entity relationship diagram (ERD), 2. explanation about Perilous Print, and 3. the database structure for the above database. Using all this information about the Perilous Print database, write SQL statements to answer each of the following questions: Please note: For queries that need multiple table joins, you must use Oracle JOIN syntax that you learnt in this course. You may lose marks if you use JOIN syntax that will not work in Oracle. (e)Display the publisher number, name and city of all publishers that have printjobs where job type is not specified. Sort the result in ascending order of publisher name. (6 marks)

The database In this test you will be using the Perilous Prinr database. The tables included in the database are described below. pintatt PK stato printios PK officeNo Brincountry PK countryNo Address O TONO oFox No - FK Martin FK1 OLIND country Name Nama INama Name Adress Te No Faxha SOK DCE job taxtado salary FK1 office No morto publisher pubNa Printah PK PK LODNO jol:D jobsDescriplan job Type FK1 puble FK2 SENO Neme | 2Chty H STONE FaxNo CredilCode FK1 Melo print Purchase Orde PK PONO prin POller PK.FK1pono PK,FK2 iternNo FK1jbNo poDate FK2 supplier ID cuently printSupplier PK supplier ontsupolleriem PK itemNo Description SNAMIC Adara Country STONO FaxNa pricePerlen FK uplier 5

Perilous Print is a large printing company that does work for book publishers throughout The Asia Pacific region. The company currently has 10 offices, most of which operate autonomously, apart from salaries, which are paid by the head office in each country. To improve the sharing and communication of data, the company has decided to implement a Distributed DBMS. Perilous Print jobs consist of printing books or part of books. A printing job requires the use of materials, such as paper and ink, which are assigned to a job via purchase orders. Lach printing job may have several purchase orders assigned to it. Likewise, each purchase order may contain several purchase order items. As well as accessing printing jobs based on the publisher, jobs can also be accessed on the job type (job Type), which can be: Normal: 2 - Rush. • printCountry contains the names of each country that Perilous Print opcralcs in and the country number (countryNo) is the key. • printOffice contains details of each office and the office number (OfficeNo) is the key. Each office has a Manager represented by the manager's staff number (mgrStaffNo). • printStaff contains details of staff and the staff number (staffNo) is the key. The office that the member of stutt works from is giveni lay officeNo. Fach person's manager is given by mgrNo. • publisher contains details of publisher and the publisher number (pubNo) is the key. Pub- lishers are registered with the nearest office in their country. given by officeNo. • printSupplier contains details of suppliers that the company buys their supplies from, and the supplier number (supplierID) is the key • printJob contains details of publishing jobs and the job number (jobNo) is the key. The publisher is given by the publisher number (pubNo) and the supervisor for the job by staffNo. • printSupplier tem contains items that are supplied by individual suppliers. The items are identified by itemNo and are linked to printSupplier with supplieriD. • printPurchase Order cintains details aſ the purchase orders for each jush and purchase order number (poNo) is the key. • printPOltem contains those items that are ordered by the company from suppliers. It has a two part key (poNo and itemNo) which are also foreign keys. 6

Perilous Print Schema prin Country countryNo country Name printOffice officeNo 0/\ddress TelNo ol'axNo mgrStaffNo Caruntry Nu printStaff stafino IName 1Name SAddress slel No sFax.No sex DOB job taxCode salary officeNo mgr Vo publisher pubNo p. Name pCity pre No pFaxNo creditCode olliceNo printSupplier supplierID sName S.Achures sCountry sTcINo sFax. o printlob job No jobDale jobDescription jubType pubNo salino printSuppliertem itemNo iteniDescription pricePerlem supplierID printPurchase Order pono job No poDate supplierID printPOllem pollo itemNo quantity CHARTAN PRIMARY KEY VARCHAR2(12) CHAR(41 PRIMARY KEY VARCHAR2(12) CHAR(14) CITARI14) CHARI) CHAR(4) FOREIGN KEY CUARTO) PRIMARY KIY VARCHAR2(20) VARCHAR2(20) VARCHAR2(60) CHARI14) CHAR(14) CHAR(1) DATE VARCHAR2(20) CHAR(11 NUMBER(10,2) CILAR(4) FOREIGN KEY CHAR(6) CHAR4) PRIMARY KEY VARCHAR2(40) VARCHAR2(20) CHARI14) CHAR(14) CHARI 10) CHAR(4) CHAR/4) PRIMARY KEY VARCHAR2(35) VARCHAR2(60) VARCHAR2(30) C'HAR(14) CHARI 141 CHARITOJ PRIMARY KIY DATE VARCHAR2(60) CHARID) CHAR(41 FOREIGN KEY CHAR(6) FOREIGN KEY CHART4) PRIMARY KEY VARCHAR2(60) NUMBER(10.2) CHAR(41 FOREIGN KEY CHAR(41 PRIMARY KEY CHARITO) FOREIGN KEY DATE CHAR(4) FOREIGN KEY CHAR(4) PRIMARY KEY, FOREIGN KEY CHAR4) PRIMARY KEY, FOREIGN KEY NLMBER10,2) 7