My SQL: Data Manipulation

1
My SQL Data Manipulation
Lecture 4 CS157B

• Prof. Sin Min Lee
• Deparment of Computer Science
• San Jose State University

2
Introduction
3

• Writing an SQL Command
• Retrieving Data
• Building SQL Statements
• Performing Database Updates

4
SQL

• SQL (Structured Query Language) is the standard
  language for commercial DBMSs
• SEQUEL (Structured English QUEry Language)was
  originally defined by IBM for System R
• standardization of SQL began in the 80s
• current standard is SQL-99
• SQL is more than a query language it includes a
  DDL, DML and administration commands
• SQL is an example of a transform-oriented
  language.
• A language designed to use relations to transform
  inputs into required outputs.

5
Basic structure of an SQL query
2
6
2
The SituationStudent Particulars
field type width contents id numeric
4 student id number name character
10 name dob date 8 date of
birth sex character 1 sex M /
F class character 2 class hcode character
1 house code R, Y, B, G dcode character
3 district code remission logical 1 fee
remission mtest numeric 2 Math test score
7
General Structure
I
SELECT ...... FROM ...... WHERE ......
SELECT ALL / DISTINCT expr1 AS col1, expr2
AS col2 FROM tablename WHERE condition
8
General Structure
I
SELECT ALL / DISTINCT expr1 AS col1, expr2
AS col2 FROM tablename WHERE condition

• The query will select rows from the source
  tablename and output the result in table form.

• Expressions expr1, expr2 can be
• (1) a column, or
• (2) an expression of functions and fields.

• And col1, col2 are their corresponding column
  names in the output table.

9
General Structure
I
SELECT ALL / DISTINCT expr1 AS col1, expr2
AS col2 FROM tablename WHERE condition

• DISTINCT will eliminate duplication in the output
  while ALL will keep all duplicated rows.

• condition can be
• (1) an inequality, or
• (2) a string comparison
• using logical operators AND, OR, NOT.

10
General Structure
I

• Before using SQL, open the student file
• USE student

eg. 1 List all the student records.
SELECT FROM student
11
General Structure
I
eg. 2 List the names and house code of 1A
students.
SELECT name, hcode, class FROM student
WHERE class"1A"
12
General Structure
I
eg. 2 List the names and house code of 1A
students.
13
General Structure
I
eg. 3 List the residential district of the Red
House members.
SELECT DISTINCT dcode FROM student WHERE
hcode"R"
14
What is SQL?

• ISO SQL has two major components
• Data Definition Language (DDL)
• Data Manipulation Language (DML)

Source Database Systems Connolly/Begg
15
Data Definition Language (DDL)

• Defining the database structure
• Tables
• Controlling access to the data
• What a user can legally access

Source Database Systems Connolly/Begg
16
Data Manipulation Language (DML)

• Retrieving Data
• Query tables
• Updating Data
• Populate tables

Source Database Systems Connolly/Begg
17
Writing SQL Commands

• SQL statement consists of reserved words and
  user-defined words
• Reserved words are a fixed part of the SQL
  language and have a fixed meaning
• User-defined words are made up by the user
  (according to syntax rules)

Source Database Systems Connolly/Begg
18
Reserved Words

• Are fixed part of the SQL language
• Have a fixed meaning
• Require exact spelling
• Kept on the same line

Source Database Systems Connolly/Begg
19
User-defined Words

• Are made up by the user
• Governed by a set of syntax rules
• Represent names of database objects such as
• Tables
• Columns
• Views
• Indexes

Source Database Systems Connolly/Begg
20
Data Manipulation

• Select query data in the database
• Insert insert data into a table
• Update updates data in a table
• Delete delete data from a table

Source Database Systems Connolly/Begg
21
Literals

• Non-numeric data values must be enclosed in
  single quotes
• 16 Holland Drive
• CS157B
• Numeric data values must NOT be enclosed in
  single quotes
• 6
• 600.00

Source Database Systems Connolly/Begg
22
Writing SQL Commands

• Most components of an SQL statement are case
  insensitive, but one exception is that literal
  character data must be typed exactly as it
  appears in the database.

Source Database Systems Connolly/Begg
23
Simple Query

• Select specifies which columns are to appear in
  the output.
• From specifies the table(s) to be used.
• Where filters the rows subject to some
  condition(s).

Source Database Systems Connolly/Begg
24
Simple Query

• Group By forms groups of rows with the same
  column value.
