CSC370: SQL set operations

SQL-set-operations.org

Set operations in SQL

First create some tables

\d

#+RESULTS[8652b65264450175c6f74c986973c17c07350b29]:

List of relations
Schema	Name	Type	Owner
public	battles	table	dmg
public	class	table	dmg
public	classes	table	dmg
public	outcomes	table	dmg
public	r	table	dmg
public	s	table	dmg
public	ships	table	dmg

\d battles

Table “public.battles”
Column	Type	Modifiers
battle	character(30)	not null
bdate	integer
Indexes:
“battles_pkey” PRIMARY KEY, btree (battle)
Referenced by:
TABLE “outcomes” CONSTRAINT “outcomes_battle_fkey” FOREIGN KEY (battle) REFERENCES battles(battle)

TABLE r; 

a	b
0	20
1	30

TABLE s; 

a	c
0	0
2	-1

DROP TABLE IF EXISTS Classes CASCADE;
DROP TABLE IF EXISTS Ships CASCADE;
DROP TABLE IF EXISTS Battles CASCADE;
DROP TABLE IF EXISTS Outcomes CASCADE;
CREATE TABLE classes(
   class CHAR(20),
   country CHAR(30),
   numgus  INTEGER,
   bore         FLOAT,
   displacement FLOAT,
   PRIMARY KEY (class)
);
CREATE TABLE Ships(
   shipname CHAR(30),
   class    CHAR(20),
   launched DATE,
   PRIMARY KEY (shipname),
   FOREIGN KEY (class) REFERENCES Classes
);
CREATE TABLE Battles(
   battle    CHAR(30),
   bdate    int,
   PRIMARY KEY (battle)
);
CREATE TABLE Outcomes(
   shipname  CHAR(30),
   battle    CHAR(20),
   result    CHAR(20),
   PRIMARY KEY(shipname, battle),
   FOREIGN KEY (shipname) REFERENCES Ships,
   FOREIGN KEY (battle) REFERENCES Battles
);

DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE

Then populate them

insert into battles VALUES  ('Tsushima', 1905), ('Yellow Sea', 1904 ); 

#+RESULTS[cf389225e7d2a1bd98f156edf30d4467384b8165]:

INSERT 0 2

insert into classes VALUES 
('Petropavlovsk', 'Russia', NULL, NULL, NULL), 
('Pallada', 'Russia',40, NULL,6839.0 ),
('Kasuga', 'Japan', 37, NULL, 7750 ),
('Nisshin', 'Japan', 35, NULL, 7822 ),
('Peresvet', 'Russia', NULL, NULL, NULL);

#+RESULTS[f8178d8a129f342e9b622fb616e6c56f2ff41843]:

INSERT 0 5

insert into ships VALUES  
('Kasuga', 'Kasuga','22 October 1902'),
('Nisshin', 'Nisshin', '9 February 1903'),
('Pallada', 'Pallada', '1 August 1899'),
('Diana', 'Pallada', '1 October 1899'),
('Aurora', 'Pallada', NULL),
('Oslyabya', 'Peresvet', '8 December 1898'),
('Peresvet', 'Peresvet', '19 May 1898'),
('Pobeda', 'Peresvet', NULL);

#+RESULTS[051b6c7030e418ec05c336b31a025c5eb58045d4]:

INSERT 0 8

delete from outcomes;
insert into Outcomes VALUES
('Kasuga','Yellow Sea', 'ok'),
('Nisshin', 'Yellow Sea', 'ok'), 
('Kasuga','Tsushima', 'ok'),
('Nisshin', 'Tsushima', 'ok'), 
('Pallada', 'Yellow Sea', 'damaged'),
('Diana', 'Yellow Sea', 'Light damage'),
('Oslyabya','Tsushima', 'sunk'),
('Peresvet','Tsushima', 'captured'),
('Pobeda','Tsushima', 'captured');

#+RESULTS[bb98e434cecf71252afe0ed78e5af3b584717758]:

