Structured Query Language SQL and the SELECT, INSERT, UPDATE and DELETE Statements

1
Structured Query Language (SQL)and theSELECT,
INSERT, UPDATE and DELETE Statements

• Alan Schneider

2
Overview

• SQL Definition
• SQL Languages
• DCL, DML and DDL
• Joins
• Outer
• Cartesian
• SELECT Statement
• Relational Operators
• CREATE
• INSERT
• UPDATE
• DELETE

3
SQL Is

• A computer language designed to get information
  from a relational database
• A Declarative Language
• Describe the information you want versus the
  procedure that will be applied to the data
• WHAT vs. HOW

4
SQL Languages

• Data Control Language (DCL)
• Grant, Revoke ... DML, Execute to users and roles
  on database objects
• Data Manipulation Language (DML)
• Select, Insert, Update, Delete ... data in tables
• Data Definition Language (DDL)
• Create, Drop ... users, tables, views, indexes,
  sequences, synonyms, etc.

5
Join

• Strength of Relational Databases
• Used to return column values from two or more
  tables
• SELECT fname
• ,lname
• ,dept_name FROM employee emp
• ,department dept
• WHERE emp.dept_no dept.dept_no

6
Special JoinANSI Standard Outer

• SELECT emp.fname
• ,emp.lname
• ,dept.dept_no
• ,dept.dept_name
• FROM department dept
• LEFT OUTER JOIN employee emp
• ON dept.dept_no emp.dept_no
• ORDER
• BY dept.deptno

7
Special Join NON-Standard Outer (Oracle)

• SELECT emp.fname
• ,emp.lname
• ,dept.dept_no
• ,dept.dept_name
• FROM employee emp
• ,department dept
• WHERE emp.dept_no dept.dept_no()

8
Special JoinCartesian

• Concatenation of one record in one table with
  every record of a second table
• Happens when a where condition does not join two
  or more tables.
• SELECT emp.fname
• ,emp.lname
• ,dept.dept_name FROM employee emp
• ,department dept

• 99.999999. . . . . . . percent of the time this
  is a VERY BAD thing to happen

9
Special JoinCartesian
10
Select Statement

