Showing posts with label SQL Exercise. Show all posts
Showing posts with label SQL Exercise. Show all posts

Monday, 23 July 2018

Table creation in SQL solved exercise 1


Question:
Write SQL queries to create tables for company database as per the specification given below; [Note: Primary keys are underlined. If not, identify one.]

EMP(SSN, Name, Gender, Birthdate, Street, City, DNO, SuperSSN, Salary) – stores information about employees, with SSN as key. The DNO denotes the department to which they are assigned and the SuperSSN is the SSN of their supervisor and supervisor is one of the employees. Every employee has a supervisor (assume the CEO has a supervisor with a fictitious SSN). Gender can be one of either ‘M’ or ‘F’.

DEPT(DeptNo, DeptName, MgrSSN) – stores information about the departments such as the unique department number, a unique department name (Dname), and the SSN of the department manager (every department must have a manager).

PROJECTS(PNum,Pname, PLocation, PCost, DNO) – information about projects at the company, with unique Project number (PNO). The project has a name and a location and is controlled/managed by a department. PCost is the total cost for a project given that it should be in the range of 100000 to 1 crore.

WORKS(ESSN, PNO, Hours) – information about the projects that each employee works on and the number of hours they work on each project in a day. The number of hours cannot exceed 8 hours. An employee may work on more than one project.

Queries:
Notes:

  • To create all these tables you must follow certain order. The attributes of one table refers other tables (foreign key). We may consider the referring table as child and the referred table as parent. If you need to refer, then the parent table should exist. Hence, the following tables created without foreign key attributes and later we may use ALTER TABLE statement to add the foreign keys.
  • The datatype and size of a foreign key attribute should be same as the referred attribute. For example, for the first table EMP the datatype and size of DNO attribute should be same as DNUM attribute of DEPT table.
 

Queries
Description
CREATE TABLE EMP (
SSN Number(5) PRIMARY KEY,
Name Varchar(50),
Gender Char(1),
Birthdate Date,
Street Varchar(50),
City Varchar(50),
DNO Number(3),
SuperSSN Number(5),
Salary Number(9,2),
CHECK (Gender IN (‘M’, ‘F’)),
FOREIGN KEY (DNO) REFERENCES DEPT(DNUM),
FOREIGN KEY (SuperSSN) REFERENCES EMP(SSN));
SSN is the key.

Gender can have only two values ‘M’ or ‘F’.

DNO refers DNUM attribute of DEPT table.

SuperSSN refers SSN of same (EMP, Self reference) table, because supervisor is one of the employees.
CREATE TABLE DEPT (
DNUM Number(3) PRIMARY KEY,
DNAME Varchar(50) UNIQUE,
MGRSSN Number(5),
FOREIGN KEY (MGRSSN) REFERENCES EMP(SSN));
DNUM is the key attribute.

DNAME is not a key but should have unique values, ie., no duplicate values are permitted.

MGRSSN is the manager SSN refers SSN attribute of EMP table.
CREATE TABLE PROJECTS(
PNum Number(5) PRIMARY KEY,
Pname Varchar(50),
Plocation Varchar(50),
PCost Number(10,2)
DNO Number(3),
CHECK (PCost >= 100000 AND PCost <= 10000000),
FOREIGN KEY (DNO) REFERENCES DEPT(DNUM));
PNum is the key attribute.

Project cost should be in the range of 1 lakh to 1 crore.

DNO refers DNUM attribute of Dept table.
CREATE TABLE WORKS(
ESSN Number(5),
PNO Number(3),
HOURS Number(1),
PRIMARY KEY (ESSN,PNO),
CHECK (HOURS BETWEEN 1 AND 8),
FOREIGN KEY (ESSN) REFERENCES EMP(SSN));
FOREIGN KEY (PNO) REFERENCES PROJECTS(PNUM));
The combination of ESSN and PNO form the key (called table level primary key).

Number of hours should not exceed 8.

ESSN (Employee SSN) refers SSN attribute of EMP table.

PNO refers PNUM attribute of PROJECTS table.

***********

 






How to create tables using SQL?
Table creation with Foreign keys,
How to use CHECK constraint in Oracle SQL,
Self referencing tables in Oracle,
How to create tables with multiple foreign keys in Oracle?
 




