SQL Problem Solution 1 to 50

Site :  http://www.sql-ex.com
res-
https://github.com/anyinlover/anyinlover.github.io/BLOB/master/_posts/2016-6-22-sql-exercise.md

1. Find the model NUMBER, speed AND hard drive capacity FOR ALL the PCs WITH prices below $500.RESULT SET: model, speed, hd.

   ~~~SQL
   SELECT model, speed, hd
   FROM PC
   WHERE price<500;
   ~~~

2. List ALL printer makers. RESULT SET: maker.

   ~~~SQL
   SELECT DISTINCT maker
   FROM Product
   WHERE TYPE='Printer';
   ~~~

3. Find the model NUMBER, RAM AND screen SIZE OF the laptops WITH prices OVER $1000.

   ~~~SQL
   SELECT model, ram, screen
   FROM Laptop
   WHERE price>1000;
   ~~~

4. Find ALL records FROM the Printer TABLE containing DATA about color printers.

   ~~~SQL
   SELECT *
   FROM Printer
   WHERE color='y';
   ~~~

5. Find the model NUMBER, speed AND hard drive capacity OF PCs cheaper than $600 HAVING a 12x OR a 24x CD drive.

   ~~~SQL
   SELECT model, speed, hd
   FROM PC
   WHERE price<600
   AND cd IN ('12x','24x');
   ~~~

6. FOR each maker producing laptops WITH a hard drive capacity OF 10 Gb OR higher, find the speed OF such laptops. RESULT SET: maker, speed.

   ~~~SQL
   SELECT DISTINCT maker, speed
   FROM Product, Laptop
   WHERE Product.model = Laptop.model
   AND hd >= 10;
   ~~~

7. Find OUT the models AND prices FOR ALL the products (OF any TYPE) produced BY maker B.

   ~~~SQL
   SELECT Product.model, price
   FROM Product, PC
   WHERE Product.model = PC.model
   AND maker='B'
   UNION
   SELECT Product.model, price
   FROM Product, Laptop
   WHERE Product.model = Laptop.model
   AND maker='B'
   UNION
   SELECT Product.model, price
   FROM Product, Printer
   WHERE Product.model = Printer.model
   AND maker='B';
   ~~~

8. Find the makers producing PCs but NOT laptops.

   ~~~SQL
   SELECT DISTINCT maker
   FROM Product
   WHERE TYPE='PC'
   AND maker NOT IN
   (SELECT DISTINCT maker
   FROM Product
   WHERE TYPE='Laptop')
   ~~~

9. Find the makers OF PCs WITH a processor speed OF 450 MHz OR more. RESULT SET: maker.

   ~~~SQL
   SELECT DISTINCT maker
   FROM Product, PC
   WHERE Product.model = PC.model
   AND speed >= 450;
   ~~~

10. Find the printer models HAVING the highest price. RESULT SET: model, price.

    ~~~SQL
    SELECT model, price
    FROM Printer
    WHERE price =
    (SELECT MAX(price)
    FROM Printer);
    ~~~

11. Find OUT the average speed OF PCs.

    ~~~SQL
    SELECT avg(speed)
    FROM PC;
    ~~~

12. Find OUT the average speed OF the laptops priced OVER $1000.

    ~~~SQL
    SELECT avg(speed)
    FROM Laptop
    WHERE price > 1000;
    ~~~

13. Find OUT the average speed OF the PCs produced BY maker A.

    ~~~SQL
    SELECT avg(speed)
    FROM Product, PC
    WHERE Product.model = PC.model
    AND maker='A';
    ~~~

14. GET the makers who produce ONLY one product TYPE AND more than one model. Output: maker, TYPE.

    ~~~SQL
    SELECT DISTINCT maker, TYPE
    FROM Product
    WHERE maker IN
    (SELECT maker
    FROM Product
    GROUP BY maker
    HAVING COUNT(DISTINCT TYPE)=1
    AND COUNT(DISTINCT model)>1);
    ~~~

15. GET hard drive capacities that are identical FOR two OR more PCs. RESULT SET: hd.

    ~~~SQL
    SELECT hd
    FROM PC
    GROUP BY hd
    HAVING COUNT(*) > 1
    ~~~

