Question

Using SQL, carry out the various DML and DDL functions on the tables mentioned below:
1. Insert the data given above in both employee, department and project tables.
2. Display all the employees’ information.
3. Display Employee name along with his SSN and Supervisor SSN.
4. Display the employee names whose bdate is ’29-MAR-1959’.
5. Display salary of the employees without duplications.
6. Display the MgrSSN, MgrStartDate of the manager of ‘Finance’ department.
7. Modify the department number of an employee having fname as ‘Joyce’ to 5
8. Alter Table department add column DepartmentPhoneNum of NUMBER data type and insert values into this column only.
9. Alter table department to modify the size of DepartmentPhoneNum.
10. Modify the field name DepartmentPhoneNum of departments table to PhNo.
11. Rename Table Department as DEPT.
12. Alter Table department remove column PhNo.
13. Create a table COPYOFDEPT as a copy of the table DEPT.
14. Delete all the rows from COPYOF DEPT table.
15. Remove COPYOF DEPT table.

Mention the SQL code used and the Output for each and every part.

Table Name: Employee

Attribute	Data Type
First Name	VARCHAR(15)
Mid Name	CHAR(2)
Last Name	VARCHAR(15)
SSN Number	CHAR(9)
Birthday	DATE
Address	VARCHAR(50)
Sex	CHAR(1)
Salary	NUMBER (7)
Supervisor SSN	CHAR(9)
Department Number	NUMBER (5)

Table Name: Department

Attribute	Data Type
Department Name	Varchar(15)
Department Number	Number(5)
ManagerSSN	CHAR(9)
ManageStartDate	DATE

Table Name: Project

Attribute	Data Type
Project Name	VARCHAR(15)
Project Number	NUMBER(5)
Project Location	VARCHAR(15)
Department Number	NUMBER(5)

Data For Employee Table

FName	Mini t	LName	SSN	BDate	Address	Sex	Salary	SuperSSN	DepNo
Doug	E	Gilbert	554433221	09-JUN-60	11 S 59 E, Salt Lake City, UT	M	80000	NULL	3
Joyce		PAN	543216789	07-FEB-78	35 S 18 E, Salt Lake City, UT	F	70000	NULL	2
Frankin	T	Wong	333445555	08-DEC- 45	638 Voss, Houston, TX	M	40000	554433221	5
Jennifer	S	Wallace	987654321	20-JUN-31	291 Berry, Bellaire, TX	F	43000	554433221	4
John	B	Smith	123456789	09-JAN-55	731 Fondren, Houston, TX	M	30000	333445555	5
Ramesh	K	Narayan	666884444	15-SEP-52	975 Fire Oak, Humble, TX	M	38000	333445555	5
Joyce	A	English	453453453	31-JUL-62	5631 Rice, Houston, TX	F	25000	333445555	5
James	E	Borg	888665555	10-NOV- 27	450 Stone, Houston, TX	M	55000	543216789	1
Alicia	J	Zelaya	999887777	19-JUL-58	3321 Castle, Spring, TX	F	25000	987654321	4
Ahmad	V	Jabbar	987987987	29-MAR- 59	980 Dallas, Houston, TX	M	25000	987654321	4

Data For Department table

DName	DepNo	MgrSSN	MgrStartDate
Manufacture	1	888665555	19-JUN-71
Administration	2	543216789	04-JAN-99
Headquarter	3	554433221	22-SEP-55
Finance	4	987654321	01-JAN-85
Research	5	333445555	22-MAY-78

Data For Project

PName	PNumber	Plocation	DepNo
ProjectA	3388	Houston	1
ProjectB	1945	Salt Lake City	3
ProjectC	6688	Houston	5
ProjectD	2423	Bellaire	4
ProjectE	7745	Sugarland	5
ProjectF	1566	Salt Lake City	3
ProjectG	1234	New York	2
ProjectH	3467	Stafford	4
ProjectI	4345	Chicago	1
ProjectJ	2212	San Francisco	2

Answer 1

DDL commands means Data defination language which are the subset of SQL commands.

In DDL we use Create,Alter,Drop.

For Creation of the table we follow the syntax.

Create table_name(
attribute_name_1 datatype(size),
attribute_name_2 datatype(size),
...,
...,
attribute_name_n datatype(size)
)

Alter means modifying the table columns and their datatypes.

In Alter we can do following Modes:

1.Add

2.Drop

3.Modify

Alter Syntax :

Alter table table_name {mode} column datatype;

Drop means removing the table .

Drop Syntax : Drop table table_name;

DML are the Data Manipulation Language commands in SQL

These are Select, Insert, Update, Delete etc

According to the given question

1.Insert data into tables Employee,Department and Projects

Insert into Employee Table:

INSERT INTO employee (

FName,Minit,Lname,SSN,Bdate

,address,sex,salary,superSSN,DepNo

)

VALUES (

'Doug','E','Gilbert',55443321

,'09-JUN-60','11 S 59 E,Salt Lake City, UT'

,'M',80000,NULL,3

)

For Department and Project we need to use same commands for insertion of data.

2. For Displaying the details we use select command.

Syntax: SELECT attribute_name_1,attribute_name_2... from table_name

To display all attributes we can simply use star(*) instead of writing all attribute names.

SELECT * FROM table_name.

--> For displaying all employees data.

Select * from Employee;

This means the sql will pick employee table and we have select that means we are fetching so what will be fetching means we have * that means it will fetch all the attributes and all the rows present in the table.

--> For displaying name along with ssn and supervisior ssn

Select FName,SSN,SuperSSN from Employee;

This will also fetching but we have written some attributes after select so the sql will fetch those attribute values of all rows in the table.

--> For displaying details without duplications

Select Distinct Salary from Employee;

In this query we are fetching only salary attribute of all rows but we have added distinct means it will fetch unique values by eliminating the duplicates.

-->For displaying details based on conditions. Display details of manager from finance department

SELECT MGRssn,Mgrstartdate FROM Dname WHERE DName='Finance';

In this query we are fetching some attributes but we have added some where clause that means it will fetch details based on the condition.That means the result will have the details of only the department name is Finance.

-->Add column DepartmentPhoneNum

ALTER table Department ADD DepartmentPhoneNum Number(10);

In query we are using add mode to the table so the new column name departmentphonenumber with datatype number and size 10 will be added to the deparment table.

--> Remove Column PhNo from Deparment

ALTER table Department DROP PhNo ;

In this query we are using drop mode in alter so that means drop is used to be removing the column in the table.

so the column phno will be removed from the table.

--> Delete all rows from table COPYOFDEPT

Delete From COPYOFDEPT

Delete from tablename means it will remove all the rows from the table.

-->Delete table from database

DROP Table COPYOFDEPT

Drop means removing so the table COPYOFDEPT will be removed from the database.

--> Rename table name

ALTER table table_name RENAME new_table_name

ALTER table Department RENAME Dept.

In this query we using rename mode in alter so the name of the table Department will be replaces as Dept.