SQL Lesson 2

1
SQLLesson 2

• ?????a ???ßa?d?

2
??sa???? ?ed?µ???? st??? p??a?e? t?? ß?s??

• ??t??? INSERT
• ??sa???? se ??a ta ped?a INSERT table_name
  VALUES (c1,c2..)
• ??sa???? se s???e???µ??a ped?a insert into
  "tablename (first_column,...last_column)
  values (first_value,...last_value)

3
INSERT EXAMPLES

• INSERT S VALUES (1,ME,NewYork)
• INSERT into S (ID_S , S_NAME) VALUES (2,you)
• insert into employee (first, last, age,
  address, city, state) values ('Luke', 'Duke',
  45, '2130 Boars Nest', 'Hazard Co', 'Georgia')
• INSERT authors (au_id, address) SELECT from
  newauthors

4
?s??s?

• ?a e?sa????? st???e?a st??? p??a?e? t?? ß?s?? p??
  ??e? d?µ???????e?.

5
??a???s?/e??µ???s? t?µ??

• ??t??? UPDATE
• update "tablename"
• set "columnname" "newvalue","nextcolumn"
  "newvalue2" ...
• where "columnname" OPERATOR "newvalue"
  andor "column" OPERATOR "newvalue"

6
UPDATE Examples

• UPDATE publishers SET city 'Atlanta', state
  'GA
• UPDATE titles SET price price 2
• UPDATE authors SET state 'PC', city 'Bay
  City' WHERE state 'CA' AND cityOakland
• UPDATE publishers SET pub_name NULL

7
S?????e? where...

• ?? s?????e? a?t?? a?af????ta? se ped?a t??
  p??a?a.
• ?? p???e?? (delete, update) ?aµß????? ???a µ???
  st?? e???af?? p?? p?????? t?? s?????e? a?t??

8
?s??s?

• ???a?? t??ef???? t?? as?e???? µe ??d???
• ???a?? t?? s?????? ep?s?e??? , e?sa????

9
??a??af? e???af??

• ??t??? DELETE DELETE FROM table_name WHERE
  conditions clause
• conditions clause "columnname" OPERATOR
  "newvalue" andor "column" OPERATOR "newvalue"

10
DELETE Examples

• delete from employee
• S?µe??s? ????????????S ??? S??T??? where...
  ???G??F????? ???S ?? ?GG??F?S ??? ??????!
• delete from employee where lastname 'May
• delete from employee where firstname 'Mike' or
  firstname 'Eric
• delete titles FROM authors, titles, titleauthor
  WHERE titles.title_id titleauthor.title_id AND
  authors.au_idtitleauthor.au_id AND city SAN
  FRANSISCO

11
?s??s?

• ?a d?a??afe? ? as?e??? ?a? ??e? ?? ep?s???e??
  t??
• ?a d?a??af??? ?? ???????? p???e?? t?? s?µe?????
  ?µe??µ???a?

12
S?????e? 1

• ????µ?t???? p???e?? ?a? s?????se??
• In this example, the WHERE clause affects only
  those titles that have year-to-date sales more
  than twice the advance given
• WHERE advance 2 gt ytd_sales

13
S?????e? 2

• Grouping Boolean Expressions
• Using parentheses always takes precedence over
  other operators. Without the parentheses, the
  following WHERE clause would have totally
  different meaning because AND takes a higher
  precedence than OR.
• This example affects only books with an advance
  greater than 5500 and are either business or
  psychology books. If the parentheses were not
  included, the WHERE clause would affect all
  business books OR psychology books that have an
  advance greater than 5500.
• WHERE (type 'business' OR type 'psychology')
  AND
• advance gt 5500

14
S?????e? 3

• Between Condition
• This example returns all titles with year-to-date
  sales between and including 4095 through 12000.
• WHERE ytd_sales BETWEEN 4095 AND 12000

15
S?????e? 4

• Wildcard with the NOT LIKE Condition
• In this example, only phone numbers that do not
  begin with the 415 prefix are affected in the
  results set
• WHERE phone NOT LIKE '415'
• Specific Character Wildcards with the LIKE
  Condition
• This example finds the rows for authors named
  Carson, Carsen, Karsen, and Karson
