SQL: Use of Basic Queries


This article is intended to introduce the reader to the different ways in which they can query the database system in order to get the appropriate answer from a single table or even joining multiple tables.

In the previous article we came across the different schema elements, data types and the constraints used in the basic SQL statements. We also witnessed the different statements use to create this and even stated the reasons for the success of SQL over other querying languages. The basic statement used for the retrieval of information from a database is SELECT statement. A thing that needs to be kept in mind about the distinction between SQL and formal relational model is that SQL is not a set of tuples but a bag or multi-set of tuples. This means that even though a relational model can't have more than one attributes having all attribute values identical, this is possible for SQL. It is well known that a set can't have identical values which is the main reason that SQL is not called a set. It is possible to get a table having unique tuple by using SQL if the suitable constraints or keys are used.

SELECT-FROM-WHERE

One of the most basic SQL queries in order to retrieve the desired data or even a collection of data. This is also called mapping or the select-from-where block and has the following structure:
SELECT ATTRIBUTE_LIST
FROM TABLE_LIST
WHERE CONDITION;


Here refers to the list of attribute names whose values are to be found out for the selected tuples, is used to mention the relation name from where the data is to be located and lastly is the conditional statement used to decide whether or not to select a given tuple. The operators for logical comparison are same as that for C except != which in SQL is given as <>. Apart from all these there are other additional operators in SQL which would be mentioned later.
Question : Retrieve the birth date and address of the employee whose name is 'Ramesh Narayan'. (Suppose we have a table named Employee having attributes FName, LName, BDate and Address)
SELECT BDATE, ADDRESS
FROM EMPLOYEE
WHERE FNAME='RAMESH' AND LNAME='NARAYAN';


In the given question we are required to fetch the birth date and address of the mentioned employee, thus we use the select statement to mention the attributes whose value need to be found. Then we are required to inform the database that which table is being referred to so that it can fetch the data from that particular table. Lastly we are required to give the information to select the suitable tuples where we would get the desired item. The could not be searched by only giving 'Ramesh' since there might be multiple people having the same first name but not necessarily identical last name, as a result the complete name is mentioned in the query.

Select-project-join-queries come into play when the required data can't be acquired from a single table but needs a combination of multiple tables. In such cases the relations are joined in a similar manner as done using relational algebra.

Aliasing

There may be certain situations while querying multiple relations one may come across attributes having the same name since SQL allows multiple tables have the same attribute name. In such cases the one needs to do aliasing. Aliasing refers to naming of an attribute or relation in order to avoid erroneous output due to the ambiguity between selection of the right attribute. This can be done by the use of statement AS, used in a similar manner as given below:
Question : Retrieve the first name and last name of each employee as well as the first name and last name of their employer. (Suppose there is a table named Employee have the columns FName, LName, EmpId, Super_EmpId)
SELECT E.FNAME, E.LNAME, S.FNAME, S,LNAME
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.SUPER_EMPID = S.EMPID;


In the given example name aliasing is done because the same attributes are accessed twice in a single query. Once aliasing is done, one only needs to use the new name given to the table in order to get details. As in object oriented programming one can create the object of the same class multiple times in a particular block similar is the case with aliasing. The other method to do so is mentioned below, in this process we can give names to all the attributes in a table having the same order that they had during creation.
TABLE_NAME AS E(ATTRIBUTE1, ATTRIBUTE2,.....,ATTRIBUTEn)


Unspecified WHERE and Asterisk

In case one misses out the WHERE statement then the output would contain the cross product of all the tables whose columns have been referred in the SELECT statement. Thus its extremely important to correctly specify the table name because otherwise many unwanted tuples would be generated in the result.

On the other hand if someone desires to have all the attributes of some particular table as output, there is no need to mention all the names. One could simply type asterisk after SELECT followed by WHERE and the rest of the commands. This would output the values of all the attributes for selected tuple based on given condition if any.

Tables as Sets

It was earlier mentioned that a table in SQL is a multi-set and not a set since similar tuples may exist in a given relation but its also possible to derive the distinct tuples to generate a set. First of all it is important to note the reason for this situation.

  • The removal of repeated tuples is a costly function and hence avoided.

  • There might be some situation where the duplicate tuple is intentionally inserted and later the user may demand to view the same.

  • When aggregate is done for all the tuples in a relation, the presence of duplicate tuple is essential.

The unique values can be generated by the use of the statement DISTINCT as shown below:
SELECT DISTINCT ATTRIBUTE
FROM TABLE_NAME
WHERE CONDITION;

SQL has some set operations namely UNION, EXCEPT and INTERSECT which perform the mathematical set operations of union, difference and intersection respectively. Since these operations are similar to set operations it is evident that their output would be a set of tuples. So these can be used in order to get unique tuple if needed.
(SELECT DISTINCT ATTRIBUTE
FROM TABLE_NAMES
WHERE CONDITION)
UNION
(SELECT DISTINCT ATTRIBUTE
FROM TABLE_NAMES
WHERE CONDITION);

The only condition is that the relations on which these are applied have the same attribute and that too occur in the same order. The above mentioned query would give output as the union of the two SELECT statements used. The other set operations can also be applied in the same manner.
There exists a keyword ALL whose usage after any of the set operations would act in an entirely opposite manner and instead of producing a set of tuples it would generate a multi-set in case the conditions allow to do so.

Sub-string Matching

A need to check a particular portion of some attribute value may arise i.e. partial string matching. This can be done by the use of 2 symbols based on requirement. '%' is used to replace arbitrary number of characters whereas '_' is used to replace a single character. Proper positioning of these two reserved characters would result in efficient querying.
In case the reserved characters are actually a part of the string they meed to be represented as follows to avoid confusion. In this piece of code '\' is known as the escape character and needs to be specified as given at the end of the string.
'AB\_SD\%RF' ESCAPE '\'


If there is single apostrophe in the given string one needs to represent it as double quotes so that the system doe snot interpret it as the end of the string since all strings start and end with single quote.

Arithmetic Operators

SQL supports the usage of mathematical operators fro addition (+), multiplication (*) and subtraction(-). These can be used in the SELECT statement with some attribute. If this is done then the presented view would be the result of the given operation on all the values of that attribute. It is important to note that when this is done, only the user view is affected and the original table stays as it is. (+) and (-) can be used to increment or decrement the value of date, time, or time-stamp for some interval. Concatenation operator has already been discussed in earlier article. There is another added keyword BETWEEN which can be put in condition to get data between some mentioned values. It can be used in the following manner by putting the code statement in the condition area and replacing x and y by desired values.
ATTRIBUTE BETWEEN X AND Y


Query Result Ordering

Data can be retrieved in ascending or descending order as per wish. This is done by the use of keyword ORDER BY after the WHERE statement. The default order is ascending (ASC) but we can specify otherwise by mentioning the keyword DESC.
ORDER BY ATTRIBUTE NAMES DESC


Comments

Author: Srijita Dey24 Mar 2017 Member Level: Silver   Points : 1

Truely its amazing. The way you have explained the subject deserves applause. SQL or Oracle deals with these DBMS things and students get confused with the queries and all ending up in a mess.Your explanation will definitely help them and enthuse them to study further.



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: