cis245 sql

CIS-245

Used to display data and calculations Main tool for making use of the data

that’s stored in tablesSELECT can be used as part of other

commands

SELECT {field list}: What’s returned FROM {data source}: Which table to use WHERE {criteria}: Which rows to

include GROUP BY {field list}: How to

summarize HAVING {field list}: Which groups to

include when grouping ORDER BY {field list}: How to sort

1. FROM – get data from original source(s)

2. WHERE – limit rows to work with3. GROUP BY – create groups4. HAVING – limit groups5. SELECT – specify what to return6. ORDER BY – specify sorting

SELECT starts statement Followed by field list

Fields can refer to table fields or calculations created as part of query

SELECT can be used by itself:SELECT GetDate()

Describes where to find the data Can refer to a table or view

View is a pre-defined select query, considered a “virtual” table

Basic query typically has SELECT and FROMSELECT * FROM Titles

INTO – write results into table DISTINCT – eliminate repeating value(s)

or rows AS – name or rename a table or column * - include all fields from source

Optional clause can write data from one or more tables into another (new) tableAccess uses this for Append queries

Typically used for ArchivingTo speed processing by creating temporary

or summary tablesTo organize for reporting

Distinct eliminates repeating rows or valuesUsed in SELECT clause

SELECT DISTINCT Lastname FROM Persons

Ensures that a Lastname is returned once, regardless of how may times that Lastname exists in persons

AS is used to create a field name for a calculation or to rename an existing field Also referred to as an aliasUse is optional

Rename fields in tblPersons:SELECT prs_txtLastname AS LastName FROM

tblPersonsSELECT prs_txtFirstName FirstName FROM

tblPersons

* represents all fields in data sourceResults may differ from what’s expected if

fields are added, deleted or order changed

SELECT * FROM tblCustomers

WHERE limits which rows are included in the result

Criteria evaluate to True/FalseTrue means row will be used for result, false

means row will be excludedSometimes ‘Predicates’ is used in place of

criteria (a predicate is a characteristic that’s true about a row)

Relational (=, >, <,<>,>=,<=) Like – compares text patterns

Wildcards One Character: _ (underscore) Any combination: %

Between – compares to range Is – compares true/false/null In – value is in a list Exists – whether there’s a value

SELECT *FROM tblRatesWHERE rts_curAmount > 20

SELECT *FROM tblAddressesWHERE add_txtState <> ‘wa’

If have more than one test use AND/OR to tie tests togetherAND: All parts must evaluate to True for a

row to be includedOR: Any part evaluating to True means a

row is includedNo Precedence

NOT returns inverseNot False returns True

SELECT * FROM Titles WHERE Price > 20 AND Title LIKE ‘%SQL%’

SELECT *FROM TitlesWHERE Not Contract

Allows data from tables to be summarizedFind rows that share a common value

Can discover information about the group using aggregate functionsCountAverageMax

Count number of agreements by Customer:SELECT Count(*) AgreementCount,

agr_lngCustomerIDFROM tblAgreementsGROUP BY agr_lngCustomerID

Determines which groups to include Use Criteria similar to Where clause

Test characteristics of group using HAVING Tests involve aggregate functions which

summarize characteristics of rowsUse WHERE to determine which particular

rows to include in result

Display Employee ID’s of those Employees who have received more than 5 payments:SELECT pmt_lngEmployeeIDFROM tblPaymentsGROUP BY pmt_lngEmployeeIDHAVING Count(*) > 5

Provides means to sort results by a column (actual or calculated)

Default sort order is Ascending

ORDER BY pub_dateORDER BY au_lname DESC