Learning of SQL Day 8

 

Day 8: WHERE Clause

Introduction:

• The WHERE clause is used to filter records that meet a specified condition. It's essential for retrieving specific data from your tables.

Key Concepts:

• Filtering Rows: Use conditions to select rows that meet criteria.

• Comparison Operators: =, !=, <, >, <=, >=.

• Logical Operators: AND, OR, NOT.

SQL Commands and Examples:

1. Basic WHERE Clause:

   • Retrieve rows that match a specific condition.

sql

-- Select students who are older than 21
SELECT * FROM Students
WHERE Age > 21;

2. Using Comparison Operators:

   • Filter records using different comparison operators.

sql

-- Select courses with more than 3 credits
SELECT * FROM Courses
WHERE Credits > 3;

-- Select courses with exactly 3 credits
SELECT * FROM Courses
WHERE Credits = 3;

3. Using Logical Operators:

   • Combine multiple conditions using logical operators.

sql

-- Select students who are older than 21 and have a last name 'Smith'
SELECT * FROM Students
WHERE Age > 21 AND LastName = 'Smith';

-- Select courses with more than 3 credits or a course name 'Mathematics'
SELECT * FROM Courses
WHERE Credits > 3 OR CourseName = 'Mathematics';

4. Using NOT Operator:

   • Exclude rows that meet a specified condition.

sql

-- Select students who are not older than 21
SELECT * FROM Students
WHERE NOT Age > 21;

Practice Exercise:

1. Create a table named Employees with columns EmployeeID (INT), FirstName (VARCHAR), LastName (VARCHAR), Department (VARCHAR), and Salary (DECIMAL).

2. Insert a few records into the Employees table.

3. Write a query to select employees from the Sales department.

4. Write a query to select employees with a salary greater than 50000.

5. Write a query to select employees who are in the Sales department and have a salary greater than 50000.

6. Write a query to select employees who are not in the HR department.

sql

-- Create the Employees table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(255),
    LastName VARCHAR(255),
    Department VARCHAR(255),
    Salary DECIMAL(10, 2)
);

-- Insert records into the Employees table
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary)
VALUES 
(1, 'Alice', 'Johnson', 'Sales', 60000),
(2, 'Bob', 'Smith', 'HR', 45000),
(3, 'Charlie', 'Brown', 'IT', 70000),
(4, 'Daisy', 'Miller', 'Sales', 55000);

-- Select employees from Sales department
SELECT * FROM Employees
WHERE Department = 'Sales';

-- Select employees with a salary greater than 50000
SELECT * FROM Employees
WHERE Salary > 50000;

-- Select employees from Sales department with a salary greater than 50000
SELECT * FROM Employees
WHERE Department = 'Sales' AND Salary > 50000;

-- Select employees who are not in the HR department
SELECT * FROM Employees
WHERE Department != 'HR';

Using the WHERE clause effectively allows you to focus on the data that matters most.