Scribd
Upload
2K views3 pages

SQL Database Management Tasks

The document describes creating and querying tables in a MySQL database. It includes instructions to create 4 tables - Employee, Works, Company, and Manages - with primary and foreign keys defined. It then provides 8 queries to: add a column to the Employee table; find manager names that work for 2 specific companies; find employee details that work for a specific company and earn over a salary; find employees that live in the same city as the company; and calculate salary statistics and payroll for each company.

Uploaded by

Harshitha
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
2K views3 pages

SQL Database Management Tasks

The document describes creating and querying tables in a MySQL database. It includes instructions to create 4 tables - Employee, Works, Company, and Manages - with primary and foreign keys defined. It then provides 8 queries to: add a column to the Employee table; find manager names that work for 2 specific companies; find employee details that work for a specific company and earn over a salary; find employees that live in the same city as the company; and calculate salary statistics and payroll for each company.

Uploaded by

Harshitha
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

PRACTICAL 4

Create the following tables, enter at least 5 records in each table and answer the queries given
below.
Employee ( Employee_ID, Person_Name, Street, City )
Works ( Employee_ID, Company_ID, Salary )
Company( Company_ID, Company_Name, City )
Manages ( Employee_ID, Manager_ID)

a) Identify primary and foreign keys.

PRIMARY KEYS:

Employee Company Works Manages


Emp_ID Emp_ID Emp_ID Emp_ID
Company_ID Company_ID Manager_ID
FOREIGN KEYS:

Employee Company Works Manages


Emp_ID Emp_ID Emp_ID
Company_ID Manager_ID

b) Alter table employee, add a column “Email” of type varchar(20).


c) Find the name of all managers who work for both Samba Bank or NCB Bank.

mysql> select Person_Name from Employee,Manages,Works,Company


-> where (Company_Name='Samba Bank'or
-> Company_Name='NPB Bank') AND
-> works.Company_ID=Company.Company_ID AND
-> works.Emp_ID=Manages.Manager_ID AND
-> Employee.Emp_ID=works.Emp_ID;

d) Find the names, street address and cities of residence and salary of all employees
who work for “Samba Bank” and earn more than Rs.10,000.

mysql> select * from Employee

-> where Emp_ID IN


-> (select Emp_ID from Company,works where Company_Name="Samba Bank" and
salary>10000);
e) Find the names of all employees who live in the same city as the company for which
they work.

mysql> Select Person_Name from Employee,Company,Works

-> where Employee.City=Company.City and Employee.Emp_ID=Works.Emp_ID and


Company.Company_ID=Works.Company_ID;
f) Find the highest salary, lowest salary and average salary paid by each company.

g) Find the sum of salary and number of employees in each company.


h) Find the name of the company that pays the highest payroll.

You might also like