Transcript SQL-PART1

1
Structured Query Language (SQL)
DDL
Chapter Objective
To provide an overview of the Structured Query
Language (SQL) approach to relational database
management
Intro to SQL


SQL has become the de facto standard for creating and
managing relational databases
It is easy to learn and use because:
 it
is a non-procedural language
 it has a small set of basic commands


SQL commands may be used by themselves or may be
embedded within a procedural host language such as COBOL
or C.
SQL*PLUS is based on standard SQL, but has non standard
features specific to ORACLE
2
Intro to SQL

SQL commands fall into two categories:
 Data


defining the database structure - create, alter, and drop
setting up access authorization to database objects - grant and revoke
privileges, and other operations
 Data



Definition Language (DDL) used for
Manipulation Language (DML) used for
managing data in the database - insert, update, and delete
querying the database - select
Comments
 A block
of comment spanning more than one line is enclosed
between /* and */
 An in-line comment starts with -- and ends with a line break
3
Data Definition Commands

Create database structure
Holds all tables and is a collection of physical files stored on
disk
 DBMS automatically creates tables to store metadata
 Database administrator creates structure or schema




Logical group of tables or logical database
Groups tables by owner
Enforces security
CREATE SCHEMA AUTHORIZATION;
<creator>
Example:
CREATE SCHEMA AUTHORIZATION JONES;
4
Table Creation - Create Table

CREATE TABLE command - simple form
CREATE TABLE <table name>
(<column1 name> <data type>,
<column2 name> <data type>,
….
<columnn name> <data type>);
Example:
/* Code for creating a table
to store sales person data */
CREATE TABLE Salesperson
(empid NUMBER(5), -- employee id
ename CHAR(15),
-- employee name
rank NUMBER(1),
salary NUMBER(7,2));
5
Table Creation - Data Types

Some Data Types in Sql*Plus:
 CHAR(size)

Fixed length character string
 VARCHAR2(size)

Variable length character string of max length size
 DATE



Stores date and time
Default date format is ‘DD-MON-YY’
SYSDATE stores current date
6
Table Creation - Data Types

Data Types
 NUMBER(p,s)





Numeric data with a total of p (precision) number of digits, of which s
(scale) number of digits are to the right of the decimal position
NUMBER(p) can store only integer data
NUMBER stores floating point data with decimal precision 38
negative s causes rounding the number to s places before the decimal point
smallint and integer are stored as NUMBER(38)
Actual Data
123456.78
123456.78
Specified as
NUMBER
NUMBER(8)
Stored as
123456.78
123457
123456.78
123456.78
NUMBER(6,2) Error
NUMBER(6,-2) 123500
Comment
Float
Rounded because
scale is 0
Exceeds precision
Rounded to 100s
because scale is -2
7
Table Creation - Column Constraints

DESCRIBE <table name>;
 displays
the structure of a table
 Example: DESCRIBE Salesperson;

Column Constraints allow us to specify constraints on the data
stored in a column.
 NOT


NULL
forces the user to enter a value into a column
use when the column cannot be left blank
 UNIQUE



no duplicate values are allowed in the column
NULL values, however, may appear in several rows if NOT NULL is not
used in conjunction with UNIQUE
UNIQUE and PRIMARY KEY are not used together
8
Table Creation - Column Constraints

9
Column Constraints
 PRIMARY KEY



enforces entity integrity - a unique identifier for each row
by definition it is UNIQUE and NOT NULL
only one column in a table can have this constraint
 REFERENCES




<referenced table name> [(<reference column>)]
enforces referential integrity - NULL or a matching value
referenced column must have a UNIQUE specification
when referenced column name is not specified the primary key is used as
the referenced column
can also specify on delete/ on update clause as described under table
constraint
Table Creation - Column Constraints

Column Constraints
 CHECK



10
(<search condition>)
validates the data against the search condition, which is a logical expression
rejects data for the row when the search condition evaluates to FALSE for
that row
Default Value
 DEFAULT



<default option>
default option can be a literal (constant value), a system value, or NULL
default value is stored when no value is supplied
Oracle requires that the Default clause must appear before the NOT NULL
clause
Table Creation - Column Constraints

Example - create table with column constraints
CREATE TABLE Salesperson
(empid NUMBER(5) PRIMARY KEY,
ename CHAR(15) NOT NULL,
rank NUMBER(1) DEFAULT 1 NOT NULL
CHECK (rank IN (1,2,3)),
salary NUMBER(7,2) NOT NULL
CHECK (salary >= 1000.00));
CREATE TABLE Customer
(custid NUMBER(5) PRIMARY KEY,
cname CHAR(15) NOT NULL,
caddress VARCHAR2(25) NOT NULL,
credit CHAR(1) NOT NULL
CHECK (credit IN (‘A’, ‘B’, ‘C’)));
11
Table Creation - Column Constraints

Example - create table with column constraints
CREATE TABLE Orders
(orderid NUMBER(5) PRIMARY KEY,
empid NUMBER(5) NOT NULL
REFERENCES Salesperson(empid),
custid NUMBER(5) NOT NULL
REFERENCES Customer(custid),
salesdate DATE DEFAULT SYSDATE);
12
Table Creation - Table Constraints

Table Constraints
 Constraints
that apply to the whole table or involves more than
one column in the table are defined as table constraints.
 A column constraint (with the exception of NOT NULL) can
also be defined as a table constraint
 We will discuss primary key and foreign key constraints, and
will skip check and unique constraints.
 PRIMARY KEY (<column1


name>, <column2 name>, ….)
This specifies the primary key for the table
All attributes that make up the primary key of a table are listed in this
statement.
13
Table Creation - Table Constraints
14
 FOREIGN
