Top 50 SQL Interview Questions for Business Analyst

1. What is SQL?

Answer: SQL or Structured Query Language, is a domain specific programming language used to manage relational databases. It enables us to carry out a number of tasks, including creating database structures, updating records, removing data, and querying data. SQL is a crucial tool for effectively managing massive volumes of structured data.

2. What are the different types of SQL statements?

Answer: SQL statements are commands used to communicate with a database. They are grouped into different categories based on what they do. The main types are:

1. DDL (Data Definition Language)

DDL (Data Definition Language) consists of SQL commands used to define, change, and remove the structure of database objects like tables, indexes, and schemas. It deals with the database schema—the blueprint of the database—and is used to create and modify the structure of database objects.

Common DDL Commands

Command	Description	Example Syntax
CREATE	Creates a new database or its objects (tables, indexes, views, stored procedures, triggers, etc.)	CREATE TABLE Students (ID INT, Name VARCHAR(50));
DROP	Deletes objects from the database permanently	DROP TABLE Students;
ALTER	Changes the structure of a database object (e.g., adding or removing a column)	ALTER TABLE Students ADD Age INT;
TRUNCATE	Removes all rows from a table and frees the storage space	TRUNCATE TABLE Students;
COMMENT	Adds comments to the database data dictionary	COMMENT 'Stores student details' ON TABLE Students;
RENAME	Renames an existing object in the database	RENAME TABLE Students TO Alumni;

2. DML – Data Manipulation Language

DML (Data Manipulation Language) consists of SQL commands that deal with managing the data stored inside tables. These commands allow you to add, update, delete, and fetch records.

Common DML Commands

Command	Description	Example Syntax
SELECT	Retrieves data from one or more tables	SELECT Name, Marks FROM Students WHERE Marks > 80;
INSERT	Adds new data (rows) into a table	INSERT INTO Students (RollNo, Name, Marks) VALUES (2, 'Asha', 92);
UPDATE	Modifies existing records in a table	UPDATE Students SET Marks = 95 WHERE RollNo = 2;
DELETE	Removes rows from a table	DELETE FROM Students WHERE Marks < 40;

3. DQL – Data Query Language
   

DQL (Data Query Language) is used only for querying data from the database. It helps in getting filtered and meaningful results without changing the data.

Common DQL Command

Command	Description	Example Syntax
SELECT	Fetches data from tables according to the conditions given	SELECT Course, COUNT(*) FROM St

4. DCL – Data Control Language
   

DCL (Data Control Language) is used to control access and permissions for users in the database. It defines who can view or change the data.

Common DCL Commands

Command	Description	Example Syntax
GRANT	Assigns new privileges to a user account, allowing access to specific database objects, actions, or functions.	GRANT SELECT, UPDATE ON Students TO teacher1;
REVOKE	Takes back the permissions previously given	REVOKE UPDATE ON Students FROM teacher1;

5. TCL – Transaction Control Language

TCL (Transaction Control Language) manages transactions — groups of SQL commands that are treated as a single logical unit. Transactions help ensure that the database stays accurate, even if something goes wrong.

Common TCL Commands

Command	Description	Example Syntax
COMMIT	Saves all the changes made in the current transaction permanently	COMMIT;
ROLLBACK	Cancels the changes made in the current transaction and restores the previous state	ROLLBACK;
SAVEPOINT	Sets a marker inside a transaction so you can roll back to that point if needed	SAVEPOINT before_update;
SET TRANSACTION	Sets properties for a transaction like read or write access	SET TRANSACTION READ ONLY;

3. What is a primary key?

Answer: A primary key in SQL is a constraint that uniquely identifies each record in a database table.

The key characteristics of a primary key:

• Uniqueness: Every value in the primary key column (or set of columns) must be unique. No two rows can have the same primary key.

• Non-Null: A primary key column cannot contain NULL values. Every row must have a valid identifier.

• Single Primary Key per Table: A table can have only one primary key. However, this primary key can consist of one or more columns, which is then called a composite primary key.

