SQL — Queries & Database Design

Primary Key

A unique identifier for a record in a database table.

Foreign Key

A field in a table that uniquely identifies a row in another table.

What is a JOIN operation in SQL?

A JOIN operation is used to combine rows from two or more tables based on a related column.

Normalization

The process of organizing data to minimize redundancy and improve data integrity.

Denormalization

The process of combining normalized tables to improve read performance, often at the expense of write performance.

SELECT

An SQL command used to retrieve data from a database.

INSERT

An SQL command used to add new rows to a table.

UPDATE

An SQL command used to modify existing records in a table.

DELETE

An SQL command used to remove records from a table.

What does the SQL keyword 'DISTINCT' do?

It removes duplicate rows from the result set of a query.

HAVING clause

Used to filter records that work on aggregated data after the GROUP BY clause.

What is a subquery?

A subquery is a query nested inside another query, often used to perform operations in multiple steps.

Index

A database object that improves the speed of data retrieval operations on a table.

Aggregate Functions

Functions that perform calculations on a set of values and return a single value, such as SUM, AVG, COUNT.

What is a stored procedure?

A stored procedure is a set of SQL statements that can be executed as a unit to perform a specific task.

Transaction

A sequence of one or more SQL operations that are treated as a single unit of work.

ACID properties

Atomicity, Consistency, Isolation, Durability — properties that ensure reliable processing of database transactions.

What is a view in SQL?

A view is a virtual table based on the result set of a SELECT query.

Data Definition Language (DDL)

A subset of SQL commands used to define and modify database structures, like CREATE, ALTER, DROP.

Data Manipulation Language (DML)

A subset of SQL commands used to manipulate data in a database, like SELECT, INSERT, UPDATE, DELETE.

Data Control Language (DCL)

A subset of SQL commands used to control access to data, like GRANT and REVOKE.

Constraint

A rule applied to columns in a database table to enforce data integrity, such as UNIQUE, NOT NULL, CHECK.

What is the purpose of the SQL 'GROUP BY' clause?

It is used to arrange identical data into groups, often used with aggregate functions.

What is an SQL trigger?

A trigger is a procedural code that is automatically executed in response to certain events on a particular table or view.

UNION vs UNION ALL

UNION combines results from multiple queries and removes duplicates; UNION ALL includes all duplicates.

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only matching rows from both tables, while LEFT JOIN returns all rows from the left table and matched rows from the right.

What is a self-join?

A self-join is a regular join, but the table is joined with itself.

What is a composite key?

A primary key that consists of two or more columns used to identify a record uniquely.

What is a schema in SQL?

A schema is a collection of database objects, including tables, views, and procedures, associated with a database.

Explain the use of the 'LIKE' operator in SQL.

The 'LIKE' operator is used in a WHERE clause to search for a specified pattern in a column.

What is SQL injection?

A code injection technique that might destroy your database, it is one of the most common web hacking techniques.

Explain the term 'database lock'.

A mechanism to prevent simultaneous access to data in a database to ensure data integrity.

Explain the purpose of SQL 'JOIN' types: INNER, LEFT, RIGHT, FULL.

These JOIN types determine how tables are combined: INNER returns matching rows, LEFT returns all left table rows, RIGHT returns all right table rows, FULL returns all rows when there's a match in one of the tables.

What is the purpose of the SQL 'LIMIT' clause?

It specifies the maximum number of records to return from a query.