Database Lab 03

Download All  Questions And SQL Files Import SQL To Your Database

Download Sql

Problem title: Design database schema and populate the database with appropriate

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


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


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

group by dept_name;

Share on Google Plus

About Ashadullah Shawon

I am Ashadullah Shawon. I am a Software Engineer. I studied Computer Science and Engineering (CSE) at RUET. I Like To Share Knowledge. Learn More: Click Here
    Blogger Comment
    Facebook Comment


Post a Comment