16. GET pairs OF PC models WITH identical speeds AND the same RAM capacity. Each resulting pair should be displayed ONLY once, i.e. (i, j) but NOT (j, i). RESULT SET: model WITH the bigger NUMBER, model WITH the smaller NUMBER, speed, AND RAM.

    ~~~SQL
    SELECT DISTINCT p1.model, p2.model, p1.speed, p1.ram
    FROM PC p1, PC p2
    WHERE p1.model > p2.model
    AND p1.speed = p2.speed
    AND p1.ram = p2.ram;
    ~~~

17. GET the laptop models that have a speed smaller than the speed OF any PC. RESULT SET: TYPE, model, speed.

    ~~~SQL
    SELECT DISTINCT TYPE, Product.model, speed
    FROM Product, Laptop
    WHERE Product.model = Laptop.model
    AND speed <
    (SELECT MIN(speed)
    FROM PC);
    ~~~

18. Find the makers OF the cheapest color printers.RESULT SET: maker, price.

    ~~~SQL
    SELECT DISTINCT maker, price
    FROM Printer, Product
    WHERE Product.model=Printer.model
    AND color='y'
    AND price=
    (SELECT MIN(price)
    FROM Printer
    WHERE color='y');
    ~~~

19. FOR each maker HAVING models IN the Laptop TABLE, find OUT the average screen SIZE OF the laptops he produces. RESULT SET: maker, average screen SIZE.

    ~~~SQL
    SELECT maker, avg(screen)
    FROM Product, Laptop
    WHERE Product.model = Laptop.model
    GROUP BY maker;
    ~~~

20. Find the makers producing at least three DISTINCT models OF PCs. RESULT SET: maker, NUMBER OF PC models.

    ~~~SQL
    SELECT maker, COUNT(model)
    FROM Product
    WHERE TYPE='PC'
    GROUP BY maker
    HAVING COUNT(model) >= 3;
    ~~~

21. Find OUT the maximum PC price FOR each maker HAVING models IN the PC TABLE. RESULT SET: maker, maximum price.

    ~~~SQL
    SELECT maker, MAX(price)
    FROM Product, PC
    WHERE Product.model = PC.model
    GROUP BY maker;
    ~~~

22. FOR each VALUE OF PC speed that exceeds 600 MHz, find OUT the average price OF PCs WITH identical speeds. RESULT SET: speed, average price.

    ~~~SQL
    SELECT speed, avg(price)
    FROM PC
    WHERE speed > 600
    GROUP BY speed;
    ~~~

23. GET the makers producing BOTH PCs HAVING a speed OF 750 MHz OR higher AND laptops WITH a speed OF 750 MHz OR higher. RESULT SET: maker

    ~~~SQL
    SELECT DISTINCT maker
    FROM Product, PC
    WHERE Product.model = PC.model
    AND speed >= 750
    AND maker IN
    (SELECT DISTINCT maker
    FROM Product, Laptop
    WHERE Product.model = Laptop.model
    AND speed >= 750)
    ~~~

14. List the models OF any TYPE HAVING the highest price OF ALL products present IN the DATABASE.

    ~~~SQL
    SELECT DISTINCT model
    FROM
    (SELECT model, price
    FROM PC
    UNION ALL
    SELECT model, price
    FROM Laptop
    UNION ALL
    SELECT model, price
    FROM Printer) AS union_model
    WHERE price >= ALL
    (SELECT price FROM (
    SELECT price FROM PC
    UNION ALL
    SELECT price FROM Laptop
    UNION ALL
    SELECT price FROM Printer) AS union_price);
    ~~~

15. Find the printer makers also producing PCs WITH the lowest RAM capacity AND the highest processor speed OF ALL PCs HAVING the lowest RAM capacity. RESULT SET: maker.

    ~~~SQL
    SELECT DISTINCT maker
    FROM Product, PC
    WHERE Product.model = PC.model
    AND ram =
    (SELECT MIN(ram)
    FROM PC)
    AND speed =
    (SELECT MAX(speed)
    FROM PC
    WHERE ram =
    (SELECT MIN(ram)
    FROM PC))
    AND maker IN
    (SELECT maker
    FROM Product
    WHERE TYPE='Printer');
    ~~~

