[SOLVED] CES6000000001, CES6000000002, and CES6000000003 together, we can match it

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 < 1950Copyright By Assignmentchef assignmentchef

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 Dataii. We are now starting the process of building the data back into a relational database by linking together similar datag. 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 000000i. 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 Employmentvi. Female Employmentvii. Male Employmentviii. Average Hourly Wagesix. Average Weekly Hoursx. 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: assignmentchef QQ: 1823890830 Email: [email protected]