cis245 sql
TRANSCRIPT
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