2

So I've just started learning MySQL with a few exercises and I'm currently stuck at these 2 problems. Since this is technically an exercise I'd really appreciate hints instead of full solutions. Here are the 2 tables first:

CREATE TABLE LINE (
INV_NUMBER int,
LINE_NUMBER int,
P_CODE varchar(10),
LINE_UNITS float(8),
LINE_PRICE float(8),
LINE_TOTAL float(8)
);
INSERT INTO LINE VALUES('1001','1','13-Q2/P2','1','14.99','14.99');
INSERT INTO LINE VALUES('1001','2','23109-HB','1','9.95','9.95');
INSERT INTO LINE VALUES('1002','1','54778-2T','2','4.99','9.98');
INSERT INTO LINE VALUES('1003','1','2238/QPD','1','38.95','38.95');
INSERT INTO LINE VALUES('1003','2','1546-QQ2','1','39.95','39.95');
INSERT INTO LINE VALUES('1003','3','13-Q2/P2','5','14.99','74.95');
INSERT INTO LINE VALUES('1004','1','54778-2T','3','4.99','14.97');
INSERT INTO LINE VALUES('1004','2','23109-HB','2','9.95','19.90');
INSERT INTO LINE VALUES('1005','1','PVC23DRT','12','5.87','70.44');
INSERT INTO LINE VALUES('1006','1','SM-18277','3','6.99','20.97');
INSERT INTO LINE VALUES('1006','2','2232/QTY','1','109.92','109.92');
INSERT INTO LINE VALUES('1006','3','23109-HB','1','9.95','9.95');
INSERT INTO LINE VALUES('1006','4','89-WRE-Q','1','256.99','256.99');
INSERT INTO LINE VALUES('1007','1','13-Q2/P2','2','14.99','29.98');
INSERT INTO LINE VALUES('1007','2','54778-2T','1','4.99','4.99');
INSERT INTO LINE VALUES('1008','1','PVC23DRT','5','5.87','29.35');
INSERT INTO LINE VALUES('1008','2','WR3/TT3','3','119.95','359.85');
INSERT INTO LINE VALUES('1008','3','23109-HB','1','9.95','9.95');

