Databse 3. More Advanced SQL

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';