1. What is the purpose of an Index?
An index is used when we need a bookmark for our table. They are just used to speed up searches/queries. A table can only have one Clustered index and up to 249 Non-Clustered Indexes.
A clustered index physically sorts the data in a table.The value of a clustered index is a key-value pair,where key is the index key and value is the actual value.
If you create a primary key in a table,a clustered index is created by default.
Non- Clustered Index:
A non-clustered index sorts the data logically but not physically.The value of a non-clustered index is not the data but a pointer to the data page.So, we can say a non-clustered index is dependent on the clustered index.In the absence of clustered index, it refers a physical location in the Heap for value.
2. Difference between Stored procedure and function?
• Function has a return type but Stored procedure doesn't have a return type.
• Stored Procedure supports IN/OUT/IN-OUT Parameters while function supports only IN parameters.
• Stored procedure can contain all the DML(Select,update,insert,delete) statements but function can contain only select statement.
• Function can be called from a stored procedure but stored procedure cannot be executed from a function.
• For Exception Handling, the stored procedure can contain try---catch block but Function doesn't support try---catch block.
• Function can be called in a SELECT statement not a stored procedure.
3. What are the different types of joins you know? And Explain them.
The different type of joins I know are:
Inner Join, Outer Join and Self Join.
I. Inner Join/Join:
The First table and second table are matched row by row. The result set contains only the matching records from both the tables, unmatched rows are ignored. If the 2 tables has no matching records, then it returns NULL.
II. Outer Join:
Outer join is of 3 types,such as:
Left outer Join, Right outer Join, Full outer Join.
Left outer Join:
This join returns all the rows from the left table along with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.
Right outer Join:
This join returns all the rows from the right table along with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.
Full outer Join:
It returns all the rows from both the table when the conditions are met and returns null value when there is no match.
III. Self Join:
Joining the table itself called self join. Self join is used to retrieve the records having some relation or similarity with other records in the same table. Here we need to use aliases for the table to join 2 different column of the same table.
5. What is the maximum number of non-clustered index that can be created by for a table?
6. What is the difference between "Having" and "Where" clause ?
• Having clause is a search condition like Where clause but is used only for an aggregate(Avg, Sum, etc.) or Group By statement.
• Having can be used only in a Select statement unlike Where clause which can be used in any DML Statement.
7. Difference between Primary Key and Unique Key?
I. A table can have only one primary key column but it can have more than one unique key columns.But the combination of the columns must be having a unique value.
II. Primary key cannot have a NULL value but an Unique Key column can have only one NULL value.
III. By default,primary key creates a clustered Index whereas unique key creates a non-clustered Index.
8. Can a foreign key reference a non-primary key?
Yes, a foreign key can actually reference a key that is not the primary key of a table. But, a foreign key must reference a unique key.
9. Difference between delete and truncate?
i. Delete command delete the rows from a table row by row but Truncate command delete all the rows from a table at one time.
ii. TRUNCATE is much faster than DELETE.
iii. You cann't rollback in TRUNCATE but in DELETE you can rollback.TRUNCATE removes the record permanently.
iv. TRUNCATE is a DDL command whereas DELETE is a DML command.
v. In case of TRUNCATE ,Trigger doesn't get fired.But in DML commands like DELETE .Trigger get fired.
vi. You cann't use conditions(WHERE clause) in TRUNCATE.But in DELETE you can write conditions using WHERE clause.
10. Difference between Union and Union All ?
The only difference between UNION and UNION ALL is the fact Union removes all the duplicate rows between 2 tables whereas Union All returns all the values from both tables.
Performance wise Union All is faster than Union as itt requires some extra effort to eliminate the extra rows.
Union command is basically used to return all related rows between 2 tables.
11. What are different type of subqueries?
A query nested inside another query is called a subquery. The different types of subqueries are:
i. Single row subquery : Returns zero or one row.
ii. Multiple row subquery : Returns one or more rows.
iii. Multiple column subquery : Returns one or more columns.
iv. Correlated subqueries : Reference one or more columns in the outer SQL statement. The subquery is known as a correlated subquery because the subquery is related to the outer SQL statement.
v. Nested subqueries : Subqueries are placed within another subqueries.
1. What is the purpose of an Index?
3. What are the different types of joins you know? And explain them?
A)Inner Join: Returns matching columns from both the tables.
Outer join: Returns all columns from both the tables.
Left join: Returns all columns from left table and matching columns from right table.
Right join: Returns all columns from right table and matching columns from left table.
9.Difference between delete and truncate?
Delete: Using this command we can delete the specified rows using condition in a table. If we dont specify any condition then all rows will be deleted.
We can rollback the delete command.
Truncate: Using this command we can delete rows in a table, we cant specify any condition for this command.
We cannot rollback the truncate command.
Truncate command executes faster than delete command.
10.Difference between Union and Union All ?
Union command is used to join two sql queries and gives one result set.
By using Union we get only distinct records.
Union All is also used for the same purpose but this command will include all duplicate records as well.
If you learn't from defeat..
You haven't really lost..