This is an extra credit assignment, which is designed to let you have a hands-on

Question

This is an extra credit assignment, which is designed to let you have a hands-on experience writing advanced SQI query statements - subqueries and complex joins Subqueries: one way to nest or a cascade query is to stick a query in the where clause, find the names of employees worked at the department Research. This is a powerful way to take advantage of the fact that any SQL query returns a table, which can then be the starting point of another SQL query Complex Joins: the 'where' clause can become quite complex with many joins and related 'and' and 'or' conditions » » Consider the following database schema for Greensboro Shop Items(itemID string, name string, description string, gtyInStock integer) Departments(deptID string, name string, location string, telNumber string, budget real, manager string) Suppliers(supID string, name string, address string, city string, state string) Orders(ordID string.ordDate date, shipAddress string) Employees (emplD string, firstNaming, lastName string, ssn string, telNumber string, salary real) Carries(deptID string, itemDstring) Supplie(itemID string,supD string, price real) Includes(itemlD string, ordID string, qty integer) WorksIn(deptID string, empID string) In this assignment, your task is to write SQL queries that answer the following questions based on the database GShop in the ANSI-style JOIN. Each question should be answered by one SQL statement only. The result of each statement must not contain duplicates, but you should use the SQL keyword distinct only when necessary. For clarity, each output column should be properly renamed. For example, the output column of name from the table departments may be renamed as "Department Name." Please put your SQL query statements and their results into a SQL script file. Run your SQL statements on the MySQL Database Server. Make sure that your SQL script file does not contain any syntax errors before submitting to the Assignment Link - Extra Credit 1. Find the ids of items that are carried by Junior Clothing and Girl's Teen Clothing. Note that Girl's Teen Clothing and Junior Clothing are department names. You must use a nested query with two SELECT statements 2. Find the name of each item that its average sale price is 40% more than its average unit cost. 3. Find the name of items that are carried by Junior Clothing but not Girl's Teen Clothing. Note that Girl's Teen Clothing and Junior Clothing are department names. You must use a nested query with three SELECT statements

Explanation / Answer

QUERIES:

Q1:
select ItemId 'Item ID' from (select c.ItemId,d.DeptId from dept d,carries c where d.name='d1' and d.name='d2' and c.DeptId=d.DeptId) item_alias;

Q2: Not clear what is average sale price and average unit cost. (please mention)/

Q3:
select name 'Item Name' from (select c.itemid,d.deptid,i.name from dept d,carries c,items i where d.name='d1' and d.name!='d2' and c.deptid=d.deptid and c.itemid=i.itemid)item_alias;

Q4:

select name 'Department Name' from (select name,max(most_count) from (select d.deptid,d.deptname,count(c.itemid) 'most_count' from dept d,carries c where c.deptid=d.deptid group by deptid) getmax) deptname;

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

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