More Advanced SQL
Relational Database
- has tables which are linked using key attributes
Foreign Keys
- An attributes in one table that uniquely identifies a row of another table is a foreign key
- It is a primary key in the other table, it doesn't need to be unique in this table
Referential Integrity
- Each foreign key need to refer to an actual row in the table it refers to;-------> this is called Referential Integrity
- The exception is a NULL value
Querying Multiple Tables - Joins
- INNER JOIN
INNER JOIN
- return the rows where the join condition is met
- SELECT column_name(s)
FROM talbe1
INNER JOIN table2
ON talbe1.column_name = table2.column_name; - e.g. SELECT * FROM student INNER JOIN lecturer ON student.advisor=lecturer.staffid;
Aliases
- instead of table1.column_name we can use aliases: aliases are used to temporarily rename a table or column.
- syntax :
table rename: SELECT col FROM table1 AS temp_name;
column rename:SELECT col AS temp_name FROM table1;
LEFT JOIN
- return all rows of table1 (left table) with corresponding rows of table 2 if the condition is met, or null if not
RIGHT JOIN
- return all rows of table2 (Right table) with corresponding rows of table 1 if the condition is met, or null if not
- e.g.
FULL OUT JOIN
- SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 on table1.column_name = table2.column_name;
SELF JOIN
join a table to itself: compare a column in the table to another column in the same table
SELECT alias.col, alias2.col FROM table1 AS alias1 INNER JOIN talbe1 AS alias2 ON alias1.fk = alias2.pk;
Joining Multiple Tables
- SELECT table1.col, table3.col FROM table1 INNER JOIN table2 ON table1.pk1 = table2.fk1 INNER JOIN table3 ON table2.fk3 = table3.pk3;
- SELECT table1.col, table3.col FROM table1,table2,table3 WHERE table1.pk1 = table2.fk1 AND table2.fk3 = table3.pk3;
Nested Queries
- sub-queries must be enclosed in()brackets
IN / NOT IN
VIEW Syntax
- CREATE VIEW <ViewName> AS <query>
- CREATE VIEW CSstaffView AS SELECT* FROM Lecturer WHERE school = 'Computing Science';
