Retrieve data from more than two tables

Question

I have five tables.

Employee(Fname,Lname,Empno(Primary),Bdate,Address,Salary,Dnumber(Foreign))
Department(Dname, Dnumber(Primary),Mgrno)
Location(Dnumber(Foreign),Dlocation)
Timesheet(Empno(foreign),Pnumber(Foreign),Hours_per_day)
Project(Pname,Pnumber(Primary),Location,Dnumber(Foreign))

How can I retrieve:

a. For project location 'ljk' list the project number, the controlling Department and the Department Manager's Fname, Lname and Address.

b. Find the total salary of all employees in the 'F' department as well as the maximum, minimum and average of the salary in this department.

c. For each department, retrieve the Department number(Dnumber), the number of the employees and the average salary in the department.

For the problem c. I am able to retrieve data only about the the Department, Department number and number of employees. I am not sure how to add the average salary column in the result table.

For the other two part. I think it will need joining of more than 2 tables. But, I am not sure if that is possible.

sample: Employee:

+-------+----------+-------+------------+---------+--------+---------+
| Fname | Lname    | Empno | Bdate      | Address | Salary | Dnumber |
+-------+----------+-------+------------+---------+--------+---------+
| g     | a        |   755 | 1986-09-09 | how     |   6598 |     100 |
| d     | v        |   796 | 1969-12-03 | e       |      2 |     101 |
| r     | n        |   850 | 1979-12-01 | a       |     10 |     100 |
| n     | h        |   879 | 1979-12-02 | b       |      8 |     101 |
| k     | k        |   888 | 1979-12-03 | c       |      6 |     102 |
+-------+----------+-------+------------+---------+--------+---------+

Department:

+-----------+---------+-------+
| Dname     | Dnumber | Mgrno |
+-----------+---------+-------+
| F         |     100 |   850 |
| ll        |     101 |   879 |
| M         |     102 |   888 |
+-----------+---------+-------+

Project:

+----------+---------+----------+---------+
| Pname    | Pnumber | Location | Dnumber |
+----------+---------+----------+---------+
| a        |      79 | ljk      |     101 |
| a        |      89 | ljk      |     100 |
| mardf    |      90 | kjk      |     102 |
+----------+---------+----------+---------+

Timesheet:

+-------+---------+---------------+
| Empno | Pnumber | Hours_per_day |
+-------+---------+---------------+
|   850 |      89 |             6 |
|   888 |      90 |             6 |
|   879 |      79 |             9 |
+-------+---------+---------------+

Location:

+---------+-----------+
| Dnumber | Dlocation |
+---------+-----------+
|     100 | east      |
|     101 | west      |
|     102 | north     |
+---------+-----------+

What I have tried:

I have tried this for the part (c)

SELECT Department.Dname, 
    Department.Dnumber, 
    COUNT(*) AS 'TOTAL EMPLOYEES' 
FROM Department 
INNER JOIN Employee 
    ON Department.Dnumber = Employee.Dnumber 
GROUP BY Department.Dnumber;

I am not sure how to add the average salary column here.

http://sqlfiddle.com/#!9/a512f1

CREATE TABLE Department(
    Dname varchar(10),
    Dnumber int(5),
    Mgrno int(5),
    PRIMARY KEY(Dnumber)
);

CREATE TABLE Employee(
    Fname varchar(20),
    Lname varchar(20),
    Empno int(5),
    Bdate date,
    Address varchar(10),
    Salary float(5),
    Dnumber int(5),
    PRIMARY KEY(Empno),
    FOREIGN KEY(Dnumber) REFERENCES Department(Dnumber)
);


CREATE TABLE Location(
    Dnumber int(5),
    Dlocation varchar(10),
    PRIMARY KEY(Dlocation),
    FOREIGN KEY(Dnumber) REFERENCES Department(Dnumber)
);

CREATE TABLE Project(
    Pname varchar(10),
    Pnumber int(5),
    Location varchar(10),
    Dnumber int(5),
    PRIMARY KEY(Pnumber),
    FOREIGN KEY(Dnumber) REFERENCES Department(Dnumber)
);

CREATE TABLE Timesheet(
    Empno int(5),
    Pnumber int(5),
    Hours_per_day int(5),
    FOREIGN KEY(Empno) REFERENCES Employee(Empno),
    FOREIGN KEY(Pnumber) REFERENCES Project(Pnumber)
);

UkFLSUI · Accepted Answer

Try these queries for all your particular problems.

And Here's the SQL Fiddle: http://sqlfiddle.com/#!9/e4ad1a/2/2

For problem a:

SELECT P.Pnumber as 'Project Number', 
D.Dname as Department,
E.Fname as 'Manager\'s Fname',
E.Lname as 'Manager\'s Lname',
E.Address as 'Manager\'s Address'
FROM Project P 
INNER JOIN Department D ON P.Dnumber = D.Dnumber
INNER JOIN Employee E ON P.Dnumber = E.Dnumber
WHERE P.Location = 'ljk';

Output:

| Project Number | Department | Manager's Fname | Manager's Lname | Manager's Address |
|----------------|------------|-----------------|-----------------|-------------------|
|             79 |         ll |               d |               v |                 e |
|             79 |         ll |               n |               h |                 b |
|             89 |          F |               g |               a |               how |
|             89 |          F |               r |               n |                 a |

For problem b:

SELECT SUM(Salary) as 'Total Salary',
MAX(Salary) as 'Maximum Salary',
MIN(Salary) as 'Minimum Salary',
AVG(Salary) as 'Average Salary'
FROM Employee E
INNER JOIN Department D ON E.Dnumber = D.Dnumber
WHERE D.Dname = 'F';

Output:

| Total Salary | Maximum Salary | Minimum Salary | Average Salary |
|--------------|----------------|----------------|----------------|
|         6608 |           6598 |             10 |           3304 |

For problem c:

SELECT D.Dname as 'Department Name',
D.Dnumber as 'Department Number',
COUNT(DISTINCT E.Empno) as 'No. of Employees',
AVG(E.salary) as 'Average Salary'
FROM Department D
INNER JOIN Employee E ON D.Dnumber = E.Dnumber
GROUP BY D.Dnumber;

Output

| Department Name | Department Number | No. of Employees | Average Salary |
|-----------------|-------------------|------------------|----------------|
|               F |               100 |                2 |           3304 |
|              ll |               101 |                2 |              5 |
|               M |               102 |                1 |              6 |

Retrieve data from more than two tables

Answers (2)

Related Questions