Scribd
Upload
45 views3 pages

Employee Table Creation in SQL

The document contains the SQL statements to create two tables - Student and Employee - and perform various queries on them. For the Student table, data is inserted into fields and the Total field is updated as the sum of marks fields. Queries are run to find the highest mark in sub2, sort names alphabetically, and find the highest total marks. For the Employee table, data is inserted into fields and the Gross field is updated as the sum of Basicpay and DA. Queries are run to sort employees alphabetically by name within department, sum salaries for sales employees, and sort employees by Gross salary. Additional queries find total salary by department, average salary for managers, and maximum Gross salary.

Uploaded by

Sinan K
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
45 views3 pages

Employee Table Creation in SQL

The document contains the SQL statements to create two tables - Student and Employee - and perform various queries on them. For the Student table, data is inserted into fields and the Total field is updated as the sum of marks fields. Queries are run to find the highest mark in sub2, sort names alphabetically, and find the highest total marks. For the Employee table, data is inserted into fields and the Gross field is updated as the sum of Basicpay and DA. Queries are run to sort employees alphabetically by name within department, sum salaries for sales employees, and sort employees by Gross salary. Additional queries find total salary by department, average salary for managers, and maximum Gross salary.

Uploaded by

Sinan K
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

Table structure and queries : SQL-1 (Student)

Create a table Student with the following fields and insert at least 5 records into the table except for the column
Total.

RollNo Integer Primary key


Name Varchar (25)
Batch Varchar (15)
Mark1 Integer
Mark2 Integer
Mark3 Integer
Total Integer

a. insert data in the fields RollNo, Name, sub1, sub2, sub3(At least 5 records)
b. Update the field Total with the sum of sub1, sub2, and sub3
c .Display the highest mark in sub2.
d. Display the names in ascending order
e. Display the highest Total mark in the table

SQL statements :
Create database lab;
Use lab;
CREATE TABLE student
(
RollNO INT PRIMARY KEY,
Name VARCHAR (25),
sub1 INT,
sub2 INT,
sub3 INT,
Total INT
);

a. I. INSERT INTO student (RollNo, Name, sub1, sub2, sub3) VALUES


(1, ’Akhil’, 20, 30, 25);
II. INSERT INTO student (RollNo, Name, sub1, sub2, sub3) VALUES
(2, ’Sreejith’, 140, 110, 20);
III. INSERT INTO student (RollNo, Name, sub1, sub2, sub3) VALUES
(3, ’Chithra’, 45, 35, 28);
IV INSERT INTO student (RollNo, Name, sub1, sub2, sub3) VALUES
(4, ’Arun’, 120, 150, 100);
V. INSERT INTO student (RollNo, Name, sub1, sub2, sub3) VALUES
(5, ’Vinitha’, ’commerce’, 22, 25, 35);

b. UPDATE student SET Total=sub1+sub2+sub3;


c. SELECT Max (sub2) from student;
d. SELECT Name FROM student order by Name;
e. SELECT Max (Total) from student;
SQL-2 (Employee)

Create a table Employee with the following fields and insert at least 5 records into the table except the column
Gross.

EmpNo Integer Primary key


Name Varchar (20)
Designation Varchar (25)
Department Varchar (25)
Basicpay Decimal (10,2)
DA Decimal (10,2)
Gross Decimal (10,2)

a) insert data in the fields EmpNo, Name, Designation, Department, Basicpay , DA (At least 5 records)
.
b) Update the Gross with the sum of Basicpay and DA.
c) Display the department wise listing of all employees in ascending order of name
d) Display the total salary paid to salesman.
e) Display the details of employees arranged in ascending order of gross.

