SQL Interview Questions CHEAT SHEET (2025)

Question: What is a primary key and a foreign key in SQL?

Answer:

1. Primary Key:

A primary key is a column or a set of columns in a database table that uniquely identifies each row in that table. The primary key ensures that no two rows in a table have the same value for the primary key column(s). A primary key must have the following properties:

• Uniqueness: Each value in the primary key column must be unique. No two rows can have the same primary key value.
• Non-null: The primary key column cannot contain NULL values. Every row must have a valid value for the primary key.
• Single and Composite: A primary key can consist of a single column or multiple columns. A composite primary key is created when more than one column is used together to form a unique identifier.
• Indexing: Most databases automatically create an index on the primary key, which helps in optimizing search and retrieval operations.

Example of a Primary Key:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,       -- EmployeeID is the primary key
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50)
);

Here, the EmployeeID column is the primary key for the Employees table, ensuring that each EmployeeID value is unique and non-null.

2. Foreign Key:

A foreign key is a column or a set of columns in one table that is used to establish a link between the data in two tables. A foreign key in one table points to the primary key in another table, creating a relationship between the two tables. The foreign key ensures referential integrity by enforcing that every value in the foreign key column(s) must either be NULL or match a value in the referenced table’s primary key or unique column.

• Referential Integrity: The foreign key enforces referential integrity by ensuring that the value in the foreign key column corresponds to a valid row in the referenced (parent) table.
• Relationship: A foreign key defines a one-to-many or many-to-one relationship between tables. For example, one employee can belong to one department, but one department can have many employees.

Example of a Foreign Key:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

In this example, the DepartmentID column in the Employees table is a foreign key that references the DepartmentID column in the Departments table. This ensures that every employee is associated with a valid department that exists in the Departments table.

Key Differences Between Primary Key and Foreign Key:

Aspect	Primary Key	Foreign Key
Purpose	Uniquely identifies each record in the table.	Establishes a relationship between two tables.
Uniqueness	Values must be unique across the table.	Values may not be unique and can be repeated across rows.
Nullability	Cannot contain NULL values.	Can contain NULL values, unless explicitly restricted.
References	Points to data within the same table.	Points to the primary key of another table (parent table).
Indexing	Automatically indexed by the database.	May or may not be indexed by the database.
Relation Type	Defines no relationships, but uniquely identifies records.	Defines a relationship (usually one-to-many) between tables.
Enforcement of Integrity	Enforces uniqueness and non-null constraint.	Enforces referential integrity between tables.

Example of a Relationship Between Primary and Foreign Keys:

Let’s say we have two tables: Departments and Employees. The Departments table stores information about each department, and the Employees table stores information about employees. Each employee belongs to one department.

Departments Table (Primary Key: DepartmentID):

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,     -- Primary Key
    DepartmentName VARCHAR(50)
);

Employees Table (Foreign Key: DepartmentID):

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,       -- Primary Key
    EmployeeName VARCHAR(50),
    DepartmentID INT,                 -- Foreign Key
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)  -- Foreign Key
);

In this example:

• DepartmentID in the Departments table is a primary key that uniquely identifies each department.
• DepartmentID in the Employees table is a foreign key that references the DepartmentID in the Departments table, creating a relationship between the two tables. This ensures that every employee is assigned to a valid department.

Question: What is the concept of GROUP BY in SQL?

Answer:

The GROUP BY clause in SQL is used to group rows that have the same values in specified columns into summary rows, like “total”, “average”, or “count”. This clause is often used in conjunction with aggregate functions such as COUNT(), SUM(), AVG(), MAX(), and MIN() to perform calculations on each group of rows.

The GROUP BY statement is particularly useful when you want to organize and summarize your data based on certain columns and perform aggregate calculations on each group.

Key Points:

• Grouping Rows: The GROUP BY clause groups rows that have the same values into summary rows.
• Aggregate Functions: Typically used with aggregate functions like COUNT(), SUM(), AVG(), etc., to perform calculations on each group.
• Column Selection: When using GROUP BY, you must include in the SELECT clause either the grouped columns or apply aggregate functions to other columns.
• HAVING Clause: Often used in conjunction with GROUP BY to filter groups based on aggregate values, similar to how the WHERE clause filters rows.

Syntax:

SELECT column1, column2, aggregate_function(column3)
FROM table_name
GROUP BY column1, column2;

Example 1: Basic Usage of GROUP BY

Let’s say we have a Sales table that contains information about sales transactions:

SaleID	Product	Amount	SaleDate
1	ProductA	100	2024-01-01
2	ProductB	150	2024-01-01
3	ProductA	200	2024-01-02
4	ProductB	100	2024-01-02
5	ProductA	300	2024-01-02

To calculate the total sales amount for each product, you can use the GROUP BY clause like this:

SELECT Product, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY Product;

Output:

Product	TotalSales
ProductA	600
ProductB	250

Explanation:

• The GROUP BY Product statement groups the rows by the Product column.
• The SUM(Amount) function calculates the total sales amount for each product.

Example 2: GROUP BY with HAVING

If you want to find products with total sales greater than a certain value, you can use the HAVING clause to filter the groups after aggregation.

SELECT Product, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY Product
HAVING SUM(Amount) > 300;

Output:

Product	TotalSales
ProductA	600

Explanation:

• The HAVING clause filters out products where the total sales amount is less than or equal to 300, leaving only ProductA with total sales greater than 300.

Rules and Considerations:

• Columns in SELECT: In the SELECT clause, you can only include columns that are either part of the GROUP BY clause or are used with aggregate functions. For example, you cannot select a column that isn’t part of the grouping unless it’s aggregated.

• Order of Clauses: The GROUP BY clause is placed after the WHERE clause (if any) and before the ORDER BY clause (if any).

Example with Multiple Columns:

You can also group by multiple columns. For instance, if you want to calculate the total sales amount for each product on each date:

SELECT Product, SaleDate, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY Product, SaleDate;

Output:

Product	SaleDate	TotalSales
ProductA	2024-01-01	100
ProductB	2024-01-01	150
ProductA	2024-01-02	500
ProductB	2024-01-02	200

Key Takeaways:

• GROUP BY is used to group rows based on one or more columns.
• It works with aggregate functions to summarize data.
• HAVING is used to filter the groups created by GROUP BY.
• You can group by multiple columns to create more granular groupings.

Read More

If you can’t get enough from this article, Aihirely has plenty more related information, such as SQL interview questions, SQL interview experiences, and details about various SQL job positions. Click here to check it out.