Standard Query Language (Data Definitions & Data Manipulation)


 

Objectives

  1. To learn Standard Query Language

    1. Data definition

    2. Data manipulation

Readings

From "Taylor AG.  SQL for dummies:  A reference for the rest of us"  read the following chapters:

  • Chapter 1: Introduction

  • Chapter 2:  SQL fundamentals

  • Chapter 3:  The components of SQL

  • Chapter 4:  Building and maintaining a simple database

  • Chapter 6:  Manipulating database data

  • Chapter 9:  Zeroing in on data you want

  • Chapter 10: Relational operators

  • Chapter 11:  Delving into nested queries

Presentations

There are several presentations for this lecture:

  1. Listen to lecture on SQL Data Definition (SWF file)

  2. Listen to lecture on SQL Data Manipulation lecture (SWF file)

  3. Download slides for data definition or for data manipulation

  4. Narrated slides will be available here within a few days

Narrated slides and videos require Flash.

What do you know?

  1. Create a table with two variables, call it Table1.  The first variable is the ID and it is an auto-number.  The second variable is LOS, the length of stay.  It is a double precision integer and has the values of 20, 30, 25, 15, 125, 0, 23, 20, 25, 25, and null.

  2. Write an SQL that would analyze data in above table and identify if any of the values are more than 3 standard deviations away from the mean of the length of stay.

  3. Write an SQL that would count the number of values above 20.

  4. Write an SQL to calculate the log of the table created in question 1.   Estimate the log of 0 as log of 0.001.  Note that the log of null values is not defined.

  5. What value does this SQL produce for the table in question 1:  SELECT Avg(Table1.LOS) AS AvgOfLOS FROM Table1 WHERE (((Table1.LOS)>25));

  6. What is an SQL command for selecting all values that are not 0 in Table made in question 1.

  7. Write an SQL to calculate the average of values in Table in question 1.

  8. Write an SQL that sorts the data in Table 1 in descending order of the ID field.

Analyze Data

  1. Examine the query you produced for answer to questions in lecture on complex queries.  List the SQL commands used in this query and for each listing explain in English what the command does:
     

    Command ending in semi-colon Explanation of
    command in English
       
       
       
       


     

  2. Create two tables in Access.  Each table should contain at least three records and two fields.  Write an SQL command that would combine the two tables using the Union command.   What is the result? 

If you are taking the course online, submit your responses as a word document attached to an email to your instructor, otherwise bring your work to class.  Keep a copy of all of your work till end of semester.   

 

 

More


This page is part of the course on Healthcare Databases, the lecture on SQL.  Copyright © 2005 Farrokh Alemi, Ph.D. Created on January 9th 2005. Most recent revision 10/21/2011