Ben Miller
CS65 - Database Systems
Problem Set 3 (Part 2) PSQL

------------------------

CREATE TABLE s (
	s_num	varchar(20),
	sname	varchar(20),
	status	INT,
	city	varchar(20),
	PRIMARY KEY (s_num)
);

INSERT 
INTO s ( s_num, sname, status, city)
VALUES ('S1', 'Smith', 20, 'London' );

INSERT 
INTO s ( s_num, sname, status, city)
VALUES ('S2', 'Jones', 10, 'Paris' );

INSERT 
INTO s ( s_num, sname, status, city)
VALUES ('S3', 'Blake', 30, 'Paris' );

INSERT 
INTO s ( s_num, sname, status, city)
VALUES ('S4', 'Clark', 20, 'London' );

INSERT 
INTO s ( s_num, sname, status, city)
VALUES ('S5', 'Adams', 30, 'Athens' );

CREATE TABLE p (
	p_num 	varchar(20),
	pname 	varchar(20),
	color 	varchar(20),
	weight 	INT,
	city   	varchar(20),
	PRIMARY KEY (P_num)
);

INSERT 
INTO p (p_num, pname, color,weight, city)
VALUES ('P1', 'Nut', 'Red', 12, 'London' );

INSERT 
INTO p (p_num, pname, color,weight, city)
VALUES ('P2', 'Bolt', 'Green', 17, 'Paris' );

INSERT 
INTO p (p_num, pname, color,weight, city)
VALUES ('P3', 'Screw', 'Blue', 17, 'Rome' );

INSERT 
INTO p (p_num, pname, color,weight, city)
VALUES ('P4', 'Screw', 'Red', 14, 'London' );

INSERT 
INTO p (p_num, pname, color,weight, city)
VALUES ('P5', 'Screw', 'Blue', 14, 'Paris' );

INSERT 
INTO p (p_num, pname, color,weight, city)
VALUES ('P6', 'Cog' , 'Red', 19, 'London' );

CREATE TABLE j (
	j_num varchar(20),
	jname varchar(20),
	city varchar(20),
	PRIMARY KEY (j_num)
);

INSERT
INTO j (j_num, jname, city)
VALUES ('J1', 'Sorter', 'Paris');

INSERT
INTO j (j_num, jname, city)
VALUES ('J2', 'Display', 'Rome');

INSERT
INTO j (j_num, jname, city)
VALUES ('J3', 'Ocr', 'Athens');

INSERT
INTO j (j_num, jname, city)
VALUES ('J4', 'Console', 'Athens');

INSERT
INTO j (j_num, jname, city)
VALUES ('J5', 'Raid', 'London');

INSERT
INTO j (j_num, jname, city)
VALUES ('J6', 'Eds', 'Oslo');

INSERT
INTO j (j_num, jname, city)
VALUES ('J7', 'Tape', 'London');

CREATE TABLE spj (
       s_num varchar(20),
       p_num varchar(20),
       j_num varchar(20),
       QTY INT,
       PRIMARY KEY (s_num, p_num, j_num)
);

INSERT
INTO spj ( s_num, p_num, j_num, QTY )
VALUES ('S1', 'P1', 'J1', 200 );

INSERT
INTO spj ( s_num, p_num, j_num, QTY )
VALUES ( 'S1', 'P1', 'J4', 700 );

INSERT
INTO spj ( s_num, p_num, j_num, QTY )
VALUES ( 'S2', 'P3', 'J1', 400 );

INSERT
INTO spj ( s_num, p_num, j_num, QTY )
VALUES ( 'S2', 'P3', 'J2', 200 );

INSERT
INTO spj ( s_num, p_num, j_num, QTY )
VALUES ( 'S2', 'P3', 'J3', 200 );

INSERT
INTO spj ( s_num, p_num, j_num, QTY )
VALUES ( 'S2', 'P3', 'J4', 500 );

INSERT
INTO spj ( s_num, p_num, j_num, QTY )
VALUES ( 'S2', 'P3', 'J5', 600 );

INSERT
INTO spj ( s_num, p_num, j_num, QTY )
VALUES ( 'S2', 'P3', 'J6', 400 );

INSERT
INTO spj ( s_num, p_num, j_num, QTY )
VALUES ( 'S2', 'P3', 'J7', 800 );

INSERT
INTO spj ( s_num, p_num, j_num, QTY )
VALUES ( 'S2', 'P5', 'J2', 100 );

INSERT
INTO spj ( s_num, p_num, j_num, QTY )
VALUES ( 'S3', 'P3', 'J1', 200 );

INSERT
INTO spj ( s_num, p_num, j_num, QTY )
VALUES ( 'S3', 'P4', 'J2', 500 );

INSERT
INTO spj ( s_num, p_num, j_num, QTY )
VALUES ( 'S4', 'P6', 'J3', 300 );

INSERT
INTO spj ( s_num, p_num, j_num, QTY )
VALUES ( 'S4', 'P6', 'J7', 300 );

INSERT
INTO spj ( s_num, p_num, j_num, QTY )
VALUES ( 'S5', 'P2', 'J2', 200 );

INSERT
INTO spj ( s_num, p_num, j_num, QTY )
VALUES ( 'S5', 'P2', 'J4', 100 );

INSERT
INTO spj ( s_num, p_num, j_num, QTY )
VALUES ( 'S5', 'P5', 'J5', 500 );