- It also helps maintain data integrity.

- It is used to create relationships between tables (via foreign keys)

- Enables faster lookups

4. What is a foreign key?

Answer: A foreign key is a column (or set of columns) in one table that refers to a unique key (usually the primary key) in another table. It is used to create and enforce a link between the data in the two tables, ensuring that the value in the foreign key column matches a valid value in the referenced table. This helps maintain data integrity and defines relationships between tables.

5. What is a unique key?

Answer: A unique key is a column (or a combination of columns) in a table that ensures all values in that column are different (unique) for each row. It is used to prevent duplicate values in specific fields.

Key Features of a Unique Key:

• Each value must be unique.

• It can contain NULL values (unlike a primary key).

• A table can have multiple unique keys.

• Ensures data integrity by avoiding duplicates.

Example:

CREATE TABLE Employees (

    employee_id INT PRIMARY KEY,

    email VARCHAR(255) UNIQUE

);

Here:

• employee_id is the primary key (unique + NOT NULL)

• email is a unique key — no two employees can have the same email, but it can be NULL for some rows

6. What is difference between DROP, DELETE and TRUNCATE?

Answer: All three commands remove data, but they work in different ways:

Command	What it does	Affects Structure?	Can use WHERE?	Speed	Rollback?
DELETE	Removes selected rows from a table.	No – table and structure stay.	Yes, it workswith WHERE.	Slower (deletes row by row, logs each deletion).	Yes, if inside a transaction.
TRUNCATE	Removes all rows from a table quickly.	No – table structure stays.	No, deletes everything.	Faster (deallocates data pages in one step).	Yes, if inside a transaction.
DROP	Completely deletes the table (or database) along with all its data, structure, and dependencies.	Yes – table is removed from the database.	Not applicable.	Fastest – removes object entirely.	No, table is gone.

7. What is a view in SQL?

Answer: A view in SQL is a virtual table that is based on the result of a SELECT query. It doesn’t store data itself — it just shows data from one or more real tables.

In simple words,A view is like a saved custom query that you can use like a table.

Key features:

• It looks and behaves like a table, but doesn't store data

• Always shows up-to-date data from the underlying tables

• You can filter, join, and rename columns in views

• Can be used to simplify complex queries or hide sensitive data

Example: We can create a view to show only high-salary employees:

Now, using this view:

8. What are joins in SQL?

Answer: Joins in SQL are used to combine rows from two or more tables based on a related column between them. They allow you to retrieve data that is spread across multiple tables by creating a logical relationship between them. For example, if we have a Customers table and an Orders table, we can use a join to find all orders made by a specific customer.

9. What are the types of joins in SQL?

Answer: A JOIN is used to combine rows from two or more tables based on a related column (like a common ID).

The main types of joins are:

1. INNER JOIN

• Returns only the rows that have matching values in both tables.

• If there’s no match, the row is not included.

(Only students who are enrolled in a course will appear.)

2. LEFT JOIN (or LEFT OUTER JOIN)

• Returns all rows from the left table and the matching rows from the right table.

• If there’s no match, it shows NULL for right table columns.

(Shows all students, even if they are not enrolled in any course.)

3. RIGHT JOIN (or RIGHT OUTER JOIN)

• Returns all rows from the right table and the matching rows from the left table.

• If there’s no match, it shows NULL for left table columns.

(Shows all courses, even if no student has enrolled in them.)

4. FULL JOIN (or FULL OUTER JOIN)

• Returns all rows when there is a match in either table.

• If there’s no match, it shows NULL for missing data on one side.

(Shows all students and all courses, matching them where possible.)

5. CROSS JOIN

• Combines every row of the first table with every row of the second table.

• Creates a cartesian product.

(If there are 10 students and 5 courses, you’ll get 50 rows.)

6. SELF JOIN

• A table joined with itself.

• Useful when you want to compare rows in the same table.

(Here, the Students table is joined with itself to find students with the same marks.)

10. What is normalization?

Answer: Normalization is the process of organizing data in a database so that:

