Lecture5 sql overview oracle data type ddl and constraints
Download
1 / 46

Lecture5: SQL Overview , Oracle Data Type , DDL and Constraints - PowerPoint PPT Presentation


  • 61 Views
  • Uploaded on

Lecture5: SQL Overview , Oracle Data Type , DDL and Constraints. Ref. Chapter6. Prepared by L. Nouf Almujally. The Process of Database Design. SQL overview. Official pronunciation is ‘S-Q-L‘ or ( see- qual ) SQL: Structured Query Language

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Lecture5: SQL Overview , Oracle Data Type , DDL and Constraints' - venus-pugh


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Lecture5 sql overview oracle data type ddl and constraints

Lecture5: SQL Overview , Oracle Data Type , DDL and Constraints

Lecture4

Ref. Chapter6

Prepared by L. NoufAlmujally



Sql overview
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

Lecture4


Sql overview1
SQL Overview

  • SQL provides statements for a variety of tasks, including:

  • Querying data( Select command)

  • Inserting, updating, and deleting rows in a table (DML)

  • Creating, replacing, altering, and dropping objects(DDL)

  • Controlling access to the database and its objects(DCL)

  • Guaranteeing database consistency and integrity(DCL)

Lecture4



Number p s
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

Lecture4


String
String

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

  • Varchar2

    • Stores variable-length strings of up to 4000 characters long. Eg. Varchar2(50)

    • Preferred for most string types

  • String values are quoted with single quotes

    • Eg ‘12234’, ‘abcd’, ‘a12’

Lecture4


Date and time
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

      MIMinute 0-59

      HHHour 1-12

      HH24Military 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

Lecture4


Date and time1
Date and time

  • Example of date format:

    • ‘dd-mon-yyyy’ 01-dec-2001

    • ‘dd/mm/yyyy’ 01/12/2001

    • ‘mm-dd-yyhh:mi:ss’ 12-01-01 12:30:59

  • Default format: ‘dd-mon-yyyy’

  • Current date and time:

    • Sysdate

Lecture4



Data definition language ddl

Data Definition language ( DDL )

Lecture4

Table Creation


Data definition creating a table
Data Definition (Creating a table)

  • Creating a table

    create tabletable-name (

    column-name1datatype,

    ……..

    column-nameNdatatype);

    • 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

Lecture4


Identifying primary key
Identifying Primary Key

create table table-name (

column-name1 datatype,

……..

column-nameNdatatype,

[constraint constraint-name] Primary key (columns-name) );

OR

create table table-name (

column-name1 datatype[constraint constraint-name] primary key,

……..

column-nameNdatatype);

Lecture4


Example
Example

create table Department (

Dept_nochar(4) PRIMARY KEY,

Dept_name varchar2(25));

OR

create table Department (

dept_no char(4),

Dept_name varchar2(25),

CONSTRAINT dept_PKPRIMARY KEY(dept_no));

Lecture4


Integrity constraints
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 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.

Lecture4


Integrity constraints1
Integrity Constraints

  • Constraint clauses can appear in the following statements:

  • CREATE TABLE

  • ALTER TABLE

  • CREATE VIEW

  • ALTER VIEW

Lecture4


The fifth types of integrity constraint
The FIFTH types of integrity constraint

  • NOT NULL constraint

  • unique constraint

  • primary key constraint

  • foreign key constraint

  • check constraint

Lecture4


Not null constraint
NOT NULL constraint

  • The NOT NULL constraintenforces 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.

    Syntax:

    create table table-name (

    column-name1 datatypeNOT NULL );

Lecture4


Not null constraint1
NOT NULL constraint

  • Example : The following SQL enforces the "P_Id" column and the "LastName" column to not accept NULL values:

    CREATE TABLE Persons( P_Idchar(5) NOT NULL,LastNamevarchar2(255) NOT NULL,FirstNamevarchar2(255), Address varchar2(255), City varchar2(255) );

Lecture4


Sql null
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.

Lecture4


Unique constraint
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.

  • 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 table-name (

    column-name1 datatypeUNIQUE);

    OR

    create table table-name(

    …….

    …….

    [constraint constraint-name] UNIQUE(Columns_list) ;

Lecture4


Example1
Example

  • 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),City varchar2(255),

    Unique (LastName,FirstName))

Lecture4


Primary key constraints
Primary key Constraints

  • 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.

Lecture4


Primary key constraints1
Primary key Constraints

Syntax:

create table table-name (

column-name1 datatypePrimary Key);

OR

