Sql overview and ddl ch 10 1 10 6
1 / 36

SQL: Overview and DDL Ch. 10.1 - 10.6 - PowerPoint PPT Presentation

  • Uploaded on

SQL: Overview and DDL Ch. 10.1 - 10.6. John Ortiz. Overview. SQL: Structured Query Language, pronounced S. Q. L. or sequel. A standard language for relational, object-relational databases: SQL-86 by ANSI, SQL1 by ISO (87), SQL-89, SQL2 (92), SQL-1999. Web site: www.ansi.org

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

PowerPoint Slideshow about ' SQL: Overview and DDL Ch. 10.1 - 10.6' - celeste-marty

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
Sql overview and ddl ch 10 1 10 6

SQL: Overview and DDLCh. 10.1 - 10.6

John Ortiz


  • SQL: Structured Query Language, pronounced S. Q. L. or sequel.

  • A standard language for relational, object-relational databases: SQL-86 by ANSI, SQL1 by ISO (87), SQL-89, SQL2 (92), SQL-1999. Web site: www.ansi.org

  • Implemented by all commercial R/OR DBMSs: Oracle, Sybase, Ingres, Informix, DB2, SQL Server, … (with various degree of conformity)

  • We focus on SQL2 & SQL-1999 features and also Oracle SQL.

SQL: Overview

Components of sql
Components of SQL

  • Have statements for

    • Schema definition & update

      • tables, indexes, views, …

    • Data Manipulation

      • query, insertion, deletion, update, …

    • Data integrity

      • constraints, …

    • System administration

      • users, data security, performance tuning, …

    • Application development

      • procedures, transaction, embedded SQL, …

SQL: Overview

Sql a simple example
SQL: A Simple Example

  • Create a Students table

    create table Students (

    SSN char(9) not null,

    Name varchar2(20),

    GPA number(3,2),

    Age number(2),

    primary key (SSN));

  • Insert a Student tuple

    insert into Students

    values (312334455, ‘John Smith’, 3.25, 29);

SQL: Overview

Sql a simple example cont
SQL: A Simple Example (cont.)

  • Retrieve names of students whose GPA is higher than 3.0 and Age is less than 30.

    select Name

    from Students

    where GPA > 3.0 and Age < 30;

  • What data types are supported?

  • What constraints can be expressed? How?

  • What if I want to change the schema?

  • How to change data?

  • How to express more complex queries?

SQL: Overview


  • Oracle SQL interface: sqlplus


  • Interactive SQL queries, update, view

  • PL/SQL for procedures, constraints, triggers

  • Embedded SQL (Oracle Pro*C/C++, JDBC)

  • Oracle SQL Standard SQL

  • Oracle manual: Oracle SQL Reference Manual (on-line).

SQL: Overview

Oracle dbms
Oracle DBMS

  • A major commercial DBMS from Oracle (Object-Relational), supporting SQL.

  • Components: Oracle server, SQL*Plus, Precompilers (Pro*C/C++, …), JDBC driver, SQLJ, WebDB, Forms, Report, …

  • Platforms: Client-server, PC, Unix or Windows, Distributed, Parallel, …

  • Multiuser, Security, Transactions, Recovery,

  • We use it for course project and homework assignments.

SQL: Overview

Oracle db user account
Oracle DB User Account

  • Needed for every DB user. Has separate user id & password.

  • Access through CSLan UNIX accounts.

  • Environment variables for Oracle access:

    • Oracle_BASE, Oracle_HOME, PATH, LD_PATH, CLASS_PATH, …

  • On-line Instructions for using Oracle.

    • “Using Oracle in CSLab” on course web page.

    • On-line Oracle manuals (URL is given in the previous article).

SQL: Overview

Oracle sql plus
Oracle SQL*Plus

  • Interactive user environment.

    • Runs SQL statements, PL/SQL statements, and other SQL*Plus commands

    • Allow simple command editing

  • Documentation:

    • On-line “help” command within sqlplus

    • On-line manual.

  • User login: Unix$sqlplus userid/pwd@cs

    SQL> exit

SQL: Overview

Use sql plus to run sql
Use SQL*Plus to Run SQL

  • SQL*plus understands only Oracle SQL.

  • An SQL statement must end with semicolon ; a slash / on a line by itself, or a blank line.

  • An SQL statement can be typed in, or loaded from a .sql file (get filename).

  • An SQL can be executed from the command buffer (run), or from a .sql file (@filename)

  • SQL statement in buffer can be written to a .sql file (save filename)

