Queries on SQL Operators

QUERIES :

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.
  • a) SELECT Rollno, MAX(Marks) FROM Student; ×   // Produces
                                                     Incorrect Result
    
    Queries on SQL Operators - Wrong Query Result
    
    b) SELECT Rollno, MAX(Marks) FROM Student GROUP BY Rollno;  √
    
    In Example a), in order to get Rollno of Student having Max Marks, we should put Rollno inside GROUP BY.
  • 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]
Solution :
           Queries on SQL Operators
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 ?
Solution :

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' ;

Click to know about LIKE Operator and some queries on LIKE operator.


Previous Home Next
SQL Like Operator Joins in SQL

     

Leave a Reply