INFO1085 (Winter 2022)_Lab 4.1
pdf
keyboard_arrow_up
School
Conestoga College *
*We aren’t endorsed by this school
Course
1000
Subject
Information Systems
Date
Apr 3, 2024
Type
Pages
5
Uploaded by DeaconPencil11980
INFO1085 - SQL Server (Winter 2022 - Section #1)
Page 1
of 5
School of Workforce Development, Continuing Education, and Online Learning Network Technical Support (# 1470) Course Information Name
SQL Server
Code
INFO1085 - Section 1
Faculty Information Professor/Instructor
Firas Chahine, Ph.D. Email
fchahine@conestogac.on.ca Office Number
113 Office Hours
By appointment only Lab 4.1 Managing database objects: 1-
Create the following views on AdvenstureWorks2016 database and show results: a.
Create a view called vQuery7C based on your query in Activity 3.1 for question 7.c: i.
Create a query that will show ONLY loginID, jobTitle, name, and email address for all employees:
ii.
[
ASSESSMENT
]: Provide the T-SQL command you have used
iii.
[
SCREENSHOT
]: Query the view and show your result set b.
Create a view called vQuery7D based on your query in Activity 3.1 for question 7.d: i.
Create a query that will show name and email addresses of people who are NOT employees: ii.
[
ASSESSMENT
]: Provide the T-SQL command you have used
INFO1085 - SQL Server (Winter 2022 - Section #1)
Page 2
of 5
iii.
[
SCREENSHOT
]: Query the view and show your result set 2-
Create the following table on AdvenstureWorks2016 database and show results: a.
Create a table called EmployeeCopy under HumanResources schema with attributes: LoginID, JobTitle, and BusinessEntityID and copy the contents of HumanResources.Employee table to it. i.
[
ASSESSMENT
]: Provide the T-SQL command(s) you have used:
ii.
[
SCREENSHOT
]: Provide screenshot of new table and its columns:
b.
Insert a new row in EmployeeCopy: i.
[
ASSESSMENT
]: Provide the T-SQL command you have used
3-
Managing storage on tables: a.
What is the size of the Person.Person table: SSMS > DB Instance > AdventureWorks2016 > Tables > [Right click] Person.Person > Properties > Storage > Data space: i.
[
ASSESSMENT
]: Provide the current size in KB:
b.
Which compression method provide the best space savings for the Person.Person table? ROW or PAGE compression? And Why is that? Run the stored procedure sp_estimate_data_compression_savings
to estimate spaces savings:
EXEC sp_estimate_data_compression_savings @schema_name = 'Person', @object_name = 'Person', @index_id = 1, @partition_number = NULL, @data_compression = 'ROW'; EXEC sp_estimate_data_compression_savings @schema_name = 'Person', @object_name = 'Person', @index_id = 1, @partition_number = NULL, @data_compression = PAGE;
INFO1085 - SQL Server (Winter 2022 - Section #1)
Page 3
of 5
i.
[
ASSESSMENT
]: Which method provides best space savings and why is that (hint look at the type of data stored in Person table)? 4-
Create a relational database called Assignment4 with the following specifications: a.
Database must have two filegroups: PRIMARY and A4_SEC: i.
[
ASSESSMENT
]: Provide the T-SQL command you have used to create the database
b.
Database will have three tables: Customer, Store, and Sales. i.
Design the tables and their columns using the result set below. Remember best practices when designing tables (i.e. what are we modeling) ii.
Sales table will be stored on A4_SEC filegroup, whereas the rest can be on PRIMARY filegroup. iii.
Store ID and Store Location attributes cannot be duplicated (i.e. constraint) iv.
You must use the correct data types, constraints, and primary & foreign keys to create a “relational” database. v.
[
ASSESSMENT
]: Provide the T-SQL commands you have used to create the tables as per the specifications above:
vi.
[
ASSESSMENT
]: Provide the database relationship diagram of the database you built:
SMSS > DB Instance > Assignment 4 > [Right click] Database Diagrams > New Database Diagram:
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
INFO1085 - SQL Server (Winter 2022 - Section #1)
Page 4
of 5
Name Address Store ID Store Location Sale Amount Sale date Matt Hills 2080 Derry Road Mississauga, ON 1900 1 Weber Street N Waterloo, ON 4.95 January 1 2019 11:34:34 Chris George 20 Younge Street, Toronto ON 1400 1 King Street S Kitchener, ON 12.20 January 2 2019 12:19:13 Matt Hills 2080 Derry Road Mississauga, ON 1200 22 Lancaster Blvd, Kitchener, ON 19.11 January 2 2019 08:34:21 Chris George 20 Younge Street, Toronto ON 560 12 Westmount Cres, Waterloo ON 7.54 January 4 2019 07:33:33 Matt Hills 2080 Derry Road Mississauga, ON 560 12 Westmount Cres, Waterloo ON 14.33 January 4 2019 13:58:12 Matt Hills 2080 Derry Road Mississauga, ON 1400 1 King Street S Kitchener, ON 6.32 January 5 2019 15:33:09 c.
Add the above data into the correct tables:
i.
[
ASSESSMENT
]: Provide the T-SQL commands you have used to:
d.
Create the above result set as a view called vAssignment4:
i.
[
ASSESSMENT
]: Provide the T-SQL commands you have used to:
Assignment
INFO1085 - SQL Server (Winter 2022 - Section #1)
Page 5
of 5
ii.
[
SCREENSHOT
]: Query the view and show your result set