Question

ISE 311 SPRING 2023 HOMEWORK #2 You can submit the screenshot of the queries on MS SQL. You do not have to write the queries by hand. Answer the following questions for the sample database studied in classes. Note that your queries must be written based on the data provided in the question, not based on the values in the tables. For example if the question asks to find the orders of Carter Baizen, in your query, you must use the name Carter Baizen, not the EMP_ID of Carter Baizen (3) which you can obtain by looking at tables. 1) List types of products ordered in 2009. The same part should not appear twice in the final list (Hint: use built-in functions to extract year information from the date). 2) List types of products whose ordered quantity (i.e. quantity in a single order) is more than their available quantity. The same product should not appear twice in the final list. 3) List the customers the amount of a single order made by whom exceeds its credit limit. 4) List the order numbers and prices of orders taken by Northern offices. List should be ordered by price in ascending order. 5) Consider the employees whose sales are below their targets. List the names and ages of these employees together with the names of their supervisors. List should be ordered with respect to the ages of employees in descending order. 6) List the all information of customers who did not make an order (Hint: use outer join and IS NULL function. Keep in mind that your query should not be based on the values in the tables. It should work even if the values are different than the ones given in handouts.) 7) List the total and average prices of orders made in 2008 8) List minimum, maximum and total prices of orders taken by Western offices. 9) List the names of companies whose sales representative is hired after year 2008. 10) List the names and titles of employees who are the managers of Southern offices and older than 40 years old. 11) Write a query that returns the average age of employees in the company.

ISE 311 SPRING 2023
HOMEWORK #2

You can submit the screenshot of the queries on MS SQL. You do not have to write the
queries by hand.

Answer the following questions for the sample database studied in classes. Note that your
queries must be written based on the data provided in the question, not based on the values in
the tables.

For example if the question asks to find the orders of Carter Baizen, in your query, you must
use the name Carter Baizen, not the EMP_ID of Carter Baizen (3) which you can obtain by
looking at tables.

1) List types of products ordered in 2009. The same part should not appear twice in the final list
(Hint: use built-in functions to extract year information from the date).

2) List types of products whose ordered quantity (i.e. quantity in a single order) is more than
their available quantity. The same product should not appear twice in the final list.

3) List the customers the amount of a single order made by whom exceeds its credit limit.

4) List the order numbers and prices of orders taken by Northern offices. List should be ordered
by price in ascending order.

5) Consider the employees whose sales are below their targets. List the names and ages of these
employees together with the names of their supervisors. List should be ordered with respect
to the ages of employees in descending order.

6) List the all information of customers who did not make an order (Hint: use outer join and
IS NULL function. Keep in mind that your query should not be based on the values in
the tables. It should work even if the values are different than the ones given in
handouts.)

7) List the total and average prices of orders made in 2008

8) List minimum, maximum and total prices of orders taken by Western offices.

9) List the names of companies whose sales representative is hired after year 2008.

10) List the names and titles of employees who are the managers of Southern offices and older
than 40 years old.

11) Write a query that returns the average age of employees in the company.

ISE 311 SPRING 2023
HOMEWORK #2

You can submit the screenshot of the queries on MS SQL. You do not have to write the
queries by hand.

Answer the following questions for the sample database studied in classes. Note that your
queries must be written based on the data provided in the question, not based on the values in
the tables.

For example if the question asks to find the orders of Carter Baizen, in your query, you must
use the name Carter Baizen, not the EMPID of Carter Baizen (3) which you can obtain by
looking at tables.

1) List types of products ordered in 2009. The same part should not appear twice in the final list
(Hint: use built-in functions to extract year information from the date).

2) List types of products whose ordered quantity (i.e. quantity in a single order) is more than
their available quantity. The same product should not appear twice in the final list.

3) List the customers the amount of a single order made by whom exceeds its credit limit.

4) List the order numbers and prices of orders taken by Northern offices. List should be ordered
by price in ascending order.

5) Consider the employees whose sales are below their targets. List the names and ages of these
employees together with the names of their supervisors. List should be ordered with respect
to the ages of employees in descending order.

6) List the all information of customers who did not make an order (Hint: use outer join and
IS NULL function. Keep in mind that your query should not be based on the values in
the tables. It should work even if the values are different than the ones given in
handouts.)

7) List the total and average prices of orders made in 2008

8) List minimum, maximum and total prices of orders taken by Western offices.

9) List the names of companies whose sales representative is hired after year 2008.

10) List the names and titles of employees who are the managers of Southern offices and older
than 40 years old.

11) Write a query that returns the average age of employees in the company.

Added by Meagan P.

Question

Please give Ace some feedback