SQL_test0.
Below there are 6 problems for a simple 2-table database.
 

Company Database:  Tables:  department (d_name,d_id) - 20 rows employee (e_name, e_address, e_salary, e_d_id) - 500 rows

Q1. There are 20 rows in department table and 500 rows in employee table. How many rows will be returned by this request:
          select * from department, employee
Q2. Make a list of all the employees: name, address, department (if assigned) sorted by department and alphabetically inside departments.
Q3. Make a list of departments and average salary for each department.
Q4. For every department show the highest salary (except departments where average salary is more than $50,000/year).
Q5. For every department show the highest paid employee (and his salary).
Q6. Show the dept. name and average salary for the department which has the highest average salary.
Answers:
A1. There are 20 rows in department table and 500 rows in employee table. How many rows will be returned by this request:
          select * from department, employee

       20*500=10000

A2. Make a list of all the employees: name, address, department (if assigned) sorted by department and alphabetically inside departments.

select e_name, e_address, d_name
from department, employee
where e_d_id *= d_id
order by d_name, e_name

A3. Make a list of departments and average salary for each department.

select d_name, avg(e_salary)
from department, employee
where e_d_id = d_id
group by d_name

A4. For every department show the highest salary (except departments where average salary is more than $50,000/year).

select d_name, max(e_salary)
from department, employee
where e_d_id = d_id
group by d_name
having avg(e_salary) <= $50000

A5. For every department show the highest paid employee (and his salary).

select d_name, e_name, e_salary
from department, employee
where e_d_id = d_id
group by d_name
having e_salary = max(e_salary)

/* note: will not work on MS SQL server */

or

select d_name, e_name, e_salary
from department d, employee e
where e_d_id = d_id
  and e_salary = ( select max(e_salary) from employee where e_d_id = d.d_id group by d.d_id)
 

A6. Show the dept. name and average salary for the department which has the highest average salary.

select d_name, avg(e_salary)
from department, employee
where e_d_id = d_id
group by d_name
having avg(e_salary) = max(avg(e_salary))