SQL: Queries for Schemas ( Employee, Department, Students, Project )

Consider the following relational database:

  • Employee(empid, gender, name, dob, salary, address, deptno, start_date)
  • Department(dpetno, name)
  • Students(std_id, gender, name, dob, projectno, deptno)
  • Project(projectno, name, location)


Write SQL queries for the following based on the schema given:

  1. Display the name of employees working in 'BBM' Department.
  2. List details of employees earning salary above average salary.
  3. List details of all students along with the name of project they are involved in. Also display the name of the department.
  4. Decrease salary by 10% of all employees whose salary is more than 10,000.
  5. Insert (empid, name, dob, salary) to the table Employee.

a. Display the name of employees working in the 'BBM' Department.


SELECT e.name
FROM   Employee e
       INNER JOIN Department d
               ON e.deptno = d.deptno
WHERE  d.name = 'BBM';

 

b. List details of employees earning a salary above average salary.


SELECT *
FROM   Employee
WHERE  salary > (SELECT AVG(salary)
                 FROM   Employee);

 

c. List details of all students along with the name of the project they are involved in. Also, display the name of the department.


SELECT s.std_id,
       s.gender,
       s.name,
       s.dob,
       p.name,
       d.name
FROM   Students s
       JOIN Project p
         ON s.projectno = p.projectno
       JOIN Department d
         ON s.deptno = d.deptno;

 

d. Decrease salary by 10% of all employees whose salary is more than 10,000.


UPDATE Employee
SET    salary = salary * 0.9
WHERE  salary > 10000;

------ OR --------

UPDATE Employee
SET    salary = (salary - (salary * 0.1))
WHERE  salary > 10000;

 

e. Insert (empid, name, dob, salary) to the table Employee.


INSERT INTO Employee
            (empid,name,dob,salary)
VALUES      (1004, 'Ram Prasad', '1998-04-05', 80000);