Wednesday, October 9, 2019

DBMS QUERY

Structured Query Language (SQL)

Structured Query Language is a standard Database language which is used to create, maintain and retrieve the relational database. Following are some interesting facts about SQL.
    Data Manipulation Language: It is used to manipulate data in the relations. e.g.; INSERT, DELETE, UPDATE and so on.
    Data Query Language: It is used to extract the data from the relations. e.g.; SELECT
    So first we will consider the Data Query Language. A generic query to retrieve from a relational database is:
    1. SELECT [DISTINCT] Attribute_List FROM R1,R2….RM
    2. [WHERE condition]
    3. [GROUP BY (Attributes)[HAVING condition]]
    4. [ORDER BY(Attributes)[DESC]];
    Part of the query represented by statement 1 is compulsory if you want to retrieve from a relational database. The statements written inside [] are optional. We will look at the possible query combination on relation shown in Table 1.
    Case 1: If we want to retrieve attributes ROLL_NO and NAME of all students, the query will be:
    SELECT ROLL_NO, NAME FROM STUDENT;
    ROLL_NONAME
    1RAM
    2RAMESH
    3SUJIT
    4SURESH
    Case 2: If we want to retrieve ROLL_NO and NAME of the students whose ROLL_NO is greater than 2, the query will be:
    SELECT ROLL_NO, NAME FROM STUDENT 
    WHERE ROLL_NO>2;
    ROLL_NONAME
    3SUJIT
    4SURESH
    CASE 3: If we want to retrieve all attributes of students, we can write * in place of writing all attributes as:
    SELECT * FROM STUDENT 
    WHERE ROLL_NO>2;
    ROLL_NONAMEADDRESSPHONEAGE
    3SUJITROHTAK915625313120
    4SURESHDELHI915676897118
    CASE 4: If we want to represent the relation in ascending order by AGE, we can use ORDER BY clause as:
    SELECT * FROM STUDENT ORDER BY AGE;
    ROLL_NONAMEADDRESSPHONEAGE
    1RAMDELHI945512345118
    2RAMESHGURGAON965243154318
    4SURESHDELHI915676897118
    3SUJITROHTAK915625313120
    Note: ORDER BY AGE is equivalent to ORDER BY AGE ASC. If we want to retrieve the results in descending order of AGE, we can use ORDER BY AGE DESC.
    CASE 5: If we want to retrieve distinct values of an attribute or group of attribute, DISTINCT is used as in:
      SELECT DISTINCT ADDRESS FROM STUDENT;
      ADDRESS
      DELHI
      GURGAON
      ROHTAK
      If DISTINCT is not used, DELHI will be repeated twice in result set. Before understanding GROUP BY and HAVING, we need to understand aggregations functions in SQL.
      AGGRATION FUNCTIONS: Aggregation functions are used to perform mathematical operations on data values of a relation. Some of the common aggregation functions used in SQL are:
      • COUNT: Count function is used to count the number of rows in a relation. e.g;
      SELECT COUNT (PHONE) FROM STUDENT;
      COUNT(PHONE)
      4
      • SUM: SUM function is used to add the values of an attribute in a relation. e.g;
      SELECT SUM (AGE) FROM STUDENT;
      SUM(AGE)
      74
      In the same way, MIN, MAX and AVG can be used.  As we have seen above, all aggregation functions return only 1 row.
      GROUP BY: Group by is used to group the tuples of a relation based on an attribute or group of attribute. It is always combined with aggregation function which is computed on group. e.g.;
      SELECT ADDRESS, SUM(AGE) FROM STUDENT
      GROUP BY (ADDRESS);
      In this query, SUM(AGE) will be computed but not for entire table but for each address. i.e.; sum of AGE for address DELHI(18+18=36) and similarly for other address as well. The output is:
      ADDRESSSUM(AGE)
      DELHI36
      GURGAON18
      ROHTAK20
      If we try to execute the query given below, it will result in error because although we have computed SUM(AGE) for each address, there are more than 1 ROLL_NO for  each address we have grouped. So it can’t be displayed in result set. We need to use aggregate functions on columns after SELECT statement to make sense of the resulting set whenever we are using GROUP BY.
      SELECT ROLL_NO, ADDRESS, SUM(AGE) FROM STUDENT
      GROUP BY (ADDRESS); 
      NOTE: An attribute which is not a part of GROUP BY clause can’t be used for selection. Any attribute which is part of GROUP BY CLAUSE can be used for selection but it is not mandatory.
      Quiz on SQL
      Article Contributed by Sonal Tuteja. Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.