sql overview , oracle data type , ddl and constraints · pdf filedatabase languages •data...
TRANSCRIPT
Prepared by L. Nouf Almujally & Aisha AlArfaj
1
Ref. Chapter6
I S220 : Dat abase Fundam ent a l s
Lecture6: SQL Overview , Oracle Data Type , DDL and Constraints
Part #1
Coll
ege
of
Com
pute
r an
d I
nfo
rmat
ion S
cien
ces
- In
form
atio
n S
yst
ems
Dep
t.
The Process of Database Design
Lec
ture
4
2
Conceptual Design
Logical Design
(Relational Model)
Physical Design
-Create schema (DDL)
-Load Data (DML)
Database Languages
Lec
ture
6
3
Database Languages
• It is made up of three components:
1. Data Definition Language (DDL)
2. Data Manipulation Language (DML)
3. Data Control Language (DCL)
• Data Definition Language (DDL)
• Allows the DBAs or users to describe and name entities, attributes, and relationships required for the application ( define a schema or modify an existing one)
• plus any associated integrity and security constraints. 4
Lec
ture
6
Database Languages
• Data Manipulation Language (DML) • provides basic data manipulation operations on data held
in the database – include • Insertion of new data into the database • Modification of data stored in the database • Retrieval of data contained in the database • Deletion of data from database
• Two type of DML: • Procedural DML
• allows user to tell system exactly how to manipulate data. • Ex. Oracle’s PL/SQL
• Non-Procedural DML • allows user to state what data is needed rather than how it
is to be retrieved. • Example : SQL (Structured Query Languages)
5
Lec
ture
6
Database Languages
• The Data Control Language (DCL)
• This component of the database language is used to create privileges to allow users access to the database.
6
Lec
ture
6
Structured Query Language
SQL Lec
ture
6
7
SQL Overview
• Official pronunciation is ‘S-Q-L‘ or ( see-qual)
• SQL: Structured Query Language
• The standard for relational database management systems (RDBMS) such as Oracle Database.
• All SQL statements are instructions to the database.
• Easy to learn:
• Consists of standard English words, case insensitive
• It is a non-procedural language:
• you specify what information you require, rather than how to get it. In other words, SQL does not require you to specify the access methods to the data
Lec
ture
6
8
SQL Overview
• SQL provides statements for a variety of tasks, including:
1. Querying data( Select command)
2. Inserting, updating, and deleting rows in a table (DML)
3. Creating, replacing, altering, and dropping objects(DDL)
4. Controlling access to the database and its objects(DCL)
5. Guaranteeing database consistency and integrity(DCL)
Lec
ture
6
9
SQL in Oracle • SQL is made up of 4 components:
1.Data Definition Language (DDL)
2.Data Manipulation Language (DML)
3.Data Control Language (DCL)
4.Transaction control statements
10
Lec
ture
6
SQL in Oracle 1. The Data Definition Language (DDL)
This component of the SQL language is used to create and modify tables and other objects in the database.
For tables there are three main commands:
1. CREATE TABLE tablename : to create a table in the database
2. DROP TABLE tablename : to remove a table from the database
3. ALTER TABLE tablename : to add or remove columns from a table in the database
11
Lec
ture
6
SQL in Oracle
2. The Data Manipulation Language (DML)
component of the SQL language is used to manipulate data within a table.
There are four main commands:
1. SELECT : to select rows of data from a table.
2. INSERT : to insert rows of data into a table.
3. UPDATE : to change rows of data in a table.
4. DELETE : to remove rows of data from a table.
12
Lec
ture
6
SQL in Oracle
3. The Data Control Language (DCL)
This component of the SQL language is used to create privileges to allow users access to, and manipulation of, the database.
There are two main commands:
1.GRANT : to grant a privilege to a user
2.REVOKE : to revoke (remove) a privilege from a user
4. Transaction control statements
• COMMIT, ROLLBACK
13
Lec
ture
6
Built-In Oracle Data type Summary Built-in Data type Description Max Size: Oracle 9i/10g
VARCHAR2(size [BYTE | CHAR]) Variable length character string having
maximum length size bytes.
You must specify size
4000 bytes minimum is 1
CHAR(size)
Fixed length character data of length size
bytes. This should be used for fixed length
data. Such as codes A100, B102...
2000 bytes Default and minimum size is 1 byte.
NUMBER(p,s) Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127.
The precision p can range from 1 to 38. The scale s can range from -84 to 127.
DATE Valid date range from January 1, 4712 BC to December 31, 9999 AD.
from January 1, 4712 BC to December 31, 9999 AD.
TIMESTAMP[timePrecsion]
Year, month, and day values of date, as well as hour, minute, and second values of time,
Accepted values of fractional_seconds_precision are 0 to 9. (default = 6)
Lec
ture
6
14
NUMBER(p,s)
• Syntax for specifying numbers
Number (precision, scale)
• Precision is the maximum digits of numbers
• Scale specifies the position of decimal point.
• E.g.
• Number(5) 5 digit integer, ex) 12345
• Number(6,2) 6 digit (not including decimal point) decimal number with 2 digits after the decimal point , ex) 1234.56
• Can store 1—38 digits precision
Lec
ture
6
15
String
To store strings, you can choose from:
• Char (size)
• stores fixed-length character strings of up to 2000 characters. Eg. char(10)
• Use it to store short strings
• Varchar2 (size)
• Stores variable-length strings of up to 4000 characters long. Eg. Varchar2(50)
• Preferred for most string types
• Must specify the size
String values are quoted with single quotes
• Eg ‘12234’, ‘abcd’, ‘a12’
Lec
ture
6
16
Date and time
• Oracle uses the date data type to store both date and time • Always uses 7 bytes for date-time data. • Oracle date has rich formats, you need to specify it SS second 0-59 MI Minute 0-59 HH Hour 1-12 HH24 Military hour 1-24 DD day of month 1-31 (depends on month) DAY day of the week Sunday-Saturday D day of the week 1-7 MM month number 1-12 MON month abbreviated Jan—Dec Month Month spelled out January-December YY last 2 digits of year eg, 98 YYYY full year value eg, 1998
Lec
ture
6
17
Date and time
• Example of date format:
• ‘dd-mon-yyyy’ 01-dec-2001
• ‘dd/mm/yyyy’ 01/12/2001
• ‘mm-dd-yy hh:mi:ss’ 12-01-01 12:30:59
• Default format: ‘dd-mon-yyyy’
• Current date and time:
• Sysdate
Lec
ture
6
18
Lec
ture
6
19
Data Definition language ( DDL )
Table Creation
20
Lec
ture
6
Data Definition (Creating a table)
CREATING A TABLE Syntax:
• Table Name CONDITIONS :
• can not exceed 30 characters long
• Must begin with an alphabetic character
• May contain letters, numbers, $, # and _
• Should not be an oracle reserved word
• Should be descriptive
Lec
ture
6
21
create table table-name ( column-name1 datatype , …….. column-nameN datatype );
Integrity Constraints
• An integrity constraint defines a business rule for a table column.
• When enabled, the rule will be enforced by oracle
• constraints can be defined at either the table level or the column level.
• If the results of an INSERT or UPDATE statement violate an integrity constraint, the statement will be rolled back.
• Note : If you don’t give the constraint name, the system will generate a name automatically, but the name is hard for human understanding.
Lec
ture
6
22
Integrity Constraints
• Constraint clauses can appear in the following statements:
1. CREATE TABLE
2. ALTER TABLE
3. CREATE VIEW
4. ALTER VIEW
Lec
ture
6
23
The FIFTH types of integrity constraint
1. NOT NULL constraint
2. UNIQUE constraint
3. PRIMARY KEY constraint
4. FOREIGN KEY constraint
5. CHECK constraint Lec
ture
6
24
NOT NULL constraint
• The NOT NULL constraint enforces a column to NOT accept NULL values.
• The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.
• Defined at column level ONLY
Syntax:
OR
Lec
ture
6
25
CREATE TABLE table-name ( column-name1 datatype NOT NULL );
CREATE TABLE table-name ( column-name1 datatype CONSTRAINT constraint_name NOT NULL );
CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE CONSTRAINT emp_hire_date NOT NULL );
Example: NOT NULL
System named
User named
Lec
ture
6
26
Example: NOT NULL
• Example : The following SQL enforces the "P_Id" column and the "LastName" column to not accept NULL values:
CREATE TABLE Persons ( P_Id char(5) NOT NULL, LastName varchar2(255) NOT NULL, FirstName varchar2(255), Address varchar2(255), City varchar2(255) );
Lec
ture
6
27
SQL NULL
• If a column in a table is optional, we can insert a new record or update an existing record without adding a value to this column. This means that the field will be saved with a NULL value.
• NULL values are treated differently from other values.
• NULL is used as a placeholder for unknown or inapplicable values.
• Note: It is not possible to compare NULL and 0; they are not equivalent.
• It is not possible to test for NULL values with comparison operators, such as =, <, or <>.
• We will have to use the IS NULL and IS NOT NULL operators instead.
Lec
ture
6
28
UNIQUE constraint
• The UNIQUE constraint prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.
• Defined at either the table level or the column level
• Unique columns are not automatically NOT NULL
Syntax:
Lec
ture
6
29
1- column level CREATE TABLE table-name (
column-name1 datatype UNIQUE ); 2- table level CREATE TABLE table-name ( …….
[CONSTRAINT constraint-name] UNIQUE (Columns_list)) ;
The UNIQUE Constraint
EMPLOYEES
UNIQUE constraint
Allowed
Not allowed: already exists
Lec
ture
6
30
Example: UNIQUE
• The following SQL creates a UNIQUE constraint on the "P_Id" column when the "Persons" table is created:
CREATE TABLE Persons ( P_Id char(5) NOT NULL UNIQUE, LastName varchar2(255) NOT NULL, FirstName varchar2(255), Address varchar2(255),
Telephone varchar2(10), City varchar2(255),
UNIQUE (LastName, FirstName),
CONSTRAINT tel_unique UNIQUE (telephone) );
Lec
ture
6
31
Column level
Table level
Primary Key Constraint
• A primary key constraint combines a NOT NULL constraint and a UNIQUE constraint in a single declaration. That is, it prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null.
• A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.
• Defined at either the table level or the column level
• Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
Lec
ture
6
32
Primary Key Constraint
OR
Lec
ture
6
33
1- Table level CREATE TABLE table-name (
column-name1 datatype, …….. column-nameN datatype, [CONSTRAINT constraint-name] PRIMARY KEY (columns-name));
2- Column level CREATE TABLE table-name (
column-name1 datatype PRIMARY KEY, …….. column-nameN datatype);
Example: Primary Key
Column Level
CREATE TABLE Department (
Dept_no char(4) PRIMARY KEY,
Dept_name varchar2(25));
OR Table Level
CREATE TABLE Department (
dept_no char(4),
Dept_name varchar2(25),
CONSTRAINT dept_PK PRIMARY KEY (dept_no));
Lec
ture
6
34
Column level
Table level
FOREIGN KEYS constraint
foreign key constraint requires values in one table to match values in another table.
Syntax:
Lec
ture
6
35
CREATE TABLE table-name ( column-name1 datatype,
……
……
[CONSTRAINT constraint-name] FOREIGN KEY
(Column_name) REFERENCES referenced_table
(column_in_referenced_table) );
The FOREIGN KEY Constraint DEPARTMENTS
EMPLOYEES
FOREIGN
KEY
INSERT INTO Not allowed (9 does not
exist)
Allowed
PRIMARY
KEY
Lec
ture
6
36
90
Referential integrity constraints
Lec
ture
6
37
DeptNo DeptName
101 IS
102 CS
103 NW
EmpId EmpName DeptNo
1 Aesha 101
2 Nouf 101
4 Sara 102
3 Dena 103
DeptNo DeptName
101 IS
103 NW
EmpId EmpName DeptNo
1 Aesha 101
2 Nouf 101
4 Sara 102
3 Dena 103
dangling reference (is a reference to an object that doesn't exist anymore)!!
Violate referential integrity
Example: Foreign Keys
Create table Staff(
staff_no char(3),
staff_name varchar2(20),
dept_no char(4),
CONSTRAINT staff_fk FOREIGN KEY (dept_no) REFERENCES department (dept_no));
Lec
ture
6
38
Referential integrity constraints
• When you delete or update a value of the columns referenced by other tables, the referential integrity constraints may be violated.
• ON DELETE/UPDATE Clause
• The ON DELETE / ON UPDATE clause lets you determine how Oracle Database automatically maintains referential integrity if you remove or update a referenced primary key value. If you omit this clause, then Oracle does not allow you to delete referenced key values in the parent table that have dependent rows in the child table.
Lec
ture
6
39
referential integrity constraints
Four options are supported when the user attempt to delete the CK and there matching FKs:
• ON DELETE/UPDATE CASCADE: delete or update the CK row from the
parent table and all matching FK rows in the child table.
• ON DELETE/UPDATE SET NULL: delete or update the CK row from the
parent table and set the FK values to NULL in the child table. This is valid
only if the foreign key columns do not have the NOT NULL qualifier
specified.
• ON DELETE/UPDATE SET Default: delete or update the CK row from
the parent table and set the FK values to the specified default value in the
child table. Valid only if DEFAULT constraint is specified
• No action: Reject the delete operation from the parent table. This is the
default setting if the ON DELETE rule is omitted.
Lec
ture
6
40
CREATE TABLE Department (
DeptNo CHAR(3) PRIMARY KEY, DeptName VARCHAR2(20) );
CREATE TABLE Employee (
EmpId CHAR(1) PRIMARY KEY, EmpName VARCHAR2(25),
DeptNo CHAR(3) ,
CONSTRAINT dept_fk FOREIGN KEY (DeptNo) REFERENCES
Department(DeptNo) ON DELETE CASCADE );
• delete from Department where DeptNo=103
EmpId EmpName DeptNo
1 Ali 101
2 Sally 101
3 John 103
DeptNo DeptName
101 AAA
102 BBB
103 CCC
PK
FK
Lec
ture
6
41 DeptNo DeptName
101 AAA
102 BBB
EmpId EmpName DeptNo
1 Ali 101
2 Sally 101
Department Employee
CREATE TABLE Department (
DeptNo CHAR(3) PRIMARY KEY, DeptName VARCHAR(20) );
CREATE TABLE Employee (
EmpId CHAR(1) PRIMARY KEY, EmpName VARCHAR(25),
DeptNo CHAR(3) ,
CONSTRAINT dept_fk FOREIGN KEY (DeptNo) REFERENCES
Department(DeptNo) ON DELETE SET NULL );
• delete from Department where DeptNo=103
EmpId EmpName DeptNo
1 Ali 101
2 Sally 101
3 John 103
DeptNo DeptName
101 AAA
102 BBB
103 CCC
PK
FK
Lec
ture
6
42 DeptNo DeptName
101 AAA
102 BBB
EmpId EmpName DeptNo
1 Ali 101
2 Sally 101
3 John
Department Employee
NULL
CREATE TABLE Department (
DeptNo CHAR(3) PRIMARY KEY, DeptName VARCHAR(20) );
CREATE TABLE Employee (
EmpId CHAR(1) PRIMARY KEY, EmpName VARCHAR(25),
DeptNo CHAR(5) Default ‘Empty’,
CONSTRAINT dept_fk FOREIGN KEY (DeptNo) REFERENCES
Department(DeptNo) ON DELETE SET Default);
• delete from Department where DeptNo=103
EmpId EmpName DeptNo
1 Ali 101
2 Sally 101
3 John 103
DeptNo DeptName
101 AAA
102 BBB
103 CCC
PK
FK
Lec
ture
6
43 DeptNo DeptName
101 AAA
102 BBB
EmpId EmpName DeptNo
1 Ali 101
2 Sally 101
3 John Empty
Department Employee
Check Constraints
• The CHECK constraint is used to limit the value range that can be placed in a column.
• If you define a CHECK constraint on a single column it allows only certain values for this column.
• If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.
• Defined at either the table level or the column level
Lec
ture
6
44
Check Constraints
Syntax:
OR
Lec
ture
6
45
create table table-name ( column-name1 datatype CHECK (Check_condition) ) ;
create table table-name ( …….
……. [CONSTRAINT constraint-name] CHECK (Check_condition) );
Example : Check
Create table staff(
Staff_no char(3),
Staff_name varchar2(20) not null,
Staff_gender char(1) CHECK (staff_gender in (‘M’, ‘F’)),
Staff_salary number(8,2) not null,
Dept_no char(4),
Constraint staff_pk Primary key (staff_no),
Constraint staff_fk Foreign key (dept_no) references department (dept_no),
Constraint staff_sal CHECK (staff_salary >10000.00));
Table level
Lec
ture
6
46
Column level
Check Condition Examples 1- The Age entered should be greater than or equal 18?
CHECK (AGE >= 18 )
2- the age grade is greater than 18 OR the nationality is ‘Saudi’ ? CHECK (Age >18 OR nationality=‘Saudi' ) 3- the supplier name either IBM or Microsoft or NVIDIA? CHECK (supplier_name IN ('IBM', 'Microsoft', 'NVIDIA'))
Lec
ture
6
47
DEFAULT constraint
• The DEFAULT constraint is used to insert a default value into a column.
• The default value will be added to all new records, if no other value is specified.
Syntax:
Lec
ture
6
48
create table table-name ( column-name1 datatype DEFAULT (Default_value) );
Example: DEFAULT
• Example : The following SQL creates a DEFAULT constraint on the "City" column when the "Persons" table is created:
CREATE TABLE Persons
( P_Id char(5) NOT NULL, LastName varchar2(255) NOT NULL, FirstName varchar2(255), Address varchar2(255), City varchar2(255) DEFAULT 'Sandnes' );
Lec
ture
6
49
Example : All Constraints
Create table staff(
Staff_no char(3),
Staff_name varchar2(20) NOT NULL,
DateofBirth date,
Staff_nationality char(10) DEFAULT ‘Saudi’,
Staff_salary number(8,2) NOT NULL,
Dept_no char(4),
Constraint staff_pk PRIMARY KEY (staff_no),
Constraint staff_fk FOREIGN KEY (dept_no) references department (dept_no) on delete set null on update cascade,
Constraint staff_sal CHECK (staff_salary >10000.00),
UNIQUE(staff_name, DateofBirth));
Lec
ture
6
50
Summary
51
Constraint Name Type Define it at
NOT NULL Simple( single column) Column-level ONLY
UNIQUE Simple ( single column) Column-level or Table-level
Composite (on multiple (columns Table-level ONLY
PRIMARY KEY
Simple (single column) Column-level or Table-level
Composite (on multiple (columns Table-level ONLY
FOREIGN KEY
Simple ( single column) Column-level or Table-level
Composite (on multiple) columns
Table-level ONLY.
Check -- Column-level or Table-level
Defualt -- Column-level ONLY
Lec
ture
6
52
References
• “Database Systems: A Practical Approach to Design, Implementation and Management.” Thomas Connolly, Carolyn Begg. 5th Edition, Addison-Wesley, 2009.
Lec
ture
6
53