DAV Sushil Kedia Vishwa Bharati

Affiliated to Tribhuvan University

Jawalakhel, Lalitpur

LAB REPORT
On
Database Management System (CACS255)

Submitted By:
Satwik Tripathi
21

Submitted To:
Er. Simanta Kasaju
Lecturer
DAV College
 Satwik Tripathi |2

Index
S.N TITLE DATE REMARKS SIGNATURE

1. INTRODUCTION TO SQL 24-02-2024

2. FAMILIRIZATION WITH DDL COMMAND 26-02-2024

FAMILIARIZATION WITH VARIOUS
3. CONSTRAINTS IN SQL 04-03-2024

4. FAMILIRIZATION WITH DML COMMAND 05-03-2024

5. FAMILIRIZATION WITH DCL COMMAND 12-03-2024

6, 7. FAMILIRIZATION WITH TCL& DQL 18-03-2024

COMMAND

8. FAMILIRIZATION WITH CLAUSES, ALIAS & 19-03-2024

NESTED QUERY
 Satwik Tripathi |3

LAB 8: FAMILIARIZATION WITH CLAUSE, ALIAS

OPERATION & NESTED QUERY

OBJECTIVE:
The primary objective of this lab report is to understand some clause like SELECT , FROM
WHERE, GROUP BY, HAVING, ORDER BY, BETWEEN, NOT BETWEEN, also alias operation with as
operator and nested query commands like in, not in, having.

THEORY:
Some common SQL clauses and operations:

• SELECT: The SELECT clause is used to retrieve specific columns from a database table.
Example: SELECT column1, column2 FROM table_name;

• FROM: The FROM clause specifies the table or tables from which you want to retrieve
data. It is used in conjunction with the SELECT clause. Example: SELECT column1,
column2 FROM table_name;

• WHERE: The WHERE clause is used to filter data based on specific conditions. It allows
you to retrieve rows that meet certain criteria. Example: SELECT column1, column2
FROM table_name WHERE condition;

• GROUP BY: The GROUP BY clause is used to group rows that have the same values in
a specific column or columns. It is often used with aggregate functions like COUNT,
SUM, AVG, etc. Example: SELECT column1, SUM(column2) FROM table_name
GROUP BY column1;

• HAVING: The HAVING clause is used to filter data based on conditions after the
GROUP BY clause. It is similar to the WHERE clause but operates on grouped data.
Example: SELECT column1, SUM(column2) FROM table_name GROUP BY column1
HAVING condition;
 Satwik Tripathi |4

• ORDER BY: The ORDER BY clause is used to sort the result set based on one or more
columns. It can sort the data in ascending (ASC) or descending (DESC) order. Example:
SELECT column1, column2 FROM table_name ORDER BY column1 ASC;

• BETWEEN: The BETWEEN operator is used to retrieve rows based on a range of

values. It includes both the start and end values. Example: SELECT column1 FROM
table_name WHERE column1 BETWEEN value1 AND value2;

• NOT BETWEEN: The NOT BETWEEN operator is used to retrieve rows that are outside
a specified range of values. Example: SELECT column1 FROM table_name WHERE
column1 NOT BETWEEN value1 AND value2;

• Alias Operation (AS operator): The AS operator is used to assign an alias or temporary
name to a table or column in a SQL query. It is often used for convenience or to make the
query results more readable. Example: SELECT column1 AS alias_name FROM
table_name;

• Nested Query Commands (IN, NOT IN, HAVING, ALL, ANY, EXISTS): These are
used for subqueries within a SQL statement.

• IN: The IN operator is used to check if a value matches any value in a list or the result of
a subquery.

• NOT IN: The NOT IN operator is used to check if a value does not match any value in a
list or the result of a subquery.

• HAVING: The HAVING clause is used to filter the result of a GROUP BY query using
conditions.
 Satwik Tripathi |5

• ALL: The ALL operator is used to compare a value with all values returned by a
subquery.

• ANY: The ANY operator is used to compare a value with any value returned by a
subquery.

• EXISTS: The EXISTS operator is used to check if a subquery returns any rows.
Example: SELECT column1 FROM table_name WHERE column2 IN (SELECT
column3 FROM another_table);

Questions:

1. Create an employee table with attributes emp_id, emp_name, emp_salary, emp_gender,

emp_country, dept_id.

create table employee(

emp_id int primary key,
emp_name varchar(255),
emp_salary int,
emp_gender varchar(255),
emp_country varchar(255),
dep_id int);
2. Create another table department with attributes emp_id, dept_id, dept_name, dept_address.

create table department(

emp_id int,
depy_id int primary key,
dept_name varchar(255),
dept_country varchar(255));
3. Insert at least 5 row.

insert into employee values

(1,'A',9775,'male','nepal',1),
 Satwik Tripathi |6

(2,'b',19550,'male','nepal',2),
(3,'C',9775,'male','india',3),
(4,'A',19550,'female','india',4),
(5,'B',29325,'female','USA',5),
(6,'C',39100,'female','USA',6);

4. Group employee table by salary

5. Group the employee table by name and salary

6. Group department table by dept_name

 Satwik Tripathi |7

7. Group department table by dept_name and dept_id

8. Group employee table by emp_salary where salary>15000

9. Display all the dept_name and dept_id having dept_name= civil

10. Order the employee table by name both ascending and descending
Name in ascending order

Name by descending order:

 Satwik Tripathi |8

11. Order the department table by name both ascending and descending
Department name in ascending order:

In descending

12. Find the top 3 employee info who have highest salary

[Link](as) operator Update salary of employee by 15% and name them as updated salary.
 Satwik Tripathi |9

14. Select the employee name as name_of_employee

15. Select employee salary as salary_of_employee

16. Select the employee’s name and employee gender As name and gender.

17. Select the info of employee from country ‘Nepal’ , ‘England’ , ‘India’.
 S a t w i k T r i p a t h i | 10

18. Select the info of employee not from country ‘England’ , ‘India’

19. Select the info of department from address ktm, Kolkata

20. Select the info not from address ktm, Kolkata

21. Select the info of employee whose name is A, gender is male and country from Nepal, India,
USA.

22. Select the employee salary where emp_salary belongs to emp_id 2

 S a t w i k T r i p a t h i | 11

23. Select emp_salary whose dept_name is civil

24. Select emp_salary whose dept_name is not civi

25. Select name of employee for all whose dept is engineering.

26. Select department name whose salary is >18000

 S a t w i k T r i p a t h i | 12

27. Select dept_address and dept_name whose name is “A”.

28. select dept_address and dept_name who is from USA

Conclusion:
In conclusion this lab provided us with an understanding of clauses such as select, group by,
having, alias, etc., how they function and provide output and how they can be incorporated into
SQL commands to achieve our desired output.