3) Open a new SQL worksheet then write in SQL the queries, which answer the questions...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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] 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]
Expert Answer:
Answer rating: 100% (QA)
a Display in alphabetical order the list of cities in which there is at least one customer SELECT DISTINCT city FROM customers ORDER BY city ASC b Display the number name and city of customers who bel... View the full answer
Related Book For
Introduction to Statistical Investigations
ISBN: 978-1118922002
1st edition
Authors: Beth L.Chance, George W.Cobb, Allan J.Rossman Nathan Tintle, Todd Swanson Soma Roy
Posted Date:
Students also viewed these algorithms questions
-
To investigate whether there is an association between happiness and income level, we will use data from the 2002 General Social Survey (GSS), cross classifying a persons perceived happiness with...
-
To investigate whether there is an association between happiness and income level, we will use data from the 2002 General Social Survey (GSS), cross-classifying a persons perceived happiness with...
-
Design one of the following logic gates in static CMOS architecture using 50nm CMOS technology: 1. NAND3 2. NOR3 Simulate it. Use VDD as 1V. Please: 1. Take screenshots of the simulation. 2. Write...
-
Problem 5. A RRP Polar Robot is given by 1 (a) If the homogeneous transformation matrix is: T- Joint link length 11-0.5m, 12-0.25m, 0, 0, r (b) Find the Jacobian Matrix (c) Determine interior...
-
Omaha Synthetic Fibers Inc. specializes in the manufacture of synthetic fibers that the company uses in many products such as blankets, coats, and uniforms for police and firefighters. The company...
-
Akshay Limited uses the average cost formula in a perpetual inventory system. Fill in the missing amounts for items [1] to [13] in the following perpetual inventory schedule. (Use unrounded numbers...
-
Petitioner Curtis Flowers has been tried six separate times for the murder of four employees of a Mississippi furniture store. Flowers is black; three of the four victims were white. At the first two...
-
You have just been hired by FAB Corporation, the manufacturer of a revolutionary new garage door opening device. The president has asked that you review the companys costing system and do what you...
-
How Safe Are You? Assume one of your favorite activities is mountain climbing. When you go mountain climbing, you have several safety devices to keep you from falling. You notice that attached to one...
-
Daniel B. Butler and Freida C. Butler, husband and wife, file a joint return. The Butlers live at 625 Oak Street in Corbin, KY 40701. Dan's Social Security number is 111-11-1112, and Freida's is...
-
A 5 0 0 kg milk truck is moving west at 1 2 m / s . At the same time, a 6 kg tricycle is being driven at 6 5 deg N of E with a velocity of 1 5 m / s . The two vehicles collide and become fused...
-
You are working as a Bookkeeper in your training organisation. The Operations Manager of your training organisation has asked you to prepare a set of financial statements for the current financial...
-
If a customer requests a statement of a brokerage firm's financial condition, what document(s) must the brokerage firm send to the customer immediately?
-
Provide and example of a company's financial decision that could affect the three components of the accounting equation (Assets = Liabilities + Equity)? How might a decision impact assets,...
-
A company buys a $48 machine with a useful life of 3 years. For book purposes, the asset is depreciated using straight-line method. For tax purposes, it's depreciated using MACRS (Yr 1=50%, Yr 2=33%,...
-
A shoe store orders shoes from the manufacturer and sells them at a mall. Storing shoes at the store costs $7 per shoe pair for a year. When reordering shoes from the manufacturer, there is a fixed...
-
Life insurance is an example of Select answer from the options below diversification. speculation. mortality intermediation. risk pooling.
-
Is it ethical to provide safety training in English to immigrant workers who speak little English, in order to reduce costs?
-
Which of the following statements are true and which are false? a. In a paired design, each pair of observations always consists of measuring the same individual twice. b. In a paired design, each...
-
Based on your p-value and confidence interval, what conclusions can you draw from this test? There are several research questions we will ask in this investigation. The first one is: On average, is...
-
Ordinary dice have six equally likely sides. You can buy dice online with (A) 4 sides, (B) 6 sides, (C) 8 sides, or (D) 10 sides. Suppose you want to estimate the probability of rolling a 1 for each...
-
The figure below illustrates a thin plate of thickness \(t\). An approximate displacement field, which accounts for displacements due to the weight of the plate, is given by a. Determine the...
-
For a plane stress problem, the strain components in the xyplane at a point \(P\) are computed as: a. Compute the state of stress at this point if Young's modulus \(E=2 \times 10^{11} \mathrm{~Pa}\)...
-
The stress matrix at a particular point in a body is Determine the corresponding strain if \(E=20 \times 10^{10} \mathrm{~Pa}\) and \(v=0.3\). -2 1-3 [0] = 104 -345 107 Pa.
Study smarter with the SolutionInn App