1 / 18

Advanced SQL Commands: Insert, Update, Delete - Database Management System Outline

This recap covers advanced SQL commands including INSERT, UPDATE, and DELETE statements in a database management system. Learn about various types of INSERT statements for single or multiple rows, different types of UPDATE statements, and the two types of DELETE statements. Explore examples and actions for referenced rows in SQL.

justincortez
Download Presentation

Advanced SQL Commands: Insert, Update, Delete - Database Management System Outline

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

More Related