Lecture 4 Lecture 5 SQL Overview Oracle Data

Lecture 4 Lecture 5: SQL Overview , Oracle Data Type , DDL and Constraints Ref. Chapter 6 1 Prepared by L. Nouf Almujally

Real World Domain Conceptual model (ERD) Relational Data Model Create schema (DDL) Load Data (DML) Lecture 4 The Process of Database Design 2

• 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 Lecture 4 SQL overview 3

• 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) Lecture 4 SQL Overview 4

Built-In Oracle Data type Summary VARCHAR 2(size [BYTE | CHAR]) Description Variable length character string having maximum length size bytes. You must specify size Max Size: Oracle 9 i/10 g 4000 bytes minimum is 1 Fixed length character data of length size bytes. This should be used for fixed length data. Such as codes A 100, B 102. . . 2000 bytes Default and minimum size is 1 byte. 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. DATE Valid date range from January 1, 4712 BC to December 31, 9999 AD. TIMESTAMP[time. P recsion] 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) CHAR(size) NUMBER(p, s) Lecture 4 Built-in Data type The scale s can range from -84 to 127. 5

• 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 Lecture 4 NUMBER(p, s) 6

To store strings, you can choose from: • Char • stores fixed-length character strings of up to 2000 characters. Eg. char(10) • should use it to store short strings • Varchar 2 • Stores variable-length strings of up to 4000 characters long. Eg. Varchar 2(50) • Preferred for most string types Ø String values are quoted with single quotes • Eg ‘ 12234’, ‘abcd’, ‘a 12’ Lecture 4 String 7

Date and time SS MI HH HH 24 DD DAY D MM MON Month YY YYYY second 0 -59 Minute 0 -59 Hour 1 -12 Military hour 1 -24 day of month 1 -31 (depends on month) day of the week Sunday-Saturday of the week 1 -7 month number 1 -12 month abbreviated Jan—Dec Month spelled out January-December last 2 digits of year eg, 98 full year value eg, 1998 Lecture 4 • Oracle uses the 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 8

• 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 Lecture 4 Date and time • Default format: ‘dd-mon-yyyy’ • Current date and time: • Sysdate 9

10 Lecture 4

Lecture 4 Data Definition language ( DDL ) Table Creation 11

• Creating a table create table-name ( column-name 1 datatype , ……. . column-name. N datatype ); • 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 Lecture 4 Data Definition (Creating a table) 12

create table-name ( column-name 1 datatype, ……. . column-name. N datatype , [constraint-name] Primary key (columns-name) ); Lecture 4 Identifying Primary Key OR create table-name ( column-name 1 datatype [constraint-name] primary key, ……. . column-name. N datatype); 13

Example OR create table Department ( dept_no char(4), Dept_name varchar 2(25), CONSTRAINT dept_PK PRIMARY KEY(dept_no)); Lecture 4 create table Department ( Dept_no char(4) PRIMARY KEY, Dept_name varchar 2(25)); 14

• An integrity constraint defines a business rule for a table column. • When enabled, the rule will be enforced by oracle • constraints can be specified as row constraints and table constraints. Tables constraints can, for example, identify several columns such as Primary key. • 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. Lecture 4 Integrity Constraints 15

Integrity Constraints 1. 2. 3. 4. CREATE TABLE ALTER TABLE CREATE VIEW ALTER VIEW Lecture 4 • Constraint clauses can appear in the following statements: 16

1. 2. 3. 4. 5. NOT NULL constraint unique constraint primary key constraint foreign key constraint check constraint Lecture 4 The FIFTH types of integrity constraint 17

• 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. Lecture 4 NOT NULL constraint Syntax: create table-name ( column-name 1 datatype NOT NULL ); 18

NOT NULL constraint CREATE TABLE Persons ( P_Id char(5) NOT NULL, Last. Name varchar 2(255) NOT NULL, First. Name varchar 2(255), Address varchar 2(255), City varchar 2(255) ); Lecture 4 • Example : The following SQL enforces the "P_Id" column and the "Last. Name" column to not accept NULL values: 19

• 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. Lecture 4 SQL NULL 20