CREATE TABLE PRODUCT (
P_CODE varchar(10),
P_DESCRIPT varchar(35),
P_INDATE date,
P_QOH int,
P_MIN int,
P_PRICE float(8),
P_DISCOUNT float(8),
V_CODE int
);
INSERT INTO PRODUCT VALUES('11QER/31','Power painter, 15 psi., 3- 
nozzle','2015-11-03','8','5','109.99','0','25595');
INSERT INTO PRODUCT VALUES('13-Q2/P2','7.25-in. pwr. saw blade','2015-12- 
13','32','15','14.99','0.05',NULL);
INSERT INTO PRODUCT VALUES('14-Q1/L3','9.00-in. pwr. saw blade','2015-11- 
13','18','12','17.49','0','21344');
INSERT INTO PRODUCT VALUES('1546-QQ2','Hrd. cloth, 1/4-in., 2x50','2016-01- 
15','15','8','39.95','0','23119');
INSERT INTO PRODUCT VALUES('1558-QW1','Hrd. cloth, 1/2-in., 3x50','2016-01- 
15','23','5','43.99','0','23119');
INSERT INTO PRODUCT VALUES('2232/QTY','B&D jigsaw, 12-in. blade','2015-12- 
30','8','5','109.92','0.05','24288');
INSERT INTO PRODUCT VALUES('2232/QWE','B&D jigsaw, 8-in. blade','2015-12- 
24','6','5','99.87','0.05','24288');
INSERT INTO PRODUCT VALUES('2238/QPD','B&D cordless drill, 1/2-in.','2016- 
01-20','12','5','38.95','0.05','25595');
INSERT INTO PRODUCT VALUES('23109-HB','Claw hammer','2016-01- 
12','23','10','9.95','0.1','21225');
INSERT INTO PRODUCT VALUES('23114-AA','Sledge hammer, 12 lb.','2016-01- 
2','8','5','14.40','0.05',NULL);
INSERT INTO PRODUCT VALUES('54778-2T','Rat-tail file, 1/8-in. fine','2015- 
12-15','43','20','4.99','0','21344');
INSERT INTO PRODUCT VALUES('89-WRE-Q','Hicut chain saw, 16 in.','2016-02- 
17','11','5','256.99','0.05','24288');
INSERT INTO PRODUCT VALUES('PVC23DRT','PVC pipe, 3.5-in., 8-ft','2016-02- 
27','188','75','5.87','0','24004');
INSERT INTO PRODUCT VALUES('SM-18277','1.25-in. metal screw, ''25','2016-03- 
01','172','75','6.99','0','21225');
INSERT INTO PRODUCT VALUES('SW-23116','2.5-in. wd. screw, 50','2016-02- 
14','237','100','8.45','0','21231');
INSERT INTO PRODUCT VALUES('WR3/TT3','Steel matting, 4''x8''x1/6", .5" 
mesh','2016-01-27','18','5','119.95','0.1','25595');

PKs are line.INV_NUMBER, line.LINE_NUMBER, product.P_CODE and FKs are line.P_CODE and line.V_CODE

So the first question is to list all product sales that are greater than the average units sold for that product and add a correlated in-line sub-query to list the average units sold per product. If I understood correctly this means that I need to sum the line.LINE_UNITS group by P_CODE then compare it to the average units sold but I keep getting the same error that says sub query returns more than 1 row. Here's my code:

select  P_CODE,
        LINE_UNITS, 
        ( SELECT  AVG(line.LINE_UNITS) ) as ‘Unit_Average’
    from  line
    where  
        ( SELECT  sum(line.LINE_UNITS)
            from  line
            group by  P_CODE ) >
        ( SELECT  AVG(line.LINE_UNITS) );

I think I need to do a join instead and I'd really appreciate some hints.

For the second question I am supposed to list the difference between each product's prices and the average product price. I managed to type up a few lines but I couldn't get it to execute for all rows of the product table. I'm thinking of using over() but im having quite a lot of syntax errors. Here's my code:

select  P_CODE, P_PRICE, 
        ( SELECT  avg(product.P_PRICE) ) as 'Average',
        if(product.P_PRICE <= 
              ( SELECT  avg(product.P_PRICE) ), 
              ( SELECT  avg(product.P_PRICE) ) - product.P_PRICE,
              product.P_PRICE - ( SELECT  avg(product.P_PRICE) )
        ) as 'Difference'
    from  product;

Thanks guys!

  • Other hints: Don't use FLOAT for money, nor for integral values ("units"). – Rick James Aug 18 '18 at 2:02
1

For your first question, I would start by figuring out what the average for each product are:

select p_code, avg(line_units) as line_units 
from LINE 
group by p_code;

SQL is closed in the sense that every result of a query is a table, so you can use the result just like an ordinary table. In other words, we can join this query with the original table:

select l.* 
from LINE as l 
join (
    select p_code, avg(line_units) as line_units 
    from LINE 
    group by p_code
) as avg_l 
    on l.p_code = avg_l.p_code 
   and l.line_units > avg_l.line_units;

Latest version of MySQL finally implements window functions from SQL99 which makes it a bit easier (untested)

select INV_NUMBER, LINE_NUMBER, P_CODE, LINE_UNITS
     , LINE_PRICE, LINE_TOTAL
from (
    select INV_NUMBER, LINE_NUMBER, P_CODE, LINE_UNITS
         , LINE_PRICE, LINE_TOTAL
         , AVG(LINE_UNITS) OVER (PARTITION BY P_CODE) AS AVG_LINE_UNITS
    from line
) AS T
WHERE LINE_UNITS > AVG_LINE_UNITS

I believe you should be able to solve question 2 using any of the above techniques. Let me know if you encounter any problems

0

Hints:

  • Think about what product.P_PRICE - ( SELECT avg(product.P_PRICE) ) gives you depending on which is larger.

  • Don't use FLOAT for money, nor for integral values ("units").

  • Not all quotes are equal.

  • You can't use aggregate functions in WHERE, but you can in HAVING.

  • Prefixing columns with the name of the table is clutter. If you are using a JOIN, you can (and should) use aliases for clarity: SELECT p.price, ... JOIN Products AS p

  • A SELECT usually needs a FROM.

  • Think about what you can do with SELECT @avg_price := avg(p.PRICE) FROM products AS p as a separate statement.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.