Write SQL queries and DML statements to do these tasks a. List the total value o

Question

Write SQL queries and DML statements to do these tasks

a. List the total value of all the “Land Before Time” movies on hand. Rename the column headings in the results table so that the report is easy to understand.

b. List order numbers of the orders with at least 3 different parts ordered.

c. List the names of all the employees and all the customers. (Hint: Think “’set theory’)

d. Find the names and cities of employees who have taken orders for parts costing more than $20.00.

e. Find the names of customers who have ordered parts from employees living in Wichita.

f. Find the names of parts along with the number of orders they appear in, sorted in decreasing order of the number of orders.

g. Find the names of customers who have placed the highest number of orders.

h. Decrease by 15 percent the prices of all parts that cost less than $20.00.

i. Change the street address of the customer with the customer number 3333 to 321 Fir Rd.

j. Add a customer, Mary, with no address or phone number to the customers table.

Qdetails Ono Pno 1020 105061 1020 105071 1020 10508 2 1020 10509 3 1021 10601 4 1022 10601 1 1022 10701 1023 10800 1 1023 10900 2 1023 10506 1 Parts Png 10506Land Before Time I 10507Land Before Time II 15619.99 20 10508Land Before Time III 19019.99 20 10509Land Before Time VI 20019.9920 10601 10701When Harry Met Sally 120 19.9930 10800 10900 Pname QOH Price Level 200 19.99 20 Sleeping Beauty 300 24.99 20 Dirty Harry Dr. Zhivago 140 14.99 30 100 24.9930 Emplovees Customers Cno Cname 1111 Charles123 Main St 67226 316-636-5555 2222 Bertram 237 Ash Ave. 67226 316-689-5555 3333Barbara 111 Inwood St. 60606 316-111-1234 Street ZIP Phone Eno Ename ZIP 1000 Jones67226 12-DEC-95 1001 Smith6060601-JAN-92 1002 Brown 50302 01-SEP-94 Zipcodes ZIP 67226Wichita 60606 Fort Dodge 50302 Kansas City 54444 Columbia 66002 Liberal 61111 Fort Hays Orders Ono CnoEno Reived Shipped 1020 111 1000 10-DEC-94 12-DEC-94 1022 2222 1001 13-FEB-95 20-FEB-95 1023 3333 1000 20-JUN-97 null

Explanation / Answer

Find the below set of queries for the above listed tasks. These queries are build based on the provided table schema.

Note: Make sure to changes the queries if there is any changes in the tables or attributes names compared to the actual DB before executing it on the server.

a. Aggregate function is used to calculate the total/average/min/max values. Below query with SUM aggregate function find the total values(i.e Price) of all the “Land Before Time” movies and also renamed the column headings in the results table as 'Total. Keyword AS is used to rename the field values)

Select Sum(Price) as Total from Parts Where Pname like 'Land Before Time%'

b.Below query List all the Order numbers of 'Land Before Time I', 'Sleeping Beauty' and 'Dirty Harry' products by fetching the details from Orderdetails and Parts
SELECT od.Ono,od.Pno
   FROM ODetails od
   INNNER JOIN Parts p
       ON od.Pno=p.Pno
   WHERE p.Pname IN ('Land Before Time I', 'Sleeping Beauty','Dirty Harry');
Note: changes the product name as per the needs)

c.The FULL OUTER JOIN keyword returns all the rows from the left table (Customers), and all the rows from the right table (Employees). If there are rows in "Customers" that do not have matches in "Employees", or if there are rows in "Employees" that do not have matches in "Customers", those rows will be listed as well.

SELECT e.Ename,c.Cname From Employees e FULL OUTER JOIN Customers c
ON e.Eno=c.Cno

d. Below query returns names and cities of employees who have taken orders for parts costing/price more than $20.00.
SELECT e.Ename,z.City
FROM Orders o
INNER JOIN ODetails od ON o.Ono=od.Ono
INNER JOIN Parts p ON p.Pno = od.Pno
INNER JOIN Employee e ON o.Eno=e.Eno
INNER JOIN Zipcodes z ON e.ZIP=z.ZIP
WHERE p.Price>20

e.Below query list out the names of customers who have ordered parts from employees living in Wichita.

SELECT c.Cname From Orders o
INNER JOIN Customer c
   ON c.Cno=o.Cno
INNER JOIN Employees e
   ON e.En0=o.Eno
INNER JOIN Zipcodes z
   ON e.Zip=z.ZIP
WHERE z.City IN ('Wichita')

f.Below query list out the name of parts along with order number , sorted order number (ono) in decreasing order.
SELECT e.Pname, od.Ono as OrderNumber
FROM Orders o
INNER JOIN ODetails od ON o.Ono=od.Ono
INNER JOIN Parts p ON p.Pno = od.Pno
ORDER od.Ono DESC

h. Update statement is used to change the value of multiple fields from the table.
below query decrease the value of price by 15 percent for all parts that cost less than $20.00.
UPDATE Parts SET Price = (Price - (Price * 15/100) WHERE Price <20

i. Change the street address of the customer with the customer number 3333 to 321 Fir Rd.
UPDATE Customers SET Street='321 Fir Rd.' WHERE Cno=3333

j. INSERT statement is used to insert a record in to a table. Below query add a customer, Mary, with no address or phone number to the customers table.
   Insert INTO Customers(Cname) Values('Mary'

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

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