diff options
1 files changed, 39 insertions, 0 deletions
| -rw-r--r-- | Year 3/Databases/Practice05.sql | 39 |
diff --git a/Year 3/Databases/Practice05.sql b/Year 3/Databases/Practice05.sql new file mode 100644 index 0000000..cfa5f8c --- /dev/null +++ b/Year 3/Databases/Practice05.sql @@ -0,0 +1,39 @@ +-- PC +use PC; +-- Ex 1 +select AVG(speed) from pc; +-- Ex 2 +select p.maker, AVG(l.screen) from product p join laptop l where p.model = l.model group by p.maker; +-- Ex 3 +select AVG(l.price) from laptop l where l.price > 1000; +-- Ex 4 +select AVG(l.price) from laptop l join product p on p.model = l.model where p.maker = "A"; +-- Ex 5 +select AVG(l.price) from (select price, model from laptop union select price, model from pc) l join product p on p.model = l.model where p.maker = "B"; +-- Ex 6 +select pc.speed, AVG(pc.price) from pc group by pc.speed; +-- Ex 7 +select p.maker from product p join pc on pc.model = p.model group by p.maker having COUNT(*) >= 3; +-- Ex 8 +select p.maker from product p join pc on pc.model = p.model where pc.price = (select MAX(price) from pc) group by p.maker; +-- Ex 9 +select pc.speed, AVG(pc.price) from pc where pc.price > 800 group by pc.speed; +-- Ex 10 +select AVG(pc.hd) from pc join product p on p.model = pc.model where p.maker = ANY (select distinct maker from product where type = "Printer"); +-- Ex 11 +select distinct l.screen, ((select MAX(price) from laptop where screen = l.screen) - (select MIN(price) from laptop where screen = l.screen)) diff from laptop l; + +-- SHIPS +use SHIPS; +-- Ex 1 +select COUNT(*) from CLASSES; +-- Ex 2 +select AVG(c.NUMGUNS) from CLASSES c join SHIPS s on s.CLASS = c.CLASS; +-- Ex 3 +select c.CLASS, (select MIN(LAUNCHED) from SHIPS where CLASS = c.CLASS) earliest, (select MAX(LAUNCHED) from SHIPS where CLASS = c.CLASS) latest from CLASSES c; +-- Ex 4 +select c.CLASS, (select COUNT(*) from OUTCOMES o join SHIPS s on o.SHIP = s.NAME join CLASSES cl on cl.CLASS = s.CLASS where cl.CLASS = c.CLASS and o.RESULT = "sunk") sank from CLASSES c; +-- Ex 5 +select c.CLASS, (select COUNT(*) from OUTCOMES o join SHIPS s on o.SHIP = s.NAME join CLASSES cl on cl.CLASS = s.CLASS where cl.CLASS = c.CLASS and o.RESULT = "sunk" having COUNT(LAUNCHED) > 4) sank from CLASSES c; +-- Ex 6 +select c.COUNTRY, AVG(c.DISPLACEMENT) from CLASSES c group by c.COUNTRY; |