Structured Query Language (SQL)

Data Retrieval (DQL)

• SELECT and FROM: The fundamental components used to specify which columns to retrieve and from which table the data originates. Using * retrieves all columns, while specific names limit the output.
• Filtering with WHERE: This clause restricts the result set to only those rows that satisfy a specific boolean condition, such as equality, range comparisons, or pattern matching.

Data Manipulation (DML)

• INSERT INTO: Used to add new records to a table. It requires specifying the target columns and the corresponding values to be stored.
• UPDATE: Modifies existing data within a table. It is typically paired with a WHERE clause to ensure only specific records are changed.
• DELETE: Removes records from a table. Like the update command, it relies on filtering to prevent the accidental removal of all data.

Relational Operations

• INNER JOIN: A method to combine rows from two or more tables based on a related column (usually a primary key in one and a foreign key in the other).
• Subqueries (Nested SELECT): A query nested inside another query, allowing for complex filtering where the criteria for the outer query depends on the results of the inner query.

• Check the WHERE Clause: In exams, always verify if an UPDATE or DELETE statement includes a WHERE clause. Without it, the operation applies to every single row in the table, which is a common trick question.

• Order of Operations: Remember the logical sequence: SELECT identifies columns, FROM identifies the source, WHERE filters rows, and ORDER BY sorts the final output. Writing them out of order will result in syntax errors.

• Wildcard Precision: When using the LIKE operator, ensure the wildcard % is placed correctly. For example, 'A%' finds values starting with A, while '%A' finds values ending with A.

• JOIN Logic: Always identify the common field (Primary Key/Foreign Key) before writing a JOIN. If the join condition is missing or incorrect, the result will be a Cartesian product (every row matched with every other row), which is rarely the intended answer.

• Null Values: Students often forget that NULL represents the absence of data. Comparing a value to NULL using = will fail; instead, the specific syntax IS NULL or IS NOT NULL must be used.

• Case Sensitivity: While SQL keywords (like SELECT) are usually case-insensitive, the actual data stored in the tables (like names or cities) may be case-sensitive depending on the database configuration.

• Sorting Defaults: If an ORDER BY clause does not specify ASC (ascending) or DESC (descending), the database will default to ascending order.