SQL: Overview

Common sql plus commands
Common SQL*Plus Commands

  • Display schema: describe table_name

  • Run Unix commands: !command

  • Run editor on .sql file: edit filename

    • Set host editor: define_editor=vi

  • Save a journal file: spool filename

    spool off

  • Customize environment: set option

    • Options: echo on, pause on, pagesize 30, …

    • View current settings: showall

    • Configuration file: login.sql

SQL: Overview

Sql plus command editing
SQL*Plus Command Editing

  • Show command in buffer: list

  • A set of simple line editing commands.

  • Better use external editor, say, vi or emacs.

SQL: Overview

Sql data definition language
SQL Data Definition Language

  • Used by DBA or Designer to specify schema

  • A set of statements used to define and to change the definition of tables, columns, data types, constraints, views, indexes, …

  • SQL DDL & DML are integrated.

    • A DDL statement often needs to contain some DML statements.

SQL: Overview

A sample university schema
A Sample University Schema

  • Students(SID:string, Name:string, Age:integer, Sex:char, Major:string, GPA:real)

  • Courses(Cno:string, Title:string, Hours:integer, Dept:string)

  • Enrollment(SID:string, Cno:string, Year:string, Grade:string)

  • Offers(Cno:string, Year:integer, FID:string)

  • Faculty(FID:string, Name:string, Rank:string, Dept:string, Salary:real)

  • Departments(Name:string, Location:string, ChairID:string)

SQL: Overview

Create students table
Create Students Table

  • In SQL*Plus:

    SQL> create table Students

    2 (SID char(9) not null,

    3 Name varchar2(25),

    4 Age integer,

    5 Sex char(1),

    6 Major char(4),

    7 GPA number(3,2),

    8 primary key (SID));

SQL: Overview

Create tables syntax
Create Tables Syntax

