Scribd
Upload
5 views5 pages

SQL Queries with Logical Operators

The document provides SQL queries that demonstrate the use of logical operators such as AND, OR, NOT, IN, BETWEEN, ALL, LIKE, NULL, ANY, EXISTS, and SOME on an 'Employees' table. It includes the creation of the table, insertion of sample data, and various queries to filter employees based on different conditions. Each query is explained briefly, detailing the criteria used to select the employees.

Uploaded by

jakesullyavatar143
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
5 views5 pages

SQL Queries with Logical Operators

The document provides SQL queries that demonstrate the use of logical operators such as AND, OR, NOT, IN, BETWEEN, ALL, LIKE, NULL, ANY, EXISTS, and SOME on an 'Employees' table. It includes the creation of the table, insertion of sample data, and various queries to filter employees based on different conditions. Each query is explained briefly, detailing the criteria used to select the employees.

Uploaded by

jakesullyavatar143
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Practical No.

Write SQL queries using logical operators [ =, <,>, etc].


1- CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,

FirstName VARCHAR(50),

LastName VARCHAR(50),

Age INT,

Department VARCHAR(50),

Salary DECIMAL(10, 2)

);

2- INSERT INTO Employees (EmployeeID, FirstName, LastName, Age, Department, Salary)


VALUES

(1, 'John', 'Doe', 28, 'Sales', 50000.00),

(2, 'Jane', 'Smith', 34, 'Marketing', 60000.00),

(3, 'Emily', 'Jones', 42, 'IT', 75000.00),

(4, 'Michael', 'Brown', 23, 'HR', 45000.00),

(5, 'Chris', 'Davis', 30, 'Finance', 55000.00),

(6, 'Sarah', 'Miller', 37, 'Sales', 58000.00),

(7, 'David', 'Wilson', 29, 'IT', 68000.00);

3-AND
This query returns all employees who have age greater than 30 and belong to Department sales.
SELECT * FROM Employees
WHERE Age > 30 AND Department = 'Sales';

4-OR
This query returns all employees who have age less than 25 or salary greater than 70000
SELECT * FROM Employees
WHERE Age < 25 OR Salary > 70000;

SELECT * FROM Employees


WHERE Age < 25 OR Department = 'Marketing';

5-NOT
This query returns all employees who do not belong to Department IT.
SELECT * FROM Employees
WHERE NOT Department = 'IT';
6-IN
This query returns all employees who belong to Department HR, FINANCE, or MARKETING.
SELECT * FROM Employees
WHERE Department IN ('HR', 'Finance', 'Marketing');

7-BETWEEN
This query returns all employees whose age is between 30 and 40 .
SELECT * FROM Employees
WHERE Age BETWEEN 30 AND 40;

8-ALL
This query selects all employees whose salary is greater than the salary of every employee in the HR
department.
SELECT * FROM Employees
WHERE Salary > ALL (SELECT Salary FROM Employees WHERE Department = 'HR');
9-LIKE
This query returns all employees whose first name starts with the letter 'J' also name end with j(%j).
SELECT * FROM Employees
WHERE FirstName LIKE 'J%';

10-NULL
This query returns all employees whose EmployeeID is NULL).
SELECT * FROM Employees
WHERE EmployeeID IS NULL;

11-ANY
This query selects all employees whose salary is greater than the salary of any one employee in the HR
department.
SELECT * FROM Employees
WHERE Salary > ANY (SELECT Salary FROM Employees WHERE Department = 'sales');
12-EXISTS
This query selects all employees if there is at least one department named 'HR' in the Departments
table.
SELECT * FROM Employees
WHERE EXISTS (SELECT 1 FROM Employees WHERE Department = 'HR');

13-SOME
Some is equivalent to any
SELECT * FROM Employees
WHERE Salary > SOME (SELECT Salary FROM Employees WHERE Department = 'HR');

You might also like