Basic SQL

1
Basic SQL
CS157A Lecture 12

• Prof. Sin-Min Lee
• Department of Computer Science

Prof. Sin-Min Lee Department of Computer Science
2
In this chapter, you will learn

• The basic commands and functions of SQL
• How SQL is used for data manipulation (to add,
  modify, delete, and retrieve data)
• How to use SQL to query a database to extract
  useful information
• About more advanced SQL features such as
  updatable views, stored procedures, and triggers

3
Introduction to SQL

• Ideal database language
• Create database and table structures
• Perform basic data management chores (add,
  delete, and modify)
• Perform complex queries to transform data into
  useful information
• SQL is the ideal DB language
• Data definition language
• Data manipulation language

4
Background

• IBM developed the original version of SQL at its
  San Jose Research Laboratory
• Evolved as The Sequel language, its name has
  changed to SQL (Structured Query Language)
• SQL has clearly established itself as the
  standard relational-database language

5
Good Reasons to Study SQL

• ANSI standardization effort led to de facto query
  standard for relational database
• Forms basis for present and future DBMS
  integration efforts
• Becomes catalyst in development of distributed
  databases and database client/server architecture

6
Basic Structure of SQL

• Consists of three clauses
• Select
• - Used to list the attributes desired in the
  result of a query.
• From
• - Lists the relations to be scanned in the
  evaluation of the expression.
• Where
• - Consists of a predicate involving attributes
  of the relations that appear in the from clause.

7
SQL a Short Introduction

• SQL stands for Structured Query Language
• Queries are used to extract information from the
  database.
• An SQL expression/block consists of three main
  clauses
• select (projection operation) Lists the
  attributes desired in the result of a query
• from (Cartesian-product operation) List the
  relation to be scanned in the evaluation of the
  expression.
• where (selection predicate) Consists of
  predicate involving attributes of the relations
  that appear in the from clause
• Example select name from students where gpa
  '4.0'
• attribute relation
  condition

8
A typical SQL query form

• Select A1, A2,.An
• Ai represents an attribute.
• From r1, r2,.rm
• ri is a relation
• Where P
• P represents a predicate.

9
The Select Clause

• Example of a Simple Query
• Find the names of all branches in the loan
  relation
• select branch-name
• from loan

10
Creating Table Structure

• Tables store end-user data
• May be based on data dictionary entries

CREATE TABLE lttable namegt(ltattribute1 name and
attribute1 characteristics,attribute2 name and
attribute2 characteristics,attribute3 name and
attribute3 characteristics,primary key
designation,foreign key designation and foreign
key requirementgt)
11
Data Definition Commands

• Create database structure
• Holds all tables and is a collection of physical
  files stored on disk
• DBMS automatically creates tables to store
  metadata
• Database administrator creates structure or
  schema
• Logical group of tables or logical database
• Groups tables by owner
• Enforces security

CREATE SCHEMA AUTHORIZATION ltcreatorgt ExampleCRE
ATE SCHEMA AUTHORIZATION JONES
12
Using Domains

• Domain is set of permissible values for a column
• Definition requires
• Name
• Data type
• Default value
• Domain constraint or condition

CREATE DOMAIN ltdomain_namegt AS DATA_TYPEDEFAULT
ltdefault_valuegt CHECK (ltconditiongt)
13
Different parts of SQL

• Data-definition language
• Interactive data-manipulation language
• View definition
• Transaction Control
• Embedded SQL and dynamic SQL
• Integrity
• Authorization

14
More examples continued

• Inserting keyword distinct after select we can
  eliminate duplication
• For instance
• select distinct branch-name
• from loan
• Inserting keyword all after select helps
  restoring duplication.

15
The where clause

• Example
• Find all loan numbers for loans made at the
  Perryridge branch with loan amounts greater than
  1200.
• select loan-number
• from loan
• where branch-name Perryridge and amount gt
  1200

16
More examples of Where clause

• Logical connectives like and, or, and not are
  used in the where clause
• Example
• Loan number of those loans with loan amounts
  between 90,000 100,000
• select loan number
• from loan
• where amount between 90000 and 100000

17
The from Clause

• Defines a Cartesian product of the relations in
  the clause.
• Example
• For all customers who have a loan from the bank,
  find their names, loan numbers and loan amount

18
The from Clause (Cond)

• select customer-name, borrower.loan-number,
  amount
• from borrower, loan
• where borrower.loan-number loan.loan-number

19
The Rename Operation

• Uses as clause to rename both, relations and
  attributes
