Database is the most important components of any web application. Many organizations spend their handsome revenue to build and secure database of their customers to develop and market products. These days many new cloud technologies and advanced database models provides flexible and secure gateway to build secure database. Technically database includes all the complex logic of any web application.
In this article we will understand the logic behind selecting data from database, which is a technical aspect of a database. But before explaining database and its specific technical queries, we need to understand the meaning of database. In simple words database relates to the back end of any web application and termed as the repository of data/records in form of tables. We query to the database with the help of query language which we further defines as a Structured Query Language (SQL). SQL provides the flexible way to query the tables in terms of predefined language constructs and their clauses. With the help of SQL we can do many operation on table such as:
a) We can insert, modify or delete rows from the table.
b) We can create and delete a table.
c) We can modify security information
d) We can search specific records from the table and many more.
Note: SQL commands are not case sensitive but reference to data in a database is case sensitive. So readers are advised to be careful about this fact while selecting or inserting any records in a table.
We can retrieve data from database with the help “SELECT” statement and it is commonly referred as a Data Manipulation Language (DML) command. This DML command is very helpful in retrieving records from database. We commonly use this statement to select and list all rows and columns, selected columns and columns from multiple tables. This SELECT statement has many optional clauses which we generally use according to the SQL statement such as:
ORDER BY: It specifies an order in which we need to return the rows.
AS: It facilitates alias property in which we can temporarily rename columns and tables.
WHERE: It specifies the row or group of rows to be retrieved from the table.
GROUP BY: It aggregate records by the specified columns which allows us to perform aggregate functions on non-grouped columns(like COUNT, AVG etc).
HAVING: It selects among the groups defined by GROUP BY clause.
However, SQL SELECT statement never works alone. We use above mentioned clauses according to the SQL query from the table.
Selecting whole table with SELECT statement
We consider below mentioned “employee_tbl” table as a example to explain data selection from table.
We usually use SELECT statement to select a table like:
SELECT * FROM employee_tbl;
Above query in nutshell:
- In above query we used two keywords such as: SELECT and FROM to select whole table at once.
- The asterisk(*) in the query tells the table to return all the columns associated with it.
- Semicolon in above query tells the SQL interpreter that user has finished writing the query.
- As we used asterisk(*) in above statement, so the order of columns appearance is determined by table. If we need to change the order then we should define the order in above statement. for example: (ASC or DESC).
Selecting individual column or group of columns
If we need to select column or group of columns from specific table then we use simple column name or DISTINCT keyword before the column name such as:
a) SELECT location FROM employee_tbl;
b) SELECT DISTINCT location FROM employee_tbl;
Note: The difference between SQL keyword and clauses is that SQL keywords refers to individual SQL elements such as: SELECT and FROM. While on the other side a clause is a part of an SQL statement such as: SELECT column1_name,column2_name.. SQL clauses generally form a complete SQL statement.
Uploaded by: Author