DELETE 7
INSERT 0 9

Some simple queries

select * from classes;

#+RESULTS[2b17a81edfea017a73b52642f768787483826bf4]:

class	country	numgus	bore	displacement
Petropavlovsk	Russia
Pallada	Russia	40		6839
Kasuga	Japan	37		7750
Nisshin	Japan	35		7822
Peresvet	Russia

select * from ships;

shipname	class	launched
Kasuga	Kasuga	1902-10-22
Nisshin	Nisshin	1903-02-09
Pallada	Pallada	1899-08-01
Diana	Pallada	1899-10-01
Aurora	Pallada
Oslyabya	Peresvet	1898-12-08
Peresvet	Peresvet	1898-05-19
Pobeda	Peresvet

select * from battles;

battle	bdate
Tsushima	1905
Yellow Sea	1904

select class from classes where numgus > (select 5);

class
Pallada
Kasuga
Nisshin

select distinct  5 * 10 as x from outcomes;

x
50

select 5 * 10 + 5;

?column?
55

SELECT * FROM ships 
WHERE 
   shipname IN (SELECT shipname FROM outcomes WHERE result = 'sunk');

shipname	class	launched
Oslyabya	Peresvet	1898-12-08

solutions for exercise

participated in both battles

One long statement

SELECT shipname from outcomes where battle = 'Yellow Sea'
INTERSECT
SELECT shipname from outcomes where battle = 'Tsushima'

#+RESULTS[37f31c908a4188f005697cde79f8de760e72c1a5]:

shipname
Nisshin
Kasuga

Using WITH to document subqueries

WITH YS AS (SELECT shipname from outcomes where battle = 'Yellow Sea'),
     TS AS (SELECT shipname from outcomes where battle = 'Tsushima')
SELECT * FROM YS INTERSECT SELECT * FROM TS

#+RESULTS[9bd709a2b25bfab89caf5aff1a491ecf4223d0af]:

shipname
Nisshin
Kasuga

Using WITH and TABLE

WITH YS AS (SELECT shipname from outcomes where battle = 'Yellow Sea'),
     TS AS (SELECT shipname from outcomes where battle = 'Tsushima')
TABLE YS INTERSECT TABLE TS

#+RESULTS[9e8c39aa5d94fb5e587d93b652f2debdf39ba64b]:

shipname
Nisshin
Kasuga

participated in either one of the two battles

single query

SELECT shipname from outcomes where battle IN ('Yellow Sea', 'Tsushima');

#+RESULTS[42675e73b26de5c5aa511bef4564b6f103f862af]:

shipname
Kasuga
Nisshin
Kasuga
Nisshin
Pallada
Diana
Oslyabya
Peresvet
Pobeda

two subqueries

WITH YS AS (SELECT shipname from outcomes where battle = 'Yellow Sea'),
     TS AS (SELECT shipname from outcomes where battle = 'Tsushima')
TABLE YS UNION TABLE TS 

#+RESULTS[1307b3c7b67e3f40e2ba73f3ea6245762eccb099]:

shipname
Diana
Kasuga
Nisshin
Oslyabya
Pallada
Peresvet
Pobeda

Participated in one but not the other

WITH YS AS (SELECT shipname from outcomes where battle = 'Yellow Sea'),
     TS AS (SELECT shipname from outcomes where battle = 'Tsushima')
TABLE YS EXCEPT TABLE TS 

#+RESULTS[e857d2d52e3219a9b43cc266c9bfddbaba192dcd]:

shipname
Pallada
Diana

Of course EXCEPT is not commutative:

WITH YS AS (SELECT shipname from outcomes where battle = 'Yellow Sea'),
     TS AS (SELECT shipname from outcomes where battle = 'Tsushima')
TABLE TS EXCEPT TABLE YS

#+RESULTS[e77d9c4774342218daedb64a575ec7878ec7d012]:

shipname
Pobeda
Peresvet
Oslyabya