Oracle SQL Fundamentals - Lecture 3

Oracle SQL Fundamentals - Lecture 3

• 1.
  SQL Fundamentals Oracle 11g MU H A M M A D WA H E E D O R A C L E D ATA B A S E D E VE L O P E R E M A I L : m.w a h e e d 3 6 6 8 @ g ma i l . c om . 1 Lecture#3
• 2.
  Lab Work VideoTutorial •Video is embedded inside the slides. •Go through each slide for conceptual knowledge then practical is performed in the video that is provided in last slides. 2
• 3.
  COMMENTS •Use the COMMENTstatementto add to the data dictionary a comment about a table or table column etc. •We use following syntax for creating comments: COMMENT ON TABLE <table_name> IS ‘<comment_statement>’; or COMMENT ON COLUMN <table_name>.<column_name> IS ‘<comment_statement>'; •We use following syntax to remove/drop comments: COMMENT ON TABLE <table_name> IS ' '; or COMMENT ON COLUMN <table_name>.<column_name>IS ' '; 3
• 4.
  View COMMENTS •SELECT *FROM USER_COL_COMMENTS; •SELECT * FROM USER_TAB_COMMENTS; 4
• 5.
  Data Types Data TypeDescription VARCHAR2( size ) Variable length character data with specified size ( 1 to 4000) INT Fixed length number data type with default size 38. CHAR [ size ] Fixed length character data of specified size ( 1 to 2000) NUMBER [ p,s ] Number having precision p(1 to 38) and scale s(-84 to 127) Date It must be between January 1, 4712 and December 31,9999 TIMESTAMP(s) It is date+time data type with scale specified from user on the right of decimal point in time. Scale is 6 by default. 5
• 6.
  Data Integrity Constraints •Theseare used to enforce limitations on the data entered in tables. •It have two levels: column level(applied on specific column definition) table level(applied at table definition) 6
• 7.
  Column Level Constraints •Columnlevel constraints are defined with following syntax: CREATE TABLE <table_name> (<column_name> <data_type> <constraint_definition>); 7
• 8.
  Table Level Constraints •Tablelevel constraints are defined with following syntanx: CREATE TABLE <table_name> (<column_name> <data_type>, <column_name> <data_type>, CONSTRAINT<constraint_name> <constraint_definition>); 8
• 9.
  Constraint Types •There arefollowing five constraints: 1- NULL/NOT NULL 2- UNIQUE 3- PRIMARYKEY 4- FOREIGN KEY 5- CHECK 9
• 10.
  NULL/NOT NULL Constraint •Itdetermines whether a column can be blank or not. •It is only possible at column level. •By default each column is set to NULL. •Example (column level): CREATE TABLE student ( std_id NUMBER(4) NOT NULL, std_name VARCHAR2(30)); •Example(table level): ALTER TABLE student MODIFY std_name VARCHAR2(30)NOT NULL); 10
• 11.
  UNIQUE Constraint •It isused for a field which needs to be unique but also can be null/empty. •Example (column level): contact_no of student table can be empty but must be unique. CREATE TABLE student ( std_contact_no NUMBER(4)CONSTRAINTcontact_uk UNIQUE, ); • Example(table level): CREATE TABLE student (std_contact_no int, CONSTRAINTcontact_uk UNIQUE(std_contact_no)); 11
• 12.
  PRIMARY KEY Constraint •Itrestricts the duplication of values and doesn’t allow null values. •Example(columnlevel): CREATE TABLE student ( std_id int PRIMARYKEY, std_name varchar2(100)); •Example( table level): CREATE TABLE student (std_id int, CONSTRAINTstdid_pk PRIMARY KEY(std_id)); 12
• 13.
  FOREIGN KEY Constraint •Itis used to create the link between data of two tables. •It is based on common field in two tables where parent table have primary key and child table uses it as foreign key. •One table can have multiple foreign keys. •Example(columnlevel): CREATE TABLE result ( std_id int REFERENCES student); •Example(table level): CREATE TABLE result (std_id int, CONSTRAINTstdid_fk FOREIGN KEY(std_id)REFERENCES student(std_id)); 13
• 14.
  CHECK Constraint •Defines acondition that each row mustsatisfy. •It is only possible at column level. •Example(columnlevel): CREATE TABLE student ( age int CONSTRAINTstd_age CHECK(age>18)); •Example(table level): CREATE TABLE result (age int, CONSTRAINTstd_age CHECK (age>18)); 14
• 15.
  Modify Constraints •SQL providesthe facility of modification of constraints on existing schemas. •There are four following facilities to be used on constraints : Add Drop Enable Disable Cascade 15
• 16.
  ADD Constraint •ADD constraint: ALTERTABLE <table_name> ADD CONSTRAINT<constraint_name><constraint_defination>; 16
• 17.
  DROP Constraint •DROPconstraint: ALTER TABLE<table_name> DROPCONSTRAINT<constraint_name>; •*Note: PRIMARYKEY as a special case uses “CASCADE” to disable its foreign keys. For example: ALTER TABLE student DROPPRIMARYKEY CASCADE; In above case foreign key constraint on result table will be deleted too. 17
• 18.
  DISABLE Constraint •DROPconstraint: ALTER TABLE<table_name> DISABLECONSTRAINT<constraint_name>CASCADE; •Apply the “CASCADE” option to disable its dependent integrity constraints. 18
• 19.
  ENABLE Constraint •It isused to ENABLE the DISABLED constraints: ALTER TABLE <table_name> ENABLE CONSTRAINT<constraint_name>; 19
• 20.
  CASCADE Constraint •The CASCADEconstraints clause is used along with the DROPCOLUMN clause. •The CASCADE clause drops all referential integrity constraints that refer to the primary and unique keys defined on the dropped columns. •The CASCADE clause also drops all multicolumn constraints defined on the dropped columns. •Example: ALTER TABLE <table_name> DROP(<constraint_name(s)>)CASCADECONSTRAINTS; 20
• 21.
  Your Suggestions/Feedback? -Elbert Hubbard 21 Email:m.waheed3668@gmail.com