1. Index ?
Index is a lookup table associated with actual table or view that is used by the database to improve the data retrieval performance timing. In index, keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently. Index gets automatically created if primary key and unique constraint is defined on the table
2. Differences between clustered and non-clustered indexes.
There can be only one clustered index per table. However, you can create multiple non-clustered indexes on a single table.
Clustered indexes only sort tables. Therefore, they do not consume extra storage. Non-clustered indexes are stored in a separate place from the actual table claiming more storage space.
Clustered indexes are faster than non-clustered indexes since they don’t involve any extra lookup step
3. What do you understand by query optimization?
The phase that identifies a plan for evaluation query which has the least estimated cost is known as query optimization.
The advantages of query optimization are as follows:
· The output is provided faster
A larger number of queries can be executed in less time
· Reduces time and space complexity
4. Explain different types of Normalization.
There are many successive levels of normalization. These are called normal forms. Each consecutive normal form depends on the previous one.The first three normal forms are usually adequate.
· First Normal Form (1NF) – No repeating groups within rows
· Second Normal Form (2NF) – Every non-key (supporting) column value is dependent on the whole primary key.
· Third Normal Form (3NF) – Dependent solely on the primary key and no other non-key (supporting) column value.
5. What is the ACID property in a database?
ACID stands for Atomicity, Consistency, Isolation, Durability. It is used to ensure that the data transactions are processed reliably in a database system.
· Atomicity: Atomicity refers to the transactions that are completely done or failed where transaction refers to a single logical operation of a data. It means if one part of any transaction fails, the entire transaction fails and the database state is left unchanged.
· Consistency: Consistency ensures that the data must meet all the validation rules. In simple words, you can say that your transaction never leaves the database without completing its state.
· Isolation: The main goal of isolation is concurrency control.
· Durability: Durability means that if a transaction has been committed, it will occur whatever may come in between such as power loss, crash or any sort of error
6. What do you mean by “Trigger” in SQL?
A SQL trigger is a database object which fires when an event occurs in a database. We can execute a SQL query that will "do something" in a database when a change occurs on a database table such as a record is inserted or updated or deleted. For example, a trigger can be set on a record insert in a database table
7. List the ways to get the count of records in a table?
To count the number of records in a table in SQL, you can use the below commands:
· SELECT COUNT(*) FROM table1
· SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2
8. Write a SQL query to get the third-highest salary of an employee from employee_table?
TOP keyword
SELECT TOP 1 salary
FROM(
SELECT TOP 3 salary
FROM employee_table
ORDER BY salary DESC) AS emp
ORDER BY salary ASC;
Using Limit :
SELECT salary
FROM employee_table
ORDER BY salary DESC
LIMIT 2, 1
Sub Query
SELECT salary
FROM
(SELECT salary
FROM employee_table
ORDER BY salary DESC
LIMIT 3) AS Comp
ORDER BY salary
LIMIT 1;
Rank:
select * from(
select ename, sal, dense_rank()
over(order by sal desc)r from Employee)
where r=&n;
To find to the 2nd highest sal set n = 2
To find 3rd highest sal set n = 3 and so on.
SELECT EmpName , Salary FROM(
SELECT ROW_NUMBER() OVER(ORDER BY Salary DESC) AS SNo , EmpName, Salary
FROM Employee
)Sal
WHERE SNo = 3
9. How can you fetch alternate records from a table?
You can fetch alternate records i.e both odd and even row numbers. For example- To display even numbers, use the following command:
Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=0
Now, to display odd numbers:
Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=1
10. What is a View?
A view is a virtual table which consists of a subset of data contained in a table. Since views are not present, it takes less space to store. View can have data of one or more tables combined and it depends on the relationship
11.
1. An organization is having multiple departments
2. Organization is having multiple employees
3. An employee can play one or multiple roles in same or various departments
employee(eid, ename, salary)
department(did, dname)
empdept(eid, did, role)
Write down the query to select employee count of the department along with the department name. It should select those department as well which are having no employees.
SELECT department_name AS 'Department Name',
COUNT(*) AS 'No of Employees'
FROM departments
INNER JOIN employees
ON employees.department_id = departments.department_id
GROUP BY departments.department_id, department_name
ORDER BY department_name;
1212.
The nth highest salary in SQL SERVER using TOP keyword
SELECT TOP 1 salary FROM ( SELECT DISTINCT TOP N salary FROM #Employee ORDER BY salary DESC ) AS temp ORDER BY salary
using row num
SELECT * FROM ( SELECT e.*, ROW_NUMBER() OVER (ORDER BY salary DESC) rn FROM Employee e ) WHERE rn = N
Difference Between SCOPE_IDENTITY() and @@IDENTITY
@@IDENTITY - Returns the last identity values that were generated in any table in the current session. @@IDENTITY is not limited to a specific scope.
SCOPE_IDENTITY() - Return the last identity values that are generated in any table in the current session. SCOPE_IDENTITY returns values inserted only within the current scope.
50 SQL
1. What is SQL and what it is used for?
2. What is Primary Key?
3. What is Foreign Key?
4. What is an index and how to create one?
5. Name types of indexes
6. How to select all columns from a table?
7. How to select given columns from a table?
8. How to select a column with duplicated values?
9. What is JOIN used for?
10. Name types of JOINs
11. What is the difference between LEFT and RIGHT joins?
12. What is a self join?
13. How does CROSS join work?
14. How does UNION ALL work?
15. How to select data from 2 tables without a JOIN?
16. What are subqueries?
17. How to skip NULLs when using LEFT or RIGHT join?
18. When to use each type of join?
19. What is database transaction?
20. Explain transaction isolation levels
21. When to use each transaction isolation level
22. When can deadlocks occur in a SQL statement?
23. Explain how the query execution plan works
24. What is a database view?
25. What is a materialized view?
26. What is the difference between a stored procedure and a function?
27. What is the purpose of triggers?
28. What database normalization forms do you know?
29. In what cases would you consider database denormalization?
30. What is GROUP BY used for?
31. How to add condition to the GROUP BY ?
32. Write a SQL query to select top 5 products with highest sales
33. Write a SQL query to select top 5 products with the highest price
34. How to filter a single column by multiple values?
35. Name the scalar functions
36. Difference between a subquery and join
37. Difference between a correlated subquery from a regular subquery
38. How to ensure the uniqueness of the column?
39. How to add columns to an index?
40. What is SQL injection, and how can it be prevented?
41. Explain the ACID
42. What is a common table expression (CTE)?
43. How do you write a recursive CTE?
44. What is a database sequence?
45. What is ROW_NUMBER ?
46. How do you handle errors in SQL?
47. What is a composite key?
48. What are default constraints?
49. How does the CASE statement work in SQL ?
50. What are the diff between SQL and NoSQL databases
----------------------
1. What is SQL, and what are its different types?
2. Explain the difference between SQL and MySQL.
3. What are the different types of joins in SQL? Provide examples.
4. What is normalization, and how does it differ from denormalization?
5. Explain the different types of normal forms.
6. What is the difference between DELETE, TRUNCATE, and DROP commands?
7. What are primary keys and foreign keys?
8. How do INNER JOIN and OUTER JOIN differ?
9. What is a subquery? Explain with an example.
10. What are indexes in SQL, and why are they important?
11. What are aggregate functions in SQL? Provide some examples.
12. What is the difference between GROUP BY and HAVING clauses?
13. What is the purpose of the WHERE clause?
14. What are the different types of constraints in SQL?
15. What is a stored procedure, and how does it differ from a function?
16. What is a trigger in SQL? Provide an example.
17. Explain ACID properties in the context of SQL.
18. How do VARCHAR and CHAR differ?
19. What are the different types of SQL data types?
20. What is a self-join? Provide an example.
21. What is a view in SQL, and how does it differ from a table?
22. How does the EXISTS clause work?
23. What is a Common Table Expression (CTE)?
24. What is the purpose of INDEX in SQL?
25. What are transactions in SQL, and how can you manage them?
26. Explain the RANK() and DENSE_RANK() functions in SQL.
27. How can you optimize SQL queries to improve performance?
28. What is the difference between UNION and UNION ALL?
29. How is the LIKE operator used in SQL?
30. Explain the COALESCE function with an example.