• There is no duplicate (repeated) data.

• Data is stored in a logical and efficient way.

• It becomes easy to update, insert, or delete without problems.

In short, normalization makes your database clean and saves space.

Example (Without Normalization)

A single table:

StudentID	Name	Course1	Course2
1	Raj	Math	Science
2	Neha	Math	Science

Here, "Math" and "Science" are repeated many times.

After Normalization

We create two tables:

Students Table

StudentID	Name
1	Raj
2	Neha

Courses Table

StudentID	Course
1	Math
1	Science
2	Math
2	Science

Now each piece of data is stored only once.

11. What is the difference between WHERE and HAVING?

Basis	WHERE	HAVING
Purpose	Filters rows before any grouping or aggregation is performed.	Filters groups or aggregated data after grouping is done.
Used With	Non-aggregated columns.	Aggregate functions like SUM, COUNT, etc.
Execution Order	Applied first, before grouping.	Applied after the GROUP BY clause.
Example	SELECT * FROM Employees WHERE Department = 'Sales';	SELECT Department, COUNT(*) FROM Employees GROUP BY Department HAVING COUNT(*) > 10;

12. What are constraints in SQL?

Answer: Constraints are rules that we set on columns in a table to make sure the data is correct and reliable.They help control what kind of data can be stored in the database.

Think of them as safety checks for your data. Examples include:

• NOT NULL: Ensures a column cannot have NULL values.

• UNIQUE: Ensures all values in a column are unique.

• CHECK: Ensures values meet a specific condition.

• PRIMARY KEY: Uniquely identifies each row in a table. Combines NOT NULL + UNIQUE.

• FOREIGN KEY: Links two tables together. Ensures the value in one table matches a value in another table.

• DEFAULT: Sets a default value if no value is given.

Constraints keep your data accurate, consistent, and reliable by controlling what can and cannot go into the table.

13. What is a subquery?

Answer: A subquery is a query inside another query.It is used when we need to use the result of one query in another query. Think of it like asking a small question first to get the answer, and then using that answer in the main (outer) query.

Example

Find students who scored more than the average marks using subquery:

Here:

• The inner query (SELECT AVG(Marks) FROM Students) calculates the average marks.

• The outer query uses that result to find students who scored more than the average.

14. What is the difference between CHAR and VARCHAR?

Answer: CHAR and VARCHAR are used to store character strings.

Feature	CHAR	VARCHAR
Length	CHAR is Fixed-length string data type.	VARCHAR is Variable-length string data type.
Storage	This allocates the defined length, even if the actual data is shorter.	This allocates only the length of the data stored.
Performance	CHAR is faster for fixed-length data.	VARCHAR is more efficient for varying-length data.
Padding	It pads with spaces to reach the defined length.	It does not pad with spaces.

15. How do you change the data type of a column?

Answer: Use the ALTER TABLE statement to modify a column's data type:

Example:

Suppose we have a table Students and the column Marks is INT.We want to change it to DECIMAL(5,2):

16. What is the difference between INNER JOIN and OUTER JOIN?

Answer:

Feature	INNER JOIN	OUTER JOIN
Definition	Combines rows with matching values in both tables.	Combines matching rows and also includes nonmatching rows from one or both tables.
Output	It returns only matching rows.	It returns all matching rows and non-matching rows with NULL for missing values.
Types	It does not have any types.	OUTER JOIN includes LEFT JOIN, RIGHT JOIN, and FULL JOIN.
Example	SELECT * FROM A INNER JOIN B ON A.id = B.id;	SELECT * FROM A LEFT JOIN B ON A.id = B.id;

17. What is the difference between UNION and UNION ALL?

Answer:

Feature	UNION	UNION ALL
Definition	Combines the results of two or more queries and removes duplicates.	Combines the results of two or more queries and includes duplicates.
Performance	It is slower due to duplicate elimination.	It is faster as it does not check for duplicates.
Use Case	UNION is used when duplicate rows need to be avoided.	UNION ALL is used when duplicates are acceptable or desirable.
Example	SELECT City FROM Customers UNION SELECT City FROM Suppliers;	SELECT City FROM Customers UNION ALL SELECT City FROM Suppliers;

