SQL Questions

Conceptual questions going over SQL functions

Relational DBMS

In this DBMS, data is stored in table formatwhich allows for efficient data organization and retrieval through predefined relationships between tables.

Non-Relational DBMS

In this DBMS, data is stored in various formats such as documents, key-value pairs, or graphs, allowing for flexibility and scalability without rigid schemas.

What is SQL?

SQL (Structured Query Language) is a standard programming language used to

interact with relational databases. It is used to store, retrieve, update, and delete

data. SQL is also used to create and modify database structures such as tables,

views, and indexes.

Example:

SELECT * FROM Employees;

This query retrieves all the records from the Employees table.

What is a database?

A database is an organized collection of data that is stored and managed

electronically. It allows users to efficiently store, retrieve, update, and manage

data. Databases are used to handle large amounts of information in various

applications such as websites, business systems, and applications.

Example:

A customer database in an e-commerce website may store customer details like

name, email, contact number, and purchase history.

What are the types of SQL commands?

SQL commands are categorized into five types based on their functionality:

1.

DDL (Data Definition Language) – Defines the structure of the database.

CREATE, ALTER, DROP, TRUNCATE

2.

DML (Data Manipulation Language) – Manages data stored in the database.

SELECT, INSERT, UPDATE, DELETE

3.

DCL (Data Control Language) – Controls access to the data.

GRANT, REVOKE

4.

TCL (Transaction Control Language) – Manages transactions in the

database.

COMMIT, ROLLBACK, SAVEPOINT

What is a primary key?

A Primary Key Primary Key?

is a column or a combination of columns in a table that uniquely identifies

each row in that table. It does not allow NULL values and must always contain unique

values.

Key Features:

Uniquely identifies each record

Cannot have duplicate values

Cannot contain NULL values

Only one primary key is allowed per table

What is a foreign key?

A Foreign Key is a column or combination of columns in one table that refers to the Primary

Key in another table. It is used to create a relationship between two tables and enforce

referential integrity.

Key Features:

Establishes a relationship between two tables

Can contain duplicate values

Can accept NULL values

Helps maintain data consistency

What is a unique key?

A UNIQUE Key is a constraint that ensures all values in a column or combination of

columns are distinct across all rows in the table. It prevents duplicate values but allows

NULL values (only one NULL value in most databases).

Key Features:

Ensures uniqueness of each record in the column

Allows one NULL value (depending on the database)

Multiple UNIQUE keys can be defined in a table

Helps maintain data integrity

What is the difference between a primary key and unique key?

A primary key is used to uniquely identify each record in a table and cannot contain NULL values, while a unique key also enforces uniqueness but can accept NULL values and allows for multiple unique keys in a table.

What is a NOT NULL constraint?

The NOT NULL constraint ensures that a column cannot have NULL values. It is used to

enforce that every row must have a value in that column.

Key Features:

Prevents insertion of NULL values

Ensures mandatory fields have data

Can be applied to one or more columns

What is a default constraint?

The Default Constraint provides a default value for a column when no value is specified

during the insertion of a new record.

Key Features:

Automatically assigns a default value if no value is provided

Helps avoid NULL values in specific columns

Can be applied to any data type

What is the difference between delete, truncate, and drop?

These are SQL commands used for data and structure manipulation. Delete removes specific rows from a table, Truncate removes all rows quickly without logging individual row deletions, and Drop deletes the entire table structure from the database.

What is the difference between WHERE and HAVING?

The WHERE clause filters records before aggregation, while the HAVING clause filters records after aggregation or grouping. HAVING is typically used with GROUP BY to apply conditions on aggregated data.

What are Joins in SQL?

Joins in SQL are used to combine data from two or more tables based on a related column

between them.

Types of Joins:

1.

INNER JOIN – Returns only matching rows from both tables.

2.

LEFT JOIN – Returns all rows from the left table and matching rows from the right table.

3.

RIGHT JOIN – Returns all rows from the right table and matching rows from the left

table.

4.

