1 / 14

SQL’s Data Definition Language (DDL)

SQL’s Data Definition Language (DDL) . DDL statements define, modify and remove objects from data dictionary tables maintained by the DBMS Whenever you use a DDL statement, the DBMS changes metadata (“data about data”) maintained in the data dictionary

heidi
Download Presentation

SQL’s Data Definition Language (DDL)

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’s Data Definition Language (DDL) • DDL statements define, modify and remove objects from data dictionary tables maintained by the DBMS • Whenever you use a DDL statement, the DBMS changes metadata (“data about data”) maintained in the data dictionary • Objects that can be defined include: table, view, sequence, index

  2. Oracle’s Data Dictionary • Data dictionary tables maintain data about the structure of databases • Users cannot directly insert, update or delete information in the data dictionary • DDL statements cause the DBMS to add/update or delete information in the data dictionary tables • Oracle’s data dictionary views such as ALL_OBJECTS, ALL_TABLES, ALL_CONSTRAINTS, ALL_COLUMNS can be queried using SELECT statements and contain information about objects you have access to

  3. SQL’s DDL Statements • Objects are defined using the CREATE statement • Some objects can be modified using the ALTER statement • Objects are removed using the DROP statement • Oracle’s data dictionary views such as USER_OBJECTS, USER_TABLES, USER_CONSTRAINTS, USER_COLUMNS, USER_SEQUENCES, USER_INDEXES can be queried using SELECT statements and contain information about objects you have created

  4. Schema • The DataBase Administrator (DBA) has set up your Oracle account with permission for you to create objects in a schema (schema has same name as your account) • A schema (same as DB2’s collection) is a collection of related tables, views, sequences and indexes, i.e. a database

  5. System Datatypes • System-defined datatypes include: • CHAR(n): Fixed length character string often used for Primary and Foreign Key numeric fields and also for codes such as ProgramCode(CPA/CPD/CNS/…); • eg. program_code CHAR(3) • VARCHAR2(n): Variable length character string often used for names; • eg. last_name VARCHAR2(30) • NUMBER(n,r): Real numbers with n digits in total and with r digits to the right of the decimal point; eg hrly_pay_rate NUMBER(5,2) can store hourly pay rates from 0.00 to 999.99 • INTEGER: Integer values up to 2*(10**9) • DATE: dates in the format DD-MON-YYYY or MM/DD/YYYY

  6. Create a Table • You define a table and its columns using the CREATE TABLE statement using the basic format: CREATE TABLE tablename ( column1name datatype(length), column2name datatype(length),… column255name datatype(length)) eg. CREATE TABLE student (id CHAR(9), fname VARCHAR2(30), lname VARCHAR2(30), gpa decimal(2,1) )

  7. Create a Table (ctd) • You can also create a table based on an existing table: CREATE TABLE table1name AS SELECT col1name, …, colnname FROM table2name WHERE … eg. CREATE TABLE honourstudent AS SELECT id, lname FROM student WHERE gpa = 4.0

  8. Change Table Structure • Change structure of a table (i.e. add columns or constraints, remove columns or constraints, change properties of a column) using the ALTER TABLE statement: ALTER TABLE tablename ( ADD col1name datatype(length), DROP col2name, MODIFY col3name datatype(length), MODIFY col4name NOT NULL, … )

  9. ALTER TABLE Examples • ALTER TABLE student ADD (address VARCHAR(40)) • ALTER TABLE student MODIFY (lname VARCHAR2(35) ) • ALTER TABLE student DROP COLUMN gpa

  10. Remove Table from Data Dictionary • Remove table from data dictionary (and also deletes all data in table) DROP TABLE tablename • Deletes all data in table and frees storage but leaves table in data dictionary TRUNCATE TABLE tablename

  11. Constraints • Constraints protect the integrity of data in a database • Constraints are defined on tables and columns in tables • Constraint types: PRIMARY KEY, FOREIGN KEY, NOT NULL, CHECK, UNIQUE • All constraints should be given a name, except for NOT NULLs, so error messages produced will include the name of the constraint violated

  12. Constraints (ctd) • Information about constraints is maintained in the data dictionary and can be queried using the view USER_CONSTRAINTS • Constraints may be defined as part of a CREATE TABLE statement • Constraints may be added, dropped or disabled (but not modified) by using an ALTER TABLE statement

  13. Constraint Example • CREATE TABLE student ( id CHAR(9) PRIMARY KEY, fname VARCHAR(30) NOT NULL, lname VARCHAR2(30) NOT NULL, sem_num SMALLINT, soc_ins CHAR(9) CONSTRAINT soc_ins_uq UNIQUE(soc_ins), prog_code CHAR(3) NOT NULL, CONSTRAINT student_prog_code_fk FOREIGN KEY (prog_code) REFERENCES program(progcode) )

  14. Constraint Examples • ALTER TABLE student ADD CONSTRAINT student_sem_num CHECK (sem_num BETWEEN 1 AND 6) • ALTER TABLE student DROP UNIQUE (soc_ins_uq) • ALTER TABLE student DROP PRIMARY KEY • ALTER TABLE student DROP CONSTRAINT student_sem_num • ALTER TABLE student DISABLE CONSTRAINT student_prog_code_fk

More Related