1 / 21

CS105 Lab 2 - SQL Queries

CS105 Lab 2 - SQL Queries. Announcements: MP1 has been posted, and is due Saturday, September 8 at noon. You are NOT allowed to work in groups for MP1. Each person should should complete and submit their own MP1.

korene
Download Presentation

CS105 Lab 2 - SQL Queries

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 2 - SQL Queries • Announcements: • MP1 has been posted, and is due Saturday, September 8 at noon. You are NOT allowed to work in groups for MP1. Each person should should complete and submit their own MP1. • Instructions on downloading and installing SQLyog on your personal computer can be found at: http://www.cs.uiuc.edu/class/cs105/sqlyog.htm CS 105 - Fall 2007

  2. Objectives • Make basic queries to a database using SELECTstatements. • Filter your queries using the WHEREclause. • Advanced filters using And,Or& Not • Sort the results of your queries using the ORDER BY clause. • Learn to add comments that explain your code CS 105 - Fall 2007

  3. Connecting with SQLyog • Start SQLyog. (Start / Class Software / CS105 / SQLyog) • You should see login screen. • If a connection is not already defined, click “New” and name the new connection CS105. • Enter the following data into the login screen: • Hostname: • cs105-mysql1.cs.uiuc.edu • or cs105-mysql2.cs.uiuc.edu • User: cs105 • Password: cs105 • Leave the ‘Database(s)’ box blank. • Click “Connect!” CS 105 - Fall 2007

  4. Find the players table • Once logged in, click on the plus sign to the left of the Baseballdatabase, then click on the players table. CS 105 - Fall 2007

  5. Querying the database with SELECT • The SELECT statement is used to query the database. Select statements in this course have the following format: selectfieldName [, fieldName, …] fromtableName [whereconditionStatement] [order byfieldNames] CS 105 - Fall 2007

  6. Spelling and Clause Order • CAUTION: SQL queries are computer code. If you do not spell queries correctly, they will not work! • You also must order the clauses as specified. Always use the order shown on the previous slide. • MySQL is case insensitive, except for table names. CS 105 - Fall 2007

  7. A Simple Query • Suppose, your boss wants you to get a list of last names of all players that are in the database. • Recall your reading material. What do you think will be the solution? CS 105 - Fall 2007

  8. A Simple Query (Continued) • What the following query will give us? select lastname from players • Enter a query as follow: • Click on the Query tab • On line 1 type the query • Click on the Run (or F5) button • The results are called a result set CS 105 - Fall 2007

  9. Using multiple lines Also try these two queries: • Line 1:selectLASTNAMEfromplayers OR • Line 1:selectlastnameLine 2:fromplayers • You can and should use as many lines as you need to make your query easy to read. • Remember: MySQL is case insensitive, except for table names. CS 105 - Fall 2007

  10. Retrieving multiple fields • Now, your boss wants to know first names as well as last names of all players. • How you would modify your query? • Hint: You can use several field names in your query separated by coma. • The query should look like this: select firstname, lastname from players CS 105 - Fall 2007

  11. Retrieving all fields • Pretend you are an enterprising worker and you are curious what other fields are in the table. What should be the query now? • The answer is: select * from players CS 105 - Fall 2007

  12. Filtering the result set with WHERE • Assume, your boss wants to know which players have the first name Mike? Line 1: select * Line 2:from players Line 3: where firstname = “Mike” • Note: Strings in MySQL can be surrounded by either single quotes (‘) or double quotes (“) • How has the result set changed? CS 105 - Fall 2007

  13. More advanced filters • Filters can be made more powerful by the use of And, Or & Not. These logical operators allow us to combine several conditions when filtering queries. What team does Mike Cameron play for? • How do you tell the computer that you don’t want just players whose first name is Mike, rather you want the player whose first name is Mike and last name Cameron? select team from players where firstname = “Mike” and lastname = “Cameron” CS 105 - Fall 2007

  14. Using NOT in Query Filters • If you wanted to select all players except those named Mike in the players table, this is a good time to use NOT. select* fromplayers where not(firstname = “Mike”) • The parentheses are very important here. CS 105 - Fall 2007

  15. About AND/OR/NOT • You must always use AND/OR/NOT with conditions, i.e., statements which are either True or False. • Let’s say we wanted to retrieve all the players whose first name is either Mike or Jeff. wherefirstname = “Mike” or “Jeff” • This does not work. Why? • How do we fix it? CS 105 - Fall 2007

  16. Sorting the result set with ORDER BY • To sort the result set by team name: Line 1: select * from players Line 2:order by team • Can you explain the result? • How do you sort it in descending order? CS 105 - Fall 2007

  17. Sorting the result set with ORDER BY • The players are already sorted by last name, suppose we want them sorted by first name instead. Change line 2 to: order by team, firstname How is the result set organized now? • What happens if you change it to: order by firstname, team • Can you explain the result has changed? CS 105 - Fall 2007

  18. Sorting in reverse order with DESC • Change line two of the previous slide to: order by team, firstname desc How is the result set sorted now? • Now change lines two to:order by team desc, firstname desc Now how is it sorted? • What conclusion we can make from two given examples? CS 105 - Fall 2007

  19. Exercises • Write a SELECT statement that returns the first and last names of all the Sox players sorted by last name in ascending order. • Write a SELECT statement that returns players from the Sox before the players from the Cubs but with their first names sorted in ascending order. CS 105 - Fall 2007

  20. Comments are text in a program (or SQL query) meant for other humans to read. They usually contain information like what the programmer is doing, who wrote that piece of code, and when it was last changed. Comments are ignored by the computer!!! SQL comments are included between /* and */ /*retrieve the first name of all players*/ selectfirstnamefromplayers Comments (remarks) CS 105 - Fall 2007

  21. Comments for MPs • It is very important to have comments in your own words in the MPs. Do not copy comments from the MP or from friends! • For MPs always put the comments BEFORE your SQL statement or result set. CS 105 - Fall 2007

More Related