Consider the employee database Emp consisting of the following tables where the primary keys are underlined:

Problem Definition:

            Consider the employee database Emp consisting of the following tables where the primary keys are underlined:

            employee (employee_name, street, city)

            works (employee_name, company_name, salary)

            company (company_name, city)

            manages (employee_name, manager’s_name)

Write down the SQL expression for the following queries:

a)      Find the names, cities and salaries of all employees who work for Prime Bank Ltd.

b)      Find the total salaries of each company.

c)      Add a record in the database using a form.

d)     Display your result of query (a) on a report.

   Software Development Tools:

1.      XAMPP (Apache server & MySQL)

2.      PHP

3.      HTML & CSS

4.      Text Editor: Notepad++ / Sublime Text3

5.      Browser: Mozilla Firefox/ Google Chrome etc.

Database Design View

Figure 01: Employee Database Design View

      User Interfaces:

Figure 02: Employee Form

Figure 03: Employee Report

Figure 04: Company Form

Figure 05: Company Report

Figure 06: Works Form

Figure 07: Works Report

Figure 08: Manager Form

Figure 09: Manager Report

    SQL statements of the queries:

1.      SELECT `employee`.`employee_name`, `employee`.`city`, `works`.`salary` FROM `employee` LEFT JOIN `works` ON `works`.`employee_name` = `employee`.`employee_name` WHERE `employee`.`employee_name` = `works`.`employee_name` AND `works`.`company_name`='Prime Bank Ltd';

 Result of the Queries:

  Result of the Query (a)

Figure 10: Result of the Query (a) and Query (d)

    SQL statements of the queries:

2.      SELECT company_name, SUM(salary) AS total_salary FROM works GROUP BY company_name

 Result of the Queries:

  Result of the Query (a)

Figure 11: Result of the Query 02