SQL - Interview Questions and Answers

SQL constraints are used to specify rules for the data in a table. Constraints are used to limit the type of data that can go into a table.

• NOT NULL
• CHECK
• DEFAULT
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY

A FOREIGN KEY is a key used to link two tables together. A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.

A database is an organized collection of data, generally stored and accessed electronically from a computer system. Where databases are more complex they are often developed using formal design and modeling techniques.

SQL stands for Structured Query Language. SQL is a domain-specific language used in programming and designed for managing data held in a relational database management system, or for stream processing in a relational data stream management system.

SQL is a standard language for retrieving and manipulating structured databases. On the contrary, MySQL is a relational database management system, like SQL Server, Oracle or IBM DB2, that is used to manage SQL databases.

There are four basic types of SQL joins: inner, left, right, and full. The easiest and most intuitive way to explain the difference between these four types is by using a Venn diagram, which shows all possible logical relations between data sets.

Data Integrity defines the accuracy as well as the consistency of the data stored in a database. It also defines integrity constraints to enforce business rules on the data when it is entered into an application or a database.

A table contains fields and records. You can visualize Fields (also known as columns) as vertical, and records (also known as rows) as horizontal. Fields define the table's structure, and rows contain data.

Example:.

Table: Student.

Field: Stud ID, Stud Name, Date of Birth.

Data: 301557, Aayush, 08/05/1998.

Database normalization is the process of structuring a relational database in accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity. "NF" refers to "normal form" . The three main types of normalization are 1NF,2NF,3NF. Normalization is also known as data normalization.

Denormalization is a database optimization technique in which we add redundant data to one or more tables. This can help us avoid costly joins in a relational database. It is also process of introducing redundancy into a table by incorporating data from the related tables.

A database index is a data structure that provides quick lookup of data in a column or columns of a table. An index creates an entry for each value and it will be faster to retrieve data.

The SQL programming language was first developed in the 1970s by IBM researchers Raymond Boyce and Donald Chamberlin.In 1986 SQL become the standard of American National Standards Institute (ANSI) and ISO(International Organization for Standardization) in 1987.

1. Data definition language (DDL) - It allows you to perform various operations on the database such as CREATE, ALTER and DELETE objects.
2. Data manipulation language (DML) - It allows you to access and manipulate data.
3. Data control language (DCL) - A data control language (DCL) is a syntax similar to a computer programming language used to control access to data stored in a database (Authorization).

• Hierarchical databases (DBMS)
• Relational databases (RDBMS)
• Network databases (IDMS)
• Object-oriented databases

Differences between these operator is that the BETWEEN operator is used to select a range of data between two values while The IN operator allows you to specify multiple values.

A view is the result set of a stored query on the data, which the database users can query just as they would in a persistent database collection object. This pre-established query command is kept in the database dictionary.

Database Relationship is defined as the connection between the tables in a database. 

1. One to One Relationship.
2. One to Many Relationship.
3. Many to One Relationship.
4. Self-Referencing Relationship.

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.

A query is a request for data or information from a database table or combination of tables. A database query can be either a select query or an action query.

SELECT fname, lname 		 /* select query */
FROM myDb.students
WHERE student_id = 1;

UPDATE myDB.students 		 /* action query */
SET fname = 'Captain', lname = 'America'
WHERE student_id = 1;

SQL commands are categorized into:

• DDL (Data Definition Language) ? CREATE, ALTER, DROP
• DML (Data Manipulation Language) ? INSERT, UPDATE, DELETE
• DCL (Data Control Language) ? GRANT, REVOKE
• TCL (Transaction Control Language) ? COMMIT, ROLLBACK, SAVEPOINT

DELETE removes specific records and can have a WHERE clause, whereas TRUNCATE removes all records and resets identity columns.

UNION removes duplicate records, while UNION ALL includes duplicates.

A subquery is a query nested inside another query. Example:

SELECT name FROM employees WHERE dept_id = (SELECT dept_id FROM departments WHERE dept_name = 'HR');

WHERE filters rows before grouping, whereas HAVING filters after grouping.

Numeric (INT, FLOAT), Character (VARCHAR, TEXT), Date (DATE, TIMESTAMP), and Boolean (BOOLEAN in some DBs).

The DEFAULT constraint provides a default value if no value is inserted

CREATE TABLE users (id INT, name VARCHAR(50), age INT DEFAULT 18);

It ensures that a column cannot have NULL values.

It ensures that values in a column meet a specific condition.

CREATE TABLE employees (id INT, salary INT CHECK (salary > 3000));

It ensures that all values in a column are unique.

Aggregate functions perform calculations on multiple rows:

• SUM(), AVG(), COUNT(), MAX(), MIN()

COUNT(*) counts all rows, including NULLs, while COUNT(column_name) ignores NULL values.

Functions that return a single value per row, such as UPPER(), LOWER(), ROUND(), LEN(), and SUBSTRING().

SELECT column_name, COUNT(*) 
FROM table_name 
GROUP BY column_name 
HAVING COUNT(*) > 1;

DELETE FROM employees
WHERE id NOT IN (SELECT MIN(id) FROM employees GROUP BY name);

SELECT MAX(salary) 
FROM employees 
WHERE salary < (SELECT MAX(salary) FROM employees);

It acts as an IF-ELSE condition.

SELECT name, 
CASE 
  WHEN age < 18 THEN 'Minor' 
  ELSE 'Adult' 
END AS category 
FROM users;

SELECT CURRENT_DATE; -- (or GETDATE() in SQL Server)

SELECT LENGTH('Hello World'); -- MySQL, PostgreSQL
SELECT LEN('Hello World'); -- SQL Server

SELECT REPLACE('Hello World', 'World', 'SQL'); -- Output: Hello SQL

A transaction is a sequence of SQL statements executed as a single unit of work.

Atomicity, Consistency, Isolation, and Durability ensure reliable database transactions.

COMMIT saves changes permanently, while ROLLBACK reverts changes.

It shows the execution path of a query, helping with optimization.

A stored procedure is a set of SQL statements that can be executed as a function.

CREATE PROCEDURE GetEmployees()
AS
BEGIN
  SELECT * FROM employees;
END;

A trigger is an automatic action that runs when an event (INSERT, UPDATE, DELETE) occurs

CREATE TRIGGER before_insert BEFORE INSERT ON employees 
FOR EACH ROW 
SET NEW.salary = IF(NEW.salary < 3000, 3000, NEW.salary);

A cursor is a pointer used to retrieve rows one by one in a loop.

A materialized view stores the result of a query physically, unlike a regular view.

• Clustered Index: Sorts and stores data in physical order.
• Non-Clustered Index: Stores only pointers to data without changing physical order.

WITH RECURSIVE employee_hierarchy AS (
    SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id 
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
) 
SELECT * FROM employee_hierarchy;

This helps in hierarchical data like employee-manager relationships.