Reference no: EM131160450
Lab: Advanced SQL
Objectives:
1. Explore more advanced SQL queries.
2. Answer the questions, copy the SQL code, create screenshots, and submit the results.
Submission requirements:
• For all text and image submissions, use MS Word, which is available to you within the Virtual Desktop Infrastructure (VDI).
• For all SQL code submissions, use MS Word, which is available to you within VDI.
• For all diagram submissions, use MS Visio, which is available to you within VDI.
o Note: If you need assistance on how to get started with this tool, go to the references section at the end of this document.
• If the submission is more than one file:
1. Name each item appropriately.
a. For example: LAB6-AdvSQL-yourName.vsd, LAB6-Questions-yourName.docx
2. Save each item in a single folder.
3. This folder should also be named appropriately.
a. For example: LAB5-yourName
4. Compress the folder.
5. Submit the compressed file in Blackboard.
Lab:
1. Write an SQL statement to get the average, maximum, and minimum quantity per order stored in table "Sales.SalesOrderDetail", column "SalesOrderID", for order numbers 43660, 43670, and 43672. This query should be written as a single SQL statement.
a. Fill out the following table:
|
Order number
|
Average
|
Maximum
|
Minimum
|
|
43660
|
|
|
|
|
43670
|
|
|
|
|
43672
|
|
|
|
b. Submit the SQL statement used to accomplish this task.
c. How many rows were affected?
d. Provide a screenshot of the result set.
2. When working in a normalized environment, chances are one will have to combine tables and get a result set into a table. To accomplish this task, the clause JOIN is used. Depending on what result is needed, different forms of this clause are used. They are:
• INNER JOIN
• OUTER JOIN (both LEFT and RIGHT)
• FULL JOIN
• CROSS JOIN
What all types of JOIN have in common is that they, based on a condition, match one record from one table to one or more records in another table. The result will be records that combine the data from both tables.
INNER JOINs are the most used type of JOIN. They return only the data for which matches were found.
SELECT *
FROM Person.Person
INNER JOIN HumanResources.Employee
ON Person.Person.BusinessEntityID =
HumanResources.Employee.BusinessEntityID
Using the example above, write an SQL query that returns all the information for all contacts stored in the Person.BusinessEntity table and only the jobTitle from the HumanResources.Employee table. Note that multiple rows may be returned.
a. Submit the SQL statement used to accomplish this task.
b. How many rows were affected?
c. Provide a screenshot of the result set.
|
Behind the scenes affecting everyday life
: When we study technology we should consider how technology is changing and how those changes affect the world around us. Considering how the world into which someone was born in 1955 was different in 2015 when that person would turn 65. What changes ..
|
|
Discuss the reaction rate of homogeneous reactions
: Minus signmeans disappearance.In addition, rates of reaction of all materials are related by (-rA) / a = (-rB) / b = rR/ r = rS/ s.
|
|
What responsibility does a judge have
: What responsibility does a Judge have to the victims of crime and their families? Summarize the events that took place surrounding the scandal. What ethical and moral implications does this scandal have for the criminal justice system
|
|
Sum of even numbers up
: Prove by induction that the sum of even numbers up to n is equal to n(n+2)/4. In other words: 2 + 4 + 6 + ..+n = n(n+2)/4 Hint: Since these are even numbers, the next number from n is n + 2?
|
|
Submit the sql statement used to accomplish the task
: Submit the SQL statement used to accomplish this task. What all types of JOIN have in common is that they, based on a condition, match one record from one table to one or more records in another table.
|
|
What would you advise john blake to do
: Do you believe it would take courage for Blake to honestly reveal the timing and content of the scientific studies? What sources of courage might he call upon to help him make his decision?
|
|
What is the probability of not getting any hits in game
: Buster Posey of the San Francisco Giants had the highest batting average in the 2012 Major League Baseball season. His average was .477. So assume the probability of getting a hit is .477 for each time he batted. In a particular game, assume he batte..
|
|
Playing a round-robin style tournament
: A league of 20 teams is playing a "round-robin" style tournament, where each team plays every other team exactly once. How many games total need to be played?
|
|
Differentiate elementary andnon-elementary reactions
: What are the various intermediates that can be formed in a non-elementary reaction?Free radicals, ions and polar substances, molecules, transition complexes are thevarious intermediates that can be formed ina non-elementary reaction
|