• WHERE au_lname LIKE 'CKarseon'

16
S?????e? 5

• The string can include these wildcard characters
• Wildcard Meaning
• Any string of zero or more characters
• _  (underscore) Any single character
• Any single character within the specified
  range (a-f) or set (abcdef)
• Any single character not within the specified
  range (a-f) or set (abcdef)
• Symbol Meaning
• LIKE '5' 5 followed by any string of 0 or more
  characters
• LIKE '5' 5
• LIKE '_n' an, in, on, and so on
• LIKE '_n' _n
• LIKE 'a-cdf' a, b, c, d, or f
• LIKE '-acdf' -, a, c, d, or f
• LIKE ' '

17
S?????e? 6

• IN Condition
• This example finds the rows in which the state is
  California (CA), Indiana (IN), or Maryland (MD).
• WHERE state IN ('CA', 'IN', 'MD')

18
?p????? e???af??

• SELECT column_list FROM table_list WHERE
  search_conditions
• Select ALLDISTINCT select_list into
  newtablefrom table_list Where conditionGroup
  by column Order by column

19
?p?????? Select

• Select List
• for all columns select from S
• column names c1,c2 select ID_S, S_NAME from S
• expressions and computed values (,-,/,,)
  select title_id, 2ytd_sales from titles
• DISTINCT eliminates duplicates from the results
  of select. Select au_id from
  titleauthor Select DISTINCT au_id from
  titleauthor
• FROM
• from table1,
• from table1, table2, view3
• Use table alias select p.pub_id, p.pub_name
  from publishers p

20
WHERE

• Comparison operations (,lt,lt,gt,gt,!lt,!gt)where
  advance 2 gt ytd_sales price
• Ranges BETWEEN, NOT BETWEENwhere ytd_sales
  between 4096 and 12000
• LISTS IN, NOT IN where state in (CA, IN,
  MD)
• Pattern Matches LIKE , NOT LIKE where phone
  like 415, where name like ckan-ron
• IS NULL, IS NOT NULL where advance IS NULL
• Logical operations (AND, OR, NOT) where advance
  lt 1500 and (price gt 1000 or advance lt 10)
• Nested queries select au_lname , au_fname from
  authors where au_id IN (select au_id from
  titleauthor where royaltyper lt 50)

21
Order By

• order by col1 ascdesc, col2, col3
• default is ASC
• select pub_id, type title_id, price from titles
  order by pub_id desc, type,price

22
SELECT EXAMPLES 1

• Simple SELECT All Rows, All Columns SELECT
  FROM publishers
• Simple SELECT Subset of Columns, All Rows
  SELECT pub_id, pub_name, city, state FROM
  publishers
• Simple SELECT Subset of Rows, Subset of
  ColumnsSELECT pub_id, total sum (ytd_sales)
  FROM titles WHERE advance lt 10000 AND ytd_sales
  IS NOT NULL

23
GROUP BY

• Divides a table into groups by column name or by
  the results of computed columnsSelect type,
  avg(advance), sum(ytd_sales) from titles group by
  type order by avg(advance)
• HAVING sets conditions for the group by.
  Select type from titles group by type having
  count() gt1 select pub_id , SUM(advance),
  AVG(price) from titles group by pub_idhaving
  SUM(advance) gt 1500 and AVG(price) lt20

24
GROUP BY

• SELECT type, title_id, avg(price), avg(advance)
  FROM titles GROUP BY type, title_id
• You must include in the GROUP BY list all
  non-aggregates in the select list.
• In the example above, if the GROUP BY contained
  only 'GROUP BY TYPE', an error would result,
  stating that title_id is contained in the select
  list but not in the grouping. Hence, title_id
  would either need to be removed from the select
  list or added to the group by list.

25
GROUP BY

• You can list more than one column in the GROUP BY
  clause to nest groups ¾ that is, you can group a
  table by any combination of columns. 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)

26
S??des? p?????? - JOIN

• CROSS JOIN Is the cross product of two tables.
  Returns the same rows as if no WHERE clause was
  specified in an old-style join.
• INNER Specifies that all inner rows be returned.
  Discards unmatched rows.