SQL statements :
Create database lab;
Use lab;
.
CREATE TABLE Employee
(
EmpNo INT PRIMARY KEY,
Name VARCHAR (20),
Designation VARCHAR(25),
Department VARCHAR(25),
Basicpay DEC(10,2),
DA DEC(10,2),
Gross DEC(10,2)
);
a. SQL Query to insert 5 records into the table
I. INSERT INTO Employee (EmNo , Name, Designation, Department, Basicpay,DA)
VALUES (1, ’Rahul’, ’clerk’, ‘sales’, 5000,3750);
II. INSERT INTO Employee (EmNo , Name, Designation, Department, Basicpay,DA)
VALUES (2, ’Abraham’, ’supervisor’, ‘purchase’, 9000,6750);
III. INSERT INTO Employee (EmNo , Name, Designation, Department, Basicpay,DA)
VALUES (3, ’Roshan’, ’manager’, ‘HR’ , 12000,9000);
IV. INSERT INTO Employee (EmNo , Name, Designation, Department, Basicpay,DA)
VALUES (4, ’Soumya’, ’supervisor’, ‘stock’, 4000,3000);
V. INSERT INTO Employee (EmNo , Name, Designation, Department, Basicpay,DA)
VALUES (5, ’Anusree’, ’clerk’, ‘purchase’, 3000,2250);

b. UPDATE Employee SET Gross= Basic + DA;


C. SELECT Name, Department FROM Employee order by Name;
d. SELECT Gross from Employee where Department=”sales”;
e. SELECT * from Employee order by Gross desc;
SQL-3

c. Display total salary paid in each Department.

d. Display average salary of managers

e. Display Maximum Gross salary

c. SELECT Sum( Gross), Department from Employee group by Department;


d. SELECT avg(Gross) from Employee where Designation=”manager”;
e. SELECT Max(Gross) from Employee;

Common questions

Powered by AI

To find the employee with the maximum gross salary, you first need to get the maximum gross value using the MAX() function, then filter the employee records. The query could be written as: SELECT Name FROM Employee WHERE Gross=(SELECT Max(Gross) FROM Employee); This uses a subquery to first determine the maximum gross salary, which is then used to filter out the specific employee(s) with that gross value .

To list employees by department with their names in ascending order, you can combine the ORDER BY clause in SQL with multiple sorting criteria. The query would be: SELECT Name, Department FROM Employee ORDER BY Department, Name; This command sorts the employees first by 'Department' and then each department's employees by 'Name' in ascending order .

To display employee details sorted by their gross salary in descending order, you use the SQL SELECT statement combined with the ORDER BY clause. The exact command would be: SELECT * FROM Employee ORDER BY Gross DESC; This command sorts the rows in the 'Employee' table by the 'Gross' column in descending order .

The SQL query to determine the average gross salary of managers would use the AVG() function. The query is: SELECT AVG(Gross) FROM Employee WHERE Designation='manager'; This command computes the average of the 'Gross' column values filtered by the 'Designation' specified as 'manager' .

SQL can update a column with calculated values from other columns using the UPDATE statement combined with SET. The process involves specifying the target column, then assigning it a formula that calculates the value using other columns. For instance, updating a 'Total' column with sum values from 'sub1', 'sub2', and 'sub3', the SQL command is: UPDATE student SET Total=sub1+sub2+sub3; This demonstrates SQL's ability to handle arithmetic operations for column updates .

To calculate the total salary expenditure for each department, you use the SQL SUM() function along with GROUP BY. The query is: SELECT Sum(Gross), Department FROM Employee GROUP BY Department; This command calculates the sum of the 'Gross' salaries and groups the results by 'Department' .

To retrieve a sorted list of student names in ascending order, you would use the ORDER BY clause. The SQL statement would be: SELECT Name FROM student ORDER BY Name; This command selects the 'Name' column from the 'student' table and sorts the results in ascending order by name .

To find the highest mark recorded for the second subject (sub2) in a student table, you use the SQL aggregate function MAX(). The query is: SELECT Max(sub2) FROM student; This command retrieves the maximum value in the 'sub2' column .

To update a column with the total marks calculated from individual subject scores, you use the SQL UPDATE statement. In this case, for the 'student' table, the command would be: UPDATE student SET Total=sub1+sub2+sub3; This updates the 'Total' column by summing the values of 'sub1', 'sub2', and 'sub3' for each record .

SQL uses the GROUP BY clause to perform operations on subsets of data divided into groups based on one or more columns. For aggregation tasks, it allows you to apply functions like SUM, AVG, MAX to grouped records, producing a single result per group. An example is calculating the total gross salary per department: SELECT Sum(Gross), Department FROM Employee GROUP BY Department; Here, the SQL statement computes the total salary ('Sum(Gross)') for each 'Department', demonstrating the use of GROUP BY to aggregate data on a group basis .

You might also like