1 / 63

Chapter Six

Chapter Six. Objectives Introduction to SQL Types of SQL statements Concepts of DDL & DML Creating Tables Restrictions on Tables Data Definition Language(DDL) Data Manipulation Language (DML). SQL. Structured Query Language Developed by IBM Used in most Commercial DBMS

radha
Download Presentation

Chapter Six

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. Chapter Six Objectives Introduction to SQL Types of SQL statements Concepts of DDL & DML Creating Tables Restrictions on Tables Data Definition Language(DDL) Data Manipulation Language (DML)

  2. SQL • Structured Query Language • Developed by IBM • Used in most Commercial DBMS • Statements are not case sensitive. • Statements can be on one or more lines. • Reserved words cannot be abbreviated or split over lines. • Terminated with a semi colon. • Statements are entered at SQL prompt. The subsequent lines are numbered (SQL buffer) • Only one statement can be current at any time in the buffer.

  3. Types of SQL Statements • Data Definition Language (DDL) • Data Manipulation Language (DML) • Transaction Control • Session Control • System Control • Embedded SQL

  4. Example:

  5. Data Definition Language (DDL) • Format: CREATE TABLE [schima.]Table_Name (Attribute Attribute_Type [DEFAULT expr] [Col_constraint], …. Attribute Attribute_Type [Table_constraint]);

  6. Data Definition Language (DDL) • Example: SQL> CREATE TABLE student 2 (Name VARCHAR2(80), 3 ID NUMBER(9), 4 GPA NUMBER(3,2), 5 B_Date DATE, 6 Major CHAR(4) 7 );

  7. Data Definition Language Name:User Identifiers: 1-30 characters Start with an alphabet Followed by alphabet, digit, _ Unique Not reserved Not case sensitive

  8. Oracle Data Types: • Built-in Datatypes • ANSI Supported Datatypes • Oracle Supplied Datatypes • User-Defined Types • External Datatypes

  9. Example of Oracle Data Types: • CHAR(size) • VARCHAR2(size) • NUMBER(n,d) • DATE • LONG • ROW(size) • CLOB BLOB BFILE • XML • XML index

  10. Built-in Data Types: 1-Character: Format: CHAR [ (size [ BYTE/CHAR ] )] VARCHAR2 (maxsize [ BYTE/CHAR ] ) ‘4321’ ‘19 Main St. Frostburg’ ‘ * ’ ‘Student’’s ID’

  11. Built-in Data Types: 2-Number:Format: NUMBER [ (Precision] [, Scale] )] NUMBER NUMBER(P,S) 3-Date (&Time):Format: DATE MyBirthdate = ‘11-JAN-37’ TIMESTAMP

  12. Built-in Data Types: 4-Large Objects:Format: BLOB CLOB BFILE CREATE TABLE add_cosc_F08 (title VARCHAR2(80), composite BLOB, text CLOB, graph BFILE);

  13. Built-in Data Types: 5-ROWID:Format: ROWID

  14. ANSI Supported Data Types: Examples: CHAR CHARACTER VARCHAR NUMERIC DECIMAL INTEGER INT FLOAT REAL ……

  15. Oracle Supplied Data Types: Any Type:SYS.ANYDATA SYS.ANYTYPE SYS.ANYDATASET XML Type:XMLType URLType Media Type: ORDAudio ORDImage ORDDoc

  16. Display a Structure of a Table: DESCRIBE Student; Name Null? Type ---------------------------------------------------------- NAME VARCHAR2(80) ID NUMBER(9) GPA ……

  17. Tables • User Tables: • USER_ • ALL_ • DBA_ • V$_ • Data Dictionary • USER_TABLES • USER_OBJECTS • USER_CATalog

  18. Integrity Constraints Not Null Unique Primary Key Foreign Key Check -Why Integrity Constraints?

  19. Integrity Constraints • Inline (Col_Constraint) • Out_of_line(Table_Constraint) NOT NULL

  20. Integrity Constraints • NOT NULL: CREATE TABLE student (Name VARCHAR2(80) NOT NULL, ID NUMBER(9) NOT NULL, GPA NUMBER(3,2), B_Date DATE, Major CHAR(4));

  21. Integrity Constraints • Constraints on Tables: Why? NOT NULL: CREATE TABLE student (Name VARCHAR2(80) NOT NULL, ID NUMBER(9) NOT NULL, GPA NUMBER(3,2), B_Date DATE, Major CHAR(4));

  22. Integrity Constraints UNIQUE: CREATE TABLE student (Name VARCHAR2(80) UNIQUE NOT NULL, ID NUMBER(9) UNIQUE, GPA NUMBER(3,2), B_Date DATE, Major CHAR(4));

  23. Integrity Constraints UNIQUE: CREATE TABLE student (Name VARCHAR2(80), ID NUMBER(9), GPA NUMBER(3,2), B_Date DATE, Major CHAR(4), UNIQUE (Name,ID) );

  24. IntegrityConstraints PRIMARY KEY: CREATE TABLE student (Name VARCHAR2(80), ID NUMBER(9) PRIMARY KEY, GPA NUMBER(3,2), B_Date DATE, Major CHAR(4) );

  25. Integrity Constraints PRIMARY KEY: CREATE TABLE student (Name VARCHAR2(80), ID NUMBER(9), GPA NUMBER(3,2), B_Date DATE, Major CHAR(4), PRIMARY KEY(ID) );

  26. Integrity Constraints FOREIGN KEY: CREATE TABLE Course (C_Num NUMBER(4,0) NOT NULL, Dept VARCHAR2(20) REFERENCES Department(name), Title VARCHAR2(40), Credit NUMBER(1), CONSTRAINT dep_PK PRIMARY KEY (C_Num, Dept) );

  27. Integrity Constraints CREATE TABLE Course (C_Num NUMBER(4,0) NOT NULL, Dept VARCHAR2(20) Title VARCHAR2(40), Credit NUMBER(1), CONSTRAINT dep_PK PRIMARY KEY (C_Num, Dept) CONSTRAINT dep_FK FOREIGN KEY (dept) REFERENCES Department(name) );

  28. Integrity Constraints CREATE TABLE Course (C_Num NUMBER(4,0) NOT NULL, Dept VARCHAR2(20) REFERENCES Department(name) ON DELETE CASCADE, -- ON DELETE SET NULL Title VARCHAR2(40), Credit NUMBER(1), PRIMARY KEY (C_Num, Dept) );

  29. IntegrityConstraints • Range Constraint: CREATE TABLE student (Name VARCHER2(80), ID NUMBER(9) CHECK (ID BETWEEN 000000000 AND 999999999), GPA NUMBER(3,2) CHECK (GPA BETWEEN 0.00 AND 4.00), B_Date DATE, Major CHAR(4) ); --CHECK (grade IN(‘A’, ‘B’, ‘C’, ‘D’, ‘F’)) --Constraint grade_CK CHECK (grade IN (‘A’,’B’,’C’,’D’,’F’))

  30. Integrity Constraints CREATE TABLE student (Name VARCHAR2(80) CONSTRAINT student_nn NOT NULL, ID NUMBER(9) NOT NULL, GPA NUMBER(3,2), B_Date DATE, Major CHAR(4), CONSTRAINT student_UQ UNIQUE (Name,ID) );

  31. IntegrityConstraints CREATE TABLE student (Name VARCHAR2(80), ID NUMBER(9), GPA NUMBER(3,2) CHECK (GPA BETWEEN 0.00 AND 4.00), B_Date DATE, Major CHAR(4), CONSTRAINT student_ID_CK CHECK(ID BETWEEN 000000000 AND 999999999) );

  32. Constraints SELECT Constraint_Name, Constraint_Type, Table_Name FROM user_constraints; Constraint_Name Constraint_Type Table_Name ----------------------------------------------------------------------- SYS_COU2111 P student student_UQ U student student_ID_CK C student dep_FK R student

  33. IntegrityConstraints Constraints States: • DISABLE • ENABLE

  34. Integrity Constraints CREATE TABLE student (Name VARCHAR2(80) CONSTRAINT student_nn NOT NULLDISABLE, ID NUMBER(9) NOT NULL DISABLE, GPA NUMBER(3,2), B_Date DATE, Major CHAR(4), CONSTRAINT student_UQ UNIQUE (Name,ID) DISABLE );

  35. Default CREATE TABLE Course (C_Num NUMBER(4,0) Default ‘XXXX’ NOT NULL, Dept VARCHAR2(20) REFERENCES Department(name) ON DELETE CASCADE, Title VARCHAR2(40), Credit NUMBER(1).DEFAULT 3, PRIMARY KEY (C_Num, Dept));

  36. Default CREATE TABLE Temp ( inserts VARCHAR2(80) DEFAULT USER, arrived DATE DEFAULT SYSDATE, loggedInAs NUMBER(4) DEFAULT UID );

  37. Create table from a table CREATE TABLE tempstudent AS SELECT Name ,ID ,GPA, B_Date FROM student;

  38. Delete a Table: • DROP TABLE Student; • DROP TABLE Student CASCADE CONSTRAINTS; • DROP TABLE Student PURGE; • TRUNCATE TABLE Student; • PURGE TABLE Student;

  39. Flash_back • Privilege is required • DESC student; • DROP TABLE student CASCADE CONSTRAINTS; • SELECT * FROM RECYCLEBIN; -- USER_RECYCLEBIN

  40. Flash_back Object_Name Original_Name Operation Type TS_Name -------------------------------------------------------------------------------------------------- RB$$48448$TABLE$0 STUDENT DROP TABLE USERS Creation Droptime ----------------------------------------------------------- 2005-01-23:14:11:50 2005-05-01:12:30:31

  41. Restoring Tables from Recycle Bin: FLASHBACK TABLE Student TO BEFORE DROP RENAME TO Student2;

  42. Changing an existing Table Structure • Adding Columns: ALTER TABLE student ADD (Address VARCHAR2(50), Phone CHAR(10) ); • Modify Table Condition: ALTER TABLE student MODIFY (ID NUMBER(10,0), Name VARCHAR2(200) ); (continued)

  43. Changing an Existing Table Structure 3. Deleting Columns: ALTER TABLE student DROP COLUMN Address; ALTER TABLE student DROP COLUMN (Major, Minor); 4. Modify Table Condition: ALTER TABLE student SET UNUSED COLUMN Address ;

  44. Renaming Table Columns 5. ALTER TABLE student RENAME COLUMN ID TO NewID;

  45. Rules for Adding and Modifying: • You can add columns with no NOT NULL • You can increase the CHAR width • You can increase the number of Digits • You can increase/decrease the number of decimals • You can convert CHAR to VARCHAR2 • You can change data type if the column contains no values

  46. Changing the name of a table: RENAME student TO GradStudent;

  47. Adding Comments to a Table: COMMENT ON TABLE student IS ‘staff information’; COMMENT ON COLUMN student.column IS ‘text’;

  48. Viewing information about a Table: USER_TABLES USER_TAB_COLUMNS USER_ALL_TABLES USER_TAB_COMMENTS USER_COL_COMMENTS USER_TAB_STATISTICS USER_TAB_MODIFICATIONS ALL_TABLES DBA_TABLES

  49. Querying Data • SELECT * FROM USER_TABLES; • SELECT DISTINCT object_type FROM user_object; • SELECT * FROM cat;

  50. Add a constraint to an existing table: • ALTER TABLE student ADD CONSTRAINT student_pk PRIMARY KEY (ID);

More Related