• LEFT OUTER Specifies that all left outer rows
  be returned. Specifies that all rows from the
  left table that did not meet the condition
  specified be included in the results set, and
  output columns from the other table be set to
  NULL.
• RIGHT OUTER Specifies that all rows from the
  right table that did not meet the condition
  specified will be included in the results set,
  and output columns that correspond to the other
  table be set to NULL.
• FULL OUTER If a row from either table does not
  match the selection criteria, specifies the row
  be included in the results set and its output
  columns that correspond to the other table be set
  to NULL.

27

• select firstname, lastname , v_date from
  patients, patient_visits where patients.p_idpatie
  nt_visits.p_id and v_date gt 5/1/2002

28
JOIN EXAMPLES

• Use Left Outer Join
• This example joins two tables on au_id and
  preserves the unmatched rows from the left table.
  The authors table is matched with the titleauthor
  table on the au_id columns in each table. All
  authors appear in the results set, whether or not
  they have published any books.
• SELECT authors.au_lname, authors.au_fname,
  titleauthor.title_id
• FROM authors LEFT OUTER JOIN titleauthor
• ON authors.au_id titleauthor.au_id

29
JOIN EXAMPLES

• Use Inner Join with Three Tables
• This example joins three tables authors,
  titleauthors and titles. The results set contains
  a list of authors and royalties paid to date.
• SELECT authors.au_fname, authors.au_lname,
  sum(titles.royalty titles.ytd_sales/100)
• FROM authors
• JOIN titleauthor ON authors.au_idtitleauthor.au_i
  d
• JOIN titles ON titleauthor.title_id
  titles.title_id
• GROUP BY authors.au_lname, authors.au_fname
• ORDER BY authors.au_lname

30
JOIN EXAMPLES

• Use Full Outer Join
• This example returns the book title and its
  corresponding publisher in the titles table. It
  also returns any publishers who have not
  published books listed in the titles table, and
  any book titles with a publisher other than the
  one listed in the publishers table.
• SELECT titles.title, publishers.pub_name
• FROM publishers FULL OUTER JOIN titles
• ON titles.pub_id publishers.pub_id
• WHERE titles.pub_id IS NULL OR publishers.pub_id
  IS NULL

31
JOIN EXAMPLES

• Use Right Outer Join
• This example joins two tables on pub_id and
  preserves the unmatched rows from the right
  table. The publishers table is matched with the
  titles table on the pub_id column in each table.
  All publishers appear in the results set, whether
  or not they have published any books.
• SELECT publishers.pub_id, titles.title,
  titles.title_id
• FROM titles RIGHT OUTER JOIN publishers
• ON publishers.pub_id titles.pub_id

32
JOIN EXAMPLES

• Use Cross Join
• This example returns the cross product of the two
  tables authors and publishers. A list of all
  possible combinations of au_lname rows and all
  pub_name rows are returned.
• SELECT au_lname, pub_name
• FROM authors CROSS JOIN publishers

33
JOIN EXAMPLES

• SELECT au_fname, au_lname, pub_name FROM authors,
  publishers WHERE authors.city publishers.city
• SELECT au_fname, au_lname, pub_name FROM authors,
  publishers WHERE authors.city publishers.city
• SELECT au_fname, au_lname, pub_name FROM authors,
  publishers WHERE authors.city publishers.city

34
Aggregate Functions

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

35
Other Functions

• Date Functions Date functions compute datetime
  values and their components, dataparts. These are
  the date functions
• DATEADD DATENAME GETDATE
• DATEDIFF DATEPART
• Mathematical Functions Mathematical functions
  perform operations on numeric data. These are the
  mathematical functions
• ABS DEGREES RAND ACOS EXP ROUND
• ASIN FLOOR SIGN ATAN LOG SIN
• ATN2 LOG10 SQRT CEILING PI TAN
• COS POWER COT RADIANS

36
Other Functions

• String Operations
• LTRIM SOUNDEX
• ASCII PATINDEX SPACE
• CHAR REPLICATE STR
• CHARINDEX REVERSE STUFF
• DIFFERENCE RIGHT SUBSTRING
• LOWER RTRIM UPPER