SQL Interview Question and Answers

 

Question	Answer
What is SQL?	- SQL stands for Structured Query Language. <br> - It's a standard language for storing, manipulating, and retrieving data in databases.
What are the main types of SQL commands?	- DDL (Data Definition Language) <br> - DML (Data Manipulation Language) <br> - DQL (Data Query Language) <br> - DCL (Data Control Language) <br> - TCL (Transaction Control Language).
What does the SELECT statement do?	- It retrieves data from one or more tables.
Name the SQL clause used to filter the results of a query.	- The WHERE clause.
How can you prevent SQL injection in your queries?	- By using parameterized queries or prepared statements.
How do you retrieve all columns from a table?	- SELECT * FROM table_name.
Write a SQL query to find the total number of employees in the "Employees" table.	- SELECT COUNT(*) FROM Employees;
How do you retrieve unique values from a column?	- Using the DISTINCT keyword, e.g., SELECT DISTINCT column_name FROM table_name;
What is the default sort order of the ORDER BY clause in SQL?	- Ascending.
What is a JOIN in SQL?	- A JOIN clause is used to combine rows from two or more tables based on a related column.
What is the difference between INNER JOIN and LEFT JOIN?	- INNER JOIN returns rows when there is a match in both tables. <br> - LEFT JOIN returns all rows from the left table, and the matching rows from the right table. If no match, NULL values will be returned.
Write a SQL query to find the second highest salary from a table.	- SELECT MAX(salary) <br> FROM table_name <br> WHERE salary NOT IN (SELECT MAX(salary) FROM table_name);
What is a subquery, and what are its types?	- A subquery is a query embedded within another query. <br> - Types include correlated and non-correlated.
Explain the difference between a primary key and a unique key.	- Both primary key and unique key enforce uniqueness of the column on which they are defined. <br> - A primary key doesn't allow NULLs while a unique key does.
What is normalization?	- Normalization is a process of organizing data in a database to avoid redundancy and improve data integrity.
What are ACID properties in a database?	- ACID stands for Atomicity, Consistency, Isolation, and Durability. <br> - They ensure reliable processing in a database.
What is a VIEW in SQL?	- A VIEW is a virtual table based on the result-set of an SQL statement.
How would you create an index on a table column?	- CREATE INDEX index_name ON table_name(column_name);
What is a stored procedure?	- A stored procedure is a precompiled collection of one or more SQL statements that can be executed as a single call to the database.
How do you handle transactions in SQL?	- Transactions are handled using TCL commands like BEGIN, COMMIT, ROLLBACK.

Question	Answer
What is the difference between CHAR and VARCHAR data types?	- CHAR is a fixed-length character type. <br> - VARCHAR is a variable-length character type.
How can you fetch alternate records from a table?	- Use the MOD function. <br> - For odd rows: SELECT * FROM table_name WHERE MOD(row_id, 2) <> 0;
What is a self-join?	- A self-join is a join in which a table is joined with itself.
What are the different types of normalization?	- 1NF, 2NF, 3NF, BCNF, 4NF, 5NF.
Write a query to get the current date and time.	- SELECT CURRENT_TIMESTAMP;
How do you rename a column in a SQL table?	- ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
What is the difference between DELETE and TRUNCATE commands?	- DELETE removes rows one by one and logs individual row deletions. <br> - TRUNCATE removes all rows and doesn't log individual row deletions.
What is the difference between a clustered and a non-clustered index?	- A clustered index determines the physical order of data in a table. <br> - A non-clustered index doesn't rearrange the physical order but creates a separate object within the table.
What is a cursor?	- A cursor is a database object used to retrieve data from a result set one row at a time.
What is SQL injection?	- SQL injection is a code injection technique that might destroy your database. <br> - It's where attackers can insert malicious SQL code into SQL statements via web page input.
Explain the GROUP BY clause.	- GROUP BY groups rows with the same values in specified columns into summary rows, like "find the number of customers in each country".
What is the HAVING clause?	- HAVING filters the results of a GROUP BY clause. <br> - It's like a WHERE clause but for groups.
What are SQL constraints?	- Constraints are rules enforced on data columns on a table. <br> - They limit the type of data that can enter a table. <br> - Examples: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK.
What are SQL triggers?	- A trigger is a stored procedure that automatically runs when an event occurs in the database server.
What is the use of the COALESCE function?	- COALESCE returns the first non-NULL value in a list.
What is the difference between HAVING and WHERE clause?	- WHERE filters individual rows before they're grouped. <br> - HAVING filters groups based on aggregate results.
Explain the concept of a relational database.	- A relational database is a collection of data organized into structured tables that relate based on common columns.
How do you retrieve the first 5 characters of a string column?	- SELECT SUBSTRING(column_name, 1, 5) FROM table_name;
What is an aggregate function? Give examples.	- Aggregate functions perform a calculation on a set of values and return a single value. <br> - Examples: AVG, COUNT, SUM, MIN, MAX.
How can you avoid duplicate records in a query?	- Using the DISTINCT keyword.

