In this course, we will study SQL, the categories of SQL-data definition language, data manipulation language, data control language, data query language, and transaction control language, various operations performed in SQL-string operations, rename operation and set operations, various clauses-select clause, from clause and where clause. So let us start.
Structured Query Language is a standard Database language that is used to create, maintain, store and retrieve the data in the relational database.
It is outlined over relational algebra and tuple relational calculus.
SQL comes in the form of a package with all major distributions of RDBMS.
SQL consists of both data definition and data manipulation languages.
The data definition properties help one to design and modify the schema, whereas by using data manipulation properties we can store and retrieve data from the database.
Structured query language(SQL) is generally categorized into four categories. The categories are:
1. Data query language (DQL)
2. Data manipulation language (DML)
3. Data control language (DCL)
4. Data definition language (DDL)
5. Transaction control language (TCL)
The data query language is used to fetch the data that is stored in the database. There are various ways to retrieve data from the database. The commands that are used are:
The SELECT command is used for retrieving records from one or more table.
GROUP BY command is used for grouping the same kind of attributes by using some conditions.
ORDER BY command is used for ordering the data into ascending or descending order.
SELECT attribute list FROM R1, R2
GROUP BY attributes HAVING condition
ORDER BY attribute DESC/ASC(descending or ascending order)
Data manipulation language is used for manipulating the data already stored in the database. It can be done using various insertion, deletion, and updation functions. The changes made by using data manipulation language can be rolled back that means they are not permanent. The commands that are used are:
☆ INSERT INTO command is used for the insertion of new data into the table.
☆ DELETE FROM command is used for deleting the data from the table.
☆ MERGE command is used for merging two tables together with the help of some common attributes.
☆ The SET command is used for changing the value to another value using conditions.
☆ INSERT INTO table
VALUES new values
☆ DELETE FROM table
☆ SELECT attribute
WHERE condition update table
☆ SET attribute
It is used to define database schema with the help of a set of commands. The changes done using data definition language can not be undone that means they are permanent. The commands that are used are:
☆ CREATE command is used for creating table, database, etc.
☆ ALTER command is used for adding attributes to an existing relation.
☆ The TRUNCATE command is used for the removal of all the records.
☆ DROP command is used for deleting the records of the given relation.
☆ RENAME command is used for renaming the table, database, etc.
☆ CREATE database name;
☆ ALTER attribute_1, attribute_2 parameters;
☆ TRUNCATE TABLE <table name>;
☆ DROP table name;
☆ RENAME to new table name;
It is used for granting and revoking permissions, rights and other controls of the system. It gives you access to the database. The commands that are used are:
☆ GRANT command is used for granting the access.
☆ REVOKE command is used for taking back the access.
GRANT <privilege list>
On <relation name or view name> to <user list>
REVOKE <privilege list>
On <relation name or view name> from <user list>
It deals with the transactions in the database. These commands can undo the changes done by the other commands by rolling back. These commands can make temporary changes permanent. The commands that are used are:
☆ COMMIT command is used to commit a transaction.
☆ ROLLBACK command is used to rollback transactions in case of error.
☆ SAVEPOINT command sets savepoints within the transactions which saves the transaction done so far temporarily.
☆ SET TRANSACTION command specifies the characteristics of the transaction.
SQL has a string-matching operator for comparing various character strings. The operator “like” is used and it uses patterns that are described using two special characters:
☆ percent (%). The % character matches any substring.
☆ underscore (_). The _ character matches any character.
The set operations union(∪), intersect(∩), and accept(-) operates on various relations. The automatic elimination of duplicates occurs on using the above operations. To retain all duplicates one can use the corresponding multiset versions union all, intersect all and except all.
Let us assume a tuple occurs p times in a and q times in b, then, it occurs:
p+q times in a union all b
min(p,q)times in a intersect all b
max(0,p-q)times in a except all b
Using the as clause we can rename relations and attributes.
old-name as new-name
Let us find the name, id number and fee amount of all students; rename the column name id_number as student_id.
select student_name, student.id_number as student_id, fees
from student, fees
where student.id_number = fees.id_number
The select clause list all the attributes desired in the result of a query that is
corresponding to the projection operation of the algebra.
Example: find the names of all employees in the employee relation:
In the relational algebra, the query is:
The where clause specifies conditions that need to be satisfied by the result. It Corresponds to the selection predicate of the algebra.
To find all student_id from student table who have marks greater than 200
The from clause lists all the relations which are involved in the query. It Corresponds to the Cartesian product operation of the algebra.
Find the Cartesian product student X marks.