NAME: SAKINA BOHRA CLASS: B.
TECH 2ND YEAR (AI&DS) 4TH SEM
ASSIGNMENT(AI 248)
CREATING THE
DATABASE - CREATE
DATABASE COMPANY;
CREATING THE
TABLES -
CREATE TABLE EMPLOYEE
(Fname VARCHAR(15) NOT NULL,
Minit CHAR,
Lname VARCHAR(15) NOT NULL,
Ssn CHAR(9) NOT NULL,
Bdate DATE,
Address VARCHAR(30),
Sex CHAR,
Salary DECIMAL(10,2),
Super_ssn CHAR(9),
Dno INT NOT
NULL, PRIMARY KEY(Ssn));
CREATE TABLE DEPARTMENT
(Dname VARCHAR(15) NOT NULL,
Dnumber INT NOT NULL,
Mgr_ssn CHAR(9) NOT NULL,
Mgr_start_date
DAT
E, PRIMARY KEY(Dnumber),
UNIQUE(Dname),
FOREIGN KEY(Mgr_ssn) REFERENCES EMPLOYEE(Ssn));
CREATE TABLE DEPT_LOCATIONS
(Dnumber INT NOT NULL,
Dlocation VARCHAR(15) NOT NULL,
PRIMARY KEY(Dnumber,Dlocation),
FOREIGN KEY(Dnumber) REFERENCES DEPARTMENT(Dnumber));
CREATE TABLE
PROJECT
(Pname VARCHAR(15) NOT NULL,
Pnumbe INT NOT NULL,
r VARCHAR(15),
Plocatio
n
Dnum INT NOT NULL,
PRIMARY
KEY(Pnumber),
UNIQUE(Pname),
FOREIGN KEY(Dnum) REFERENCES
DEPARTMENT(Dnumber)); CREATE TABLE WORKS_ON
(Essn CHAR(9) NOT NULL,
Pno INT NOT NULL,
PRIMARY KEY(Essn,Pno),
FOREIGN KEY(Essn) REFERENCES
EMPLOYEE(Ssn), FOREIGN KEY(Pno)
REFERENCES PROJECT(Pnumber));
CREATE TABLE
DEPENDENT CHAR(9) NOT NULL,
(Essn
Dependent_name VARCHAR(15) NOT NULL,
Sex CHAR,
Bdate DATE,
Relationship VARCHAR(8),
PRIMARY KEY(Essn,Dependent_name),
FOREIGN KEY(Essn) REFERENCES EMPLOYEE(Ssn));
INSERTING VALUES IN THE RESPECTIVE TABLES -
INSERT INTO EMPLOYEE (Fname,Minit,Lname,Ssn,Bdate,Address,Sex,salary,super_ssn,Dno)
VALUES ('John','B','Smith','123456789','1965-01-09','731 Fondren, Houston,
TX','M','30000','333445555',5),
('Franklin','T','JohWong','333445555','1955-12-08','638 Voss, Houston,
TX','M','40000','888665555',5),
('Alicia','J','Zelaya','999887777','1968-07-19','3321 Castel, Spring,
TX','F','25000','987654321',4),
('Jennifer','S','Wallace','987654321','1941-06-20','291 Berry, Bellaire,
TX','F','43000','888665555',4),
('Ramesh','K','Narayan','666884444','1962-09-15','975 Fire Oak,Humble,TX','M','38000','333445555',5),
('Joyace','A','English','453453453','1972-07-31','5631 Rice, Houston, TX','M','25000','333445555',5),
('Ahmed','V','Jabbar','987987987','1969-03-29','980 Dallas, Houston, TX','M','25000','987654321',4),
('James','E','Borg','888665555','1937-11-10','450 Stone, Houston, TX','M','55000','NULL',1);
INSERT INTO DEPARTMENT VALUES ('Research',5,'333445555','1988-05-22'),
('Administration',4,'987654321','1995-01-01'),
('Headquarters',1,'888665555','1981-06-19');
INSERT INTO DEPT_LOCATIONS VALUES (1,'Houston'),(4,'Stafford'),
(5,'Bellaire'), (5,'Sugarland'), (5,'Houston');
INSERT INTO PROJECT VALUES ('ProductX',1,'Bellaire',5),
('ProductY',2,'Sugarland',5),('ProductZ',3,'Houston',5),
('Computerization',10,'Stafford',4),('Reorganization',20,'Houston',1),
('Newbenefits',30,'Stafford',4);
INSERT INTO WORKS_ON VALUES ('123456789',1,32.5), ('123456789',2,7.5),
('666884444',3,40.0), ('453453453',1,20.0), ('453453453',2,20.0),
('333445555',2,10.0), ('333445555',3,10.0), ('333445555',10,10.0),
('333445555',20,10.0), ('999887777',30,30.0), ('999887777',10,10.0),
('987987987',10,35.0), ('987987987',30,5.0), ('987654321',30,20.0),
('987654321',20,15.0), ('888665555',20,15.0);
INSERT INTO DEPENDENT
(Essn,Dependent_name,Sex,Bdate,Relationship) VALUES
('333445555','Alice','F','1986-04-05','Daughter'),
('333445555','Theodore','M','1983-10-25','Son'),
('333445555','Joy','F','1958-05-03','Spouse'),
('987654321','Abner','M','1942-02-28','Spouse'),
('123456789','Michael','M','1988-01-04','Son'),
('123456789','Alice','F','1988-12-30','Daughter'),
('123456789','Elizabeth','F','1967-05-05','Spouse');
DISPLAYING ALL THE TABLES-
SELECT * FROM EMPLOYEE;
SELECT * FROM DEPARTMENT;
SELECT * FROM DEPT_LOCATIONS;
SELECT * FROM PROJECT;
SELECT * FROM DEPENDENT;
SELECT * FROM WORKS_ON;
ADDING CONSTRAINTS PK, FK, ON UPDATE CASCADE AND ON DELETE USING
ALTER TABLE COMMAND -
ALTER TABLE EMPLOYEE ALTER COLUMN Dno SET
DEFAULT 1; ALTER TABLE EMPLOYEE ADD
CONSTRAINT EMPSUPERFK FOREIGN KEY(Super_ssn) REFERENCES EMPLOYEE(Ssn)
ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT EMPDEPTFK FOREIGN KEY(Dno) REFERENCES DEPARTMENT(Dnumber)
ON DELETE SET DEFAULT ON UPDATE CASCADE;
ALTER TABLE DEPARTMENT ALTER COLUMN Mgr_ssn SET DEFAULT '888665555';
ALTER TABLE DEPARTMENT ADD
CONSTRAINT DEPTMGRFK FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn)ON
DELETE SET DEFAULT ON UPDATE CASCADE;
ALTER TABLE DEPT_LOCATIONS ADD
CONSTRAINT DPTLFK FOREIGN KEY(Dnumber) REFERENCES
DEPARTMENT(Dnumber) ON DELETE CASCADE ON UPDATE CASCADE;
ASSIGNMENT - 2
Write SQL Queries for the SELECT FROM WHERE structure of Basic SQL Queries.
Query 1: Retrieve the name and address of all employees who work for the ‘Research’ department.
SELECT Fname, Lname, Address
FROM EMPLOYEE, DEPARTMENT
Query 2: For every project located in ‘Houston’, list the project number, the controlling department
number and the department manager’s last name and address.
SELECT Pnumber, Dnum, Lname, Address
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE Dnum = Dnumber AND Mgr_ssn = Ssn AND Plocation = 'Houston';
Query 3: For each employee, retrieve the employee first name and last name and the first and last name of
his or her immediate supervisor.
SELECT E.Fname, E.Lname, S.Fname AS Super_Fname, S.Lname AS Super_Lname
FROM EMPLOYEE AS E, EMPLOYEE AS S
Query 4: Select all the Employee Numbers (SSN) in the database.
SELECT Ssn FROM EMPLOYEE;
Query 5: Select all combinations of Employee Number (SSN) and Department Name (Dname) in the
database
SELECT Ssn, Dname
FROM EMPLOYEE, DEPARTMENT;
Query 6: Retrieve the salary of every employee.
SELECT Salary FROM EMPLOYEE;
Query 7: Retrieve the distinct salary values of every employee.
SELECT DISTINCT Salary FROM EMPLOYEE;
Write SQL Queries for ambiguous Attribute Names, Aliasing
Query 8: For each employee, retrieve the employee first name and last name and the first and last name of
his or her immediate supervisor.
SELECT E.Fname, E.Lname, S.Fname AS Super_Fname, S.Lname AS
Super_Lname FROM EMPLOYEE AS E, EMPLOYEE AS S
Write SQL Queries for Unspecified WHERE Clause and Use of Asterisk (*).
Query 9: Unspecified WHERE Clause
SELECT Ssn, Fname, Lname
FROM EMPLOYEE;
Use of Asterisk (*)
Write SQL Query for Query as Tables as Sets in SQL (Union, Except, Intersection)
Query 10: Make a list of all project numbers for projects that involve an employee whose last name
is ‘Smith’, either as a worker or as a manager of the department that controls the project.
(SELECT DISTINCT Pnumber
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE Dnum = Dnumber AND Mgr_ssn = Ssn AND Lname = 'Smith')
UNION
(SELECT DISTINCT Pnumber
FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE Pnumber = Pno AND Essn = Ssn AND Lname = 'Smith');
Query 11: Show the salaries of every employee working on the “ProductX project is
given a 10 percent raise.
SELECT Fname, Lname, 1.1*Salary as New_Salary
FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE Pnumber = Pno AND Essn = Ssn AND Pname = 'ProductX';
Query 12: Retrieve all the employees whose address is in Houston.
SELECT Ssn, Fname, Lname
FROM EMPLOYEE
WHERE Address LIKE '%Houston%';
Query 13: Retrieve the name of each employee who has a dependent with the same first
name same sex as the employee.
SELECT E.Fname, D.Lname
FROM EMPLOYEE E,DEPENDENT D
WHERE E.Fname = D.Dependent_name AND Ssn = Essn AND E.Sex = D.Sex;
Query 14: Retrieve the name of employees who have no dependents.
SELECT Fname, Lname
FROM EMPLOYEE E
WHERE NOT EXISTS ( SELECT *
FROM DEPENDENT D
WHERE E.Ssn = D.Essn ) ;
