DUMMY
/********************* 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','1997-07-24', '7316 N. Durham St. Morgantown, WV 26508', 1) INSERT INTO DBO.PERSON (PersonID, PersonName, Sex, DateofBirth, Address, IsActive) VALUES (2, 'Palmer Hales (Billy)', 'Male','1951-07-21', '686 Myrtle Dr. Banning, CA 92220', 1) INSERT INTO DBO.PERSON (PersonID, PersonName, Sex, DateofBirth, Address, IsActive) VALUES (3, '(Lilly) Lilla Solano', 'F','1982-05-17', '354 Jockey Hollow Street Hoffman Estates, IL 60169', 1) INSERT INTO DBO.PERSON (PersonID, PersonName, Sex, DateofBirth, Address, IsActive) VALUES (4, 'Romeo Styles', 'Male','1949-06-02', '306 Beach Ave. Springfield, PA 19064', 1) INSERT INTO DBO.PERSON (PersonID, PersonName, Sex, DateofBirth, Address, IsActive) VALUES (5, 'Margot Steed ())', 'Female','1962-03-12', '62 South Peg Shop Street Terre 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 return 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' --------answer a-------------------- ---------answer 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 geol 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 arrival times of the first incoming p waves and s waves, b) the time difference (time delay) between the p wave and s wave arrival 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 arrival 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 arrival of the first p waves and also for the arrival of the first s waves (use hints on page 2 to help you). mark the arrival times on the seismogram (e.g., use a vertical line to show when the first p waves arrive, then do the same for the s waves). record the arrival 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 arrival 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 arrival time (seconds) s-wave arrival time (seconds) ∆tsp s-wave arrival time - p-wave arrival 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 '1/1/1961'="" )="" and="" p.isactive='1' --------answer="" a--------------------="" ---------answer="" 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="" geol="" 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="" arrival="" times="" of="" the="" first="" incoming="" p="" waves="" and="" s="" waves,="" b)="" the="" time="" difference="" (time="" delay)="" between="" the="" p="" wave="" and="" s="" wave="" arrival="" 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="" arrival="" 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="" arrival="" of="" the="" first="" p="" waves="" and="" also="" for="" the="" arrival="" of="" the="" first="" s="" waves="" (use="" hints="" on="" page="" 2="" to="" help="" you).="" mark="" the="" arrival="" times="" on="" the="" seismogram="" (e.g.,="" use="" a="" vertical="" line="" to="" show="" when="" the="" first="" p="" waves="" arrive,="" then="" do="" the="" same="" for="" the="" s="" waves).="" record="" the="" arrival="" 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="" arrival="" 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="" arrival="" time="" (seconds)="" s-wave="" arrival="" time="" (seconds)="" ∆tsp="" s-wave="" arrival="" time="" -="" p-wave="" arrival="" 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="">