1 / 22

CS105 Lab 3 SQL Queries

CS105 Lab 3 SQL Queries. Announcements: Online Quiz 2 is up and is due on 8:00 pm , Tuesday, September 12 th . MP2 will be posted soon. You can work in groups of up to three people for MP2. Only one person per group should submit the MP. To install SQLyog on your home computer:

lyris
Download Presentation

CS105 Lab 3 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 3SQL Queries • Announcements: • Online Quiz 2 is up and is due on 8:00pm, Tuesday, September 12th. • MP2 will be posted soon. You can work in groups of up to three people for MP2. Only one person per group should submit the MP. • To install SQLyog on your home computer: http://www.cs.uiuc.edu/class/cs105/sqlyog.htm • If you need to take a conflict exam for Midterm 1, make sure you request a conflict ASAP. You can reach the conflict signup system from the course website. CS 105 – Fall 2006

  2. Objectives • Understand the elements of a database. • 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 use comments that explain your code CS 105 – Fall 2006

  3. Elements of a Database • Table: A database consists of one or more tables. Tables are used to group data in an organized manner. For example, all players on a baseball team might be listed in a database table. • Record: A table consists of records (or rows). In a baseball team table, each record might correspond to a player. • Field: A record consists of fields. Fields store information about the records. Example fields in a baseball player’s record: first name, last name, and batting average. CS 105 – Fall 2006

  4. One table Three Records Two Fields First Name Last Name Alex Rodriguez Sammy Sosa Manny Ramirez An Example Table Players: CS 105 – Fall 2006

  5. Database Software • MySQL is a server program. It manages databases on a server computer and serves client programs with the information we request. MySQL is called a database management system (DBMS). • SQLyog is a client program. It is used to query the database. This is the program we will use on the computers in lab. CS 105 – Fall 2006

  6. Connecting with SQLyog • Start SQLyog. (Start / Class Software / C S 105 / SQLyog???) • You should see login screen. • 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 2006

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

  8. Now let’s query the database • The SELECT statement is used to query the database. All Select statements in this course have this format: Select … From … [Where …] [Order by …] CS 105 – Fall 2006

  9. Spelling and order of queries • 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. CS 105 – Fall 2006

  10. A simple query: Get all players’ last names • Click on the Query tab. • On line 1, type:Select lastname from players • Click on the Run (F5) button. • The results are called a result set. • Delete your query so we can write another. CS 105 – Fall 2006

  11. More queries to try:Case sensitivity, using multiple lines Try these two queries: • Line 1:Select LASTNAMEfrom players • Line 1:Select lastnameLine 2:from players • You can and should use as many lines as you need to make your query easy to read. • MySQL is case insensitive, except for table names. CS 105 – Fall 2006

  12. Retrieving multiple fields or all fields • Now try retrieving two of the fields: • Line 1: Select firstname, lastname • Line 2: from players This displays the players’ first and last names. • You can get all fields by using * • Line 1: Select * from players This displays all fields in the players table. CS 105 – Fall 2006

  13. Filtering the result set with WHERE • Suppose we want to know which players have the first name Mike? Line 1: Select * from players Line 2: 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 2006

  14. 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? wherefirstname = “Mike”AND lastname = “Cameron” CS 105 – Fall 2006

  15. Using AND/OR in query filters • cond1AND cond2AND requires that both conditions are satisfied • cond1ORcond2OR requires that at least one of the conditions is satisfied CS 105 – Fall 2006

  16. NOT • NOT(cond1)NOT requires that the condition is not satisfied • If you wanted to select all players except Mikes in the players table, this is a good time to use Not. select*fromplayers whereNot(firstname = “Mike”) • The parentheses are very important here. CS 105 – Fall 2006

  17. About AND/OR/NOT • You must always use AND/OR/NOT with conditions, aka 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”would not work. (Why?) • How do we fix it? CS 105 – Fall 2006

  18. Sorting the result set with ORDER BY • To sort the result set by team name: Line 1: Select * from playersLine 2:order by team • 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 CS 105 – Fall 2006

  19. Sorting the result set 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 would we write if we wanted to see players from the Sox before the players from the Cubs but with their first names sorted in ascending order? CS 105 – Fall 2006

  20. Exercise • Write a SELECT statement that returns the first and last names of all the Sox players sorted by last name in ascending order CS 105 – Fall 2006

  21. 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. Such text is ignored by the computer!!! In SQL, each comment is included between the /* and */ signs /*retrieve the first name of all players*/ Select firstname from players Comments (remarks) CS 105 – Fall 2006

  22. Comments for MP2 • 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 MP2 always put the comments on the TOP of your SQL statement. CS 105 – Fall 2006

More Related