16. Find OUT the average price OF PCs AND laptops produced BY maker A. RESULT SET: one overall average price FOR ALL items.

    ~~~SQL
    SELECT avg(price)
    FROM
    (SELECT price
    FROM Product, PC
    WHERE Product.model = PC.model
    AND maker = 'A'
    UNION ALL
    SELECT price
    FROM Product, Laptop
    WHERE Product.model = Laptop.model
    AND maker='A'
    ) AS union_price;
    ~~~

17. Find OUT the average hard disk drive capacity OF PCs produced BY makers who also manufacture printers.
    RESULT SET: maker, average HDD capacity.

    ~~~SQL
    SELECT maker, avg(hd)
    FROM Product, PC
    WHERE Product.model = PC.model
    AND maker IN
    (SELECT DISTINCT maker
    FROM Product
    WHERE TYPE='printer')
    GROUP BY maker;
    ~~~

18. Determine the average quantity OF paint per square WITH an accuracy OF two DECIMAL places.

    ~~~SQL
    SELECT
    round(SUM(ifnull(B_VOL,0))/COUNT(DISTINCT Q_ID),2)
    FROM utB RIGHT JOIN utQ
    ON utB.B_Q_ID = utQ.Q_ID;
    ~~~

19. UNDER the assumption that receipts OF money (inc) AND payouts (OUT) are registered NOT more than once a DAY FOR each collection point [i.e. the PRIMARY KEY consists OF (point, DATE)], WRITE a query displaying cash flow DATA (point, DATE, income, expense).
    USE Income_o AND Outcome_o TABLES.

    ~~~SQL
    SELECT i.point, i.DATE, i.inc, o.OUT
    FROM Income_o i LEFT JOIN Outcome_o o
    ON i.point = o.point
    AND i.DATE = o.DATE
    UNION
    SELECT o.point, o.DATE, i.inc, o.OUT
    FROM Outcome_o o LEFT JOIN Income_o i
    ON o.point = i.point
    AND o.DATE = i.DATE
    ~~~

10. UNDER the assumption that receipts OF money (inc) AND payouts (OUT) can be registered any NUMBER OF times a DAY FOR each collection point [i.e. the code COLUMN IS the PRIMARY KEY], display a TABLE WITH one corresponding ROW FOR each operating DATE OF each collection point.
    RESULT SET: point, DATE, total payout per DAY (OUT), total money intake per DAY (inc).
    Missing VALUES are considered TO be NULL.

    ~~~SQL
    SELECT i.point, i.DATE, o.OUT, i.inc
    FROM
    (SELECT point, DATE, SUM(inc) AS inc
    FROM Income
    GROUP BY point, DATE) i
    LEFT JOIN
    (SELECT point, DATE, SUM(OUT) AS OUT
    FROM Outcome
    GROUP BY point, DATE) o
    ON i.point = o.point
    AND i.DATE = o.DATE
    UNION
    SELECT o.point, o.DATE, o.OUT, i.inc
    FROM
    (SELECT point, DATE, SUM(OUT) AS OUT
    FROM Outcome
    GROUP BY point, DATE) o
    LEFT JOIN
    (SELECT point, DATE, SUM(inc) AS inc
    FROM Income
    GROUP BY point, DATE) i
    ON o.point = i.point
    AND o.DATE = i.DATE;
    ~~~

11. FOR ship classes WITH a gun caliber OF 16 IN. OR more, display the class AND the country.

    ~~~SQL
    SELECT class, country
    FROM Classes
    WHERE bore >= 16;
    ~~~

12. One OF the characteristics OF a ship IS one-half the cube OF the calibre OF its main guns (mw).
    Determine the average.

    ~~~SQL
    SELECT country, round(avg(POWER(bore,3)*0.5),2)
    FROM
    (SELECT country, bore, name
    FROM Classes, Ships
    WHERE Classes.class = Ships.class
    UNION
    SELECT country, bore, ship
    FROM Classes, Outcomes
    WHERE Classes.class = Outcomes.ship
    ) AS new_ships
    GROUP BY country;
    ~~~