Question	Answer
What is the difference between SQL and NoSQL databases?	- SQL databases are relational and have a fixed schema. <br> - Use structured query language. <br> - NoSQL databases are non-relational and have dynamic schema. <br> - Don't use structured query language.
Explain the concept of data integrity.	- Ensures data remains accurate, consistent, and unaltered. <br> - Applies during storage or transfer.
How would you find duplicates in a table?	- SELECT column_name, COUNT() <br> - FROM table_name <br> - GROUP BY column_name <br> - HAVING COUNT() > 1;
What is an ORM?	- Stands for Object-Relational Mapping. <br> - Converts data between incompatible systems using OOP.
Explain UNION and UNION ALL.	- UNION combines query results and removes duplicates. <br> - UNION ALL combines query results and includes duplicates.
How can you get the last record from a table without using ORDER BY?	- Depends on the database system. <br> - No inherent "last record" without defining "last", e.g., highest ID.
What is a foreign key?	- A column or set of columns referring to the primary key in another table. <br> - Establishes a link between data in two tables.
How do you add a column to an existing table?	- ALTER TABLE table_name ADD column_name datatype;
How do you delete a table in SQL?	- DROP TABLE table_name;
What is the difference between NOT NULL and DEFAULT constraints?	- NOT NULL ensures no NULL values in the column. <br> - DEFAULT sets a default value if none is specified.
What is a composite key?	- A type of primary key using two or more columns. <br> - Uniquely identifies rows in a table.
How do you update data in a SQL table?	- UPDATE table_name SET column1 = value1, column2 = value2 WHERE some_column = some_value;
What is data redundancy?	- Occurs when the same data is held in two separate places.
Explain the difference between a logical and a physical database model.	- Logical model describes without detailed physical considerations. <br> - Physical model delves into specifics of database construction.
What is an alias in SQL?	- A temporary name for a table or column in a specific SQL query.
What is a schema in SQL?	- Collection of database objects, e.g., tables, views, indexes. <br> - Associated with a user or database owner.
How would you select all records from one table that do not exist in another table?	- SELECT column_name FROM table1 <br> - WHERE column_name NOT IN (SELECT column_name FROM table2);
What is the difference between a CROSS JOIN and a FULL OUTER JOIN?	- CROSS JOIN returns the Cartesian product of tables. <br> - FULL OUTER JOIN returns matching and unmatched rows from both tables.
What is indexing and why is it used?	- Technique to speed up row retrieval from a table. <br> - Allows faster data finding without scanning entire table.
What is normalization? Why is it important?	- Organizes data to reduce redundancy and improve integrity. <br> - Ensures data consistency, reduces anomalies, optimizes queries.

