SQL: Data Manipulation - PowerPoint PPT Presentation

1 / 62

About This Presentation

Title:

SQL: Data Manipulation

Description:

SQL is an example of a transform-oriented language. ... A column number (deprecated) Source: Database Systems Connolly/Begg. Sorting. SELECT type, rent ... – PowerPoint PPT presentation

Number of Views:29

Avg rating:3.0/5.0

Slides: 63

Provided by: x7210

Learn more at: http://www.cs.sjsu.edu

Category:

more less

Transcript and Presenter's Notes

Title: SQL: Data Manipulation

1
SQL Data Manipulation

Presented by Mary Choi
For CS157B
Dr. Sin Min Lee

2
Introduction

Structured Query Language (SQL)
Writing an SQL Command
Retrieving Data
Building SQL Statements
Performing Database Updates

3
What is SQL?

SQL is an example of a transform-oriented
language.
A language designed to use relations to transform
inputs into required outputs.

Source Database Systems Connolly/Begg
4
What is SQL?

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

Source Database Systems Connolly/Begg
5
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
6
Data Manipulation Language (DML)

Retrieving Data
Query tables
Updating Data
Populate tables

Source Database Systems Connolly/Begg
7
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
8
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
9
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
10
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
11
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
12
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
13
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
14
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
15
Retrieve all columns and all rows

SELECT firstColumn,,lastColumn
FROM tableName
SELECT
FROM tableName

16
Use of Distinct

SELECT DISTINCT columnName
FROM tableName

17
Calculated fields

SELECT columnName/2
FROM tableName

18
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
19
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
20
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

21
Set membership search condition

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

22
Pattern matching symbols

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

Source Database Systems Connolly/Begg
23
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
24
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
25
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
26
NULL search condition

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

27
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
28
Sorting

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

Source Database Systems Connolly/Begg
29
Sorting

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

Source Database Systems Connolly/Begg
30
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
31
Use of COUNT( )

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

32
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
33
Grouping

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

34
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
35

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
36
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
37
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
38
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
39
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
40
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
41
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
42
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
43
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
44
Multi-Table Queries

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

Source Database Systems Connolly/Begg
45
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
46
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
47
Inner Join