Top 250 SQL Interview Questions and Answers

A clause is a group of words that contains a subject and a predicate. A phrase is a group of words that does not contain a subject and a predicate.

• A clause can function as a complete sentence or as part of a sentence.

• A phrase cannot function as a com...read more

Query to get the customer with the highest total order value for each year, month.

• Join the Order and Customer tables on the foreign key

• Group the results by year, month, and customer

• Calculate the total order value for each group

• Find the maximum total ...read more

Different types of joins are used in database queries to combine data from multiple tables.

• Inner join: Returns only the matching records from both tables.

• Left join: Returns all records from the left table and the matching records from the right table...read more

The number of columns that can be created in a table depends on the database management system being used.

• The limit varies depending on the DBMS

• For MySQL, the limit is 4096 columns per table

• For Oracle, the limit is 1000 columns per table

• The number of...read more

Inner join returns only the matching records from both tables, while outer join returns all records from both tables.

• Inner join combines rows from two tables based on a related column.

• Outer join combines rows from two tables and includes unmatched ro...read more

To insert or update data in a table, use SQL statements like INSERT INTO or UPDATE.

• For inserting data, use the INSERT INTO statement followed by the table name and column names.

• Specify the values to be inserted using the VALUES keyword.

• For updating d...read more

Normal types of SQL views include simple views, complex views, and materialized views.

• Simple views are based on a single table or multiple tables with a simple join.

• Complex views involve more complex queries, such as subqueries or aggregations.

• Materi...read more

UNION is a user-defined data type that allows storing different data types in the same memory location.

• UNION can be used to save memory space by sharing the same memory location for different data types.

• It is defined using the 'union' keyword in C pr...read more

SQL is preferred for structured data, transactions, complex queries, and ACID compliance.

• SQL is better for structured data and relationships between data.

• SQL is preferred for transactions and ensuring data integrity.

• SQL is more suitable for complex q...read more

SQL injection is a type of cyber attack where malicious SQL code is inserted into input fields to manipulate a database.

• SQL injection is a common web application security vulnerability.

• Attackers can inject malicious SQL code into input fields to acce...read more

Dense rank assigns consecutive integers to distinct values, rank assigns integers to distinct values but skips integers for ties, row number assigns integers to rows in the result set.

• Dense rank assigns consecutive integers to distinct values

• Rank ass...read more

CTE is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement in SQL.

• CTEs help improve readability and maintainability of complex SQL queries.

• They can be recursive, allowing for hierarchical data querying.

• C...read more

The order of execution of SQL clauses is: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.

• The SELECT clause is executed first to retrieve the desired columns from the table.

• The FROM clause is executed next to specify the table(s) from which the data ...read more

Rollback is the process of undoing or reversing a transaction or change made to a system.

• Rollback is commonly used in database management systems to undo changes made to a database.

• It can also be used in software development to undo changes made to c...read more

Aggregation functions are used to perform calculations on a set of values and return a single value as output.

• Aggregation functions are commonly used in databases and data analysis to summarize and analyze large amounts of data.

• Examples of aggregatio...read more

Use window function to find 2nd highest salary in SQL

• Use the ROW_NUMBER() function to assign a unique row number to each row based on the salary in descending order

• Filter the results where the row number is 2 to get the 2nd highest salary

SQL is a programming language used for managing relational databases. It uses dynamic memory management.

• SQL stands for Structured Query Language

• It is used for managing and manipulating data in relational databases

• SQL uses dynamic memory management, w...read more

AS is used to assign an alias to a column or table in SQL, while IS is used to compare values or check for null values.

• AS is used in SELECT statements to assign a temporary name to a column or table

• AS can also be used in JOIN statements to assign ali...read more

To add a column without using create, you can use the ALTER TABLE statement.

• Use the ALTER TABLE statement to add a new column to an existing table

• Specify the table name and the new column name, data type, and any other constraints

• Example: ALTER TABLE...read more

SQL functions are used to perform calculations on data and return the result.

• Aggregate functions like SUM, AVG, COUNT, MAX, MIN

• String functions like CONCAT, SUBSTRING, LENGTH

• Date functions like DATEADD, DATEDIFF, GETDATE

• Mathematical functions like AB...read more

To create a table in SQL, use the CREATE TABLE statement and define its elements.

• Use CREATE TABLE statement followed by table name

• Define columns with data types and constraints

• Add primary key constraint to uniquely identify each row

• Example: CREATE TA...read more

I have a good understanding of both SQL and Java.

• I have experience working with both SQL and Java in various projects.

• I have a strong understanding of SQL syntax and can write complex queries.

• I am proficient in Java and have worked with various frame...read more

SQL queries for CRUD operations

• CREATE: INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...)

• READ: SELECT column1, column2, ... FROM table_name WHERE condition

• UPDATE: UPDATE table_name SET column1 = value1, column2 = value2, ... ...read more

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity.

• Normalization involves breaking down a table into smaller tables and defining relationships between them.

• There are different levels of norma...read more

A subquery in SQL is a query nested within another query, used to return a subset of data from a larger dataset.

• A subquery is enclosed within parentheses and can be used in SELECT, INSERT, UPDATE, or DELETE statements.

• It can be used to filter results...read more

Implement lag() and lead() functions without using lag() or lead()

• To implement lag(), shift the values in the array by one position to the right

• To implement lead(), shift the values in the array by one position to the left

• For example, to implement la...read more

Drop command deletes the table structure along with data, while truncate command deletes only the data.

• Drop command removes the table structure and all its data, while truncate command only removes the data but keeps the table structure intact.

• Drop c...read more