What is SQL?

Structured Query Language is the full form of SQL. It is one of the standard languages to deal with the relational database. It is used to insert, update, delete, and search the records stored in a RDBMS. The relational databases are created and managed by using SQL.

Database and SQL

A structured data set, which contains a set of tables is known as a database. A database consists of collection of tables where each table has a set of rows such as tuples or records, and attributes are referred to as columns. Each column present at the table is to store a certain type of data like roll number, name, date and so forth.

SQL uses

The structure of the data is described with the help of SQL. SQL enables data professionals and users to retrieve the data from RDBMS. Create, insert, update and delete are some of the operations that can be carried out in a relational database using SQL. The user can set constraints or permissions at the table rows, columns, stored procedures, and views. The user can also create, manipulate and drop the database and their table. In a relational database, the SQL allows the professionals to create a stored procedure, function, and view.

SQL statements types

The most widely used five different kinds of SQL queries are:

• DDL: Data Definition Language.
• DML: Data Manipulation Language.
• DCL: Data Control Language.
• TCL: Transaction Control Language.
• DQL: Data Query Language.

Components used in SQL query execution process

At RDBMS, when the SQL commands are executed, then the request is carried out by the system automatically and the SQL engine will determine how the interpretation of the command needs to happen. The four different components in the process are:

• Query dispatcher.
• Optimization engine.
• Classic query engine.
• SQL query engine.

Commands in SQL

SQL performs various commands and the following are the basic SQL queries:

• CREATE: Defines the structure of the database schema.
• INSERT: Inserts a row with multiple values in the table.
• DELETE: Remove the data from the table.
• UPDATE: Update the data present in the database.
• DROP: Remove the entire database or table.
• SELECT: Displays the attribute based on the WHERE clause.

The SQL query is case insensitive, that is the SQL query with “SELECT statement” and “select statement” have one and the same meaning.

Example: Consider the following select statement,

 “ SELECT * FROM student_table; “ is same as “ select * from student_table; ”.

SQL clause

SQL has certain in-built functions known as clauses; they are used to perform some operation on the data stored in the database. To analyze and filter the data quickly clauses are used. When a huge amount of data is stored in the database, then clauses are used in queries to obtain the user-required data. Some of the clauses used in SQL are:

SELECT clause

SELECT clause is used to specify the columns that are to selected from the table. In order to display values corresponding to all columns, asterisk (*) is used after the SELECT clause which means "SELECT ALL".

Syntax: SELECT columnnames FROM tablename;

Example: SELECT empno, empname FROM employee;

FROM clause

FROM clause is used for listing the tables and any required joins for the SQL statement. To use the FROM clause, there must be one or more tables joined using either INNER or OUTER joins.

Syntax:  SELECT * FROM table_name1 [ { INNER JOIN | LEFT JOIN [ OUTER ] | RIGHT JOIN [ OUTER ] | FULL JOIN [ OUTER ]  }, table_name2 ON table_name1.Column_name1 = table_name2.Column_name1 ]

Example: SELECT emp_table.empName, sal_table.empName FROM emp_table INNER JOIN sal_table ON emp_table.empId = sal_table.empId WHERE empName = “AAA”;

WHERE clause

Where clause is used to specify the conditions in SQL queries. To filter records in the database where clause is used. It is used in select, delete, and update statements.

Syntax: SELECT * FROM table_name WHERE CONDITION;

Example: SELECT * FROM emp_table WHERE emp_Age >30;

AND clause

AND clause is used to specify more than one condition in a single query. AND clause is used along with the WHERE clause. At least it should have two conditions and both the conditions need to be true to execute the query.

Syntax: SELECT * FROM table_name WHERE CONDITION_1 AND CONDITION_2;

Example: SELECT * FROM emp_table WHERE emp_Age >30 AND emp_Age <40;

OR clause

The most beneficial clause, when there is a need to pass more than one condition and the data can satisfy at least one of the specified conditions. It is used in both the delete and update statement along with the WHERE clause.

Syntax: SELECT * FROM table_name WHERE CONDITION_1 OR CONDITION_2;

Example: SELECT * FROM emp_table WHERE emp_Age >30 OR emp_Age <40;

GROUP BY clause

GROUP BY is used in arranging the identical data to a group. The GROUP BY clause is used in the SELECT statement preceding with the clause ORDER BY.

