Download Sql
Problem title: Design database schema and populate the database with appropriate
datasets.
Believe you have got many resources and
system already. Please provide enough efforts for the solution of the following problems.
Database Schema for a Employee
employee(emp_id : integer, emp_name: string
department(dept_id: integer, dept_name: string
paydetails(emp_id : integer, dept_id: integer
integer, DOJ: date)
payroll(pay_id, emp_id : integer
For the above schema, perform the following
1. Create the tables with the appr
of the tables
2. List all the employee names who joined after 1st January 2013
2. List the individual amount paid to each 1st of august 2016
3. List the details of employees whose total salary is between $50,000 and $60,000
3. Give a count of how many employees are working in each department
Query 2:
SELECT emp_name,doj FROM employee,paydetails WHERE
employee.emp_id=paydetails.emp_id AND
doj>'2008-06-23';
Query 3:
SELECT employee.emp_id,emp_name, pay_id,pay_amount,pay_date from employee,payroll WHERE
employee.emp_id=payroll.emp_id AND
pay_date='2016-08-01';
Query 4:
SELECT employee.emp_id,emp_name, basic+addition-deduction AS TOTAL_SALARY FROM employee,paydetails WHERE
employee.emp_id=paydetails.emp_id AND
basic+addition-deduction>=50000 AND basic+addition-deduction<=60000;
Query 5:
SELECT dept_name , COUNT(DISTINCT emp_name) AS COUNT FROM employee,department,paydetails WHERE
employee.emp_id=paydetails.emp_id AND
department.dept_id=paydetails.dept_id
group by dept_name;
0 comments:
Post a Comment