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;
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.
No comments:
Post a Comment