CIST 1220: SQL Assign #11

(40 Points)

[Simple SubQueries Chap 8]

Using the NEW database provided(Employee.accdb See Blackboard to download this DB), write SELECT statements to get the following information(most answers should use Sub-Queries):

Database:

Sub Queries

Who(give their name) makes the most money in each building?

List all the employees, fname, lname and dept who make more money than the avg?

Give the person, lname and fname, and their account number, who has the most money in their Retirement Account.

Give a list of all employees(fname, lname and dept) who make the least amount of money in their building, but only if they work in building 400, 402 or 405.

Give a list of all the managers(fname, lname and dept). [Hint: Self Join]

Give a list of all employees(fname, lname and eid) who have their RetirementAccounts in Stocks.

Give me the person(fname and lname) that makes the most $$ and the person that makes the least. Use only one query(2 sub-queries)!

Give the employee(fname and lname) that make the most money in each dept.

SELECTFROM Employees" LIMIT 0,30 (Ctrl+Enter) FNAME LNAME Bill Mary DEPT BUILDING EID MANAGERSALARY RETIREACCTNUMBER RETIREAMOUNT RETIREACCT 29000 b412 34000 b242 37000 b219 42000a133 38500 a415 29000 b204 50000 a131 51000 C543 37000 a890 40000 C546 39000a417 41000a432 28000 a800 29500 C54 31200a102 12000 29000 50000 14000 32000 22000 120000 33000 26000 33000 18000 42000 17000 19000 69000 Turner Smith Twist Acct 401 402 Acct 401 BarbApplegate T 402 Fin 401 400 Fin 400 Underwood Fin 405 Acct 405 405 Acct 405 401 Acct 401 Fin 404 Fin 404 231 909 299 239 289 231 239 285 567 207 567 270 567 567 786 231 689 239 900 231 788 239 702 231 700 567 201 567 IT on stocks Tupple Patrick Porter stocks Tim Frank Cary Jerry Betty KimberlyPorter Ted Fin stocks mutualfunds stocks mutualfunds stocks stocks stocks mutualfunds stocks Davis IT Reid March Jensen Bakula Jones er IT Dave Bill Paula