1
\$\begingroup\$

Good day, I am a college student and lately we had an assignment where we were tasked to write SQL commands on various questions depending on the information that is needed to be displayed from 3 different tables. Here are the instructions and a sample question with a provided solution:

Assignment Instruction

Table

And below are my answers:

"Question 1: 1. Display information about parts that have their weights greater than the average weight of all parts.  "
SELECT      pNo, pName
FROM            p
WHERE           pWeight > (SELECT AVG(pWeight) FROM p);

"Question 2: 2. Find supplier numbers who supply any screws (i.e., such that the part name is ‘screw’).  The constraint is that you may not use any join or cross (product).  (Hint: Use subquery(ies).)"
SELECT      DISTINCT(sNo) 
FROM            sp
WHERE           pNo IN(SELECT pNo FROM p WHERE pName LIKE 'SCREW');

"Question 3: 3. Find supplier numbers of suppliers located in London who supply screws (i.e., such that the part name is ‘screw’) or bolts.  The constraint is that you may not use any join or product, but use a subquery(ies). (Hint: Use subquery(ies)."
SELECT      DISTINCT(s.sNo) 
FROM            s, p, sp
WHERE   sp.sNo = s.sNo AND sp.pNo = p.pNo AND sCity = 'LONDON' AND pName in('Screw', 'Bolt');           

"Question 4: 4. Display the total number of orders (over all parts) and the minimum, average, and maximum quantity of individual orders (each order is a tuple in sp). "
SELECT  COUNT(pNo), SUM(qty), MIN(qty), AVG(qty), MAX(qty), (SELECT 
AVG(qty) FROM SP) AVGqty
FROM            sp;

"Question 5: 5. For each part find the total, minimum, average, and maximum quantity of individual orders (each order is a tuple in p) and the total number of orders.  Display the part number and for that part: the number of orders, total, minimum, average, maximum quantity of individual orders for that part and also the average quantity on order over all parts.  Furthermore, display information in reverse order by part number. "
SELECT  pNo, COUNT(pNo), SUM(qty), MIN(qty), AVG(qty), MAX(qty), (SELECT AVG(qty) 
FROM SP) AVGqty
FROM            sp 
GROUP BY        pNo
ORDER BY        pNo DESC;

"Question 6: 6. For each part find the total, minimum, average, and maximum quantity of individual orders (each order is a tuple in p) and the total number of orders, but only for those parts for which the average quantity on order is greater than the average quantity on order over all parts.  Display the part number and for that part: the number of orders, total, minimum, average, maximum quantity of individual orders for that part and also the average quantity on order over all parts.  Furthermore, display information in reverse order by part number. "
SELECT  pNo, COUNT(pNo), SUM(qty), MIN(qty), AVG(qty), MAX(qty), (SELECT AVG(qty) 
FROM SP) AVGqty
FROM            sp
GROUP BY        pNo HAVING AVG(qty) > (SELECT AVG(qty) FROM sp)
ORDER BY        AVG(qty) DESC;

"Question 7: 7. Find each part for which its largest individual order in terms of quantity is larger than the largest quantity on order for either nut or a cam and the largest quantity for a nut and the largest quantity for a cam. "
SELECT  pNo, MAX(qty), (SELECT MAX(qty) FROM sp WHERE pNo IN (SELECT pNo FROM p WHERE pName = 'Nut' OR pName = 'Cam') ) AS MAXcamORnut
FROM            sp
GROUP BY        pno
HAVING      MAX(qty) > ALL (SELECT qty FROM sp WHERE pNo IN(SELECT pNo FROM p
WHERE pName = 'Nut' OR pName = 'Cam'));

What I would like to know is if there are any more ways to shorten each one of my answers for the 7 questions, preferably the shortest code possible. As a curious person, efficiency matters to me and it is ideal to use much shorter commands.

For example, I am most definitely sure that the code from #7 could be shortened:

SELECT  pNo, MAX(qty), (SELECT MAX(qty) FROM sp WHERE pNo IN (SELECT pNo FROM    
p WHERE pName = 'Nut' OR pName = 'Cam') ) AS MAXcamORnut
FROM            sp
GROUP BY        pno
HAVING      MAX(qty) > ALL (SELECT qty FROM sp WHERE pNo IN(SELECT pNo FROM p
WHERE pName = 'Nut' OR pName = 'Cam'));

Thank you guys in advanced. :)

\$\endgroup\$
  • 3
    \$\begingroup\$ Please post the code to be reviewed as text (as you did for number 7) rather than as screenshots. \$\endgroup\$ – 200_success Mar 15 at 3:41
  • \$\begingroup\$ Your image of text isn't very helpful. It can't be read aloud or copied into an editor, and it doesn't index very well. Please edit your post to incorporate the relevant text directly (preferably using copy+paste to avoid transcription errors). \$\endgroup\$ – Toby Speight Mar 15 at 11:31
  • \$\begingroup\$ I have made these changes. \$\endgroup\$ – X Lefora Mar 15 at 11:49
  • \$\begingroup\$ @XLefora Some of these questions are ambigious. Take the last one, what exactly is asked here? There is a larger than x,y,z of which y and z are subsets of x. These type of questions would make me furious on a test. \$\endgroup\$ – dfhwze Jun 5 at 16:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.