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.
The SQL DDL also includes commands for defining views.
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
- 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
- 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
- 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”
- 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
- 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.
- 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]);
- 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,…)
- 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>
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
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.
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.