220 likes | 300 Views
SQL: Advanced. Sylvia Jones Wyoming Department of Employment. ORDER BY . If a SELECT statement has no ORDER BY clause, the sequence in which the rows in the result of the statement are presented is unpredictable. You are allowed to sort on each column in the SELECT clause or on expressions.
E N D
SQL: Advanced Sylvia Jones Wyoming Department of Employment
ORDER BY • If a SELECT statement has no ORDER BY clause, the sequence in which the rows in the result of the statement are presented is unpredictable. • You are allowed to sort on each column in the SELECT clause or on expressions.
ORDER BY (example) SELECT area, emplab,unemprate FROM labforce WHERE stfips = ’56’ and periodyear = ‘2005’ and period = ’06’ ORDER BY unemprate desc, emplab
ORDER BY (example 2) SELECT * FROM industry WHERE stfips = ’56’ and periodyear = ‘2004’ and period = ‘02’ and indcode like = ’21%’ ORDER BY (m1emp + m2emp + m3emp)/3
JOINs • Joins are used to merge data of two or more tables into one table. • Joins can occur in either the SELECT statement or in the FROM statement.
JOINs in SELECT clause (example) SELECT labforce.areatype, labforce.area, areatype.areatyname FROM labforce, areatype WHERE labforce.areatype = areatype.areatype
JOINs in FROM clause • 3 main types of Joins • Inner • Outer Left • Outer Right
JOINs in FROM clause (example) SELECT industry.indcode, industry.avgemp, areatype.areatyname, naiccode.naicstitll FROM industry left outer join areatype on (industry.areatype = areatype.areatype) inner join naiccode on (industry.indcode = naiccode.naicscode) WHERE period = '04' and ownership = '50'
JOINs in FROM clause (example with aliases) SELECT i.indcode, i.avgemp, a.areatyname, n.naicstitll FROM industry as i left outer join areatype as a on (i.areatype = a.areatype) inner join naiccode as n on (i.indcode = n.naicscode) WHERE period = '04' and ownership = '50'
And/Or/Not • A WHERE clause may contain multiple conditions if the logical operators And, Or, and Not are used. • If the clause contains more than one operator, the evaluation is performed from left to right.
And/Or/Not (example) SELECT * FROM labforce WHERE areatype = ’04’ and stfips = ’56’ SELECT * FROM labforce WHERE areatype = ’04’ or stfips = ’56’ Wyoming county level information only All Wyoming information as well as county level information for all other states
And/Or/Not (example 2) SELECT * FROM labforce WHERE areatype <> ’04’ and stfips <> ’56’ SELECT * FROM labforce WHERE areatype <> ’04’ or stfips <> ’56’ All information for other states except county level information All Wyoming information except county level information, as well as all information for all other states including county level data
Set Functions • Avg • Count • Max • Min • Stdev • Sum • Variance
Set Functions • If a SELECT clause contains a set function, the entire SELECT statement yields only one column as an end result. SELECT count(*) FROM industry WHERE indcode = ’21’
Set Functions (example) SELECT distinct(periodyear) FROM labforce SELECT count(distinct(substring(indcode,1,1))) FROM industry SELECT min(unemprate) as lowest, max(unemprate) as highest FROM labforce WHERE periodyear = ‘2004’ and areatype = ’01’
Set Functions (example) SELECT count(*) FROM industry WHERE mnth1emp = (SELECT min(mnth1emp) FROM industry) SELECT indcode FROM industry WHERE avgwkwage/(SELECT avg(avgwkwage) FROM industry) > 3 and period = '04' and ownership = '50'
GROUP BY • The GROUP BY clause groups rows on the basis of similarities between them. • The GROUP BY clause may contain one or more column specifications. • The sequence of the columns has no effect on the end result of the statement. • Any set function can be used in the SELECT clause as long as that function operates on a column that is not grouped. • If a SELECT statement has a GROUP BY clause, any column specification must occur within a set function or the GROUP BY clause
GROUP BY (example) SELECT substring(indcode,1,2), count(*) FROM industry WHERE period = '03' GROUP BY substring(indcode,1,2) ORDER BY substring(indcode,1,2)
GROUP BY (HAVING) • The HAVING clause allows you to select groups on the basis of their group properties. • HAVING is similar to WHERE, except you can use set functions. SELECT substring(indcode,1,6) as Naics_6, avg(avgwkwage) as avgwage FROM industry WHERE period = '02' GROUP BY substring(indcode,1,6) HAVING count(*) >= 4 ORDER BY naics_6
Extras (Minimize transaction log) use master go sp_detach_db tonyworking, true go xp_cmdshell 'del C:\sylvia\sqlserverfiles\sylviaworking_log.ldf', no_output go sp_attach_db @dbname = N‘sylviaworking', @filename1 = N'c:\sylvia\sqlserverfiles\sylviaworking_data.mdf' go
Extras (avoiding reserved words) SET QUOTED_IDENTIFIER ON GO SELECT * FROM "table"
Contact Information Sylvia Jones Wyoming Department of Employment Research & Planning 246 South Center St. Second Floor Casper, WY 82601 sjones2@state.wy.us (307)473-3814