18. What are the types of relationships in databases?

Answer:In a database, a relationship shows how data in one table is connected to data in another table.

There are three main types:

1. One-to-One (1:1)

One row in Table A is linked to one row in Table B.

Example:A person has one passport, and each passport belongs to one person.

Tables:

PersonID	Name
1	Riya

PassportID	PersonID
P123	1

2. One-to-Many (1:N)

One row in Table A is linked to many rows in Table B.

Example:One teacher can teach many students, but each student has only one teacher.

Tables:

TeacherID	Name
1	Mr. Amit

StudentID	Name	TeacherID
101	Riya	1
102	Rahul	1

3. Many-to-Many (M:N)

Many rows in Table A can be linked to many rows in Table B.

Example:A student can enroll in many courses, and each course can have many students.

To create this, we use a third table (called a junction table or linking table).

Tables:

StudentID	Name
1	Riya
2	Amit

CourseID	CourseName
C1	Math
C2	Science

Enrollment Table (Junction Table):

StudentID	CourseID
1	C1
1	C2
2	C1

19. What is a composite key?

Answer: A composite key in a database is a primary key that is formed by the combination of two or more columns (attributes) in a table. It's used when a single column isn't sufficient to uniquely identify each row in a table.

Example: Imagine a table named 'Orders' with columns OrderID and ProductID. If OrderID alone doesn't uniquely identify each record (because an order can contain multiple products), you can combine OrderID and ProductID to form a composite key that uniquely identifies each order item.

20. What is a schema in SQL?

Answer: In SQL, a schema is a collection of database objects, such as tables, views, indexes, and stored procedures, that are logically grouped together. It provides a blueprint or structure for how data is organized and stored within a database. Essentially, a schema acts as a container or namespace for related database objects, allowing for better organization, management, and security of the database. 

Example:

Imagine a database for a library. You might have a schema for books, another for members, and a third for loans. Each schema would contain the tables, views, and other objects relevant to that specific area. For instance, the "books" schema might contain tables for book details, authors, and publishers.

The image below shows an example of a database schema.

21. What is the purpose of COALESCE in SQL?

Answer: The SQL COALESCE function is used to handle NULL values in a query. It evaluates a list of expressions or column values in order from left to right and returns the first non-NULL value it encounters. If all the expressions in the list evaluate to NULL, the function returns NULL. 

For example, consider an "employees" table with potential NULL values for name and salary. You can use COALESCE to replace these NULL values with defaults, like "Unknown" for names and 0 for salaries, using a query similar to this: 

22. Write a SQL query to fetch all records from a given table say, employees table.

Answer: To retrieve all records from a table, use the SELECT * statement:

23. How do you retrieve unique values from a column?

To retrieve unique values from a column in SQL, you use the SELECT DISTINCT statement.

Explanation:

• SELECT DISTINCT: This combination tells the database to return only the unique (different) values from the specified column.

• column_name: This is the name of the column from which you want to retrieve the unique values.

• FROM table_name: This specifies the table that contains the column you're interested in.

For example, if you have a table named Employees with a column called Department, and you want to see a list of all the unique departments in your company, you would use this query:

24. What are window functions in SQL?

Answer: In SQL, Window functions are a powerful tool used to perform calculations across a specific set of rows related to the current row, known as a 'window', without collapsing rows into a single output like aggregate functions do. They provide a result for each row in the result set, while considering a subset of related rows, enabling more advanced analysis and processing of data.

Key characteristics and syntax

• OVER clause: This is the core of any window function. It defines the window (the set of rows) on which the function will operate.
  

• PARTITION BY clause (optional): This divides the result set into partitions or groups based on one or more columns. The window function is then applied independently to each partition.
  

• ORDER BY clause: This specifies the order of rows within each partition (or the entire result set if no PARTITION BY is used), crucial for calculating rankings, running totals, and other sequential operations.

