Module 02b - Basic SQL - Part II/II

Question 1

What are subqueries

Answer 1

When one SQL query is embedded in another SQL query

Question 2

What is the syntax of a subquery in a DDL statement

Answer 2

SELECT	SUM (ExtendedPrice) AS Revenue
FROM ORDER_ITEM
WHERE SKU IN
	(SELECT   SKU
	FROM SKU_DATA
	WHERE Department = 'Water Sports');

Question 3

How to connect two or more tables

Answer 3

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.

Question 4

What’s the syntax to perform a junction between tables

Answer 4

SELECT Column1, Column2
FROM TABLE1, TABLE2
WHERE TABLE1.ColumnX = TABLE2.ColumnY
//admits AND or OR operators on WHERE clause

Question 5

How the JOIN ON operator works?

Answer 5

By “derivating” a new table JOINING rows ON their attributes are common.

Question 6

What’s is the syntax of JOIN ON

Answer 6

SELECT Column1, Column2
FROM TABLE1 JOIN TABLE2
ON TABLE1.ColumnX = TABLE2.ColumnY
WHERE ColumnWhatever > 1000000

Question 7

How INNER JOIN works?

Answer 7

Returns all rows when there is at least one match in BOTH tables

Question 8

How LEFT JOIN works?

Answer 8

Return all rows from the left table, and the matched rows from the right table

Question 9

How RIGHT JOIN works?

Answer 9

Return all rows from the right table, and the matched rows from the left table

Question 10

How FULL JOIN works?

Answer 10

Return all rows when there is a match in ONE of the tables

Question 11

Recap

Answer 11

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.