One-to-Many Relationship

No, I'm not talking about a player!

Database Management:
When dealing witih One-to-Many (1:M) Relationship

Join condition - where DORMATORY.DormName = STUDENT.DormName
DORMATORY.DormName = STUDENT.DormCode is okay too, as long as it's the foreign key the renamed FK is okay.
Order doesn't matter, DORMATORY or STUDENT first, it doesn't matter.

TableName.AttributeName = TableName.AttributeName

SQL is a binary operator, can deal with only 2 tables at a time.

minimum cardinalaties are totally irrelivent to joining tables

List the customer name, customer Id and order date for all orders
Join Condition:
WHERE CUSTOMER_t.Customer_ID = ORDER_t.Customer_ID;
Alternative is to do it from the FROM line:
FROM Customer_t INNER JOIN Order_t ON Customer_t.Customer_ID = Order_t.Customer_ID;

Cartesian Product - All combination of all rows from one table with all rows from another table.
Select all combination of a Customer name and a Product Name:
MISTAKE:
SELECT C.Customer_name, P.Product_description
FROM Customer_t C, Product_t P;
FORGOT:
the join condition

Union - everything in the 1st table and everything in the 2nd table and combine it to be a 3rd table
Must be union compatible
- same degree (same number of attributes)
- same domain

(SELECT
FROM
WHERE )
union
(SELECT
FROM
WHERE )

Assume table and attributes:
MAILING-LIST (Name, Street, City, ST, ZIP)
CUSTOMER (Name, Street, City, ST, ZIP, Balance-due)

We want to send a special offer mailing to all customers and protential customers who have no outstanding balance-due with us.
Write the SQL to create the mailing list.

The results will be titled from the attributes from the 1st table

Alias
To shorten a table name:

SELECT C.Customer_name, C.City
FROM Customer_t C
Where C.State = "FL"
-Alias can be used before it is declared. . . C is the Alias, Customer_t is the original name
-Notice that C was already used in SELECT line. That's okay! As long as it is defined in the FROM line. Remember do not separate original and alias with a coma when defining it.
-Nested query okay, separate query the alias does not carry over.

Next Wednesday: Working on the Multi-table queries!