SQL Interview Q& A Part- 1
To improve performance, transactions are sub divided, this is called as Partitioning.
The types of partitions are
Round-Robin Partitioning:
- By informatica data is distributed evenly among all partitions
- In each partition where the number of rows to process are approximately same this partioning is applicable
Hash Partitioning:
- For the purpose of partitioning keys to group data among partitions Informatica server applies a hash function
- It is used when ensuring the processes groups of rows with the same partitioning key in the same partition need to be ensured
2. What is the difference between DROP and TRUNCATE statements?
If a table is dropped, all things associated with the tables are dropped as well. This includes — the relationships defined on the table with other tables, the integrity checks and constraints, access privileges and other grants that the table has. To create and use the table again in its original form, all these relations, checks, constraints, privileges and relationships need to be redefined. However, if a table is truncated, none of the above problems exist and the table retains its original structure.
3. 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 t
4. What is an Index?
An index is performance tuning method of allowing faster retrieval of records from the table. An index creates an entry for each value and it will be faster to retrieve data.
There are various types of indexes in SQL server:
CLUSTERED INDEX
A clustered index is an index that sorts the rows in a database table based on a specific column value and defines the manner in which the data in the table is stored on the disk. When we create a table with a primary key constraint, a clustered index is automatically created by default based on this primary key. A table can only have one clustered index. In order to create a new clustered index, we have to remove the previous one.
NON-CLUSTERED INDEX
It represents a structure, which is isolated from data rows. This types of indexes in SQL server covers the non-clustered key values, and each worth pair has a pointer to the data row that comprises vital significance.
In the non-clustered index, the client can undoubtedly add non-key to the leaf level, as it sidesteps the current index key cut-off points and performs completely covered recorded questions. A non-clustered index is made to improve the general exhibition of much of the time posed inquiries, which are not covered by grouped things.
Clustered vs. Non-clustered index in SQL server is that the non-clustered index stores the data at one area and indices at another area, while the clustered index is a kind of index that sorts the data rows in the table on their key values.
COLUMN STORE INDEX
A column store index is one of the types of indexes in SQL Server that has a standard type of index with regards to putting away and questioning enormous data warehousing truth tables. This is an index of SQL, which was intended for development in the presentation of inquiry in the event of jobs with huge measures of data.
The column-store index empowers putting away information inside little impressions, which helps in speeding up. The use of this index takes into account the client to get IO with multiple times higher inquiry execution when contrasted with conventional column arranged capacity. For examination, the Columnstore Index gives a significant degree to have a preferable exhibition over other records in SQL. Column store index esteems from a similar area have comparative qualities, which expands the general pace of information compressions.
FILTERED INDEX
A filtered index is one of the types of indexes in an SQL server that is made when a column has just a few applicable numbers for questions on the subset of values. If, when a table comprises heterogeneous data rows, a separated list is made in SQL for at least one sort of data.
HASH INDEX
Hash Index is one of the types of indexes in SQL server that slots containing a pointer or an array of N buckets and a row on each slot or bucket. It utilizes the Hash function F (K, N), where N is several buckets and K is critical. The capacity delineates the key relating to the bucket of the hash index. Every bucket of the Hash Index comprises eight bytes, which are utilized to stock the memory address of the connected rundown of basic sections.
UNIQUE INDEX
The unique index in the SQL server confirms and guarantees that the index key doesn’t contain any copy esteems and along these lines, empowers the clients to examine that each row in the table is exceptional in either way.
The unique index in SQL is extraordinarily utilized when the client needs to have an extraordinary trait of every information. It permits people to guarantee the data respectability of each characterized section of the table in the data set. This index likewise gives extra data about the data table, which is useful to question enhancers.
5. How to fetch alternate records from a table?
Records can be fetched for both Odd and Even row numbers -.
To display even numbers-.
Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=0To display odd numbers-.
Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=1from (Select rowno, studentId from student) where mod(rowno,2)=1.[/sql]
6. What are the different subsets of SQL?
- Data Definition Language (DDL) — It allows you to perform various operations on the database such as CREATE, ALTER, and DELETE objects.
- Data Manipulation Language(DML) — It allows you to access and manipulate data. It helps you to insert, update, delete and retrieve data from the database.
- Data Control Language(DCL) — It allows you to control access to the database. Example — Grant, Revoke access permissions.
7. What is the usage of the NVL() function?
You may use the NVL function to replace null values with a default value. The function returns the value of the second parameter if the first parameter is null. If the first parameter is anything other than null, it is left alone.
This function is used in Oracle, not in SQL and MySQL. Instead of NVL() function, MySQL have IFNULL() and SQL Server have ISNULL() function.
Let’s move to the next question in this SQL Interview Questions.
8. What is the difference between the RANK() and DENSE_RANK() functions?
The RANK() function in the result set defines the rank of each row within your ordered partition. If both rows have the same rank, the next number in the ranking will be the previous rank plus a number of duplicates. If we have three records at rank 4, for example, the next level indicated is 7.
The DENSE_RANK() function assigns a distinct rank to each row within a partition based on the provided column value, with no gaps. It always indicates a ranking in order of precedence. This function will assign the same rank to the two rows if they have the same rank, with the next rank being the next consecutive number. If we have three records at rank 4, for example, the next level indicated is 5.
9. Use COALESCE to return the first non-null expression
The SQL Coalesce is used to manage the NULL values of the database. In this method, the NULL values are substituted with the user-defined value. The SQL Coalesce function assesses the parameters in series and always delivers the first non-null value from the specified argument record.
Syntax
1
SELECT COALESCE(NULL,NULL,'ByteScout',NULL,'Byte')
Output
ByteScout
10. DENSE_RANK()Analytical query
It is an analytic query that computes the rank of a row in an arranged collection of rows. An output rank is a number starting from 1. DENSE_RANK is one of the most important SQL queries. It returns rank preferences as sequential numbers. It does not jump rank in event of relations. For example, the following query will give the sequential ranks to the employee.
SELECT eno,
dno,
salary,
DENSE_RANK() OVER (PARTITION BY dno ORDER BY salary) AS ranking
FROM employee;
ENO DNO SALARY RANKING
---------- ---------- ---------- ----------
7933 10 1500 1
7788 10 2650 2
7831 10 6000 3
7362 20 900 1
7870 20 1200 2
7564 20 2575 3
7784 20 4000 4
7903 20 4000 4
7901 30 550 1
7655 30 1450 2
7522 30 1450 2
7844 30 1700 3
7493 30 1500 4
7698 30 2850 5
11.Query_partition_clause
The query_partition_clause breaks the output set into distributions, or collections, of data. The development of the analytic query is limited to the confines forced by these partitions, related to the process a GROUP BY clause modifies the performance of an aggregate function. If the query_partition_clause is eliminated, the entire output collection is interpreted as a separate partition.
The following query applies an OVER clause, so the average displayed is based on all the records of the output set.
SELECT eno, dno, salary,
AVG(salary) OVER () AS avg_sal
FROM employee;
EO DNO SALARY AVG_SAL
---------- ---------- ---------- ----------
7364 20 900 2173.21428
7494 30 1700 2173.21428
7522 30 1350 2173.21428
7567 20 3075 2173.21428
7652 30 1350 2173.21428
7699 30 2950 2173.21428
7783 10 2550 2173.21428
7789 20 3100 2173.21428
7838 10 5100 2173.21428
7845 30 1600 2173.21428
7877 20 1200 2173.21428
7901 30 1050 2173.21428
7903 20 3100 2173.21428
7935 10 1400 2173.21428
12. Finding the last five records from the table
Now, if you want to fetch the last eight records from the table then it is always difficult to get such data if your table contains huge information. For example, you want to get the last 8 records from the employee table then you can use rownum and a union clause. The rownum is temporary in SQL.
For example,
Select * from Employee A where rownum <=8
union
select * from (Select * from Employee A order by rowid desc) where rownum <=8;
The above SQL query will give you the last eight records from the employee table where rownum is a pseudo column. It indexes the data in an output set.
13. LAG
The LAG is applied to get data from a prior row. This is an analytical function. For example, the following query gives the salary from the prior row to compute the difference between the salary of the current row and that of the prior row. In this query, the ORDER BY of the LAG function is applied. The default is 1 if you do not define offset. The arbitrary default condition is given if the offset moves past the range of the window. The default is null if you do not define default.
Syntax
SELECT dtno,
eno,
emname,
job,
salary,
LAG(sal, 1, 0) OVER (PARTITION BY dtno ORDER BY salary) AS salary_prev
FROM employee;
Output
DTNO ENO ENAME JOB SAL SAL_PREV
---------- ---------- ---------- --------- ---------- ----------
10 7931 STEVE CLERK 1300 0
10 7783 JOHN MANAGER 2450 1300
10 7834 KING PRESIDENT 5000 2450
20 7364 ROBIN CLERK 800 0
20 7876 BRIAN CLERK 1100 800
20 7567 SHANE MANAGER 2975 1100
20 7784 SCOTT ANALYST 3000 2975
20 7908 KANE ANALYST 3000 3000
30 7900 JAMES CLERK 950 0
30 7651 CONNER SALESMAN 1250 950
30 7522 MATTHEW SALESMAN 1250 1250
30 7843 VIVIAN SALESMAN 1500 1250
30 7494 ALLEN SALESMAN 1600 1500
30 7695 GLEN MANAGER 2850 1600
14. LEAD
The LEAD is also an analytical query that is applied to get data from rows extra down the output set. The following query gives the salary from the next row to compute the deviation between the salary of the prevailing row and the subsequent row. The default is 1 if you do not define offset. The arbitrary default condition is given if the offset moves past the range of the window. The default is null if you do not define default.
SELECT eno,
empname,
job,
salary,
LEAD(salary, 1, 0) OVER (ORDER BY salary) AS salary_next,
LEAD(salary, 1, 0) OVER (ORDER BY salary) - salary AS salary_diff
FROM employee;
ENO EMPNAME JOB SALARY SALARY_NEXT SALARY_DIFF
---------- ---------- --------- ---------- ---------- ----------
7369 STEVE CLERK 800 950 150
7900 JEFF CLERK 950 1100 150
7876 ADAMS CLERK 1100 1250 150
7521 JOHN SALESMAN 1250 1250 0
7654 MARK SALESMAN 1250 1300 50
7934 TANTO CLERK 1300 1500 200
7844 MATT SALESMAN 1500 1600 100
7499 ALEX SALESMAN 1600 2450 850
7782 BOON MANAGER 2450 2850 400
7698 BLAKE MANAGER 2850 2975 125
7566 JONES MANAGER 2975 3000 25
7788 SCOTT ANALYST 3000 3000 0
7902 FORD ANALYST 3000 5000 2000
7839 KING PRESIDENT 5000 0 -5000
15. WITH (Common Table Expressions)
A common table expression (CTE) is a defined short result set that endures within the range of a particular statement and that can be called later within that statement, perhaps on many occasions. The following query is describing the CTE:
Syntax
WITH all_emp
AS
(
SELECT empId, BossId, FirstName, LastName
FROM Emp
WHERE BossId is NULL
UNION ALL
SELECT e.empId, e.BossId, e.FirstName, e.LastName
FROM Emp e INNER JOIN all_emp r
ON e.BossId = r.Id
)
SELECT * FROM all_emp
16.MIN
Utilizing a blank OVER clause converts the MIN into an analytic function. This is also an analytical query. In this, the entire result set is interpreted as a single partition. It gives you the minimum salary for all employees and their original data. For example, the following query is displaying the use of MIN in the Select query.
SELECT eno,
empname,
dtno,
salary,
MIN(salary) OVER (PARTITION BY dtno) AS min_result
FROM employee;
ENO EMPNAME DTNO SALARY MIN_RESULT
---------- ---------- ---------- ---------- ---------------
7782 CLARK 10 2450 1300
7839 KING 10 5000 1300
7934 MILLER 10 1300 1300
7566 JONES 20 2975 800
7902 FORD 20 3000 800
7876 ADAMS 20 1100 800
7369 SMITH 20 800 800
7788 SCOTT 20 3000 800
7521 WARD 30 1250 950
7844 TURNER 30 1500 950
7499 ALLEN 30 1600 950
7900 JAMES 30 950 950
7698 BLAKE 30 2850 950
7654 MARTIN 30 1250 950
17. MAX
Using a blank row OVER clause converts the MAX into an analytic function. The lack of a partitioning clause indicates the entire output set is interpreted as a separate partition. This gives the maximum salary for all employees and their original data. For example, the following query displays the use of MAX in the select query.
SELECT eno,
empname,
dtno,
salary,
MAX(salary) OVER () AS max_result
FROM employee;
ENO EMPNAME DTNO SALARY MAX_RESULT
---------- ---------- ---------- ---------- ----------
7369 SMITH 20 800 3000
7499 ALLEN 30 1600 3000
7521 WARD 30 1250 3000
7566 JONES 20 2975 3000
7654 MARTIN 30 1250 3000
7698 BLAKE 30 2850 3000
7782 CLARK 10 2450 3000
7788 SCOTT 20 3000 3000
7839 KING 10 5000 3000
7844 TURNER 30 1500 3000
7876 ADAMS 20 1100 3000
7900 JAMES 30 950 3000
7902 FORD 20 3000 3000
7934 MILLER 10 1300 3000
from (Select rowno, studentId from student) where mod(rowno,2)=1.[/sql]
18. SQL Query Order of Execution
In SQL, the order of written code differs from the order of execution. So in order to understand how the SQL query works you’ll need to understand proper SQL query structure and the order in which SQL queries must be written using following keywords
19.What is the difference between UNION and JOIN?
A JOIN allows a user to find records on another table based on a given condition between the two tables.
A UNION operations allows a user to add 2 similar dat sets to create a resulting data set that contains all the data from the source data sets. The syntax for a UNION looks like:
SELECT * FROM Table_A
UNION
SELECT * FROM Table_B;
20. What is the difference between UNION and UNION ALL?
UNION ALL is almost the same at UNION except it will return all rows of both datasets, even if there are duplicates.
21.How to write the sql query for below scenario?
I/P:Mohit
o/p:
M
o
h
i
t
select substr(“Mohit”,level,1) from dual
connected by level ≤ length(‘Mohit’);
22. How to display following query?
- *
- **
- ***
select lpad(‘*’,ROWNUM,’*’) from dual where ROWNUM<4
23. How to display 1 to 100 number using query?
select level from dual connected by level ≤100
24. How to fetch the 3rd highest salary?
select * from (select dense_rank() over (order by sal) as rnak ,salary from emp) where rnak=3;
or
select distint salary from emp a where 3≥(select count(distinct salary) from emp b where a.emp≤b.emp) order by a.salary desc;
25. What is pivot table?
1. Pivot:
SELECT (ColumnNames)
FROM (TableName)
PIVOT
(
AggregateFunction(ColumnToBeAggregated)
FOR PivotColumn IN (PivotColumnValues)
) AS (Alias) //Alias is a temporary name for a table2. Unpivot:
SELECT (ColumnNames)
FROM (TableName)
UNPIVOT
(
AggregateFunction(ColumnToBeAggregated)
FOR PivotColumn IN (PivotColumnValues)
) AS (Alias)Applying PIVOT and UNPIVOT operator:
SELECT CourseName, CourseCategory, Price
FROM
(
SELECT CourseName, PROGRAMMING, INTERVIEWPREPARATION FROM geeksforgeeks
PIVOT
(
SUM(Price) FOR CourseCategory IN (PROGRAMMING, INTERVIEWPREPARATION)
) AS PivotTable
) P
UNPIVOT
(
Price FOR CourseCategory IN (PROGRAMMING, INTERVIEWPREPARATION)
)
AS UnpivotTable
