Account Table Project Table Job Table Acct# Acct Balance Project# Project# Manag
ID: 3850706 • Letter: A
Question
Account Table
Project Table
Job Table
Acct#
Acct Balance
Project#
Project#
Manager
Job#
Job#
Description
10067
$8,354,778
354
354
Lopez
1
1
Remodel
10068
$6,014,987
354
687
Jones
2
2
Expansion
10151
$4,654,867
687
721
Van Man
2
10203
$983,577
721
Use the data above to write queries in SQL.
1. Write a query to show a list of account numbers and account balances.
2. Write a query to show a list of jobs and account balances.
3. Write a query to show a list of account balances for job# 2. (show job #)
4. Write a query to show the project description and total account balance for all projects that are job# 2.
5. Write a query to show the total for all account balances.
6. Write a query to show the total number of accounts.
7.Write a query showing the total account balance by jobs.
8. Write a query showing the total account balance by manager from A to Z.
9. Write a query showing the total account balance for projects handled by Lopez.
10. Write a query showing a list of accounts with balances greater than $5 Million (show balance).
Account Table
Project Table
Job Table
Acct#
Acct Balance
Project#
Project#
Manager
Job#
Job#
Description
10067
$8,354,778
354
354
Lopez
1
1
Remodel
10068
$6,014,987
354
687
Jones
2
2
Expansion
10151
$4,654,867
687
721
Van Man
2
10203
$983,577
721
Explanation / Answer
1. SELECT ACCT#, ACCT_BALANCE FROM ACCOUNT;
2. SELECT ACC_BALANCE, JOB# FROM ACCOUNT, PROJECT WHERE ACCOUNT.PROJECT#=PROJECT.PROJECT#;
3. SELECT ACC_BALANCE, JOB# FROM ACCOUNT, PROJECT WHERE ACCOUNT.PROJECT#=PROJECT.PROJECT# AND JOB#=2;
4. SELECT ACC_BALANCE, DESCRIPTION FROM ACCOUNT, PROJECT, JOB WHERE ACCOUNT.PROJECT#=PROJECT.PROJECT# AND PROJECT.JOB#=JOB.JOB# AND JOB#=2;
5. SELECT SUM(ACCT_BALANCE) AS TOTAL FROM ACCOUNT;
6. SELECT COUNT(ACCT#) FROM ACCOUNT;
7. SELECT SUM(ACC_BALANCE), JOB# FROM ACCOUNT, PROJECT WHERE ACCOUNT.PROJECT#=PROJECT.PROJECT# GROUP BY JOB#;
8. SELECT SUM(ACC_BALANCE), MANAGER FROM ACCOUNT, PROJECT WHERE ACCOUNT.PROJECT#=PROJECT.PROJECT# GROUP BY MANAGER ORDER BY MANAGER;
9. SELECT SUM(ACC_BALANCE), MANAGER FROM ACCOUNT, PROJECT WHERE ACCOUNT.PROJECT#=PROJECT.PROJECT# WHERE MANAGER='LOPEZ';
10. SELECT ACCT_BALANCE FROM ACCOUNT WHERE ACCT_BALANCE>5000000;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.