SQL Query Practice #3

Display the average salary of the stock clerks.

Display the average salary of each job titled except the president and vice presidents. Display the table alphabetically in title.

Display the average salary of each manager ID where more than one person holds that ID. Only include IDs where the average salary is greater than 1000.

Display the average order for each order date where more than one order was placed and the average order is greater than 1000. Display them in descending order of average order.

Display the customer name and the number of each customer with more than one order. Arrange alphabetically by customer.

Find the average salary for those employees where the average salary is greater than 1000. (Write this query using having and derived relation)

One employee was hired June 24, 2000, and another employee was hired March 28, 2002. Find how many months were between their hiring. Also show the number of days between. (use DUAL table)

A person is hired today. Show the date of a review performance after 2 months. (Use DUAL table)

From employee, show the start date and the last day of the month that all the sales representatives were hired.

Show the number of days between ordering and shipping for all the orders in June

Challenge

Use the tables given in the SQL Query Practice #1

Express the following queries in SQL:

Display the average salary of the stock clerks.
Display the average salary of each job titled except the president and vice presidents. Display the table alphabetically in title.
Display the average salary of each manager ID where more than one person holds that ID. Only include IDs where the average salary is greater than 1000.
Display the average order for each order date where more than one order was placed and the average order is greater than 1000. Display them in descending order of average order.
Display the customer name and the number of each customer with more than one order. Arrange alphabetically by customer.
Find the average salary for those employees where the average salary is greater than 1000. (Write this query using having and derived relation)
One employee was hired June 24, 2000, and another employee was hired March 28, 2002. Find how many months were between their hiring. Also show the number of days between. (use DUAL table)
A person is hired today. Show the date of a review performance after 2 months. (Use DUAL table)
From Employee, show the start date and the last day of the month that all the sales representatives were hired.
Show the number of days between ordering and shipping for all the orders in June

Solutions

Display the average salary of the stock clerks.

SELECT AVG(salary) FROM employee where title = 'Stock Clerk';

Display the average salary of each job titled except the president and vice presidents. Display the table alphabetically in title.

SELECT title, AVG(salary) FROM employee GROUP BY title HAVING title <> 'president' AND title<>'vice president' ORDER BY title;

Display the average salary of each manager ID where more than one person holds that ID. Only include IDs where the average salary is greater than 1000.

SELECT manager_id, AVG(salary) FROM employee GROUP BY manager_id HAVING AVG(salary) > 1000;

Display the average order for each order date where more than one order was placed and the average order is greater than 1000. Display them in descending order of average order.

SELECT date_ordered, AVG(total) FROM orders GROUP BY date_ordered HAVING count(date_ordered) > 1 AND AVG(total) > 1000 ORDER BY AVG(total) DESC;

Display the customer name and the number of each customer with more than one order. Arrange alphabetically by customer.

SELECT customer.customer_name, count(*) FROM orders JOIN customer on customer.customer_id = orders.customer_id GROUP BY customer.customer_name;

Find the average salary for those employees where the average salary is greater than 1000. (Write this query using having and derived relation)

SELECT employee_first_name, avg(salary) FROM employee GROUP BY employee_first_name HAVING avg(salary) > 1000;

One employee was hired June 24, 2000, and another employee was hired March 28, 2002. Find how many months were between their hiring. Also show the number of days between. (use DUAL table)

SELECT round(months_between(TO_DATE('2002-03-28','YYYY-MM-DD'),TO_DATE('2000-06-24','YYYY-MM-DD'))) AS mnth, round(months_between(TO_Date('2002-03-28','YYYY-MM__DD'),TO_DATE('2000-06-24','YYYY-MM-DD'))*30) as day FROM dual;

A person is hired today. Show the date of a review performance after 2 months. (Use DUAL table)

SELECT add_months(sysdate,2) FROM dual;

From employee, show the start date and the last day of the month that all the sales representatives were hired.

SELECT start_date "start date" , last_day(start_date) "Last Day" FROM employee;

Show the number of days between ordering and shipping for all the orders in June

SELECT round(months_between(date_shipped,date_ordered)*30) AS day FROM orders WHERE extract(month FROM date_ordered)=6;

Page updated

Google Sites

Report abuse