SQL Data Manipulation Language (DML) Queries

SQL Data Manipulation Language (DML) queries are used to manipulate data within tables in a database. These include operations like inserting, updating, deleting, and retrieving(selecting) data. 

The four types are:

• SELECT
• INSERT
• DELETE
• UPDATE

SELECT Query

Used to query and retrieve data from a table.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example

SELECT FirstName, LastName, Salary
FROM Employees
WHERE Salary > 40000;

UPDATE

Used to modify existing records in a table.

Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example

UPDATE Employees
SET Salary = 55000
WHERE EmployeeID = 1;

INSERT

Used to add new records into a table.

Syntax:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Example:

INSERT INTO Employees (EmployeeID, FirstName, LastName, Salary)
VALUES (1, 'John', 'Doe', 50000);

DELETE

Used to remove existing records from a table.

Syntax

DELETE FROM table_name
WHERE condition;

Example

DELETE FROM Employees
WHERE EmployeeID = 1;

Data Aggregation and Summarization

Queries can perform calculations and aggregations on database data, providing summarized views.

Example: Query to summarize data using an aggregate function

SELECT department, AVG(salary) AS average_salary FROM employees GROUP BY department;

This query calculates the average salary (average_salary) for each department in the employees table.

Joining and Relational Views

Queries can combine data from multiple tables through joins, offering a relational view. This is essential in relational databases where related data is stored in separate tables.

Example: Query that joins two tables to provide combined information

SELECT orders.order_id, customers.name, orders.order_date
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

This query joins the orders table with the customers table to show each order along with the customer's name.

Filtering and Sorting

Queries can filter data based on specific criteria, showing only the data that meets these conditions.

They can also sort data, allowing you to view it in a specified order, like ascending or descending by date or alphabetically by name.

Example: Query that filters and sorts data

SELECT name, department, salary 
FROM employees 
WHERE department = 'Marketing' 
ORDER BY salary DESC;

This query selects employees in the Marketing department and sorts them by their salary in descending order.

Conditional Logic

Advanced queries can incorporate conditional logic (using CASE, IF statements, etc.), which means the view can dynamically adjust based on certain conditions within the data.

Example: Query with conditional logic to create different output columns

SELECT name,
       CASE 
           WHEN salary > 50000 THEN 'High'
           ELSE 'Low'
       END AS salary_level
FROM employees;

This query assigns a salary_level based on the salary of each employee.

Creating Virtual Tables (Views)

In SQL, there's also a concept of a "view" which as a saved query. These views act like virtual tables, representing a specific query's view of the data. They are not physically stored but are defined and saved in the database schema.

Example: Creating a view to save a specific query

CREATE VIEW high_earning_employees AS
SELECT name, salary 
FROM employees 
WHERE salary > 100000;

This creates a view high_earning_employees which contains names and salaries of employees earning more than 100,000.