Mod6 SQL Lab

Instructions for the Lab

1. Download and save theAdvanced SQL Lab Worksheet to your computer.
2. The instructions take you through your assignment. Follow them carefully!
3. As required, attach screenshots of your work to the lab document.

Mod 6 Data Structure Forms

Steps of Your Assignment

1. Identify each Data Structure in your business. (Example: you're running a trucking company. One Data Structure would be all the details about one driver including the types of trucks this driver is qualified to drive.) Remember to use the 3rd Normal Form!
2. For each Data Structure in your business, complete a copy of the Data Structure Form. Depending on your business, you could have two or more forms. The form provides spaces for you to list the following:
   1. Business Rules listed, such as cardinality and modality rules, candidate key selection, etc., cover issues that pertain to the student's project.
   2. In the Data Structure for each form, all Foreign Key fields have been entered.
   3. The Data Structure for each form has a primary key, and all Primary Key fields have been designated with an underline.
   4. The Data Structure for each form is designed to 3rd Normal Form.
   5. All details for each Data Structure have been inserted as needed in the Table Structure, including Column Name, Data, Type, Allow Nulls, and Comments. (The correct data types were used for each field. Assumptions are specified for cardinality, modality, and referential integrity constraints as needed. Follows definition for referential integrity constraint.)
   6. In the Physical Design Issues, entries have been made for data volume, data volatility, and additional considerations as these apply to the business for the project.
3. Zip your separate Word Data Structure forms into a single file before submitting.

 

mod06_data_structures_form.docx

Data Structure Form
Complete the form according to your instructions. For details, please refer to the sample completed form
provided separately. The spaces expand as you write. Table Name:
Business Rules: List all cardinality and modality rules that apply to this data structure, rules for primary /candidate key
selection, de-normalization considerations, and so on. Remember the data structure is one that comes out of
the business for your project. Data Structure: (List only one per form.) Table Structure:
For each field identified in the data structure, complete the information in the table below.
Tip: In the "Comments" column (far right), be sure to specify assumptions for cardinality, modality and
referential integrity constraints as needed. See definition below.* Column Name Data Type Allow
Nulls? Comments: (foreign key, constraints,
default) * Use the following definition of referential integrity constraint:
A constraint (rule) that specifies that the value or existence
of an attribute in one relation depends on the value or existence
of a primary key in another relation.
2 tables - patient/ physician - patient one physician and physician many patients.
student -teacher
teacher - many students
Example: Admitting Physician in Patient must exist in Physician. Data Structure Form
Physical Design Issues: Estimate number of records in table, number of updates and/or queries per unit of time, issues that affect
physical design such as security, joins, applications that use tables, and so on. Data Volume
(# Records)
24,000 Data Volatility
(# of accesses per time period,
% update to read access)
How many records read each day. Additional Considerations
(Response time, Security,
Performance, Applications, etc.)
Password for security; maybe add
indexes. © Rasmussen College 2011

Advanced SQL Lab - Tutorial Lab Objective
This lab will allow you to use SQL Server Management Studio to create advanced
queries in a database. This software allows you to interact with the database
directly. This tool allows a Database Administrator to manage and maintain the
database.
Required Materials SQL Server 2008 (express or full version).
AdventureWorks database file or AdventureWorksR2 database file
Advanced SQL Lab - Tutorial (this document) Lab Steps
1. Click start, navigate to all programs and select Microsoft SQL Server 2008,
and then click on SQL Server Management Studio.
2. Attach the AdventureWorks or AdventureWorks R2 database to SQL Server
2008.
3. You will now create the following queries using Microsoft SQL Server 2008. For
each query create a screenshot to show your results. Make sure you use the
correct version of each query for your database.
4. Query 1- for AdventureWorks
****note error—have group function and row data in
**** same Select clause—requires Group By
select min(Rate), EmployeeID
from HumanResources.EmployeePayHistory
group by EmployeeID
Query 1- for AdventureWorksR2
****note error—have group function and row data in
**** same Select clause—requires Group By
select min(Rate), ID
from HumanResources.EmployeePayHistory
group by BusinessEntityID
Insert screenshot below: 5. Query 2-for AdventureWorks *** SubQuery! Only have to run one query…
select EmployeeID, Rate
from HumanResources.EmployeePayHistory
where Rate = (Select min(Rate) from HumanResources.EmployeePayHistory)
Query 2- for AdventureWorks R2
*** SubQuery! Only have to run one query…
select BusinessEntityID, Rate
from HumanResources.EmployeePayHistory
where Rate = (Select min(Rate) from HumanResources.EmployeePayHistory)
Insert screenshot below: 6. Query 3- for either database
****execute query with min, max in place of avg
select avg(Rate)
from HumanResources.EmployeePayHistory
Insert screenshot below: 7. Query 4- for AdventureWorks
**** only display first 3 from results list
***enter ASC to see top 3 lowest paid employees!
select top 3 Rate, EmployeeID
from HumanResources.EmployeePayHistory
Order by Rate Desc Query 4- for AdventureWorksR2
**** only display first 3 from results list
***enter ASC to see top 3 lowest paid employees! select top 3 Rate, BusinessEntityID
from HumanResources.EmployeePayHistory
Order by Rate Desc Insert screenshot below: 8. Query 5-for AdventureWorks
****Note Alias. INNER JOIN returns only those rows that have matches in
***both tables.
SELECT Person.Contact.FirstName + ' ' + Person.Contact.LastName AS [Full Name]
FROM Person.Contact INNER JOIN HumanResources.Employee ON
Person.Contact.ContactID = HumanResources.Employee.ContactID Query 5- for AdventureWorksR2
****Note Alias. INNER JOIN returns only those rows that have matches in
***both tables.
SELECT Person.Person.FirstName + ' ' + Person.Person.LastName AS [Full Name]
FROM Person.Person INNER JOIN HumanResources.Employee
ON Person.Person.BusinessEntityID = HumanResources.Employee.BusinessEntityID Insert screenshot below: 9. Query 6-for AdventureWorks
***When need data from 3 tables, need 2 sets of join conditions. Need to
***join first two tables and join result to third table.
SELECT HumanResources.Employee.EmployeeID, Person.Contact.FirstName,
Person.Contact.LastName, Person.Contact.Phone,
HumanResources.EmployeePayHistory.Rate FROM Person.Contact INNER JOIN
HumanResources.Employee ON Person.Contact.ContactID =
HumanResources.Employee.ContactID INNER JOIN
HumanResources.EmployeePayHistory ON
HumanResources.Employee.EmployeeID =
HumanResources.EmployeePayHistory.EmployeeID WHERE
HumanResources.EmployeePayHistory.Rate > 50 ORDER BY
HumanResources.EmployeePayHistory.Rate DESC
Query 6-for AdventureWorksR2
***When need data from 3 tables, need 2 sets of join conditions. Need to
***join first two tables and join result to third table. SELECT HumanResources.Employee.BusinessEntityID,
Person.Person.FirstName, Person.Person.LastName,
HumanResources.EmployeePayHistory.Rate
FROM Person.Person
INNER JOIN HumanResources.Employee
ON Person.Person.BusinessEntityID = HumanResources.Employee.
BusinessEntityID
INNER JOIN HumanResources.EmployeePayHistory ON
HumanResources.Employee.BusinessEntityID =
HumanResources.EmployeePayHistory.BusinessEntityID WHERE
HumanResources.EmployeePayHistory.Rate > 50 ORDER BY
HumanResources.EmployeePayHistory.Rate DESC Insert screenshot below: 10.Now submit the file with your screenshots to the dropbox.

Attachments:

• 1493786832-mod06_data_structures_form.docx
• 1493786832-mod06_sql_lab_REVISED.docx