CS105 Lab 4 SQL: joins, insert/delete

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

CS105 Lab 4 SQL: joins, insert/delete