Nothing Special   »   [go: up one dir, main page]
Scribd Logo
Upload

Welcome to Scribd!

  • 50 views

    Introduction To SQL II: Data Definition Language (DDL) Data Manipulation Language (DML)

    Uploaded by

    cahnpek
    The structures are different because dept_copy was created without specifying column data types and constraints, while departments was created with that metadata.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PPT, PDF, TXT or read online from Scribd

    Introduction To SQL II: Data Definition Language (DDL) Data Manipulation Language (DML)

    Uploaded by

    cahnpek
    50 views30 pages
    The structures are different because dept_copy was created without specifying column data types and constraints, while departments was created with that metadata.

    Original Description:

    academic

    Original Title

    Introduction to SQL II

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    PPT, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The structures are different because dept_copy was created without specifying column data types and constraints, while departments was created with that metadata.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PPT, PDF, TXT or read online from Scribd
    Download as ppt, pdf, or txt
    50 views30 pages

    Introduction To SQL II: Data Definition Language (DDL) Data Manipulation Language (DML)

    Uploaded by

    cahnpek
    The structures are different because dept_copy was created without specifying column data types and constraints, while departments was created with that metadata.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PPT, PDF, TXT or read online from Scribd
    Download as ppt, pdf, or txt
    of 30

    LECTURE 1

    Introduction to SQL II
    • Data Definition Language (DDL)
    • Data Manipulation Language (DML)
    DDL WITH TABLES.
    • Creating Tables with Table constraints
    • Modifying Table structures
    Create Table – using table and column
    constraints
     Constraints enforce rules on data whenever a row is
    inserted, updated, or deleted from a table. The constraints
    have to be satisfied for the operation to succeed.
     Data Integrity Constraints:
     Not null: specifies that the column cannot contain a
    null value.
     Unique: specifies that a column or combination of
    columns whose values must be unique for all rows in
    the table.
    Create Table – using table and column
    constraints cont’d
     Check: specifies a condition that must be true
     Primary key: uniquely identifies each row of the table
     Foreign key: establishes and enforces a foreign key
    relationship between the column and a column of the
    referenced table.
     Constraints can be divided at one of the two levels
    i. Column constraint:
     References a single column and is defined within
    the column definition.
     Can define any type of integrity constraint.
    Create Table – using table and column
    constraints Cont’d
     

    o Table constraint:
     References one or more columns and is defined
    after the column list.
     Can define any constraint except not null.

     It is an issue of style to define constraints at either table


    level or column level; however, the not null constraint
    must be strictly defined at the column level.
    Create Table – using table and column
    constraints Cont’d
    Example:
     [using column constraints]
    create table students (regNo varchar(15) primary key, name
    varchar(20), dob date, gender char(1) not null);
     

    OR
    [using table constraints]
     

    create table students (regNo varchar(15), name varchar(20),


    dob date, gender char(1) not null, constraint
    students_regNo_pk primary key(regNo));
     

     How would you confirm constraints defined on a table?


    Modifying a Table Structure
     Modifying a table involves using the ALTER TABLE
    statement which could cater for three kinds of adjustments:
     MODIFY column (modify [data type/size][not null]
    [default])
     ADD column(s) / constraint(s),
     DROP column(s) / constraint(s)
     Examples:
     alter table students add weight float (3,1);
     alter table students add height float (3,1);;
     alter table students modify weight int;
    Modifying a Table Structure cont’d
     alter table students drop column height;
     alter table students modify gender char(1) default ‘F’;
     Alter table students add constraint students_gender_ck
    check (gender in (‘M’,’F’));
     Alter table students add constraint students_name_uk
    unique (name);
     alter table students drop key students_name_uk;
    Modifying a Table Structure Cont’d

    Notes:
     The not null constraint is added by modifying the column
    that is to be defined as not null
    Example:
    ALTER TABLE students
    MODIFY weight int not null;

     Adjustments to populated tables is more restrictive


    because the adjustments should not violet the nature of
    data stored in the tables.
    Removing a Table from a DB schema

     Example:
    DROP TABLE students;
    Quiz
     Write all SQL statements that will create the relational DB schema:

    DEPTS (DeptNo, DName, Loc)


    PERSONNEL (EmpNo, EName, Position, *Mgr, HireDate, Sal,
    Comm, *DeptNo)

    Data Constraints:
     A department name value must never be repeated.
     No person should earn a salary that is more than 5000.
    Note:
     A foreign key is preceded by an asterisk (*);
     Mgr references EmpNo.
    Quiz Cont’d

    1. Assuming that Sex has data type char, modify the personnel
    table such that it can accept values of varied lengths.

    2. Modify the staff table such that each staff member has a
    position.
    Manipulating Data In a Table
    • UPDATE statement
    • DELETE Statement
    DML Overview
     This is a language that provides a set of operations to
    support the basic data manipulation operations on the
    data held in the database.

     There are many types of DML statements, these include;

     INSERT – to add data/new rows to a table

     UPDATE – to make changes to existing data/rows


    in a table
    DML Overview cont’d
     SELECT – to query data in the database (could involve
    one table or many tables to be covered in another learning unit)

     DELETE – to remove data from a table/remove existing


    rows from a table

    Therefore some of the operations performed are insertion,


    modification, retrieval and deletion.
    Example to be used
     Create a table called emp_table with following attributes:
    Statement for creating emp_table
    create table emp_table (empId varchar(20), fName
    varchar(15), lName varchar(15) not null, dob date,
    constraint emp_empid_pk primary key(empId));
    The Update Operation
     The Update operation is used to modify existing rows in a
    database.

     The general syntax of an update statement is;


    UPDATE table
    SET column = value [,column = value,…]
    [WHERE condition];
    The Update Operation
     Specific value(s) are modified if you specify the WHERE
    clause in the UPDATE statement. This is mainly by the
    use of the primary key because any other column name
    may cause several rows to be updated unexpectedly .
    Example:
    UPDATE emp_table
    SET lname = 'nakato'
    WHERE empid = '93/3/3';
    The Update Operation cont’d
     All rows in the table are modified if you omit the where
    clause.
    Example:
    UPDATE emp_table
    SET lname= 'nakato';
     Updating rows with values with that are tied to intergrity
    constraints usually returns an error when the constraint is
    violated.
     Example when updating a foreign key.
    Updating with the use of a Subquery
     A subquery may be used to update a row or rows in a given
    relation
    Example: 
    UPDATE emp_copy
    SET job_id='SAL_PER',
    salary = (select salary
    from employees
    where employee_id = 100)
    WHERE job_id = 'FI_ACCOUNT';
    The Update Operation cont’d
     Updating multiple columns using a subquery;
     The general syntax is
    UPDATE table
    SET column = ( SELECT column
    FROM table
    WHERE condition)
    [,
    column = ( SELECT column
    FROM table
    WHERE condition)]
    [WHERE condition];
    The Delete Operation
     To be able to remove rows from a table, the DELECT
    statement is used.

     The general syntax is;


    DELETE [FROM] table
    [WHERE condition];
    The Delete Operation
     Specific row(s) are deleted if you specify the WHERE
    clause in the DELETE FROM statement.
    Example:
    DELETE FROM emp_copy
    WHERE job_id= ‘SAL_PER’;

     Take care, if a WHERE clause is not specified, then all


    rows from the table will be deleted.
    Example:
    DELETE FROM emp_copy;
    Deleting Rows based on Another Table
     This is usually done using a Subquery
    Example:
    DELETE FROM emp_copy
    WHERE department_id = (SELECT department_id
    FROM departments
    WHERE location_id=2400);
    Deleting Rows: Integrity constraint Error
     You can not delete a row that contains a primary key that is
    used as a foreign key in another table.

     Example:
    DELETE FROM departments
    WHERE department_id = 60;

    Error: child record found violation


    Common Integrity Constraint Errors
     Leaving out values for columns with NOT NULL
    constraints.
     Deleting a parent record that has dependencies on it.
    Example:
    DELETE FROM dept_copy
    WHERE department_id = 20;
     Inserting or updating a record with a foreign key value
    that does not exist in the referenced primary or unique
    key of the parent table. Example:
    UPDATE emp_copy
    SET department_id = 55
    WHERE department_id = 110;
    Exercise
    The following tables must be created for purposes of
    completing the remaining DML operations:
     Create a table called emp_copy with a data storage
    that is the same as that of the employees table.

     Create a table called dept_copy with a data storage


    that is the same as that of the departments table.

     Compare the structures for dept_copy and


    departments. Why the difference?
    Exercise Cont’d
    Primary keys and Foreign key constraints to be added to the
    tables created:
     Add a primary key constraint on the emp_copy table.

     Add a primary key constraint on the dept_copy table

     Add a foreign key constraint on the dept_copy table


    which references the primary key of the emp_copy
    table.
    Exercise Cont’d – statements for adding
    the constraints
     alter table emp_copy add constraint
    emp_copy_employee_id_pk primary key(employee_id);

     alter table dept_copy add constraint


    dept_copy_department_id_pk primary
    key(department_id);

     alter table emp_copy add constraint


    emp_copy_department_id_uk foreign key(department_id)
    references dept_copy(department_id);

    You might also like