Wednesday, 25 April 2018

Write SQL statment for each of the following requirement

Write SQL statement for each of the following requirement

Question:

Consider the following relational schema
EMPLOYEE (EmpNo, Name, DOB, DepNo)
DEPARTMENT (DepNo, DName, Location, Head)
JOBHISTORY (EmpNo, Position, StartDate, EndDate, Salary)
where
EmpNo is the primary key for table EMPLOYEE
DepNo is the primary key for table DEPARTMENT
(EmpNo, StartDate) is the composite primary key for table JOBHISTORY
Write an SQL statement for each of the following queries.
a) List all the employees (by name) who are working at the Finance department.
b) List all the employees who are working at the same department as Michael.
c) Find the number of employees currently working at each department (by name).
d) List all the employees (by Name) who are currently working as Software Engineer.
e) Count the average salary for all the employees who are currently working at the Information Technology department.

Solution:

a) List all the employees (by name) who are working at the Finance department.

Expected resultNames of employees of Finance department
Where to get? – from EMPLOYEE and DEPARTMENT tables
How?join both tables on DepNo attribute.
WHERE clause conditions – join condition Employee.DepNo = Department.DepNo  and Dname = ‘Finance’
Query:
SELECT Name FROM Employee, Department
WHERE Employee.DepNo = Department.DepNo
AND DName = ‘Finance’;

b) List all the employees who are working at the same department as Michael.

Expected resultNames of employees of Michael’s department
Where to get? – from EMPLOYEE table
How? – use sub-query.
WHERE clause conditions – filter Michael’s department using Name = ‘Michael’ in the sub-query.
Query:
SELECT Name FROM Employee
WHERE DepNo = (SELECT DepNo FROM Employee
WHERE Name = ‘Michael’);

c) Find the number of employees currently working at each department (by name).

Expected resultCount of employees at each department (individual count)
Where to get? – from EMPLOYEE and DEPARTMENT tables
How? – use join and aggregate function count().
WHERE clause conditions – join condition Employee.DepNo = Department.DepNo.
Any other clauses to be usedGROUP BY clause
Query:
SELECT DName, Count(*) FROM Employee, Department
WHERE Employee.DepNo = Department.DepNo
GROUP BY DName;

d) List all the employees (by Name) who are currently working as Software Engineer.

Expected resultNames of employees working as software engineers
Where to get? – from EMPLOYEE and JOBHISTORY tables
How? – use join.
WHERE clause conditions – join condition Employee.EmpNo = Jobhistory.EmpNo, Position = ‘Software Engineer’, and EndDate is NULL of empty.
Any other clauses to be used – No
Query:
SELECT Name FROM Employee, Jobhistory
WHERE Employee.EmpNo = Jobhistory.EmpNo
AND Position = ‘Software Engineer’
AND EndDate = ‘’;

e) Count the average salary for all the employees who are currently working at the Information Technology department.

Expected resultAverage salary of employees of Information Technology department
Where to get? – from EMPLOYEE, DEPARTMENT, and JOBHISTORY tables
How? – use join and aggregate function avg().
WHERE clause conditions – join condition Employee.DepNo = Department.DepNo, Employee.EmpNo = Jobhistory.EmpNo, DName = ‘Information Technology’ and EndDate is NULL or empty [we need to include only current salary. That means salary of current position].
Any other clauses to be used – No
Query:
SELECT Avg(*) FROM Employee, Department, Jobhistory
WHERE Employee.DepNo = Department.DepNo
AND Employee.EmpNo = Jobhistory.EmpNo
AND DName = ‘Information Technology’
AND EndDate = ‘’;


 ***********



Go to Natural join in DBMS page (SQL)
Go to Equi-join in DBMS page (SQL)
Go to Multiple choice questions in SQL/Relational algebra page
Go to Solved exercise 1 
Go to Solved exercise 2
Go to Solved exercise 3
 
 









Find candidate key and normalize the relation into 2nf and 3nf

Find candidate key and normalize the relation into 2nf and 3nf Question: A relation R is defined as follows. R = (name, stre...