1 / 50

More SQL Statements To Modify Tables

More SQL Statements To Modify Tables. Books ISBN bkTitle bkPrice pubID. Authors auID auName auPone. Books/Authors ISBN auID. Publishers pubID pubName pubPhone. Library Database. INNER JOIN Simplified in SELECT Query.

molly-cervantes
Download Presentation

More SQL Statements To Modify Tables

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. More SQL Statements To Modify Tables

  2. BooksISBN bkTitle bkPrice pubID AuthorsauID auName auPone Books/AuthorsISBNauID PublisherspubID pubName pubPhone Library Database

  3. INNER JOIN Simplified in SELECT Query SELECT bkTitle, pubNameFROM Books INNER JOIN PublishersON Books.pubID = Publishers.pubIDWHERE Books.bkPrice <= 50; is equivalent to SELECT bkTitle, pubNameFROM Books, PublishersWHERE Books.pubID = Publishers.pubIDAND Books.bkPrice <= 50;

  4. INNER JOIN Simplified in SELECT Query (2) SELECT bkTitle, auNameFROM Books INNER JOIN BooksAuthors INNER JOIN Authors ON BooksAuthors.auID = Authors.auIDON Books.ISBN = BooksAuthors.ISBN WHERE Books.bkPrice <= 50; is equivalent to SELECT bkTitle, auNameFROM Books, BooksAuthors, AuthorsWHERE Books.ISBN = BooksAuthors.ISBNAND (BooksAuthors.auID = Authors.auIDAND (Books.bkPrice <= 50) );

  5. UPDATE More Than One Column • Change the price of “Iliad” to $50 and pubID to 3.UPDATE Books SET bkPrice = 50, pubID = 3WHERE title= ‘Iliad’; • What will result from the following statement?UPDATE Books SET bkPrice = 50;

  6. UPDATE Statement with Subquery • Raise the price of all books from publisher “Big House” by 10 % • Required Tables: • Books (ISBN, bkTitle, bkPrice, pubID) • Publishers (pubID, pubName, pubPhone)

  7. UPDATE Statement with Subquery • Raise the price of all books from publisher “Big House” by 10 % • UPDATE BooksSET bkPrice = bkPrice * 1.1WHERE pubID in SubqueryNote: subquery • SELECT pubIDFROM PublishersWHERE pubName = “Big House”

  8. Raise the price of all books from publisher “Big House” by 10 % • UPDATE BooksSET bkPrice = bkPrice * 1.1WHERE Books.pubID in (SELECT Publishers.pubID FROM Publishers WEHRE pubName = “Big House”)

  9. Alternately…UPDATE Books INNER JOIN Publishers ON Books.pubID = Publishers.pubIDSET Books.bkPrice = Books.bkPrice * 1.1WHERE Publishers.pubName=”Big House";

  10. UPDATE with Data from Another Table • Update price column in Books table, with new prices from table NewPrices (ISBN, price). BooksISBN bkTitle bkPrice pubID NewPricesISBN price

  11. UPDATE with Data from Another Table • Update price column in Books table, with new prices from table NewPrices (ISBN, price).UPDATE Books INNER JOIN NewPricesON Books.ISBN = NewPrices.ISBNSET Books.bkPrice = NewPrices.priceWHERE Books.bkPrice <> NewPrices.price

  12. Previewing Update • To check which records will be modified by the UPDATE statement… SELECT Books.* FROM Books INNER JOIN NewPrices ON Books.ISBN = NewPrices.ISBNWHERE BOOKS.bkPrice<>NewPrices.price;

  13. Change author “Shakespeare’s” telephone number to “123-4568”. Reduce the price of all books from publisher “Small House” by 10%. Raise the price of all books by author “Shakespeare” by 10%. Your Turn

  14. Change author Shakespear's phone number to "123-4568“. UPDATE Authors SET auPhone = "123-4568“WHERE auName = "Shakespeare";

  15. Reduce the price of all books published by "Small House" by 10%. UPDATE Books SET Books.bkPrice = Books.bkPrice * 0.90WHERE Books.pubID IN (SELECT Publishers.pubID FROM Publishers WHERE Publishers.pubName="Small House");

  16. Difference Between “IN” and “=“ • WHERE Books.pubID = (single value) • But,SELECT Publishers.pubIDFROM PublishersWHERE Publishers.pubName = “XXX”OR Publishers.pubName = “YYY”may return multiple values. • WHERE Books.pubID IN (No1, No2)same as Books.pubID = No1 OR Books.pubID = No2

  17. Raise by 10% the price of all books authored by Shakespeare. BooksISBNbkPrice BooksAuthosISBNauID AuthorsauIDauName UPDATE BooksSET bkPrice = bkPrice * 1.10WHERE ISBN IN SELECT BooksAuthors.ISBNFROM BooksAuthorsWHERE BooksAuthors.auID = SELECT auIDFROM AuthorsWHERE auName = “Shakespeare”

  18. Raise by 10% the price of all books authored by "Shakespeare”. UPDATE Books SET Books.bkPrice = Books.bkPrice * 1.10WHERE Books.ISBN IN (SELECT BooksAuthors.ISBN FROM BooksAuthors WHERE BooksAuthors.auID = (SELECT Authors.auID FROM Authors WHERE Authors.auName = "Shakespeare“ ) );

  19. Raise the price by 5% of all books by Author Shakespeare. UPDATE BooksSET bkPrice = bkPrice * 1.05FROM Books INNER JOIN BooksAuthors INNER JOIN Authors ON BooksAuthors.auID = Authors.auIDON Books.ISBN = BooksAuthors.ISBNWHERE Authors.auName = “Shakespeare”;

  20. Adding New RecordsINSERT INTO Statement • INSERT INTO TableNameVALUES (“value-1”, “value-2”, “value-3”) • INSERT INTO TableName (field-2, field-3)VALUES (“value-2”, “value-3”)

  21. Adding New RecordsINSERT INTO Statement • INSERT INTO BooksVALUES (“1-1111-1111-1”, “SQL Is Fun”, 1, 25.00) • INSERT INTO Books (ISBN, Title)VALUES (“2-2222-2222-2”, “Born to Code”)

  22. Your Turn • Add the following record to Books • ISBN: 1-2345-6789-0 • title: SQL for Dummies • pubID: 3 • price: $35.50 • Add the following record to Authors • auID: 14 • auName: Brando

  23. Inserting from Another Table • Create a new table called NewAuthors with same fields as Authors. • Add to NewAuthors a field called new (boolean) • Add 3 new authors in NewAuthors.

  24. Inserting from Another Table • To copy data from NewAuthors to AuthorsINSERT into Authors (auName, auPhone) SELECT NewAuthors,auName, NewAuthors.auPhone FROM NewAuthors WHERE NewAuthors.new = true;

  25. Removing RecordsDELETE Statement • Syntax DELETE FROM TableName WHERE Criteria • Deletes whole records (rows) • Criteria chooses the rows to be deleted • Can delete whole table data, but not structure

  26. Delete Statement (cont) • Delete the book whose ISBN is 0-12-345678-9 • DELETE FROM BooksWHERE ISBN=“0-12-345678-9”

  27. Delete Statement (cont) • Delete all books published by “Small House” • DELETE FROM Books WHERE Books.pubID = (Subquery to return pubID for “Small House” from Publishers table)

  28. Delete Statement (cont) • Delete all books published by “Small House” • DELETE FROM Books WHERE Books.pubID = (SELECT Publishers.pubID FROM Publishers WHERE Publishers.pubName="Small House“ );

  29. Your Turn • Delete a book titled “Born in Maui” • Delete all books priced $50 or above • Delete all books published by “Small House” • Delete all books written by Shakespeare

  30. Delete all books priced $50 or above. DELETE FROM BooksWHERE bkPrice >= 50.00;

  31. Delete all books published by “Small House” DELETEFROM BooksWHERE Books.pubID IN (SELECT Publishers.pubID FROM Publishres WHERE Publishers.pubName=“Small House” );

  32. Delete all books written by Shakespeare DELETEFROM BooksWHERE Books.ISBN IN (SELECT BooksAuthors.ISBN FROM BooksAuthors WHERE BooksAuthors.auID = (SELECT Authors.auID FROM Authors WHERE Authors.auName = "Shakespeare“ ) );

  33. Used-Car Dealership DB(available from Resources/320/ on the server)

  34. Your Turn (dealership DB) • SELECT • List all cars that were made in 2000 or later. • Who was the salesperson that took care of Customer “John Wayne”? • Who was the salesperson that sold “Ford” “Edsel” in carlot at “Windward Cars”? • Update • Reduce price of all Honda cars by 30%. • Reduce the price of all non-Honda cars by 50%. • Mark as sold all cars which are located at “Windward Cars”

  35. Your Turn • Delete • Remove from Salespeople table an employee named “Linda” “Gingle”. • Remove from Cars table all cars which are located at “Windward Cars”. • Insert • Add a customer whose name is “Zeke” “Zorro” with a phone number of “808-123-4567”. • Add to Cars table the cars from the Newcars table.

  36. Solutions • SELECT • List all cars that were made in 2000 or later.SELECT *FROM carsWHERE year >= 2000; • Who was the salesperson that took care of Customer “John Wayne”?SELECT salespeople.firstName, salespeople.lastNameFROM salespeople, transactions, customersWHERE salespeople.salesID = transactions.salesIDAND transactions.custID = customers.custIDAND customers.nameFirst = “John”AND customers.nameLast = “Wayne”;

  37. Solutions • SELECT • Who was the salesperson that sold “Ford” “Edsel” in carlot at “Windward Cars”?SELECT salespeople.firstName, salespeople.lastNameFROM salespeople, transactions, carsWHERE salespeople.salesID = transactions.salesIDAND transactions.carID = cars.carIDAND cars.make = “Ford”AND cars.model = “Edsel”;

  38. Solutions • Update • Reduce price of all Honda cars by 30%.UPDATE cars SET carListPrice = carListPrice * 0.7 WHERE carMake = 'Honda'; • Reduce the price of all non-Honda cars by 50%.UPDATE Cars SET carListPrice = carListPrice * 0.5 WHERE carMake <> 'Honda';

  39. Solutions • Update • Mark as sold all cars which are located at “Windward Cars”UPDATE cars SET carSold = true WHERE cars.lotID IN (SELECT carlots.lotID FROM cars, carlots WHERE cars.lotID = carLots.lotID AND carlots.lotName = 'Windward Cars');

  40. Solutions • Delete • Remove from Salespeople table an employee named “Elmo” “Jones”.DELETE FROM salePeople WHERE salNameFirst = 'Elmo' AND salNameLast = 'Jones'; • Remove from Cars table all cars which are located at “Lemons Are Us”.DELETEFROM CarsWHERE Cars.lotID = (SELECT Carlots.lotID FROM Carlots WHERE Carlots.lotName = 'Lemons Are Us');

  41. Solutions • Insert • Add a customer whose name is “Zeke” “Zorro” with a phone number of “808-123-4567”.INSERT INTO Customers (nameFirst, nameLast, phoneNumber)VALUES (“Zeke”, “Zorro”, “808-123-457”); Add to Cars table the cars from the Newcars table.INSERT INTO Cars (carMake, carModel, carYear, carDoors, carListPrice,lotID) (SELECT make, model, year, doors, listPrice, lotID FROM newcars );

  42. SQL Statements • SELECT field1, field2FROM TableWHERE someCondtion; • SELECT Table1.field1, Table2.field1FROM Table1, Table2WHERE Table1.prKey = forKeyAND someCondtion;

  43. SQL Statements • UPDATE TableSET field1 = value1, field2 = value2WHERE someCondition • UPDATE TableSET field1 = value1WHERE (subquery)

  44. SQL Statements • INSERT INTO Table(field1, field2)VALUES (value1, value2);

  45. SQL Statements • DELETE FROM TableWHERE someCondition;

  46. Functions • Aggregate Functions—perform on set of data • Value Functions—perform on individual values

  47. Aggregate Functions • COUNT – returns count of rows • SELECT COUNT(auName)FROM Authors • AVG – returns average value of column • SELECT AVG(bkPrice)FROM BOOKS • MAX • MIN • SUM

  48. VALUE FUNCTIONS • String Value Functions • Numeric Value Functions • Datetime Value Functions

  49. String Value Functions • SUBSTRING(sourceString FROM start [FOR length])sourceString=“Ala Moana”SUBSTRING(sourceString FROM 5 FOR 3) -- “Moa” • UPPER(sourceString) -- “ALA MOANA”

  50. Numeric Value Function • POSITION (subString IN sourceString)sourceString = “Ala Moana”POSITION (“Moana” in sourceString) -- 5 • CHARACTER_LENGTH(sourceString)CHARACTER_LENGTH(“Ala Moana”) -- 9

More Related