New Perspectives Classic Labs

SQL QUERIES

To query many relational databases, you use SQL (usually pronounced by saying the letters of the acronym, "S Q L"). IBM developed SQL in the mid 1970s for use in mainframe relational database products such as DB2. In 1986 the American National Standards Institute (ANSI) adopted SQL as the standard relational database language and it is now used extensively on microcomputer databases as well. Understanding how to use SQL is an important skill for many data management jobs. In this Lab, you will get a taste of this powerful and flexible database language. To gain further expertise, you should refer to the course offerings at your school.  

1.  Click the Steps button to learn how to formulate SQL queries. As you proceed through the steps, answer the Quick Check questions. After you complete the Steps, you will see a Quick Check Summary Report. Follow the instructions on the screen to print this report.  

2.  In Explore, use the scroll bar to browse through the database to find the answers to the following questions:

a. What are the names of the staff physicians? (Hint: The JobCode for staff physicians is SMD.)

b. How many LPNs are in the database?

c. Who makes more than $20 an hour?

d. What is Ralph Smith's job?

e. When was Tony Jackson hired?

3.  In Explore, try the following queries and indicate if each accomplishes the result listed:

a. QUERY: SELECT * FROM Employee order by HourlywageRESULT: Displays employees beginning with the person who is the lowest paid 

b. QUERY: SELECT * FROM Employee where Gender = 'F' AND Jobcode = 'SMD'RESULT: Displays all the female staff physicians

c. QUERY: SELECT * FROM Employee where LastName between 'C' and 'M'RESULT: Displays all the employees with last names that begin with a C, D, E, F, G, or H

d. QUERY: SELECT * FROM Employee where Gender = 'M' or Hourlywage > 20. 00RESULT: Displays only the men who make more than $20 an hour

e. QUERY: SELECT * FROM Employee where DeptCode <> 'OB' order by BirthDateRESULT: Displays all the obstetricians' birthdays

4.  In Explore, suppose that the database contains thousands of records and it is not practical to browse through all of the records using the scroll bar. Write down the queries you use to do the following:

a. Find the record for Angela Peterson.  

b. Find the records for all the RNs.  

c. Find all the employees who work in Intensive Care (IC).  

d. Find all the employees who make less than $15 an hour.  

e. Find all the employees who do not work in obstetrics (OB).  

5.  In Explore, suppose that the database contains thousands of records and it is not practical to browse through all of the records using the scroll bar. Write down the queries you use to do the following:

a. Find the name of the oldest employee.  

b. Find the departments that have female employees.  

c. Find out how many employees work in Intensive Care.  

d. Find the last names of the female employees who make between $10 and $15 per hour.  

e. Get an alphabetized list of male employees who work as Rns or LPNs.  

6.  Circle the errors in the following SQL queries:

a. SELECT * FROM Employee where DeptCode <> OB order by BirthDate

b. SELECT * FROM Employee where Wages between 10 and 50

c. SELECT * FROM Employee where order = LastName 

d. SELECT * FROM Employee where DeptCode <> 'OB' and Hourly Wage > '$10.00' order by LastName

e. SELECT * FROM Employee where FirstName like T*