Transcript SQL Components DML DDL DAL

SQL Components
DML
DDL
DAL
Overview
 Getting
the records onto the disk - mapping
 Managing disk space
 SQL Modes
 Ceating database
Differing Vantages
User
Developer
Access Inquiry
DB
Definition
Relation
Organization
Entry
Validation
Protection
Recovery Storage
Administrator
M S
DB Language Modes
•
•
•
DML - Data Manipulation Language - enter,
inquire, update, delete data from end user or
programming language
DDL - Data Definition Language - define database
objects, attributes and characteristics at conceptual
and physical layers
DAL - Data Administration Language - grant and
revoke data access privileges, manage physical
data configuration, perform backup and recovery
functions
Tables
•
•
Basic storage structure
Base tables
– stored on the disk
– constraints always upheld
•
Virtual tables
– not stored, transient
– join tables
•
Views
Primary Keys
Uniquely
identifies tuple
All base tables must have primary key
Role of PK
– prevent duplicate rows
– assure existence of data
Information
should not be encoded
into primary keys
Composite Primary Key
 Primary
key may be composed of more
than one attribute
 Composite primary key should be minimal
subset
 Unique identifier simplifies lengthy
compound primary key
Foreign Key
 An
attribute in one table refers to a primary
key in another table
 Relationships formed through foreign keys
but not exclusively
SQL DDL
•
CREATE TABLESPACE
– allocates default space for table creation
•
CREATE TABLE
– makes base tables
» define field size
» determine field data types
» name primary key
» define foreign keys
» include all constraints
Table creation
CREATE TABLE SALESPERSON
(SNUMBER VARCHAR2(2)
PRIMARY KEY,
LAST
VARCHAR2(10),
FIRST
VARCHAR2(8),
STREET
VARCHAR2(15),
CITY
VARCHAR2(15),
STATE
VARCHAR2(2),
ZIP_CODE VARCHAR2(9),
COMMISSION
NUMBER(8,2),
COMMISSION_RATE
NUMBER(4,2) );
DROP TABLE SALESPERSON;
DATA TYPES

NUMBER





123457
123456.78
123456.8
exceeds precision
VARCHAR2(size)


NUMBER(9)
number(8,2)
NUMBER(8,1)
NUMBER(5)
123456.78
Variable length character string
DATE


DD-MMM-YY
date arithmetic
sysdate
SQL DML
•
SELECT
– returns table containing all records meeting criteria
•
UPDATE
– makes changes to column contents based on
provided specifications
•
INSERT
– adds rows, placing data in some or all of the
columns
•
DELETE
INSERTS
Insert into emp
values (4243, 'OTTER', 'ENGINEER', 4234,
'20-JUN-95', 2900, NULL, 40);
Insert into emp
(empno, ename, hiredate)
values(1235, 'KINNEY', 22-JUN-95');
Insert into emp
(job, mgr,sal, deptno)
select job, mgr, 2500, deptno
from emp
where ename = 'AUGUST';
Update Rows
 UPDATE
SALESPERSON
SET COMMISSION_RATE =
COMMISSION_RATE+.05
WHERE COMMISSION_RATE
< .15;
Delete
 DELETE
FROM SALESPERSON;
 DELETE FROM SALESPERSON
WHERE STATE = ‘MA’;
 DELETE FROM SALESPERSON
WHERE ZIP_CODE IN (SELECT ZIP
FROM ANOTHER
WHERE CITY = ‘BOSTON’;
SQL DAL
•
GRANT
– Grant access to other users
•
•
•
BACKUP
AUDIT
SYSTEM TABLES