Overview of Data Manipulation Language
Data manipulation language or DML is used to control the data in a relational database. The data is manipulated using SELECT, INSERT and UDPATE statements. The most commonly used DML is SQL. DML can either be procedural or declarative.
Explain how to retrieve information using SELECT.
SELECT clause is used to select or retrieve data from the database.For e.g.
I have a Table employee with the fields id, name, salary and I want to view all the data, I can use SELECT statement as shown
SELECT * from employee
Add information using INSERT
INSERT clause is used to enter or insert information in the database.
For e.g. I have a Table employee with the fields id, name, salary and I want to insert some data, I can use INSERT statement as shown
INSERT into employee values (‘1’,’John’,’20000’);
Change information using UPDATE
UPDATE clause is used to update information of a table.For e.g.
I have a Table employee with the fields id, name, salary and I want to change the salary of an id 1, I can use UPDATE statement as shown
UPDATE employee SET emp_salary=’23000’ Where emp_id=’1’;
Remove information using DELETE
DELETE clause is used to delete or remove information from the table.For e.g.
I have a Table employee with the fields id, name, salary and I want to delete the record with id 1, I can use DELETE statement as shown
DELETE FROM employee Where emp_id=1’;
Use Subqueries as your search condition.
Subqueries is a query within a query. It is most commonly used when data from multiple tables is selected and the inner query specifies a condition.
The inner subquery will be processed first which fill fetch the row from appraisal table with id 1 and the check if present in employee table.Example
SELECT id, name FROM employee WHERE name IN (SELECT name FROM appraisal WHERE emp_id= ‘1’);
Displaying and aggregating data