How to test the Stored Procedures?
What do you mean by index and explain different types of indexes?
Answer: An Index is a database object, created on a column of the table to find data
more quickly and efficiently (or we can say, it is used to speed up the searches/queries).
How to write a query to get the second largest value from a given column of a
table?
What is the function of Union operator?
What is the difference between Union and Union All?
What is SQL Injection threat?
What are Constraints in SQL?
NOT NULL - Restricts NULL value from being inserted into a column.
CHECK - Verifies that all values in a field satisfy a condition.
DEFAULT - Automatically assigns a default value if no value has been specified
for the field.
UNIQUE - Ensures unique values to be inserted into the field.
INDEX - Indexes a field providing faster retrieval of records.
PRIMARY KEY - Uniquely identifies each record in a table.
FOREIGN KEY
What is the difference between Clustered and Non-
clustered index?
As explained above, the differences can be broken down into three small factors -
1. Clustered index modifies the way records are stored in a database based on the
indexed column. Non-clustered index creates a separate entity within the table which
references the original table.
2. Clustered index is used for easy and speedy retrieval of data from the database,
whereas, fetching records from the non-clustered index is relatively slower.
3. In SQL, a table can have a single clustered index whereas it can have multiple
non-clustered indexes.
What are UNION, MINUS and INTERSECT
commands?
The UNION operator combines and returns the result-set retrieved by two or more
SELECT statements.
The MINUS operator in SQL is used to remove duplicates from the result-set obtained
by the second SELECT query from the result-set obtained by the first SELECT query
and then return the filtered results from the first.
The INTERSECT clause in SQL combines the result-set fetched by the two SELECT
statements where records from one match the other and then returns this intersection of
result-sets.
Certain conditions need to be met before executing either of the above statements in
SQL -
Each SELECT statement within the clause must have the same number of
columns
The columns must also have similar data types
The columns in each SELECT statement should necessarily have the
same order
SELECT name FROM Students /* Fetch the union of queries */
UNION
SELECT name FROM Contacts;
SELECT name FROM Students /* Fetch the union of queries with duplicates*/
UNION ALL
SELECT name FROM Contacts;
SELECT name FROM Students /* Fetch names from students */
MINUS /* that aren't present in contacts */
SELECT name FROM Contacts;
SELECT name FROM Students /* Fetch names from students */
INTERSECT /* that are present in contacts as well */
SELECT name FROM Contacts;
What is the difference between DELETE and
TRUNCATE statements?
The TRUNCATE command is used to delete all the rows from the table and free the
space containing the table.
The DELETE command deletes only the rows from the table based on the condition
given in the where clause or deletes all the rows from the table if no condition is
specified. But it does not free the space containing the table.
Name some Aggregate functions in SQL?
AVG() - Calculates the mean of a collection of values.
COUNT() - Counts the total number of records in a specific table or view.
MIN() - Calculates the minimum of a collection of values.
MAX() - Calculates the maximum of a collection of values.
SUM() - Calculates the sum of a collection of values.
FIRST() - Fetches the first element in a collection of values.
LAST() - Fetches the last element in a collection of values.
