DBMS queries

SQL> @ e:/ASSIGNMENT3.sql
SQL> REM ASSIGNMENT 3
SQL>
SQL> REM DROPPING TABLES
SQL>
SQL> DROP TABLE outcomes;
Table dropped.
SQL> DROP TABLE ships;
Table dropped.
SQL> DROP TABLE battles;
Table dropped.
SQL> DROP TABLE classes;
Table dropped.
SQL>
SQL> REM CREATING TABLES
SQL>
SQL> CREATE TABLE classes
2 (
3 class VARCHAR2(15),
4 type VARCHAR2(5),
5 country VARCHAR2(15),
6 numGuns NUMBER(3),
7 bore NUMBER(3),
8 displacement NUMBER(7),
9 CONSTRAINT pk_class PRIMARY KEY(class),
10 CONSTRAINT ck_type CHECK(type in('bb','bc'))
11 );
Table created.
SQL>
SQL> CREATE TABLE ships
2 (
3 name VARCHAR2(15),
4 Class VARCHAR2(15),
5 launched NUMBER(5),
6 CONSTRAINT pk_ship PRIMARY KEY(name),
7 CONSTRAINT fk_class FOREIGN KEY(Class) REFERENCES
classes(class)
8 );
Table created.
SQL>
SQL> CREATE TABLE battles
2 (
3 name VARCHAR2(15),
4 battle_date DATE,
5 CONSTRAINT pk_battle PRIMARY KEY(name)
6 );
Table created.
SQL>
SQL> CREATE TABLE outcomes
2 (
3 ship VARCHAR2(15),
4 battle VARCHAR2(15),
5 result VARCHAR2(10),
6 CONSTRAINT pk_outcome PRIMARY KEY(ship,battle),
7 CONSTRAINT ck_result CHECK (result
in('sunk','damaged','ok')),
8 CONSTRAINT fk_ship FOREIGN KEY(ship) REFERENCES ships(name),
9 CONSTRAINT fk_battle FOREIGN KEY(battle) REFERENCES
battles(name)
10 );
Table created.
SQL>
SQL> REM Populating CLASSES relation
SQL> REM Classes(class,type,country,numGuns,bore,displacement)
SQL>
SQL> insert into classes
values('Bismark','bb','Germany',8,14,32000);
1 row created.
SQL> insert into classes values('Iowa','bb','USA',9,16,46000);
1 row created.
SQL> insert into classes values('Kongo','bc','Japan',8,15,42000);
1 row created.
SQL> insert into classes values('North
Carolina','bb','USA',9,16,37000);
1 row created.
SQL> insert into classes values('Renown','bc','Gt.
Britain',6,15,32000);
1 row created.
SQL> insert into classes values('Revenge','bb','Gt.
Britain',8,15,29000);
1 row created.
SQL> insert into classes
values('Tennessee','bb','USA',12,14,32000);
1 row created.
SQL> insert into classes values('Yamato','bb','Japan',9,18,65000);
1 row created.
SQL>
SQL>REM-----------------------------------------------------------
> REM Populating SHIPS relation - each class has different ships
SQL> REM ships(name,class,launched)
SQL>
SQL> insert into ships values('California','Tennessee',1921);
1 row created.
SQL> insert into ships values('Bismark','Bismark',1923);
1 row created.
SQL> insert into ships values('Haruna','Kongo',1915);
1 row created.
SQL> insert into ships values('Hiei','Kongo',1914);
1 row created.
SQL> insert into ships values('Iowa','Iowa',1943);
1 row created.
SQL> insert into ships values('Kirishima','Kongo',1915);
1 row created.
SQL> insert into ships values('Kongo','Kongo',1913);
1 row created.
SQL> insert into ships values('Missouri','Iowa',1944);
1 row created.
SQL> insert into ships values('Musashi','Yamato',1942);
1 row created.
SQL> insert into ships values('New Jersey','Iowa',1943);
1 row created.
SQL> insert into ships values('North Carolina','North
Carolina',1941);
1 row created.
SQL> insert into ships values('Ramillies','Revenge',1917);
1 row created.
SQL> insert into ships values('Renown','Renown',1916);
1 row created.
SQL> insert into ships values('Repulse','Renown',1916);
1 row created.
SQL> insert into ships values('Resolution','Revenge',1916);
1 row created.
SQL> insert into ships values('Revenge','Revenge',1916);
1 row created.
SQL> insert into ships values('Royal Oak','Revenge',1916);
1 row created.
SQL> insert into ships values('Royal Sovereign','Revenge',1916);
1 row created.
SQL> insert into ships values('Tennessee','Tennessee',1920);
1 row created.
SQL> insert into ships values('Washington','North Carolina',1941);
1 row created.
SQL> insert into ships values('Wisconsin','Iowa',1944);
1 row created.
SQL> insert into ships values('Yamato','Yamato',1941);
1 row created.
SQL>
SQL>REM-----------------------------------------------------------
> REM Populating BATTLES relation
SQL> REM Battles(name,date)
SQL>
SQL> insert into battles values('North Atlantic','24-may-41');
1 row created.
SQL> insert into battles values('Guadalcanal','11-nov-42');
1 row created.
SQL> insert into battles values('North Cape','12-dec-43');
1 row created.
SQL> insert into battles values('Surigao Strait','10-oct-44');
1 row created.
SQL>
SQL>REM----------------------------------------------------------
> REM Populating OUTCOMES relation - the result of battle by each
ship
SQL> REM outcomes(ship,battle,result)
SQL>
SQL> insert into outcomes values('Kongo','North Atlantic','sunk');
1 row created.
SQL> insert into outcomes values('California','Surigao
Strait','ok');
1 row created.
SQL> insert into outcomes values('Resolution','North
Atlantic','damaged');
1 row created.
SQL> insert into outcomes values('Musashi','North Cape','ok');
1 row created.
SQL> insert into outcomes values('Ramillies','Surigao
Strait','sunk');
1 row created.
SQL> insert into outcomes values('Haruna','North
Atlantic','sunk');
1 row created.
SQL> insert into outcomes values('Royal Oak','North
Atlantic','ok');
1 row created.
SQL> insert into outcomes
values('Kirishima','Guadalcanal','sunk');
1 row created.
SQL> insert into outcomes values('Resolution','Guadalcanal','ok');
1 row created.
SQL> insert into outcomes values('Tennessee','North
Atlantic','ok');
1 row created.
SQL> insert into outcomes values('North Carolina','North
Cape','sunk');
1 row created.
SQL> insert into outcomes
values('Revenge','Guadalcanal','damaged');
1 row created.
SQL> insert into outcomes values('Wisconsin','Surigao
Strait','ok');
1 row created.
SQL> insert into outcomes values('Washington','Guadalcanal','ok');
1 row created.
SQL> insert into outcomes values('Yamato','Surigao Strait','ok');
1 row created.
SQL> insert into outcomes values('New Jersey','Surigao
Strait','sunk');
1 row created.
SQL> insert into outcomes values('Washington','North
Cape','damaged');
1 row created.
SQL>
SQL> REM ------------END OF INSERT------------------------------
>
SQL>
SQL>
SQL> REM QUERY 1. List the name, class, the number of guns and
displacement of the ships engaged in the REM battle of
Guadalcanal.
SQL>
SQL> SELECT o.ship,c.class,c.numGuns,c.displacement
2 FROM outcomes o JOIN ships s on(o.ship=s.name)
3 JOIN classes c on(s.class=c.class)
4 WHERE o.battle='Guadalcanal';
SHIP CLASS NUMGUNS DISPLACEMENT
--------------- --------------- ---------- ------------
Kirishima Kongo 8 42000
Washington North Carolina 9 37000
Revenge Revenge 8 29000
Resolution Revenge 8 29000
SQL>
SQL> REM QUERY 2. Find those countries that had both battleships
and battle cruisers.
SQL>
SQL> SELECT DISTinCT c.country
2 FROM classes c JOIN classes c1 on(c.country=c1.country)
3 WHERE c.type='bb' AND c1.type='bc';
COUNTRY
---------------
Gt. Britain
Japan
SQL>
SQL> REM QUERY 3. Find the class of ships that resulted in all
(ok, sunk, damaged) as the outcome of REM the battle.
SQL>
SQL> SELECT class
2 FROM ships s JOIN outcomes o on(o.ship=s.name)
3 GROUP BY class
4 HAVinG count(DISTinCT o.result)=(SELECT count(DISTinCT
result)
5 FROM outcomes);
CLASS
---------------
North Carolina
Revenge
SQL>
SQL> REM QUERY 4. Find the battle in which, the ships with same
number of guns AND bore but highest REM displacement had
participated.
SQL>
SQL> SELECT DISTINCT o.battle
2 FROM classes cc,outcomes o,ships s,classes c
3 WHERE o.ship=s.name AND c.class=s.class
4 AND c.displacement>cc.displacement
5 AND c.numGuns=cc.numGuns
6 AND c.bore = cc.bore;
BATTLE
---------------
North Atlantic
Surigao Strait
Guadalcanal
SQL>
SQL> REM QUERY 5. Find those battles with at least three ships of
the same country.
SQL>
SQL> SELECT battle,count(ship)
2 FROM classes c JOIN ships s on(s.class=c.class)
3 JOIN outcomes o on(o.ship=s.name)
4 GROUP BY country,battle
5 HAVinG count(o.ship)>=3;
BATTLE COUNT(SHIP)
--------------- -----------
Surigao Strait 3
SQL>
SQL>
SQL> REM QUERY 6. Find the classes that had only one ship as a
member of that class.
SQL>
SQL> SELECT c.class
2 FROM ships s JOIN classes c on(s.class=c.class)
3 WHERE s.name <> c.class
4 GROUP BY c.class
5 HAVinG count(s.name)=1;
CLASS
---------------
Yamato
North Carolina
Renown
Tennessee
SQL>
SQL> REM You should use at least one subquery in each of your
answers and write each query in two
SQL> REM significantly different ways (e.g., using different sets
of the operators EXISTS, in, ALL, AND
SQL> REM ANY) for the following:
SQL>
SQL> REM QUERY 7. Find those classes of ships that does not
participated in the battle.
SQL>
SQL> REM 1
SQL>
SQL> SELECT country,class
2 FROM classes
3 WHERE class in(SELECT class
4 FROM ships
5 WHERE name in(SELECT ship
6 FROM outcomes
7 WHERE result='sunk')
8 GROUP BY class
9 HAVinG count(name)>=ALL(SELECT count(name)
10 FROM ships
11 WHERE name in(SELECT ship
12 FROM outcomes
13 WHERE result='sunk')
14 GROUP BY class));
COUNTRY CLASS
--------------- ---------------
Japan Kongo
SQL>
SQL> REM 2
SQL>
SQL> SELECT country,class
2 FROM classes c
3 WHERE EXISTS(SELECT class FROM ships s
4 WHERE class=c.class AND EXISTS ( SELECT ship
5 FROM outcomes
6 WHERE result='sunk'
7 AND s.name=ship)
8 GROUP BY class
9 HAVinG count(name)=(SELECT max(count(name))
10 FROM ships s1
11 WHERE EXISTS(SELECT ship
12 FROM outcomes
13 WHERE result='sunk'
14 AND s1.name=ship)
15 GROUP BY class));
COUNTRY CLASS
--------------- ---------------
Japan Kongo
SQL>
SQL> REM QUERY 8. Among the battle ships, display the class AND
country that has the maximum participation among all the REM
battles.
SQL>
SQL> REM 1
SQL>
SQL> SELECT country,class
2 FROM classes
3 WHERE type = 'bb'
4 AND class in (SELECT class
5 FROM outcomes,ships
6 GROUP BY class
7 HAVinG count(battle)=(SELECT
max(count(battle))
8 FROM outcomes,ships
9 GROUP BY class));
COUNTRY CLASS
--------------- ---------------
Gt. Britain Revenge
SQL>
SQL> REM 2
SQL>
SQL> SELECT country,class
2 FROM classes c
3 WHERE type = 'bb'
4 AND exists (SELECT class
5 FROM outcomes,ships s
6 WHERE class=c.class
7 GROUP BY class
8 HAVinG count(battle)=(SELECT max(count(battle))
9 FROM outcomes,ships
10 GROUP BY class));
COUNTRY CLASS
--------------- ---------------
Gt. Britain Revenge
SQL>
SQL>
SQL> REM QUERY 9. Find for each class with at least three ships,
the number of ships of that class sunk in battle.
SQL>
SQL> REM 1
SQL>
SQL> SELECT class,count(name)
2 FROM ships
3 WHERE name in(SELECT ship
4 FROM outcomes
5 WHERE result='sunk')
6 AND class in(SELECT class
7 FROM ships
8 GROUP BY class
9 HAVinG count(*)>=3)
10 GROUP BY class;
CLASS COUNT(NAME)
--------------- -----------
Iowa 1
Revenge 1
Kongo 3
SQL>
SQL> REM 2
SQL>
SQL> SELECT class,count(name)
2 FROM ships s
3 WHERE exists(SELECT class
4 FROM ships
5 GROUP BY class
6 HAVinG count(name)>=3
7 AND class=s.class)
8 AND exists(SELECT ship
9 FROM outcomes
10 WHERE result='sunk'
11 AND ship=s.name)
12 GROUP BY class;
CLASS COUNT(NAME)
--------------- -----------
Iowa 1
Revenge 1
Kongo 3
SQL>
SQL> REM QUERY 10.Find the class(es) and country of ships that
participated in at least three different battles.
SQL>
SQL>
SQL> REM 1
SQL>
SQL> SELECT class,country
2 FROM classes
3 WHERE class=ANY(SELECT s.class
4 FROM ships s,outcomes o
5 WHERE s.name=o.ship
6 GROUP BY class
7 HAVinG count(DISTinCT(o.battle))>=3);
CLASS COUNTRY
--------------- ---------------
Revenge Gt. Britain
SQL>
SQL>
SQL> REM 2
SQL>
SQL> SELECT class,country
2 FROM classes c
3 WHERE exists(SELECT class
4 FROM ships s,outcomes o
5 WHERE c.class=class
6 AND s.name=o.ship
7 GROUP BY class
8 HAVinG count(DISTinCT(o.battle))>=3);
CLASS COUNTRY
--------------- ---------------
Revenge Gt. Britain
SQL>
SQL>
SQL> REM QUERY 11. Display the country and class of at least two
ships (FROM same class) that participated in atleast two battles.
SQL>
SQL> REM 1
SQL>
SQL> SELECT country, class
2 FROM classes
3 WHERE class in (SELECT class
4 FROM ships s JOIN outcomes o
on( s.name=o.ship)
5 GROUP BY class
6 HAVinG count(DISTinCT o.battle)>=2)
7 AND class in(SELECT class
8 FROM ships
9 GROUP BY class
10 HAVinG count(*)>=2);
COUNTRY CLASS
--------------- ---------------
Japan Kongo
USA North Carolina
Gt. Britain Revenge
USA Tennessee
Japan Yamato
SQL>
SQL> REM 2
SQL>
SQL> SELECT country, class
2 FROM classes c
3 WHERE exists ( SELECT 'x'
4 FROM ships s JOIN outcomes o
on(s.name=o.ship)
5 WHERE c.class=class
6 GROUP BY class
7 HAVinG count(DISTinCT o.battle)>=2)
8 AND exists(SELECT 'x' FROM ships
9 WHERE c.class=class
10 GROUP BY class
11 HAVinG count(*)>=2);
COUNTRY CLASS
--------------- ---------------
Japan Kongo
USA North Carolina
Gt. Britain Revenge
USA Tennessee
Japan Yamato
SQL>
SQL>
SQL> REM QUERY 12. Find those ships that were damaged in one
battle, but later fought in another.
SQL>
SQL> REM 1
SQL>
SQL> SELECT o.ship
2 FROM outcomes o JOIN battles b on(o.battle=b.name)
3 WHERE ship in(SELECT ship
4 FROM outcomes o1 JOIN battles b1
5 on(o1.battle=b1.name)
6 WHERE result='damaged'
7 AND o.battle<>o1.battle
8 AND b.battle_date>b1.battle_date);
SHIP
---------------
Resolution
SQL>
SQL>
SQL> REM 2
SQL>
SQL> SELECT o.ship
2 FROM outcomes o JOIN battles b
3 on(o.battle=b.name)
4 WHERE exists(SELECT ship
5 FROM outcomes o1 JOIN battles b1
6 on(o1.battle=b1.name)
7 WHERE result='damaged'
8 AND o.battle<>b1.name
9 AND b.battle_date>b1.battle_date
10 AND o.ship=o1.ship);
SHIP
---------------
Resolution
SQL> REM QUERY 13. Find the country and class of ships which never
resulted 'sunk' or 'damaged' in the battle.
SQL>
SQL> REM 1
SQL>
SQL> SELECT country,class
2 FROM classes
3 WHERE class in(SELECT class
4 FROM ships
5 WHERE name in(SELECT ship
6 FROM outcomes ))
7 AND class NOT in(SELECT class
8 FROM ships
9 WHERE name in(SELECT ship
10 FROM outcomes
11 WHERE result in ('damaged','sunk')));
COUNTRY CLASS
--------------- ---------------
Japan Yamato
USA Tennessee
SQL> REM 2
SQL>
SQL> SELECT country,class
2 FROM classes c
3 WHERE exists(SELECT class
4 FROM ships s
5 WHERE c.class=class
6 AND exists(SELECT ship
7 FROM outcomes
8 WHERE s.name=ship))
9 AND not exists(SELECT class
10 FROM ships s
11 WHERE c.class=class
12 AND exists(SELECT ship
13 FROM outcomes
14 WHERE s.name=ship
15 AND result in('damaged','sunk')));
COUNTRY CLASS
--------------- ---------------
Japan Yamato
USA Tennessee
SQL>
SQL> REM Use SET operators (any one operator) for each of the
following:
SQL> REM QUERY 14. Display the ship name, the class and the year
launched for all the ships/except for the first ship of that REM
class.
SQL>
SQL> SELECT name,class,launched
2 FROM ships
3 MINUS
4 SELECT name,class,launched
5 FROM ships
6 WHERE name=class;
NAME CLASS LAUNCHED
--------------- --------------- ----------
California Tennessee 1921
Haruna Kongo 1915
Hiei Kongo 1914
Kirishima Kongo 1915
Missouri Iowa 1944
Musashi Yamato 1942
New Jersey Iowa 1943
Ramillies Revenge 1917
Repulse Renown 1916
Resolution Revenge 1916
Royal Oak Revenge 1916
NAME CLASS LAUNCHED
--------------- --------------- ----------
Royal Sovereign Revenge 1916
Washington North Carolina 1941
Wisconsin Iowa 1944
14 rows selected.
SQL>
SQL> REM QUERY 15. Display the class and country that never
participated in the battle.
SQL>
SQL> SELECT class,country
2 FROM classes
3 MINUS (SELECT c.class,c.country
4 FROM classes c
5 JOIN ships s on(s.class=c.class)
6 JOIN outcomes o on(o.ship=s.name));
CLASS COUNTRY
--------------- ---------------
Bismark Germany
Renown Gt. Britain
SQL> spool off;