Download - Using Relational Databases and SQL
![Page 1: Using Relational Databases and SQL](https://reader035.vdocuments.us/reader035/viewer/2022062723/56814020550346895dab7a66/html5/thumbnails/1.jpg)
Using Relational Databases and SQL
Steven EmoryDepartment of Computer Science
California State University, Los Angeles
Lecture 9:Data Manipulation Language
![Page 2: Using Relational Databases and SQL](https://reader035.vdocuments.us/reader035/viewer/2022062723/56814020550346895dab7a66/html5/thumbnails/2.jpg)
Miscellany
School is almost over
Last 3 lectures are pretty relaxing
DML (How to update and modify a database)
DDL (How to create a database)
Database Design (How to design a database)
![Page 3: Using Relational Databases and SQL](https://reader035.vdocuments.us/reader035/viewer/2022062723/56814020550346895dab7a66/html5/thumbnails/3.jpg)
Topics for Today
Inserting Records (Pages 164 – 167)
Deleting Records (Pages 167 – 168)
Updating Records (Pages 168 – 173)
Transactions (Pages 173 – 179)
![Page 4: Using Relational Databases and SQL](https://reader035.vdocuments.us/reader035/viewer/2022062723/56814020550346895dab7a66/html5/thumbnails/4.jpg)
Before We Start
When inserting and deleting data, you are going to mess up because nobody is perfect
If you mess up there are two ways to restore the original database:
Remove and restore the tables
Use transactions (use BEGIN and ROLLBACK)
I prefer using BEGIN and ROLLBACK
![Page 5: Using Relational Databases and SQL](https://reader035.vdocuments.us/reader035/viewer/2022062723/56814020550346895dab7a66/html5/thumbnails/5.jpg)
Inserting Records
Two syntaxes:
INSERT INTO
Insert one record at a time
INSERT SELECT
Insert one or more records at a time
![Page 6: Using Relational Databases and SQL](https://reader035.vdocuments.us/reader035/viewer/2022062723/56814020550346895dab7a66/html5/thumbnails/6.jpg)
Inserting Records
INSERT INTO Syntax
-- Form #1: Insert whole record.INSERT INTO tablenameVALUES(value1, value2, ..., valuen);
-- Form #2: Insert partial record.INSERT INTO tablename(field1, field2, ..., fieldn)VALUES(value1, value2, ..., valuen);
![Page 7: Using Relational Databases and SQL](https://reader035.vdocuments.us/reader035/viewer/2022062723/56814020550346895dab7a66/html5/thumbnails/7.jpg)
Inserting Records
INSERT SELECT Syntax
-- Form #1: Insert whole record.INSERT INTO destination_tableSELECT field1, field2, ..., fieldnFROM source_tablesWHERE conditions;
-- Form #2: Insert partial record.INSERT INTO destination_table(df1, df2, ..., dfn)SELECT sf1, sf2, ..., sfnFROM source_tablesWHERE conditions;
![Page 8: Using Relational Databases and SQL](https://reader035.vdocuments.us/reader035/viewer/2022062723/56814020550346895dab7a66/html5/thumbnails/8.jpg)
INSERT Examples
Examples:
Josh Scott Wagner was just hired by Lyric Music as a salesperson working under supervisor Bob Bentley. Base salaries for new recruits are set at $50.00. Add Josh to the database.INSERT INTO SalesPeopleVALUES(5, 'Josh', 'Wagner', 'jsw', 50.00, 1);
Note that we have to use 1 instead of using a subquery. MySQL doesn't allow INSERT INTO and a subquery selection from the same table.SELECT SalesID FROM SalesPeople WHEREFirstName = 'Bob' AND LastName = 'Bentley';
![Page 9: Using Relational Databases and SQL](https://reader035.vdocuments.us/reader035/viewer/2022062723/56814020550346895dab7a66/html5/thumbnails/9.jpg)
INSERT Examples
Way around it is to use INSERT SELECT
INSERT INTO + subquery from same table = NO!INSERT INTO SalesPeopleVALUES(5, 'Josh', 'Wagner', 'jsw', 50.00,(SELECT SalesID FROM SalesPeople WHEREFirstName = 'Bob' AND LastName = 'Bentley'));
INSERT SELECT from same table = OK!INSERT INTO SalesPeopleSELECT 5, 'Josh', 'Wagner', 'jsw', 50.00, SalesIDFROM SalesPeople WHERE FirstName = 'Bob'AND LastName = 'Bentley';
![Page 10: Using Relational Databases and SQL](https://reader035.vdocuments.us/reader035/viewer/2022062723/56814020550346895dab7a66/html5/thumbnails/10.jpg)
INSERT Examples
Example:
Bobby Crum, a member of 21 West Elm, has also decided to join The Neurotics. Update the table XrefArtistsMembers to reflect this. Bobby won't be the responsible member for the artist he his joining.INSERT INTO XrefArtistsMembersVALUES((SELECT ArtistID FROM Artists WHERE ArtistName = 'The Neurotics'), (SELECT MemberID FROM Members WHERE FirstName = 'Bobby' AND LastName = 'Crum'), 0);
![Page 11: Using Relational Databases and SQL](https://reader035.vdocuments.us/reader035/viewer/2022062723/56814020550346895dab7a66/html5/thumbnails/11.jpg)
INSERT Examples
Example:
-- All members of 21 West Elm have decided to also become members of The Neurotics (as non-responsible members). Update the table XrefArtistsMembers to reflect this.INSERT INTO XrefArtistsMembersSELECT MemberID, (SELECT ArtistID FROM Artists WHERE ArtistName = 'The Neurotics'), 0FROM Members JOIN XrefArtistsMembers USING(MemberID) JOIN Artists USING(ArtistID)WHERE ArtistName = '21 West Elm';
![Page 12: Using Relational Databases and SQL](https://reader035.vdocuments.us/reader035/viewer/2022062723/56814020550346895dab7a66/html5/thumbnails/12.jpg)
Deleting Records
Deletes one or more rows from a table
Deletes all rows without WHERE condition
Single-Table DELETE Syntax
DELETEFROM tablenameWHERE conditions;
Multi-Table DELETE Syntax
Not in book
Complicated and ugly!
On next slide
![Page 13: Using Relational Databases and SQL](https://reader035.vdocuments.us/reader035/viewer/2022062723/56814020550346895dab7a66/html5/thumbnails/13.jpg)
Deleting Records
Multi-table DELETE Syntax #1 (Preferred)
DELETE T1, T2, ..., TnFROM T1 JOIN T2 JOIN ... JOIN TnWHERE conditions;
Note: If you use table alias in the FROM clause, you must use the alias in the DELETE clause as well (see examples later on).
Multi-table DELETE Syntax #2 (Ugly)
DELETEFROM T1, T2, ..., TnUSING T1 JOIN T2 JOIN ... JOIN TnWHERE conditions;
![Page 14: Using Relational Databases and SQL](https://reader035.vdocuments.us/reader035/viewer/2022062723/56814020550346895dab7a66/html5/thumbnails/14.jpg)
DELETE Examples
Examples:
-- Delete all rows from the Artists table.DELETE FROM Artists;
-- Delete all titles by The Neurotics (subquery).DELETE FROM TitlesWHERE ArtistID = (SELECT ArtistID FROMArtists WHERE ArtistName = 'The Neurotics');
-- Delete all titles by The Neurotics (multi-table).DELETE T FROM Titles TJOIN Artists AWHERE A.ArtistID = T.ArtistID ANDA.ArtistName = 'The Neurotics';
![Page 15: Using Relational Databases and SQL](https://reader035.vdocuments.us/reader035/viewer/2022062723/56814020550346895dab7a66/html5/thumbnails/15.jpg)
DELETE Examples
Examples:
-- Delete all titles and tracks by the The Neurotics.DELETE T, K FROM Artists A JOIN Titles T JOIN Tracks K WHERE A.ArtistID = T.ArtistID AND T.TitleID = K.TitleID AND A.ArtistName = 'The Neurotics';
-- Delete The Neurotics from the Artists table and all titles and tracks by the them as well.DELETE A, T, K FROM Artists A JOIN Titles T JOIN Tracks K WHERE A.ArtistID = T.ArtistID AND T.TitleID = K.TitleID AND A.ArtistName = 'The Neurotics';
![Page 16: Using Relational Databases and SQL](https://reader035.vdocuments.us/reader035/viewer/2022062723/56814020550346895dab7a66/html5/thumbnails/16.jpg)
Updating RecordsTo update existing records:
-- Single-table syntax.UPDATE tablenameSET field1 = value1, field2 = value2, ...WHERE conditions;
-- Multi-table equi-join syntax.UPDATE tablename1, tablename2, ...SET field1 = value1, field2 = value2, ...WHERE conditions;
-- Multi-table subquery syntax.UPDATE tablenameSET field1 = subquery1, field2 = subquery2, ...WHERE conditions;
![Page 17: Using Relational Databases and SQL](https://reader035.vdocuments.us/reader035/viewer/2022062723/56814020550346895dab7a66/html5/thumbnails/17.jpg)
Updating Examples
Example:
-- The Bullets have decided to change their name to The Rockets. Update the database to reflect this change.UPDATE ArtistsSET ArtistName = 'The Rockets'WHERE ArtistName = 'The Bullets';
![Page 18: Using Relational Databases and SQL](https://reader035.vdocuments.us/reader035/viewer/2022062723/56814020550346895dab7a66/html5/thumbnails/18.jpg)
Updating Examples
Example:
-- The Bullets have decided to change their name to The Rockets and update their web domain to www.therockets.com. Update the database to reflect these changes.UPDATE ArtistsSET ArtistName = 'The Rockets', WebAddress = 'www.therockets.com'WHERE ArtistName = 'The Bullets';
![Page 19: Using Relational Databases and SQL](https://reader035.vdocuments.us/reader035/viewer/2022062723/56814020550346895dab7a66/html5/thumbnails/19.jpg)
Updating Examples
Example:
-- All members that had Bob Bentley as a sales contact will now have Scott Bull as a sales contact. Update the database to reflect these changes. Use the subquery syntax.UPDATE MembersSET SalesID = (SELECT SalesID FROM SalesPeople WHERE FirstName = 'Scott' AND LastName = 'Bull')WHERE SalesID = (SELECT SalesID FROM SalesPeople WHERE FirstName = 'Bob' AND LastName = 'Bentley');
![Page 20: Using Relational Databases and SQL](https://reader035.vdocuments.us/reader035/viewer/2022062723/56814020550346895dab7a66/html5/thumbnails/20.jpg)
Updating Examples
Example:
-- All members that had Bob Bentley as a sales contact will now have Scott Bull as a sales contact. Update the database to reflect these changes. Use the join syntax.UPDATE Members M, SalesPeople SSET M.SalesID = (SELECT SalesID FROM SalesPeople WHERE FirstName = 'Scott' AND LastName = 'Bull')WHERE M.SalesID = S.SalesID AND S.FirstName = 'Bob' AND S.LastName = 'Bentley';
![Page 21: Using Relational Databases and SQL](https://reader035.vdocuments.us/reader035/viewer/2022062723/56814020550346895dab7a66/html5/thumbnails/21.jpg)
Transactions
ACID
Atomicity
Consistency
Isolation
Durability
SQL Keywords
BEGIN/START TRANSACTION
COMMIT
ROLLBACK