S 6

1
SQL

• ????µa 3

2
subqueries

• This query finds the names of all publishers who
  publish business books
• USE pubs
• SELECT DISTINCT pub_name FROM publishers WHERE
  EXISTS (SELECT FROM titles WHERE pub_id
  publishers.pub_id AND type 'business')
• SELECT distinct pub_name FROM publishers WHERE
  pub_id IN (SELECT pub_id FROM titles WHERE type
  'business')

3
Subqueries

• If a table appears only in a subquery and not in
  the outer query, then columns from that table
  cannot be included in the output (the select list
  of the outer query).
• Statements that include a subquery usually take
  one of these formats
• WHERE expression NOT IN (subquery)
• WHERE expression comparison_operator (subquery)
• WHERE NOT EXISTS (subquery)

4
Examples with Subqueries

• / SELECT statement built using a subquery. /
• SELECT ProductName FROM Northwind.dbo.Products
  WHERE UnitPrice (SELECT UnitPrice FROM
  Northwind.dbo.Products WHERE ProductName 'Sir
  Rodney''s Scones')
• / SELECT statement built using a join that
  returns the same result set. /
• SELECT Prd1.ProductName FROM Northwind.dbo.Produc
  ts AS Prd1 JOIN Northwind.dbo.Products AS Prd2 ON
  (Prd1.UnitPrice Prd2.UnitPrice) WHERE
  Prd2.ProductName 'Sir Rodney''s Scones'

5

• ??µ??????a ap??? a?af????
• Simple Summary Reports

6
GROUP BY

• ? GROUP BY s??t?seta? ?? e???
• SELECT column,SUM(column) FROM table GROUP BY
  column

7
S??a?t?se??

• SUM(expression) - ?T???S??-
• Returns the total of the values in the numeric
  column.
• AVG(expression) - ??S?S ???S-
• Returns the average of the values in the numeric
  column.
• COUNT(expression) - ???T??S ???O?-
• Returns the number of non-null values in the
  column.
• MAX(expression) - ??G?S??-
• Returns the highest value in the column.
• MIN(expression) - ?????S??-
• Returns the lowest value in the column.

8
S??a?t?se?? -Aggregate functions

• ?? s??a?t?se?? a?t?? e?te???? ??a? ?p?????sµ? se
  ??a s????? t?µ?? ?a?e p?st??f??? µ?a t?µ?. ?e
  e?a??es? t?? COUNT, ?? s??a?t?se?? a?????? t??
  t?µ?? null.
• ?? s??a?t?se?? ???s?µ?p?????ta? s???? µe t??
  ??f?as? GROUP BY se ??a SELECT.
• ?p?t??p??ta? ? e?f??se?? se
• The select list of a SELECT statement (either a
  subquery or an outer query).
• A HAVING clause.

9
S??a?t?se?? ?a? GROUP BY

• SELECT type, title_id, avg(price), avg(advance)
  FROM titles GROUP BY type, title_id
• St?? ??sta ped??? t??GROUP BY p??pe? ?a
  pe????f???? ??a ta ped?a t?? select p?? de? e??a?
  s??a?t?se??.
• St? pa?ap??? pa??de??µa, a? ? GROUP BY pe????e?
  µ??? t? 'GROUP BY TYPE', ?a p?????e? sf??µa, p??
  ?a ??e? ?t? t? title_id ß??s?eta? st? ??sta t??
  select a??? ??? st? group. ??a, t? title_id
  p??pe? e?te ?a pa?a??f?e? ap? t? select ? ?a
  p??ste?e? st? group by .

10
GROUP BY

• For example, this statement finds the average
  price and the sum of year-to-date sales, grouped
  by type and publisher ID
• SELECT type, pub_id, 'avg' AVG(price), 'sum'
  sum(ytd_sales) FROM titles GROUP BY type, pub_id
• type pub_id avg
  sum
• ------------ ------ --------------------------
  -----------
• business 0736 11.96
  18722
• business 1389 17.31
  12066
• mod_cook 0877 11.49
  24278
• popular_comp 1389 21.48
  12875
• psychology 0736 45.93
  9564
• psychology 0877 21.59
  375
• trad_cook 0877 15.96
  19566
• UNDECIDED 0877 (null)
  (null)

11
GROUP BY and Null Values

• ?? st? ??sta ped??? t?? group ?p???e? ped?? p??
  pe????e? null t?µ??,t?te t? null ???eta? µ?a ap?
  t?? ?µ?de? t?? group.

12
Choosing Rows with the HAVING Clause

• ? ??f?as? HAVING ??te? s?????e? st? GROUP BY ?p??
  ? WHERE a????ep?d?? µe t? SELECT.
• ?? WHERE ??te? s?????e? p??? t?? ?µad?p???s?
• ?? HAVING efa?µ??eta? sta ap?te??sµata t??
  ?µad?p???s??.
• ? s??ta?? t?? HAVING e??a? pa??µ??a µe t?? WHERE,
  µe t? d?af??? ?t? t? HAVING µp??e? ?a pe????e?
  s??a?t?se??. ?? HAVING µp??e? ?a a?af??eta? se
  ped?a p?? eµfa?????ta? st? ??sta t?? select .

13
Choosing Rows with the HAVING Clause

• This query finds publishers whose year-to-date
  sales are greater than 40,000
• SELECT pub_id, total SUM(ytd_sales) FROM titles
  GROUP BY pub_id HAVING SUM(ytd_sales) gt 40000
• Here is the result set
• pub_id total ------ ----------- 0877 44219

14
Choosing Rows with the HAVING Clause

• To make sure there are at least six books
  involved in the calculations for each publisher,
  this example uses HAVING COUNT() gt 5 to
  eliminate the publishers that return totals for
  fewer than six books
• USE pubs SELECT pub_id, total SUM(ytd_sales)
  FROM titles GROUP BY pub_id HAVING COUNT() gt 5
  Here is the result set
• pub_id total ------ ----------- 0877 44219 1389
  24941

15

• Understanding the correct sequence in which the
  WHERE, GROUP BY, and HAVING clauses are applied
  helps in coding efficient queries
• The WHERE clause is used to filter the rows that
  result from the operations specified in the FROM
  clause.
• The GROUP BY clause is used to group the output
  of the WHERE clause.
• The HAVING clause is used to filter rows from the
  grouped result.

16
ORDER BY

• ? ORDER BY ???s?µ?p??e?ta? ??a ?a ta????µ?se? t?
  ap?t??esµa t?? GROUP BY.
• SELECT pub_id, SUM(advance) AS AmountAdvanced,
  AVG(price) AS AveragePrice FROM pubs.dbo.titles
  WHERE pub_id gt '0800' AND price gt 5 GROUP BY
  pub_id HAVING SUM(advance) gt 15000 AND
  AVG(price) lt 20 ORDER BY pub_id DESC

17
?s??s?

• ?a ß?e?e? ? µ???st? t?µ? ß????? p?? ??e?
  ?ata?????e? st? ß?s?
• ?a ß?e?e? ? µ?s?? ???? t?? ß????? ???e as?e????
• ?a ß?e?e? ? µ?s?, e????st? ?a? µ???st? t?µ?
  ???????? ???e as?e???? t?? te?e?ta?? µ??a
• ?a ß?e?e? ? µ?s? ?????a t?? a?d??? d?aß?t????
  pe?at?? t?? ?at?e???
• ?a ß?e?e? ? µ?s?? ???? ep?s???e?? t?? as?e???
• ?a ß?e?e? p?se? ???????? p???e?? ????? ???e? ??a
  ???e as?e??

18
User Views - ??e??

• ?e? e??a? p?a?µat???? p??a?e?
• ?p?te???? ape?????se?? se p?a?µat????? ?p??a?e?
• ? ???st?? ß??pe? ?a? ?e????eta? t?? ??e?? sa?
  p?a?µat????? p??a?e? ?a? µp??e? ?a ???e? p???e??
  p??? s a?t??
• ? p?a?µat??? ß?s? de ??e???eta? ?a
  a?ad????a???eta? ??a t?? d?af??et???? a????e? t??
  ???e ???st?

19
User Views - ??e??

• G?a ?a est??se? ? ???st?? se s???e???µ??a
  ded?µ??a
• G?a ?a d?e????????? t? d?a?e???s? t?? ded?µ????,
  µ?a p?? ta constraints ????? ?d? te?e?
• ??af??et???? ???ste? µp????? ?a ???s?µ?p????? t??
  ?d?a ß?s? µe p??sa?µ?sµ??? t??p?
• G?a t?? e?a???? ded?µ???? se ???e? efa?µ????
• G?a asf??e?a. ?? ???ste? µp????? ?a a??????? µ???
  ta ded?µ??a p?? ß??p???

20
CREATE/DROP VIEW

• CREATE VIEW viewname As
• Select CLAUSE
• DROP VIEW viewname

21
CREATE VIEW EXAMPLES

• CREATE VIEW titles_view As Select title, type,
  price, pubdate FROM titles
• CREATE VIEW accounts (title, advance, amt_due) AS
  
• SELECT title, advance, price royalty
  ytd_sales
• FROM titles
• WHERE price gt 5
• CREATE VIEW categories (category, average_price)
  AS
• SELECT type, AVG(price) FROM titles
• GROUP BY type

22
?s??s?

• ??e??
• ?? ep?s???e?? ???e as?e?? ??a t?? te?e?ta?? ?????
• ?? fa?µa?e?t???? a????? ap? t? ??at?? ??a t??
  µet??se?? t?? eßd?µ?da?
• ??? as?e??, ? µ???st? t?µ? ???????? t?? µ??a
• ?µ???? ß?????

23
Stored Procedures

• Simple Procedure with a Complex SELECT
• This stored procedure returns all authors (first
  and last names supplied), their titles, and their
  publishers from a four-table join. This stored
  procedure does not have any parameters.
• CREATE PROCEDURE au_info_all AS
• SELECT au_lname, au_fname, title, pub_name
• FROM authors a, titles t, publishers p,
  titleauthor ta
• WHERE a.au_id ta.au_id AND t.title_id
  ta.title_id AND
• t.pub_id p.pub_id
• The au_info_all stored procedure can be executed
  in the following ways
• EXECUTE au_info_all , EXEC au_info_all
• Or, if this procedure is the first statement
  within the batch
• au_info_all

24
Stored Procedures

• Simple Procedure with Parameters
• This stored procedure returns only the specified
  authors (first and last names supplied), their
  titles, and their publishers from a four-table
  join. This stored procedure accepts exact matches
  for the parameters passed.
• CREATE PROCEDURE au_info
• _at_lastname varchar(40), _at_firstname varchar(20)
  AS
• SELECT au_lname, au_fname, title, pub_name
• FROM authors, titles, publishers, titleauthor
• WHERE au_fname _at_firstname AND
• au_lname _at_lastname AND authors.au_id
  titleauthor.au_id AND
• titles.title_id titleauthor.title_id AND
  titles.pub_id publishers.pub_id
• The au_info stored procedure can be executed in
  the following ways
• EXECUTE au_info 'Dull', 'Ann, EXEC au_info
  'Dull', 'Ann'
• EXECUTE au_info _at_lastname 'Dull', _at_firstname
  'Ann'
• EXECUTE au_info _at_firstname 'Ann', _at_lastname
  'Dull
• or, if this procedure is the first statement
  within the batch au_info 'Dull', 'Ann'

25
Stored Procedures

• Simple Procedure with Wildcard Parameters
• This stored procedure returns only the specified
  authors (first and last names supplied), their
  titles, and their publishers from a four-table
  join. This stored procedure pattern matches the
  parameters passed or, if not supplied, uses the
  defaults.
• CREATE PROCEDURE au_info2 _at_lastname varchar(30)
  'D',
• _at_firstname varchar(18) ' AS
• SELECT au_lname, au_fname, title, pub_name
• FROM authors a, titles t, publishers p,
  titleauthor ta
• WHERE au_fname LIKE _at_firstname AND au_lname LIKE
  _at_lastname
• AND a.au_id ta.au_id AND t.title_id
  ta.title_id AND t.pub_id p.pub_id
• The au_info2 stored procedure can be executed in
  many combinations. For simplicity, only a few
  combinations are shown.
• EXECUTE au_info2, EXECUTE au_info2 'Hunter',
  'Sheryl
• EXECUTE au_info2 'Wh, EXECUTE au_info2
  _at_firstname 'A,
• EXECUTE au_info2 'CKarsOEn', EXECUTE
  au_info2 'H', 'S'

26
Stored Procedures

• Use OUTPUT Parameters
• OUTPUT parameters are used to allow an external
  procedure, a batch, or SQL statements access to a
  value set during the procedure execution. In this
  example, a stored procedure (titles_sum) is
  created and allows one optional input parameter
  and one output parameter.
• First, create the procedure
• CREATE PROCEDURE titles_sum _at_title varchar(40)
  '', _at_sum money OUTPUT AS SELECT 'Title Name'
  title FROM titles WHERE title LIKE _at_title
• SELECT _at_sum SUM(price) FROM titles WHERE title
  LIKE _at_title
• Next, use the OUTPUT parameter with
  control-of-flow extensions. Note that the OUTPUT
  variable must be defined during the table
  creation as well as during use of the variable.
  When using a variable, the parameter name and
  variable name do not need to match however, the
  datatype and parameter positioning must match
  (unless _at_sum variable is used).

27
?s??s?

• ?????ta? t? ???µa as?e????, ?a ß??s?eta? se p??a
  ?µe??µ???a pa???s?ase t? µ???st? ß???? ?a? p???
  ?ta? a?t?.
• ?????ta? t? ???µa as?e???? ?a ß??s?eta? e?? ?a?
  p?te ??a?e ? ??at??? ???????? p???e?? (d?a???se??
  , fa?µa?e?t???? a?????) ??a t?? as?e?? a?t? ?a?
  p??e? ?ta? a?t??.