SQL

1
SQL

• Dagmar Kusiak

2
Chapter Objectives

• Intro to SQL (structured query language)
• DML
• SELECT
• INSERT
• UPDATE
• DELETE

3
DML continued

• SELECT
• retrieve information from the database

4
DML continued

• SELECT Example
• SELECT ltcolumn1, column2..gt FROM lttablenamegtWHERE
  ltconstraintgt

5
DML continued

• Retrieving basic table and column info
• Aliases
• Concatenation
• Cast
• Convert
• Aggregate functions
• AVG()
• COUNT()
• COUNT_BIG()
• MAX()
• MIN()
• SUM()

• Other functions
• ISNULL
• Joins, subqueries
• Other
• Distinct
• Order by
• Group by
• Having
• Like
• Not in / ltgt
• INTERSECT
• UNION

6
Retrieving table and column info
7
Retrieving table and column info continued
8
Retrieving table and column info continued
9
Table alias
10
CAST and CONVERT

• Definition
• converting from one data type to another
• Used when
• You want to concatenate
• Concatenation only takes char and varchar formats.

11
CAST and CONVERT
12
CAST and CONVERT
13
CAST and CONVERT continued
14
CAST and CONVERT continued
15
Aggregate functions

• AVG returns the average
• COUNT returns the number of rows (int) in a
  table
• COUNT_BIG returns number of rows (bigint) in a
  table
• MAX returns the maximum value
• MIN returns the minimum value
• SUM returns the total value

16
Aggregate functions continued
17
Aggregate functions continued

• Examples
• Return the product with the highest unit price
• Return the total unit price of each category
  beverages, condiments, confections etc.

18
Aggregate functions continued
19
Aggregate functions continuedWhy doesnt the
below query work?
20
Fixed the query
21
Aggregate functions continued
22
Aggregate functions continued
23
OTHER

• Examples
• 1. Return all products that begin with G
• 2. Returns all products that end with G
• 3. Return all products that have G in their name
• 4. Return all products named Chai
• 5. Return unit prices
• 6. Return all unit prices between 10.00 and
  15.00
• 7. Return all the categories with more than 10
  products
• 8. Return the all products except for the
  beverages and produce

24
OTHER 1
25
OTHER 2
26
OTHER 3
27
OTHER 4
28
OTHER 5 (a)
29
OTHER 5(b)
30
OTHER 6 (a)
31
OTHER 6(b)
32
OTHER 7
33
OTHER 8 (a)
34
OTHER 8(b)
35
INSERT

• INSERT Example
• INSERT INTO lttablenamegt (ltcolumn1, column2,)
  VALUES (ltvalue1, value2gt)
• INSERT row in the EMPLOYEE table
• INSERT INTO EMPLOYEE (EmployeeNumber, Name,
  Phone, Department) VALUES (100, Mary Jacobs,
  285-8879, Accounting)
• INSERT INTO EMPLOYEE VALUES (100, Mary Jacobs,
  285-8879, Accounting)

36
INSERT continued
37
UPDATE

• UPDATE
• Change a row of data

38
UPDATE continued

• UPDATE Example
• UPDATE lttablenamegt SET ltcolumnname valuegt WHERE
  ltconstraintgt
• Change Mary Jacobs EmployeeNumber from 100 to 200
• UPDATE EMPLOYEE SET EmployeeNumber 200 where
  Name Mary Jacobs

39
DELETE

• DELETE
• delete a row of data

40
DELETE continued

• DELETE Example
• DELETE lttablenamegtWHERE ltcolumnname valuegt
• DELETE the row with EmployeeNumber 200
• DELETE EMPLOYEE WHERE EmployeeNumber 200