1

Basic SQL

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:(i) Select

- Used to list the attributes desired in the result of a query.(ii) From

- Lists the relations to be scanned in the evaluation of the expression.(iii) 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 <table name>(<attribute1 name and attribute1 characteristics,attribute2 name and attribute2 characteristics,attribute3 name and attribute3 characteristics,primary key designation,foreign key designation and foreign key requirement>);

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 <creator>

Example:CREATE 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 <domain_name> AS DATA_TYPE[DEFAULT <default_value>][CHECK (<condition>)]

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 > 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,000select loan numberfrom loanwhere 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 (Con’d)

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 (Con’d)

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 (Con’d)

Example: Find the names of all customers whose

street address includes the substring ‘Main’select customer-namefrom customerwhere 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-namefrom depositor)union(select customer-namefrom borrower)

25

Intersect Operation

Example: To find all customers who have both a loan

and an account at the bank:(select distinct customer-namefrom depositor)intersect(select distinct customer-namefrom 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-namefrom 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 (Con’d)

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 (Con’d)

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 (Con’d)

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 (Con’d)

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 > 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 > 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 relationEx: 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 < 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

Loan Borrower

loan-num branch-name amount cust-name

L-170

L-230L-260

Downtown

Redwood`PerryRidge 1700

4000

3000 Jones

Smith

Hayes

L-170

L-230

L-155

L-170

L-230

Downtown

Redwood

3000

4000

Jones

Smith

loan-num

amount cust-name

L-170

L-230

3000

4000

Jones

Smith

branch-name amount cust-name loan-num

L-170

L-230

Downtown

Redwood

3000

4000

Jones

Smith

L-170

L-230

branch-name amount

L-170

L-230

Downtown

Redwood

3000

4000

40

Joined relations cont’d 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

branch-name amount cust-name loan-num

L-170

L-230

Downtown

Redwood

3000

4000

Jones

Smith

L-170

L-230

branch-name amount

L-170

L-230

Downtown

Redwood

3000

4000

loan-num

L-260 Perryridge 1700 null null

branch-name amount cust-name loan-num

L-170

L-230

Downtown

Redwood

3000

4000

Jones

Smith

L-170

L-230

branch-name amount

L-170

L-230

Downtown

Redwood

3000

4000

loan-num

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 <table name> VALUES (attribute 1 value, attribute 2 value, … etc.);

COMMIT <table names> ;

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 hasn’t been used DELETE command removes table row

SELECT <attribute names> FROM <table names>;

45

Queries Creating partial listings of table contents

SELECT <column(s)>FROM <table name>WHERE <conditions>;

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_CODEFROM PRODUCTWHERE V_CODE <> 21344;

SELECT P_CODE,P_DESCRIPT,P_ONHAND,P_MIN,P_PRICEFROM PRODUCTWHERE P_CODE < ‘1558-QWI’;

SELECT P_DESCRIPT,P_ONHAND,P_MIN,P_PRICE,PINDATEFROM PRODUCTWHERE P_INDATE >= ‘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_ONHAND*P_PRICE 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 <table name>ADD (<column name> <new column characteristics>);

ALTER TABLE <table name>MODIFY (<column name> <new column characteristics>);

UPDATE PRODUCTSET P_SALECODE = ‘2’WHERE P_CODE = ‘1546-QQ2’;

50

Advanced Data Management Commands (con’t.)

Dropping a column

Arithmetic operators and rules of precedence

ALTER TABLE VENDORDROP COLUMN V_ORDER;

Table 5.5

51

Advanced Data Management Commands (con’t.)

Copying parts of tables

Deleting a table from database

Primary and foreign key designation

INSERT INTO <receiving table> <receiving table’s column names>SELECT <column names of the columns to be copied>FROM <contributing table name>;

DROP TABLE PART;

ALTER TABLE LINEADD PRIMARY KEY (INV_NUMBER, LINE_NUMBER)ADD FOREIGN KEY (INV_NUMBER) REFERENCES INVOICEADD 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 <= 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 (con’t.)

SUM

AVG

SELECT SUM(P_ONHAND * P_PRICE)FROM PRODUCT;

SELECT P_DESCRIPT, P_ONHAND, P_PRICE, V_CODEFROM PRODUCTWHERE P_PRICE > (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 <attributes>

ORDER BY <attributes> DESC

ORDER BY <attribute 1, attribute 2, ...>

55

More Complex Queries and SQL Functions (con’t.)

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 (con’t.)

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_2GROUP BY V_CODEHAVING AVG(P_PRICE) < 10;

57

More Complex Queries and SQL Functions (con’t.)

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 > 50.00;

CREATE INDEX P_CODEXON PRODUCT(P_CODE);

58

More Complex Queries and SQL Functions (con’t.)

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_LNAME FROM EMP A, EMP BWHERE A.EMP_MGR=B.EMP_NUMORDER BY A.EMP_MGR