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!
Strategy:
short term advantage means that competition can easily copy it.
long term advantage means that it is difficult to copy. Thus creating distance from competition.
In project think of what is the long term advantages of the business.
From data to knowledge:
Data - out of context
Information - relationships between data
Knowledge - patterns between data
Porter's 5 Forces:
the knowledge of the market.
Next week: answer "What is Wisdom"?
Elements of CIS
- Hardware (machine)
- Software (machine)
- data (the pivot between machine and human)
- people (human)
- procedures (human)
*************************
Teams:
This is going to be interesting . . .we're already down 2 players in this group of 4. Stay tune!
