Download
recap of sql n.
Skip this Video
Loading SlideShow in 5 Seconds..
Recap of SQL PowerPoint Presentation
Download Presentation
Recap of SQL

Recap of SQL

0 Views Download Presentation
Download Presentation

Recap of SQL

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Recap of SQL Lab no 8 Advance Database Management System

  2. Outline • Revision of SQL Commands • Insert • Update • Delete

  3. INSERT Statement • There are two types of INSERT statements • One row at a time can be inserted in tables. Specify values for each column with the VALUES Clause • Multiple rows can be inserted in a single INSERT Statement.

  4. INSERT INTO Customer ( CustID, Name, Address, Phone ) VALUES ( 101, ‘Ali Khan’, ’21 Sabzazar Karachi’, ’092-021-8311865’ )

  5. Example – One row at a time INSERT INTO Customer ( CustID, Name, Address, Phone ) VALUES ( 101, ‘Ali Khan’, ’21 Sabzazar Karachi’, ’092-021-8311865’ )

  6. Example – One row at a time INSERT INSERT INTO Customer VALUES ( 101, ‘Ali Khan’, ’21 Sabzazar Karachi’, ’092-021-8311865’ )

  7. Example – Multiple rows INSERT INSERT INTO DBMSStudent SELECT * FROM Student WHERE StdMajor = ‘DBMS’

  8. UPDATE Statement • There can be 2 types of UPDATE statements • Single column update • Multiple column update

  9. Single Column UPDATE UPDATE Employee SET EmpSalary=EmpSalary*5 WHERE EmpDept=‘Finance’ Note: All Employees of Finance Dept got five times raise in salary

  10. Multiple Columns UPDATE UPDATE Employee SET EmpLastName=‘Homer’, EmpPhone=’92-51-223123’, EmpDept=‘Accounts’ WHERE EmpDept=‘Finance’

  11. Multiple Columns UPDATE UPDATE Student SET StdMajor=‘Computer Engineering’, StdSession= ‘2k3-CP’ Notice there is no WHERE condition

  12. DELETE Statement • There are 2 types of DELETE statements • Selected rows DELETE • All rows DELETE

  13. Example - Selected Rows DELETE DELETE FROM Student WHERE StdMajor=‘IS’ AND StdSession=‘Fall04’

  14. Example - Selected Rows DELETE • DELETE statement Using Join Operator DELETE Offering.* FROM Offering INNER JOIN Faculty ON Offering.FacID=Faculty.FacID WHERE FacFisrtName=‘John’ AND FacLastName=‘Barron’ Note: Used when rows of a table reference other tables

  15. Example – All rows DELETE DELETE FROM DBMSStudent

  16. DELETE and UPDATE actions for Referenced Rows • Deleting a referenced row : What happens to related rows (in foreign key table) when referenced rows are deleted • Updating the PK of a referenced row: What happens to related rows when the primary key of the referenced row is updated

  17. Things to done … • Some ACTIONS must be applied on referenced rows of the tables in CREATE TABLE statements • Restrict (NO ACTION) • Cascade (CASCADE) • Nullify (SET NULL) • Default (SET DEFAULT)

  18. Example CREATE TABLE Enrollment ( EnrID INTEGER NOT NULL, Stdid CHAR(11) NOT NULL, Enrgrade DECIMAL(3,2) CONSTRAINT PKEnrID PRIMARY KEY(EnrID), CONSTRAINT FKStdid FOREIGN KEY(STDid) REFERENCES Student ON DELETE NO ACTION ON UPDATE CASCADE )