380%20sum11%20Class%2012%20SQL%20full%20set

380%20sum11%20Class%2012%20SQL%20full%20set - Objective Be...

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

View Full Document Right Arrow Icon
Click to edit Master subtitle style Information Retrieval – SQL cont’d Objective : Be able to Write Queries using Structured Query Language (SQL) Get practice with REA modeling 1. Access project and presentation 2. Structured Query Language (SQL) Syntax Inner Joins vs Outer Joins Full Outer Joins 3. Sparky’s Amusement Park REA problem
Background image of page 1

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

View Full Document Right Arrow Icon
‘Universal’ language for RDB Queries. In Access: Query, Open, View | SQL view Simple syntax, ending with a semi-colon ‘;’ SELECT attribute1,attribute2 for output Table.attribute, or [Table name].[attribute name] - use square brackets if any spaces in the table or attribute name FROM tables containing all attributes used in query if >1 table, use INNER JOIN to specify foreign keys (next slides) (optional) WHERE attributes (not necessarily those in output) are =, >,< number or "text" (turn off SmartQuotes in Office 07 – Options|Proofing | Autocorrect Options …|Autoformat As You Type – first box) LIKE is equivalent to = but can also use * as a ‘wild card’; e.g. WHERE Table.attribute LIKE "*Los*"; use AND and OR In Access Table.Attribute=[ Instruction ] will prompt for input (optional) ORDER BY attribute1 ASC(ending) or DESC(ending), attribute2 ASC(ending) or DESC(ending) (optional) GROUP BY attribute for sub-totals, add SUM(attribute) after SELECT Structured Query Language (SQL) 
Background image of page 2
Runner Database
Background image of page 3

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

View Full Document Right Arrow Icon
SQL Practice (Single table) Runner’s database queries 1. List the names and phone numbers of all runners
Background image of page 4
SQL Practice Runner’s database queries 1. List the names and phone numbers of all runners SELECT [Last Name],[First Name],Phone FROM Runner; SELECT Runner.[Last Name], Runner.[First FROM Runner; OR Put in the TABLE in addition to the field – required if you use more than one table !
Background image of page 5

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

View Full Document Right Arrow Icon
SQL Practice (Single table) Runner’s database queries 1. List the names and phone numbers of all runners 2. List the race id, race name and city of all of the races held in Kansas (KS)
Background image of page 6
SQL Practice (Single table) 2. List the race id, race name, and city of all of the races held in Kansas ("KS"). SELECT [Race ID],[Race Name],City,State FROM Race WHERE State="KS"; SELECT Race.[Race ID], Race.[Race Name], Race.City,Race.State FROM Race WHERE Race.State="KS"; OR
Background image of page 7

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

View Full Document Right Arrow Icon
Inner  Join Syntax-2 or 3 tables  INNER Joins are used when you want to select only rows with EXACT MATCHES in BOTH tables Used to specify table relationships in FROM command E.g. FROM Table2 INNER JOIN Table1 ON Table2.foreignkey = Table1.primarykey Example if Invoice table has secondary key from Customer FROM Invoice INNER JOIN Customer ON Invoice.CustNo = Customer.CustNo If 3 tables, e.g. Invoice, Customer, Product: FROM ( Invoice INNER JOIN Customer ON Invoice.CustNo = Customer.CustNo) INNER JOIN Product ON Invoice.ProdNo = Product.ProdNo *Note no [brackets] since no spaces in table or attribute names*
Background image of page 8
SQL Practice:  Inner Joins Runner’s database queries 3.
Background image of page 9

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

View Full Document Right Arrow Icon
Image of page 10
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

Page1 / 31

380%20sum11%20Class%2012%20SQL%20full%20set - Objective Be...

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

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