Attempting to adjust and create SQL queries 4,9, and 10.

#4 needs to be only the highest and lowest account holders not all

#9 has 2 different approaches with the top one not running due to the EXTRACT keyword that someone showed us. So the bottom approach would be more helpful.

#10 has the count for each number required for the AVG but I’m uncertain on how to calculate the AVG with the subqueries.

**Note 9 and 10 has two versions of code too, the top code was provided for me but doesn’t run the bottom versions are the approach I’ve tried.

Then, struggling to Convert SQL queries 1, 2, updated 4, 6, 7, 9, 10 into Access queries due to syntax. SQL or Design view would be helpful for Access work.

This is what I have thus far.

Thank you!

–1. Display the sum of accounts that are of a special type.
SELECT(SELECT DISTINCT COUNT(TRUST.[Account ID]) AS TC
FROM (ACCOUNT JOIN TRUST ON ACCOUNT.[Account ID] = TRUST.[Account ID])) +
(SELECT DISTINCT COUNT(ESTATE.[Account ID]) AS EC
FROM (ACCOUNT JOIN ESTATE ON ACCOUNT.[Account ID] = ESTATE.[Account ID])) AS ‘SUM OF SPECIAL ACCOUNTS’;

–2. Show the count of accounts owned by customers whose last name begins with a letter within the first half of the alphabet.
SELECT COUNT(DISTINCT ACCOUNT.[Account ID]) AS ‘Total Accounts’, CUSTOMER.[Customer Last Name]
FROM CUSTOMER INNER JOIN ACCOUNT ON CUSTOMER.[Customer ID] = ACCOUNT.[Customer ID]
WHERE CUSTOMER.[Customer Last Name] LIKE ‘[A-M]%’
GROUP BY [Customer Last Name];

–4. Display the names of the customers and the bank they use with hours of operation, that hold the highest and lowest number of accounts?
SELECT DISTINCT CUSTOMER.[Customer First Name], CUSTOMER.[Customer Last Name], BANK.[Bank ID], BANK.[Bank HQ Hours]
FROM (BANK INNER JOIN CUSTOMER ON BANK.[Bank ID] = CUSTOMER.[Bank ID]) INNER JOIN ACCOUNT ON CUSTOMER.[Customer ID] = ACCOUNT.[Customer ID];

–6. List the names of the employees that supervise another employee. Then show the ratio (in the form of a percent) of supervisors to employees.
SELECT EMPLOYEE.[Employee Name]
FROM EMPLOYEE
where EMPLOYEE.[Employee ID] in(SELECT DISTINCT [Supervisor ID] FROM EMPLOYEE);
SELECT(1.0*(SELECT COUNT(DISTINCT EMPLOYEE.[Supervisor ID])
FROM EMPLOYEE)/(SELECT COUNT(*) FROM EMPLOYEE))*100 AS ‘Percent of Supervisors to Total Employees’;

–7. What percent of accounts that are regular accounts (not of special account types)?
SELECT(1.0*(SELECT COUNT([Account Type]) FROM ACCOUNT WHERE [Account Type] != ‘T-E’)/(SELECT COUNT(*) FROM ACCOUNT))*100;

–9. Display how many of each account type do(es) the customer(s) with the most accounts have? Then list the CEO of the bank the(se) customer(s) use.
SELECT ACCOUNT.[Account ID], ACCOUNT.[Account Type]
FROM ACCOUNT
WHERE [Customer ID] IN(SELECT CUSTOMER.[Customer ID] FROM CUSTOMER INNER JOIN ACCOUNT ON CUSTOMER.[Customer ID] = ACCOUNT.[Customer ID]
GROUP BY CUSTOMER.[Customer ID]
HAVING COUNT(*)=(SELECT *FROM(SELECT COUNT(*)
FROM CUSTOMER INNER JOIN ACCOUNT ON CUSTOMER.[Customer ID]=ACCOUNT.[Customer ID]
GROUP BY CUSTOMER.[Customer ID]
ORDER BY COUNT(*) DESC));

–10. What is the average number of trust accounts open within 2016 through 2018, and estate accounts?
SELECT ROUND(AVG(TOTAL),2) ‘Average total’
FROM(SELECT COUNT(*) AS TOTAL FROM ACCOUNT INNER JOIN TRUST ON ACCOUNT.[Account ID]=TRUST.[Account ID]
WHERE EXTRACT(YEAR FROM [Account Open Date]) between ‘2016-01-01’ and ‘2018-12-31’)

SELECT COUNT(TRUST.[ACCOUNT ID])
FROM ACCOUNT INNER JOIN TRUST ON ACCOUNT.[ACCOUNT ID]=TRUST.[ACCOUNT ID]
WHERE ACCOUNT.[Account Open Date] BETWEEN ‘2016-01-01’ AND ‘2016-12-31’;
SELECT COUNT(TRUST.[ACCOUNT ID])
FROM ACCOUNT INNER JOIN TRUST ON ACCOUNT.[ACCOUNT ID]=TRUST.[ACCOUNT ID]
WHERE ACCOUNT.[Account Open Date] BETWEEN ‘2017-01-01’ AND ‘2017-12-31’;
SELECT COUNT(TRUST.[ACCOUNT ID])
FROM ACCOUNT INNER JOIN TRUST ON ACCOUNT.[ACCOUNT ID]=TRUST.[ACCOUNT ID]
WHERE ACCOUNT.[Account Open Date] BETWEEN ‘2018-01-01’ AND ‘2018-12-31’;

SELECT COUNT(ESTATE.[Account ID])
FROM ACCOUNT INNER JOIN ESTATE ON ACCOUNT.[ACCOUNT ID]=ESTATE.[ACCOUNT ID]
WHERE ACCOUNT.[Account Open Date] BETWEEN ‘2016-01-01’ AND ‘2016-12-31’;
SELECT COUNT(ESTATE.[ACCOUNT ID])
FROM ACCOUNT INNER JOIN ESTATE ON ACCOUNT.[ACCOUNT ID]=ESTATE.[ACCOUNT ID]
WHERE ACCOUNT.[Account Open Date] BETWEEN ‘2017-01-01’ AND ‘2017-12-31’;
SELECT COUNT(ESTATE.[ACCOUNT ID])
FROM ACCOUNT INNER JOIN ESTATE ON ACCOUNT.[ACCOUNT ID]=ESTATE.[ACCOUNT ID]
WHERE ACCOUNT.[Account Open Date] BETWEEN ‘2018-01-01’ AND ‘2018-12-31’;