TOP Question of SQL Ask in Interview ?
1.What is Sql ?
SQL, which stands for Structured Query Language, is a standardized programming language specifically designed for managing and manipulating databases.
It is used to perform a variety of tasks, such as querying data, updating records, deleting data, and managing database structures like tables and indexes.
2.What is databse?
A database is an organized collection of data that is stored and managed to allow easy access, retrieval, and manipulation of the information
Databases are used in various applications to store large amounts of data efficiently and ensure that it can be easily accessed and updated as needed.
3.What Is primary Key ?
A primary key is a special column or a combination of columns in a database table that uniquely identifies each record (or row) in that table.
The primary key ensures that each record can be uniquely distinguished from all others, which is crucial for maintaining data integrity and enabling efficient data retrieval.
the primary key column cannot contain null values.
4.What Is Foreign key ?
A foreign key is a column or a set of columns in a database table that creates a relationship between two tables. The foreign key in one table points to a primary key or a unique key in another table, establishing a link between the data in the two tables.
5.What is Uniqe Key ?
A unique key (or unique constraint) is a database constraint that ensures that all values in a column or a group of columns are unique across the table.This means that no two rows in the table can have the same value(s) in the unique key column(s), except for NULL values,
6.What is Normalization ? what are the types ?
Normalization is a process in database design that organizes data into related tables to minimize redundancy and dependency. The primary goal of normalization is to ensure that the database is structured in a way that reduces duplicate data, improves data integrity, and makes the database more efficient for queries and updates.
Types (Normal Forms) of Normalization:
Normalization is usually carried out through a series of steps, each corresponding to a specific “normal form” (NF)
- First Normal Form (1NF)
2. Second Normal Form (2NF)
3. Third Normal Form (3NF)
4. Boyce-Codd Normal Form (BCNF)
5. Fourth Normal Form (4NF)
6. Fifth Normal Form (5NF)
7.What is JOIN and What are the different types of Joins in sql ?
A JOIN in SQL is a powerful operation used to combine rows from two or more tables based on a related column between them
Joins allow you to retrieve data that is spread across multiple tables and present it as a single result set.
Types of Joins
- INNER JOIN
2. LEFT JOIN (or LEFT OUTER JOIN)
3. RIGHT JOIN (or RIGHT OUTER JOIN)
4. FULL JOIN (or FULL OUTER JOIN)
8.What is Difference between Drop,Truncate and delete in sql ?
In SQL, DROP, TRUNCATE, and DELETE are commands used to remove data from a database, but they operate differently and are used in different scenarios those are defin breifly below.
1. DELETE
The DELETE command is used to remove specific rows from a table based on a condition specified in the WHERE clause
If no condition is specified, all rows in the table will be deleted, but the table structure remains intact.
2. TRUNCATE
The TRUNCATE command is used to remove all rows from a table, quickly and efficiently, but it does not remove the table structure or its columns, indexes, constraints, and relationships.
3.DROP
The DROP command is used to completely remove a database object, such as a table, from the database
This includes deleting all the rows, the table structure, and any associated indexes, constraints, and relationships.
9.What is Difference between Schema and Database?
schema and database are related concepts, but they refer to different aspects of database design and management.
Database
A database is a collection of organized data that is stored and managed in a way that allows for easy access, retrieval, modification, and management.
A database physically stores the data, which can include tables, indexes, stored procedures, and other objects.
A database can contain multiple schemas, each containing its own set of tables and other objects.
Schema
A schema is a logical container within a database that groups together database objects like tables, views, indexes, stored procedures, and other elements
It defines the structure of the data and the relationships between different parts of the data.
A schema defines how data is organized within a database, including the structure of tables, the relationships between tables, and the constraints applied to the data.
10.What is deadlock ?
it is a situation where two or more transactions (or processes) are unable to proceed because each is waiting for the other to release resources that they need to continue. This results in a standstill where none of the transactions can proceed, leading to a situation that requires intervention to resolve.
11.What is Difference between Char,Varchar and nvarchar ?
CHAR,VARCHAR, and NVARCHAR are data types used to store character strings.
they are different in how to they store data and the type of data they can store.
1.CHAR
CHAR is used to store fixed-length character strings. When you define a CHAR column, you specify a fixed size (e.g. CHAR(10)).
the database will always allocate that much space for the data, regardless of the actual length of the string stored.
2.VARCHAR
VARCHAR is used to store variable-length character strings. Unlike CHAR, VARCHAR only uses as much space as needed to store the actual data, plus a small amount of overhead to store the length of the string.
The length of the stored string can vary up to the maximum length specified. For example, VARCHAR(10) can store strings of any length from 0 to 10 characters.
3.NVARCHAR
NVARCHAR is similar to VARCHAR but is used to store variable-length Unicode character strings.
Unicode allows for the storage of characters from multiple languages and character sets, which is important for applications that need to support internationalization.
12.What is Difference between Temp Table and Table variable ?
temporary tables and table variables are both used to store temporary data within a session or a batch. but they have different in scope, usage, and performance . Those are briefly define as below
1.Temporary Tables
Temporary tables are similar to regular tables, but they are stored in the ‘tempdb’ database and are meant to hold temporary data that is needed only for the duration of a session or a specific scope.
Example
CREATE TABLE #TempTable (
ID INT PRIMARY KEY,
Name VARCHAR(50)
);
INSERT INTO #TempTable (ID, Name)
VALUES (1, ‘Alice’), (2, ‘Bob’);
SELECT * FROM #TempTable;
— Temporary table is automatically dropped when the session ends
2.Table Variables
Table variables are variables that store a table-like structure and are used to hold temporary data within the scope of a batch, stored procedure, or function.
Example
DECLARE @TableVar TABLE (
ID INT PRIMARY KEY,
Name VARCHAR(50)
);
13.What is the use of GROUP BY Clause in SQL?
The ‘GROUP BY’ clause in SQL is used to group rows that have the same values in specified columns into aggregated data sets. It is typically used in combination with aggregate functions (like MIN,MAX,AVG etc) to perform calculations on each group of rows.
14.What is Stored procedure ?
A stored procedure in SQL is a precompiled collection of one or more SQL statements that are stored and executed on the database server
Stored procedures are used to encapsulate and execute a series of SQL queries and commands, allowing for reuse, modularity, and efficient database operations.
Example
CREATE PROCEDURE GetEmployeeDetails
@EmployeeID INT
AS
BEGIN
— Fetch employee details based on the provided EmployeeID
SELECT Name, Position, Department, HireDate
FROM Employees
WHERE EmployeeID = @EmployeeID;
END;
How to Execute storedprocedure ?
EXEC GetEmployeeDetails @EmployeeID = 101;
15.What is Aggrigate function ?
Aggregate functions in SQL are functions that perform a calculation on a set of values and return a single value. They are often used in conjunction with the GROUP BY clause to group data before performing the calculation Like SUM,MIN,MAX etc
16.What is subquery ?
A subquery is a query embedded within another SQL query.
The result of the subquery is used by the main query (also called the outer query) to further refine the data or provide input.
it is also known as an inner query or nested query.
17.What is Difference between CROSS JOIN and INNER JOIN in sql ?
CROSS JOIN
A CROSS JOIN returns the Cartesian product of two tables. This means it combines every row from the first table with every row from the second table.
If you have ‘table A’ with m rows and ‘table B’ with n rows, a CROSS JOIN will return m*n rows.
No condition or ‘ON’ clause is needed because every row in the first table is paired with every row in the second table.
CROSS JOINs are often used when you need to generate all possible combinations of rows from two tables.
INNER JOIN
An INNER JOIN returns only the rows where there is a match between columns in both tables based on a specified condition.
An INNER JOIN combines rows from both tables where the specified condition is met. Rows that do not meet the condition are excluded from the result.
INNER JOINs are commonly used to retrieve related data from two tables.
18.What is Use of Commit and Rollback in sql ?
COMMIT and ROLLBACK are commands used to manage transactions, which are sequences of operations performed as a single unit.
COMMIT
The COMMIT command is used to save all the changes made during the current transaction to the database permanently. Once a transaction is committed, its changes become visible to other users and cannot be undone.
ROLLBACK
The ROLLBACK command is used to undo all the changes made during the current transaction.
If there is an error or an issue detected, you can use ROLLBACK to revert all operations back to the state they were in before the transaction began.
19.What is Different between Union and UnionALL ?
UNION and UNION ALL are both used to combine the result sets of two or more select data.
Those both are define as below,
1.UNION
Combines the result sets of two or more select data into a single result and removes duplicate rows from the combined result.
Example
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;
2.UNIO ALL
Combines the result sets of two or more select data into a single result set and includes all rows, including duplicates.
Example
SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;
20.What is Difference between HAVING cluase and Where cluase ?
A HAVING clause and the WHERE clause in SQL are both used to filter data from query. but those diifernt uses
in briefly define as below
1.WHERE Clause
the WHERE clause is used to filter rows before any grouping operations are performed.
Filters rows from the database tables based on specific conditions before they are processed by aggregate functions like SUM, COUNT, AVG, etc.
2.HAVING Clause
The HAVING clause is used to filter groups after the GROUP BY operation has been applied.
Filters groups of rows that are created by the GROUP BY clause based on conditions that often involve aggregate functions (like SUM, COUNT, AVG, etc).
21.What is Transcation in sql ?
A transaction in SQL is a sequence of one or more SQL operations that are executed as a single unit of work.
22.What is ACID properties in Database Transcation?
The ACID properties are a set of four key properties that guarantee that database transactions are processed reliably and ensure the integrity of the data within a database.
The acronym ACID stands for Atomicity, Consistency, Isolation, and Durability.
These properties are essential for maintaining the reliability of database transactions, especially in systems where multiple transactions occur concurrently.
- Atomicity
Atomicity ensures that each transaction is treated as a single “unit of work,” which means that all the operations within the transaction must be completed successfully
If any part of the transaction fails, the entire transaction is rolled back, leaving the database in its original state.
2. Consistency
Consistency ensures that a transaction takes the database from one valid state to another, maintaining the integrity of the data according to all predefined rules (such as constraints, triggers, and cascades)
After the transaction is completed, the database must remain in a consistent state.
3. Isolation
Isolation ensures that transactions are executed independently of one another. The operations within a transaction are isolated from other concurrent transactions,meaning the intermediate state of a transaction is invisible to others until the transaction is completed
4. Durability
Durability ensures that once a transaction is committed, its effects are permanently recorded in the database, even in the event of a system failure.
This means that committed transactions survive crashes, power failures, and other system issues.