When we have to insert records into table or get specific record from the table, or need to change some record, or delete some record or perform any other actions on records in the database, we need to have some media to perform it. DML helps to handle user requests. It helps to insert, delete, update, and retrieve the data from the database. Let us see some of them.
Table of Contents
Select
Select command helps to pull the records from the tables or views in the database. It either pulls the entire data from the table/view or pulls specific records based on the condition. It can even retrieve the data from one or more tables/view in the database.
The basic SELECT command is
SELECT * FROM table_name; -- retrieves all the rows and columns from table table_name and displays it in tabular form. SELECT COLUMN1, COLUMN2, COLUMN3 from table_name; -- retrieves only 3 columns from table table_name SELECT t1.COLUMN1, t2.COLUMN1 FROM table_name1 t1, table_name2 t2 WHERE t1.COLUMN2 = t2.COLUMN2; -- Combines 2 tables and retrieves specific columns from both the tables.
Some examples of SELECT:
SELECT * FROM STUDENT; -- All the columns are retrieved and displayed in below format
SELECT STUDENT_NAME, ADDRESS FROM STUDENT; -- Retrieves only name and address from STUDENT table
SELECT e.EMPLOYEE_NAME, e.ADDRESS, e.PHONE_NUMBER, d.DEPARTMENT_NAME FROM EMPLOYEE e, DEPARTMENT d WHERE E.DEPARTMENET_ID = D.DEPARTMENT_ID; -- Displays employee name, address, phone number and the department name for which he works, by joining EMPLOYEE and DEPARTMENT tables.
General syntax of SELECT is:
SELECT column_list FROM table-name [WHERE Clause] [GROUP BY clause] [HAVING clause] [ORDER BY clause];
Where
WHERE Clause – here we can specify the filter conditions to the query. We can add any number of conditions.
GROUP BY – We can combine specific categories of columns together and show the results. For example, there are multiple employees working in different department. Using group by option we can display how many employees are working in each department.
SELECT d.DEPATMENT_NAME, COUNT (e.DEPATMENT_ID) total_emp_count FROM EMPLOYEE e, DEPARTMENT d WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID – this condition pulls the matching employees GROUP BY e.DEPARTMENT_ID;
In the above query instead of count (e.DEPARTMENT_ID), we can give count (1). Both are same.
Some of the Group functions are
Count – it counts the total number of records in the table/s after applying ‘where’ clause. If where clause is not specified, it gives the total number of records in the table. If Group by clause is applied, it filters the records based on where clause (if any), then groups the records based on the columns in group by clause and gives the total count of records in each grouped category.
SUM – It totals the value in each numeric column. We can use this function to find the total marks of a student, total salary of an employee in a specific period etc.
AVG – It gives the average value of a column, provided column has numeric value. E.g.: Average age of students present in particular class.
MAX – It gives the maximum value in a column. For example, highest scorer in the class can be retrieved by MAX function.
MIN– It gives the minimum value in a column. For example, lowest paid employee in a department can be obtained by MIN.
These group functions can be used with group by clause or without it.
Having Clause – Using this clause we can add conditions to the grouped categories and filter the records. For examples, if we want to display the details of the department which has more than 100 employees.
SELECT d.DEPATMENT_NAME, COUNT (e.DEPATMENT_ID) total_emp_count FROM EMPLOYEE e, DEPARTMENT d WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID – this condition pulls the matching employees GROUP BY e.DEPARTMENT_ID HAVING COUNT(e.DEPATMENT_ID)>100; -- filters more than 100 employees present in specific department. We cannot give this condition in the where clause as this is the result of Group by clause.
ORDER BY – This clause helps to sort the records that are retrieved. By default, it displays the records in ascending order of primary key. If we need to sort it based on different columns, then we need to specify it in ORDER BY clause. If we need to order by descending order, then DESC keyword has to be added after the column list.
SELECT * FROM EMPLOYEE ORDER BY EMPLOYEE_NAME DESC; -- Displays all records in descending order of employee name
We can combine two or more columns into one column by using || in select statement.
SELECT EMP_FIRST_NAME || ‘ ‘ || EMP_LAST_NAME as emp_name FROM EMPLOYEE WHERE EMP_ID = 1001; -- Here first name and last name of employee with id 1001 to show it as emp_name
Insert
Insert statement is used to insert new records into the table. The general syntax for insert is as follows:
INSERT INTO TABLE_NAME (col1, col2, col3,...colN) VALUES (value1, value2, value3,...valueN);
It inserts value to each column from 1 to N. When we insert the data in this way, we need to make sure datatypes of each column matches with the value we are inserting. Else, data will not be inserted or will insert wrong values. Also, if there is any foreign key constraint on the table, then we have to make sure foreign key value already exists in the parent table.
INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, ADDRESS, DEPT_ID) VALUES (10001, ‘Joseph’, ‘Troy’, 11101); -- Here dept_id 11101 is a foreign key and it has be inserted into department table before inserting into the employee table.
We can insert the values into the table without specifying the columns, provided we enter the value in the order the table structure is. Imagine table structure for Employee is as follows and then we can insert the record without specifying column names as below:
INSERT INTO EMPLOYEE VALUES (10001, ‘Joseph’, ‘Troy’, 11101);
If we are specifying the column list then we need not insert it in the order of table structure. It inserts the values in the order the column is listed in the INSERT statement.
INSERT INTO EMPLOYEE (EMP_ID, DEPT_ID, ADDRESS, EMP_NAME) VALUES (10001, 11101,’Troy’, ‘Joseph’);
We can even copy some of the column values from the existing table. Suppose we have to copy emp_id, emp_name from Employee table to some temporary table. Then we can write as follows: (Assuming here that datatype in both the tables are same and emp_name has enough buffers to store both first name and last name combined).
INSERT INTO TEMP_EMP SELECT e.EMP_ID, e.EMP_FIRST_NAME || ‘ ‘|| e.EMP_LAST_NAME as emp_name FROM EMPLOYEE e;
Update
Update statement is used to modify the data value in the table. General syntax for update is as below:
UPDATE table_name SET column_name1 = value1, column_name2 = value2, ... column_nameN = valueN, [WHERE condition]
Imagine, an employee has changed his address and it needs to be updated in the Employee table.
UPDATE EMPLOYEE SET ADDRESS = ‘Clinton Township’ WHERE EMP_ID = 10110;
If we do not specify ‘WHERE’ condition in the ‘UPDATE’ statement, then it will update the whole table with new address. Hence we have to specify which record/employee has to be updated with new address.
Suppose there is increment in the salary of all the employees by 10% and this has to be updated in the Employee table. How will we write update statement? Do we need to specify the WHERE clause? Can we specify the arithmetic calculation in the UPDATE statement?
UPDATE EMPLOYEE SET Salary = salary+ (salary*0.1);
This is how we update the salary.
Delete
Using Delete statement, we can delete the records in the entire table or specific record by specifying the condition.
DELETE FROM table_name [WHERE condition];
Suppose we have to delete an employee with id 110 from Employee table. Then the delete statement would be
DELETE FROM EMPLOYEE WHERE EMP_ID = 110;
If we do not specify the condition, then it would delete entire record from the Employee table. This statement is different from TRUNCATE in two ways:
- Using DELETE statement, we can delete few records by specifying the condition. If we do not specify the condition, it deletes entire records in table. Whereas TRUNCATE deletes all the records in the table.
- DELETE statement simply removes records from the table, whereas TRUNCATE statement frees the space occupied by the data. Hence TRUNCATE is more efficient than DELETE, when we have to empty the table.