sql overview and ddl ch 10 1 10 6
Download
Skip this Video
Download Presentation
SQL: Overview and DDL Ch. 10.1 - 10.6

Loading in 2 Seconds...

play fullscreen
1 / 36

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


  • 80 Views
  • 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

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

outline
Outline
  • Oracle SQL interface: sqlplus
  • SQL DDL
  • 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/[email protected]

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,

Table-Constraint,

Table-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

Departments(Name)

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)

or

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

ad