| |
| EMP |
| ID |
Name |
Country |
Salary |
| 1 |
Vijay |
India |
6000 |
| 2 |
Bill |
USA |
8000 |
| 3 |
Kris |
France |
3000 |
|
|
| Continents |
| Country |
Continent |
| India |
Asia |
| USA |
N.America |
| Italy |
Europe |
|
|
-- Logical Operators : SOME, ALL, ANY, NOT, AND, OR, BETWEEN, EXISTS, IN, LIKE
IF 4000 < SOME (SELECT Salary FROM EMP)
PRINT '4000 is less than SOME of the Salaries in the table'
ELSE
PRINT 'FALSE';
---- OutPut using SOME
4000 is less than SOME of the Salaries in the table
IF 4000 < ALL (SELECT Salary FROM EMP)
PRINT 'TRUE'
ELSE
PRINT '4000 is not less than ALL of the Salaries in the table' ;
---- OutPut using ALL
4000 is not less than ALL of the Salaries in the table
Create Proc LogiOp @Salary int, @ID int
AS
IF @Salary > ANY
(
SELECT Salary FROM EMP
Where ID > @ID
)
PRINT '4000 is less than ANY of the Salaries in the table'
ELSE
PRINT 'FALSE';
EXEC LogiOp 4000, 1
---- OutPut using ANY
4000 is less than ANY of the Salaries in the table
---- NOT, AND, OR
SELECT *
FROM EMP
WHERE
NOT (ID = 3)
AND (Salary > 3000)
OR (Country = 'India')
---- BETWEEN
SELECT * FROM EMP WHERE Salary BETWEEN 5000 and 9000
---- EXISTS
SELECT *
FROM EMP a
WHERE EXISTS (
SELECT * from Continents b
WHERE a.Country = b.Country )
---- IN
SELECT *
FROM EMP
WHERE Country IN (SELECT Country from Continents)
---- LIKE
SELECT * FROM EMP where Name LIKE '[a-v]i_%'
---- above 5 queries will give the same output like below
|
| Output |
| ID |
Name |
Country |
Salary |
| 1 |
Vijay |
India |
6000 |
| 2 |
Bill |
USA |
8000 |
|
|
|
|
|