Chapter 8 La
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
ief 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.
. 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 $ XXXXXXXXXX $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
ief 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
eplace 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
) City
c) Calculated field “Item price * Quantity”, aliases as “InvoiceSum”
The tables:
a) Address
) 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
eceive 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
ief 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 XXXXXXXXXXNOT 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 XXXXXXXXXXINT 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', 'Dea
orn', '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', ' XXXXXXXXXX', 1, 250000, 'y', 1 ),
('Fred', 'Sanford', '1960/12/10', ' XXXXXXXXXX', 1, 75000, 'y', 1 ),
('Amy', 'Jones', '1988/08/30', ' XXXXXXXXXX', 1, 75000, 'y', 2 ),
('Sam', 'Temm', '1960/11/22', ' XXXXXXXXXX', 3, 75000, 'y', 2 ),
('Nicole', 'Simmons', '1972/01/01', ' XXXXXXXXXX', 1, 10, 'h', 2 ),
('Andy', 'Donny', '1980/09/22', ' XXXXXXXXXX', 2, 75000, 'h', 3 ),
('De
ie', 'Andrews', '1990/06/30', ' XXXXXXXXXX', 3, 75000, 'y', 3 ),
('George', 'Frank', '1984/05/12', ' XXXXXXXXXX', 2, 12, 'h', 3 )
GO