INSERT
INTO spj ( s_num, p_num, j_num, QTY )
VALUES ( 'S5', 'P5', 'J7', 100 );

INSERT
INTO spj ( s_num, p_num, j_num, QTY )
VALUES ( 'S5', 'P6', 'J2', 200 );

INSERT
INTO spj ( s_num, p_num, j_num, QTY )
VALUES ( 'S5', 'P1', 'J4', 100 );

INSERT
INTO spj ( s_num, p_num, j_num, QTY )
VALUES ( 'S5', 'P3', 'J4', 200 );

INSERT
INTO spj ( s_num, p_num, j_num, QTY )
VALUES ( 'S5', 'P4', 'J4', 800 );

INSERT
INTO spj ( s_num, p_num, j_num, QTY )
VALUES ( 'S5', 'P5', 'J4', 400 );

INSERT
INTO spj ( s_num, p_num, j_num, QTY )
VALUES ( 'S5', 'P6', 'J4', 500 );

-------------------------

GRANT ALL ON s TO millebe;
GRANT ALL ON p TO millebe;
GRANT ALL ON j TO millebe;
GRANT ALL ON spj TO millebe;

-------------------------

millebe=> SELECT * FROM s;
s_num|sname|status|city  
-----+-----+------+------
S1   |Smith|    20|London
S2   |Jones|    10|Paris 
S3   |Blake|    30|Paris 
S4   |Clark|    20|London
S5   |Adams|    30|Athens
(5 rows)

millebe=> SELECT * FROM p;
p_num|pname|color|weight|city  
-----+-----+-----+------+------
P1   |Nut  |Red  |    12|London
P2   |Bolt |Green|    17|Paris 
P3   |Screw|Blue |    17|Rome  
P4   |Screw|Red  |    14|London
P5   |Screw|Blue |    14|Paris 
P6   |Cog  |Red  |    19|London
(6 rows)

millebe=> SELECT * FROM j;
j_num|jname  |city  
-----+-------+------
J1   |Sorter |Paris 
J2   |Display|Rome  
J3   |Ocr    |Athens
J4   |Console|Athens
J5   |Raid   |London
J6   |Eds    |Oslo  
J7   |Tape   |London
(7 rows)

millebe=> SELECT * FROM spj;
s_num|p_num|j_num|qty
-----+-----+-----+---
S1   |P1   |J1   |200
S1   |P1   |J4   |700
S2   |P3   |J1   |400
S2   |P3   |J2   |200
S2   |P3   |J3   |200
S2   |P3   |J4   |500
S2   |P3   |J5   |600
S2   |P3   |J6   |400
S2   |P3   |J7   |800
S2   |P5   |J2   |100
S3   |P3   |J1   |200
S3   |P4   |J2   |500
S4   |P6   |J3   |300
S4   |P6   |J7   |300
S5   |P2   |J2   |200
S5   |P2   |J4   |100
S5   |P5   |J5   |500
S5   |P5   |J7   |100
S5   |P6   |J2   |200
S5   |P1   |J4   |100
S5   |P3   |J4   |200
S5   |P4   |J4   |800
S5   |P5   |J4   |400
S5   |P6   |J4   |500
(24 rows)

------------------------

2) Create a view named PS ...

millebe=> CREATE VIEW PS AS
millebe-> SELECT sname, pname FROM s, p, spj
millebe-> WHERE spj.s_num = s.s_num
millebe-> AND spj.p_num = p.p_num;
CREATE 27818 1

millebe=> SELECT * FROM PS;
sname|pname
-----+-----
Smith|Nut  
Smith|Nut  
Adams|Nut  
Adams|Bolt 
Adams|Bolt 
Jones|Screw
Jones|Screw
Jones|Screw
Jones|Screw
Jones|Screw
Jones|Screw
Jones|Screw
Blake|Screw
Adams|Screw
Blake|Screw
Adams|Screw
Jones|Screw
Adams|Screw
Adams|Screw
Adams|Screw
Clark|Cog  
Clark|Cog  
Adams|Cog  
Adams|Cog  
(24 rows)

-----------------------

3) What happens when you update a view?

Because views are not ordinary tables, INSERT, UPDATE, and DELETE on views have no effect.

------------------------

4) Create sql expressions ...

a)
millebe=> SELECT s_num FROM spj WHERE QTY >= 300 AND QTY <= 750;
s_num
-----
S1   
S2   
S2   
S2   
S2   
S3   
S4   
S4   
S5   
S5   
S5   
(11 rows)

b)
millebe=> SELECT p.p_num FROM s, p, j, spj
millebe-> WHERE s.s_num = spj.s_num
millebe-> AND p.p_num = spj.p_num
millebe-> AND j.j_num = spj.j_num
millebe-> AND s.city = 'London'
millebe-> AND j.city = 'London';
p_num
-----
P6   
(1 row)

c)
millebe=> SELECT * FROM spj
millebe-> WHERE spj.s_num NOT IN (SELECT DISTINCT s.s_num FROM s)
millebe-> OR spj.p_num NOT IN (SELECT DISTINCT p.p_num FROM p)
millebe-> OR spj.j_num NOT IN (SELECT DISTINCT j.j_num FROM j);
s_num|p_num|j_num|qty
-----+-----+-----+---
(0 rows)

After checking the DB over I realized that this query is correct.