Lab Notes Sql Queries | Select, Where, Order By | Dbms

DATABASE SYSTEMS LAB MANUAL


SQL Queries
Topic: SELECT, WHERE, ORDER BY, and SQL Operators
1. Objective:
By the end of this lab, you will be able to:
• Understand the purpose of SQL queries in retrieving data from relational databases.
• Use the SELECT statement to retrieve records from a database table.
• Differentiate between selecting all columns and specific columns.
• Apply the WHERE clause to filter records based on given conditions.
• Use comparison operators such as =, >, <, >=, <=, and <>.
• Apply logical operators such as AND, OR, and NOT.
• Use BETWEEN, IN, and LIKE operators for filtering records.
• Sort query results using ORDER BY with ASC and DESC.
• Combine SELECT, WHERE, and ORDER BY in a single SQL query.
• Execute SQL queries using MySQL Workbench and interpret the results.
•
2. HARDWARE AND SOFTWARE REQUIRED:
Hardware Requirements
• PC or laptop
• Minimum 4 GB RAM
• Keyboard and mouse
Software Requirements
• MySQL Server
• MySQL Workbench
• Windows, Linux, or macOS operating system
Other Requirements
• Access to LMS
• Existing MySQL connection
• Basic knowledge of database creation and table operations
3. Introduction to SQL Queries
SQL stands for Structured Query Language. It is used to communicate with relational databases to store, retrieve, modify, and
manage data.
In a database, information is stored in tables consisting of rows and columns. SQL queries allow users to retrieve specific
information from these tables according to their requirements.
For example, consider a university database containing student information. A user may want to:
• Display all students.
• Display only student names.
• Find students whose marks are greater than 80.
• Display students belonging to the Computer Science department.
• Arrange students according to their marks.
These operations can be performed using SQL queries.
In this lab, you will learn how to retrieve, filter, and sort data using SQL statements.

3.1 What is a SELECT Statement?
The SELECT statement is used to retrieve data from one or more database tables.
It is one of the most commonly used SQL statements.
Basic Syntax:
SELECT column_name
FROM table_name;
To retrieve all columns:
1|P age

, DATABASE SYSTEMS LAB MANUAL
SELECT *
FROM table_name;
The asterisk (*) represents all columns in a table.

3.2 What is a WHERE Clause?
The WHERE clause is used to filter records based on a specified condition.
It returns only those records that satisfy the given condition.
Syntax:
SELECT column_name
FROM table_name
WHERE condition;
The WHERE clause is useful when a table contains many records but only specific information is required.

3.3 SQL Comparison Operators
Comparison operators compare values and help specify conditions in SQL queries.
Operator Meaning Example
= Equal to Marks = 80
> Greater than Marks > 80
< Less than Marks < 80
>= Greater than or equal to Marks >= 80
<= Less than or equal to Marks <= 80
<> Not equal to Department <> 'CS'

3.4 SQL Logical Operators
Logical operators are used to combine or modify conditions in SQL queries.
• AND Operator
The AND operator returns records only when all specified conditions are true.
SELECT *
FROM Students
WHERE Department = 'CS'
AND Marks > 80;
This query retrieves students who belong to the CS department and have marks greater than 80.
• OR Operator
The OR operator returns records when at least one specified condition is true.
SELECT *
FROM Students
WHERE Department = 'CS'
OR Department = 'SE';
This query retrieves students belonging to either CS or SE.
• NOT Operator
The NOT operator reverses a condition.
SELECT *
FROM Students
WHERE NOT Department = 'CS';
This query retrieves students who do not belong to the CS department.
• Difference Between AND and OR
AND OR
All conditions must be true At least one condition must be true
Usually produces more restricted results Usually produces broader results

3.5 BETWEEN Operator
The BETWEEN operator is used to select values within a specified range, including both boundary values.
Syntax:
SELECT *
FROM table_name
2|P age

