Chapter 8 Homework Problems
This homework 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.
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 query has one line, all output for query 2.
7. Add descriptive column aliases for the output on query 1.
Query 1 - Write a select statement that returns four columns based on the “ItemPrice” column of
the “OrdersItems” table.
a. Use the CAST function to return the second column with a $ in front of the value.
. Using the CONVERT function to return the second column with the smallest precision in
mind to return the scale of 5.
c. Return the third column with a $ in front and a scale of 0. i.e. $3,813
d. For the fourth column, using the numbers on the left of the decimal, convert that
number to it’s equivalent Unicode character.
One row example without your descriptive headings:
$ XXXXXXXXXX $1199 ү
Query 2 – Write a query that will return output in the format below. You will need: the FirstName,
LastName from the Customers table. Then, Line1, City, State, and zip code from the Addresses table.
Only output for the first name of “Ammie”
Sample output: Note: you will need to put SQL into text mode, “
t “
Ammie Co
io
2 W Scyene Rd #3
Baltimore, MD XXXXXXXXXX
(1 row affected)
Completion time: XXXXXXXXXX11T16:17: XXXXXXXXXX:00
Chapter 9 Homework
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.
Please create solutions to the following queries:
Query 1 – The boss has asked for a report to show the number of orders per employee that has been
created going to Germany, ranked highest total number of orders being first.
Return the following information.
a. Employees
. Number of orders shipped
c. The ranking from highest number of orders being first, to lowest.
d. The shipping destination country is Germany.
Query 2 - Write a SELECT statement that returns two columns based on the Employees table.
B. The first column FirstName and Last name initial “Bob J.” alias it “Contact”
. For the phone, return the full Number without parens around the area code, alias as phone.
i.e XXXXXXXXXX
additional Criteria:
a. Only return rows for those employees with a phone ending with either 2 or 9
. Sort the result set by last name.
One row example with headers:
Contact Phone
Thom A XXXXXXXXXX
Chapter 11 Homework
Page 1 of 3
This homework 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 are
separate from anu other databases.
Create an SQL query to solve the problems. 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.
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. Turn-in all script files, and other query files that are used to show the inserted data
Please create solutions to the following queries:
1. Using the SQL query window, create a database named CIS111_Ch11_.
2. Using an SQL script, create the tables below, containing the following data elements and constraints (a and b):
a. Table Definition:
Create a table names Students
StudentID integer
StudentName varchar 30
Gender char 1
DateOfBirth datetime
Phone Number varchar 14
Career Interests varchar 100
AdvisorID integer
Create another table named Advisors
AdvisorID integer
AdvisorName varchar 30
. With the following constraints (in the initial table definition):
Students table
• StudentID, StudentName, Gender, DateOfBirth, AdvisorID – Not Null
• Phone Number – Allow Nulls
• StudentID is the primary key and auto incrementing
• Gender can only be an ‘M’ or ‘F’
• Define a foreign key constraint for the Advisors table; the AdvisorID Key.
Advisors table
• AdvisorID and AdvisorName is Not Null
• AdvisorID is primary key and auto increment field
c. Add the following as an alter table, below the initial table definitions:
• Add an Email field to the Students table
• Remove the Interests field.
3. Add one row of data for each table. Create a query to show all data.