Transcript Lecture Note 4

ITEC 3220M
Using and Designing Database Systems
Instructor: Prof. Z. Yang
Course Website:
http://people.yorku.ca/~zyang/itec
3220m.htm
Office: Tel 3049
Chapter 7
Introduction to Structured Query
Language (SQL)
Introduction to SQL
• SQL functions fit into two broad categories:
– Data definition language
•SQL includes commands to create
– Database objects such as tables, indexes, and views
– Commands to define access rights to those database
objects
– Data manipulation language
•Includes commands to insert, update, delete, and
retrieve data within the database tables
3
Introduction to SQL (continued)
4
Introduction to SQL (continued)
5
Introduction to SQL (continued)
6
Creating the Database
• Two tasks must be completed
– create the database structure
– create the tables that will hold the enduser data
7
Creating Table Structures
• Use one line per column (attribute) definition
• Use spaces to line up the attribute characteristics and
constraints
• 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
Table Creation Steps
Steps in table creation:
1.
Identify data types for attributes
2.
Identify columns that can and cannot be null
3.
Identify columns that must be unique
4.
Identify primary key-foreign key mates
5.
Determine default values
6.
Identify constraints on columns (domain specifications)
7.
Create the table
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>);
9
Data Types
10
An Example
CREATE TABLE STUDENT(
2 STU_NUM INTEGER
3 STU_NAME VARCHAR(15)
4 GPA DECIMAL(3,2)
5 PRIMARY KEY(STU_NUM));
Identify primary key
Unique
specifications
NOT NULL UNIQUE,
NOT NULL, Define attributes and
their data types
NOT NULL,
Not null
specifications
Table created.
Semicolon indicates
end of command
Comma indicates end
of description of an
attribute
Message indicates
table was created
11
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
12
Advanced Data Definition
Commands
• All changes in the table structure are
made by using the ALTER command
– Followed by a keyword that produces
specific change
– Three options are available
•ADD
•MODIFY
•DROP
13
Changing a Column’s Data Type
• ALTER can be used to change data type
• Some RDBMSs (such as Oracle) do not
permit changes to data types unless the
column to be changed is empty
14
Changing a Column’s Data
Characteristics
• Use ALTER to change data
characteristics
• If the column to be changed already
contains data, changes in the column’s
characteristics are permitted if those
changes do not alter the data type
15
Adding or Dropping a Column
• Use ALTER to add a column
– Do not include the NOT NULL clause for new
column
• Use ALTER to drop a column
– Some RDBMSs impose restrictions on the
deletion of an attribute
16
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
17
Common SQL Data Manipulation
Commands
18
Data Entry
• Enters data into a table
INSERT INTO <table name>
VALUES (attribute 1 value, attribute 2 value, … etc.);
19
Listing Table Rows
• SELECT
– Used to list contents of table
• Syntax
– SELECT columnlist
FROM tablename
• Columnlist represents one or more
attributes, separated by commas
• Asterisk can be used as wildcard character
20
to list all attributes
Updating Table Rows
• UPDATE
– Modify data in a table
• Syntax
– UPDATE tablename
SET columnname = expression [, columname =
expression]
[WHERE conditionlist];
• If more than one attribute is to be updated in the
row, separate corrections with commas
21
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
– Will permanently save any changes made to
any table in the database
22
Restoring Table Contents
• ROLLBACK
– Used to restore the database to its previous condition
– Only applicable if COMMIT command has not been
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
23
Deleting Table Rows
• DELETE
– Deletes a table row
• Syntax
– DELETE FROM tablename
[WHERE conditionlist ];
• WHERE condition is optional
• If WHERE condition is not specified, all rows from
the specified table will be deleted
24
Selecting Rows with Conditional
Restrictions
• Select partial table contents by placing
restrictions on rows to be included in output
– Add conditional restrictions to the SELECT
statement, using WHERE clause
• Syntax
– SELECT columnlist
FROM tablelist
[ WHERE conditionlist ] ;
25
Comparison Operators
26
Arithmetic Operators:
The Rule of Precedence
• Perform operations within parentheses
• Perform power operations
• Perform multiplications and divisions
• Perform additions and subtractions
27
Special Operators
• BETWEEN
– Used to check whether attribute value is within a
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 value matches any
value within a value list
• EXISTS
– Used to check if a subquery returns any rows
28
More Complex Queries and SQL
Functions
• Listing unique values
– DISTINCT clause produces list of different values
SELECT DISTINCT V_CODE
FROMfunctions
PRODUCT;
• Aggregate
– Mathematical summaries
29
Example Aggregate
Function Operations
• COUNT
SELECT COUNT(DISTINCT V_CODE)
FROM PRODUCT;
SELECT COUNT(DISTINCT V_CODE)
FROM PRODUCT
WHERE P_PRICE <= 10.00;
• MAX and MIN
SELECT MIN(P_PRICE)
FROM PRODUCT;
SELECT P_CODE, P_DESCRIPT, P_PRICE
FROM PRODUCT
WHERE P_PRICE = MAX(P_PRICE);
30
Example Aggregate
Function Operations (Cont’d)
• SUM
SELECT SUM(P_ONHAND * P_PRICE)
FROM PRODUCT;
• AVG
SELECT P_DESCRIPT, P_ONHAND, P_PRICE, V_CODE
FROM PRODUCT
WHERE P_PRICE >
(SELECT AVG(P_PRICE) FROM PRODUCT)
ORDER BY P_PRICE DESC;
31
More Complex Queries and SQL
Functions (Cont’d)
• Ordering a listing
ORDER BY <attributes>
• Results ascending by default
– Descending order uses DESC
ORDER BY <attributes> DESC
• Cascading order sequence
ORDER BY <attribute 1, attribute 2, ...>
32
More Complex Queries and
SQL Functions (cont’d)
• Grouping data
– Creates frequency distributions
– Only valid when used with SQL arithmetic
functions SELECT P_SALECODE, MIN(P_PRICE)
FROM PRODUCT_2
GROUP BY P_SALECODE;
– HAVING clause operates like WHERE for grouping
output
SELECT
V_CODE,COUNT(DISTINCT(P_CODE)),AVG(P_PRICE)
FROM PRODUCT_2
GROUP BY V_CODE
33
HAVING AVG(P_PRICE) < 10;
SQL Exercise
• Write SQL code that will create the relation shown
below. Assume the following attribute data types:
– Customer_ID: integer
– Customer_Name: 25 characters
– Street: 25 characters
– City: 15 characters
– State: 15 characters
– Balance: number(8,2)
– Credit_Limit: number(8,2)
– Rep_Number: integer
Customer (Customer_ID, Customer_Name, Street, City, State,
Balance, Credit_Limit, Rep_Number)
34
SQL Exercise (cont’d)
• Find the ID and name of each customer located in the
city of Toronto.
• Find the ID and name for all customers with credit
limits that exceed their balances.
• Find the ID, name and available credit (the credit limit
minus balance) for each customer.
• Find the total number of customers and the total of
their balances.
• For each sales rep, list the rep number and the
average balance of the rep’s customers.
35
Summary
• SQL commands can be divided into two overall
categories:
– Data definition language commands
– Data manipulation language commands
• Basic data definition commands allow you to create
tables, indexes, and views
• Many SQL constraints can be used with columns
• Aggregate functions
– Special functions that perform arithmetic
computations over a set of rows
36
Summary (Cont’d)
• ORDER BY clause
– Used to sort output of a SELECT statement
– Can sort by one or more columns and use either
an ascending or descending order
• Join output of multiple tables with SELECT statement
• Natural join uses join condition to match only rows
with equal values in specified columns
37
Lab Instruction
• Before you go to the lab sessions, please use your
Passport York to create TEL lab account and AML
account. These two accounts will allow you to get
access to the ORACLE database. Please create these
two accounts as early as possible since it takes time
for these two accounts to take effect.
38
Lab Instruction (cont’d)
• Login to sit.yorku.ca
• Start Oracle SQL*PLUS environment by
typing the following command: sqlplus
• When prompted for the username/password
enter your_username@studb10g (where
your_username is your AML username) at the
username prompt and your AML password at
the password prompt.
39