FULL JOIN – Returns all rows from both tables (matching and non-matching).

5.

SELF JOIN – Joins a table with itself.

6.

CROSS JOIN – Returns the Cartesian product of both tables (all possible combinations).

What is INNER JOIN?

INNER JOIN is used to combine rows from two or more tables based on a matching

condition between them. It returns only those records where the specified condition is true in

both tables.

Key Features:

Returns matching rows from both tables

Ignores unmatched rows

Most commonly used type of join

What is LEFT JOIN?

LEFT JOIN is used to return all records from the left table and the matching records from the

right table. If no match is found, the result will contain NULL values from the right table.

Key Features:

Returns all rows from the left table

Returns matching rows from the right table

Displays NULL for non-matching rows from the right table

What is RIGHT JOIN?

RIGHT JOIN is used to return all records from the right table and the matching records from the

left table. If no match is found, the result will contain NULL values from the left table.

Key Features:

Returns all rows from the right table

Returns matching rows from the left table

Displays NULL for non-matching rows from the left table

What is FULL JOIN?

FULL JOIN combines the results of both LEFT JOIN and RIGHT JOIN. It returns all records

from both tables, with matching rows from both sides where available. If there is no match, the

result will contain NULL values on the side where no match was found.

Key Features:

Returns all rows from both tables

Displays NULL where there is no match

Useful to find unmatched records in both tables

What is Self Join?

Self Join is a type of join where a table is joined with itself to compare rows within the same table.

It is used when a table contains a hierarchical relationship or when comparing values in the same

table.

Key Features:

Joins a table with itself

Requires table aliases to differentiate table instances

Used to compare rows within the same table

What is Cross Join?

Cross Join returns the Cartesian product of two tables, meaning it combines every row from the

first table with every row from the second table. It does not require any condition.

Key Features:

Combines all rows from both tables

Number of rows in the result = (Rows in Table 1) × (Rows in Table 2)

Can produce large result sets if tables have many rows

What is Union and Union All?

Union combines the result sets of two or more SELECT statements, eliminating duplicate records, while Union All includes all records without removing duplicates.

What is the difference between UNION and UNION ALL?

The primary difference is that UNION removes duplicate records from the combined result set, whereas UNION ALL includes all records from the SELECT statements, preserving duplicates.

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 related tables and defining

relationships between them.

Key Features:

Reduces data redundancy

Improves data consistency

Simplifies data maintenance

Increases data integrity

Types of Normalization:

1.

1NF (First Normal Form) – Eliminates duplicate columns and ensures each column contains

atomic values.

2NF (Second Normal Form) – Ensures no partial dependency by making all non-key

attributes fully dependent on the primary key.

3NF (Third Normal Form) – Removes transitive dependencies where non-key columns

depend on other non-key columns.

4.

BCNF (Boyce-Codd Normal Form) – Ensures that every determinant is a candidate key.

What is Denormalization?

Denormalization is the process of combining tables or adding redundant data into a database

to improve read performance at the cost of data redundancy.

It is the opposite of Normalization, used when fast data retrieval is more important than

maintaining data integrity.

Key Features:

Improves data retrieval speed

Increases data redundancy

Reduces the number of joins required

Used in data warehouses and reporting systems

What is the difference between CHAR and VARCHAR?

The difference between CHAR and VARCHAR is that CHAR is a fixed-length data type, which means it always reserves a specific amount of storage space regardless of the actual string length, while VARCHAR is a variable-length data type that only uses storage for the actual string length plus a small amount of overhead.

What is the difference between SQL and MySQL?

SQL is a standard language for managing and manipulating databases, while MySQL is an open-source relational database management system that uses SQL to query data.

What is Auto Increment in SQL?

Auto Increment is a property in SQL that automatically generates a unique sequential number

whenever a new row is inserted into a table. It is typically used to create unique identifiers like

primary keys.

Key Features:

Automatically generates unique numbers

Commonly used with Primary Key columns

Starts from a defined value (default is 1)

Automatically increments by 1 for each new row