[SQL] SQL Practice #3

Oracle Practice # 10 - Aggregation

1. Display the lowest last name and the highest last name alphabetically (Customers table).

SELECT MIN(last_name), MAX(last_name)
FROM ACDB_CUSTOMERS;

 

2. Display the total number of states (allowing redundancy) and the number of distinct states (Customers table).

SELECT COUNT(state), COUNT(DISTINCT state)
FROM ACDB_CUSTOMERS;​

 

3. Display the lowest, highest, and average monthly discount (Customers table).

SELECT MIN(monthly_discount), MAX(monthly_discount), AVG(monthly_discount)
FROM ACDB_CUSTOMERS;

4. Display the names of customers whose monthly discounts are larger than the average monthly discount (Customer table).

SELECT first_name, last_name
FROM ACDB_CUSTOMERS
WHERE monthly_discount > (SELECT AVG(monthly_discount) FROM ACDB_CUSTOMERS);​

 

 

Oracle Practice #11 – GROUP BY

1. Display the state and the number of customers for each state in the descending order by the number of customers (Customers table).

SELECT state, COUNT(*) as cnt
FROM ACDB_CUSTOMERS
GROUP BY state
ORDER BY cnt DESC;

 

2. For each internet package (Customers table)
A. Display the package number and the number of customers for each package number.
B. Modify the query to display the package number and number of customers for each package number, only for the customers  whose monthly discount is greater than 20.

SELECT pack_id, COUNT(*) as cnt
FROM ACDB_CUSTOMERS
WHERE monthly_discount > 20
GROUP BY pack_id;

 

 

Oracle Practice #12 - HAVING

1. For each internet package, Display the package number and number of customers for each package number, only for the packages with more than 100 customers (Customers table)

SELECT pack_id, COUNT(*)
FROM ACDB_CUSTOMERS
GROUP BY pack_id
HAVING COUNT(*) > 100;

 

2. States and the lowest monthly discount (Customers table)
A. Display the state and the lowest monthly discount for each state.
B. Display the state and lowest monthly discount for each state, only for states where the lowest monthly discount
is greater than 10

SELECT state, MIN(monthly_discount)
FROM ACDB_CUSTOMERS
GROUP BY state
HAVING MIN(monthly_discount) > 10;

 

3. Display the internet speed and number of package for each internet speed where monthly payment is
larger than $50 (i.e., > 50), only for the internet speeds with more than 3 packages.

SELECT speed, count(*)
FROM ACDB_PACKAGES
WHERE monthly_payment > 50
GROUP BY speed
HAVING count(*) > 3;

 

 

Oracle Practice #13 – Outer Join

Customers and internet packages (Customers and Packages tables)
A. Display the first name, last name, internet speed and monthly payment for all customers. Use INNER JOIN to solve this exercise.

 

SELECT c.first_name, c.last_name, p.speed, p.monthly_payment
FROM ACDB_CUSTOMERS c, ACDB_PACKAGES p
WHERE c.pack_id = p.pack_id;

B. Modify last query to display all customers, including those without any internet package.

SELECT c.first_name, c.last_name, p.speed, p.monthly_payment
FROM ACDB_CUSTOMERS c
LEFT OUTER JOIN ACDB_PACKAGES p on c.pack_id = p.pack_id;

C. Modify last query to display all packages, including those without any customers.

SELECT c.first_name, c.last_name, p.speed, p.monthly_payment
FROM ACDB_CUSTOMERS c
RIGHT OUTER JOIN ACDB_PACKAGES p on c.pack_id = p.pack_id;

D. Modify last query to display all packages and all customers.

SELECT c.first_name, c.last_name, p.speed, p.monthly_payment
FROM ACDB_CUSTOMERS c
FULL OUTER JOIN ACDB_PACKAGES p on c.pack_id = p.pack_id;