SQL Components DML DDL DAL Overview u Getting

SQL Components DML DDL DAL

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

Differing Vantages User DB Developer Access Inquiry Entry Definition Relation Organization 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 u. Uniquely identifies tuple u. All base tables must have primary key u. Role of PK – prevent duplicate rows – assure existence of data u. Information should not be encoded into primary keys

Composite Primary Key u Primary key may be composed of more than one attribute u Composite primary key should be minimal subset u Unique identifier simplifies lengthy compound primary key

Foreign Key u An attribute in one table refers to a primary key in another table u 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 VARCHAR 2(2) PRIMARY KEY, LAST VARCHAR 2(10), FIRST VARCHAR 2(8), STREET VARCHAR 2(15), CITY VARCHAR 2(15), STATE VARCHAR 2(2), ZIP_CODE VARCHAR 2(9), COMMISSION NUMBER(8, 2), COMMISSION_RATE NUMBER(4, 2) ); DROP TABLE SALESPERSON;

DATA TYPES l NUMBER t t l 123457 123456. 78 123456. 8 exceeds precision VARCHAR 2(size) t l NUMBER(9) number(8, 2) NUMBER(8, 1) NUMBER(5) 123456. 78 Variable length character string DATE DD-MMM-YY t t 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 u UPDATE SALESPERSON SET COMMISSION_RATE = COMMISSION_RATE+. 05 WHERE COMMISSION_RATE <. 15;

Delete u DELETE FROM SALESPERSON; u DELETE FROM SALESPERSON WHERE STATE = ‘MA’; u 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