Reference no: EM131267291

Use SQL Server Management Studio to create the queries.

1. For all products with a quantity on hand of 100 or more, list their product number, description, and quantity on hand. List the products in alphabetical order by description. Use the default column headings.

2. An increase of 10% in the salary of employees with the job title of Clerk is being considered. For all clerks, list the employee last name, current salary, and projected new raised salary. Order the list by salary, from the highest salary to the lowest salary. Use a secondary sort order of employee last name. For column headings, use: Name, Salary, and Raised Salary.

3. For each employee hired on or after January 1, 2012, list the employee's first name, last name, hire date, and phone number (which is the phone number of the department in which the employee works). Order the list primarily by last name and secondarily by first name. Use the default column headings.

4. For each customer that is located in either Florida or Georgia and that has a credit limit of $10,000 or more, list the company name, state, credit limit, and account representative last name (the last name of the employee that serves as the customer's account representative). Order the list alphabetically by company name. For column headings, use: Customer, State, Credit Limit, and Account Rep.

5. List the product description, customer company name, and order date for each time that a Junior product was ordered by a customer in 2012. (Assume that the descriptions of all such products begin with the word Junior.) Order the list by product description. Use the default column headings.

6. For all customers, list the company name and the total number of orders placed by the customer. Include all customers, even those who have never placed an order. Order the customers in the list by the number of orders placed, from most to fewest. For any customers with the same number of orders, arrange them within the list alphabetically by company name. Use Customer Name and Order Count as the column headings.

7. For all departments, list the department number, department name, and average salary of the employees in that department. Do not include the salaries of employees who are clerks in the average salary calculation. Only include a department in the list if it has an average salary (of non-clerk employees) greater than $5,000. List the departments by average salary, from highest to lowest. For column headings use: Dept. No., Department Name, and Average Salary.

8. For the customer who placed the first order with Acme, as well as the customer who placed the most recent order with Acme, list the company name, phone number, last name of sales representative, and order date. The customer placing the most recent order should be listed first. For column headings use: Company Name, Telephone, Salesperson, and Date.