Types of window functions

1. Aggregate Window Functions: These functions (like SUM(), AVG(), COUNT(), MAX(), MIN()) calculate aggregates over a defined window of rows while retaining individual rows in the result set.

   • Example: Calculating the average salary for each department.

   
   

2. Ranking Window Functions: These functions assign a rank or sequential number to each row within a window based on a specified order.

   
   

   • Examples: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE().

   • ROW_NUMBER() assigns a unique sequential integer to each row.

   • RANK() assigns the same rank to rows with identical values but skips subsequent ranks.

   • DENSE_RANK() assigns the same rank to rows with identical values without skipping subsequent ranks.

   • NTILE() divides rows into a specified number of groups or tiles.

   
   

3. Value Window Functions: These functions retrieve specific values from other rows within a window, useful for comparing current values to previous or subsequent ones.

   • Examples: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()

25. What is the difference between RANK() and DENSE_RANK()?

Answer:

Feature	RANK()	DENSE_RANK()
Handling of Duplicates	Skips ranks if there are duplicates	Does not skip ranks for duplicates.
Result	The rank numbers may have gaps.	The rank numbers are continuous.
Example	If two employees have the same salary, they will share the same rank, but the next rank will skip.	If two employees have the same salary, they will share the same rank, and the next rank will follow directly.
Syntax Example	RANK() OVER (ORDER BY salary DESC)	DENSE_RANK() OVER (ORDER BY salary DESC)

26. How do you filter records using a specific condition?

Answer: Filtering records in SQL is done using the WHERE clause. It allows you to specify conditions that rows must meet to be included in the result set. 

You can use various operators within the WHERE clause:

• Comparison Operators: =, <>, >, <, >=, <= (e.g., WHERE age > 30)

• Logical Operators: AND, OR, NOT (e.g., WHERE country = 'USA' AND status = 'Active')

• Pattern Matching: LIKE with wildcards (%, _) (e.g., WHERE name LIKE 'A%')

• List and Range: IN, BETWEEN (e.g., WHERE id IN (1, 2, 3) or WHERE price BETWEEN 10 AND 20)

• Null Values: IS NULL, IS NOT NULL (e.g., WHERE email IS NULL)

The WHERE clause is essential for retrieving only the specific data you need from your tables. 

27. What is a domain in SQL?

Answer: In SQL, a domain refers to a named, user-defined set of valid values that can be stored in a column. Think of it as creating a custom data type with specific rules and constraints.

Here's why domains are useful:

• Data Integrity and Consistency: By defining a domain, you ensure that all columns based on that domain have the same data type, size, and most importantly, the same constraints. For instance, if you define a "postal_code" domain that ensures values are 5 or 9 digits long, every column referencing that domain will inherit that validation.

• Reusability: Instead of writing the same constraints repeatedly for multiple columns, you define the domain once and reuse it across tables. This saves time and reduces errors.

• Centralized Management: If you need to change a constraint or the data type associated with a domain, you only need to modify the domain definition, and the change will apply to all columns using that domain. 

Example of a domain definition

This creates a domain named "Age" which is an integer type, has a default value of 0, and includes a check constraint ensuring that the age value falls between 0 and 120 (inclusive). You can then define columns in your tables based on this domain, ensuring all age-related data adheres to these rules.

28. What is the difference between a table and a view?

Answer:

Feature	Table	View
Data Storage	Physically stores data in the database.	Does not store data; it is a virtual table.
Definition	A table is a collection of data organized into rows and columns.	A view is a virtual table created by a query that can pull data from one or more tables.
Modifiable	A table can be modified with DML operations (INSERT, UPDATE, DELETE)	A view may or may not be modifiable, depending on the underlying query.
Purpose	Used to store and organize data persistently.	Used to simplify complex queries or present data in a specific format.

29. How do you retrieve records in ascending order?

