To learn Standard Query Language
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
There are several presentations for this lecture:
Narrated slides will be available here within a few days
Narrated slides and videos require Flash.
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.
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.
Write an SQL that would count the number of values above 20.
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.
What value does this SQL produce for the table in question 1: SELECT Avg(Table1.LOS) AS AvgOfLOS FROM Table1 WHERE (((Table1.LOS)>25));
What is an SQL command for selecting all values that are not 0 in Table made in question 1.
Write an SQL to calculate the average of values in Table in question 1.
Write an SQL that sorts the data in Table 1 in descending
order of the ID field.
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||
command in English
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.