SQL Advanced

Document Sample
SQL Advanced Powered By Docstoc
					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’
   Wyoming county level information only
  SELECT *
  FROM labforce
  WHERE areatype = ’04’ or stfips = ’56’
   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’
 All information for other states except county level information
            SELECT *
            FROM labforce
            WHERE areatype <> ’04’ or stfips <> ’56’
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