SQL is important because it is the standard language for managing and manipulating databases, which used for storing and retrieving data in many applications. Learning SQL opens up job opportunities such as database administrator, data analyst, and software developer. Big companies like Google, Facebook, Amazon, and Microsoft use SQL to manage their vast amounts of data. After reading our interview-based blog, you will be better prepared for SQL job interviews, understand common questions, and know how to answer them confidently.

Beginner SQL Interview Questions

What is SQL?

SQL stands for Structured Query Language, a standard language for interacting with relational databases. It allows you to perform CRUD operations—Create, Read, Update, and Delete—on database records.

Example:

1SELECT * FROM customers;
2

What is a Database?

A database is an organized collection of structured data stored electronically in a computer system, managed by database management systems (DBMS).

Example: A customer database containing tables for customer information, orders, and products.

What is a Table in SQL?

A table is a collection of related data entries consisting of rows and columns. Each table has a unique name within a database.

Example: A customers table with columns like customer_id, name, email, and phone.

What are SQL Dialects?

SQL dialects are variations of SQL used by different database management systems. Each RDBMS may have its own set of SQL extensions. Examples include MySQL, PostgreSQL, SQL Server, SQLite, and Oracle SQL.

What is a Primary Key?

A primary key is a unique identifier for a record in a table. It ensures that each record is unique and not null.

Example: In a customers table, the customer_id can be the primary key.

What is a Foreign Key?

A foreign key is a column or a set of columns that establishes a link between data in two tables, ensuring referential integrity.

Example: The customer_id in an orders table referencing the customer_id in the customers table.

What is Normalization?

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing large tables into smaller ones and defining relationships between them.

What is a Join? Explain Different Types.

A join is used to combine rows from two or more tables based on a related column. Types of joins include:

• INNER JOIN: Returns records with matching values in both tables.
• LEFT JOIN (LEFT OUTER JOIN): Returns all records from the left table and matched records from the right table.
• RIGHT JOIN (RIGHT OUTER JOIN): Returns all records from the right table and matched records from the left table.
• FULL JOIN (FULL OUTER JOIN): Returns all records when there is a match in either left or right table.

Example:

1SELECT customers.name, orders.order_id
2FROM customers
3INNER JOIN orders ON customers.customer_id = orders.customer_id;

What is a Subquery?

A subquery is a query nested inside another query. It can be used in SELECT, INSERT, UPDATE, or DELETE statements.

Example:

1SELECT name FROM customers
2WHERE customer_id IN (SELECT customer_id FROM orders WHERE amount > 100);

What is an Index?

An index is a database object that improves the speed of data retrieval operations on a table at the cost of additional space and maintenance overhead.

Example:

1CREATE INDEX idx_email ON customers(email);

What are Aggregate Functions? Give Examples.

Aggregate functions perform calculations on multiple rows of a table and return a single result. Examples include COUNT(), SUM(), AVG(), MAX(), and MIN().

Example:

1SELECT COUNT(*) FROM orders;

DELETE vs TRUNCATE

• DELETE: Removes specified rows from a table and can be rolled back. It activates triggers.
• TRUNCATE: Removes all rows from a table without logging individual row deletions. It cannot be rolled back and does not activate triggers.

What is a View?

A view is a virtual table created by a query. It contains data from one or more tables and can simplify complex queries and improve security.

Example:

1CREATE VIEW customer_orders AS
2SELECT customers.name, orders.order_id, orders.amount
3FROM customers
4JOIN orders ON customers.customer_id = orders.customer_id;

What is a Stored Procedure?

A stored procedure is a set of SQL statements that can be stored and executed on the database server. It helps encapsulate logic, improve performance, and maintain consistency.

Example:

1CREATE PROCEDURE AddCustomer (IN name VARCHAR(50), IN email VARCHAR(50))
2BEGIN
3    INSERT INTO customers (name, email) VALUES (name, email);
4END;

What is a Transaction?

A transaction is a sequence of one or more SQL operations treated as a single unit of work. It ensures data integrity by following the ACID properties (Atomicity, Consistency, Isolation, Durability).

What are ACID Properties?

• Atomicity: Ensures that all operations in a transaction are completed or none are.
• Consistency: Ensures that the database remains in a consistent state before and after the transaction.
• Isolation: Ensures that transactions do not interfere with each other.
• Durability: Ensures that the results of a transaction are permanently stored.

What is a Trigger?

A trigger is a set of SQL statements that automatically execute in response to specific events on a table, such as INSERT, UPDATE, or DELETE.

