Learning Objectives:

Learn to use the WHERE clause in SQL SELECT statements

Database Setup and Practice

If the dabase TOYSTORE does not exist in your MySQL yet, please first download and run the script file named ToyStore DB Setup.sql, which is available from the same page of this instruction file on Blackboard. After running the script, double check to see if the database named TOYSTORE appears as the default database in you MySQL workbench.

Then, try some or all the example query statements used in the lecturers in-class presentation slides.

If confident with the example statements used in class, finish the exercise questions below.

Exercise

Write SQL SELECT statements to query the TOYSTORE database for the following questions. Answer the exercise questions on Blackboard (CourseMaterials\Labs\Lab03\Exercise) based on query results. Note that you can submit your answers at maximum THREE TIMES. After your first submission, you will know which questions you get wrong (but you will not know the correct answers.) Then, you will have a second and a third chance to submit, but you will have to start over and re-submit ALL the questions, not just the ones you got wrong. Please also note that the order of questions and choices might change in your later attempts. The exercise is due on Blackbaord by 11:59pm of September 28 (Friday), but normally you should be able to complete it in lab.

Using the CUSTOMERS table, write SELECT statements to perform the following queries

Q1: Write an SQL statement to list the ID, name, and address for all the customers whose names are Fun4All

Q2: Write an SQL statement to all the information of the customers whose city is Detroit or Chicago

Q3: Write an SQL statement to list the ID, name, address, and zipcode for all customers whose zipcodes start with the number 4

Q4: Write an SQL statement to list the ID, name, address, and zipcode for all customers whose addresses include the words Drive or Lane

Q5: Write an SQL statement to list the ID, name, contact person, and email for all customers whose email address is NOT missing

Using the VENDORS table, write SELECT statements to perform the following queries

Q6: Write an SQL statement to list the ID and name for all the vendors who are NOT located in USA

Q7: Write an SQL statement to list all the information for all the vendors whose names include bear

Q8: Write an SQL statement to list all the ID, name, and address for the vendors whose ID is BRS01,DLL01, FRB01, or JTS01

Q9: Write an SQL statement to list all the ID, name, zipcode, and country of all the vendors who are located in USA and the second digit of whose zipcodes is the number 4. Sort the results by vendor ID in the descending order

Q10: Write an SQL statement to list all the ID, name, zipcode, and country of all the vendors who are located in either France or USA but whose zip codes do not start with the number 4. Sort the results by vendor name in the descending order