lect9 - 1.264 Lecture 9 SQL Joins subqueries views Joins...

Info iconThis preview shows pages 1–5. Sign up to view the full content.

View Full Document Right Arrow Icon
1.264 Lecture 9 SQL: Joins, subqueries, views
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Joins Relational model permits you to bring data from separate tables into new and unanticipated relationships. Relationships become explicit when data is manipulated: when you query the database, not when you create it. This is critical; it allows extensibility in databases. The EPA never thought its data would be used in 1.264 along with DOT carrier data, and some new order tables. You can join on any columns in tables, as long as data types match and the operation makes sense. They don’t need to be keys, though they usually are. Good joins Join column is usually key column: Either primary key or foreign key Join columns must have compatible data types Nulls will never join
Background image of page 2
Joins List all orders, showing order number and amount, and name and credit limit of customer Orders has order number and amount, but no customer names or credit limits Customers has customer names and credit limit, but no order info SELECT OrderNbr, Amt, Company, CreditLimit FROM Customers, Orders WHERE Cust = CustNbr; (Implicit syntax) SELECT OrderNbr, Amt, Company, CreditLimit FROM Customers INNER JOIN Orders ON Customers.CustNbr = Orders.Cust; Join (SQL-92) CustNbr Company CustRep CreditLimit 211 Connor Co 89 $50,000.00 522 Amaratunga Enterprises 89 $40,000.00 890 Feni Fabricators 53 $1,000,000.00 OrderNbr Cust Prod Qty Amt Disc 1 211 Bulldozer 7 $31,000.00 0.2 2 522 Riveter 2 $4,000.00 0.3 3 522 Crane 1 $500,000.00 0.4
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Join with 3 tables List orders over $25,000, including the name of the salesperson who took the order and the name of the customer who placed it. SELECT OrderNbr, Amt, Company, Name FROM Orders, Customers, SalesReps WHERE Cust = CustNbr AND CustRep = RepNbr AND Amt >= 25000; (Implicit syntax) OrderNbr Cust Prod Qty Amt Disc 1 211 Bulldozer 7 $31,000.00 0.2 2 522 Riveter 2 $4,000.00 0.3 3 522 Crane 1 $500,000.00 0.4 CustNbr Company CustRep CreditLimit 211 Connor Co 89 $50,000.00 522 Amaratunga Enterprises 89 $40,000.00 890 Feni Fabricators 53 $1,000,000.00 RepNbr Name RepOffice
Background image of page 4
Image of page 5
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 12/06/2011 for the course ESD 1.264j taught by Professor Georgekocur during the Fall '06 term at MIT.

Page1 / 18

lect9 - 1.264 Lecture 9 SQL Joins subqueries views Joins...

This preview shows document pages 1 - 5. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online