• The as clause takes the form in SQL
• old-name as new-name

20
The Rename Operation (Cond)

• Example
• To change attribute name loan-number to be
  replaced with name loan-id
• select customer-name, borrower.loan-number as
  loan-id, amount
• from borrower, loan
• where borrower.loan-number loan.loan-number

21
String Operations

• SQL specifies by enclosing in single quotes, for
  example, Perryridge
• character is use to match any substring.
• _ character is use to match any character
• It expresses patterns by using the like
  comparison operator

22
String Operations (Cond)

• Example
• Find the names of all customers whose street
  address includes the substring Main
• select customer-name
• from customer
• where customer-street like Main

23
Set Operations

• Operations such as union, intersect, ad except
  operate on relations.
• Corresponds to relational-algebra operations ?, ?
  and ?.
• Relations participating in the operations must be
  compatible i.e. must have same set of attributes.

24
Union Operation

• Example
• To find all customers having a loan, an account,
  or both at bank
• (select customer-name
• from depositor)
• union
• (select customer-name
• from borrower)

25
Intersect Operation

• Example
• To find all customers who have both a loan and an
  account at the bank
• (select distinct customer-name
• from depositor)
• intersect
• (select distinct customer-name
• from borrower)

26
Except Operation

• Example
• To find all customers who have an account but no
  loan at the bank
• (select distinct customer-name)
• from depositor)
• except
• (select customer-name
• from borrower)

27
Aggregate Functions

• These functions take a collection of values as
  input and return a single value.
• SQL offers five built-in aggregate functions
• Average avg
• Minimum min
• Maximum max
• Total sum
• Count count

28
Aggregate Functions (Cond)

• Example
• Find the average account balance at the
  Perryridge branch.
• select avg (balance)
• from account
• where branch-name Perryridge

29
Null Values

• Used to indicate absence of information about the
  value of an attribute.
• Can use special keyword null in a predicate to
  test for a null value.

30
Null Values (Cond)

• Example
• select loan-number
• from loan
• where amount is null

31
Nested Subqueries

• A subquery is a select-from-where expression that
  is nested within another query.
• Common use includes
• Perform tests for set membership
• Make set comparisons
• Determine set cardinality

32
Nested Subqueries (Cond)

• Example
• Find those customers who are borrowers from the
  bank and who appear in the list of account
  holders obtained in the subquery
• select distinct customer-name
• from borrower
• where customer-name in (select customer- name
  from depositor)

33
Views

• We define a view in SQL by using the create view
  command.
• To define a view, we must give the view a name
  and must state the query that computes the view.

34
Views (Cond)

• Example
• Using view all-customer, we can find all
  customers of the Perryridge branch
• select customer-name
• from all-customer
• where branch-name Perryridge

35
Complex Queries

• What are complex queries?
• Queries that are hard to write as a single SQL
  block.
• Way to compose multiple SQL blocks
• Derived Relations
• Subquery expression to be used in the from
  clause.
• The result relation must be given a name and the
  attributes can be renamed.
• Example To find the average account balance of
  those branches where the avg acct balance is gt
  1200
• select branch-name, avg-balance from
  (select branch-name, avg(balance)
• from account group by branch-name) as
  branch-avg (branch-name, avg-balance)
• where avg-balance gt 1200
• 
  
• 
  result relation
  renamed attribute
• ( Note balance is an attribute in the relation.
  Since we're calculating the average balance it's
  more meaningful to rename balance to avg-balance
  )

36
Complex Queries cont'd

• With clause
• Makes the query logic clearer by providing ways
  to define temporary views
• view, like procedures ( in structure programming
  ), can be broken up into smaller views for
  clarity and reusability.
• Permits view definition to be used in multiple
  places within a query.

37
Modification of the Database

• Add, Remove, and Change information.
• Insertion ( add ) Insert data ( tuple or set of
  tuples ) into a relation
• Ex insert into Students values ( 'Jane Doe',
  '4.0', 'honor')
• inserts the student Jane Doe into
  Students whose GPA 4.0 and who is an honor
  student.
• Deletion ( remove ) Deletes the entire tuple
  from a relation.
• Ex delete from Students where name"Jane Doe"
• Update ( change ) Changes a value in a tuple
  without changing all values in the tuple.
• Ex
• 1. update Students set gpa 3.5 where name
  'Jane Doe'
• 2. update Students set dean_list case
• when gpa lt 4.0 then 'regular' else 'honor'
• end

38
Joined Relations

• SQL provides mechanisms for joining relations,
  including condition joins and natural joins.