13. GET the ships sunk IN the North Atlantic battle.
    RESULT SET: ship.

    ~~~SQL
    SELECT ship
    FROM Outcomes
    WHERE battle = 'North Atlantic'
    AND RESULT = 'sunk';
    ~~~

14. IN accordance WITH the Washington Naval Treaty concluded IN the beginning OF 1922, it was prohibited TO build battle ships WITH a displacement OF more than 35 thousand tons.
    GET the ships violating this treaty (ONLY consider ships FOR which the YEAR OF launch IS known).
    List the names OF the ships.

    ~~~SQL
    SELECT DISTINCT name
    FROM Ships, Classes
    WHERE Ships.class = Classes.class
    AND TYPE = 'bb'
    AND displacement > 35000
    AND launched >= 1922;
    ~~~

15. Find models IN the Product TABLE consisting either OF digits ONLY OR Latin letters (A-Z, CASE insensitive) ONLY.
    RESULT SET: model, TYPE.

    ~~~SQL
    SELECT model, TYPE
    FROM Product
    WHERE model REGEXP '^(([0-9]+)|([a-zA-Z]+))$';
    ~~~

16. List the names OF lead ships IN the DATABASE (including the Outcomes TABLE).

    ~~~SQL
    SELECT name
    FROM Ships
    WHERE name IN
    (SELECT class
    FROM Classes)
    UNION
    SELECT ship
    FROM Outcomes
    WHERE ship IN
    (SELECT class
    FROM Classes);
    ~~~

17. Find classes FOR which ONLY one ship EXISTS IN the DATABASE (including the Outcomes TABLE).

    ~~~SQL
    SELECT class
    FROM
    (SELECT Classes.class, name
    FROM Classes, Ships
    WHERE Classes.class = Ships.class
    UNION
    SELECT class, ship AS name
    FROM Classes, Outcomes
    WHERE Classes.class = Outcomes.ship
    ) AS full_ship
    GROUP BY class
    HAVING COUNT(*) = 1;
    ~~~

18. Find countries that ever had classes OF BOTH battleships (‘bb’) AND cruisers (‘bc’).

    ~~~SQL
    SELECT DISTINCT country
    FROM Classes
    WHERE TYPE='bb'
    AND country IN
    (SELECT DISTINCT country
    FROM Classes
    WHERE TYPE='bc');
    ~~~

19. Find the ships that "survived for future battles"; that IS, after being damaged IN a battle, they participated IN another one, which occurred later.

    ~~~SQL
    SELECT DISTINCT o2.ship FROM
    (SELECT ship, battle, RESULT, DATE
    FROM Outcomes, Battles
    WHERE Outcomes.battle = Battles.name
    AND RESULT='damaged'
    ) AS o1,
    (SELECT ship, battle, RESULT, DATE
    FROM Outcomes, Battles
    WHERE Outcomes.battle = Battles.name
    ) AS o2
    WHERE o1.ship = o2.ship
    AND o1.DATE < o2.DATE;
    ~~~

10. FOR the ships IN the Ships TABLE that have at least 10 guns, GET the class, name, AND country.

    ~~~SQL
    SELECT Ships.class, name, country
    FROM Ships LEFT JOIN Classes
    ON Ships.class = Classes.class
    WHERE numGuns >= 10;
    ~~~

11. FOR the PC IN the PC TABLE WITH the maximum code VALUE, obtain ALL its characteristics (EXCEPT FOR the code) AND display them IN two COLUMNS: name OF the characteristic (title OF the corresponding COLUMN IN the PC TABLE);its respective VALUE.

    ~~~SQL
    SELECT 'model', model
    FROM PC
    WHERE code=
    (SELECT MAX(code)
    FROM PC)
    UNION
    SELECT 'speed', speed
    FROM PC
    WHERE code=
    (SELECT MAX(code)
    FROM PC)
    UNION
    SELECT 'ram', ram
    FROM PC
    WHERE code=
    (SELECT MAX(code)
    FROM PC)
    UNION
    SELECT 'hd', hd
    FROM PC
    WHERE code=
    (SELECT MAX(code)
    FROM PC)
    UNION
    SELECT 'cd', cd
    FROM PC
    WHERE code=
    (SELECT MAX(code)
    FROM PC)
    UNION
    SELECT 'price', price
    FROM PC
    WHERE code=
    (SELECT MAX(code)
    FROM PC)
    ~~~

