1 / 16

SQL Components

SQL Components. DML DDL DAL. Overview. Getting the records onto the disk - mapping Managing disk space SQL Modes Ceating database. Differing Vantages. User. Developer. Inquiry. Definition. Access. Relation. Organization. Entry. D B. Validation. M S. Protection. Storage.

mccainr
Download Presentation

SQL Components

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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), 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;

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

More Related