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:
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 (DML) 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:
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:
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:
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:
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:
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.