Queries on SQL Operators
Example of Some Queries related to aggregate functions
Consider the relation Student(Rollno, Name, Age, Marks1, Marks2, Marks). Following Results are obtained from the Queries below :
- Result 1: MAX and MIN marks can be used on both numeric and non numeric columns. For Example,
SELECT MAX(Marks) FROM Student; √ SELECT MAX(Name) FROM Student; √
- Result 2: SUM and AVG must be applied only on numeric queries. For Example,
SELECT SUM(Name) FROM Student; × SELECT SUM(Marks) FROM Student; √
- Result 3: DISTINCT has no effect with MIN and MAX but will have a impact on SOME and AVG. For Example,
SELECT SOME(DISTINCT Marks) FROM Student; √ SELECT MIN(DISTINCT Name) FROM Student; √
- Query : Find Out Which Query is Wrong ?
a) SELECT COUNT(Rollno) FROM Student; √ b) SELECT SOME(Marks*10) FROM Student; √ c) SELECT AVG(Mark1 + mark2) FROM Student; √ d) SELECT AVG(Mak1, mark2) FROM Student; ×
The Query (d) is wrong.
Reason : Aggregate functions operate on a single column of a table or on expressions and returns a single value, but it cannot work on multiple columns simultaneously.
- Result 4: Aggregate functions can be used only in the SELECT and HAVING clauses but not in WHERE, ORDER BY and GROUP BY clauses.
For Example, The following queries will gives you error :
SELECT * FROM Student where MAX(Marks)=500; × SELECT * FROM Student ORDER BY MAX(Marks) × SELECT * FROM Student GROUP BY COUNT(Rollno); ×
- Result 5: If SELECT clause consists of some aggregate functions along with ordinary columns then it must be associated with GROUP BY clause, Otherwise, it will produce incorrect results.
- Point : If GROUP BY exists, then Selection and Aggregation is for every group, not for entire table.
Some Range Queries in which BETWEEN and NOT BETWEEN Operator is used :
Consider the relation Student(Rollno, Name, Age, Marks). ×√
- Query 1: Find the details of students whose marks lies between 500 and 600.
SELECT * FROM Student WHERE Marks BETWEEN 500 AND 600; √
- Query 2: Find the details of student whose marks does not lie between 350 and 400.
SELECT * FROM Student WHERE Marks NOT BETWEEN 350 and 400; √
- Result 1: While executing the BETWEEN query with characters, it will take lower boundary and stops exactly at the upper boundary. For Example,
SELECT * FROM Student WHERE Name BETWEEN 'A' and 'K'; √
If any name consists only a single letter K, then that will be included in the BETWEEN case.
- Result 2: The following query is in the wrong form :
SELECT * FROM Student WHERE Marks BETWEEN 400 and 350; ×
SQL will give you a syntax error because lower bound must be mentioned first in between query.
Some Examples of NULL Queries
Query 1 : Find the details of students who are free from Fine ?
SELECT * FROM Library WHERE Fine = NULL; × We cannot use = operator because value of NULL is random therefore, we can't put Fine = NULL. SELECT * FROM Library WHERE Fine IS NULL; √
Query 2 : Find the details of all the students who are having e-mail addresses.
SELECT * FROM Students WHERE email IS NOT NULL;
Question 3 : In SQL, relations can contain NULL values, and comparisons with null values are treated as unknown. Suppose all comparisons with a null value are treated as false. Which of the following pairs is not equivalent? a) x=5 not(not(x=5)) b) x=5 x>4 and x<6 where x is an integer c) x≠5 not(x=5) d) None of the Above [GATE 2000]
Some Queries Related to EXIST and NOT EXIST :
Consider the relation Customer(Cno, Cname,) and Order(Cno,Ano).
Query : Retrieve all customers Name, where customer places an order through Agent A5 ?
1. SELECT Cname FROM Customers WHERE Cno IN(SELECT Cno FROM Order WHERE Ano = 'A5'); OR 2. SELECT Cname FROM Customers WHERE Cno ANY (SELECT Cno FROM Order WHERE Ano = 'A5'); OR 3. SELECT C.Cname FROM Customer C WHERE EXIST(SELECT * FROM Order O WHERE C.Cno = O.Cno AND O.Ano = 'A5'); OR 4. SELECT C.Cname FROM Customer C, ORDER O WHERE C.Cno = O.Cno AND O.Ano = 'A5' ;
|SQL Like Operator||Joins in SQL|