1. Add CLUSTERED / regular INDEX to each table as appropriate. Each table should have at least one CLUSTERED Index. These should be executed in your wrk. Schema.
2. Create partitions that segment the ceAllData by 20 year periods from 1940 to 2020. Store your new ceAllData table into a new schema called par that has the partitions properly executed.
a. Execute a showplan and compare your wrk.ceAllData to your par.ceAllData with some basic select count(*) where queries when the year is
i. Year < 1950
Copyright By PowCoder代写 加微信 powcoder
ii. Year > 2010
iii. Year = 1980
iv. Year in (1980, 1990, 2000, 2010, 2020)
v. Year between 1995 and 2005
vi. Year between 2005 and 2020
b. What do you observe about the performance when the queries are executed on the wrk version vs. the par version of the data depending on the time period of the query.
3. Create a new database call TMP
c. Write a dynamic SQL loop to copy all of your data from your wrk schema into your TMP database
d. Write a dynamic SQL loop to print to screen the first 10 rows of all of your DBO tables
4. In case you have not noticed, but the series IDs are related to each by data type, industry, and supersector. We are going to link the series together in order to create powerful insight into the data. For example, data_type equal to “01” is total employment, “02” is average weekly hours, and “03” is average hourly earnings. If we link “01,” “02,” and “03” together and do some simple math, we can estimate the total payroll labor earnings for an entire industry or the entire economy!
e. Parse the Series ID to break it down into pieces, then match those pieces to each other, but adjust the “Data Type” (the last two digits of the SeriesID) to ensure the match
f. For example, if we wanted to match CES6000000001, CES6000000002, and CES6000000003 together, we can match it by using CE, S<-Seasonal, 60000000 <- Industry Code, year, and period.
i. Recall that these series are stacked together in the All Series Data
ii. We are now starting the process of building the data back into a relational database by linking together similar data
g. Using Dynamic SQL, create separate hard tables in your database that create analysis heaps that have the following Columns. Each table will be a series of similar data that you will combine. You will keep monthly records and drop annualized records. You will keep seasonally adjusted records. There should be 20 tables in total, one for each main supersector type. Use when the last six digits of Industry is equal to ‘000000’
i. Parsed Series ID (hint, the SeriesID you created to link together the different series.
ii. Parsed Series_Title (hint, not the exact series title. You are going to create a new one that is generalized for the supersector)
v. Total Employment
vi. Female Employment
vii. Male Employment
viii. Average Hourly Wages
ix. Average Weekly Hours
x. Average Overtime hours
· Question #5
Now take the tables in 4 and build a dynamic SQL query that will process each table and PIVOT it so that you have total employment, female employment, male employment, average hourly wages, average weekly hours average overtime hours on the left-hand side and year across the top. You will want to average each of the fields and remove monthly detail in your pivots.
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com