Module 02b - Basic SQL - Part II/II Flashcards
What are subqueries
When one SQL query is embedded in another SQL query
What is the syntax of a subquery in a DDL statement
SELECT SUM (ExtendedPrice) AS Revenue FROM ORDER_ITEM WHERE SKU IN (SELECT SKU FROM SKU_DATA WHERE Department = 'Water Sports');
How to connect two or more tables
By joining “composite” rows from multiples tables as long as they have fields that can link the rows of one table with the rows in another table.
What’s the syntax to perform a junction between tables
SELECT Column1, Column2
FROM TABLE1, TABLE2
WHERE TABLE1.ColumnX = TABLE2.ColumnY
//admits AND or OR operators on WHERE clause
How the JOIN ON operator works?
By “derivating” a new table JOINING rows ON their attributes are common.
What’s is the syntax of JOIN ON
SELECT Column1, Column2
FROM TABLE1 JOIN TABLE2
ON TABLE1.ColumnX = TABLE2.ColumnY
WHERE ColumnWhatever > 1000000
How INNER JOIN works?
Returns all rows when there is at least one match in BOTH tables
How LEFT JOIN works?
Return all rows from the left table, and the matched rows from the right table
How RIGHT JOIN works?
Return all rows from the right table, and the matched rows from the left table
How FULL JOIN works?
Return all rows when there is a match in ONE of the tables
Recap
SELECT … FROM … WHERE …
Like
Build-in functions
GROUP BY: HAVING v.s. WHERE
Querying multiple tables: Sometimes, a join can be used as an alternative to a subquery, and a subquery can be used as an alternative to a join.