Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

answer all Q3 . Description of the schema The following database is used by a building materials company to edit invoices for its customers and

answer all Q3 . image text in transcribed
image text in transcribed
Description of the schema The following database is used by a building materials company to edit invoices for its customers and to manage its inventory. The schema of this database contains the following tables: The customer table is used to record the properties of the company's customers: name. address, etc. The product table is used to record the properties concerning the building materials available for sale: their description, the unit price, the quantity in stock. The order table is used to record the properties concerning an order: its number, the date of the order and the customer identifier The ordered Product table is used to record the "lines" of orders along with the products: the order identifier (orderNO), the product identifier (productNO) and the quantity ordered. Customer (customerNO, customer Name, address, city, category, custBalance) Product (productNO, label, price, QStock) customerOrder (orderNO, orderDate, #customerNO) ordered Product (#order NO. #productNO, orderQuantity) Note: Primary keys are underlined and foreign key are marked with #. Use SQL Developer to answer the questions given below: 3) Open a new SQL worksheet then write in SQL the queries, which answer the questions below. Save the script as queries.sql. (write the number of question as comment before each query) a) Display in alphabetical order the list of cities in which there is at least one customer.[0.5] b) Display the number, name and city of customers who belong to category Cl and who are not living in Muscat.[0.5] c) List the characteristics of products made of steel.[0.5] d) Display the details of products having a price under 200 and a stock more than 150.[0.5] e) List the number, name and balance of customers who are from Salalah or Ibri and whose balance is positive [0.5] 1) List the number of cities in which there are customers of category C1.[0.5] g) Display the number of customers who have placed at least one order. (0.5) h) Calculate, for each city, the number of distinct categories. [0.75] i) Assign zero to the balance of customers of category CI and whose balance is negative. [0.75) j) Change to 10 units the ordered quantity of the product number PA60 in the order number 30185.0.75] k) Calculate the total amount in the stock of products made of fir. [0.75] 1) Display the details (orderNO, orderDate ) of the most recent order.[1] m) Display the details of orders (orderNO, order Date, customerNO ) which doesn't include the products having the code PA60.[1] n) Display numbers and names of customers who are from Muscat and have not placed orders. [1] 0) Display numbers of products (productNO) which are made of fir and are placed in orders. p) Find for each product the list of cities in which this product is ordered in more than 500 units.[1.5) 4) List, for each customer category, the number of products that were ordered on 23-12- 2008.[1.5)

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Concepts of Database Management

Authors: Philip J. Pratt, Mary Z. Last

8th edition

1285427106, 978-1285427102

More Books

Students also viewed these Databases questions