What is SQL in DBMS

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.

What is SQL

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.

What are the types of structured query languages (SQL)?

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)
Types of structured query languages

What is a data query language (DQL)?

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
WHERE
GROUP BY attributes HAVING condition
ORDER BY attribute DESC/ASC(descending or ascending order)

What is the data manipulation language

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:

  1. INSERT INTO table
    VALUES new values
  2. DELETE FROM table
    WHERE constraints
  3. MERGE
  4. SELECT attribute
    FROM table
    WHERE condition update table
  5. SET attribute
    WHERE when
    Else
    end

What is the data definition language (DDL)?

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:

  1. CREATE database name;
  2. ALTER attribute_1, attribute_2 parameters;
  3. TRUNCATE TABLE <table name>;
  4. DROP table name;
  5. RENAME to new table name;

What is the data control language (DCL)?

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:

  1. GRANT <privilege list>
  2. On <relation name or view name> to <user list>
  3. REVOKE <privilege list>
  4. On <relation name or view name> from <user list>

What is the transaction control language (TCL)?

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:

What are the various operations performed in SQL

String operations

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:

Set operations

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

Rename operation

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

What are the various clauses in SQL?

Select clause

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:

Select emloyee_name
From employee

In the relational algebra, the query is:
𝚷employee_name(employee)

Where clause

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
Select student_id
From student
Where marks>200

From clause

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.
select*
From student,marks