Transcript Document 7632717

SQL
PROF. Sin-Min LEE
Department of Computer Science
San Jose State University
•
What is SQL?
• Motivation
•How is it used




Non-procedural Language
Data Definition
Data Management
Data Manipulation (Query)
Powerful
Easy to learn
Flexible
ANSI/ISO Standard
Data Definition
The SQL Data Definition Language (DDL)
allows us to create and destroy database
objects such as schemas, domains, tables,
views, and indexes. The ISO standard also
allows the creation of assertions,
character sets, collations
and translations.
The main SQL data definition
language statements are:
 CREATE SCHEMA
 DROP SCHEMA
 CREATE DOMAIN
 ALTER DOMAIN
 DROP DOMAIN
CREATE TABLE
 ALTER TABLE
 DROP TABLE
 CREATE VIEW
 DROP VIEW
While CREATE INDEX and
DROP INDEX are provided
by DBMS


As a Data Definition Language
• Create the database and its table structure
• Create the tables - CREATE TABLE command
•  Entity integrity
•  Referential integrity
• automatically enforced
CREATE TABLE Library (
1 L_Code
VCHAR(10) NOT NULL
2 L_City
VCHAR(10) NOT NULL
3 L_Size
INTEGER
NOT NULL
4 B_Code
INTEGER
5
6 PRIMARY KEY (L_Code),
7 FOREIGN KEY (B_Code ) );
UNIQUE
Basic Data Management Commands
 Add Data to the Table
• SELECT  Check the Table Contents
• COMMIT  Save the Table Contents
• UPDATE  Make changes
• DELETE  Delete Table Rows
• ROLLBACK  Restore Table Contents
• INSERT
SQL IDENTIFIER
SQL Identifiers are used to
identify objects in the database. The
characters that can be used in a
userdefined SQL identifier must appear
in a character test. The following
restrictions are imposed on an identifier:



An identifier can be no longer than
128 characters (most dialects have
a much lower limit than this ).
An identifier must start with a
letter.
An identifier can not contain
spaces.
The ISO SQL Data Types
There are six SQL scalar data types defined in
the ISO standard, which are: character, bit,
exact numeric, approximate numeric,
datetime, and interval. Sometimes for
manipulation and conversion purposes, the
data types character and bit are collectively
referred to as string data types, and exact
numeric and approximate numeric are
referred to as numeric data types, as they
share similar properties.





Based on SELECT COMMAND
* is wildcard character - gives “all rows”
Use mathematical or logical operators to
restrict output
Ordered lists with ORDER BY
SQL numeric functions
COUNT, MIN, MAX, AVG, & SUM
•Partial Listing of Table Contents
SELECT <column(s)>
FROM <table name>
WHERE <condition>
SELECT Tiger
FROM ANIMAL
WHERE A_class = Bengal
Logical Operators: AND, OR, & NOT
SELECT Tiger
FROM ANIMAL
WHERE A_class = Bengal
OR A_class = Asian
SPECIAL OPERATORS





BETWEEN - define range limits
IS NULL - check if attribute value is null
LIKE - check for similar character strings
IN - check if attribute value matches a value
within a (sub)set of listed values
EXISTS - check whether attribute has a
value
DEFINITION



SQL(Structured Query Language) is the
industry standard relational query language. It was
first designed at IBM. There are several versions of
SQL standards(SQL - 86, SQL - 89, X/Open, SQL 92, SQL3). Each vendor also has its own
extensions of the SQL language
DDL(Data Definition Language) are the commands
that create a database schema
DML(Data Manipulation Language) are the
commands that deal with data in a database(inserts,
deletes, updates, and data retrieval)
Differences between Tables and
Relations



Tables typically allow duplicates
Attributes names are ordered in a table
Cannot always identify a candidate key or a primary
key in a table
Example relations:
DEPT
DEPT2
STUDENT
COURSE
TAKE
dname location
EECS
Cory
Math
Evans
dname location
EECS
Cory
CS
Soda
name
regno
Mike
1
Jenny
2
cname
Database CS57
Discrete Math
regno
cno
1
CS57
1
Math 55
2
CS57
gpa
3.5
3.8
cno
level
3
4
dept
EECS
Math
dept
CS
Math55
Math

