CS105 Lab 5 SQL: joins, insertdelete

1
CS105 Lab 5SQL joins, insert/delete

• Announcements
• MP2 is due on Sunday, September 24th at 1130pm.
• Midterm 1 is on Monday, September 25th. (7pm,
  1hr long, rooms posted on website). If you need
  to take the conflict exam, request a conflict
  from the conflict request page absolutely no
  later than Friday, September 22nd.
• The review sessions for the midterm are on
  Sunday, September 24th (3 pm and 7pm in 141
  Wohlers)

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
• Dont enter a database name
• If you receive a Too many connections error,
  try connecting to the other server
• cs105-mysql2.cs.uiuc.edu

3
Objectives

• Learn about Primary Keys
• Use simple and advanced joins to look at data
  from multiple tables
• Add and delete records in SQL

4
Primary Key

• The Primary Key uniquely identifies a record.
• A good Primary Key should be something that is
  easy to make unique for each record.
• Social Security Number
• UIN
• ID Number
• The Primary Key can consist of any number of
  fields.
• A Primary Key cant be NULL.

5
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.

6
Joins - 1. The select line

• First, write the select clause with the fields
  you want to display.
• When you use multiple tables, you should specify
  the table name and field in the format of
  TableName.Field
• select players.LastName, players.FirstName,
  
• fielding.Position

7
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
• from players , fielding

8
Joins - 3. The Where line

• Since the position information is in the fielding
  table, the where clause will be
• where fielding.position CF

9
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 by players.lastName

We can see the name Jeff Abbot appears many, many
times. Why? Consider this example
10
Select LastName, FirstName, Position,
players.PlayerID, fielding.PlayerID From
players, fielding
players
fielding
11
Select LastName, FirstName, Position,
players.PlayerID, fielding.PlayerID From
players, fielding
The Resulting Result set
12
Select LastName, FirstName, Position,
players.PlayerID, fielding.PlayerID From
players, fielding Where players.PlayerID
fielding.PlayerID
Players
Fielding
13
Select LastName, FirstName, Position,
players.PlayerID, fielding.PlayerID From
players, fielding Where players.PlayerID
fielding.PlayerID
The Resulting Result set
14
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 where
  condition
• where fielding.position CF and
  fielding.playerID players.playerID
• Now only the rows that have the same playerID in
  both tables will be joined.

15
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 (fielding.position CF
• OR fielding.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.

16
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, well 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.

17
Advanced joins (continued)
We know something about the order_date and want
the prod_name.
18
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.
19
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

20
Adding records

• We are going to change databases now.
• Double-click on the Pets database in the left
  window.
• Choose the Pets 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 pets name, type, sex, weight,
  price, and birth date.
• Click on the save changes icon to finish.

21
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')

22
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