In this Blog i am posting the tutorial for SQL Concept in RDBMS – Data Query Language(DQL). SQL is basically a scripting language used in Relational Database Management System(RDBMS) as we learnt in my previous blog, this post will basically explain about DQL(Data Query Language) which is very important when we work on querying for specific data out of Database Tables.
We have learnt about DDL and DML. With DDL we created Tables in Database, with DML we inserted data in database and further updated or deleted data as required via DML operations. Now we will learn about DQL – Data Query Language.
Data Query Language or Data Retrieval Language(DQL or DRL)
Data Query Language is used to query or retrieve data stored in the Database. If we want to see the Data from a table and analyse it in various ways, we can use Data Query language. The basic syntax of Data Query language is – “SELECT COLS_LIST FROM TABLE_NAME;”
When we write a SELECT statement as stated above in a Database tool either in SQL Developer, Toad, MYSQL etc then we get the output of the queried data in form of Tables.
Data Retrieval can further be used for Data Analysis and to generate various Analytical Reports when used in Reporting tools.
Lets take an example below where we have a Company data stored in Database Table.
Here we can retrieve the data and get its output displayed in the console in various ways.
a) Data retrieval for all the Columns – Syntax – SELECT * FROM COMPANY;
Above query displays all the data for with all the columns displayed on the terminal in table format.
b) Data Retrieval for selected columns – Syntax – SELECT Employee ID, Employee Name, Salary from Company;
Above query displays selected columns list and all of their values on the terminal in table format.
Aliases in SQL Select Query
When the result of SELECT query is displayed on the Output Terminal, if we want the columns name to be displayed with a custom name that we want to, we can uses Aliases with Column names for eg if in the last snapshot if we want the columns to be displayed as EMPLOYYE_ID, EMPLOYEE_NAME, EMPLOYEE_SALARY we can use Aliases for displaying it as we want.
Syntax- SELECT Employee ID as EMPLOYEE_ID, Employee Name as EMPLOYEE_NAME, Salary as EMPLOYEE_SALARY from Company;
The result of the above query statement will be displayed as below:-
Hence Aliases are just used to display the column name in the database in our desired format. It does not impact the data in any ways. It is related to the structure of the Database Table.
Also if we want to calculate and display some numeric values we can uses SELECT query and perform our mathematical operation and display the result in the terminal output.
Syntax – SELECT 15*12; gives 180 in result, SELECT 100-92; gives 8 as result etc.
Where Clause in SQL Select Query
The SQL WHERE clause is used to filter records in a database table based on specified conditions.
Suppose in our example above, we want to get records only for the products – Perfume, Leather Jacket and Track Pants from Look Good Store, then we write below SQL Select Query with WHERE clause to get desired result:-
select * from LOOK_GOOD_STORE where Product IN(‘Perfume’, ‘Leather Jacket’, Track Pants’);
ORDER BY Clause in SQL Select Query
The ORDER BY clause is used in SQL is used to sort the result set of a query based on one or more columns. The sorting orders of columns can be ascending (ASC) or descending (DESC).
Suppose in the Employee Table example of Company we have to Order 2 columns – age and salary of a employee – first age should be sorted and then salary of the sorted aged employees should be displayed in the result – then the SQL query would be written as below:-
Select * from Company where salary >=2.5K ORDER BY Age ASC Salary ASC;
In the above example as the age of 2 employees are same(21, 26 years) hence after sorting employees on age, after that data of employees are sorted based on Salary in Ascending order hence employee Abhay is displayed first as he has salary less than ‘Pritam’, also employee ‘Neeraj’ is displayed earlier than employee ‘Piyush’ as his salary is lesser.
In ORDER BY clause if ORDER – ASCENDING or DESCENDING is not specified explicitly, then by default the ORDER is considered as ASCENDING only.