• Having filters the groups subject to some
  condition.
• Order By specifies the order of the output.

Source Database Systems Connolly/Begg
25
Retrieve all columns and all rows

• SELECT firstColumn,,lastColumn
• FROM tableName
• SELECT
• FROM tableName

26
Use of Distinct

• SELECT DISTINCT columnName
• FROM tableName

27
Calculated fields

• SELECT columnName/2
• FROM tableName

28
Comparison Search Condition

• equals
• lt gt is not equal to (ISO standard)
• ! (allowed in some dialects)
• lt is less than
• gt is greater than
• lt is less than or equal to
• gt is greater than or equal to

Source Database Systems Connolly/Begg
29
Comparison Search Condition

• An expression is evaluated left to right.
• Subexpressions in brackets are evaluated first.
• NOTs are evaluated before ANDs and ORs.
• ANDs are evaluated before ORs.

Source Database Systems Connolly/Begg
30
Range Search Condition

• SELECT columnName
• FROM tableName
• WHERE columnName BETWEEN 20 AND 30
• SELECT columnName
• FROM tableName
• WHERE columnName gt 20
• AND columnName lt 30

31
Set membership search condition

• SELECT columnName
• FROM tableName
• WHERE columnName
• IN (name1, name2)
• SELECT columnName
• FROM tableName
• WHERE columnName name1
• OR columnName name2

32
Pattern matching symbols

• represents any sequence of zero
• or more characters (wildcard).
• _ represents any single character

Source Database Systems Connolly/Begg
33
Pattern match search condition

• h begins with the character h .
• h_ _ _ four character string beginning with
  the
• character h.
• e any sequence of characters, of length
  at
• least 1, ending with the character e.
• CS157B any sequence of characters of any
• length containing CS157B

Source Database Systems Connolly/Begg
34
Pattern match search condition

• LIKE h
• begins with the character h .
• NOT LIKE h
• does not begin with the character h.

Source Database Systems Connolly/Begg
35
Pattern match search condition

• To search a string that includes a
• pattern-matching character
• 15
• Use an escape character to represent
• the pattern-matching character.
• LIKE 15 ESCAPE

Source Database Systems Connolly/Begg
36
NULL search condition

• DOES NOT WORK
• comment
• comment !
• DOES WORK
• comment IS NULL
• comment IS NOT NULL

37
Sorting

• The ORDER BY clause
• consists of list of column identifiers that the
  result is to be sorted on, separated by commas.
• Allows the retrieved rows to be ordered by
  ascending (ASC) or descending (DESC) order

Source Database Systems Connolly/Begg
38
Sorting

• Column identifier may be
• A column name
• A column number (deprecated)

Source Database Systems Connolly/Begg
39
Sorting

• SELECT type, rent
• FROM tableName
• ORDER BY type, rent ASC

Source Database Systems Connolly/Begg
40
Aggregate Functions

• COUNT returns the number
• SUM returns the sum
• AVG returns the average
• MIN returns the smallest
• MAX returns the largest
• value in a specified column.

Source Database Systems Connolly/Begg
41
Use of COUNT( )

• How many students in CS157B?
• SELECT COUNT( ) AS my count
• FROM CS157B

42
GROUP BY clause

• When GROUP BY is used, each item in the SELECT
  list must be single-valued per group.
• The SELECT clause may contain only
• Column names
• Aggregate functions
• Constants
• An expression involving combinations of the above

Source Database Systems Connolly/Begg
43
Grouping

• SELECT dept, COUNT(staffNo) AS my count
  SUM(salary)
• FROM tableName
• GROUP BY dept
• ORDER BY dept

44
Restricting Grouping

• HAVING clause
• is with the GROUP BY clause.
• filters groups into resulting table.
• includes at least one aggregate function.
• WHERE clause
• filters individual rows into resulting table.
• Aggregate functions cannot be used.

Source Database Systems Connolly/Begg
45

• SELECT dept, COUNT(staffNo) AS my count,
  SUM(salary) AS my sum
• FROM Staff
• GROUP BY dept
• HAVING COUNT(staffNo) gt 1
• ORDER BY dept

Source Database Systems Connolly/Begg
46
Subqueries

• SELECT columnNameA
• FROM tableName1
• WHERE columnNameB (SELECT columnNameB
• FROM tableName2
• WHERE condition)

result from inner SELECT applied as a condition
for the outer SELECT
Source Database Systems Connolly/Begg
47
Subquery with Aggregate Function
List all staff whose salary is greater than the
average salary, show by how much their salary is
greater than the average.

