Unit 5 - SQL (Part 2)

Question 1

How to rename headers in the results/output?

Answer 1

Use the ‘AS’ keyword:

SELECT CustomerName AS Name

^^ renames header/attribute in the results / output

Question 2

How to rename an entire table (in the scope of the query only)?

Answer 2

SELECT *
FROM Customers AS C1;

^^ renames customers table to c1 inside this query only, so easier to pull data (e.g., SELECT C1.Name, WHERE C1.age > 30, etc.)

Question 3

What are the two main ways to join tables in SQL?

Answer 3

1. Use the ‘WHERE’ clause
2. Use Inner and Outer JOIN Operators

Question 4

When can you use the ‘WHERE’ keyword to join tables?

Answer 4

When the tables have common attributes (even if attribute name is different)

SELECT *
FROM Table1, Table2
WHERE Table1.X = Table2.Y;

Question 5

What’s more common to see: ‘WHERE’ or ‘INNER JOIN’?

Answer 5

INNER JOIN - better readability

Question 6

What’s the syntax for INNER JOIN? Say you want to join customers with orders (common attribute customer_id)

Answer 6

FROM customers AS C
INNER JOIN orders AS O on C.customer_id = O.customer_id;

This will display all columns from customers and orders,
and all the rows/records where customer_id in orders is the same as customer_id in customers

Question 7

LEFT JOIN syntax

Answer 7

FROM customers AS C
LEFT JOIN orders AS O on C.customer_id = O.customer_id;

This will include ALL rows from customers and only the corresponding rows from orders

Question 8

FULL JOIN syntax

Answer 8

MySQL does not support FULL JOIN. You can get this result by doing the union of LEFT JOIN and RIGHT JOIN

Question 9

Does MySQL support NATURAL JOIN?

Answer 9

Yes, but DO NOT USE - AVOID IT!
That’s because there may be MULTIPLE common columns, it just assumes which ones you want - you may not get the result you want.

Question 10

What are the rules with using UNION, INTERSECT, EXCEPT? What’s the syntax?

Answer 10

The two tables must be compatible (same attribute names, same data types, same number of each)

SELECT column_name(s)
FROM table1

OPERATOR

SELECT column_name(s)
FROM table2;

^^ column_name(s) must be compatible

Question 11

What’s the syntax for EXCEPT operator?

Answer 11

MySQL doesn’t support EXCEPT, must use NOT IN

Question 12

What are the aggregate functions?

Answer 12

MIN(), MAX(), COUNT(), AVG(), SUM()

They’re applied to a single column/ attribute to return a single value
e.g., MIN(gpa) = returns the smallest value from the column gpa

Question 13

What is COUNT(*)?

Answer 13

Special case of COUNT() where you it counts all the rows of a table regardless of whether null or duplicate values occur

Question 14

Where can you use an aggregate function (what part of the SQL query)?

Answer 14

ONLY two spots:
1. SELECT list
2. HAVING clause