[go: up one dir, main page]
Scribd
Upload
28 views2 pages

CSE370 Lab Sheet 04 - Data

The document outlines the creation of a database named 'The_Office' and a table 'Employee' with various attributes. It includes SQL commands for inserting employee data and performing various queries and updates related to employee roles, salaries, and other attributes. Additionally, it specifies tasks such as finding employees based on specific criteria, updating salaries, calculating bonuses, and modifying the table structure.

Uploaded by

rahmansayyan
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
28 views2 pages

CSE370 Lab Sheet 04 - Data

The document outlines the creation of a database named 'The_Office' and a table 'Employee' with various attributes. It includes SQL commands for inserting employee data and performing various queries and updates related to employee roles, salaries, and other attributes. Additionally, it specifies tasks such as finding employees based on specific criteria, updating salaries, calculating bonuses, and modifying the table structure.

Uploaded by

rahmansayyan
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

Lab 04

1.

CREATE DATABASE The_Office;

CREATE TABLE Employee (


Emp_ID char(4),
Name varchar(50),
Age int,
Role varchar(30),
Salary int,
Joining_Date date);

2.
INSERT INTO Employee VALUES
("E001", "Michael Scott”, 40, "Manager", 100000, "1999-09-20"),
( "E002", "Jim Harper", 30, "Sales Executive", 60000, "2004-09-30"),
("E003", "Pam Beesly", 28, "Receptionist", 25000, "2003-09-30" ),
("E004", "Angela Martin", 33, "Accountant", 65000, "2005-09-28" ),
("E005", "Dwight Shrute", 32, "Assistant Manager", 60000, "2003-09-30" ),
("E006", "Kelly Kapoor", 29, "Marketing Executive", 45000, "2003-09-30" ),
("E007", "Andrew Bernard", 30, "Sales Executive", 50000, "2007-05-10" ),
("E008", "Kevin Malone", 28, "Accountant", 60000, "2004-10-30" ),
("E009", "Toby Flender", 35, "HR Manager", 70000, "2004-09-30" ),
("E010", "Phyllis Vance", 40, "Sales Executive", 61000, "1999-09-20" ),
("E011", "Creed Bratton", 50, "Sales Executive", 80000, "1980-06-01");

3.

Complete all tasks below:


a. Find the name and role of employees whose name starts with “a” or ends with “e”
b. Find the details of Employees who have salary between 40000 and 60000
c. Find the details of employees who have joined before the year 2000.
d. There will be 5% raise in salary for all sales executives, as they have done an
excellent job last year. Update the table with the new raised salary. Check if the salary
was updated.
e. Michael Scott will get a bonus of 20% on his salary for excellent leadership initiatives
in last year. Calculate his bonus and use alias (“Michael_Bonus”) for the column header.
[Note: You should not update his salary. Only show the bonus]
f. Show the details of all employees according to their salary sorted from higher to lower.
g. Show the details of all employees according to their age sorted from lower to higher.
h. Show details of employees whose age is more than 35 and who joined before 2003.
i. Turns out Creed Bratton has been lying about his age, he is actually 80 years old. So
he should retire. Delete him from the table.
j. Find the details of employees who have the word “executive” in their role.
k. Change the attribute “Name” to “Employee_Name”
l. Add attribute “Bonus” to the employee table.
m. Delete attribute “Bonus” from the table.
n. List the names of different job roles in the office. There should not be any repetition in
your list.

You might also like