380%20sum11%20Class%2010%20SQL%20HO

380%20sum11%20Class%2010%20SQL%20HO -...

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

View Full Document Right Arrow Icon
Click to edit Master subtitle style Information Retrieval - SQL Objective : Be able to Write Queries using Structured Query Language (SQL) Know when to use Inner Join vs Outer Join queries 1. Introduction to Structured Query Language (SQL) Syntax Inner Joins vs Outer Joins Full Outer Joins
Background image of page 1

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

View Full DocumentRight Arrow Icon
Muddiest Points Queries – what if refer to another query that has no related records (i.e. null values)? » Use = Nz([attribute], 0) to convert n ulls to z eroes e.g. = Nz([Amount Due], 0) - Nz([Amount Paid], 0) see Antiques example w NZ Access lab 2 class files
Background image of page 2
‘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 3

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

View Full DocumentRight Arrow Icon
Runner Database
Background image of page 4
SQL Practice (Single table) Runner’s database queries 1. List the names and phone numbers of all runners
Background image of page 5

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

View Full DocumentRight 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
Image of page 7
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 08/07/2011 for the course ACC 380 taught by Professor Harkness during the Spring '11 term at University of Michigan-Dearborn.

Page1 / 21

380%20sum11%20Class%2010%20SQL%20HO -...

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

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