Question	Answer
What are the three types of anomalies in a database?	- Insertion, Update, and Deletion anomalies.
What is DENORMALIZATION?	- Denormalization introduces redundancy to improve read performance.
How can you find the total number of rows in a table?	- SELECT COUNT(*) FROM table_name;
How would you find all tables in a database that contain a specific column name?	- Depends on RDBMS. <br> - In SQL Server: SELECT table_name FROM information_schema.columns WHERE column_name = 'your_column_name';
What does the LIKE operator do?	- The LIKE operator searches for a specified pattern in a column.
What's the difference between a primary key and a unique constraint?	- A primary key uniquely identifies records and disallows NULL values. <br> - A unique constraint ensures distinct values but may have NULL.
What is a deadlock?	- A situation where tasks are blocked, each waiting for the other to release a resource.
What is a correlated subquery?	- A subquery dependent on the outer query for values.
How do you create a copy of an existing table?	- CREATE TABLE new_table AS SELECT * FROM existing_table;
What are stored procedures? How are they different from functions?	- Pre-compiled SQL statements stored in a database. <br> - Functions always return a value; stored procedures may not.
What is the purpose of the BETWEEN operator?	- Filters results within a specified range.
What is a pivot table?	- Rotates rows into columns, providing a matrix representation of data.
How do you concatenate strings in SQL?	- Method varies by RDBMS: + operator, CONCAT() function, or
How do you handle null values in SQL?	- Use functions like IS NULL, IS NOT NULL, COALESCE, and NULLIF.
Explain the EXISTS operator in SQL.	- Tests for existence of any record in a subquery. Returns true if subquery returns one or more records.
What are views in SQL? Why are they used?	- Views are virtual tables based on SQL statements. <br> - They simplify schema access or encapsulate complex queries.
How do you retrieve unique values from a column?	- SELECT DISTINCT column_name FROM table_name;
What is a subquery, and what are its types?	- A query within another query. <br> - Types: scalar, column, row, table subqueries.
How can you increase the performance of a SQL query?	- Methods include using indexes, avoiding SELECT *, using joins instead of subqueries, reducing non-sargable queries, using stored procedures, and normalizing the database.
How can you fetch the second highest salary from a table?	- SELECT MAX(salary) FROM table_name WHERE salary NOT IN (SELECT MAX(salary) FROM table_name);

Question	Answer
What is a transaction in SQL?	- A transaction is a sequence of SQL operations executed as a single unit. <br> - Maintains data integrity with all or nothing execution.
How do you handle errors in SQL stored procedures?	- Use TRY...CATCH blocks (SQL Server example).
What are the different types of SQL statements?	- DDL (Data Definition Language). <br> - DML (Data Manipulation Language). <br> - DQL (Data Query Language). <br> - DCL (Data Control Language). <br> - TCL (Transaction Control Language).
What is the difference between INNER JOIN and OUTER JOIN?	- INNER JOIN returns matching rows in both tables. <br> - OUTER JOIN returns all rows from one table and matched rows from the second.
What is the difference between a database and a schema?	- Database: Collection of related data for efficient retrieval. <br> - Schema: Logical container for database objects (tables, views, indexes).
How do you prevent SQL Injection in your code?	- Use parameterized queries. <br> - Employ ORM tools. <br> - Escape user input. <br> - Use stored procedures. <br> - Keep software and libraries up-to-date.
What is a CASE statement in SQL?	- Allows conditional logic in SQL queries (if-then logic).
What is the use of the CASCADE attribute in SQL?	- Used with foreign key constraints. <br> - Automatically replicates actions (e.g., delete or update) on the primary table in related child table records.
What is the difference between CHAR_LENGTH and DATA_LENGTH?	- CHAR_LENGTH counts characters in a string. <br> - DATA_LENGTH counts bytes used to represent a string.
How do you swap two column values for a table?	- Use a temporary variable or SET columns equal to each other.
Explain the MERGE statement in SQL.	- Merges (upserts) records in one table based on differences found in another table.
What is a stored procedure?	- Precompiled set of one or more SQL statements executed as a single unit. <br> - Reusable.
What is the purpose of the GROUP_CONCAT function?	- Concatenates values from multiple rows into a single string. <br> - Useful with GROUP BY.
How do you remove duplicate rows in a table?	- Use ROW_NUMBER() or RANK() function and delete rows with numbers greater than 1.
What is the difference between a temporary table and a table variable?	- Temporary tables are stored in tempdb and have a physical representation. <br> - Table variables are stored in memory, typically faster.
What are CTEs (Common Table Expressions)?	- Temporary result sets referenced within SQL statements (SELECT, INSERT, UPDATE, DELETE).
What is the difference between DISTINCT and GROUP BY?	- DISTINCT gets unique values from all columns in the result. <br> - GROUP BY can be paired with aggregate functions and groups based on specified columns.
How can you retrieve the Nth highest salary from a table?	- Use subqueries with LIMIT and ORDER BY (RDBMS-dependent) or window functions like ROW_NUMBER().
What is the difference between a clustered index and a non-clustered index?	- Clustered index determines physical order of data, one per table. <br> - Non-clustered index doesn't change data order, maintains separate structure to point to data rows, multiple per table.
How do you find all employees who started in the past year?	- Example SQL query provided (syntax may vary based on RDBMS).