Task 1: SQL
Question: 2.1
Database Creation and Population
Ans: Table Creation In the Department table “deptno” is set as primary key with number datatype, by running this query able is created with 3 fields one of them is primary key.
Table: DEPT
Table Creation
In the Employee table “empno” is set as primary key with number datatype and “deptno” from
Department table is set as foreign key in this table, by running this query table is created with 8 fields
one of them is primary key and one is foreign key. Check constraint is set so salary should be between
1000 and 10000.
Table: EMP
Altering table to add Manager number as foreign key in Employee Table
Inserting Data (DEPT Table)
Data is inserted into Department table using INSERT ALL query so multiple rows can be inserted at once,
data that is to be inserted is written separated by “,” so and if there is nothing to be inserted in a field
we can write “null” in that.
Table View
This query is used to display all content of the table.
Read More
Inserting Data (EMP Table)
Data is inserted into Employee table using INSERT query, there are multiple rows to be inserted so we can also use INSERT ALL query. We have displayed the entire table by using SELECT * query at the end.
Question: 2.2
Find employees (ENAME) whose job (JOB) is either ANALYST or PROGRAMMER.
Ans. Select “Ename” query is run from Employee table to display Employees names only and WHERE condition is used to display list of only specific types of jobs.
Question: 2.3
Find employees (ENAME) whose salary (SAL) is higher than their manager’s salary. Order by employee name.
Ans.
Multiple Select fields are used and 2 new fields “Manager” and “Manager Salary” are also created and these are selected from “EMP” table, WHERE condition is used in second part of the query in which we have mentioned condition that employee salary should be higher than their manager’s salary.
Question: 2.4
Find departments (DNAME) in which all employees earn more than 4000.
Ans.
Multiple Select fields are used and these are selected from Employee and Department table, WHERE condition is used in second part of the query in which we are displaying Employees and their Department names whose salary is higher than 4000.
Question: 2.5
Find the department with the largest number of employees. Show DNAME, DEPTNO, and the number of employees.
Ans.
Department number and Department names are queried from Employee and Department tables and number of employees are also displayed in that department using the MAX query so department with most employees is displayed.
Question: 2.6
Create a view MANAGERS for employees who manage other employees. Include all EMP columns.
Ans.
All fields from Employee table are queried and employees who are managing other employees are displayed using WHERE condition.
Task 2: ER
(a) draw an ER diagram that captures the above information;
Ans.
With the provided information 6 different tables are plotted with multiple fields.
(b) transform the ER diagram in (a) into a set of relation schema and indicate primary and foreign keys.
Ans.
With the provided information 8 different tables are made all having their primary keys. Some of the tables are also connected to other table with foreign keys. Rental order table is connected to 3 tables so there are 3 foreign keys in that table.