DDL
Commands
Create a table:
create table: table_name attributes_description
Example:
create table STUDENT
(name char(30) not null,
regno integer not null,
gpa float null,
level smallint default 1 not null,
dept varchar(20) null)
Cont.

Where default specifies a default value for the
column, not null indicates that null values are not
allowed for that attribute. If you do not specify null
or not null SQL Server uses not null by default








int,
integer:
4 byte Data
integer Types
SQL
Server
smallint: 2 byte integer
tinyint: 1 byte integer
float: 4 or 8 byte floating point number
real: 4 byte floating point number
double precision: 8 bype floating point number
numeric, decimal(precision, scale): exact
numeric, 2 to 17 bytes. Only difference is that
only numeric types with a scale of 0 can be used
of the IDENTITY column.
Destroy a table: drop table table_name
SQL
Server
Data
Types-cont.
 Char(length) : fixed length character string.






255 characters or less
varchar(length): variable length character
string. 255 characters or less
nchar(length), nvarchar(length): for multibyte
character sets
text: up to 2G bytes, storage is multiples of 2 K
datetime: 8 bytes
money: 8 bytes
other data types: binary, varbinary, image, bit
Basic Insert: Inserting a Tuple
into a Table
Insert one tuple into table:
insert into table_name[(column_list)] values(value_list)
 Ex: specify values for all attributes:
insert into STUDENT values(‘Mike’, 1, 3.8, 3, ‘CS’)
 Ex: specify values for only some attributes:
insert into STUDENT(name, regno, level)
values (‘Jenny’, 2, 1)


NOTE: in SQL, strings are surrounded by single quote, not double
quote. Double quotes surround identifiers that contain special
characters. To include a single quote in the string use two single
quotes (eg.,’it’’s good’)


Basic Select: Retrieving Data
from One Table
Names of all students:
select name
from STUDENT
use distinct to eliminate duplicates:
select distinct name
from STUDENT
Basic Select - cont

use built-in function(SQL Server specific):
select getdate()
select datename(month, getdate())
select user
NOTE: the from clause is required in the SQL standard


have expression in the select list(convert gps to 5.0 scale):
select regno, gpa / 4.0 * 5.0
from STUDENT
default type conversion between all numeric data types and
money
Basic Select - cont.


Select all attributes of a table:
select *
from STUDENT
Select a subset of tuples(CS seniors):
select name, regno
from STUDENT
where dept = ‘CS’ and level = 4
Basic Select - cont.
Sort the results of a select. All students in
ascending order of name. For students with the
same name, sort in descending order of regno.
Ascending is the default sor order
select name, regno
from STUDENT
order by name asc, regno desc
NOTE: order by cannot used inside a subquery

Joins: Select Condition
 Involving
All students taking
CS 57
Multiple
Tables
select s.*
from STUDENT s, TAKE t
where s.regno = t.regno and t.cno = ‘cs57’
s and t in the from clause are called correlation names
 Departments located in the same building
select distinct d1.name, d2.name
from DEPT1 d1, DEPT d2
where d1.location = d2.location



GetJoins:
rid of combination
of same attributes values:
cont.
select d1.name, d2.name
from DEPT d1, DEPT d2
where d1.location = d2.location and d1.name > d2.name
Avoid duplicate attributes names by renaming attributes
select d1.name as name1, d2.name as name2
from DEPT d1, DEPT d2
All employees who make more than their manager
table: EMP (name, salary, manager)
select e.name, m.name
where e.manager = m.name and e.salary > m.salary
Joins: cont.


Join 3 tables: all student taking cs classes
select distinct s.*
from STUDENT s, TAKE t, COURSE c
where s.regno = t.regno and t.cno = c.no and c.dept
= ‘CS’
All students taking classes from their own department
select distinct s.*
from STUDENT s, TAKE t, COURSE c
where s.regno = t.regno and t.cno = c.cno and
c.dept = s.dept
JoinJoins:
4 tables:
cont.all students taking classes offered by
departments located in Evans.
select distinct s.*
from STUDENT s, TAKE t, COURSE c, DEPT
d
where s.regno = t.regno and t.cno = c.cno and
c.dept = d.dname and d.location =
‘Evans’
All students taking CS classes: their names, regno,
and the CS classes they take
select s.name, s.regno, c.cno
from STUDENT s, TAKE t, COURSE c
where s.regno = t.regno and t.cno = c.cno and
All students and their department location
select s.name, d.location
from STUDENT s, DEPT d
where s.dept *= d.dname
*= includes all rows from the first table
=* includes all rows from the second table:
select s.name, d.location
from STUDENT s, DEPT d
where d.dname = *s.dept

