Advanced SQL

At the end of this week, you will be able to:

Advanced SQL commands

SQL commands to return all rows from two tables:

SELECT column(s)
FROM table1
UNION ALL
SELECT column(s)
FROM table2;

SQL commands to return only rows that exist in both tables:

SELECT column(s)
FROM table1
INTERSECT
SELECT column(s)
FROM table2;

SQL commands to return all rows in the first SELECT but excludes those by the second SELECT:

SELECT col1,col2,...
FROM table1
EXCEPT
SELECT col1,col2,...
FROM table2;

SQL command to specify the number of records to return:

SELECTTOPnumber | percent column_names(s) <br/>FROM table_name;`

Subqueries

A Subquery is a SQL query nested inside a SQL query. Very useful to create a virtual table usable by the main query.

SELECT column(s)
FROM table1
WHERE value IN
(SELECT column_name
FROM tables2
WHERE conditions);

Joins

Relational databases are defined with tables or entities such Employee and Department. To create a link between the two tables a column is defined as Department_ID in both tables. Now, if you would like to extract employee names and departments names you need to SQL JOIN.

There are four main type of joins:

JOIN Action
INNER JOIN return records that have matching values in both tables
LEFT JOIN return all records from table1 (LEFT table1) and the matched records from table2
RIGHT JOIN return all records from table2 (RIGHT table1) and the matched records from table1.
FULL JOIN() return all rows from both tables

Syntax:

SELECT table1.col_name, table2.col_name
FROM table1
INNER JOIN table2ON table1.col_name = table2.col_name;

OR

SELECT table1.col_name, table2.col_name
FROM table1
LEFT JOIN table2ON table1.col_name = table2.col_name;

OR

SELECT table1.col_name, table2.col_name
FROM table1
RIGHT JOIN table2ON table1.col_name = table2.col_name;

OR

SELECT table1.col_name, table2.col_name
FROM table1
FULL JOIN table2ON table1.col_name = table2.col_name;

🛎 🎙️ Recordings on Canvas will cover more details and examples! Have fun learning and coding 😃! Let me know how I can help!

📚 👈 Assignments - Advanced SQL

Instructions are posted on Canvas.