1 / 22

SQL: Advanced

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.

becky
Download Presentation

SQL: Advanced

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL: Advanced Sylvia Jones Wyoming Department of Employment

  2. 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.

  3. ORDER BY (example) SELECT area, emplab,unemprate FROM labforce WHERE stfips = ’56’ and periodyear = ‘2005’ and period = ’06’ ORDER BY unemprate desc, emplab

  4. 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

  5. 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.

  6. JOINs in SELECT clause (example) SELECT labforce.areatype, labforce.area, areatype.areatyname FROM labforce, areatype WHERE labforce.areatype = areatype.areatype

  7. JOINs in FROM clause • 3 main types of Joins • Inner • Outer Left • Outer Right

  8. 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'

  9. 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'

  10. 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.

  11. 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

  12. 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

  13. Set Functions • Avg • Count • Max • Min • Stdev • Sum • Variance

  14. 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’

  15. 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’

  16. 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'

  17. 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

  18. 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)

  19. 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

  20. 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

  21. Extras (avoiding reserved words) SET QUOTED_IDENTIFIER ON GO SELECT * FROM "table"

  22. 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

More Related