• 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. • The UNIQUE constraint provides a guarantee for uniqueness for a column or set of columns. • Unique columns are not automatically NOT NULL Syntax: create table-name ( column-name 1 datatype UNIQUE ); OR create table-name ( ……. [constraint-name] UNIQUE (Columns_list) ; Lecture 4 UNIQUE constraint 21

Example CREATE TABLE Persons ( P_Id char(5) NOT NULL UNIQUE, Last. Name varchar 2(255) NOT NULL, First. Name varchar 2(255), Address varchar 2(255), City varchar 2(255), Unique (Last. Name, First. Name) ) Lecture 4 • The following SQL creates a UNIQUE constraint on the "P_Id" column when the "Persons" table is created: 22

• 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. • Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table. Lecture 4 Primary key Constraints 23

Primary key Constraints Primary Key); OR Lecture 4 Syntax: create table-name ( column-name 1 datatype create table-name ( ……. [constraint-name] Primary Key (Columns_list) ; 24

Example : create table Department ( dept_no char(4), Dept_name varchar 2(25), CONSTRAINT dept_PK PRIMARY KEY(dept_no)); Lecture 4 Primary key Constraints 25

Foreign Keys constraint Syntax: create table-name ( column-name 1 datatype, …… …… [constraint-name] Foreign key (Column_name) references referenced_table (column_in_referenced_table) ); Lecture 4 foreign key constraint requires values in one table to match values in another table. 26

Create table Staff( staff_no char(3), staff_name varchar 2(20), dept_no char(4), Constraint staff_fk foreign key (dept_no) references department (dept_no)); Lecture 4 Foreign Keys constraint 27

• 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. Lecture 4 referential integrity constraints 28

referential integrity constraints Four options are supported when the user attempt to delete the CK and there matching FKs: 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. Lecture 4 • ON DELETE/UPDATE CASCADE: delete or update the CK row from the • 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. 29

PK • CREATE TABLE Emp. Master (Emp. Id CHAR(1) PRIMARY KEY, Emp. Name VARCHAR(25)); Emp. Id Emp. Name 1 Ali 2 Slaut 3 John Emp. Id Dept. No FK Dept. Name AAA CCC Lecture 4 1 101 • CREATE TABLE Emp. Details 2 101 (Dept. Id CHAR(3) PRIMARY KEY, 3 103 Dept. Name VARCHAR(20) Emp. Id CHAR(1) FOREIGN KEY REFERENCES Emp. Master(Emp. Id) ON DELETE CASCADE ); • delete from Emp. Master where Emp. Id=1 Emp. Master Emp. Details Emp. Id Emp. Name Emp. Id Dept. No Dept. Name 2 Slaut 2 101 AAA 3 John 3 103 CCC parent child 30

• CREATE TABLE Emp. Master (Emp. Id CHAR(1) PRIMARY KEY, Emp. Name VARCHAR(25)); PK Emp. Id Emp. Name 1 Ali 2 Slaut 3 John Emp. Id Dept. No FK Dept. Name • delete from Emp. Master where Emp. Id=1 Emp. Name 2 Slaut 3 John parent AAA CCC Emp. Details Emp. Master Emp. Id AAA Lecture 4 1 101 • CREATE TABLE Emp. Details 2 101 (Dept. Id CHAR(3) PRIMARY KEY, 3 103 Dept. Name VARCHAR(20) Emp. Id CHAR(1) FOREIGN KEY REFERENCES Emp. Master(Emp. Id) ON DELETE SET NULL ); Emp. Id Dept. No Dept. Name NULL 101 AAA 2 101 AAA 3 103 CCC child 31

Check Constraints • If you define a CHECK constraint on a single column it allows only certain values for this column. Lecture 4 • 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 table it can limit the values in certain columns based on values in other columns in the row. 32

Check Constraints Check (Check_condition) OR create table-name ( ……. [constraint-name] Check (Check_condition) ); Lecture 4 Syntax: create table-name ( column-name 1 datatype ); 33

Create table staff( Staff_no char(3), Staff_name varchar 2(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)); To allow naming of a CHECK constraint Lecture 4 Check Constraints Example 34

• 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: create table-name ( column-name 1 datatype ); Lecture 4 DEFAULT constraint Default (Default_value) 35

DEFAULT constraint CREATE TABLE Persons ( P_Id char(5) NOT NULL, Last. Name varchar 2(255) NOT NULL, First. Name varchar 2(255), Address varchar 2(255), City varchar 2(255) DEFAULT 'Sandnes' ) Lecture 4 • Example : The following SQL creates a DEFAULT constraint on the "City" column when the "Persons" table is created: 36

Create table staff( Staff_no char(3), Staff_name varchar 2(20) not null, Dateof. Birth 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, Constraint staff_sal check (staff_salary >10000. 00), UNIQUE(staff_name, Dateof. Birth)); Lecture 4 Example : All Constraints 37

38 Lecture 4

Alter table_name add (column_specification | constraint, . . . , column_specification| constraint); Alter table_name modify (column_specification | constraint, . . . , Column_specification | constraint); Alter table_name drop column_name | drop (column_list); Alter table_name drop primary key; Alter table_name drop constraint_name; Lecture 4 Modifying Table Definitions 39

Examples alter table orders modify (quantity number(5)); Lecture 4 alter table orders add (quantity number (3) not null); alter table orders drop (quantity); • Be careful if the table contains data. 40

Dropping Tables • Drop table_name [Cascade Constraints]; • If Cascade Constraints is used, the constraints will be dropped first. • example • Drop table Staff; • Drop table Department cascade constraints; Lecture 4 • Pay attention to referential integrity constraints when dropping tables. 41

Viewing/enabling/disabling/dropping Constraints • To disable/enable a constraint, use ALTER TABLE Table_name DISABLE CONSTRAINT constraint_name; ALTER TABLE Table_name ENABLE CONSTRAINT constraint_name; • To drop a constraint, use ALTER TABLE Table_name DROP PRIMARY KEY| UNIQUE (column_name) | CONSTRAINT constraint_name; Lecture 4 • To view the constraints defined for table Department, type SELECT constraint_name, constraint_type, search_condition FROM user_constraints WHERE table_name ='DEPARTMENT'; 42

Example ALTER TABLE Persons DROP CONSTRAINT chk_Person; Lecture 4 • To DROP a CHECK Constraint • To DROP a FOREIGN KEY Constraint ALTER TABLE Orders DROP CONSTRAINT fk_Per. Orders; 43

Viewing Tables and Table Structures • To see what tables are there in SQL*plus, type • To see the structure of a table, type Lecture 4 select * from cat; describe table_name; or desc table _name; 44

45 Lecture 4

References Lecture 4 • “Database Systems: A Practical Approach to Design, Implementation and Management. ” Thomas Connolly, Carolyn Begg. 5 th Edition, Addison-Wesley, 2009. 46