Scribd
Upload
6 views5 pages

Query Sample For Basic Computer Students

The document outlines the creation and manipulation of an EMPLOYEE table in a database, including the definition of attributes and various SQL queries for data retrieval and updates. It covers operations such as inserting records, fetching data based on specific conditions, counting employees, and calculating salary statistics. Additionally, it demonstrates how to update employee information and delete records, while also providing examples of grouping and filtering data.

Uploaded by

shahildon098
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
6 views5 pages

Query Sample For Basic Computer Students

The document outlines the creation and manipulation of an EMPLOYEE table in a database, including the definition of attributes and various SQL queries for data retrieval and updates. It covers operations such as inserting records, fetching data based on specific conditions, counting employees, and calculating salary statistics. Additionally, it demonstrates how to update employee information and delete records, while also providing examples of grouping and filtering data.

Uploaded by

shahildon098
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

-- create a Table with name EMPLOYEE and insert desired attributes

CREATE TABLE EMPLOYEE (


empId INTEGER PRIMARY KEY,
name TEXT NOT NULL,
dept TEXT NOT NULL,
phone INT NOT NULL,
email TEXT NOT NULL,
salary INT NOT NULL,
s_D TEXT NOT NULL,
project TEXT NOT NULL

);

Inserting values in the table


INSERT INTO EMPLOYEE VALUES (0001, 'Clark', 'comp',5555,'clar@[Link]',20000,'per','p1');
INSERT INTO EMPLOYEE VALUES (0002, 'ramesh', 'bio',6666,'ram@[Link]',25000,'temp','p1');
INSERT INTO EMPLOYEE VALUES (0003, 'shyam', 'comp',7777,'shy@[Link]',10000,'temp','p2');
INSERT INTO EMPLOYEE VALUES (0004, 'sundar', 'comp',4555,'sun@[Link]',30000,'per','p2');
INSERT INTO EMPLOYEE VALUES (0005, 'sandesh', 'mgmt',6555,'san@[Link]',22000,'per','p1');

Fetching the data from the table


[Link] every record from the table.
SELECT * FROM EMPLOYEE;
-------+---------+------+-------+----------------+--------+------+---------+
| empId | name | dept | phone | email | salary | s_D | project |
+-------+---------+------+-------+----------------+--------+------+---------+
| 1 | Clark | comp | 5555 | clar@[Link] | 20000 | per | p1 |
| 2 | ramesh | bio | 6666 | ram@[Link] | 25000 | temp | p1 |
| 3 | shyam | comp | 7777 | shy@[Link] | 10000 | temp | p2 |
| 4 | sundar | comp | 4555 | sun@[Link] | 30000 | per | p2 |
| 5 | sandesh | mgmt | 6555 | san@[Link] | 22000 | per | p1 |
+-------+---------+------+-------+----------------+--------+------+---------+

2. Display those employee whose name start with S.


select * from EMPLOYEE where name like 's%';
empId | name | dept | phone | email | salary | s_D | project |
+-------+---------+------+-------+---------------+--------+------+---------+
| 3 | shyam | comp | 7777 | shy@[Link] | 10000 | temp | p2 |
| 4 | sundar | comp | 4555 | sun@[Link] | 30000 | per | p2 |
| 5 | sandesh | mgmt | 6555 | san@[Link] | 22000 | per | p1 |
+-------+---------+------+-------+---------------+--------+------+---------+

3. Count the total number of employee who are permanent and whose salary is above 5000.
select count(salary) as count from EMPLOYEE where s_d='per' AND salary>5000;
+-------+
| count |
+-------+
| 3 |
+-------+

4. Find the employee whose email is only gmail.


select * from EMPLOYEE where email like '%@[Link]';
+-------+--------+------+-------+----------------+--------+------+---------+
| empId | name | dept | phone | email | salary | s_D | project |
+-------+--------+------+-------+----------------+--------+------+---------+
| 1 | Clark | comp | 5555 | clar@[Link] | 20000 | per | p1 |
| 2 | ramesh | bio | 6666 | ram@[Link] | 25000 | temp | p1 |
| 4 | sundar | comp | 4555 | sun@[Link] | 30000 | per | p2 |
+-------+--------+------+-------+----------------+--------+------+---------+
5. Find the employee whose deparment is computer or bio.
select * from EMPLOYEE where dept='comp' OR dept='bio';
+-------+--------+------+-------+----------------+--------+------+---------+
| empId | name | dept | phone | email | salary | s_D | project |
+-------+--------+------+-------+----------------+--------+------+---------+
| 1 | Clark | comp | 5555 | clar@[Link] | 20000 | per | p1 |
| 2 | ramesh | bio | 6666 | ram@[Link] | 25000 | temp | p1 |
| 3 | shyam | comp | 7777 | shy@[Link] | 10000 | temp | p2 |
| 4 | sundar | comp | 4555 | sun@[Link] | 30000 | per | p2 |