create table Table-Name (

Col-Name Type Deft-Val Col-Constraint,

Col-Name Type Deft-Val Col-Constraint,



SQL: Overview

Oracle sql built in data types
Oracle SQL Built-in Data Types

  • char(n). String of n < 2000 char

  • varchar2(n). String up to n <= 4000 char

  • long. Char string of length up to 2GB

  • number(n,m). n digits, m after decimal point.

  • number. Integer or real up to 40 digits

  • integer. Integer up to 40 digits

  • blob. Binary data up to 4 GB

  • date. DD-MMM-YY

  • time. HH:MM:SS

  • These may differ from SQL2 & SQL-1999.

SQL: Overview

Sql integrity constraints
SQL Integrity Constraints

  • Rules or regulations imposed to ensure data integrity.

    • Column Constraints.

    • Table Constraints.

    • Assertions (Multiple-table Constraints).

    • Triggers.

    • Primary Key, Foreign Key, Check, Not Null, Unique, …

SQL: Overview

Column definition
Column Definition

  • Syntax for column definition:

    col_name data_type [default value] [column constraints]

  • Syntax for column constraints:

    [constraint constraint_name]

    [not] null | check condition |

    unique | primary key |

    references table_name [(column)]

    [on delete cascade]

SQL: Overview

Column constraints
Column Constraints

  • not null. Can not take null value.

  • unique. Can not have identical non-null values

  • primary key. Both not null and unique

  • references T(A). All non-null values must be currently in T.A.

  • check (condition). Values must satisfy the check condition.

  • Can be expressed as table constraints, too.

SQL: Overview

Column constraints example
Column Constraints Example

SQL>create table Courses

(CNo char(6) primary key,

Title varchar2(50) not null,

Hours integer default 3

check (Hours > 0 and hours < 6),

Dept varchar2(20)

references Departments(Name));

SQL: Overview

Table constraints
Table Constraints

  • Syntax for table constraints:

    [constraint constraint_name]

    check condition |

    unique (column {, column}) |

    primary key (column {, column}) |

    foreign key (column {, column})

    references table_name[(column {, column})]

    [on delete cascade]

SQL: Overview

Table constraints example
Table Constraints Example

SQL> create table Enrollment

(SID char(9) not null references Students,

CNo varchar2(7) not null,

Year number(2) not null,

Grade char(2),

primary key (SID, CNo, Year),

foreign key (CNo) references Courses);

SQL: Overview

Table constraints example cont
Table Constraints Example (cont.)

SQL> create table Students

(SID char(9) primary key,

Name varchar2(25),

Age integer check(Age > 18 and Age < 100),

Sex char check(Sex in {‘F’, ‘M’}),

Major varchar2(4)

GPA number (3,2) not null,

constraint ic12 check (GPA >= 2.0 and

(Major = ‘IS’ or GPA >= 3.0)));

SQL: Overview

Referential integrity data update
Referential Integrity & Data Update

  • Assume that Courses.Dept references Departments.Name. What should the system do to students if we change a department’s name or delete a department?

  • SQL provides four options:

    • No action. Disallow such an update.

    • Cascade. Accept update and update all affected foreign key values.

    • Set default. Accept update & set default FK.

    • Set null. Accept update & set FK to null.

SQL: Overview

Referential integrity example
Referential Integrity Example

SQL>create table Courses (

CNo char(6) not null primary key,

Title varchar(35) not null,

Hours int check (Hours between 1 and 5),

Dept varchar(20),

foreign key (Dept) references


on delete no action on update cascade);

SQL: Overview

Drop table
Drop Table

  • Delete schema definition of a table.

    drop table Table-Name;

  • Problem:

    drop table Departments

    will fail if it is referenced by foreign keys.

  • Solution:

    drop table Departments cascade constraints;

    All referential constraints will be dropped before the table is dropped.

SQL: Overview

Alter table
Alter Table

  • Change table schema (even after entering data)

  • Add a new column.

    alter table Students

    add (Address varchar2(40));

  • Add a new constraint.

    alter table Students add (unique(Address));

  • Modify a column definition.

    alter table Students

    modify (Name varchar2(30));

SQL: Overview

Alter table cont
Alter Table (cont.)

  • Remove a column.

    alter table Students drop (Address);

  • Enable and disable a constraint on a table

    alter table Students enable constraint ic12;

    alter table Students disable constraint ic12;

  • Newly added column can not be specified as not null.

  • Can not modify a column to a type of a smaller size.

SQL: Overview

Simple update statements
Simple Update Statements

  • Insert Statement:

    insert into table_name [(column {, column})]

    [values (expression {, expression})]

  • Update Statement:

    update table_name [corr_name]

    set column = {expression | null}

    {, column = {expression | null}}

    [where search_condition]

  • Delete Statement:

    delete from table_name

SQL: Overview

Example of update
Example of Update

insert into Students

values (`123456789', `Kathy', 26, ‘F’, ‘CS’, null)


insert into Students (Name, SID, Age, Major, Sex)

values (`Kathy', `123456789', 26, ‘CS’, ‘F’)

SQL: Overview

Example of update cont
Example of Update (cont.)

  • Increase the GPA of the student with SID = 123456789 by 0.5.

    update Students

    set GPA = GPA + 0.5

    where SID = '123456789‘

  • Delete all tuples from Students.

    delete from Students

  • The schema of Students remains.

SQL: Overview

Data dictionary
Data Dictionary

  • Data dictionary (system catalog) contains information about all database objects (tables, views, indexes, sequences, etc).

  • Common Oracle Data Dictionary Tables

    • user_objects(object_name, object_id, object_type, created, last_ddl_time, timestamp, status)

    • Example database objects include tables, views, sequences, indexes, and packages.

SQL: Overview

Data dictionary cont
Data Dictionary (cont.)

  • user_tables(table_name, tablespace_name, num_rows, blocks, empty_blocks, avg_row_len)

  • user_tab_columns(name, table_name, column_name, data_type, data_length, nullable, column_id, default_length, data_default, num_distinct, low_value, high_value)

  • Use select * from dictionary to see all system tables and views.

  • Use describe table-name to view a schema.

  • SQL: Overview

    Ddl summary
    DDL Summary

    • Specify appropriate data type for each column. You may also define your own domains.

    • Specify as many constraints as needed for applications.

    • Specify desirable actions for foreign key constraints.

    • Not all constraints can be specified at the same time. It is necessary to update schemas.

    • Major schema change after data is entered is very costly.

    SQL: Overview

    Look ahead
    Look Ahead

    • Next topic: Interactive SQL

    • Read textbook:

      • Oracle 8 Programming: A primer by R. Sunderraman. Chapter 2.

    SQL: Overview