DML Commands
Data Manipulation Language (DML) is a subset of SQL used to retrieve, store, modify, delete, insert, and update data in a database.
SELECT
The SELECT statement is used to query and retrieve data from one or more tables.
SELECT column1, column2 FROM table_name WHERE condition;
Practical Example: SELECT employee_name, salary FROM employees WHERE department = 'IT';
INSERT
The INSERT statement is used to add new rows of data to a table.
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
Practical Example: INSERT INTO employees (employee_name, salary) VALUES ('John Doe', 50000);
UPDATE
The UPDATE statement is used to modify existing records in a table.
UPDATE table_name SET column1 = value1 WHERE condition;
Practical Example: UPDATE employees SET salary = 55000 WHERE employee_name = 'John Doe';
DELETE
The DELETE statement is used to remove existing records from a table.
DELETE FROM table_name WHERE condition;
Practical Example: DELETE FROM employees WHERE employee_name = 'John Doe';
- Exam-Oriented Note: Always use a WHERE clause with UPDATE and DELETE statements to avoid modifying or deleting all records in the table accidentally.
- Common Mistake: Forgetting the WHERE clause in an UPDATE or DELETE command, leading to unintentional data modification or data loss.
Implementation of Constraints
Constraints are rules enforced on data columns in a table. They are used to limit the type of data that can go into a table, ensuring the accuracy and reliability of the database.
- NOT NULL: Ensures that a column cannot have a NULL value.
- UNIQUE: Ensures that all values in a column are different.
- PRIMARY KEY: Uniquely identifies each record in a table. It is a combination of NOT NULL and UNIQUE.
- FOREIGN KEY: Uniquely identifies a row/record in another table, establishing a relationship between tables.
- CHECK: Ensures that all values in a column satisfy a specific condition.
- DEFAULT: Sets a default value for a column when no value is specified.
CREATE TABLE employees (emp_id INT PRIMARY KEY, emp_name VARCHAR(50) NOT NULL, age INT CHECK (age >= 18), dept_id INT, FOREIGN KEY (dept_id) REFERENCES departments(dept_id));
Implementation of Joins
A join clause is used to combine rows from two or more tables, based on a related column between them.
| Join Type | Description |
|---|---|
| INNER JOIN | Returns records that have matching values in both tables. |
| LEFT (OUTER) JOIN | Returns all records from the left table, and the matched records from the right table. |
| RIGHT (OUTER) JOIN | Returns all records from the right table, and the matched records from the left table. |
| FULL (OUTER) JOIN | Returns all records when there is a match in either the left or the right table. |
Examples of Joins
SELECT employees.emp_name, departments.dept_name FROM employees INNER JOIN departments ON employees.dept_id = departments.dept_id;
Nested Subqueries
A subquery is a query nested inside another query such as SELECT, INSERT, UPDATE, or DELETE. A subquery is also called an inner query or inner select, while the statement containing the subquery is called the outer query or outer select.
SELECT emp_name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
- Important Observation: The subquery is executed first, and its result is passed to the outer query.
- Exam-Oriented Note: Subqueries can return a single value, a single column with multiple rows, or a table. Use appropriate operators like IN, ANY, or ALL for multi-row subqueries.
Complex Queries
Complex queries involve multiple clauses such as GROUP BY, HAVING, subqueries, and multiple joins combined to extract specific aggregated or filtered datasets.
SELECT department, COUNT(emp_id) AS total_employees FROM employees GROUP BY department HAVING COUNT(emp_id) > 5;
Real-World Application: Generating business reports like monthly sales summaries, identifying top-performing departments, or finding anomalies in transaction records.
Views
A view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
CREATE VIEW high_earners AS SELECT emp_name, salary FROM employees WHERE salary > 80000;
Real-World Application: Views are used for security purposes to restrict access to specific data, and to simplify complex queries by encapsulating them inside a virtual table.
Joined Relations
Joined relations refer to the formal relational algebra concepts implemented via SQL joins, allowing database systems to process Cartesian products combined with selection and projection operations across multiple tables efficiently.
- Cartesian Product (CROSS JOIN): Combines every row from the first table with every row from the second table.
- Natural Join: Automatically joins tables based on columns with the same name and data type in both tables.