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
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;


Download Coding Interview Book and Get More Tutorials for Coding and Interview Solution: Click Here

Download System Design Interview Book and Get More Tutorials and Interview Solution: Click Here

Do you need more Guidance or Help? Then Book 1:1 Quick Call with Me: Click Here

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

0 comments:

Post a Comment