Oracle
Lab Questions
1.
Create
the table Dept based on following information.
|
Column
Name
|
Datatype
|
Length
|
Constraint
|
|
ID
|
Number
|
5
|
Primary
Key
|
|
Dept_name
|
Varchar2
|
50
|
Not
Null
|
CREATE
TABLE `Dept` (
`Id` int(5) NOT NULL,
`Dept_name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`Id`)
)
2.
Insert
the values in Dept table as follows.
|
ID
|
Dept_name
|
|
10
|
IT
|
|
20
|
Account
|
|
30
|
HR
|
|
40
|
Marketing
|
|
50
|
Manufacturing
|
INSERT
INTO dept (`ID`,`Dept_name`) VALUES ( '10','IT');
INSERT
INTO dept (`ID`,`Dept_name`) VALUES ( '20','Account');
INSERT
INTO dept (`ID`,`Dept_name`) VALUES ( '30','HR');
INSERT
INTO dept (`ID`,`Dept_name`) VALUES ( '40','Marketing');
INSERT
INTO dept (`ID`,`Dept_name`) VALUES ( '50','Manufacturing');
3.
Create
the table Emp based on following information.
|
Column
Name
|
Datatype
|
Length
|
Constraint
|
|
ID
|
Number
|
5
|
Primary
Key
|
|
Emp_name
|
Varchar2
|
50
|
|
|
Address
|
Varchar2
|
50
|
|
|
DateOfBirth
|
Date
|
|
|
|
Gender
|
Char
|
1
|
Check
(M or F) only
|
|
Post
|
Varchar2
|
50
|
Not
Null
|
|
Email
|
Varchar2
|
20
|
Unique
|
|
Salary
|
Number
|
10,2
|
Not
Null
|
|
Dept_id
|
Number
|
5
|
Foreign
Key References with Dept_id of Dept table.
|
CREATE
TABLE `emp` (
`ID` int(5) NOT NULL,
`Emp_name` varchar(50) DEFAULT NULL,
`Address` varchar(50) DEFAULT NULL,
`DateOfBirth` date DEFAULT NULL,
`Gender` enum('M','F') DEFAULT NULL,
`Post` varchar(50) NOT NULL,
`Email` varchar(20) NOT NULL,
`Salary` double(10,2) NOT NULL,
`Dept_id` int(5) DEFAULT NULL,
PRIMARY KEY (`ID`,`Email`),
KEY `FK_emp` (`Dept_id`),
CONSTRAINT `FK_emp` FOREIGN KEY (`Dept_id`)
REFERENCES `dept` (`ID`)
)
4.
Describe
the Structure of the above created table DEPT and EMP.
5.
Insert
about 10 values in Emp table.
INSERT
INTO emp (
ID
,Emp_name ,Address ,DateOfBirth ,Gender ,Post ,Email ,Salary ,Dept_id
)
VALUES (
'', 'emp1',
'address1', '2014-06-19', 'M',
'post1', 'email1@email.com', '10,000',
'10'
);
INSERT
INTO emp (
ID
,Emp_name ,Address ,DateOfBirth ,Gender ,Post ,Email ,Salary ,Dept_id
)
VALUES (
'', 'emp2',
'address2', '2014-06-19', 'M',
'post2', 'email2@email.com', '20,000',
'20'
);
INSERT
INTO emp (
ID
,Emp_name ,Address ,DateOfBirth ,Gender ,Post ,Email ,Salary ,Dept_id
)
VALUES (
'', 'emp3',
'address3', '2014-06-19', 'M',
'post3', 'email3@email.com', '30,000',
'30'
);
INSERT
INTO emp (
ID
,Emp_name ,Address ,DateOfBirth ,Gender ,Post ,Email ,Salary ,Dept_id
)
VALUES (
'', 'emp4',
'address4', '2014-06-19', 'M',
'post4', 'email4@email.com', '40,000',
'40'
);
INSERT
INTO emp (
ID
,Emp_name ,Address ,DateOfBirth ,Gender ,Post ,Email ,Salary ,Dept_id
)
VALUES (
'', 'emp5',
'address5', '2014-06-19', 'M',
'post5', 'email5@email.com', '50,000',
'50'
);
INSERT
INTO emp (
ID
,Emp_name ,Address ,DateOfBirth ,Gender ,Post ,Email ,Salary ,Dept_id
)
VALUES (
'', 'emp6',
'address6', '2014-06-19', 'M',
'post6', 'email6@email.com', '60,000',
'10'
);
INSERT
INTO emp (
ID
,Emp_name ,Address ,DateOfBirth ,Gender ,Post ,Email ,Salary ,Dept_id
)
VALUES (
'', 'emp7',
'address7', '2014-06-19', 'F',
'post7', 'email7@email.com', '70,000',
'20'
);
INSERT
INTO emp (
ID
,Emp_name ,Address ,DateOfBirth ,Gender ,Post ,Email ,Salary ,Dept_id
)
VALUES (
'', 'emp8',
'address8', '2014-06-19', 'M',
'post8', 'email8@email.com', '80,000',
'30'
);
INSERT
INTO emp (
ID ,Emp_name
,Address ,DateOfBirth ,Gender ,Post ,Email ,Salary ,Dept_id
)
VALUES (
'', 'emp9',
'address9', '2014-06-19', 'F',
'post9', 'email9@email.com', '90,000',
'40'
);
INSERT
INTO emp (
ID
,Emp_name ,Address ,DateOfBirth ,Gender ,Post ,Email ,Salary ,Dept_id
)
VALUES (
'', 'emp10',
'address10', '2014-06-19', 'M',
'post10',
'email10@email.com',
'100,000', '50'
);
6.
Modify
the Data type of email to varchar2(50).
ALTER TABLE `emp` CHANGE `Email` `Email` VARCHAR2( 50 )
7.
Change
the datatype of Gender of Emp table to Varchar2(10).
ALTER TABLE `emp` CHANGE `Gender` `Gender` VARCHAR2( 10 )
8.
Increase
the salary of Employees who works in IT department by 25%.
update
`emp` SET `Salary`=salary+(salary*0.25) WHERE `Dept_id`=10
9.
Add
the Not Null constraint to Emp table on Emp_name column.
ALTER TABLE `emp` CHANGE `Emp_name` `Emp_name` VARCHAR( 50 )
NOT NULL
10.
Drop
the column Address from the table Emp.
ALTER TABLE `emp` DROP `Address`
11.
List
all the Employees who works in the IT department.
SELECT *
FROM `emp` WHERE `Dept_id`=10
12.
List
all the Employees name, email and salary whose name starts with "a".
Sort data in descending order based on Salary paid.
SELECT
`Emp_name`,`Email`,`Salary` FROM `emp` ORDER BY `Salary` DESC
13.
List
the Average salary of each Department.
SELECT
AVG(Salary) as average_salry,Dept_name
FROM
`emp` JOIN dept
ON
dept.ID=emp.Dept_id
GROUP BY
`Dept_id`