Example:

1CREATE TRIGGER log_order_changes
2AFTER UPDATE ON orders
3FOR EACH ROW
4BEGIN
5    INSERT INTO order_logs (order_id, old_status, new_status)
6    VALUES (OLD.order_id, OLD.status, NEW.status);
7END;

What is a Cursor?

A cursor is a database object used to retrieve, manipulate, and navigate through a result set row by row.

Example:

1DECLARE cursor_name CURSOR FOR
2SELECT name FROM customers;
3
4OPEN cursor_name;
5
6FETCH NEXT FROM cursor_name INTO @name;
7WHILE @@FETCH_STATUS = 0
8BEGIN
9    PRINT @name;
10    FETCH NEXT FROM cursor_name INTO @name;
11END;
12
13CLOSE cursor_name;
14DEALLOCATE cursor_name;

CHAR vs VARCHAR

• CHAR: A fixed-length character data type. It pads the remaining space with blanks.
• VARCHAR: A variable-length character data type. It stores only the actual length of the string.

Example:

1CREATE TABLE example (
2    fixed_length CHAR(10),
3    variable_length VARCHAR(10)
4);

UNION vs UNION ALL

• UNION: Combines the result sets of two or more SELECT statements and removes duplicates.
• UNION ALL: Combines the result sets and includes duplicates.

Example:

1SELECT name FROM customers
2UNION
3SELECT name FROM suppliers;
4
5SELECT name FROM customers
6UNION ALL
7SELECT name FROM suppliers;
8

What is a Constraint?

A constraint is a rule enforced on data columns to ensure data integrity. Common constraints include NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT.

What is a UNIQUE Constraint?

The UNIQUE constraint ensures that all values in a column are unique. It allows null values, but only one null value per column.

Example:

1ALTER TABLE customers
2ADD CONSTRAINT unique_email UNIQUE (email);

What is a DEFAULT Constraint?

The DEFAULT constraint provides a default value for a column when no value is specified during an insert operation.

Example:

1ALTER TABLE orders
2ADD CONSTRAINT default_status DEFAULT 'pending' FOR status;

What is a Composite Key?

A composite key is a primary key composed of two or more columns that uniquely identify a row in a table.

Example:

1CREATE TABLE enrollment (
2    student_id INT,
3    course_id INT,
4    PRIMARY KEY (student_id, course_id)
5);

What is a Data Type in SQL?

A data type defines the kind of value that can be stored in a column. Common data types include INT, VARCHAR, DATE, and FLOAT.

How to Create a Table?

Use the CREATE TABLE statement to define a new table with specified columns and data types.

Example:

1CREATE TABLE products (
2    product_id INT PRIMARY KEY,
3    name VARCHAR(50),
4    price DECIMAL(10, 2)
5);

How to Update Data in a Table?

Use the UPDATE statement to modify existing data in a table.

Example:

1UPDATE products
2SET price = 19.99
3WHERE product_id = 1;

How to Delete Data from a Table?

Use the DELETE statement to remove specific rows from a table.

Example:

1DELETE FROM customers
2WHERE customer_id = 1;

How to Add a Column to an Existing Table?

Use the ALTER TABLE statement with ADD to include a new column.

Example:

1ALTER TABLE employees
2ADD email VARCHAR(100);

How to Remove a Column from a Table?

Use the ALTER TABLE statement with DROP COLUMN to delete a column.

Example:

1ALTER TABLE employees
2DROP COLUMN email;

Clustered vs Non-Clustered Index

• Clustered Index: Determines the physical order of data in a table. Each table can have only one clustered index.
• Non-Clustered Index: Does not alter the physical order of data and creates a separate structure to hold the index. A table can have multiple non-clustered indexes.

How to Create an Index?

Use the CREATE INDEX statement to add an index to a table.

Example:

1CREATE INDEX idx_name ON customers(name);

How to Find Duplicate Records?

Use the GROUP BY clause with HAVING to identify duplicates.

Example:

1SELECT email, COUNT(*)
2FROM customers
3GROUP BY email
4HAVING COUNT(*) > 1;

How to Remove Duplicate Records?

Use a common table expression (CTE) with ROW_NUMBER() to identify and delete duplicates.

Example:

1WITH CTE AS (
2    SELECT email, ROW_NUMBER() OVER (PARTITION BY email ORDER BY customer_id) AS row_num
3    FROM customers
4)
5DELETE FROM CTE
6WHERE row_num > 1;