SQL

1
SQL

• Sangeeta Devadiga
• CS157A, Fall 2006

2
Outline

• Background
• Data Definition
• Basic Structure
• Set Operation

3
Background

• IBM developed the original version
• named sequel in early 1970s
• Sequel language has evolved into SQL
• SQL (Structured Query Language)
• Versions of SQL
• SQL 92
• SQL 99
• SQL 2003 (latest version)

4
Parts of SQL

• Data Definition Language (DDL)
• Data manipulation Language (DML)
• Integrity
• View Definition
• Transaction control
• Embedded SQL and Dynamic SQL
• Authorization

5
Basic Domain Types

• char(n) A fixed length character string with
  user specifed length n. character can be used
  instead.
• varchar(n) A variable length character string
  with user specified max length n. character
  varying is equivalent.
• int An Integer, the full form integer is
  equivalent.
• smallint A small integer, a subset of integer
  domain type
• numeric(p,d) A fixed point number with user
  specified precision. E.g numeric(3,1) allows
  31.5 to be defined precisely
• real, double precision Floating-point and double
  precision floating-point numbers with
  machine-dependent precision.
• float(n) A floating point number, with precision
  of at least n digits.

6
Basic Schema Definition in SQL

• We create SQL relation using the create table
  command
• create table r( A1D1 , A2D2 , .. , AnDn ,
• (integrity
  constraint1),
• . ,
• (integrity constraintk))
• r ? is the name of relation
• A1.An ? are the names of attributes
• D1Dn ? are the types of values in the domain

7
Examples create table

• Example 1
• create table customer
• (customer_name char(20),
• customer_street char(30),
• customer_city char(30),
• primary key (customer_name))

8
Example 2

• create table account
• (account_number char(10),
• branch_name char(15),
• balance numeric (12, 2),
• primary key (account_number))

9
Basic SQL Query Structure

• SQL is based on set and relational operations
  with some modification and enhancement.
• SQL query has the form
• select A1,A2, ,An
• from r1, r2, . ,rm
• where P
• A1 is a attribute
• r1 represents a relation
• P is a predicate
• Equivalent Query
• ? A1, A2 , , An(?P (r1 X r2 X X rm))
• The result of a SQL query is a relation

10
Example select Clause

• select branch_name
• from loan
• In relational Algebra, the query would be
• ?branch_name(loan)
• SQL allows duplicates in query result
• use distinct if no duplicates in result
• use all if duplicates required in result
• select distinct branch_name
• from loan (result has distinct branch names)
• select all branch_name
• from loan (result may have duplicates)

11
Where Clause

• Corresponds to the selection predicate of
  relational algebra
• To find loan numbers for loans made at San Jose
• branch with loan amounts greater than 500
• select loan_number
• from loan
• where branch_name San Jose and amount gt
  500
• Comparison result can be combined with logical
  connectives and, or, and not
• SQL includes between comparison operator
• To find loan numbers between amt. 900 and 10,000
• select loan_number
• from loan
• where amount between 900 and 10000

12
From Clause

• Corresponds to Cartesian product operation of
  relational algebra
• Example
• To find name, loan number, amount of all
• customers having loan at San Jose branch.
• select customer_name, loan_number, amount
• from borrower , loan
• where borrower.loan_number
  loan.loan_number and
• branch_name San Jose

13
Rename

• SQL allows renaming relations and attributes
  using as clause
• Example
• To find name, loan_number, amount
• of all customers and rename column
• loan_number as loan_id.
• select customer_name, loan_number as loan_id,
  amount
• from borrower , loan
• where borrower.loan_number loan.
  loan_number

14
Set Operation

• The set operations union, intersect and except
  corresponds to U,?, - respectively of relational
  algebra.
• Each of the above operation automatically
  eliminates duplicates
• To retain all duplicates use union all,
• intersect all, except all

15
Examples Set Operations

• Find all customers who have a loan, a account or
  both
• select customer_name from depositor
• union
• select customer_name from borrower
• Find all customers who have both loan and an
  account
• select customer_name from depositor
• intersect
• select customer_name from borrower
• Find all customers who have an account, but no
  loan
• select customer_name from depositor
• except
• select customer_name from borrower