• Ways to join relations
• Inner join - Combines two relations which contain
  a common field and eliminating tuples that don't
  match.
• left outer join - Combines two relations which
  contain a common field that results in tuples in
  left relation to be preserved and the unmatched
  tuples in the right relation filled with null
  values.
• right outer join - Combines two relations which
  contain a common field that results in tuples in
  right relation to be preserved and the unmatched
  tuples in the left relation filled with null
  values.
• natural join Similar to inner join, however,
  the common attributes of the relations will
  appear only once.

39
Examples of joined relations

• Suppose we have two relations loan and borrower
• Inner join relation
• loan inner join borrower on loan.loan-num
  borrower.loan-num
• Natural inner join loan natural inner join
  borrower

loan-num
branch-name
amount
cust-name
loan-num
L-170
Downtown
3000
Jones
L-170
L-230
Redwood
L-230
4000
Smith

L-260
PerryRidge
1700
L-155
Hayes
Loan
Borrower
loan-num
loan-num
branch-name
amount
cust-name
branch-name
amount
Downtown
Downtown
L-170
L-170
3000
Jones
L-170
3000
L-230
Redwood
L-230
L-230
Redwood
4000
Smith
4000
loan-num
branch-name
amount
cust-name
amount
cust-name
Downtown
L-170
L-170
3000
3000
Jones
Jones
L-230
Redwood
L-230
4000
Smith
4000
Smith
40
Joined relations contd

• left outer join
• Syntax loan left outer join borrower on
  loan.loan-num borrower.loan-num
• Right outer join
• Syntax loan right outer join borrower on
  loan.loan-num borrower.loan-num

loan-num
loan-num
branch-name
amount
cust-name
branch-name
amount
Downtown
Downtown
L-170
L-170
3000
3000
Jones
L-170
L-230
Redwood
L-230
L-230
Redwood
4000
Smith
4000
1700
L-260
Perryridge
null
null
loan-num
loan-num
branch-name
amount
cust-name
branch-name
amount
L-170
Downtown
L-170
Downtown
3000
3000
Jones
L-170
L-230
Redwood
L-230
L-230
Redwood
4000
Smith
4000
null
null
null
Hayes
L-155
41
SQL Integrity Constraints

• Adherence to entity integrity and referential
  integrity rules is crucial
• Entity integrity enforced automatically if
  primary key specified in CREATE TABLE command
  sequence
• Referential integrity can be enforced in
  specification of FOREIGN KEY
• Other specifications to ensure conditions met
• ON DELETE RESTRICT
• ON UPDATE CASCADE

42
Data Manipulation Commands

• Common SQL Commands

Table 5.3
43
Data Entry and Saving

• Enters data into a table
• Saves changes to disk

INSERT INTO lttable namegt VALUES (attribute 1
value, attribute 2 value, etc.)
COMMIT lttable namesgt
44
Listing Table Contents and Other Commands

• Allows table contents to be listed
• UPDATE command makes data entry corrections
• ROLLBACK command restores database back to
  previous condition if COMMIT hasnt been used
• DELETE command removes table row

SELECT ltattribute namesgt FROM lttable namesgt
45
Queries

• Creating partial listings of table contents

SELECT ltcolumn(s)gtFROM lttable namegtWHERE
ltconditionsgt
Table 5.4 Mathematical Operators
46
Examples

• Mathematical operators
• Mathematical operators on character attributes
• Mathematical operators on dates

SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM
PRODUCT WHERE V_CODE ltgt 21344
SELECT P_CODE,P_DESCRIPT,P_ONHAND,P_MIN,P_PRICEFR
OM PRODUCTWHERE P_CODE lt 1558-QWI
SELECT P_DESCRIPT,P_ONHAND,P_MIN,P_PRICE,PINDATEF
ROM PRODUCTWHERE P_INDATE gt 01/20/2002
47
Computed Columns

• New columns can be created through valid
  computations or formulas
• Formulas may contain mathematical operators
• May contain attributes of any tables specified in
  FROM clause
• Alias is alternate name given to table or column
  in SQL statement

SELECT P_DESCRIPT,P_ONHAND,P_PRICE,P_ONHANDP_PRIC
E AS TOTVALUE FROM PRODUCT
48
Operators

• Logical AND, OR, NOT
• Rules of precedence
• Conditions within parenthesis executed first
• Boolean algebra
• Special
• BETWEEN - defines limits
• IS NULL - checks for nulls
• LIKE - checks for similar string
• IN - checks for value in a set
• EXISTS - opposite of IS NULL

