Description
Unformatted Attachment Preview
Unit 2 Assignment
SQL Server: Data Manipulation Language (DML) single table queries
Outcomes addressed in this activity:
•
•
•
•
Describe query processing phases.
Identify components of a single table query.
Complete single table queries.
Use AND/OR conditions in your SQL code.
Course Outcome:
IT526-2: Apply SQL single table query commands effectively.
Purpose
In Unit 2’s assignment, you will have an opportunity to practice what you learned about single
table queries. Like most types of information technology skills, SQL proficiency is accomplished
through repetition and practice, and you will have an opportunity to do that in our class in
general, and this Unit 2 in particular.
Assignment Instructions
This assignment includes single table query questions using the sample databases attached to the
SQL Server implementation installed in Unit 1. Remember to add the personalized comment as
noted at the beginning of Unit 1 before each of your answer submissions of SQL code and
corresponding result set. Failure to include the required comment with each question will result
in a grade of zero.
Assignment Requirements
Remember to make sure the correct database is active before executing a SQL query.
Also, review the university policy on plagiarism. If you have any questions, please contact your
professor.
Unit 2 Questions: Single Table Queries
1. Submit the SQL query syntax and a result set that includes all tuples and attributes within
the customers table of the Sales Order Database.
2. Submit the SQL query syntax and a result set that includes all the tuples and the attributes
productname and categoryID from the products table within the Sales Order Database.
Display the result set in alphabetic order by productname.
3. Submit the SQL query syntax and a result set that includes all the tuples and the attributes
mbrfirstname, mbrlastname, mbrphonenumber from the members table within the
Entertainment Agency Database. Order the result set by mbrfirstname within
mbrlastname.
4. Submit the SQL query syntax and a result set that includes all the tuples and attributes,
except entertainmentID, from the engagements table within the Entertainment Agency
Database.
5. Submit the SQL query syntax and a result set that includes entstagename, entssn, and
entcity from the Entertainers table within the Entertainment Agency Database. Order the
result set by entstagename in descending order within entcity in ascending order.
6. Submit the SQL query syntax and a result set that includes all the tuples and the studstate
attribute from the Students table within the SchoolSchedulingExample database. Make
sure no duplicates are included in the result set.
7. Submit the SQL query syntax and a result set that includes all the tuples and stffirstname,
stflastname, stfcity, and stfstate attributes from the Staff table within the
SchoolSchedulingExample database. Order the result set by stfzipcode in descending
order.
8. Submit the SQL query syntax and a result set that includes buildingname from the
Buildings table within the SchoolSchedulingExample database. Sort the result set by
numberoffloors.
9. Submit the SQL query syntax and a result set that includes all the tuples and attributes in
the teams table within the BowlingLeagueExample database. Sort the result set by
teamname.
10. Submit the SQL query syntax and a result set that includes all tuples and bowlerid,
rawscore, handicapscore, and wongame attributes from the bowler_scores table within
the BowlingLeagueExample database. Sort the result set by wongame with wongames
being included first in the result set.
11. Submit the SQL query syntax and a result set that includes all tuples and the
ingredientname attribute from the ingredients table within the RecipesExample database.
Sort the result in descending order.
12. Submit the SQL query syntax and a result set that includes all tuples and productnumber,
quotedprice, and discountedprice (quotedprice – 10%) in the Order_Details table within
the SalesOrderExample database.
13. Submit the SQL query syntax and a result set that includes all tuples and productname,
retailprice, newretailprice (retail price + 20%) in the Products table within the
SalesOrderExample database. Sort the result set by quantityonhand in descending order.
14. Submit the SQL query syntax and a result set that includes all tuples and attributes in the
customers table within the EntertainmentIndustryExample database. In your result set
display the custfirstname and custlastname as customer name with a space between the
first and last names (e.g. Customer Name Mike Collins) (HINT: You will need to use the
concat function)
15. Submit the SQL query syntax and a result set that includes all tuples and entstagename,
entcity, entstate attributes in the Entertainers table within the
EntertainmentAgencyDatabase. Use the concat function to concatenate entcity and
entstate attribute values with a space between them in the result set.
16. Submit the SQL query syntax and a result set that includes all tuples and agtlastname,
agtfirstname, datehired, and First Review Date (calculated as 9 months in the future from
datehired) in the Agents table within the EntertainmentAgencyDatabase. (HINT: You
will need to use the DATEADD function in this SQL Query)
17. Submit the SQL query syntax and a result set that includes all tuples and tourneylocation,
tourneydate, NextYearTourneyDate attributes in the Tournaments table within the
BowlingLeagueExample database. (HINT: NextYearTourneyDate is calculated by adding
364 days to the tourneydate attribute)
18. Submit the SQL query syntax and a result set that includes all tuples and concatenated
agtfirstname, agtlastname, and salary with a space between the attributes. Additionally
include commission rate, and the calculated attribute New Rate as commissionrate + .01
in the Agents table within the EntertainmentAgencyExample database.
19. Submit the SQL query syntax and a result set that includes all tuples and bowlerid,
Handicap Differenence (which is handicapscore – rawscore) attributes in the
Bowler_Scores table within the BowlingLeagueExample database. Sort the result set by
Handicap Difference in descending order.
20. Submit the SQL query syntax and a result set that includes all tuples and Vendor Name
(alias of VendName), and Vendor Address (concatenated from vendstreetaddress,
vendcity, vendstate, and vendzipcode) in the Vendors table within the
SalesOrderExample database.
Directions for Submitting Your Assignment
Name your Assignment document according to this convention: First Last name (use your own
name of course). Submit your completed Assignment to the Unit 2 Dropbox by the deadline.
Purchase answer to see full
attachment