Answer: The ORDER BY clause combined with the ASC keyword is used to get records in ascending order. If no keyword is supplied, ORDER BY sorts by default in ascending order; nevertheless, ASC expressly indicates the sorting direction.

Syntax:

30. What is the purpose of the DEFAULT constraint?

Answer: The DEFAULT constraint provides a default value for a column when no value is specified during insertion. When no value is supplied during an insert operation, a column's default value is assigned using the DEFAULT constraint. In the event that the user does not specifically insert a value, it guarantees that the column has a predefined value.

As an illustration, let's say we have a students table with a column status that, in the absence of a value, should default to "active"

31. What is the difference between SQL and MySQL?

Answer: SQL is a standard language for managing relational databases, while MySQL is a specific relational database management system (RDBMS) that uses SQL.

Feature	SQL	MySQL
Definition	SQL (Structured Query Language) is a standardized language used for managing and manipulating relational databases.	MySQL is an open-source relational database management system (RDBMS) that uses SQL as its query language.
Purpose	SQL is a query language used for interacting with databases (creating, retrieving, updating, and deleting data).	MySQL is a database management system that implements SQL for managing and manipulating data stored in a database.
Scope	SQL is a language used in all relational database systems.	MySQL is one specific RDBMS that uses SQL.

32. What are NULL values in SQL?

Answer: In SQL, a NULL value is a special marker that represents missing, unknown, or inapplicable data in a database column. It's crucial to understand that NULL is not the same as a zero value (for numbers) or an empty string (for text). A column having a NULL value indicates that data is absent from that field for a specific record. 

For example, consider a table with employee details, and a column for "middle name." If an employee doesn't have a middle name, the entry in that field would be NULL, indicating that the information is either unavailable or not applicable.

33. What are candidate key & alternate key?

Answer:

Candidate Key: A column or a minimal set of columns that can uniquely identify each record in a table. It must be unique and minimal (no subset of the key can uniquely identify records). A table can have multiple candidate keys.

Alternate Key: Any candidate key that is not chosen as the primary key for the table. It still uniquely identifies records but is not the designated primary identifier.

In short: All primary keys are candidate keys, but not all candidate keys are primary keys. The candidate keys that aren't selected as the primary key become alternate keys.

For example, in a student table, "Student ID", "Roll No" and "Email Address" might be candidate keys because each can uniquely identify a student. The database designer would then choose one of these (e.g., Student ID) as the primary key, and the others (Roll No and Email Address) would become an alternate key.

34. What is CASE in SQL?

Answer: In SQL, the CASE statement is a powerful conditional expression that allows you to implement "if-then-else" logic directly within your queries. It evaluates a set of conditions and returns a value based on the first condition that is met.

The CASE statement starts with the CASE keyword and ends with the END keyword. Inside, you define one or more WHEN clauses, each with a condition and a corresponding THEN clause specifying the result if that condition is true. You can also include an optional ELSE clause to provide a default result if none of the WHEN conditions are met. If no ELSE clause is specified and no conditions are met, the CASE statement returns NULL.

Here's the basic syntax:

35. How do you get the total number of records in a table?

Answer: The COUNT() function is used to determine the total number of records in a table. The COUNT() method counts the number of rows that satisfy a given criteria, or counts all of the table's rows in the absence of a condition.

Syntax:

36. What is SQL aggregate functions?

Answer: An aggregate function in SQL performs a calculation on a set of values (a group of rows) and returns a single value as the result. These functions are commonly used with the GROUP BY clause to summarize data based on specific criteria.

Most common SQL aggregate functions:

Aggregate Function	Description	Syntax
COUNT()	Returns the number of rows that match a specified criterion.	COUNT(*) COUNT(column_name) COUNT(DISTINCT column_name)
SUM()	Calculates the total sum of a numeric column.	SUM(column_name) SUM(DISTINCT column_name)
AVG()	Computes the average value of a numeric column.	AVG(column_name) AVG(DISTINCT column_name)
MIN()	Retrieves the smallest value from a column.	MIN(column_name)
MAX()	Returns the largest value from a column.	MAX(column_name)

