Querying a Clintrial Database: An introduction to SQL for non-programmers

Introduction to SQL:
Querying a Clintrial Database
Exercise Manual
Toby Odenheim

Introduction to SQL: Querying a Clintrial Database

Page i of 16

Version 1 – January, 2008

Introduction to SQL: Querying a Clintrial Database

Page ii of 16

Contents
1

Lesson 1 Exercises: Basic SQL Statements

5

2

Lesson 2 Exercises: Restricting and Sorting Data

7

3

Lesson 3 Exercises: Single Row Functions

8

4

Lesson 4 Exercises: Joining Tables

11

5

Lesson 5 Exercises: Group Functions:

12

6

Lesson 6 Exercises: Subqueries

13

7

Appendix: Solutions

14

Introduction to SQL: Querying a Clintrial Database

Page iii of 16

Note: All exercises will use Medika_Clinical unless otherwise stated.

1 Lesson 1 Exercises: Basic SQL Statements

1.1

Write a statement to describe all the data in the vital signs table.

What is the name of the item for Systolic Blood Pressure? ____________________
What is its datatype? ____________________
What is its length? ____________________

1.2

Write a statement to display all the data in the demographics table.

1.3

Write a statement to display the subject number, race and gender for each subject.

1.4

Write a statement to display the unique values for race.

What are the values for Race? ____________________
How could we determine the actual race description? ____________________

Introduction to SQL: Querying a Clintrial Database

Page 5 of 16

1.5

CHALLENGE EXERCISE: Write a query to produce the following output:

Introduction to SQL: Querying a Clintrial Database

Page 6 of 16

2 Lesson 2 Exercises: Restricting and Sorting Data

2.1

Display subject and gender where for all males.

2.2

Display subject and systolic blood pressure for all subjects with a systolic blood pressure over 110 but less than 130.

2.3

Display subject and systolic blood pressure for all subjects with a systolic blood pressure that is less