12. Find the names OF ships sunk at battles, along WITH the names OF the corresponding battles.

    ~~~SQL
    SELECT ship, battle
    FROM Outcomes
    WHERE RESULT='sunk';
    ~~~

13. GET the battles that occurred IN years WHEN no ships were launched INTO water.

    ~~~SQL
    SELECT name
    FROM Battles
    WHERE YEAR(DATE)
    NOT IN
    (SELECT launched
    FROM Ships
    WHERE launched IS NOT NULL);
    ~~~

14. Find ALL ship names beginning WITH the letter R.

    ~~~SQL
    SELECT name
    FROM Ships
    WHERE name LIKE 'R%'
    UNION
    SELECT ship
    FROM Outcomes
    WHERE ship LIKE 'R%';
    ~~~

15. Find ALL ship names consisting OF three OR more words (e.g., King George V).
    Consider the words IN ship names TO be separated BY single spaces, AND the ship names TO have no LEADING OR TRAILING spaces.

    ~~~SQL
    SELECT name
    FROM Ships
    WHERE name LIKE '% % %'
    UNION
    SELECT ship
    FROM Outcomes
    WHERE ship LIKE '% % %';
    ~~~

16. FOR each ship that participated IN the Battle OF Guadalcanal, GET its name, displacement, AND the NUMBER OF guns.

    ~~~SQL
    SELECT DISTINCT ship, displacement, numguns
    FROM Classes LEFT JOIN Ships
    ON classes.class=ships.class
    RIGHT JOIN Outcomes
    ON Classes.class=ship
    OR ships.name=ship
    WHERE battle='Guadalcanal';
    ~~~

1.  NUMBER the ROWS OF the Product TABLE AS follows: makers IN descending ORDER OF NUMBER OF models produced BY them (FOR manufacturers producing an equal NUMBER OF models, their names are sorted IN ascending alphabetical ORDER); model numbers IN ascending ORDER.
    RESULT SET: ROW NUMBER AS described above, manufacturer's name (maker), model.

    ~~~sql
    select count(*) num, t1.maker, t1.model
    from (
    select maker, model, c
    from Product
    join (
    select count(model) c, maker m
    from Product
    group by maker ) b1
    on maker = m) t1
    join
    (select maker, model, c
    from Product
    join (
    select count(model) c, maker m
    from Product
    group by maker ) b2
    on maker = m) t2
    on t2.c > t1.c
    or t2.c=t1.c and t2.maker<t1.maker
    or t2.c=t1.c and t2.maker=t1.maker and t2.model <= t1.model
    group by t1.maker, t1.model
    order by 1;
    ~~~

2.  Find the ship classes having at least one ship sunk in battles.

    ~~~sql
    select distinct Classes.class
    from Classes, Ships, Outcomes
    where Classes.class = Ships.class
    and Ships.name = Outcomes.ship
    and Outcomes.result = 'sunk'
    union
    select distinct class
    from Classes, Outcomes
    where Classes.class = Outcomes.ship
    and Outcomes.result = 'sunk';
    ~~~

3.  Find the names of the ships having a gun caliber of 16 inches (including ships in the Outcomes table).

    ~~~sql
    select name
    from Ships, Classes
    where Ships.class = Classes.class
    and bore = 16
    union
    select ship
    from Outcomes, Classes
    where Outcomes.ship = Classes.class
    and bore = 16;
    ~~~

4.  Find the battles in which Kongo-class ships from the Ships table were engaged.

    ~~~sql
    select battle
    from Outcomes, Ships
    where Outcomes.ship = Ships.name
    and Ships.class = 'Kongo';
    ~~~