• SELECT fName, salary
• ( SELECT AVG(salary)
• FROM Staff ) AS salDiff
• FROM Staff
• WHERE salary gt ( SELECT AVG(salary)
• FROM Staff )

Source Database Systems Connolly/Begg
48
Nested Subqueries Use of IN

• SELECT property
• FROM PropertyForRent
• WHERE staff IN(
• SELECT staff
• FROM Staff
• WHERE branch (
• SELECT branch
• FROM Branch
• WHERE street
• 112 A St))

Selects branch at 112 A St
Source Database Systems Connolly/Begg
49
Nested Subqueries Use of IN

• SELECT property
• FROM PropertyForRent
• WHERE staff IN(
• SELECT staff
• FROM Staff
• WHERE branch ( branch ) )

Select staff members who works at branch.
Source Database Systems Connolly/Begg
50
Nested Subqueries Use of IN

• SELECT property
• FROM PropertyForRent
• WHERE staff IN( staffs who works
• at branch on 112 A St)

Since there are more than one row selected,
cannot be used.
Source Database Systems Connolly/Begg
51
Use of ANY/SOME

• SELECT name, salary
• FROM Staff
• WHERE salary gt SOME( SELECT salary
• FROM Staff
• WHERE branch A )

Result list of staff with salary greater than
2000.
Result2000,3000,4000
Source Database Systems Connolly/Begg
52
Use of ALL

• SELECT name, salary
• FROM Staff
• WHERE salary gt ALL( SELECT salary
• FROM Staff
• WHERE branch A )

Result list of staff with salary greater than
4000.
Result2000,3000,4000
Source Database Systems Connolly/Begg
53
Use of Any/Some and All

• If the subquery is empty
• ALL returns true
• ANY returns false
• ISO standard allows SOME to be
• used interchangeably with ANY.

Source Database Systems Connolly/Begg
54
Natural Join
4
SELECT a.comcol, a.col1, b.col2, expr1, expr2
FROM table1 a, table2 b WHERE a.comcol
b.comcol
55
Natural Join
4
eg. 25 Make a list of students and the
instruments they learn. (Natural Join)
56
Natural Join
4
eg. 25 Make a list of students and the
instruments they learn. (Natural Join)
SELECT s.class, s.name, s.id, m.type FROM
student s, music m WHERE s.idm.id ORDER BY
class, name
57
Natural Join
4
eg. 26 Find the number of students learning
piano in each class.
Three Parts (1) Natural Join. (2) Condition
m.type"Piano" (3) GROUP BY class
58
Natural Join
4
eg. 26
59
Natural Join
4
eg. 26 Find the number of students learning
piano in each class.
SELECT s.class, COUNT() FROM student s,
music m WHERE s.idm.id AND m.type"Piano"
GROUP BY class ORDER BY class
60
Outer Join
4
61
Outer Join
4
eg. 27 List the students who have not yet chosen
an instrument. (No match)
62
Outer Join
4
eg. 27 List the students who have not yet chosen
an instrument. (No match)

• SELECT class, name, id FROM student
• WHERE id NOT IN ( SELECT id FROM music )
• ORDER BY class, name

63
Outer Join
4
eg. 28 Make a checking list of students and the
instruments they learn. The list should also
contain the students without an
instrument. (Outer Join)
64
Outer Join
4
eg. 28
65
Outer Join
4
eg. 28
SELECT s.class, s.name, s.id, m.type FROM
student s, music m WHERE s.idm.id
UNION SELECT class, name, id, "" FROM
student WHERE id NOT IN ( SELECT id FROM
music ) ORDER BY 1, 2
66
Outer Join
4
67
Multi-Table Queries

• Join
• Inner Join
• Left Outer Join
• Right Outer Join
• Full Outer Join

Source Database Systems Connolly/Begg
68
Join

• SELECT client
• FROM Client c, View v
• WHERE c.client v.client

ISO standard Alternatives
FROM Client c JOIN View v ON c.client
v.client (creates two identical client
columns) FROM Client JOIN View USING
client FROM Client NATURAL JOIN View
Source Database Systems Connolly/Begg
69
Join

• The join operation combines data from two tables
  by forming pairs of related rows where the
  matching columns in each table have the same
  value.
• If one row of a table is unmatched, the row is
  omitted from the resulting table.

Source Database Systems Connolly/Begg