1 / 12

SQL Data Modification

SQL Data Modification. Spread throughout chapter 7. Changing a Table. While there is One SQL statement to obtain info (SELECT), there are 3 for changing a table INSERT - adds records to a table DELETE - deletes records from a table UPDATE - changes records in a table. Insert New Records.

lsill
Download Presentation

SQL Data Modification

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 Data Modification • Spread throughout chapter 7

  2. Changing a Table • While there is One SQL statement to obtain info (SELECT), there are 3 for changing a table • INSERT - adds records to a table • DELETE - deletes records from a table • UPDATE - changes records in a table

  3. Insert New Records • Simplest form - single record INSERT INTO enrollments VALUES (‘66419’,’2121’,’’,’’);

  4. Insert New Records • single record - perhaps not all attributes INSERT INTO enrollments(stdssn , classindex) VALUES (‘1113’,’66419’); • We can insert more than one record at a time INSERT INTO enrollments(classindex,stdssn) VALUES (‘66416’,’1111’), (‘66416’,’2222’);

  5. Inserting • DBMS should enforce all constraints - including referential integrity, no value for attribute specified as NOT NULL, duplicate primary key values • All DBMSs may not fully enforce - then user and/or programmer must defend against errors

  6. Inserting from a Query • Since the result of a query is a table, we can actually load data into a (usually temporary) table as a result of a query • First need to create table – not covered yet – here’s a simple table creation: CREATE TABLE dept_info (dname CHAR (3), NUM_CLASSES INTEGER, TOTAL_ENROLL INTEGER, MAX_ENROLL INTEGER);

  7. Inserting from a Query (con) • Then can fill table from query results: INSERT INTO dept_info (dname, NUM_CLASSES, TOTAL_ENROLL, MAX_ENROLL) SELECT DEPT, COUNT(*), SUM (enrollment), MAX (enrollment) FROM SECTIONS GROUP BY DEPT;

  8. DELETE • DELETE is pretty simple - it removes a set of records (generally that meet some condition) DELETE FROM STUDENT WHERE SSN = ‘1111’; DELETE FROM STUDENT WHERE GPA < 1.5;

  9. UPDATE • UPDATE can modify one or more attributes in one or more records. • WHERE clause selects records to be modified UPDATE STUDENT SET HOMETOWN = ‘Vorhees’ WHERE SSN = ‘1113’; • Can do multiple attributes: UPDATE SECTIONS SET TIME = ‘MW610’, ROOM = ‘BC125’ WHERE INDEX = ‘66420’;

  10. UPDATE (con) • Can modify multiple records: UPDATE STUDENT SET YEAR = ‘Sr’ WHERE numcreditsearned > 90; • Can modify using a calculation: UPDATE SECTIONS SET STOP = STOP + 5 WHERE DEPT = ‘CSC’ AND COURSE = ‘157’;

  11. Basic Data Management • Saving the Table Contents COMMIT <table names>; COMMIT PRODUCT; • Restoring the Table Contents ROLLBACK

  12. End Updates – and Thus End DML

More Related