CS$443-$ A$2$

No duplicate rows are allowed

Write the queries necessary to obtain the required information. Make sure all columns you return have descriptive column headings. You must show the result of the query. For example, if the query is:

$\backslash (\backslash$Rightarrow $\backslash )$ Show the office id$,$ the city, and the region

$\backslash (\backslash$Rightarrow $\backslash )$ Your query should be:

select office, city, region

from offices;

$\backslash (\backslash$Rightarrow $\backslash )$ and then you need to show the following on the screen: $($snapshot of your result$)$

The following are the questions for A$2.$

$1)$ Write a select statement to list all the columns from the Offices table. 'Select $*\text{'}$ is not allowed. Return the list in DESC Office order.

$2)$ List the product id$,$ mfr id$,$ Name $($its description$),$ and dollar value of quantity on hand $($price $*$ quantity$)$ of the products that their dollar value is greater than $20000.$ Return the list in descending order by their dollar value.

$3)$ List the Order Number, Order Date, Customer Number and Sales Rep Number for orders for part $\text{'}\backslash (779\backslash$mathrm$\{$C$\}^\{\backslash$prime$\}\backslash )$ or $\text{'}773$C$\text{'}.($Use a compound search condition $-$ OR$.)$

$4)$ List the Name and Age for all Sales Reps in the following offices: $11$; $13.($Use the set membership test $-$ IN$.)$

$5)$ List the names of all Sales Reps who have the letter $\text{'}$a$\text{'}($this is lower case a$)$ as the second character of their name.

$6)$ Return the Sales Rep ID and Name of any Sales Rep not assigned to an office yet.

$7)$ Show all the sales rep names with last name "Smith" or "Jones".

$8)$ List different titles in the sales reps table. Only list each title once and unknown titles should be ignored.

Duplicate rows are not allowed.

List the Mfr Id$,$ the Product Id$,$ Company and PRICE of all products bought by customers where customer

number is one of $(2111,2112,2105,2119)$ and where the amount of the order $$ $$10,000.$ Order the results by

ascending Company. Duplicate row are not allowed.

List all orders number, the name of the rep who took the order, the city where the rep has his$/$her office. Print

the employee whose office is in either Chicago or Denver;

List all customers $($Customer number, Company, and Credit Limit$)$ and orders $($Order Number,

Amount$)$ where the Amount is within $$10,000$ of the Credit Limit. Therefore:

Credit$\_$limit should be greater than zero and

Amount should be greater than zero and

Amount should be less than credit$\_$limit and

Credit$\_$limit $-$ Amount $$10000$

List all salesreps names and their managers' names where the salesrep is at least as old as the

manager. Duplicate rows are not allowed.

List all salesreps $($Name$)$ and the City they work in where either the sales of the salesrep $$ Quota or

the sales for the office is $$ Target.

List the name, id$,$ and hire date of the salesreps where the following two conditions hold:

a$)$ The salesrep sales should be greater than the quota and

b$)$ The rep should be either "Sam Clark", or "Mary Jones"

List all orders $($Order Number$)$ over $$7,000$ showing the company placing the order, the Customer

Rep assign to the company, the Office id and the city, and the rep who took the order where such

that the Customer Rep's is not the rep who took the order.

List all customer reps $($their name and their id$)$ and their managers name in which the manager is

based in New York. Use appropriate column header.

List all products $($Productld$,$ MFR$\_$Id$,$ and Description$),$ customers $($CustNum$,$ Company$)$ who have

bought that product, and orders $($Order Number, and Order Date$)$ where the order $$ $$700.$ Sort the

rows by the OrderDate. Duplicate rows is not allowed.

List the name of the salesreps and the name of their managers only if the manager has taken care of

some orders.

The screen shots provided show everything in more detail