Scribd
Upload
11 views5 pages

Ag 67

The document outlines an augmented experiment for a Faculty Database, detailing SQL queries to calculate salary adjustments for employees working on the 'IoT' project and to summarize salary data for the 'IT' department. It includes sample data for employees, departments, projects, and work assignments. The results of the SQL queries show the adjusted salaries and aggregate salary statistics for the specified department.

Uploaded by

19010c055
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
11 views5 pages

Ag 67

The document outlines an augmented experiment for a Faculty Database, detailing SQL queries to calculate salary adjustments for employees working on the 'IoT' project and to summarize salary data for the 'IT' department. It includes sample data for employees, departments, projects, and work assignments. The results of the SQL queries show the adjusted salaries and aggregate salary statistics for the specified department.

Uploaded by

19010c055
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/ 5

Exp No:

Date: Page
No:

Augmented Experiment
Aim: For a Faculty Database
EMPLOYEE (EMPID, FName, LName,Address, Sex, Salary,DeptNo)
DEPARTMENT (DeptNo, DName, HOD_EMPID)
PROJECT (ProjNo, PName, DeptNo)
WORKS_ON (EMPID, ProjNo, Hours)

With the sample data

a)Write SQL queries to a. To Show the resulting salaries if every employee working on the ‘IoT’
project is given a 10 percent raise.

b. Find the sum of the salaries of all employees of the ‘IT’ department, as well as the maximum
salary, the minimum salary, and the average salary in this department.

Program:

SQL> select * from department;

Output:

DEPTNO DNAME HOD_EMPID

1 cse 1240

2 it 1245

SQL> select * from employee;

Output:

EMPID FNAME LNAME ADDRESS Sex SALARY DEPTNO

1201 Adarsh Kumar Kakinada F 150000 1

1240 Mahi John Rajahmundry F 95000 1

ADITYA UNIVERSITY (Formerly Aditya Engineering College (A)) RollNo:23A91A0567


Exp No:
Date: Page
No:

1245 Ramu Murty Rajahmundry M 90000 2

1234 Aditya Surya Bangalore M 80000 1

1247 Jack Paul Bangalore M 75000 2

1235 Pradeep Chitra Rajahmundry M 78000 1

1211 Srinivas Kumar Hyderabad M 59000 1

1492 Gopala Rao Kakinada M 65000 2

1250 Eswari Nirupama Kakinada F 65000 2

9 rows selected. SQL>

select * from project ;

Output:

PROJNO PNAME DEPTNO

100 iot 1

101 cloud 1

102 bigdata 2 103 networks 2

104 iot 2

105 networks 16

rows selected.

SQL> select * from works_on; Output:

EMPID PROJNO HOURS

1245 104 16

ADITYA UNIVERSITY (Formerly Aditya Engineering College (A)) RollNo:23A91A0567


Exp No:
Date: Page
No:

1240 101 22

1201 100 31

1250 102 25

1492 103 25

Write SQL queries to a. To Show the resulting salaries if every employee working on the
‘IoT’ project is given a 10 percent raise

SQL> select empid,(salary+salary*10/100) as salary from employee where deptno in (select


deptno from project where pname='iot');

Output:

EMPID SALARY

1211 64900

1235 85800

1234 88000

1240 104500

1201 165000

1250 71500

1492 71500

1247 82500

1245 99000

9 rows selected.

ADITYA UNIVERSITY (Formerly Aditya Engineering College (A)) RollNo:23A91A0567


Exp No:
Date: Page
No:

Find the sum of the salaries of all employees of the ‘IT’ department, as well as the
maximum salary, the minimum salary, and the average salary in this department.

SQL> select sum(salary),min(salary),max(salary),avg(salary) from employee where deptno


in(select deptno from department where dname='it' );

Output:

SUM(SALARY) MIN(SALARY) MAX(SALARY) AVG(SALARY)

295000 65000 90000 73750

ADITYA UNIVERSITY (Formerly Aditya Engineering College (A)) RollNo:23A91A0567


Exp No:
Date: Page
No:

ADITYA UNIVERSITY (Formerly Aditya Engineering College (A)) RollNo:23A91A0567

You might also like