Welcome to Our Free Tutorial!

ALL with comparison Operator


First Step (Create Database and Use Database)

create database office
use office

Second Step (Create Tables)

/*----Create first table---*/
create table employee ( ID int, name nvarchar (15), salary int ) GO /*----Create Second Table---*/
create table job( ID int, title nvarchar (15), avgSalary int) GO

Third Step Inserting Values in tables
 

insert into employee (ID, name, salary) values  (1, 'shiv', 4321) (1 row(s) affected) insert into employee (ID,  name,  salary) values (2, 'Jack', 1234) (1 row(s) affected) insert into employee (ID, name, salary) values (3, 'Robert', 4577) (1 row(s) affected) insert into employee (ID, name, salary) values (4, 'Ram', 7895) (1 row(s) affected) insert into employee (ID, name, salary) values (5, 'Lina', 2584) (1 row(s) affected) insert into employee (ID, name, salary) values (6, 'Sudha', 4547) (1 row(s) affected) insert into employee (ID, name, salary) values (7, 'Rion', 7894) (1 row(s) affected) insert into employee (ID, name, salary) values (8, 'julious', 9245) (1 row(s) affected) insert into employee (ID, name, salary) values (9, 'Jemes', 2725) (1 row(s) affected) insert into job(ID, title, avgSalary) values (1,'Programmer',6000) (1 row(s) affected) insert into job(ID, title, avgSalary) values (2,'Designer', 5000) (1 row(s) affected) insert into job(ID, title, avgSalary) values (3,'Tester', 5000) (1 row(s) affected) insert into job(ID, title, avgSalary) values (4,'Developer', 3000) (1 row(s) affected)

Forth Step Selecting Values from tables select * from employee;

select * from job;

Use comparison operator if query returns a scallar value

SELECT e.ID,e.name FROM Employee e WHERE e.salary > ALL
(SELECT avgSalary FROM job j)

ID Name
4 Ram
7 Rion
8 julious
(3 rows affected)