6. Display only name of employee


select name from EMPLOYEE;
+---------+
| name |
+---------+
| Clark |
| ramesh |
| shyam |
| sundar |
| sandesh |
+---------+
7. Find the total salary paid to employee.
select sum(salary) as salary from EMPLOYEE;
--------+
| salary |
+--------+
| 107000 |
+--------+

8. Find the average salary paid to employee.


select avg(salary) as average from EMPLOYEE;
------------+
| average |
+------------+
| 21400.0000 |
+------------+

9. Display those employee whose name end with ‘h’.


select * from EMPLOYEE where name like '%h';
+-------+---------+------+-------+---------------+--------+------+---------+
| empId | name | dept | phone | email | salary | s_D | project |
+-------+---------+------+-------+---------------+--------+------+---------+
| 2 | ramesh | bio | 6666 | ram@[Link] | 25000 | temp | p1 |
| 5 | sandesh | mgmt | 6555 | san@[Link] | 22000 | per | p1 |
+-------+---------+------+-------+---------------+--------+------+---------+

10. Count total number of employee.


select count(*) as total from EMPLOYEE;
+-------+
| total |
+-------+
| 5 |
+-------+

[Link] maximum and minimum salary of employee.


select max(salary),min(salary) from EMPLOYEE;
+-------------+-------------+
| max(salary) | min(salary) |
+-------------+-------------+
| 30000 | 10000 |
+-------------+-------------+

[Link] the employee whose salary lies in between 9000 to 10000.


select empId,name from EMPLOYEE where salary between 9000 AND 10000;
+-------+-------+
| empId | name |
+-------+-------+
| 3 | shyam |
+-------+-------+

13. FInd the employee who are not permanent.


select * from EMPLOYEE where NOT s_D='per';
+-------+--------+------+-------+---------------+--------+------+---------+
| empId | name | dept | phone | email | salary | s_D | project |
+-------+--------+------+-------+---------------+--------+------+---------+
| 2 | ramesh | bio | 6666 | ram@[Link] | 25000 | temp | p1 |
| 3 | shyam | comp | 7777 | shy@[Link] | 10000 | temp | p2 |
+-------+--------+------+-------+---------------+--------+------+---------+

14. Display the name of employee in uppercase and department in lowercase


select upper(name),lower(dept) from EMPLOYEE;
+-------------+-------------+
| upper(name) | lower(dept) |
+-------------+-------------+
| CLARK | comp |
| RAMESH | bio |
| SHYAM | comp |
| SUNDAR | comp |
| SANDESH | mgmt |
+-------------+-------------+
15. Update the name of clark into chalara.
update EMPLOYEE set name='chalara' where empId=0001;
select * from EMPLOYEE;
+-------+---------+------+-------+----------------+--------+------+---------+
| empId | name | dept | phone | email | salary | s_D | project |
+-------+---------+------+-------+----------------+--------+------+---------+
| 1 | chalara | comp | 5555 | clar@[Link] | 20000 | per | p1 |
| 2 | ramesh | bio | 6666 | ram@[Link] | 25000 | temp | p1 |
| 3 | shyam | comp | 7777 | shy@[Link] | 10000 | temp | p2 |
| 4 | sundar | comp | 4555 | sun@[Link] | 30000 | per | p2 |
| 5 | sandesh | mgmt | 6555 | san@[Link] | 22000 | per | p1 |
+-------+---------+------+-------+----------------+--------+------+---------+

16. Display the name of employee in Ascending order.


