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...

h


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 brief 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. b. 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: $1199.00 1199.00000 $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 Corrio 2 W Scyene Rd #3 Baltimore, MD. 21217 (1 row affected) Completion time: 2022-09-11T16:17:23.5018894-04: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 brief 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 b. 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” b. For the phone, return the full Number without parens around the area code, alias as phone. i.e. 313- 845-6356 additional Criteria: a. Only return rows for those employees with a phone ending with either 2 or 9 b. Sort the result set by last name. One row example with headers: Contact Phone Thom A. 206- 555-8484 Chapter 11 Homework Page 1 of 3 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 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 brief 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 b. 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.