SQL Processing and understanding its Key Capabilities

355

The Structured Query Language(SQL) is a simple query language used for accessing, handling and managing data in relational databases.

The original version of SQL was developed at IBM’s San Jose Research Laboratory (now the Almaden Research Center).

Now let’s look at the various processing capabilities of SQL

1. Data Definition Language (DDL)

The SQL DDL provides commands for defining relation schemas, deleting relations, creating indexes, and modifying relation schemas.

2. Data Manipulation Language (DML)

The SQL DML includes commands to insert, delete and modify tuples in the database.  

3.View Definition

The SQL DDL also includes commands for defining views.

4. Integrity

The SQL DDL provides forms of integrity checking. It is used to define the parameters of a table

5. Transaction control

SQL provides commands to control transactions

SQL is a language oriented specifically around relational databases. The SQL commands can operate on entire groups of tables as single objects and can treat any quantity of information extracted or derived from them as a single unit as well.

SQL consists of mainly three sub languages

DDL – Data Definition Language

DML – Data Manipulation Language

DCL – Data Control Language

We discussed DDL and DML earlier. 

The DCL is used to control access to the database and therefore essential to the system.

Now let’s look at a few common terminologies used in SQL

Data Dictionary – A Data Dictionary is a file that contains “metadata” i.e. data about data.

Constraint – A constraint is a condition or check applicable on a field or set of fields.

Constraints in SQL

As we discussed earlier that SQL provides database integrity, for this purpose it provides various database integrity constraints

1. Unique Constraint

This does not allow duplicate data to be present in the database.

The keyword used is UNIQUE and it can be applied on individual columns

2. Primary Key Constraint

This constraint declares a column as the primary key of the table.

This key makes sure that the column has unique as well as NOT NULL values only.

Also note that only a single column can be made as primary key

3. Default Constraint

If a user does not input a value for a certain column or forgets to add, then instead of keeping it empty we can pre-define a specific value which will be added if no value is added for that field.

This is done using the default constraint using the keyword DEFAULT

For example DEFAULT = “ED”

4. Check Constraint

This constraint limits the values that can be inserted into a column of a table.

For example, CHECK(marks > 25);

Another point to note, the check constraint is supported in SQL but not in MySQL

5. Foreign Key Constraint 

The primary key used in the first table is known as the Foreign key in the second table.

This constraint is used to build relations between tables.

To apply a foreign key constraint, keyword REFERENCES should be used.

For example- CompNo  CHAR(8) REFERENCES Company (CompanyNo)

Here CompNo is a column which is made as foreign key in the second table. From the first table named Company, the column named CompanyNo is defined as the primary key.

Commands in SQL

Next, it’s time to look at some basic commands to create tables and perform various operations on it.

1. CREATE TABLE Command

This command is used to define a table. When a table is created we specify along with the table name its column names with their specific data types

The syntax for the same is

CREATE TABLE <tablename>

(<column name>  <data type> [size],

  <column name>  <data type> [size]);

2. INSERT Command

The insert command is used to add values to the table.

You need to add values for all the columns in the table, but those with a default value defined earlier or the ones in which NULL values are allowed can be left.

The syntax for the insert command is:

INSERT INTO <tablename> [column list]

VALUES (value1, value2,…)

3. SELECT Command

When you want a certain data from your data table, you use the Select Command. In short, it allows you to make queries on your database. You can select a certain row, or column or a combination of both which follows a certain condition.

The most basic syntax for a select command can be as below

SELECT <Column name> ,<column name>,..

FROM <table name>;

This will select all the columns mentioned from the table

Further we can add more conditions to this query to get a certain result. We will cover this as we move ahead in the article

Reordering Columns in Query Results

While displaying your results, you might want to change the order of columns, SQL gives you an easy option to do so using the SELECT Command

For example, SELECT a,b,d,c, 

FROM <table name>;

This will display the columns in order a,b,d and then c where a,b,c and d are the column names.

Eliminating Redundant Data

 While displaying the result, we need to make sure that the data is not repeated. If there is redundant data in the database, it can still be eliminated.

This can be done using the keyword DISTINCT which will show repeated data in a column only one.

This can be applied to specific columns.

The syntax for the same is as below

SELECT DISTINCT <column name>

FROM <table name>;

This will display the specific column mentioned and will remove any redundant data from getting displayed.

Selecting from All the Rows

Now if you want to select all the rows in a specific column, then we can use the keyword ALL or use the symbol *

It is a little different from DISTINCT but almost the same in syntax like this keyword. The DISTINCT keyword eliminates any repeated data in a column whereas the ALL Keyword makes sure that all the rows in a column are displayed irrespective of any repeated values.

The syntax to use the ALL keyword is –

SELECT ALL <column name> from <table name>;

Example: SELECT ALL names from marks;

This will select and display the names column from table marks

Selecting Specific Rows 

When considering a large table, there can  be n number of rows and to view all can be a tough task and waste of time. So we can view only those rows from the table which are required. 

For this we have the WHERE clause which checks row-wise the condition and only those rows which satisfy a condition are displayed.

The syntax to use WHERE clause can be as below-

SELECT <column name>, <column name>, …

FROM <table name>

WHERE <condition>;

Condition Based on a Range

AS we are discussing the WHERE clause where you can give a condition to be met for the rows to be displayed. So this condition can be put up in numerous ways and one such way is to define a range of values.

For example consider the case of table Students where it has columns like name, roll no. class, marks, etc. 

Now if you want to see the students list who have scored in the range of 50-70 then you can specify it as a condition in the where clause.

SELECT name, marks

From Students

WHERE marks between 50 AND 70;

Now this will work as we discussed above.

An important point to note here is the use of AND in the range.

So if you want to include both the upper and lower limits of the range then you can use AND other wise you can use TO.

Condition Based on a List

Now, if you want to give a list of items to check if the value in your tabel matches from any data of the list then you can specify a list along with the WHERE clause.

For example,

SELECT * from Students

WHERE marks IN (22,33,44,55,66);

Here only rows which has marks equal to any of the values in the list given will be displayed.

You can also exclude specific values, for example,

SELECT * from Students

WHERE marks NOT IN (22,33,44,55,66);

This will display all the rows expect those which has marks equal to any of the values in the list.

So this is about SQL processing and understanding its basic syntax, capabilities and working.