create table table-name (

…….

…….

[constraint constraint-name] Primary Key (Columns_list) ;

Lecture4


Primary key constraints2
Primary key Constraints

Example :

create table Department (

dept_no char(4),

Dept_namevarchar2(25),

CONSTRAINT dept_PKPRIMARY KEY(dept_no));

Lecture4


Foreign keys constraint
Foreign Keys constraint

foreign key constraint requires values in one table to match values in another table.

Syntax:

create table table-name (

column-name1 datatype,

……

……

[constraint constraint-name] Foreign key (Column_name) referencesreferenced_table(column_in_referenced_table) );

Lecture4


Foreign keys constraint1
Foreign Keys constraint

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

Lecture4


Referential integrity constraints
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.

Lecture4


Referential integrity constraints1
referential integrity constraints

Four options are supported when the user attempt to delete the CK and there matching FKs:

  • ON DELETE/UPDATECASCADE: 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.

Lecture4


PK

FK

  • CREATE TABLE EmpMaster(EmpId CHAR(1) PRIMARY KEY,EmpName VARCHAR(25));

  • CREATE TABLE EmpDetails(DeptId CHAR(3)   PRIMARY KEY,DeptName VARCHAR(20)

    EmpId CHAR(1) FOREIGN KEY REFERENCES EmpMaster(EmpId) ON DELETE CASCADE);

  • delete from EmpMaster where EmpId=1

Lecture4

EmpMaster

EmpDetails

child

parent


PK

FK

  • CREATE TABLE EmpMaster(EmpId CHAR(1) PRIMARY KEY,EmpName VARCHAR(25));

  • CREATE TABLE EmpDetails(DeptId CHAR(3)   PRIMARY KEY,DeptName VARCHAR(20)

    EmpId CHAR(1) FOREIGN KEY REFERENCES EmpMaster(EmpId)

    ON DELETE SET NULL );

  • delete from EmpMaster where EmpId=1

Lecture4

EmpDetails

EmpMaster

parent

child


Check constraints
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.

Lecture4


Check constraints1
Check Constraints

Syntax:

create table table-name (

column-name1 datatypeCheck (Check_condition)

);

OR

create table table-name (

…….

…….

[constraint constraint-name] Check (Check_condition)

);

Lecture4


Check constraints example
Check Constraints Example

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

Lecture4

To allow naming of a CHECK constraint


Default constraint
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:

    create table table-name (

    column-name1 datatypeDefault (Default_value)

    );

Lecture4


Default constraint1
DEFAULT constraint

  • 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')

Lecture4


Example all constraints
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_pkPrimary key (staff_no),

Constraint staff_fkForeign key (dept_no) references department (dept_no) on delete set null,

Constraint staff_salcheck (staff_salary >10000.00),

UNIQUE(staff_name, DateofBirth));

Lecture4



Modifying table definitions
Modifying Table Definitions

Alter table table_nameadd (column_specification | constraint,..., column_specification| constraint);

Alter table table_namemodify (column_specification | constraint,..., Column_specification | constraint);

Alter table table_name

drop column column_name | drop (column_list);

Alter table table_namedrop primary key;

Alter table table_namedrop constraint constraint_name;

Lecture4


Examples
Examples

alter table orders

add(quantity number (3) not null);

alter table orders

modify(quantity number(5));

alter table orders

drop(quantity);

  • Be careful if the table contains data.

Lecture4


Dropping tables
Dropping Tables

  • Drop table table_name [Cascade Constraints];

  • Pay attention to referential integrity constraints when dropping tables.

  • If Cascade Constraints is used, the constraints will be dropped first.

  • example

    • Drop table Staff;

    • Drop table Department;

    • Drop table Department cascade constraints;

Lecture4


Viewing enabling disabling dropping constraints
Viewing/enabling/disabling/dropping Constraints

  • To view the constraints defined for table Department, type

    SELECT constraint_name, constraint_type, search_condition

    FROM user_constraints

    WHERE table_name ='DEPARTMENT';

  • To disable/enable a constraint, use

    ALTER TABLE Table_nameDISABLECONSTRAINT constraint_name;

    ALTER TABLE Table_nameENABLE CONSTRAINT constraint_name;

  • To drop a constraint, use

    ALTER TABLE Table_name

    DROP PRIMARY KEY| UNIQUE (column_name) | CONSTRAINT constraint_name;

Lecture4


Example2
Example

  • To DROP a CHECK Constraint

    ALTER TABLE PersonsDROP CONSTRAINT chk_Person;

  • To DROP a FOREIGN KEY Constraint

    ALTER TABLE OrdersDROP CONSTRAINT fk_PerOrders;

Lecture4


Viewing tables and table structures
Viewing Tables and Table Structures

  • To see what tables are there in SQL*plus, type

    select * from cat;

  • To see the structure of a table, type

    describe table_name;

    or

    desctable _name;

Lecture4



References
References

  • “Database Systems: A Practical Approach to Design, Implementation and Management.” Thomas Connolly, Carolyn Begg. 5thEdition, Addison-Wesley, 2009.

Lecture4


ad