#+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
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)
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
insert into battles VALUES (' Tsushima' , 1905 ), (' Yellow Sea' , 1904 );
#+RESULTS[cf389225e7d2a1bd98f156edf30d4467384b8165]:
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 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]:
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]:
#+RESULTS[2b17a81edfea017a73b52642f768787483826bf4]:
class country numgus bore displacement
Petropavlovsk Russia
Pallada Russia 40 6839
Kasuga Japan 37 7750
Nisshin Japan 35 7822
Peresvet Russia
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
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;
SELECT * FROM ships
WHERE
shipname IN (SELECT shipname FROM outcomes WHERE result = ' sunk' );
shipname class launched
Oslyabya Peresvet 1898-12-08
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]:
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]:
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]:
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
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]:
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