Question:
Create the tables below and insert necessary records (min 6.)
Employee (Empid, Empname, age, DOB, salary, city, Supervisor_no, Dno)
Department (Dno, Dname, mgrID, city)
Project (Pno, Pname, Ploc, Dno)
Works_on(Empid, Pno,no_of_hrs)
Constraints:
- Age of an employee between 18 and 58.
- Employee name should not be null.
- Project name must be unique.
Note:
In employee table, Dno is the foreign key which is referencing Department table and Supervisor_no is the foreign key which is referencing the same table(employee).
In Department table, mgrID is the foreign key which is referencing the Employee table.
In Project table, Dno is the foreign key which is referencing the Department table.
In works_on table, Empid referencing Employee table and Pno referencing Project table and together make a primary key.
Sql command to create Employee table and value insertion
Note: Attributes supervisor_no and Dno will inserted at the end after inserting values in the table
SQL command to create Department table and value insertion
SQL command to create project table and value insertion
SQL command to create works_on table and value insertion
Now inserting supervisor_no attribute and Dno attribute in employee table
Questions may raise “Why are we inserting these two attribute in employee table finally”
The attribute Dno is created after the employee table, so before creation of department table their no attribute like Dno in the database. so we cant reference that dno in the employee table before creation of department table.
similarly supervisor_no which referencing empid which is a self referencing its own table. while we insert values in the employee table for exampel for employee id 101 whose supervisor’s employee id is 105 where the data will not entered in the database therefore we cant insert any data in the employee table to avoid this we are doing this at last.
SQL command to update the employee table
SQL command to insert value for attributes supervisor_no and dno
The following SQL command are example to update the value to the attributes and update the remaining rows using the same command.
So far we have created a tables that are mentioned in the question. Now we can execute the following query further.
NOTE: According to the query you should inserting the values if you get output like no rows selected are not acceptable
- Count the number of employees working in the company.
SQL Command to count the number of employees working in the company.
Ouput for the following command
2. Find the total salary of the employee.
SQL Command
3. Display the average age of all the employees.
SQL Command
4. Display the DOB in the format ‘dd/mm/yy’.
SQL command
5. Add the constraint on the salary column that the salary of an employee should not be less than 15000.
SQL Command
Output
6. Retrieve the name of employees who do not work on any project.
SQL Command
Output
Note: you can use Minus operator or division operator to find the name of the employee. The above command is not apporpirate way to find the output for the given query.
7. Retrieve all the cities in the database.
SQL Command
Output
8. Retrieve the name of an employee who manages the department ‘Production’.
SQL Command
Output
9. List the employee details whose age is more than 50 and being assigned with project number 1.
SQL Command
Output
10. Retrieve the project numbers in which no employees been assigned.
SQL Command
Output
11. Retrieve the name of the managers who live in the same city where his/her department located.
SQL Command
Output
12. Retrieve the employee Id, name, age of all the employees and if they belong to any department then retrieve the dno and department name of the same. (Left outer join).
SQL Command
Output
13. Display the employee number, employee name and his/her immediate supervisor number, supervisor name of all the employees.
SQL Command
Output