Syntax: SELECT column_name FROM table_name WHERE CONDITION GROUP BY column_name ORDER BY column_name;

Example: SELECT emp_name, COUNT ( * ) FROM emp_table GROUP BY emp_name;

HAVING clause

HAVING clause is used when there is a need to specify certain search conditions for an aggregate or group. This is used at GROUP BY clause, if not then the HAVING function is used like WHERE clause.

Syntax: SELECT column_name1, column_name2 FROM table_name WHERE CONDITION GROUP BY column_name1, column_name2 HAVING CONDITION ORDER BY column_name1, column_name2;

Example: SELECT emp_name, COUNT ( * ) FROM emp_table GROUP BY emp_name HAVING count ( * ) > 3;

ORDER BY clause

ORDER BY clause is used to sort the resulting set either in ascending or descending order. The default order of sorting is ascending order and the keyword “DESC” is used for sorting in descending order.

Syntax: SELECT * FROM table_name WHERE CONDITION ORDER BY column_name  ASC | DEC

Example: SELECT * FROM emp_table ORDER BY empName DESC;

SQL functions

To perform calculations on data, SQL provides many in-built functions. The SQL query has aggregate functions and scalar functions.

The aggregate functions are listed as:

• COUNT(): Returns the total count of rows.
• AVG(): Return average.
• SUM(): Return sum.
• MIN(): Return smallest value.
• MAX(): Return largest value.

Example: SQL query to return the total count of rows/records.

Query: SELECT COUNT (empId) FROM emp_table;

The scalar functions are used to return a single value and the scalar functions are listed as:

• LEN(): Return text field length.
• NOW(): Return system time and date.
• FORMAT(): formats how the rows and columns to be displayed.
• LCASE(): convert the column to lower case.
• UCASE(): convert the column to upper case.

Example: SQL query to find the length.

Query: SELECT empid, LEN(empName) as LenthOfEmployeeName FROM emp_table;

Advantages

• Simple syntactical rules of SQL are used to maintain and access the database and these rules are user-friendly.
• Huge amount of data is accessed efficiently and quickly by using SQL.
• The operations like update, delete, and insert are performed in very less time.
• The structure of the database can have multiple view by using SQL.
• The communication with the database becomes quite easy because of simple query.

Disadvantages

• Certain SQL version has high operation cost.
• The interface is quite difficult to understand, so the users face difficulties in managing the data.
• The users and professionals might not have the entire control over the database as it contains certain hidden rules.
• Certain database has property extension to the SQL standards, which ensures the vendor lock-in.

Context and Applications

This topic is important for postgraduate and undergraduate courses, particularly for Bachelors in Computer Science Engineering and Associate of Science in Computer Science.

Practice Problems

Question 1: Which query is used to retrieve a row, which contains location as “USA”?

a) SELECT * FROM emp_table WHERE location=“USA”

b) SELECT * FROM emp_table WHERE name=“USA”

c) SELECT * FROM emp_table WHERE department=“USA”

d) None of the above

Answer: Option a is correct.

Explanation: The query in option a, will return the row/record which contains “USA” as its location. WHERE clause is used to check the condition.

Question 2: ______ is the full form of SQL.

a) Structured Query List

b) Structured Query Language

c) Sample Query Language

d) None of these

Answer: Option b is correct.

Explanation: The full form of SQL is Structured Query Language. SQL, a programming language used to manipulate data in RDBMS.

Question 3: If ASC/DESC is not specified in the ORDER BY clause then which one is used by default?

a) ASC

b) DESC

c) No default

d) None of above

Answer: Option a is correct.

Explanation: The ORDER BY clause is used to sort the resulting set either in ascending or descending order. If the sorting order is not mentioned then ASC is used as the default sorting order.

Question 4: Which among the following clause is used to create a temporary relation for the query where it is defined?

a) FROM

b) WHERE

c) WITH

d) SELECT

Answer: Option c is correct.

Explanation: WITH clause is used to define a relation that is temporary, whose definition is only to the query. WITH clause is generally used to simplify the complex query, which has subqueries and JOINs.

Question 5: We cannot use the SELECT clause without the ___ clause.

a) ORDER BY

b) FROM

c) WHERE

d) All the above

Answer: Option b is correct.

Explanation: FROM clause is important to point to the tables present in the database. FROM clause is used for listing the tables and any required joins for the SQL statement.