37. How do you update a specific record in a table?

Answer: To update a specific record (or multiple specific records) in a table, you use the SQL UPDATE statement along with a WHERE clause. The WHERE clause is crucial because it specifies the condition that identifies the row or rows you want to modify. If you omit the WHERE clause, all records in the table will be updated, which is usually not what you intend.

Here's the basic syntax:

Example

Let's say you have an Employees table with columns like EmployeeID, FirstName, LastName, and Salary. If you want to update the Salary of the employee with EmployeeID 101 to 60000, your query would look like this:

To update multiple columns for a specific employee, for example, changing both Salary and Department for EmployeeID 103, you would use:

38. What is a DBMS?

Answer: A Database Management System (DBMS) is software that enables users to create, manage, organize, store, retrieve, and manipulate data in databases. It serves as an interface between the database and the end-user, ensuring that data is well-organized and easily accessible.

For instance, Oracle DB, PostgreSQL, and MySQL are well-known DBMS programs.

39. What is an RDBMS?

Answer: An RDBMS, or Relational Database Management System, is a type of Database Management System (DBMS) that stores and manages data using the relational model. This model organizes data into tables composed of rows and columns. Rows represent records, and columns represent attributes or fields.

Examples of popular RDBMSs include MySQL, Oracle Database, Microsoft SQL Server, PostgreSQL, and MariaDB. RDBMSs are widely used in various applications for managing structured data effectively. 

40. What is the difference between DBMS and RDBMS?

Answer:

Feature	DBMS	RDBMS
Scope	Includes all database management systems.	A specific type of DBMS that follows the relational model.
Data Organization	Data can be stored as files, key-value pairs, or hierarchical structures.	Data is stored in tables with rows and columns.
Relationships	May or may not support relationships.	Supports relationships using Primary Keys & Foreign Keys.
Normalization	Does not necessarily support normalization.	Follows normalization to reduce redundancy.
Integrity Constraints	Fewer integrity rules.	Follows strict integrity constraints (ACID properties).
Examples	MongoDB, XML Databases, File Systems	MySQL, PostgreSQL, Oracle, SQL Server

41. How do you add a new column to an existing table?

Answer: To add a new column to an existing table in a SQL database, you use the ALTER TABLE statement followed by the ADD clause. 

Here's the basic syntax:

Examples

To add a column named "genre" with a VARCHAR data type (length 35) to the "film" table: 

42. What is the role of SQL in RDBMS?

Answer: SQL (Structured Query Language) is the standard language for maintaining and dealing with relational databases in an RDBMS. In addition to defining and maintaining database schemas, it enables users to query, change, and manage data contained in tables.

SQL's Role:

• Querying: Use SELECT to retrieve particular data from the database.

• Data manipulation: Use the INSERT, UPDATE, and DELETE commands to add, edit, and remove data.

• Data Definition: Use CREATE, ALTER, and DROP to define and change database structures.

• Data Control: Use GRANT and REVOKE to manage security and permissions.

43. What is cardinality in databases?

Answer: In the context of databases, cardinality refers to two related concepts:

1. Data uniqueness within a column: How many distinct values are in a column compared to the total rows.

   • High cardinality: Many unique values (e.g., email addresses).

   • Low cardinality: Few unique values (e.g., gender).

     
     

2. Relationships between tables: How many instances of one entity relate to another (e.g., One-to-One, One-to-Many, Many-to-Many).

Cardinality is crucial for designing efficient databases and optimizing query performance. It helps in selecting appropriate indexing strategies and influences how the database retrieves and stores information. 

44. What is data redundancy?

Answer: Data redundancy occurs when the same data is stored multiple times within a database or across different storage locations. 

For example, if you have a database that stores customer information, including their addresses, and the same customer's address is listed in both the "Customers" table and the "Orders" table, that's data redundancy.

45. What is a unique constraint?

