1 / 21

CS105 Lab 4 SQL: joins, insert/delete

CS105 Lab 4 SQL: joins, insert/delete. Announcements: MP2 is posted and is due on Saturday, February 18 th at noon.

Download Presentation

CS105 Lab 4 SQL: joins, insert/delete

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. CS105 Lab 4SQL: joins, insert/delete • Announcements: • MP2 is posted and is due on Saturday, February 18th at noon. • Midterm 1 is on Tuesday, February 21st, at 7pm (1hr long). If you need to take the conflict exam, you must go to the conflict page on the course website and request a conflict using the webform. Conflict requests must be done by Saturday, February 18th at noon. CS105 - Spring 2006

  2. Start up SQLyog • Start SQLyog: Start / Class Software / CS105/ SQLyog • Fields to fill in: • Hostname/IP: cs105-mysql1.cs.uiuc.edu • User: cs105 • Password: cs105 • Don’t enter a database name • If you receive a “Too many connections” error, try connecting to the other server: cs105-mysql2.cs.uiuc.edu CS105 - Spring 2006

  3. Objectives • Use simple and advanced joins to look at data from multiple tables • Add and delete records in SQL CS105 - Spring 2006

  4. Joins Getting data from Multiple Tables • We want to ask the database for all the players who play Center Field. • We want to display ONLY three fields in the dynaset: Firstname, Lastname, and Position • This data is in the players table. • BUT the fielding table contains the information about field position for the players. CS105 - Spring 2006

  5. Joins - 1. The select line • First, write the selectclause with the fields you want to display. • When you use multiple tables, you should specify the tablename and field in the format of TableName.Field select players.LastName, players.FirstName, fielding.Position CS105 - Spring 2006

  6. Joins - 2. The from line • Next, we want to add the from clause to tell the query where to get the information. • All of the display information is in players, but the field position is in the fielding table fromplayers , fielding CS105 - Spring 2006

  7. Joins - 3. The Where line • Since the position information is in the fielding table, the where clause will be: wherefielding.position = “CF” CS105 - Spring 2006

  8. Joins - 4. What happens? • Try running the query and see what you get. • How many records are returned? Does this seem right? (Hint: there are 87 records in players and 121 in fielding) • To see what happened add the clause below and run the query again. order byplayers.lastName We can see the name Jeff Abbot appears many, many times. Why? Consider this example… CS105 - Spring 2006

  9. SelectLastName, FirstName, Position, players.PlayerID, fielding.PlayerIDFromplayers, fielding players fielding

  10. SelectLastName, FirstName, Position, players.PlayerID, fielding.PlayerIDFromplayers, fielding The Resulting Result set:

  11. SelectLastName, FirstName, Position, players.PlayerID, fielding.PlayerIDFromplayers, fielding Where players.PlayerID = fielding.PlayerID Players Fielding

  12. SelectLastName, FirstName, Position, players.PlayerID, fielding.PlayerIDFromplayers, fielding Where players.PlayerID = fielding.PlayerID The Resulting Result set:

  13. Joins - 5. Corrections • Just as in the example, each row in the players table is joined with each row in the fielding table. • To fix this, we need to add to our wherecondition: wherefielding.position = “CF”andfielding.playerID = players.playerID • Now only the rows that have the same playerID in both tables will be joined. CS105 - Spring 2006

  14. Using OR in query filters • What if we wanted to find all the players who play center field or right field? • Just like mathematical operations, AND, OR & NOT are evaluated in a certain order (first NOT, then AND, last OR ). Since we need OR evaluated before AND, we use parentheses: WHERE (position = “CF” OR position = “RF”) AND fielding.playerID = players.playerID • Note: It is easy to make mistakes when using complicated filters with And, Or & Not. Use parentheses where possible. CS105 - Spring 2006

  15. Advanced joins • Sometimes you want to join more than two tables to get the data you need. Example: • From the TeachYourself database, list all product names that were purchased during the month of January. • To find dates in January, we’ll use the MONTH function, which returns a number between 1 and 12. • The data you need is found in 3 different tables. They can be connected based on their primary keys, like the order number and product id. CS105 - Spring 2006

  16. Advanced joins (continued) We know something about the order_date and want the prod_name. CS105 - Spring 2006

  17. Advanced joins (continued) We can connect the two tables through the OrderItems table which has fields in common with both the Orders and the Products tables. CS105 - Spring 2006

  18. Advanced joins (continued) SELECT Orders.cust_id,Products.prod_name FROM Orders, OrderItems, Products WHERE MONTH(Orders.order_date) = 1 AND Orders.order_num = OrderItems.order_num AND OrderItems.prod_id = Products.prod_id CS105 - Spring 2006

  19. Adding records • We are going to change databases now. • Double-click on the Pets database in the left window. • Choose thePets table. • Choose the Table Data view tab. • Scroll down to the last entry. The blank entry at the bottom is where we will add our new record. • Type in your pet’s name, type, sex, weight, price, and birth date. • Click on the save changes icon to finish. CS105 - Spring 2006

  20. SQL: Insert into • Look at the SQL line that is run; it is in the History tab. • You should see something like the line below INSERT INTO Pets (`Name`,`Type`, `Sex`, `Weight`, `Price`, `BirthDate`) VALUES ('Fido', 'Dog', 'M', 12, 10, '1993-06-16') CS105 - Spring 2006

  21. Deleting records • Now, select your record in the table, and click on the delete selected rows icon. • You will get a message asking if you want to delete the record. Click OK. • Look at the SQL code in the History tab. You should see something similar to this: DELETE FROM `Pets` WHERE Name = “SQueeLy” CS105 - Spring 2006

More Related