1. Show the last name, first name, and the credit_limit for all customers who reside in the city of Grant and have a credit_limit greater than $1,200.

select last, first, credit_limit from customer where city ='Grant' and credit_limit > 1200;

LAST FIRST CREDIT_LIMIT
-------- ------- ------------
Samuels Ann 1500
Williams Al 1500

2. Show the names of all the employees who have the character string “er” as part of their job title.

select ename from emp where job like '%er%';

ENAME
------------
Smith
Jones
Blake
Walker
Clark
Adams
James
Miller

3. For every sales rep who has customers, show the sales rep number and the largest available credit (with the column heading MAX_AVAIL_CREDIT) among his/her customers. Exclude any customer who does not have a sales rep assigned yet. The results should be sorted in descending order of MAX_AVAIL_CREDIT.

select slsrep_number, max(credit_limit-balance) as MAX_AVAIL_CREDIT from customer where slsrep_number is not null group by slsrep_number order by max(credit_limit-balance) desc;

SLSREP_NUMBER MAX_AVAIL_CREDIT
------------- ---------------- 6 1478.50 12 1097.25 3 182.25

4. Count the number of orders that have a total order value of more than $500.

select count(sum(number_ordered*quoted_price)) from order_line group by order_number having sum(number_ordered*quoted_price)>500;

COUNT(SUM(NUMBER_ORDERED*QUOTED_PRICE))
---------------------------------------
3 5. List the number and name of every department that has no employees currently. (Exclude any employee whose department is not