• A Select statement is often called a "query."
  These two terms ("Select statement" and "query,)
  are used interchangeably.
• The term "Select Statement" emphasizes the syntax
  of the SQL command.
• The term Query" emphasizes the purpose of the
  command.
• The clauses must be written in the following
  order
• SELECT ltcolumnsgt
• FROM lttablesgt
• WHERE ltrecord level conditionsgt
• ORDER BY / GROUP BY ltcolumnsgt
• HAVING ltGroup By level conditionsgt

11
SELECT Clause

• Lists the columns you want to appear in the
  result table.
• Columns can be listed in any order.
• A comma appears after each column name
• Except for the one just before the FROM clause

12
SELECT Clause

• Column Names contain no spaces.
• By writing last_name, with an underscore, you are
  telling the computer that this is the name of a
  single column.
• If you wrote last name with a space, the computer
  would try to find a column named last, giving it
  the column Alias of name.
• To create an Alias with spaces put the name in
  double quotes just after the column name (no
  comma).
• SELECT last_name Last Name
• ,first_name First Name
• ,dept_name Dept Name
• ,salary Salary
• FROM

13
FROM Clause

• Names the table(s) or view(s) the data comes from
• A view is normally created to select data from
  two or more tables into a new data set. Gives
  the user the illusion they are selecting data
  from one table.
• You can also give a table an Alias, however they
  CANNOT contain spaces
• FROM employee emp
• ,department dept
• WHERE

14
WHERE Clause

• Determines which rows are returned by
• Joining two or more tables
• Establishing data value restriction/selection
  criteria
• WHERE emp.dept_no dept.dept_no
• AND dept.dept_no between 20 and 30
• OR dept.dept_no between 45 and 55
• ORDER BY or GROUP BY

15
ORDER BY Clause

• Sorts the rows of the result table in
  alphabetical order by the specified column or
  columns.
• This can be done either by ASCENDING (ASC) or
  DESCENDING (DESC) order.
• ORDER BY emp.last_name desc
• ,emp.first_name asc

16
GROUP BY Clause

• Allows the summarization of data for the defined
  group.
• All Non-Aggregate columns in the SELECT clause
  MUST be in the GROUP BY clause
• SELECT emp.dept_no
• ,SUM(emp.salary)
• FROM employee emp
• GROUP
• BY emp.dept_no
• HAVING
• Other commonly used aggregate functions include,
  COUNT, AVG, MIN, and MAX

17
HAVING Clause

• Eliminates groups of data after the row
  processing has taken place.
• HAVING SUM(emp.salary) gt 10000

18
Putting It All Together
DEPARTMENT EMPCOUNT ---------- --------
10 a 10 b 10 c 10 d
10 e 10 f 20 g 20 h
30 i 30 j 40 k 40 l
40 m
SELECT dept.dept_no Department,
COUNT(emp.emp_no) EmpCount FROM employee emp,
department dept WHERE emp.dept_no
dept.dept_no GROUP BY dept.dept_no HAVING
COUNT(emp.emp_no) gt 2
DEPARTMENT EMPCOUNT ---------- ---------
10 6 40 3
19
All Columns with All Records From a Single Table

• SELECT
• FROM lttable_name_1gt tb1
• SELECT ltcolumn_name_1gt
• ,ltcolumn_name_2gt
• ,...
• ,...
• FROM lttable_name_1gt tb1

The first example uses the asterisk wild card to
return all of the columns where as the second
example all of the columns are explicitly listed
20
Subset of Columns with All Records From a Single
Table

• SELECT ltcolumn_name_1gt
• ,ltcolumn_name_2gt
• FROM lttable_name_1gt tb1

21
WHERE Clause Using 1 Table

• SELECT ltcolumn_name_1gt
• ,ltcolumn_name_2gt
• FROM lttable_name_1gt tb1
• WHERE tb1.ltcolumn_name_1gt ltvaluegt

22
WHERE Clause Using 2 Tables and AND

• SELECT tb1.ltcolumn_name_1gt
• ,tb2.ltcolumn_name_3gt
• FROM lttable_name_1gt tb1
• ,lttable_name_2gt tb2
• WHERE tb1.ltcolumn_name_1gt tb2.ltcolumn_name_2gt
  AND tb1.ltcolumn_name_1gt ltvaluegt

23
WHERE Clause Using 2 Tables and OR

• SELECT tb1.ltcolumn_name_1gt
• ,tb2.ltcolumn_name_3gt
• FROM lttable_name_1gt tb1
• ,lttable_name_2gt tb2
• WHERE tb1.ltcolumn_name_1gt tb2.ltcolumn_name_2gt
  AND ( tb1.ltcolumn_name_1gt ltvaluegt
•   OR tb1.ltcolumn_name_1gt ltvaluegt )

24
WHERE ClauseUsing IN

• SELECT tb1.ltcolumn_name_1gt
• ,tb2.ltcolumn_name_3gt
• FROM lttable_name_1gt tb1
• ,lttable_name_2gt tb2
• WHERE tb1.ltcolumn_name_1gt tb2.ltcolumn_name_2gt
  AND tb1.ltcolumn_name_1gt IN (ltvaluegt,ltvaluegt)

25
WHERE ClauseUsing NOT IN

• SELECT tb1.ltcolumn_name_1gt
• ,tb2.ltcolumn_name_3gt
• FROM lttable_name_1gt tb1
• ,lttable_name_2gt tb2
• WHERE tb1.ltcolumn_name_1gt tb2.ltcolumn_name_2gt
  AND tb1.ltcolumn_name_1gt NOT IN
  (ltvaluegt,ltvaluegt)

26
WHERE Clause Using BETWEEN

• SELECT tb1.ltcolumn_name_1gt
• ,tb2.ltcolumn_name_3gt
• FROM lttable_name_1gt tb1
• ,lttable_name_2gt tb2
• WHERE tb1.ltcolumn_name_1gt tb2.ltcolumn_name_2gt
• AND tb1.ltcolumn_name_1gt between ltvaluegt
• and ltvaluegt

27
WHERE Clause Using LIKE

• SELECT tb1.ltcolumn_name_1gt
• ,tb2.ltcolumn_name_3gt
• FROM lttable_name_1gt tb1
• ,lttable_name_2gt tb2
• WHERE tb1.ltcolumn_name_1gt tb2.ltcolumn_name_2gt
• AND tb1.ltcolumn_name_1gt LIKE ltvaluegt
  
• ltvaluegt will return what?
• ltvaluegt will return what?
• ltvaluegt will return what?
• Note (percent) is Oracles wild card
• (asterisk) is MS Access wild card

28
WHERE Clause Using EXISTS and NOT EXISTS

• SELECT tb1.ltcolumn_name_1gt
• ,tb1.ltcolumn_name_2gt
• FROM lttable_name_1gt tb1
• WHERE EXISTS     (SELECT x
•        FROM lttable_name_2gt tb2
•         WHERE tb1.ltcolumn_name_1gt
  tb2.ltcolumn_name_1gt)

To reverse the results put the word NOT just
before EXISTS WHERE NOT EXISTS
29
WHERE Clause Using Sub-Queries

• SELECT tb1.ltcolumn_name_1gt
• ,tb1.ltcolumn_name_3gt
• FROM lttable_name_1gt tb1
• WHERE tb1.ltcolumn_name_1gt IN     
  (SELECT tb2.ltcolumn_name_1gt
•        FROM lttable_name_2gt tb2
•         WHERE tb2.ltcolumn_name_2gt
  ltvaluegt)

30
From Clause Using In-Line Views

• SELECT tb1.ltcolumn_name_1gt
• ,tb3.ltcolumn_name_2gt
• FROM lttable_name_1gt tb1
• ,(SELECT tb2.ltcolumn_name_1gt
• ,tb2.ltcolumn_name_2gt
• FROM lttable_name_1gt tb2) tb3
• WHERE tb1.ltcolumn_name_1gt tb3.ltcolumn_name_1gt

31
Using ORDER BY

• SELECT tb1.ltcolumn_name_1gt
• ,tb1.ltcolumn_name_2gt
• FROM lttable_name_1gt tb1
• ORDER
• BY tb1.ltcolumn_name_1gt

32
Using Built-in Aggregate Functions

• SELECT tb1.ltcolumn_name_1gt
• ,COUNT(tb1.ltcolumn_name_2gt)
• FROM lttable_name_1gt tb1
• GROUP
• BY tb1.ltcolumn_name_1gt
• SELECT tb1.ltcolumn_name_1gt
• ,COUNT(DISTINCT tb1.ltcolumn_name_2gt)
• FROM lttable_name_1gt tb1
• GROUP
• BY tb1.ltcolumn_name_1gt

33
Using Built-in Aggregate Functions

• SELECT tb1.ltcolumn_name_1gt
• ,SUM(tb1.ltcolumn_name_2gt)
• FROM lttable_name_1gt tb1
• GROUP
• BY tb1.ltcolumn_name_1gt
• SELECT tb1.ltcolumn_name_1gt
• ,AVG(tb1.ltcolumn_name_2gt)
• FROM tb1.lttable_name_1gt tb1
• GROUP
• BY ltcolumn_name_1gt

34
Using HAVING

• SELECT tb1.ltcolumn_name_1gt
• ,SUM(tb1.ltcolumn_name_2gt
• BY tb1.ltcolumn_namegt
• HAVING SUM(tb1.ltcolumn_name_2gt) gt 100

35
Relational Operators

• Defines operators like UNION, MINUS, and
  INTERSECT
• Used in practice, but less frequently than joining

36
Relational OperatorsUnion

• Adding rows from one table to a second.
• Duplicates are shown When UNION ALL is used.
• Must be Union Compatible.
• Must have the same number of columns in each
  SELECT statement
• Column types (i.e., character, date, number,
  etc.) by position in the SELECT statements must
  be the same

37
Union Example

• SELECT emp.emp_name rec_name
• ,emp.city rec_city
• ,emp.state rec_state
• ,I rec_type
• FROM employee emp
• UNION
• SELECT cust.cust_name
• ,cust.city
• ,cust.state
• ,E
• FROM customer cust
• ORDER
• BY 1

38
Union Union Compatible Exercise

• SELECT tb1.ltcharactergt
• ,tb1.ltcharactergt
• ,tb1.ltnumbergt
• ,tb1.ltdategt FROM lttable_name_1gt tb1
• WHERE
• UNION
• SELECT tb2.ltcharactergt
• ,tb2.ltnumbergt
• ,tb2.ltcharactergt
• ,tb2.ltdategt FROM lttable_name_2gt tb2
• WHERE

39
Relational Operators Difference

• Records occur in the first table but not the
  second
• MUST be union compatible.
• The order of subtraction matters (i.e., A-B is
  not the same as B-A
• SELECT character, character, number, date FROM
  table1 WHERE
• MINUS
• SELECT character, character, number, date FROM
  table2
• WHERE

40
Relational Operators Intersection

• Records that appear in both tables
• MUST be union compatible
• SELECT character, character, number, date FROM
  table1
• WHERE
• INTERSECT
• SELECT character, character, number, date FROM
  table2
• WHERE

41
CREATE With Primary Key

• CREATE TABLE inventory (
• item_id number
• ,description varchar(50)
• ,in_stock varchar(1)
• ,list_sale_price number
• ,purchase_cost number
• ,discount_percent number
• ,PRIMARY KEY(item_id)
• )

42
CREATE With Primary Key and Foreign Keys

• CREATE TABLE item_orders (
• order_id number
• ,date_ordered date
• ,customer_id number
• ,item_id number
• ,quantity_ordered number
• ,PRIMARY KEY(order_id)
• ,FOREIGN KEY(item_id)
• REFERENCES inventory(item_id)
• )

43
INSERT

• INSERT
• INTO employee ( emp_id
• ,emp_name
• ,emp_eye_color)
• VALUES ( emp_id_seq.nextval
• ,Alan Schneider
• ,blue)

44
UPDATE

• UPDATE employee
• SET emp_eye_color Green WHERE emp_id 1

45
DELETE

• DELETE
• FROM employee
• WHERE emp_id 1

• Allows for undoing (Rollback)