SQL Query Practice #4

SELECT employee_first_name, employee_last_name, department_id FROM employee WHERE salary IN (SELECT salary FROM employee WHERE employee_last_name = 'Cena') AND commission_pct LIKE (SELECT commission_pct FROM employee WHERE employee_last_name='Cena');

SELECT customer_name, city, country, customer.sales_rep_id FROM customer, orders where customer.customer_id = customer_id AND date_ordered IN (SELECT date_ordered FROM orders WHERE date_ordered='23-aug-2014');

SELECT sales_rep_id FROM orders WHERE total > SUM(SELECT AVG(total) FROM orders);

SELECT department_name FROM department WHERE exists(SELECT id FROM employee WHERE dept.id = employee.department_id AND department_id IS NOT NULL);

SELECT country FROM customer WHERE NOT EXISTS(SELECT employee_id FROM employee);

SELECT employee_id FROM employee WHERE employee_id IN (SELECT sales_rep_id FROM customer WHERE lower(country) = 'japan') UNION (SELECT sales_rep_id FROM customer WHERE lower(country) = 'india');

SELECT sales_rep_id FROM customer WHERE exists(SELECT employee_id FROM employee WHERE employee.employee_id = customer.sales_rep_id and customer.credit_rating > 6);

DELETE FROM orders WHERE not exists(SELECT id FROM orders WHERE total > 0 );