database systems-lec5
TRANSCRIPT
-
8/14/2019 Database Systems-Lec5
1/60
Chapter 3
Structured Query Language
(SQL)
-
8/14/2019 Database Systems-Lec5
2/60
2
Introduction to SQL
SQL functions fit into two broad
categories:
1. Data definition language SQL includes commands to create
Database objects such as tables, indexes, and
views
Commands to define access rights to thosedatabase objects
Data manipulation language
Includes commands to insert, update, delete,
and retrieve data within the database tables
-
8/14/2019 Database Systems-Lec5
3/60
3
Introduction to SQL (continued)
SQL is relatively easy to learn
Basic command set has a vocabulary of
less than 100 words
Nonprocedural language
American National Standards Institute(ANSI) prescribes a standard SQL
Several SQL dialects exist
-
8/14/2019 Database Systems-Lec5
4/60
4
SQL Data Definition Commands
-
8/14/2019 Database Systems-Lec5
5/60
5
Data Manipulation Commands
-
8/14/2019 Database Systems-Lec5
6/60
6
Data Definition Commands
Examine the simple database model
(Figure 6.1) and the database tables that
will form the basis for the many SQL
examples
Understand the data environment
-
8/14/2019 Database Systems-Lec5
7/60
7
The Database Model
-
8/14/2019 Database Systems-Lec5
8/60
8
Creating the Database
Two tasks must be completed
create the database structure
create the tables that will hold the end-userdata
First task
RDBMS creates the physical files that willhold the database
Tends to differ substantially from oneRDBMS to another
-
8/14/2019 Database Systems-Lec5
9/60
9
Creating Database Structures
CREATE DATABASE ;
Example:
CREATE DATABASE SAMPLE1;
-
8/14/2019 Database Systems-Lec5
10/60
10
Data Types
Data type selection is usually dictated by
the nature of the data and by the intended
use
Pay close attention to the expected use of
attributes for sorting and data retrieval
purposes
-
8/14/2019 Database Systems-Lec5
11/60
11
Some Common SQL Data Types
DATE
VARCHAR(L)
CHAR(L)
DECIMAL
(L,D)
SMALLINT
INTEGER
Format
Year 0001 9999
Month 01 12
Day 01 - 31
VARCHAR(5)If Ali, database keeps 3 space
only.
CHAR(5)
If Ali, database keeps Ali__ 2
space wasted.
DECIMAL(5,2) 123.45
-32 768 32 767
-2 147 483 648+2 147 483 647
Description
Date
Characte
r
Numeric
Data Type
-
8/14/2019 Database Systems-Lec5
12/60
12
Some Common SQL Data Types
-
8/14/2019 Database Systems-Lec5
13/60
13
Creating Table Structures
Use one line per column (attribute) definition
Use spaces to line up the attribute characteristics andconstraints
Table and attribute names are capitalized
NOT NULL specification
UNIQUE specification
Primary key attributes contain both a NOT NULL and a
UNIQUE specification RDBMS will automatically enforce referential integrity
for foreign keys
Command sequence ends with a semicolon
-
8/14/2019 Database Systems-Lec5
14/60
14
Other SQL Constraints
NOT NULL constraint
Ensures that a column does not accept nulls
UNIQUE constraint
Ensures that all values in a column are unique
DEFAULT constraint
Assigns a value to an attribute when a new row is added to atable
CHECK constraint
Validates data when an attribute value is entered
-
8/14/2019 Database Systems-Lec5
15/60
15
SQL Indexes
When a primary key is declared, DBMSautomatically creates a unique index
Often need additional indexes
Using the CREATE INDEX command, SQLindexes can be created on the basis of anyselected attribute
Composite index Index based on two or more attributes
Often used to prevent data duplication
-
8/14/2019 Database Systems-Lec5
16/60
16
Creating Table Structures
CREATE TABLE ();
Example:
CREATE TABLE VENDOR(v_code varchar(5) not null primary key,
v_name varchar(20),v_contact varchar(15),
v_areacode integer,
v_phone varchar(10),
v_state char(2),
v_order Char(1));
-
8/14/2019 Database Systems-Lec5
17/60
17
Creating Table Structures
Example:
CREATE TABLE PRODUCT(
(p_code varchar(10) not null primary key,
p_descript varchar(50),
p_indate date,
p_onhand integer,
p_min integer,
p_price decimal(5,2),
p_discount decimal (3,2),
v_code varchar(5),
foreign key (v_code) references vendor
on delete restrict);
-
8/14/2019 Database Systems-Lec5
18/60
18
Data Manipulation Commands
Adding table rows
Saving table changes
Listing table rows Updating table rows
Restoring table contents
Deleting table rows
Inserting table rows with a select
subquery
-
8/14/2019 Database Systems-Lec5
19/60
19
Common SQL Data
Manipulation Commands
-
8/14/2019 Database Systems-Lec5
20/60
20
A Data View and Entry Form
-
8/14/2019 Database Systems-Lec5
21/60
21
Inserting Table Rows
Data Entry
INSERT INTO VALUES (attribute 1 value, attribute 2
value, etc.);
Examples: INSERT INTO VENDOR
VALUES(21225, Bryson, Inc., Smithson, 615, 223-3234, TN,
Y);
INSERT INTO PRODUCTVALUES(11 QER/31, Power painter, 15 psi., 3-nozzle, 07-02-
1999, 8.5, 109.99, 0.00, 25595);
-
8/14/2019 Database Systems-Lec5
22/60
22
Saving Table Changes
Changes made to table contents are not
physically saved on disk until
Database is closed
Program is closed
COMMIT command is used
Syntax
COMMIT [table names]
Example: COMMIT PRODUCT;
Will permanently save any changes made to
any table in the database.
-
8/14/2019 Database Systems-Lec5
23/60
23
Listing Table Rows
SELECT
Used to list contents of table
Syntax
SELECT columnlistFROM tablename
Columnlist represents one or more attributes,separated by commas
Asterisk can be used as wildcard character tolist all attributes
-
8/14/2019 Database Systems-Lec5
24/60
24
Listing Table Rows
Examples:
SELECT * FROM PRODUCT;
SELECT P_CODE, P_DESCRIPT, P_INDATE,P_ONHAND, P_MIN, P-PRICE, P_DISCOUNT,
V_CODE FROM PRODUCT;
-
8/14/2019 Database Systems-Lec5
25/60
25
Updating Table Rows
UPDATE Modify data in a table
Syntax UPDATE tablename
SET columnname = expression [, columname = expression][WHERE conditionlist];
Examples:
UPDATE PRODUCT SET P_INDATE = 12/11/96WHERE P_CODE = 13-Q2/P2;
If more than one attribute is to be updated inthe row, separate corrections with commas. Examples:
UPDATE PRODUCT SET P_INDATE = 12/11/96, P_PRICE = 15.99,P_MIN=10 WHERE P_CODE = 13-Q2/P2;
-
8/14/2019 Database Systems-Lec5
26/60
26
Restoring Table Contents
ROLLBACK
Used restore the database to its previous
condition
Only applicable if COMMIT command has notbeen used to permanently store the changes in
the database
Syntax
ROLLBACK;
COMMIT and ROLLBACK only work with data
manipulation commands that are used to add,
modify, or delete table rows
-
8/14/2019 Database Systems-Lec5
27/60
27
Deleting Table Rows
DELETE
Deletes a table row
Syntax DELETE FROM tablename
[WHERE conditionlist];
Examples:
DELETE FROM PRODUCT WHERE P_CODE = 2238/QPD; DELETE FROM PRODUCT WHERE P_MIN = 5;
WHERE condition is optional
If WHERE condition is not specified, all rows from thespecified table will be deleted.
-
8/14/2019 Database Systems-Lec5
28/60
28
Inserting Table Rows with a
Select Subquery INSERT
Inserts multiple rows from another table(source)
Uses SELECT subquery Query that is embedded (or nested) inside another
query
Executed first
Syntax INSERT INTO tablename SELECT columnlist
FROM tablename
-
8/14/2019 Database Systems-Lec5
29/60
-
8/14/2019 Database Systems-Lec5
30/60
30
Selected PRODUCT Table
Attributes for VENDOR Code21344
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE V_CODE = 21344;
-
8/14/2019 Database Systems-Lec5
31/60
31
Comparison Operators
Selected PRODUCT Table
-
8/14/2019 Database Systems-Lec5
32/60
32
Selected PRODUCT Table
Attributes for VENDOR Codes
Other than 21344
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCTWHERE V_CODE 21344;
Selected PRODUCT Table
-
8/14/2019 Database Systems-Lec5
33/60
33
Selected PRODUCT Table
Attributes
with a P_PRICE Restriction
SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE
FROM PRODUCT
WHERE P_PRICE
-
8/14/2019 Database Systems-Lec5
34/60
34
Selected PRODUCT Table
Attributes:
The ASCII Code Effect
SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE
FROM PRODUCTWHERE P_CODE < 1558-QW1;
-
8/14/2019 Database Systems-Lec5
35/60
35
Selected PRODUCT Table
Attributes: Date Restriction
SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE, P_INDATE
FROM PRODUCT
WHERE P_INDATE >= 20-Jan-2004;
-
8/14/2019 Database Systems-Lec5
36/60
36
SELECT Statement
with a Computed Column
SELECT P_DESCRIPT, P_ONHAND, P_PRICE, P_ONHAND* P_PRICE
FROM PRODUCT;
-
8/14/2019 Database Systems-Lec5
37/60
37
SELECT Statement with a
Computed Column and an Alias
SELECT P_DESCRIPT, P_ONHAND, P_PRICE, P_ONHAND*
P_PRICE AS TOTAL VALUE
FROM PRODUCT;
-
8/14/2019 Database Systems-Lec5
38/60
-
8/14/2019 Database Systems-Lec5
39/60
39
Arithmetic Operators:
The Rule of Precedence Perform operations within parentheses
Perform power operations
Perform multiplications and divisions
Perform additions and subtractions
Selected PRODUCT Table
-
8/14/2019 Database Systems-Lec5
40/60
40
Selected PRODUCT Table
Attributes:
The Logical ORSELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE V_CODE = 21344 OR V_CODE = 24288;
-
8/14/2019 Database Systems-Lec5
41/60
Selected PRODUCT Table
-
8/14/2019 Database Systems-Lec5
42/60
42
Selected PRODUCT Table
Attributes:
The Logical AND and ORSELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE (P_PRICE < 50 AND P_DATE > 15-Jan-2004)
OR V_CODE = 24288;
-
8/14/2019 Database Systems-Lec5
43/60
43
Special Operators BETWEEN
Used to check whether attribute value is withina range
IS NULL Used to check whether attribute value is null
LIKE Used to check whether attribute value
matches a given string pattern
IN
Used to check whether attribute valuematches any value within a value list
EXISTS Used to check if a subquery returns any rows
-
8/14/2019 Database Systems-Lec5
44/60
-
8/14/2019 Database Systems-Lec5
45/60
45
Special Operators
IS NULL is used to check whether an attribute
value is null.
Examples:SELECT P_CODE, P_DESCRIPT, V_CODE
FROM PRODUCT
WHERE V_CODE IS NULL;
SELECT P_CODE, P_DESCRIPT, P_INDATE
FROM PRODUCT
WHERE P_INDATE IS NULL;
-
8/14/2019 Database Systems-Lec5
46/60
46
Special Operators
LIKE is used to check for similar character
strings.
Examples:
SELECT V_NAME, V_CONTACT, V_AREACODE, V_PHONE
FROM VENDOR
WHERE V_CONTACT LIKE Smith%;
SELECT V_NAME, V_CONTACT, V_AREACODE, V_PHONE
FROM VENDOR
WHERE V_CONTACT LIKE SMITH%;
-
8/14/2019 Database Systems-Lec5
47/60
47
Special Operators
IN is used to check whether an attribute value
matches a value contained within a (sub)set
of listed values.SELECT *
FROM PRODUCT
WHERE V_CODE IN (21344, 24288);
EXISTS is used to check whether an attributehas value.
DELETE FROM PRODUCT WHERE P_CODE EXISTS;
SELECT * FROM PRODUCT WHERE V_CODE EXISTS;
Ad d D t D fi iti
-
8/14/2019 Database Systems-Lec5
48/60
48
Advanced Data Definition
Commands All changes in the table structure are
made by using the ALTER command
Followed by a keyword that producesspecific change
Three options are available
ADD
MODIFY
DROP
Ch i C l D t
-
8/14/2019 Database Systems-Lec5
49/60
49
Changing a Columns Data
Type ALTER can be used to change data type
Some RDBMSs (such as Oracle) do not
permit changes to data types unless thecolumn to be changed is empty
-
8/14/2019 Database Systems-Lec5
50/60
50
Changing a Columns Data
Characteristics
Use ALTER to change data characteristics
ALTER TABLE MODIFY ( );
If the column to be changed already contains data, changes inthe columns characteristics are permitted if those changes donot alter the data type
Examples: Changing a Columns Data Type
ALTERTABLE PRODUCT MODIFY (V_CODE CHAR(5));
Changing Attribute Characteristics
ALTER TABLE PRODUCT MODIFY (P_PRICEDECIMAL(9,2));
Adding a New Column to the Table
ALTER TABLE PRODUCT ADD (P_SALECODE CHAR(1));
Th Eff t f D t E t i t th
-
8/14/2019 Database Systems-Lec5
51/60
51
The Effect of Data Entry into the
New P_SALECODE Column
UPDATE PRODUCT
SET P_SALECODE = 2
WHERE P_CODE = 1546-QQ2;
U d t f th P SALECODE
-
8/14/2019 Database Systems-Lec5
52/60
52
Update of the P_SALECODE
Column in Multiple Data Rows
UPDATE PRODUCT SET P_SALECODE = 1
WHERE P_CODE IN (2232/QWE, 2232/QTY);
e ect o u t p e ata
-
8/14/2019 Database Systems-Lec5
53/60
53
e ect o u t p e ataUpdates in the PRODUCT
Table (MS Access)
UPDATE PRODUCT
SET P_SALECODE = 2WHERE P_INDATE < 25-Dec-2003;
UPDATE PRODUCT
SET P_SALECODE = 1WHERE P_INDATE >= 16-Jan-2004 AND
P_INDATE < 10-Feb-2004;
e ect o u t p e ata
-
8/14/2019 Database Systems-Lec5
54/60
54
e ect o u t p e ataUpdates in the PRODUCT
Table (MS Access)
-
8/14/2019 Database Systems-Lec5
55/60
55
Copying Parts of Tables
SQL permits copying contents of selected
table columns so that the data need not
be reentered manually into newly created
table(s)
First create the PART table structure
Next add rows to new PART table using
PRODUCT table rows
PART Attributes Copied
-
8/14/2019 Database Systems-Lec5
56/60
56
PART Attributes Copied
from the PRODUCT Table
CREATE TABLE PART (
PART_CODE CHAR(8) NOT NULL,
PART_DESCRIPT CHAR(35),PART_PRICE DECIMAL(8,2),
PRIMARY KEY(PART_CODE));
INSERT INTO PART (PART_CODE, PART_DESCRIPT, PART_PRICE)
SELECT P_CODE, P_DESCRIPT, P_PRICE
FROM PRODUCT;
PART Attributes Copied
-
8/14/2019 Database Systems-Lec5
57/60
57
PART Attributes Copied
from the PRODUCT Table
-
8/14/2019 Database Systems-Lec5
58/60
58
Adding or Dropping a Column
Use ALTER to add a column
Do not include the NOT NULL clause for new column
Examples:ALTER TABLE PRODUCT ADD PRIMARY KEY (P_CODE);
ALTER TABLE PRODUCT ADD FOREIGN KEY (V_CODE) REFERENCES
VENDOR;
ALTER TABLE PRODUCT ADD PRIMARY KEY (P_CODE) ADD FOREIGN KEY(V_CODE) REFERENCES VENDOR;
-
8/14/2019 Database Systems-Lec5
59/60
59
Adding or Dropping a Column
Use ALTER to drop a column
Some RDBMSs impose restrictions on the
deletion of an attribute
DROP TABLE ;
Example:
DROP TABLE PART;
R f
-
8/14/2019 Database Systems-Lec5
60/60
60
References
ROB, P. AND CORONEL, C., 2004, Database
Systems. 6th Ed., Thomson Course Technology