Data Manipulation Language (DML) refers to the type of SQL commands used to retrieve, insert, update, and delete data from a database.
- These operations are crucial for interacting with data in relational databases.
Common DML Statements:
- SELECT: Used to retrieve data from the database.
- INSERT: Used to insert data into a table.
- UPDATE: Used to update existing data within a table.
- DELETE: Used to delete all records from a table.
1.) The SELECT Clause:
The SELECT clause is used to retrieve data from one or more tables. It is the most commonly used SQL command to query the database.
Example: This query retrieves the first name and last name of all employees.
SELECT first_name, last_name FROM employees;2.) The WHERE Clause
The WHERE clause is used to filter records based on specified conditions. It limits the rows returned by a SELECT statement.
Example: This query retrieves all records of employees who belong to department 10.
SELECT * FROM employees WHERE department_id = 10;3.) The FROM Clause
The FROM clause specifies the table(s) from which to retrieve data. It can also specify joins between multiple tables.
Example: Here, the query retrieves data from the employees table where salary is greater than 50,000.
SELECT first_name, last_name FROM employees WHERE salary > 50000;4.) The RENAME Operation
The RENAME operation is used to assign an alias (temporary name) to a table or a column. Aliases are helpful for simplifying queries and enhancing readability.
Example: Here, first_name and last_name are given aliases fname and lname, respectively.
SELECT first_name AS fname, last_name AS lname FROM employees;5.) Tuple Variables
Tuple variables are used in complex queries, especially in subqueries. A tuple variable represents a row of a table in queries.
Example: Here, e is the tuple variable representing rows in the employees table.
SELECT e.first_name FROM employees e WHERE e.salary > 50000;6.) String Operations
SQL allows string operations such as concatenation, searching for substrings, and modifying string data.
- Concatenation: CONCAT(string1, string2)
- Length: LENGTH(string)
- Substring: SUBSTRING(string, start_position, length)
Example: This query concatenates first and last names to display a full name.
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;7.) Ordering the Display of Tuples
The ORDER BY clause is used to sort the result set in ascending or descending order.
Example: This query orders the employees by their salary in descending order.
SELECT * FROM employees ORDER BY salary DESC;8.) Duplicate Tuples
SQL queries return unique results by default, but sometimes you may want to include duplicate rows. This can be done using the ALL keyword or by removing duplicates with DISTINCT.
Example
SELECT department_id FROM employees;To remove duplicates:
SELECT DISTINCT department_id FROM employees;
