emp (last, first, city, state, dept) mgr (last, first, dept) parts(pno, pname, c

Question

emp (last, first, city, state, dept)
mgr (last, first, dept)
parts(pno, pname, color, weight, pcity)
projects(jno, jname, jcity)
spj (sno, pno, jno, qty)
suppliers(sno, sname, status, scity)

**Can only use Select, Where, Count, Min, Max, Sum, Avg, Group By, Order By, and Set Operations(Union, Intersect, Except), Exists, In and Temp Tables(if needed)
***No form of Join Command is allowed
****Using SQLiteStudio v2.1.4

1. Print only the part name (pname) and total quantity (qty) for all parts that have a totaled quantity greater than 500 and less than 1500, only print a single tuple for each part name. Order by part name.

2. Select all the fields from the projects table in the order of the schema where the city in which the project occurs is a city that has more than three parts produced in that city, order the results by jno.

3. Find the projects and parts from the same cities such that any project that does not have a matching part (that does not have a matching city) will still show up in the results. The result should contain all the fields from the projects table followed by all the fields from the parts table. Order the result by project number followed by part number.

Explanation / Answer

please let me know if you need further help.Thanks.

1.select P.pname ,S.qty from parts P,spj S
where P.pno=S.pno and S.qty between 500 and 1500
order by P.pname

2.select P.* from projects P,spj S,parts part
where P.jno=S.jno and part.pno=S.pno
group by part.pcity
having count(P.jcity) >3
order by P.jno

3.select P.*,part.* from projects P,spj S,parts part
where P.jno=S.jno and part.pno=S.pno
order by P.jno,part.pno

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

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