SQL interview questions

SQL quiz questions

  • 1.

    Can you sort a column using a column alias?

    Answer:

    Yes. A column alias could be used in the ORDER BY clause.

    View
  • 2.

    What are various DCL commands in SQL? Give brief description of their purposes.

    Answer:

    Following are various DCL or Data Control Language commands in SQL −

    • GRANT − it gives a privilege to user.

    • REVOKE − it takes back privileges granted from user.

    View
  • 3.

    What are various DML commands in SQL? Give brief description of their purposes.

    Answer:

    Following are various DML or Data Manipulation Language commands in SQL −

    • SELECT − it retrieves certain records from one or more tables.

    • INSERT − it creates a record.

    • UPDATE − it modifies records.

    • DELETE − it deletes records.

    View
  • 4.

    What are various DDL commands in SQL? Give brief description of their purposes.

    Answer:

    Following are various DDL or Data Definition Language commands in SQL −

    • CREATE − it creates a new table, a view of a table, or other object in database.

    • ALTER − it modifies an existing database object, such as a table.

    • DROP − it deletes an entire table, a view of a table or other object in the database.

    View
  • 5.

    What is the difference between SQL and PL/SQL?

    Answer:

    PL/SQL is a dialect of SQL that adds procedural features of programming languages in SQL. It was developed by Oracle Corporation in the early 90's to enhance the capabilities of SQL.

    View
  • 6.

    What is the difference between SQL and MySQL or SQL Server?

    Answer:

    SQL or Structured Query Language is a language; language that communicates with a relational database thus providing ways of manipulating and creating databases. MySQL and Microsoft’s SQL Server both are relational database management systems that use SQL as their standard relational database language.

    View
  • 7.

    What are the differences among ROWNUM, RANK and DENSE_RANK?

    Answer:

    ROW_NUMBER assigns contiguous, unique numbers from 1.. N to a result set.

    RANK does not assign unique numbers—nor does it assign contiguous numbers. If two records tie for second place, no record will be assigned the 3rd rank as no one came in third, according to RANK. See below:

    SELECT name, sal, rank() over(order by sal desc) rank_by_sal
    FROM EMPLOYEE o
    
    
    name Sal RANK_BY_SAL
    Hash 100 1
    Robo 100 1
    Anno 80 3
    Darl 80 3
    Tomiti 70 5
    Pete 70 5
    Bhuti 60 7
    Meme 60 7
    Inno 50 9
    Privy 50 9

    DENSE_RANK, like RANK, does not assign unique numbers, but it does assign contiguous numbers. Even though two records tied for second place, there is a third-place record. See below:

    SELECT name, sal, dense_rank() over(order by sal desc) dense_rank_by_sal
    FROM EMPLOYEE o
    
    
    name Sal DENSE_RANK_BY_SAL
    Hash 100 1
    Robo 100 1
    Anno 80 2
    Darl 80 2
    Tomiti 70 3
    Pete 70 3
    Bhuti 60 4
    Meme 60 4
    Inno 50 5
    Privy 50 5
    View
  • 8.

    What is the difference between ROWNUM pseudo column and ROW_NUMBER() function?

    Answer:

    ROWNUM is a pseudo column present in Oracle database returned result set prior to ORDER BY being evaluated. So ORDER BY ROWNUM does not work.

    ROW_NUMBER() is an analytical function which is used in conjunction to OVER() clause wherein we can specify ORDER BY and also PARTITION BY columns.

    Suppose if you want to generate the row numbers in the order of ascending employee salaries for example, ROWNUM will not work. But you may use ROW_NUMBER() OVER() like shown below:

    SELECT name, sal, row_number() over(order by sal desc) rownum_by_sal
    FROM EMPLOYEE o
    
    
    name Sal ROWNUM_BY_SAL
    Hash 100 1
    Robo 100 2
    Anno 80 3
    Darl 80 4
    Tomiti 70 5
    Pete 70 6
    Bhuti 60 7
    Meme 60 8
    Inno 50 9
    Privy 50 10
    View
  • 9.

    How to select first 5 records from a table?

    Answer:

    This question, often asked in many interviews, does not make any sense to me. The problem here is how do you define which record is first and which is second. Which record is retrieved first from the database is not deterministic. It depends on many uncontrollable factors such as how database works at that moment of execution etc. So the question should really be – “how to select any 5 records from the table?” But whatever it is, here is the solution:

    In Oracle,

    SELECT * 
    FROM EMP
    WHERE ROWNUM <= 5;
    
    

    In SQL Server,

    SELECT TOP 5 * FROM EMP;
    
    

    Generic solution,

    I believe a generic solution can be devised for this problem if and only if there exists at least one distinct column in the table. For example, in our EMP table ID is distinct. We can use that distinct column in the below way to come up with a generic solution of this question that does not require database specific functions such as ROWNUM, TOP etc.

    SELECT  name 
    FROM EMPLOYEE o
    WHERE (SELECT count(*) FROM EMPLOYEE i WHERE i.name < o.name) < 5
    
    
    name
    Inno
    Anno
    Darl
    Meme
    Bhuti

    I have taken “name” column in the above example since “name” is happened to be unique in this table. I could very well take ID column as well.

    In this example, if the chosen column was not distinct, we would have got more than 5 records returned in our output.

    Do you have a better solution to this problem? If yes, post your solution in the comment.

    View
  • 10.

    How to generate row number in SQL Without ROWNUM

    Answer:

    Generating a row number – that is a running sequence of numbers for each row is not easy using plain SQL. In fact, the method I am going to show below is not very generic either. This method only works if there is at least one unique column in the table. This method will also work if there is no single unique column, but collection of columns that is unique. Anyway, here is the query:

    SELECT name, sal, (SELECT COUNT(*)  FROM EMPLOYEE i WHERE o.name >= i.name) row_num
    FROM EMPLOYEE o
    order by row_num
    
    
    NAME SAL ROW_NUM
    Anno 80 1
    Bhuti 60 2
    Darl 80 3
    Hash 100 4
    Inno 50 5
    Meme 60 6
    Pete 70 7
    Privy 50 8
    Robo 100 9
    Tomiti 70 10

    The column that is used in the row number generation logic is called “sort key”. Here sort key is “name” column. For this technique to work, the sort key needs to be unique. We have chosen the column “name” because this column happened to be unique in our Employee table. If it was not unique but some other collection of columns was, then we could have used those columns as our sort key (by concatenating those columns to form a single sort key).

    Also notice how the rows are sorted in the result set. We have done an explicit sorting on the row_num column, which gives us all the row numbers in the sorted order. But notice that name column is also sorted (which is probably the reason why this column is referred as sort-key). If you want to change the order of the sorting from ascending to descending, you will need to change “>=” sign to “<=” in the query.

    As I said before, this method is not very generic. This is why many databases already implement other methods to achieve this. For example, in Oracle database, every SQL result set contains a hidden column called ROWNUM. We can just explicitly select ROWNUM to get sequence numbers.

    View
  • 11.

    How can we transpose a table using SQL (changing rows to column or vice-versa) ?

    Answer:

    The usual way to do it in SQL is to use CASE statement or DECODE statement.

    View
  • 12.

    What is Self Join and why is it required?

    Answer:

    Self Join is the act of joining one table with itself.

    Self Join is often very useful to convert a hierarchical structure into a flat structure

    In our employee table example above, we have kept the manager ID of each employee in the same row as that of the employee. This is an example of how a hierarchy (in this case employee-manager hierarchy) is stored in the RDBMS table. Now, suppose if we need to print out the names of the manager of each employee right beside the employee, we can use self join. See the example below:

    SELECT e.name EMPLOYEE, m.name MANAGER
    FROM EMPLOYEE e, EMPLOYEE m
    WHERE e.mgr_id = m.id (+)
    
    
    EMPLOYEE MANAGER
    Pete Hash
    Darl Hash
    Inno Hash
    Robo Hash
    Tomiti Robo
    Anno Robo
    Privy Robo
    Meme Pete
    Bhuti Tomiti
    Hash  

    The only reason we have performed a left outer join here (instead of INNER JOIN) is we have one employee in this table without a manager (employee ID = 1). If we perform inner join, this employee will not show-up.

    View
  • 13.

    What is the difference among UNION, MINUS and INTERSECT?

    Answer:

    UNION combines the results from 2 tables and eliminates duplicate records from the result set.

    MINUS operator when used between 2 tables, gives us all the rows from the first table except the rows which are present in the second table.

    INTERSECT operator returns us only the matching or common rows between 2 result sets.

    To understand these operators, let’s see some examples. We will use two different queries to extract data from our emp table and then we will perform UNION, MINUS and INTERSECT operations on these two sets of data.

    UNION

    SELECT * FROM EMPLOYEE WHERE ID = 5
    UNION 
    SELECT * FROM EMPLOYEE WHERE ID = 6
    
    
    ID MGR_ID DEPT_ID NAME SAL DOJ
    5 2 2.0 Anno 80.0 01-Feb-2012
    6 2 2.0 Darl 80.0 11-Feb-2012

    MINUS

    SELECT * FROM EMPLOYEE
    MINUS
    SELECT * FROM EMPLOYEE WHERE ID > 2
    
    
    ID MGR_ID DEPT_ID NAME SAL DOJ
    1   2 Hash 100.0 01-Jan-2012
    2 1 2 Robo 100.0 01-Jan-2012

    INTERSECT

    SELECT * FROM EMPLOYEE WHERE ID IN (2, 3, 5)
    INTERSECT
    SELECT * FROM EMPLOYEE WHERE ID IN (1, 2, 4, 5)
    
    
    ID MGR_ID DEPT_ID NAME SAL DOJ
    5 2 2 Anno 80.0 01-Feb-2012
    2 1 2 Robo 100.0 01-Jan-2012
    View
  • 14.

    What is the difference between WHERE clause and HAVING clause?

    Answer:

    WHERE and HAVING both filters out records based on one or more conditions. The difference is, WHERE clause can only be applied on a static non-aggregated column whereas we will need to use HAVING for aggregated columns.

    To understand this, consider this example. 
    Suppose we want to see only those departments where department ID is greater than 3. There is no aggregation operation and the condition needs to be applied on a static field. We will use WHERE clause here:

    SELECT * FROM DEPT WHERE ID > 3
    
    
    ID NAME
    4 Sales
    5 Logistics

    Next, suppose we want to see only those Departments where Average salary is greater than 80. Here the condition is associated with a non-static aggregated information which is “average of salary”. We will need to use HAVING clause here:

    SELECT dept.name DEPARTMENT, avg(emp.sal) AVG_SAL
    FROM DEPT dept, EMPLOYEE emp
    WHERE dept.id = emp.dept_id (+)
    GROUP BY dept.name
    HAVING AVG(emp.sal) > 80
    
    
    DEPARTMENT AVG_SAL
    Engineering 90

    As you see above, there is only one department (Engineering) where average salary of employees is greater than 80.

    View
  • 15.

    What is the difference between UNION and UNION ALL?

    Answer:

    UNION and UNION ALL both unify for add two structurally similar data sets, but UNION operation returns only the unique records from the resulting data set whereas UNION ALL will return all the rows, even if one or more rows are duplicated to each other.

    In the following example, I am choosing exactly the same employee from the emp table and performing UNION and UNION ALL. Check the difference in the result.

    SELECT * FROM EMPLOYEE WHERE ID = 5
    UNION ALL
    SELECT * FROM EMPLOYEE WHERE ID = 5
    
    
    ID MGR_ID DEPT_ID NAME SAL DOJ
    5.0 2.0 2.0 Anno 80.0 01-Feb-2012
    5.0 2.0 2.0 Anno 80.0 01-Feb-2012
    SELECT * FROM EMPLOYEE WHERE ID = 5
    UNION 
    SELECT * FROM EMPLOYEE WHERE ID = 5
    
    
    ID MGR_ID DEPT_ID NAME SAL DOJ
    5.0 2.0 2.0 Anno 80.0 01-Feb-2012
    View
  • 16.

    What is the difference between JOIN and UNION?

    Answer:

    SQL JOIN allows us to “lookup” records on other table based on the given conditions between two tables. For example, if we have the department ID of each employee, then we can use this department ID of the employee table to join with the department ID of department table to lookup department names.

    UNION operation allows us to add 2 similar data sets to create resulting data set that contains all the data from the source data sets. Union does not require any condition for joining. For example, if you have 2 employee tables with same structure, you can UNION them to create one result set that will contain all the employees from both of the tables.

    SELECT * FROM EMP1
    UNION
    SELECT * FROM EMP2;
    View
  • 17.

    What is the difference between the WHERE and HAVING clauses?

    Answer:

    When GROUP BY is not used, the WHERE and HAVING clauses are essentially equivalent.

    However, when GROUP BYis used:

    • The WHERE clause is used to filter records from a result. The filtering occurs before any groupings are made.
    • The HAVING clause is used to filter values from a group (i.e., to check conditions after aggregation into groups has been performed).
    View
  • 18.

    What is the difference between the RANK() and DENSE_RANK() functions? Provide an example.

    Answer:

    The only difference between the RANK() and DENSE_RANK() functions is in cases where there is a “tie”; i.e., in cases where multiple values in a set have the same ranking. In such cases, RANK() will assign non-consecutive “ranks” to the values in the set (resulting in gaps between the integer ranking values when there is a tie), whereas DENSE_RANK() will assign consecutive ranks to the values in the set (so there will be no gaps between the integer ranking values in the case of a tie).

    For example, consider the set {25, 25, 50, 75, 75, 100}. For such a set, RANK() will return {1, 1, 3, 4, 4, 6} (note that the values 2 and 5 are skipped), whereas DENSE_RANK() will return {1,1,2,3,3,4}.

    View
  • 19.

    What are the NVL and the NVL2 functions in SQL? How do they differ?

    Answer:

    Both the NVL(exp1, exp2) and NVL2(exp1, exp2, exp3) functions check the value exp1 to see if it is null.

    With the NVL(exp1, exp2) function, if exp1 is not null, then the value of exp1 is returned; otherwise, the value of exp2 is returned, but case to the same data type as that of exp1.

    With the NVL2(exp1, exp2, exp3) function, if exp1 is not null, then exp2 is returned; otherwise, the value of exp3 is returned.

    View
  • 20.

    How can you select all the even number records from a table? All the odd number records?

    Answer:

    To select all the even number records from a table:

    Select * from table where id % 2 = 0 
    

    To select all the odd number records from a table:

    Select * from table where id % 2 != 0
    View

© 2017 QuizBucket.org