Consider the following database schema for Greensboro Shop Items(itemID string,
ID: 3702066 • Letter: C
Question
Consider the following database schema for Greensboro Shop Items(itemID string, name string, description string, gtvInStock 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(empID string, firstName string, lastName string, ssn string, telNumber string, salary real) Carries(deptID string, itemID string) SupplielitemlD string, supID string, price real) Includes(itemlD string, ordD string, qty integer) Worksln(deptlD string, emplD string) 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." 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.Explanation / Answer
1. select itemID from carries where deptID in (select deptID,name form Departments where name = "Teen Clothing" or name="Junior Clothing")
2. select name from (select itemId,name,qtyInStock,sold,price from Items as i join Supplie as s on i.itemId=s.itemId join (select itemId,sum(qty) as sold from Includes groupby itemID) as n on i.itemID=n.itemI0D) where (price/qtyInStock) < 0.4*(price/sold);
// description of 2nd queiry . first it will get total sold quantity of each item from includes table and their stock and their price from supplie table . Than we'll check whether the sold cost is greater than 40% of unit cost....... print their item names..
3.select itemID from Items where itemID in (select itemID,name from Departments as d join Carries as c on d.deptID=c.deptID where d.name="Junior Clothing" and d.name<>"Ggirl's Teen Clothing");
4.select name from Departments where deptID in (select max(cout),deptID from (select deptID,count(*) as cout from Departments as d join Carries as c on d.deptID=c.deptID groupby deptID))
// understand the quieries and the answers which u have provided are not relatively join so try to execute these quiries in ur machine. Beware of nomenclature..... Thanks in advance....