SELECT FROM PRODUCTWHERE V_CODE 21344 OR
V_CODE 24288
49
Advanced Data ManagementCommands

• ALTER - changes table structure
• ADD - adds column
• MODIFY - changes column characteristics
• Entering data into new column

ALTER TABLE lttable namegtADD (ltcolumn namegt ltnew
column characteristicsgt) ALTER TABLE lttable
namegtMODIFY (ltcolumn namegt ltnew column
characteristicsgt)
UPDATE PRODUCTSET P_SALECODE 2WHERE P_CODE
1546-QQ2
50
Advanced Data Management Commands (cont.)

• Dropping a column
• Arithmetic operators and rules of precedence

ALTER TABLE VENDORDROP COLUMN V_ORDER
Table 5.5
51
Advanced Data Management Commands (cont.)

• Copying parts of tables
• Deleting a table from database
• Primary and foreign key designation

INSERT INTO ltreceiving tablegt ltreceiving tables
column namesgtSELECT ltcolumn names of the columns
to be copiedgtFROM ltcontributing table namegt
DROP TABLE PART
ALTER TABLE LINE ADD PRIMARY KEY (INV_NUMBER,
LINE_NUMBER) ADD FOREIGN KEY (INV_NUMBER)
REFERENCES INVOICE ADD FOREIGN KEY (PROD_CODE)
REFERENCES PRODUCT
52
Example Aggregate Function Operations

• COUNT
• MAX and MIN

SELECT COUNT(DISTINCT V_CODE)FROM
PRODUCT SELECT COUNT(DISTINCT V_CODE)FROM
PRODUCTWHERE P_PRICE lt 10.00
SELECT MIN(P_PRICE)FROM PRODUCT SELECT P_CODE,
P_DESCRIPT, P_PRICEFROM PRODUCTWHERE P_PRICE
MAX(P_PRICE)
53
Example Aggregate Function Operations (cont.)

• SUM
• AVG

SELECT SUM(P_ONHAND P_PRICE)FROM PRODUCT
SELECT P_DESCRIPT, P_ONHAND, P_PRICE, V_CODEFROM
PRODUCTWHERE P_PRICE gt (SELECT AVG(P_PRICE)
FROM PRODUCT)ORDER BY P_PRICE DESC
54
More Complex Queries and SQL Functions

• Ordering a listing
• Results ascending by default
• Descending order uses DESC
• Cascading order sequence

ORDER BY ltattributesgt
ORDER BY ltattributesgt DESC
ORDER BY ltattribute 1, attribute 2, ...gt
55
More Complex Queries and SQL Functions (cont.)

• Listing unique values
• DISTINCT clause produces list of different values
• Aggregate functions
• Mathematical summaries

SELECT DISTINCT V_CODE FROM PRODUCT
56
More Complex Queries and SQL Functions (cont.)

• Grouping data
• Creates frequency distributions
• Only valid when used with SQL arithmetic
  functions
• HAVING clause operates like WHERE for grouping
  output

SELECT P_SALECODE, MIN(P_PRICE)FROM
PRODUCT_2GROUP BY P_SALECODE
SELECT V_CODE,COUNT(DISTINCT(P_CODE)),AVG(P_PRICE)
FROM PRODUCT_2 GROUP BY V_CODE HAVING
AVG(P_PRICE) lt 10
57
More Complex Queries and SQL Functions (cont.)

• Virtual tables creating a view
• CREATE VIEW command
• Creates logical table existing only in virtual
  memory
• SQL indexes

CREATE VIEW PRODUCT_3 ASSELECT P_DESCRIPT,
P_ONHAND, P_PRICEFROM PRODUCTWHERE P_PRICE gt
50.00
CREATE INDEX P_CODEXON PRODUCT(P_CODE)
58
More Complex Queries and SQL Functions (cont.)

• Joining database tables
• Data are retrieved from more than one table
• Recursive queries joins a table to itself
• Outer joins can be used when null values need
  to be included in query result

SELECT PRODUCT.P_DESCRIPT, PRODUCT.P_PRICE,
VENDOR.V_NAME, VENDOR.V_CONTACT,
VENDOR.V_AREACODE, VENDOR.V_PHONEFROM PRODUCT,
VENDORWHERE PRODUCT.V_CODE VENDOR.V_CODE
SELECT A.EMP_NUM,A.EMP_LNAME,A.EMP_MGR, B.EMP_LNAM
E FROM EMP A, EMP B WHERE A.EMP_MGRB.EMP_NUM ORDE
R BY A.EMP_MGR