KEY (<col1 name>)
REFERENCES <table name> [(<col2 name>)]
[ON DELETE {RESTRICT, CASCADE, SET NULL, SET DEFAULT}]
[ON UPDATE {RESTRICT, CASCADE, SET NULL, SET DEFAULT}]



The foreign key and the referenced key can be in the same table.
When col2 name is not specified the primary key of the table is used as the
referenced column.
You can specify multiple columns as parameters in the Foreign Key clause.
Table Creation - Table Constraints

On Delete/ Update
 RESTRICT:
Cannot delete from/update key value in the
referenced table if such a reference exists. This is the default.
 CASCADE: Deletion from/ update in the referenced table
causes deletion/update of the corresponding rows in the
referencing table.
 SET NULL: Deletion from / update in the referenced table sets
foreign key value in the corresponding row to NULL in the
referencing table.
 SET DEFAULT: Deletion from/ update in the referenced table
sets foreign key value to default.
 Oracle supports ON DELETE CASCADE only.
15
Table Creation - Table Constraints

Example of Table Constraints
CREATE TABLE OrderItem
(orderid NUMBER(5),
itemid NUMBER(5),
qty NUMBER(5) NOT NULL
CHECK (qty > 0),
PRIMARY KEY (orderid, itemid),
FOREIGN KEY (orderid) REFERENCES Orders(orderid)
ON DELETE CASCADE,
FOREIGN KEY (itemid) REFERENCES Inventory);
16
ALTER TABLE

17
ALTER TABLE command allows you to modify the
characteristics of a table, such as
 add
a new column
 modify the characteristics of a column
 add/drop a table constraint (constraint must be named)
 Example:

ALTER TABLE Salesperson ADD phone NUMBER(10) NOT NULL;


Use of NOT NULL with a new column is allowed only if the table is empty.
ALTER TABLE Salesperson MODIFY (ename CHAR(20));

increase size of ename from 15 to 20 characters
DROP TABLE

18
DROP TABLE <table name> [CASCADE CONSTRAINTS]
 removes
a table from the database.
 Optional clause Cascade Constraints drops all referential
integrity constraints that refer to the primary and unique keys in
the dropped table. If this is omitted the table will not be dropped
if such constraints have been defined in other tables.
 Example: DROP TABLE Salesperson;
GRANTING PRIVILEGES

19
Use the GRANT command for granting privileges to others
 GRANT
<privileges> ON <table> TO <users> [WITH GRANT
OPTION];
 privileges can be ALL or a list that includes any of SELECT,
INSERT, UPDATE, DELTE, ALTER
 users can be a list of user identifiers or PUBLIC
 WITH GRANT OPTION allows the grantee to grant privilege to
other users
 Example:


GRANT ALL ON Salesperson to pxk3124 WITH GRANT OPTION;
GRANT SELECT ON Salesperson to PUBLIC;
INSERTING DATA INTO A TABLE

INSERT INTO <table name> [(<insert column list>)]
VALUES (<value list>);
 Inserts
a single row of data into the table
 if column list is not provided values must be provided for all
columns in the sequence as they appear in the data dictionary
 if a column list is provided and some columns are omitted then
those columns get NULL or default value, as per the table
definition.
 Character data are enclosed within quotation marks and date is
specified as ‘dd-mon-yy’, numbers do not need quotes.
20
INSERTING DATA INTO A TABLE

INSERT INTO Salesperson VALUES (12134, ‘John Doe’, 1,
50000);
 will

insert a row into Salesperson table
INSERT INTO Salesperson (empid, ename, salary) VALUES
(12135, ‘Jane Doe’, 60000);
 will
insert another row into Salesperson with rank assigned a
default value of 1

You can also insert multiple rows into a table using query
statements. We will discuss this later.
21
DELETE

22
DELETE command is used to delete selected rows from a table.
DELETE
FROM <table>
[WHERE <condition>];
• Deletes all rows from the table that satisfy the condition specified in the
WHERE clause.
• If the optional WHERE clause is not specified then all rows are deleted from
the table.
/* Delete the employee whose ID is 123123123 */
DELETE
FROM Employee
WHERE eid = 123123123;
/* The following command will delete all rows from Employee table */
DELETE
FROM Employee;
UPDATE

UPDATE command is used to change (update) individual
columns of selected rows in a table
UPDATE <table>
SET <col = expression> [,<col = expression>] ….
[WHERE <condition>];
/* Transfer employee with ID 121212333 to Finance department */
UPDATE Employee
SET dept = ‘Finance’
WHERE eid = 121212333;
/* Give a 10% raise to all employees */
UPDATE Employee
SET salary = salary * 1.1;
23
COMMIT AND ROLLBACK




COMMIT makes a change permanent
ROLLBACK cancels all insertion, deletion, and update
operations since the most recent COMMIT
COMMIT and ROLLBACK are transaction management
commands
Table creation, drop and alter commands are not affected by
COMMIT or ROLLBACK
UPDATE Employee
SET salary = salary +10000
WHERE eid = 121212333;
/* Will give a raise of $10,000 to an employee with ID 121212333 */
ROLLBACK;
/* Will undo this operation */
24
SELF TEST

Create two tables:
 Student
(ID, Name, Major, GPA)
 Majors (Major, Description, Department Name)
Major is a foreign key in student
 0 <= GPA <= 4.00
 Grant privileges to one of your group members on this table
Remember




Majors table must be created before Student
Your friend must qualify the table name with your user id to access your table
25