SQL OPERATORS
Arithmetic: + , - , * , /
Logical: AND, OR, NOT
Relational: = , != , < , > , <= , >= ; <> , ^= (same as !=)
Set operators: UNION [ALL],
INTERSECT,
MINUS
SQL Operators: BETWEEN,
IN,
LIKE,
IS NULL,
UNIQUE,
EXISTS,
ANY,
SOME,
ALL
Note: ALL and ANY should be preceded by a relational operator
COLUMN ALIAS
Column aliases are used to temporarily rename a column heading in the output, but, not in the
database.
Syntax:
SELECT <column_name> [AS] <alias_name>
FROM <table_name>;
i. _ , $ and # are the only valid special characters that can be used as alias names without writing in double quotes
ii. Double quotes should be used for all others including white spaces
iii. All character fields are justified to left side, numeric fields are justified to right.
Examples:
SELECT sid ID,sname "SAILOR NAME" , age AGE FROM sailors;
SELECT sid "SAILOR ID",bid "BOAT ID", day "DATE OF RESERVATION" FROM reserves;
TABLE ALIAS
Syntax:
SELECT <column(s)
FROM <table_name> <table_alias>;
Aliases can be useful when:
• There are more than one table involved in a query
• Functions are used in the query
• Column names are big or not very readable
• Two or more columns are combined together
Note: Column aliases can be used only with the ORDER BY clause of the SELECT statement, for all other
clauses the actual column name should be used. But, the table aliases can be used in all the clauses
SET OPERATORS
1) SET Operators are used to combine information of similar type from
one or more tables. They combine two or more queries into one result
2) Number of columns should match in every select
3) Order of data-types of columns should match in every select
statement.
4) Can use any clause in any select statement but order by should be
used at the end only. Automatically sorts by the first column
• UNION : Rows of first query plus rows of second query, does not include the duplicate rows.
• UNION ALL: Rows of first query plus rows of second query, includes the duplicate rows.
Syntax:
SELECT <stmt-2>
UNION [ALL]
SELECT <stmt-2>
[ORDER BY clause]
Examples:
SELECT sname, age FROM sailors WHERE rating=7
UNION
SELECT sname, age FROM sailors WHERE rating=10;
SELECT bid FROM boats
UNION ALL
SELECT bid FROM reserves;
• INTERSECT : returns common rows between two sets of rows
Syntax:
SELECT <stmt-2>
INTERSECT
SELECT <stmt-2>
[ORDER BY clause>
Example:
SELECT bname FROM boats WHERE color='red'
INTERSECT
SELECT bname FROM boats WHERE color='blue';
• MINUS : returns the rows unique to the first query
Syntax:
SELECT <stmt-2>
MINUS
SELECT <stmt-2>
Example:
SELECT sname FROM sailors WHERE rating=7
MINUS
SELECT sname FROM sailors WHERE rating=9;
SQL OPERATORS
1) BETWEEN- The BETWEEN operator is used to search for values that are within a set of
values, given the minimum value and the maximum value. (including the boundary
values)
EXAMPLE: SELECT * FROM sailors WHERE age BETWEEN 25 AND 35;
2) IN- The IN operator is used to compare a value to a list of literal values that have been
specified.
EXAMPLE: SELECT * FROM sailors WHERE rating IN (7,10);
3) LIKE- The LIKE operator is used to compare a value to similar values using wildcard
operators.
4) IS NULL - The IS NULL operator is used to compare a value with a NULL value ( = NULL
is not valid)
EXAMPLE: UPDATE sailors SET age=NULL WHERE rating=7;
SELECT * FROM sailors WHERE age IS NULL;
5) UNIQUE- The UNIQUE operator searches every row of a specified table for uniqueness
(no duplicates) //works like DISTINCT
EXAMPLE: SELECT UNIQUE rating FROM sailors;
6) EXISTS –
This operator is used to search for the presence of a row in a specified table
that meets certain criteria.
It produces a Boolean result
It takes a sub-query as an argument and evaluates it to True if the sub-query
produces any output and False, if the sub-query does not produce any output
SET COMPARISON OPERATORS
ANY/SOME, ALL Operators –
Used along with the relational operators
Similar to IN operator, but only used in sub-queries
The SOME and ANY operators can be used interchangeably
ANY/SOME – compares the lowest value from the set
ALL – the predicate is true if every value selected by the sub-query satisfies the
condition in the predicate of the outer query.
Examples:
1) Find the names and ratings of sailor whose rating is better than Some Sailor called Horatio.
SELECT S.sname, S.rating
FROM Sailors S
WHERE S.rating > ANY (SELECT S2.rating
FROM Sailors S2
WHERE S2.sname = ‘Horatio’);
2) Find Sailors whose rating is better than every Sailor called Horatio.
SELECT S.sname, S.rating
FROM Sailors S
WHERE S.rating > ALL (SELECT S2.rating
FROM Sailors S2
WHERE S2.sname = ‘Horatio’);
ANY(SOME) ALL
> More than minimum More than maximum
< Less than maximum Less than minimum
Note that IN and NOT IN are equivalent to = ANY and < > ALL, respectively.
Sub-Query/ Inner Query
1) A nested query is a query that has another query embedded within it; the
embedded query is called a subquery.
2) Subquery may appear in FROM, WHERE and HAVING clause.
Types of Subqueries:
– Single row subquery:
– always returns a single value
– Subquery executes only once
– Uses operators = , > , < , >= , <= , !=
– Multiple row subquery:
– returns more than one value
– Subquery executes only once
– Uses operators :
• IN and NOT IN
• EXISTS and NOT EXISTS
• UNIQUE and NOT UNIQUE
• op ANY
• op ALL
– Correlated subquery:
– Subquery executes repeatedly
– Uses any of the operators
Note: When the condition includes one column from inner query and one from outer query then it is a
correlated subquery.
Examples:
Find the names of sailors who have reserved boat 103
1) SELECT S.sname FROM Sailors S WHERE S.sid IN ( SELECT R.sid FROM Reserves R WHERE R.bid =
103 );
The inner subquery has been completely independent of the outer query.
Find the names of boats that have not been reserved
2) SELECT bname
FROM boats
WHERE NOT EXISTS(SELECT * FROM reserves
WHERE boats.bid=reserves.bid);
The inner query depends on the row that is currently being examined in the outer query ( Correlated
query)
EXERCISE
CORRELATED NESTED QUERIES
The inner query depends on the row that is currently being examined in the outer
query.
4. Find the names of Sailors who have not reserved any boats
SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS (SELECT *
FROM Reserves R
WHERE R.sid = S.sid);
• The subquery clearly depends on the current row of S and must be re-evaluated for each row in Sailors.
SET-COMPARISON OPERATORS:
Op ANY and op ALL, where op is one of the comparison operators {<, <=, >, >=, =, < >}
EXAMPLES
1. Find the id, name and the age of the youngest sailor.
SELECT S.sid, S.sname, S.age
FROM Sailors S
WHERE S.age <= ALL (SELECT age
FROM Sailors);
2. Find the names and ratings of sailor whose rating is better than some sailor called
Horatio.
SELECT S.sname, S.rating
FROM Sailors S
WHERE S.rating > ANY (SELECT S2.rating
FROM Sailors S2
WHERE S2.sname = ‘Horatio’);
Note that IN and NOT IN are equivalent to = ANY and < > ALL, respectively.
Compute increments for the ratings of persons who have sailed two different boats
On the same day
Select s.sname,s.rating+1 as rating
From sailors s, reserves r1,reserves r2
Where s.sid=r1.sid and s.sid=r2.sid
And r1.day=r2.day and r1.bid<>r2.bid
AGGREGATE OPERATORS
Used to perform some computation or summarization.
Assuming that A is a column/field in a table:
1. COUNT ([DISTINCT] A): The number of (unique) values in the A column.
Example: SELECT COUNT (DISTINCT S.sname) FROM Sailors S;
2. SUM ([DISTINCT] A): The sum of all (unique) values in the A column.
Example: SELECT SUM(age) FROM Sailors ;
3. AVG ([DISTINCT] A): The average of all (unique) values in the A column.
Example: SELECT AVG (S.age) FROM Sailors S;
4. MAX (A): The maximum value in the A column.
Example: SELECT MAX (rating) FROM Sailors S;
5. MIN (A): The minimum value in the A column.
Example: SELECT MIN (rating) FROM Sailors S;
Exercise
• 1. Find the names of sailors who have reserved boat 103.
• 2.Find the names of the sailors who have reserved a red boat
• 3. Find the names of the sailors who have not reserved a red boat.
• 4. Find the names of Sailors who have reserved boat#103.
• 5. Find Sailors whose rating is better than every Sailor called Horatio.
• 6. Find the Sailor with the highest rating
• 7. Find the names of sailors who have reserved all boats.
• Query:
SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS ((SELECT B.bid
FROM Boats B)
minus
(SELECT R.bid
FROM Reserves R
WHERE R.sid = S.sid));
• 8.Find the average age of all sailors.
• 9. Find the average age of sailors with a rating of 10.
• 10. Find the name and the age of the oldest sailor.
• 11. Count the number of sailors.
• 12. Count the number of different sailor names.
• 13. Find the names of Sailors who are older than the oldest sailor with a rating of 10.
