Topic 5C – Database Operations

Learning Objectives

By the end of this topic, you should be able to:

• Identify the purpose of the SELECT, FROM, and WHERE clauses in a SQL query.
• Given a SQL query operating on a described database, state in plain English the question the query is answering.
• Identify the JOIN condition in a multi-relation SQL query and explain what it does.
• Distinguish between JOIN conditions and filter conditions in the WHERE clause of a SQL query.
• Explain how SQL querying connects to Stage 4 (analyze and explore) of the data investigation cycle.

Learning Activities

To help you meet the learning objectives, we have prepared a combination of readings, activities, and videos.

Course Readings

These reading were designed to introduce the course topics to an audience of educators. They should be considered "required" and read in order.

• Reading 1 – Selecting and Filtering — the SELECT, FROM, and WHERE clauses and how to read single-relation queries
• Reading 2 – Joining Relations — combining multiple relations with JOIN to answer questions that span tables
• Reading 3 – Reading Queries in Practice — extended practice with a new database scenario and a closing connection to the investigation cycle

Lesson Videos

These videos support the readings above and may present the material with some deeper connections and worked examples.

• VIDEO: Database Queries in Action

Activity

The material in this unit is much more understandable if you take some time to interact with a simple database and write some basic queries for yourself.  The w3schools website has a nice tool that allows you to interact with a SQL database right in your web browser.   

• Writing Database Queries

Checking for Understanding, Questions

The questions below use the following database. Read through the relations carefully before answering.

Reading SQL Queries

1. What question does this query answer?

   SELECT  LastName, FirstName
   FROM    Provider
   WHERE   Specialty = 'Pediatrics'

2. What question does this query answer?

   SELECT  Patient.LastName, Patient.FirstName
   FROM    Patient, Appointment
   WHERE   Patient.PatientID = Appointment.PatientID
           AND Appointment.ProviderID = 'D-10'

3. What question does this query answer?

   SELECT  Provider.LastName, Provider.FirstName, Provider.Specialty
   FROM    Patient, Appointment, Provider
   WHERE   Patient.PatientID = Appointment.PatientID
           AND Appointment.ProviderID = Provider.ProviderID
           AND Patient.City = 'Cedar Falls'

4. In question 3's query, identify the two JOIN conditions in the WHERE clause and the one filter condition. Explain what each JOIN condition does.
5. A hospital administrator wants to know the names of all patients who had a sick visit appointment. She is handed this query:

   SELECT  LastName, FirstName
   FROM    Patient
   WHERE   Reason = 'Sick visit'

   This query will not return the correct results. Explain why not, and describe what change would be needed to fix it.

Checking for Understanding, Answers

You can compare your answers to the following answer key.

Extend Your Learning