Answer: A unique constraint is a rule in a database that ensures all values in a specific column, or a combination of columns, are distinct. This means no two rows in the table can have the same value (or combination of values) for the constrained column(s). If an attempt is made to insert or update a record with a duplicate value, the database will return an error.

Example

In this example, the Email column has a unique constraint, meaning every email address in the Customers table must be different. 

46. What is a NOT NULL constraint?

Answer: A NOT NULL constraint in SQL enforces that a column in a table cannot contain NULL values. This means that every row inserted or updated into the table must have a non-NULL value in that column. If you attempt to insert or update a row without providing a value for a column with a NOT NULL constraint, the database will return an error.

In the example above, the EmployeeID and FirstName columns cannot contain NULL values, ensuring that every employee record has a valid ID and first name. The LastName column, however, can contain NULL values as it doesn't have a NOT NULL constraint explicitly defined.

47. What are SQL data types?

Answer: In SQL, data types are used to define the kind of data that can be stored in a column of a database table or a variable. Each data type specifies a set of allowed values and the operations that can be performed on them. 

For example, if you want a column to store only integers (whole numbers), you would use an integer data type like INT. If you want to store text, you would use a character or string data type like VARCHAR.

Common SQL data type categories

• Numeric:

  • INT: Stores whole numbers.

  • DECIMAL(P, S): Stores exact fixed-point numbers with specified precision (P) and scale (S).

• String:

  • VARCHAR(n): Stores variable-length strings up to n characters.

  • CHAR(n): Stores fixed-length strings up to n characters.

  • TEXT: Stores large blocks of text.

  • ENUM: Allows a column to have one of a predefined list of values.

• Date and Time:

  • DATE: Stores date values in 'YYYY-MM-DD' format.

  • DATETIME: Stores combined date and time in 'YYYY-MM-DD HH:MM:SS' format.

  • TIMESTAMP: Stores date and time, often converting to UTC and back during storage/retrieval.

• Boolean:

  • MySQL uses TINYINT(1) to represent boolean values, where 0 is false and non-zero (typically 1) is true. BOOLEAN or BOOL are aliases for TINYINT(1). 

In this example, OrderDate is a DATE type, OrderTime is a TIME type, IsPaid is a BOOLEAN type , and CustomerNotes uses the TEXT type.

48. What are scalar functions in SQL?

Answer: In SQL, scalar functions are a type of function that takes one input values (parameter) and returns a single, individual value as output.

Commonly used scalar functions are:

Scalar function	Description	Syntax
UCASE()	Converts a string to uppercase	SELECT UCASE(column_name) FROM table_name;
LCASE()	Converts a string to lowercase	SELECT LCASE(column_name) FROM table_name;
MID()	Extracts a substring from a string	SELECT MID(column_name, start, length) FROM table_name;
LEN()	Returns the length of a string	SELECT LEN(column_name) FROM table_name;
ROUND()	Rounds a number to a specified number of decimals	SELECT ROUND(column_name, decimals) FROM table_name;
NOW()	Returns the current date and time	SELECT NOW();
FORMAT()	Formats a value with the specified format	SELECT FORMAT(column_name, format) FROM table_name;

49. What is the GROUP BY clause?

Answer: In SQL, the GROUP BY clause is used to group rows that have the same values in one or more specified columns into a set of summary rows. Think of it as categorizing your data based on common attributes.

Example

To find the total revenue for each product category: 

This query would group all sales records by their category and then calculate the sum of revenue for each unique category, providing the total revenue per category.

50. What is the HAVING clause?

Answer: In SQL, the HAVING clause is used to filter groups of rows that have been created using the GROUP BY clause. It's similar to the WHERE clause, but with a key difference: WHERE filters individual rows before grouping or aggregation, while HAVING filters groups of rows after they've been grouped and aggregate functions have been applied.

Example

Let's say you have an Orders table with columns like product_id and quantity. You want to find products that have a total quantity ordered greater than 100.

This query first groups the orders by product_id and calculates the SUM(quantity) for each product. Then, the HAVING clause filters these grouped results, returning only the products where total_quantity is greater than 100.

---