/*********************
This script creates a test database named "PersonDatabase". Please use this
test data to answer the SQL test questions. If you cant create this locally, connect to
the test data warehouse server and use the PersonDatabase.
Note: All data in this database has been randomly generated. All demographic and risk
information is fictional.
*********************
USE MASTER
GO
IF EXISTS
(
SELECT 1
FROM SYS.DATABASES
WHERE NAME = 'PersonDatabase'
)
DROP DATABASE PersonDatabase
GO
CREATE DATABASE PersonDatabase
GO
USE PersonDatabase
GO
CREATE TABLE dbo.Person
(
PersonID INT
, PersonName VARCHAR(255)
, Sex VARCHAR(10)
, DateofBirth DATETIME
, Address VARCHAR(255)
, IsActive INT
)
GO
CREATE TABLE dbo.Risk
(
PersonID VARCHAR(10)
, AttributedPayer VARCHAR(255)
, RiskScore DECIMAL(10,6)
, RiskLevel VARCHAR(10)
, RiskDateTime DATETIME
)
GO
--PERSON DATA
INSERT INTO DBO.PERSON (PersonID, PersonName, Sex, DateofBirth, Address, IsActive)
VALUES (1, 'Azra (Az) Magnus', 'Male',' XXXXXXXXXX', '7316 N. Durham St. Morgantown, WV 26508', 1)
INSERT INTO DBO.PERSON (PersonID, PersonName, Sex, DateofBirth, Address, IsActive)
VALUES (2, 'Palmer Hales (Billy)', 'Male',' XXXXXXXXXX', '686 Myrtle Dr. Banning, CA 92220', 1)
INSERT INTO DBO.PERSON (PersonID, PersonName, Sex, DateofBirth, Address, IsActive)
VALUES (3, '(Lilly) Lilla Solano', 'F',' XXXXXXXXXX', '354 Jockey Hollow Street Hoffman Estates, IL 60169', 1)
INSERT INTO DBO.PERSON (PersonID, PersonName, Sex, DateofBirth, Address, IsActive)
VALUES (4, 'Romeo Styles', 'Male',' XXXXXXXXXX', '306 Beach Ave. Springfield, PA 19064', 1)
INSERT INTO DBO.PERSON (PersonID, PersonName, Sex, DateofBirth, Address, IsActive)
VALUES (5, 'Margot Steed ())', 'Female',' XXXXXXXXXX', '62 South Peg Shop Street Te
e Haute, IN 47802', 1)
--RISK DATA
INSERT INTO DBO.Risk (PersonID, AttributedPayer, RiskScore, RiskLevel, RiskDateTime)
VALUES (1, 'Cigna',1.231, 'Bronze', getdate() - 3)
INSERT INTO DBO.Risk (PersonID, AttributedPayer, RiskScore, RiskLevel, RiskDateTime)
VALUES (1, 'Medicare',4.453, 'Gold', getdate() - 5)
INSERT INTO DBO.Risk (PersonID, AttributedPayer, RiskScore, RiskLevel, RiskDateTime)
VALUES (1, 'Cigna', 2.568, 'Silver', getdate())
INSERT INTO DBO.Risk (PersonID, AttributedPayer, RiskScore, RiskLevel, RiskDateTime)
VALUES (1, 'Anthem', 3.945, 'Bronze', getdate())
INSERT INTO DBO.Risk (PersonID, AttributedPayer, RiskScore, RiskLevel, RiskDateTime)
VALUES (2, 'Carefirst',12.51, 'Bronze', getdate() - 3)
INSERT INTO DBO.Risk (PersonID, AttributedPayer, RiskScore, RiskLevel, RiskDateTime)
VALUES (2, 'Carefirst', 50.201, 'Gold', getdate())
INSERT INTO DBO.Risk (PersonID, AttributedPayer, RiskScore, RiskLevel, RiskDateTime)
VALUES (4, 'Cigna', 1.231, 'Bronze', getdate())
INSERT INTO DBO.Risk (PersonID, AttributedPayer, RiskScore, RiskLevel, RiskDateTime)
VALUES (4, 'Anthem', 4.63, 'Silver', getdate())
INSERT INTO DBO.Risk (PersonID, AttributedPayer, RiskScore, RiskLevel, RiskDateTime)
VALUES (5, 'Medicare', .342, 'Bronze', getdate() - 3)
--USE PERSONDATABASE
*********************
Hello!
Please use the test data provided in the file 'PersonDatabase' to answer the following
questions. All answers should be written in SQL.
***********************
QUESTION 1
The table dbo.Risk contains calculated risk scores for the population in dbo.Person. Write a
query or group of queries that return the patient name, and their most recent risk level(s).
Any patients that dont have a risk level should also be included in the results.
**********************
**********************
QUESTION 2
The table dbo.Person contains basic demographic information. The source system users
input nicknames as strings inside parenthesis. Write a query or group of queries to
eturn the full name and nickname of each person. The nickname should contain only letters
or be blank if no nickname exists.
**********************
**********************
QUESTION 3
Building on the query in question 1, write a query that returns only one row per
patient for the most recent levels. Return a level for a patient so that for patients with
multiple levels Gold > Silver > Bronze
**********************
**********************
QUESTION 4
The following query returns patients older than 55 and their assigned risk level history.
A. What changes could be made to this query to improve optimization? Rewrite the query with
any improvements in the Answer A section below.
B. What changes would we need to make to run this query at any time to return patients over 55?
Rewrite the query with any required changes in Answer B section below.
**********************
SELECT *
FROM DBO.Person P
INNER JOIN DBO.Risk R
ON R.PersonID = P.PersonID
WHERE P.PersonID IN
(
SELECT personid
FROM Person
WHERE DATEOFBIRTH < '1/1/1961'
)
AND P.ISACTIVE = '1'
XXXXXXXXXXAnswer A--------------------
XXXXXXXXXXAnswer B--------------------
**********************
QUESTION 5
Create a function that accepts a first and last name and returns a PersonID from the Person table. If no
match is found, the function should return -1.
**********************
**********************
QUESTION 6
A. Looking at the script 'PersonDatabase', what change(s) to the tables could be made to improve the database structure?
B. What method(s) could we use to standardize the data allowed in dbo.Person (Sex) to only allow 'Male' or 'Female'?
C. Assuming these tables will grow very large, what other database tools/objects could we use to ensure they remain
efficient when queried?
**********************
Microsoft Word - In-Class_Earthquake_F13.docx
1
XXXXXXXXXXGEOL 1110 Assignment #4: Locate That Epicenter! (15 pts)
Name_________________________________________________
This assignment has 3 parts:
Part 1 (5 pts)
In Part 1, you will observe seismic waves for an earthquake as recorded on seismograms from 3
different seismograph stations. For each seismograph station, you will use the seismograms to
determine a) the a
ival times of the first incoming P waves and S waves,
) the time difference (time delay) between the P wave and S wave a
ival times, and c) the
distance to the earthquake epicenter from the seismograph station.
Part 2 (5 pts)
In Part 2, using your 3 distance estimates from each of the 3 seismograph stations, you will
locate the earthquake epicenter on a map.
You will need a string, pin, and pencil, OR a drafting compass to draw some arcs (see figs):
Part 3 (5 pts)
In Part 3, you will answer two questions related to your observations, estimates, and mapping.
2
Part 1: Observations and Measurements
1) P and S waves travel at different velocities
Modified from Mies, 2004
2) The difference in a
ival times between P and S waves (∆tsp) has a known relationship to distance
from the earthquake epicenter (thus, if you know ∆tsp, you can determine the distance to the epicenter).
Modified from Mies, 2004
3) Seismographs from at least 3 different locations (seismograph stations) are necessary
to accurately determine the epicenter location.
3
Part 1: Observations and Measurements
Name:
1. Observe the 3 seismograms on Page 4.
2. On one of the seismograms, determine the time (in seconds) for the a
ival of the first P waves and
also for the a
ival of the first S waves (use hints on Page 2 to help you). Mark the a
ival times on the
seismogram (e.g., use a vertical line to show when the first P waves a
ive, then do the same for the S
waves). Record the a
ival times on the chart below (in seconds - Note that the values might appear on
the seismogram in minutes). Note: scientists perform this and subsequent steps with a computer these
days, but you can obtain excellent estimates using this paper-and-pencil method!
3. Determine the time interval (∆tsp, in seconds) between the a
ival of S and P waves. Record this
value (in seconds) on the chart below. (Note: this needs to be in seconds)
4. Next, use the "Travel-Time Scale" (this ruler-like scale is provided on page 6) and the graph of
Travel Time versus Distance (Page 5) to estimate the distance (in km) to the earthquake epicenter from the
seismograph station. To do this, orient the Travel-Time Scale vertically (increasing numbers upward) and
align the "0" value on the Travel-Time Scale with the P- wave line on the graph. Move the scale along the P-
wave line until your recorded value of ∆tsp lines up with the S-wave line (see example on Page 2). The scale
will now line up with the estimated distance traveled on the x-axis (the distance from the seismograph
station to the epicenter). Record this value on the chart below.
5. Repeat steps 1-4 for each seismogram.
Seismograph
Station
ID
P-Wave
A
ival Time
(seconds)
S-Wave
A
ival Time
(seconds)
∆tsp
S-Wave A
ival
Time - P-Wave
A
ival Time
(seconds)
Distance
to
Epicenter
(km)
W12A
BBR
TIN
5
5
Part 1: Travel-Time vs Distance Graph Name:________________________
Travel time versus distance traveled (0 to 400 km on Earth's surface) for P and S waves.
6
CUT OUT RULER SCALE (Cut this out or fold over to use with Part 1, #4, explained above)
6
Part 2: Map of
Seismograph
Stations
Name:
1. Using information from only one seismograph station, you can determine the distance from
the station