Outer Join
There are five of them : count, sum, avg, max, min

Count number of student names
select count (distinct name) from STUDENT

Count number of students
select count (*) from STUDENT

Count distinct names of senior student in CS
select count(distinct name)
from STUDENT s where s.level = 4 and s.dept = ‘CS’

All aggregate functions always return one value. If the table is empty, count
returns 0, the other return null.

Get aggregates of groups by using group by: get every dept’s gpa average
select dept, avg(gpa)
from STUDENT group by dept
Each expression in the target list must be grouping columns or aggregates

Aggregate Functions
Aggregate Functions-cont
Get gpa average of each dept and level
select dept, level, avg(gpa)
from STUDENT group by dept, level

Get seniors average gpa for each dept
select dept, avg(gpa)
from STUDENT s where s.level = 4
group by s.dept

Restrict aggregate values of groups by using having: get all depts with more
than 40 senior student
select dept
from STUDENT s
where s.level = 4
group by s.dept
having count(*) > 40
When there is no group by, the entire table is considered a group

All students who is in a dept located in Evans
Nested Queries
select s.*
from student s
where dept in
(select d.dname
from dept d
where d.location = ‘Evan’
Conceptually, the inner subquery is evaluated first to get all the
depts located in Evans. Then the outer query is evaluated. This
query can also be formulated using join

[not]exists


Exists returns true if its argument set is not empty, and false
otherwise. Not exists is particularly useful for a certain type
of queries.
Courses taken by any CS seniors(at least one CS senior
taking the courses)
select c.*
from COURSE c
where exists
(select s.*
from STUDENT s, TAKE t
where s.level = 4 and s.dept = ‘CS’ and s.regno =
t.regno and t.cno = c.cno)
ANY, ALL
a >= any (select query) : true if a >= x for any x where x
is a value returned by the subquery
a >= all (select query) : true if a >= x for all x where x is a
value returned by the subquery
The subquery must have exactly one element in its
target list. Result of the subquery is a set of values
 Students who are in a department located in Evans
select s.*
from student s
where s.dept = any (select d.name from DEPT d
where d.location = ‘Evan’)
 Students having the best gpa
select s.*
from student s
where s.gpa >= all(select s1.gpa from student s1)
Give the union of the results of multiple subqueries. Duplicates are
always eliminated. But if you say union all, duplicates are not
removed. If you say union all once, you must say it for all
unions within one statement. All subselects must have the same
number of columns with matching types. Columns of numeric
types cannot be matched with columns of character types

Students who are seniors, or have gpa better than 3.0, or are
taking cs57
select s.name, s.regno
from STUDENT s
where s.level = 4
union
select s.name, s.regno
from STUDENT s
where s.gpa > 3.0
UNION

union
UNION - cont
select s.name, s.regno
from STUDENT s, TAKE t
where s.regno = t.regno and t.cno = ‘cs57’
Note: union cannot be used inside a subquery
Basic form is:
update table_name
set column_name = {expression | select_stmt}
[, column_name = {expression | select_stmt}]…
[from table_name [,table_name]…]
[where search_condition]

The expression in the set clause can contain constants, column values from
the table being updated, or column values from tables in the from clause.
If a column value of the table being updated appears in an expression, it
refers to the old value.
NOTE: the select_stmt in the set clause is Sybase SQL Server specific, the
[from…] clause is Sybase SQL Server specific

All students whose gpa is better than or equal to 2.0 can move up one level:
update student
set level = level + 1
where gpa >= 2.0
UPDATE

Give all managers who make less than someone working for
UPDATE
them a 5% raise:
update EMP
set salary = salary * 1.05
where salary < any (select e.salary from EMP e where
e.manage = e.name)
Basic
form is:
DELETE
delete from tablename
[where search_condition]
The delete statement removes rows that satisfy the
search_condition from tablename
 Expel all students who have been around for more than 20
years
delete from student
where level > 0
 Fire all employees who make more than their managers
delete from EMP
where salary > (select e.salary from EMP e where e.name =
e.manager)

Can insert the result of a select query into another table
INSERT from the results of a subselect
 Suppose there is a table WAITING(regno, cno) for the class
waiting list. Add all CS seniors on the CS 57 waiting list to
the CS 57 enrollment list
insert into TAKE select distinct w.regno, w.cno
from WAITING w, STUDENT s
where w.cno = ‘cs57’ and s.regno = w.regno
and s.dept = ‘CS’ and s.level = 4
 Create a new table for the results of a select:
select distinct w.regno, w.cno
into TAKE
from WAITING w, STUDENT s
where w.cno = ‘cs57’ and s.regno = w.regno
and s.dept = ‘CS’ and s.level = 4
More
Example:
DEPT
(dname,
location)
dname
location
-------------------- -------------------CS
Soda
EECS
Cory
Math
Evans
Music
Morrison
Economic
Evans
EMP
(name, emp_no,
salary, dname, start_date, leave_date)
Example
cont.
name emp_no salary dname start_date leave_date
-------------------- ----------- -------------------- -------------------YDWang 1 80000.000000 CS Jan1 89 12:00AM NULL
George 2 100200.000000 CS Apr 5 92 12:00AM NULL
Ben Ling 3 85000.000000 CS De14 79 12:00AM NULL
Jennifer 4 90000.000000 CS Jul 2078 12:00AM NULL
Mark
5 70000.000000 EECS Jun11 81 12:00AM NULL
Diane
6
60000.000000 CS
Jan 1 1994 12:00AM
NULL
Cary
7
80000.000000 CS
Nov 3 1976 12:00AM
NULL
Geena
8
100000.000000 Music
Apr 25 1991 12:00AM
Aug 25 1994 12:00AM
Nino
9
61000.000000 Math
May 1 1992 12:00AM
Jan 10 1994 12:00AM
Goodman
10
70500.000000 Math
Feb 15 1994 12:00AM
NULL
Johnny
11
100.000000 Math
Mar 18 1994 12:00AM
Jan 5 1995 12:00AM
Andre
12
200.000000 Music
Aug 23 1994 12:00AM
NULL
Debi
13
40000.000000 Math
May 20 1992 12:00AM
NULL
Francis
14
12000.000000 EECS
Jul 30 1982 12:00AM
NULL
Basic Query Structure
Select A1, A2,………….., An
from r1, r2 , r3…………… rm
where P
This is equivalent to
π A1, A2,…… An( σ P (r1 x r2 x……..x rm))



if no where clause, P =TRUE
if A1, A2………. An are replaced by *, then all
attributes of r1, r2……. rm are retrieved
ri.* retrieves all attributes of ri
Basic Query Structure(cntd)

In practice, SQL systems convert a
SELECT statement to a form suitable for
efficient implementation (don’t really do a
X product).
Example - a Vet clinic
Is primary
provider
customer
owns
treated
pet
name
date
birthyr
type
discussion
vet
Ph#
name
office
Table scheme & a query
Customer(cno, name, addr, ph#)
pet(pno, name, type, birthyr, vno, cno)
vet(vno, name, office, ph#)
treated(vno, pno, date, discussion)

Find pet named “Fluffy”
select *
from pet
where name = “Fluffy”
Queries (cntd.)

Find owner name and ph# for Fluffy
select customer.name, ph#
from customer, pet
where customer.cno = pet.cno
and pet.name = “Fluffy”
Queries (cntd.)

Find all primary providers for pets owned
by “Solange”
select vet.name
from vet, pet, customer
where customer.cno = pet.cno
and pet.vno = vet.vno
and customer.name = “Solange”
Quiz

Give SQL query for:


Find the vet named Bob.
Find the name of each customer, pet and vet
where the customer’s pet was treated by that
vet on “5/18/98”.