select * from EMPLOYEE order by name ASC;
+-------+---------+------+-------+----------------+--------+------+---------+
| empId | name | dept | phone | email | salary | s_D | project |
+-------+---------+------+-------+----------------+--------+------+---------+
| 1 | Clark | comp | 5555 | clar@[Link] | 20000 | per | p1 |
| 2 | ramesh | bio | 6666 | ram@[Link] | 25000 | temp | p1 |
| 5 | sandesh | mgmt | 6555 | san@[Link] | 22000 | per | p1 |
| 3 | shyam | comp | 7777 | shy@[Link] | 10000 | temp | p2 |
| 4 | sundar | comp | 4555 | sun@[Link] | 30000 | per | p2 |
+-------+---------+------+-------+----------------+--------+------+---------+

[Link] the salary of employee in descending order.


select * from EMPLOYEE order by salary desc;

+-------+---------+------+-------+----------------+--------+------+---------+
| empId | name | dept | phone | email | salary | s_D | project |
+-------+---------+------+-------+----------------+--------+------+---------+
| 4 | sundar | comp | 4555 | sun@[Link] | 30000 | per | p2 |
| 2 | ramesh | bio | 6666 | ram@[Link] | 25000 | temp | p1 |
| 5 | sandesh | mgmt | 6555 | san@[Link] | 22000 | per | p1 |
| 1 | Clark | comp | 5555 | clar@[Link] | 20000 | per | p1 |
| 3 | shyam | comp | 7777 | shy@[Link] | 10000 | temp | p2 |
+-------+---------+------+-------+----------------+--------+------+---------+

[Link] the project name without any repeats.


select distinct project from EMPLOYEE;
+---------+
| project |
+---------+
| p1 |
| p2 |
+---------+

19. Fetch all the employee id who are not working on any projects.
Select empId from employee where project is NULL;

20. Delete the record of one employee.


Delete from employee where empId =3;
Select * from employee;

+-------+---------+------+-------+----------------+--------+------+---------+
| empId | name | dept | phone | email | salary | s_D | project |
+-------+---------+------+-------+----------------+--------+------+---------+
| 1 | Clark | comp | 5555 | clar@[Link] | 20000 | per | p1 |
| 2 | ramesh | bio | 6666 | ram@[Link] | 25000 | temp | p1 |
| 4 | sundar | comp | 4555 | sun@[Link] | 30000 | per | p2 |
| 5 | sandesh | mgmt | 6555 | san@[Link] | 22000 | per | p1 |
+-------+---------+------+-------+----------------+--------+------+---------+

21. Display name and project of employee who is involved in project p1.
select name,project from EMPLOYEE where project='p1';
+---------+---------+
| name | project |
+---------+---------+
| Clark | p1 |
| ramesh | p1 |
| sandesh | p1 |
+---------+---------+

22. Display employee detail who belong to computer department or not had salary below 10000.
select * from EMPLOYEE where dept='comp' or NOT salary<10000;
-------+---------+------+-------+----------------+--------+------+---------+
| empId | name | dept | phone | email | salary | s_D | project |
+-------+---------+------+-------+----------------+--------+------+---------+
| 1 | Clark | comp | 5555 | clar@[Link] | 20000 | per | p1 |
| 2 | ramesh | bio | 6666 | ram@[Link] | 25000 | temp | p1 |
| 3 | shyam | comp | 7777 | shy@[Link] | 10000 | temp | p2 |
| 4 | sundar | comp | 4555 | sun@[Link] | 30000 | per | p2 |
| 5 | sandesh | mgmt | 6555 | san@[Link] | 22000 | per | p1 |
+-------+---------+------+-------+----------------+--------+------+---------+

23. Find employee information who doesn’t get any salary.


Select * from EMPLOYEE where salary is NULL;

24. Find the highest salary for each of department of table.


select dept,max(salary) from EMPLOYEE group by dept;
+------+-------------+
| dept | max(salary) |
+------+-------------+
| comp | 30000 |
| bio | 25000 |
| mgmt | 22000 |
+------+-------------+

[Link] the employee based on project.


select project,count(name) from EMPLOYEE group by project;
---------+-------------+
| project | count(name) |
+---------+-------------+
| p1 | 3 |
| p2 | 2 |
+---------+-------------+

[Link] the department where sum of salary is more than 10000.


select dept,sum(salary) as salary from EMPLOYEE group by dept having
sum(salary)>=10000;
------+--------+
| dept | salary |
+------+--------+
| comp | 60000 |
| bio | 25000 |
| mgmt | 22000 |
+------+--------+

You might also like