DQL (Data Query Language)


Data Query Language (DQL) performs queries on the data within schema objects. It is used to retreive data from a table, based on queries passed to it. It uses only one command i.e. SELECT.


SELECT


As the projection operation of relational algebra, so is the SELECT command . It selects those attributes which satisfy the conditions specified by the WHERE clause. The entire table can also be fetched using SELECT command.


Syntax:


SELECT expressions FROM table_name

WHERE conditions;


DML (Data Manipulation Language)


DML is used to manipulate the data present in a database. It can be used to change data in an existing table and also used to store, delete, modify, insert and update records in a table. The changes made to database using DML are not saved automatically.


The different Types of DML commands are:


  • UPDATE


UPDATE command is used to update or alter data of an existing table.


Syntax:


UPDATE table_name SET col1 = val1, col2 = val2,..., colN = valN

WHERE condition;


Here, col1, col2,..., colN are the column names. And, val1, val2,..., valN is the updated value.


  • INSERT


It is used to insert new rows or data into a table.


Syntax:


For inserting data into a table.

INSERT INTO table_name (col1, col2, col3,..., colN)

VALUES (val1, val2, val3,..., valN);


For inserting data into a table from another table.

INSERT INTO table_name1

SELECT col1, col2,..., colN

FROM table_name2;


Here, table_name1 is the name of table in which data is inserted. And, table_name2 is the name of table from which data is extracted.


  • DELETE:


It is used to remove data from an existing table.


Syntax:


DELETE FROM table_name

WHERE condition; 


  • MERGE:


It is combination of INSERT, UPDATE and DELETE commands of sql. Hence, all these three operations can be performed together by using only MERGE command to handle large databases. But, MERGE command requires a source table to perform all the three operations on the target table.


Source table: It contains the modified and correct data which is to be displayed on the target table. And, the accurate data in target table is obtained by comparing it with the source table. 


Syntax:


MERGE target_table AS TARGET

USING source_table AS SOURCE

ON merge_condition

WHEN MATCHED

THEN UPDATE

  //update syntax

WHEN NOT MATCHED BY TARGET

THEN INSERT

   //insert syntax

WHEN NOT MATCHED BY SOURCE 

THEN DELETE;


Explanation of the above keywords:


MERGE- It specifies the target table.

USING- It specifies the source table.

ON- It specifies the join condition upon which either insertion, updation or deletion takes place.

WHEN- It specifies the actions to be taken based on the result of condition in ON clause.


Now let's consider the below table Food and write SQL queries according to given data.






Query 1. Select all records from the table Food.

Ans- SELCET * FROM Food;


Note: Asterisk (*) is used to select entire data of a table if condition is omitted. And, it is also used to select records according to given conditions specified by "WHERE" clause.

 

Query 2. List the details of food which is rich in either fats or protein.

Ans- SELECT * FROM Food

         WHERE Nutrient = 'Fats' or Nutrient = 'Protein';


Query 3. What is the food name for nutrient Minerals?

Ans- SELECT F_Name FROM Food

         WHERE Nutrient = 'Minerals';


Query 4. Display the S_No. of Cheese.

Ans- SELECT S_No. FROM Food 

         WHERE F_Name = 'Cheese';


Query 5. Display the data of the table Food where food name is cheese.

Ans- SELECT * FROM Food 

         WHERE F_Name= 'Cheese';


Query 6. Show the "F_Name" and "Nutrient" columns of table "Food".

Ans- SELECT F_Name, Nutrient FROM Food;


Query 7. Add another column in the Food table as "Type".

Ans- ALTER TABLE Food

         ADD Type varchar(255);


Query 8. Change the column name F_Name to Food_List of table Food.

Ans- ALTER TABLE Food

         RENAME COLUMN F_Name To Food_List;


Query 9. Add a new food 'Bread' in the table Food whose S_No.is 7 and is rich in carbohydrate.

Ans- INSERT INTO Food (S_No., F_Name, Nutrient)

         VALUES (7, Bread, Carbohydrate);


Query 10. Change the food "Pulse" of table "Food" to "Egg".  

Ans- UPDATE Food 

         SET F_Name = 'Egg'

         WHERE S_No. = 2;


Query 11. Delete "Potato" from the "Food" table.

Ans- DELETE FROM Food

         WHERE F_Name= 'Potato';


Query 12. Delete all records from the table "Food" but not the table itself.

Ans- TRUNCATE TABLE Food;