SQL Queries

Brief discussion about the history of SQL

                SQL original name was “Structured English Query Language” or SEQUEL, developed by IBM around 1975, and then became SEQUEL/2 then SQL.

Simple/Basic query used in SQL are Select, Project and Join

SQL Select – the select statement used for retrieval queries in SQL. Basic SQL select is the SELECT –FROM-WHERE block

Example: Let’s say we have a company database with a table employee, under this table are Fname, Minit, Lname, SSN, Bdate, Address . Inside the table is a record of employee named Franklin T. Wong, and we want to retrieve it Birthdate and address we can use this syntax:

                SELECT                 BDATE, ADDRESS
                FROM                   EMPLOYEE
                WHERE                 FNAME='Franklin' AND MINIT='T’
                                                AND LNAME='Wong’;

Using ALIASES :
The use of aliases allows some queries who need to refer to the same relation twice. Aliases will handle the relation name.

Example: Let us again use the company database. with additional attributes like 

salary, superssn,dno. Now retrieve the employee's name and the name  of his or her  immediate supervisor.

                                               SELECT         E.FNAME, E.LNAME, S.FNAME,   S.LNAME
                                        FROM            EMPLOYEE AS E, EMPLOYEE AS S
                                        WHERE          E.SUPERSSN=S.SSN;

No Where -clause

Disregarding the use of where clause means there is no condition under the given query, all the relations inside the FROM-clause where selected. unused WHERE-clause means WHERE TRUE.

Example: On the company database lets retrieve the fname of the table employee without using specifying the fname to include in the retrieval.

                                           SELECT   FNAME
                                            FROM     EMPLOYEE;

 

USE * :
When we use * means we are selecting all attributes of a given table.

Example: On the company database, let us retrieve all the attribute of the table employee having a specific DNO lets say DNO=4.
                                      SELECT    *
                                      FROM        EMPLOYEE
                                      WHERE     DNO=4;

USE of DISTINCT

In SQL it treat relation as a set. In some cases many tuples are being duplicated , in order to eliminate this and use or retrieve only those relation once with a duplicate or not we use the keyword DISTINCT.

Example: In database company lets first print out or retrieve  the salary of employee as the usual kind of retrieving: "select salary from employee;"

---------- you can see on the print that the salary being printed that some has duplicate.

now to eliminate this let us use DISTINCT

                                    SELECT      SALARY
                                    FROM          DISTINCT EMPLOYEE;

USE OF OPERATIONS :

Some of SQL operations are UNION, set DIFFERENCE or MINUS and Intersection (INTERSECT). In using this operations there are some exception that need to remember. The two relations must have the same attributes and the attributes must appear in the same order.

Example: Printout all the project numbers that involve an employee whose last name is 'Smith' worker or manager of a department that controls the project.

SYNTAX:
                           SELECT       PNAME
                           FROM           PROJECT, DEPARTMENT, EMPLOYEE
                           WHERE        DNUM=DNUMBER  and MGRSSN=SSN
                            AND             LNAME='Smith'
                           UNION          (SELECT PNAME
                           FROM             PROJECT,WORKS_ON, EMPLOYEE
                           WHERE       PNUMBER=PNO AND ESSN=SSN AND LNAME='SMITH');

NESTED QUERIES:

Nested queries is a complete SELECT query, nested queries can be specified within the WHERE-clause of another query which we called outer query

CORRELATED QUERY

The result of a correlated nested query is different for each tuple (or combination of tuples) of the relation(s) the outer query

EXISTS CLAUSE

The exist clause is used in checking correlated nested query is empty or not

EXPLICIT SETS 

Using an set of values in where clause rather than in nested query

NULL

 It is allowed in SQL query to check of the values where null

JOIN RELATIONS 

 Under the WHERE clause join relation can be used. Join in mysql means query data from two or more tables.

AGGREGATE FUNCTIONS 

It allows or let you you to return a single value after the performing a calculation on a set of records

GROUPING


            GROUP BY-clause is used in grouping attributes. It can be used in aggregate functions and also in SELECT-clause