, DATABASE SYSTEMS LAB MANUAL
WHERE column_name BETWEEN value1 AND value2;
3.6 IN Operator
The IN operator checks whether a value matches any value in a given list.
Example:
SELECT *
FROM Students
WHERE Department IN ('CS', 'SE');
This query retrieves students belonging to CS or SE.
The IN operator is useful when multiple possible values must be checked.
3.7 LIKE Operator
The LIKE operator is used to search for a specified pattern in text data.
It is commonly used with wildcard characters.

Wildcard Meaning
% Represents zero or more characters
_ Represents exactly one character

3.8 What is ORDER BY?
The ORDER BY clause is used to arrange query results in a specific order.
SQL supports two main sorting directions:
• ASC : Ascending order
• DESC : Descending order
Syntax:
SELECT *
FROM table_name
ORDER BY column_name ASC;
Ascending Order
SELECT *
FROM Students
ORDER BY Marks ASC;
This query arranges student records from the lowest marks to the highest marks.
Descending Order
SELECT *
FROM Students
ORDER BY Marks DESC;
This query arranges student records from the highest marks to the lowest marks.
Important: Ascending order is the default when no sorting direction is specified.

3.9 Sorting Using Multiple Columns
SQL also allows sorting using more than one column.
Example:
SELECT *
FROM Students
ORDER BY Department ASC, Marks DESC;
This query first arranges students according to department in ascending order. Within each department, students are arranged
according to marks in descending order.
3.10 Combining SELECT, WHERE, and ORDER BY
Multiple SQL clauses can be used together in a single query.
Syntax:
SELECT column_name
FROM table_name
WHERE condition
ORDER BY column_name ASC;
This query:
1. Selects student names and marks.
2. Filters students having marks of 75 or above.
3|P age

, DATABASE SYSTEMS LAB MANUAL
3. Arranges the selected students from highest to lowest marks.
This combination is useful for retrieving organized and meaningful information from a database.


PRACTICAL ACTIVITIES:
Activity 1: Create and Prepare the Database
Objective: To create a database and prepare a table for performing SQL queries.
Step 1: Open MySQL Workbench
Open MySQL Workbench and connect to the MySQL Server.
Step 2: Create a Database
Execute the following SQL statement:
CREATE DATABASE IF NOT EXISTS UniversityDB_Lab08;
Step 3: Select the Database
USE UniversityDB_Lab08;
Step 4: Create the Students Table
CREATE TABLE Students (
Student_ID INT PRIMARY KEY,
Student_Name VARCHAR(50),
Department VARCHAR(20),
Semester INT,
Marks INT,
City VARCHAR(30)
);
Step 5: Insert Sample Records
INSERT INTO Students
(Student_ID, Student_Name, Department, Semester, Marks, City)
VALUES
(101, 'Ali', 'CS', 4, 85, 'Karachi'),
(102, 'Sara', 'SE', 3, 92, 'Hyderabad'),
(103, 'Ahmed', 'CS', 5, 76, 'Sukkur'),
(104, 'Ayesha', 'IT', 4, 88, 'Karachi'),
(106, 'Hina', 'CS', 6, 95, 'Sukkur'),
(107, 'Danish', 'IT', 3, 72, 'Hyderabad'),
(108, 'Fatima', 'SE', 5, 81, 'Karachi');
Note: Execute the table creation and insertion statements once. If the table already exists from a previous attempt, use the existing
table.
Step 6: Display the Table
SELECT * FROM Students;
Expected Output
STUDENT_ID STUDENT_NAME DEPARTMENT SEMESTER MARKS CITY
101 Ali CS 4 85 Karachi
102 Sara SE 3 92 Hyderabad
103 Ahmed CS 5 76 Sukkur
104 Ayesha IT 4 88 Karachi
106 Hina CS 6 95 Sukkur
107 Danish IT 3 72 Hyderabad
108 Fatima SE 5 81 Karachi
Task: Execute the query and take a screenshot of the table records.




4|P age