m
Chapter 8 Lab This Lab sheet is worth 100 points for complete queries. Only fully completed and documented solutions will receive maximum points. The queries are due per the timeline in Moodle. The queries in this homework assignment will use the Northwind database. Create an SQL query to solve the problem. Don’t forget to include all of the following. 1. The database used “USE” 2. Comment with the assignment name, query number, your name and date. Also include the answers to any questions to the comments. 3. The SQL code, in a readable, programmatic format. 4. Add a brief comment for each of the main segments, what the operation will accomplish 5. The output of the messages tab, to include the full “Completion time” or “Total Execution Time” line. 6. First 5 lines of the “Results” output. Points will be deducted if missing, more than 5, or using the TOP command. Follow the instructions in the Lab Lecture, complete the following query. Write a select statement that returns five columns based on the calculating the product of the “UnitPrice” and “UnitsInStock” attributes within the ‘products’ table. Use a descriptive column alias for all output a. Use the CAST function to return the first column with 0 digits to the right of the decimal point. b. Use the CAST function to return the second column with a $ in front of the value. c. Using the CONVERT function to return the third column with the smallest precision in mind to return the scale of 3. d. Use the CONVERT function to return the fourth column with a $ on both sides of the outputted value. There should be no spaces between the symbols and number. i.e $3,813.33$ e. For this column, using the convert function, grab the first number of the product calculation, the last number of the product calculation. Put a ‘(‘ in front, a ‘)’ on the end, and a ‘#” in between the numbers of the output. i.e. (7#0) One row example without the descriptive headers you will be adding: 3813 $3813.33 3813.330 $3,813.33$ (7#0) Chapter 9 Lab This Lab sheet is worth 100 points for complete queries. Only fully completed and documented solutions will receive maximum points. The queries are due per the timeline in Moodle. The queries in this homework assignment will use the GuitarShop database. Create an SQL query to solve the problem. Don’t forget to include all of the following. 1. The database used “USE” 2. Comment with the assignment name, query number, your name and date. Also include the answers to any questions to the comments. 3. The SQL code, in a readable, programmatic format. 4. Add a brief comment for each of the main segments, what the operation will accomplish. 5. Full output of the results tab for both queries, which includes the messages line when outputting to text. In a previous chapter, we used the grouping functions with a rollup and cube. The output showed the word “NULL” for the rollup. When information like this is in a report, the readers don’t know what it means. Readable text should be in a good report. In this lab, we will create a grouping function and replace the “NULL” with understandable text. Query 1 – create a report from the following fields and group with cube. using the following items. The attributes a) State b) City c) Calculated field “Item price * Quantity”, aliases as “InvoiceSum” The tables: a) Address b) Customers c) Orders d) Orderitems More Criteria a) Limit the “InvoiceSum” to greater than 5000 Question: what is the difference in the output if you add the following line to filter? AND NOT State = ‘CA’ Chapter 9 Lab Query 2 – now let’s change the “NULL” in the output to something more readable. We will label the “State” items '***State Total***' and the City Output as '***City Total***' The output would look better if you change the query results to Text. The “
t “ or Menu item “Query” >> “Results to” >> “Results to Text” Change the select statement to the following: SELECT CASE WHEN GROUPING(City) = 1 THEN '***City Total***' ELSE City END AS City, CASE WHEN GROUPING(State) = 1 THEN '***State Total***' ELSE State END AS State, SUM(ItemPrice * Quantity) AS InvoiceSum The remaining lines in the statement would be the same. Question: what is the difference in the results between query 1 and 2? Create Tables and insert data This Lab sheet is worth 100 points for complete queries. Only fully completed and documented solutions will receive maximum points. The queries are due per the timeline in Moodle. The queries in this homework assignment will use the Northwind database. Create the queries per the lab. Run each created query and submit the usual items. 1. The database used “USE” 2. Comment with the assignment name, query number, your name and date. Also include the answers to any questions to the comments. 3. The SQL code, in a readable, programmatic format. 4. Add a brief comment for each of the main segments, what the operation will accomplish 5. The output of the messages tab, to include the full “Completion time” or “Total Execution Time” line. First 5 lines of the “Results” Using either the SQL Server Management Studio, or Azure Data Studio, complete the following tasks, in order. You will be able to see how scripts work, inserting data, see how views, procedures, and triggers work. Completing this lab will help in understanding a portion of project 2. Please submit the following. Best to zip all files into one, then upload. 1. Submit all of your queries (step 2 and 3) 2. The output and time stamps from the messages table 3. Create the queries, run, then show the data has been added to the three tables. a. Don’t forget about the proper format of the queries, and the first 5 lines of the results/messages. Please note: Please change all of the “USE” statements below to reflect the use of your initials instead of my initials: CIS111Asset Create Tables and insert data STEP1, Create the database CREATE DATABASE CIS111AssetRRJ GO STEP 2, create the tables (this is all one script) USE CIS111AssetRRJ /* drop the tables */ IF OBJECT_ID('Employee') IS NOT NULL DROP TABLE Employee IF OBJECT_ID('Asset') IS NOT NULL DROP TABLE Asset IF OBJECT_ID('Agency') IS NOT NULL DROP TABLE Agency GO USE CIS111AssetRRJ /* create the agency table */ CREATE TABLE Agency (AgencyID INT NOT NULL IDENTITY PRIMARY KEY, AgencyName VARCHAR(30) NOT NULL, City VARCHAR(30) NOT NULL, State CHAR(2) NOT NULL) GO use CIS111AssetRRJ /* Create the employee table */ CREATE TABLE Employee (EmployeeID INT NOT NULL IDENTITY PRIMARY KEY, EmpFirstName VARCHAR(50) NOT NULL, EmpLastName VARCHAR(50) NOT NULL, DateOfBirth DATETIME NOT NULL, PhoneNumber VARCHAR(14), ReportTo INT NOT NULL, Pay MONEY NOT NULL, PayType CHAR(1) NOT NULL, CHECK (PayType IN ('Y', 'H', 'y', 'h')), AgencyID INT NOT NULL REFERENCES Agency(AgencyID)) GO USE CIS111AssetRRJ /* Create the Assets table */ CREATE TABLE Asset (AssetID INT NOT NULL IDENTITY PRIMARY KEY, PurchaseDate DATETIME NOT NULL, Description VARCHAR(50) NOT NULL, PurchaseAmount Money NOT NULL, SerialNo VARCHAR(14) NULL, AgencyID INT NOT NULL REFERENCES Agency(AgencyID)) --GO not necessary, as this is the last statement in the batch -- run this query Create Tables and insert data STEP 3, Add the data Please open a new query window and insert the following statements. USE CIS111AssetRRJ /* add data to Agency table */ INSERT INTO Agency (AgencyName, City, State) VALUES ('One', 'Dearborn', 'MI'), ('Two', 'Southfield', 'MI'), ('Three', 'Royal Oak', 'MI') GO USE CIS111AssetRRJ /* add data to Employees table */ INSERT INTO Employee (EmpFirstName, EmpLastName, DateOfBirth, PhoneNumber, ReportTo, Pay, PayType, AgencyID) VALUES ('Bob', 'James', '1965/10/08', '3138456356', 1, 250000, 'y', 1 ), ('Fred', 'Sanford', '1960/12/10', '3138456357', 1, 75000, 'y', 1 ), ('Amy', 'Jones', '1988/08/30', '3138456358', 1, 75000, 'y', 2 ), ('Sam', 'Temm', '1960/11/22', '3138456359', 3, 75000, 'y', 2 ), ('Nicole', 'Simmons', '1972/01/01', '3138456301', 1, 10, 'h', 2 ), ('Andy', 'Donny', '1980/09/22', '3138456302', 2, 75000, 'h', 3 ), ('Debbie', 'Andrews', '1990/06/30', '3138456303', 3, 75000, 'y', 3 ), ('George', 'Frank', '1984/05/12', '3138456304', 2, 12, 'h', 3 ) GO