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.

***********

 

Go to CREATE TABLE page

Go to SQL home page

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?