Description of the schema The following database is used by a building materials company to edit...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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, customerName, address, city, category, custBalance) Product (productNO, label, price, QStock) customer Order (orderNO, orderDate, #customerNO) ordered Product (#orderNO, #productNO, orderQuantity) Note: Primary keys are underlined and foreign key are marked with #. Use SQL Developer to answer the questions given below: 1) Open a new SQL worksheet then write the SQL commands to create the tables' structures of the above database (enforce primary key and foreign key constraints). Save this script Schema_creation.sql.[2.5] Customer customerNO char(8) customerName Varchar (20) address Varchar (40) city Varchar(20) Char(2) Decimal(9,2) category custBalance Product productNO char(10) label price Decimal(9,2) QStock Number(11) Varchar (30) customer Order orderNO char(8) orderDate Date customerNO char(8) ordered Product orderNO char(8) productNO char(10) orderQuantity Number(11) 2) Open a new SQL worksheet then write the INSERT commands necessary to place the following data in the tables that were created in the previous question. Save this script as table population.sql. [2.5] customer NO customerName Ahmed Salim Alia B062 B112 B332 B512 C003 C123 C400 Taoufiq Islam Murad Jamilah Product Customer address 72 street Freedom Muscat Street Sultan Qaboos Building Hana Street Alhisn Ibri Building Alaamra Rustaq BI Salalah BI House N 5 Street Asahafa Building Asalalm Main Street Ibri Street of garden House n 5 Muscat CI B2 productNO label 2690 450 CS262 Chev.fir 200x6x2 CS264 Chev.fir 200x6x4 120 CS464 Chev.fir 400x6x4 220 PA45 Steel point 45 (1K) 105 PA60 PH222 PL.Hetre 200x20x2 230 PS222 PL.fir 200x20x2 185 1220 580 Steel point 60 (1K) 95 134 782 Price QStock 75 45 city Category custBalance Salalah B2 -100 1250 0.00 30179 PA60 30182 PA60 30184 CS464 30184 PA45 30185 CS464 30185 PA60 30186 PS222 30186 PA45 30188 PA60 30188 PH222 ordered Product orderNO productNO orderQuantity 30178 CS464 25 30179 CS262 60 20 30 120 20 260 15 600 3 180 92 CI B2 customer Order order NO order Date customerNO 30178 21-DEC-2017 C400 30179 22-DEC-2017 B512 30182 23-DEC-2017 C400 30184 23-DEC-2017 B062 30185 02-JAN-2018 B332 30186 02-JAN-2018 C400 30188 03-JAN-2018 C003 -500 -1000 -2000 350 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 C1 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] f) 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 C1 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, orderDate, 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] o) Display numbers of products (productNO) which are made of fir and are placed in orders. [1] p) Find for each product the list of cities in which this product is ordered in more than 500 units.[1.5] q) List, for each customer category, the number of products that were ordered on 23-12- 2008.[1.5] 4) Open a new SQL worksheet then write a SQL statement to create a view named 'orderPerClient' which will contain the number of orders placed by each customer. Save this script as view_creation.sql. [1.5] 5) Create a new folder named assignment1_yourID. Put in this folder all the scripts created in the above questions. Compress the folder then submit it via Blackboard on or before 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, customerName, address, city, category, custBalance) Product (productNO, label, price, QStock) customer Order (orderNO, orderDate, #customerNO) ordered Product (#orderNO, #productNO, orderQuantity) Note: Primary keys are underlined and foreign key are marked with #. Use SQL Developer to answer the questions given below: 1) Open a new SQL worksheet then write the SQL commands to create the tables' structures of the above database (enforce primary key and foreign key constraints). Save this script Schema_creation.sql.[2.5] Customer customerNO char(8) customerName Varchar (20) address Varchar (40) city Varchar(20) Char(2) Decimal(9,2) category custBalance Product productNO char(10) label price Decimal(9,2) QStock Number(11) Varchar (30) customer Order orderNO char(8) orderDate Date customerNO char(8) ordered Product orderNO char(8) productNO char(10) orderQuantity Number(11) 2) Open a new SQL worksheet then write the INSERT commands necessary to place the following data in the tables that were created in the previous question. Save this script as table population.sql. [2.5] customer NO customerName Ahmed Salim Alia B062 B112 B332 B512 C003 C123 C400 Taoufiq Islam Murad Jamilah Product Customer address 72 street Freedom Muscat Street Sultan Qaboos Building Hana Street Alhisn Ibri Building Alaamra Rustaq BI Salalah BI House N 5 Street Asahafa Building Asalalm Main Street Ibri Street of garden House n 5 Muscat CI B2 productNO label 2690 450 CS262 Chev.fir 200x6x2 CS264 Chev.fir 200x6x4 120 CS464 Chev.fir 400x6x4 220 PA45 Steel point 45 (1K) 105 PA60 PH222 PL.Hetre 200x20x2 230 PS222 PL.fir 200x20x2 185 1220 580 Steel point 60 (1K) 95 134 782 Price QStock 75 45 city Category custBalance Salalah B2 -100 1250 0.00 30179 PA60 30182 PA60 30184 CS464 30184 PA45 30185 CS464 30185 PA60 30186 PS222 30186 PA45 30188 PA60 30188 PH222 ordered Product orderNO productNO orderQuantity 30178 CS464 25 30179 CS262 60 20 30 120 20 260 15 600 3 180 92 CI B2 customer Order order NO order Date customerNO 30178 21-DEC-2017 C400 30179 22-DEC-2017 B512 30182 23-DEC-2017 C400 30184 23-DEC-2017 B062 30185 02-JAN-2018 B332 30186 02-JAN-2018 C400 30188 03-JAN-2018 C003 -500 -1000 -2000 350 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 C1 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] f) 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 C1 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, orderDate, 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] o) Display numbers of products (productNO) which are made of fir and are placed in orders. [1] p) Find for each product the list of cities in which this product is ordered in more than 500 units.[1.5] q) List, for each customer category, the number of products that were ordered on 23-12- 2008.[1.5] 4) Open a new SQL worksheet then write a SQL statement to create a view named 'orderPerClient' which will contain the number of orders placed by each customer. Save this script as view_creation.sql. [1.5] 5) Create a new folder named assignment1_yourID. Put in this folder all the scripts created in the above questions. Compress the folder then submit it via Blackboard on or before
Expert Answer:
Related Book For
Posted Date:
Students also viewed these accounting questions
-
A transformer is used by a European traveler while she is visiting the United States. She primarily uses it to run a 1200-watt hair dryer she brought with her. When the hair dryer is plugged in to...
-
The circuit in Fig. 7.150 is used by a biology student to study "frog kick." She noticed that the frog kicked a little when the switch was closed but kicked violently for 5 s when the switch was...
-
A program is used by a company that employs 20 workers. The program keeps the following details for each employee of the company: Surname, first name, salary, employee number and age. The details of...
-
To load and register a JDBC driver, two popular methods can be used: either using the Class.forName() method or creating a new instance of the Driver class.(True/False)
-
Identify a specific situation in which periodic processing is sufficient to support the business process, and a second situation where immediate processing is necessary to adequately support the...
-
What role does intelligent transportation systems (ITS) play in improving urban traffic flow and reducing congestion? Discuss the challenges and potential benefits of implementing ITS in major...
-
What alternative procedures are available to the auditor when he is unable to observe (a) the ending inventory and (b) the beginning inventory?
-
Chicagos Hard Rock Hotel distributes a mean of 1,000 bath towels per day to guests at t he pool and in their rooms. This demand is normally distributed with a standard deviation of 100 towels per...
-
1. [20 marks] Neatly work out the following bitwise operations. Show the work step by step. Give the answers in binary, hexadecimal and decimal. a. unsigned char Question1A = (0xDB & 0x69) | 17; b....
-
An object of mass m1 on a frictionless horizontal table is connected to an object of mass m2 through a very light pulley P1 and a light fixed pulley P2 as shown in Figure P5.34. (a) If a1 and a2 are...
-
Assuming 100% dissociation, calculate the freezing point (T) and boiling point (Tb) of 1.81 m CaCl, (aq). Colligative constants can be found in the chempendix. T = T = Incorrect Incorrect C C
-
Indicate whether each of the following statements is true or false by writing T or F in the answer column. Acts of an employee while performing duties of employment are considered the acts of the...
-
A person who is of legal age and normal mentality. a. competent pa rty b. consideration c. divisible c ontract d. entire, or indivisible, contract e. executed c ontract f. executory c ontract g....
-
You are considering opening your own photography studio specializing in weddings and other events. You think that there is a 40% probability of your business being successful, at which point you will...
-
The table contains price and quantity information for two vehicle models produced by Ford Motor Company, the F-series trucks and Escape SUVs. a. Calculate the amount contributed by Ford to U.S. GDP...
-
An agreement with a person who is required to have, but lacks, the approval of the state to practice his or her business, profession, or o ccupation. a. champerty b. franchisee c. franchisor d....
-
You will need to adapt the configuration on both Newton and Einstein and configure the Heisenberg and Schrodinger switches to complete this task. You will need to VLSM the network, to ensure that...
-
A report from the college dean indicates that for the previous semester, the grade distribution for the Department of Psychology included 135 As, 158 Bs, 140 Cs, 94 Ds, and 53 Fs. Determine what kind...
-
(a) Estimate the activity due to the 40K in a gallon of whole milk. Typically, a gallon contains about 6.4 g of potassium, which includes all the isotopes of potassium that exist naturally. Assume...
-
Locate the center of mass of the system shown in Fig. 6.39 (a) If all of the masses are equal; (b) If m2 = m4 = 2m1 = 2m3; (c) If m1 = 1.0 kg, m2 = 2.0 kg. m3 = 3.0 kg and m4 = 4.0 kg. (0, 40 m (4.0...
-
An electron travels at a speed of 2.0 x 104 m/s through a uniform magnetic field whose magnitude is 1.2 x 10-3 T. What is the magnitude of the magnetic force on the electron if its velocity and the...
-
a. Assume that \(y_{1}, \ldots, y_{n}\) are i.i.d. with a negative binomial distribution with parameters \(r\) and \(p\). Determine the maximum likelihood estimators. b. Use the sampling mechanism in...
-
For the data in Table 12.1, confirm that the Pearson statistic in equation (12.3) is 41.98 . Table 12.1 (12.3) Count Observed (j) (nj) Fitted Counts Using the Poisson Distribution (np;) 01234 6,996...
-
Consider a Poisson regression. Let \(e_{i}=y_{i}-\widehat{\mu}_{i}\) denote the \(i\) th ordinary residual. Assume that an intercept is used in the model so that one of the explanatory variables...
Study smarter with the SolutionInn App