joins.sh (1849B)
1 #!/usr/bin/env bash 2 # File : joins.sh 3 # Author : Fabian Wermelinger 4 # Description: Examples for table joins of lecture 23 5 # Copyright 2022 Harvard University. All Rights Reserved. 6 7 cat <<EOF | sqlite3 8 .header on 9 .mode column 10 .nullvalue NULL 11 12 CREATE TABLE A ( 13 ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 14 Name TEXT NOT NULL, 15 Office TEXT NOT NULL, 16 Salary REAL 17 ); 18 INSERT INTO A (Name, Office, Salary) VALUES ('Frank', 'A12', 45000.0); 19 INSERT INTO A (Name, Office, Salary) VALUES ('Roberta', 'A10', 80000.0); 20 INSERT INTO A (Name, Office, Salary) VALUES ('Lory', 'B07', 50000.0); 21 22 CREATE TABLE B ( 23 ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 24 Bonus REAL, 25 EID INTEGER NOT NULL, 26 FOREIGN KEY (EID) REFERENCES A(ID) 27 ); 28 INSERT INTO B (Bonus, EID) VALUES (8000.0, 1); 29 INSERT INTO B (Bonus, EID) VALUES (10000.0, 3); 30 INSERT INTO B (Bonus, EID) VALUES (1000000.0, 10); 31 32 .print 'Command: SELECT * FROM A INNER JOIN B ON B.EID = A.ID;' 33 SELECT * FROM A INNER JOIN B ON B.EID = A.ID; 34 .print '' 35 36 .print 'Command: SELECT * FROM A, B WHERE B.EID = A.ID;' 37 SELECT * FROM A, B WHERE B.EID = A.ID; 38 .print '' 39 40 .print 'Command: SELECT * FROM A LEFT OUTER JOIN B ON B.EID = A.ID;' 41 SELECT * FROM A LEFT OUTER JOIN B ON B.EID = A.ID; 42 .print '' 43 44 .print 'Command: SELECT * FROM B LEFT OUTER JOIN A ON B.EID = A.ID;' 45 SELECT * FROM B LEFT OUTER JOIN A ON B.EID = A.ID; 46 .print '' 47 48 -- How to address columns with identical name? 49 -- --> alias two columns with identical names to distinct names in joined table 50 .print 'Command: SELECT A.ID AS AID, B.ID AS BID FROM B LEFT OUTER JOIN A ON B.EID = A.ID;' 51 SELECT A.ID AS AID, B.ID AS BID FROM B LEFT OUTER JOIN A ON B.EID = A.ID; 52 .print '' 53 54 -- as opposed to this less useful variant 55 .print 'Command: SELECT A.ID, B.ID FROM B LEFT OUTER JOIN A ON B.EID = A.ID;' 56 SELECT A.ID, B.ID FROM B LEFT OUTER JOIN A ON B.EID = A.ID; 57 EOF