SQL Components

1
SQL Components

• DML
• DDL
• DAL

2
Overview

• Getting the records onto the disk - mapping
• Managing disk space
• SQL Modes
• Ceating database

3
Differing Vantages
User
Developer
Inquiry
Definition
Access
Relation
Organization
Entry
D B
Validation
M S
Protection
Storage
Recovery
Administrator
4
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

5
Tables

• Basic storage structure
• Base tables
• stored on the disk
• constraints always upheld
• Virtual tables
• not stored, transient
• join tables
• Views

6
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

7
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

8
Foreign Key

• An attribute in one table refers to a primary key
  in another table
• Relationships formed through foreign keys but not
  exclusively

9
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

10
Table creation
CREATE TABLE SALESPERSON (SNUMBER
VARCHAR2(2) PRIMARY KEY, LAST VARCHAR2(10), FIR
ST VARCHAR2(8), STREET VARCHAR2(15), CITY
VARCHAR2(15), STATE VARCHAR2(2), ZIP_CODE
VARCHAR2(9), COMMISSION NUMBER(8,2), COMMISSIO
N_RATE NUMBER(4,2) )
DROP TABLE SALESPERSON
11
DATA TYPES

• NUMBER 123456.78
• NUMBER(9) 123457
• number(8,2) 123456.78
• NUMBER(8,1) 123456.8
• NUMBER(5) exceeds precision
• VARCHAR2(size)
• Variable length character string
• DATE DD-MMM-YY
• date arithmetic
• sysdate

12
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

13
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'
14
Update Rows

• UPDATE SALESPERSON SET COMMISSION_RATE
  COMMISSION_RATE.05 WHERE COMMISSION_RATE
  lt .15

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

16
SQL DAL

• GRANT
• Grant access to other users
• BACKUP
• AUDIT
• SYSTEM TABLES