Understanding Basic SQL Queries in DBMS

Data Manipulation Language ( DML ) is the language used to maintain the database in a DMBS. The SQL (Structured Query Language) is the most popular language to manipulate databases.

The DML commands perform following tasks on database.

  1. INSERT
  2. SELECT
  3. DELETE
  4. UPDATE

The SQL being a DML can do above task without any problem. Other than inserting, deleting or querying database, the SQL has advanced commands to change the schema of relations or create  new relations.


I
t is used for maintaining the database and they are called Data Definition Language (DDL) commands. You can create table, delete a table and do other operations to maintain the structure of your database.

INSERT command

 
The INSERT command helps insert data in to the relation. For example, you can insert values in the ‘MANAGER’ relation as follows.
Figure 1: Inserting Data into Manager Relation
Figure 1: Inserting Data into Manager Relation


The schema for Manager relation is given in the previous post about DDL commands.

SELECT command

The SELECT command helps query the database and retrieve the tuples from one or more relations.


There are two types of query – Selection(σ)  and Projection(π).


Selection(σ) 

This unary operator selects all the tuple in a relation that meet specific condition using normal conditional operators and logical operator such as AND, OR and NOT.

For example

σage > 30(Manager), will return all the tuples that has age greater than 30.

 

Figure 2: SELECT statement on MANAGER relation
Figure 2: SELECT statement on MANAGER relation

 

Projection(π)


This unary operator select columns, but you can add selection with those retrieved columns.


For example

πage,salary(Manager), will return two column – age and salary.


Figure 3: PROJECTION on MANAGER relation
Figure 3: PROJECTION on MANAGER relation

Rename operator(ρ)


(ρ)Ename,Sal → Name,Pay(MANAGER)

The rename operator will rename the existing field of a relation to a different specified name. Alter is a DDL command that modifies the relation name.

Figure 4: RENAME operator on MANAGER relation
Figure 4: RENAME operator on MANAGER relation

 


UPDATE command

The UPDATE command modify the value for one or more tuple in the relation. In the following example, we have updated salary of manager whose salary is less than 20000.


 

Figure 5: UPDATE command on MANAGER relation
Figure 5: UPDATE command on MANAGER relation

 

The result of update is as follows

 

 

Figure 6: Result of UPDATE statement on MANAGER relation
Figure 6: Result of UPDATE statement on MANAGER relation

 

 DELETE command

 The DELETE will delete the tuple from the relation. Here is an example of DELETE.

 

 

Figure 7: DELETE command on MANAGER relation
Figure 7: DELETE command on MANAGER relation

 

The following figure show the relation Manager after the delete operation.Note that one entry is already deleted successfully.

Advertisements


 

 

Figure 8: Results of DELETE operation on MANAGER relation
Figure 8: Results of DELETE operation on MANAGER relation

 


Bibliography

Ramakrishnan, Johannes Gehrke and Raghu. 1996. Database Management Systems. McGraw Hill Education; Third edition (1 July 2014).

Wikipedia. n.d. Data definition language. Accessed March 14, 2018. https://en.wikipedia.org/wiki/Data_definition_language.

 


Advertisements