SQL Interview Questions and Answers

01.   DDL, DML commands
      DDL: Data Definition Language (DDL) statements are used to define the database structure or schema.

• CREATE - to create objects in the database
•  ALTER - alters the structure of the database
•  DROP - delete objects from the database
• TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
•  COMMENT - add comments to the data dictionary
• RENAME - rename an object

DML: Data Manipulation Language (DML) statements are used for managing data within schema objects.

•          SELECT - retrieve data from the a database
•          INSERT - insert data into a table
•          UPDATE - updates existing data within a table
•          DELETE - deletes all records from a table, the space for the records remain
•          MERGE - UPSERT operation (insert or update)
•          CALL - call a PL/SQL or Java subprogram
•          EXPLAIN PLAN - explain access path to data
•          LOCK TABLE - control concurrency

02.   Group by , Having, Where and Order by clause
      Where Clause:

             WHERE clause is used to filter the results from a SELECT, INSERT, UPDATE, or DELETE                     statement.

       eg: SELECT * FROM customers WHERE last_name = 'Anderson';

      Order By Clause:

        ORDER BY clause is used to sort the records in your result set. The ORDER BY clause can only be used in SELECT statements.

      Syntax: SELECT expressions FROM tables WHERE conditions  ORDER BY expression [ ASC | DESC ];

      eg: SELECT supplier_city FROM suppliers WHERE supplier_name = 'Microsoft' ORDER BY supplier_city;

      GROUP BY Clause:

      GROUP BY Clause is used in a SELECT statement to collect data across multiple records and group the results by one or more columns.

      aggregate_function can be a function such as SUM, COUNT, MIN, MAX, or AVG functions.

      eg: SELECT product, SUM(sale) AS "Total sales" FROM order_details GROUP BY product;

      HAVING Clause:

      HAVING Clause is used in combination with the GROUP BY Clause to restrict the groups of returned rows to only those whose the condition is TRUE.

      eg1: SELECT department, SUM(sales) AS "Total sales" FROM order_details GROUP BY department HAVING SUM(sales) > 25000;

      eg2: SELECT department, COUNT(*) AS "Number of employees" FROM employees

      WHERE salary < 49500 GROUP BY department HAVING COUNT(*) > 10;

03.   Types of Joins (Left Outer Join, Right Outer Join, Full Outer Join, Self Join, Cross Join)
Joins can be classified into following types :

1.Inner Join (Equi Join & Natural Join)

2.Outer Join (Left Outer Join, Right Outer Join & Full Join)

3.Self Join

4.Cross Join

Inner Join: To retrieve matched records we can use comparison operators (i.e. =, < >).Whenever we need to get the matched records from two tables,this type of Join is used.

Inner Join Also call as Natural Join or Equi Join.

Syntax:

Select emp.emp_id, emp. sal, ph.phone_no from employee emp Inner Join phone ph

on emp.emp_id = ph. emp_id where emp.sal > 2000

Outer Join : To retrieve matched and unmatched records from two tables we can use Outer Join. Unmatched rows will be displayed as NULL from both tables.

Outer Join can be classified as: Left Outer Join , Right Outer Join and Full Outer Join.

Left Outer Join: All matched and unmatched records from left table will be displayed and unmatched records will be displayed as NULL from left table.But from right table only matched records will be displayed as it is and unmatched records will not be displayed.

Syntax:

Select emp.emp_id, emp. sal, ph.phone_no from employee emp Left Join phone ph

on emp.emp_id = ph.emp_id where emp.sal > 2000 ;

Right Outer Join: All Records from right table will be displayed either matched or unmatched and unmatched records will be displayed as NULL from right table.But from left table only matched records will be displayed as it is and unmatched records will not be displayed.

Syntax:

Select emp.emp_id, emp. sal, ph.phone_no from employee emp Right Join phone ph

on emp.emp_id = ph.emp_id where emp.sal > 2000 ;

Full Outer Join:  All matched and unmatched records from both left table and right table will be displayed and unmatched records will be displayed as NULL from both the tables.

Cross Join: This Join returns all rows from the left table, each and every row from the left table is in combination with all rows from the right table. Cross join also called  as Cartesian Product Join.

Syntax:

SELECT au.au_fname, au.au_lname, p.pub_name FROM authors AS au INNER JOIN publishers AS p ON au.city = p.city AND au.state = p.state ORDER BY au.au_lname ASC, au.au_fname ASC ;

Self Join: A table can be joined to itself is a self-join.

For example, we can use a self-join to find out the authors in New Zealand who live in the same ZIP Code area. 

SELECT au.au_fname, au.au_lname, a.au_fname, au2.au_lname FROM authors au INNER Jauthors a ON au.zip = a.zip

WHERE au.city = ' New Zealand' ORDER BY au.au_fname ASC, au.au_lname ASC ;

04.    Difference between View and Materialized view

        http://marukondav.blogspot.in/2014/07/what-is-difference-between-view-and.html

05.   Difference between Join and Union

JOIN: A Join is used for displaying columns with the same or different names from different tables.  The output displayed will have all the columns shown individually. i.e. The columns will be aligned next to each other.

UNION: The UNION set operator is used for combining data from two tables which have columns with            the same datatype. When a UNION is performed the data from both tables will be collected in a single column having the same datatype.

06.   Difference between Where clause and Having clause

   HAVING is used to check conditions after the aggregation takes place. Such as Group by

   HAVING clause introduces a condition on aggregations

   WHERE is used before the aggregation takes place.

   WHERE clause introduces a condition on individual rows.

07.   Difference between Union and Union All
    Union: UNION performs a DISTINCT on the result set, eliminating any duplicate rows.

    Union All: UNION ALL does not remove duplicates and it therefore faster than UNION.

08.   Difference between Rank, Dense Rank and RowNumber

    ROW_NUMBER () OVER ([<partition_by_clause>] <order_by_clause>)

    Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in  each partition

    RANK () OVER ([<partition_by_clause>] <order_by_clause>)

    Returns the rank of each row within the partition of a result set.

    DENSE_RANK () OVER ([<partition_by_clause>] <order_by_clause>)

    Returns the rank of rows within the partition of a result set, without any gaps in the ranking.

09.   Difference between Function and Stored Procedure

• Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values). Functions can have only input parameters for it whereas Procedures can have input/output parameters.
• Function takes one input parameter it is mandatory but Stored Procedure may take o to n input parameters. Functions can be called from Procedure whereas Procedures cannot be called from Function.
• Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
• Procedures cannot be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
•  Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
• Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.    Inline Function can be thought of as views that take parameters and can be used in JOINs and other Rowset operations.
• Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
• We can go for Transaction Management in Procedure whereas we can't go in Function.

10.   Difference between Drop, Truncate and Delete

    DELETE:  The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE  condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.

   

  TRUNCATE:  TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUNCATE  is faster and doesn't use as much undo space as a DELETE.

    DROP:  The DROP command removes a table from the database. All the tables' rows, indexes and privileges   will also be removed. No DML triggers will be fired. The operation cannot be rolled back.

DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back    (undone),    while DROP and TRUNCATE operations cannot be rolled back.

11.   Difference between Sub-query and Correlated Sub-query

    Subquery : The inner query is executed only once The inner query will get executed first and the output of the inner query     used by the outer query.The inner query is not dependent on outer query.

    Eg:- SELECT cust_name, dept_no FROM Customer WHERE cust_name IN (SELECT cust_name FROM Customer);

    Correlated subquery:The outer query will get executed first and for every row of outer query, inner query will get     executed. So the inner query will get executed as many times as no.of rows in result of the outer query.The outer query     output can use the inner query output for comparison. This means inner query and outer query dependent on each other

     Eg:- SELECT cust_name,dept_id FROM Cust

        WHERE cust_name in (SELECT cust_name FROM dept WHERE cust.dept_id=dept.dept_id);

12.   Update, Alter functions ex: Rename the column, Update the column value

13.   Top 5 and bottom 5 sales
      http://marukondav.blogspot.in/2014/06/based-on-parameter-to-find-top-5-and.html

14.   Indexes( Bitmap index B-tree Index)
Bitmap Indexes
Oracle's two major index types are Bitmap indexes and B-Tree indexes. B-Tree indexes are the regular type that OLTP systems make much use of, and bitmap indexes are a highly compressed index type that tends to be used primarily for data warehouses.

Characteristic of Bitmap Indexes 
@@For columns with very few unique values (low cardinality)
Columns that have low cardinality are good candidates (if the cardinality of a column is <= 0.1 %  that the column is ideal candidate, consider also 0.2% – 1%)@@Tables that have no or little insert/update are good candidates (static data in warehouse)
@@Stream of bits: each bit relates to a column value in a single row of table
create bitmap index person_region on person (region);

        Row     Region   North   East   West   South
        1       North        1      0      0       0
        2       East         0      1      0       0
        3       West         0      0      1       0
        4       West         0      0      1       0
        5       South        0      0      0       1
        6       North        1      0      0       0

Advantage of Bitmap Indexes
The advantages of them are that they have a highly compressed structure, making them fast to read and their structure makes it possible for the system to combine multiple indexes together for fast access to the underlying table.
Compressed indexes, like bitmap indexes, represent a trade-off between CPU usage and disk space usage. A compressed structure is faster to read from disk but takes additional CPU cycles to decompress for access - an uncompressed structure imposes a lower CPU load but requires more bandwidth to read in a short time.
One belief concerning bitmap indexes is that they are only suitable for indexing low-cardinality data. This is not necessarily true, and bitmap indexes can be used very successfully for indexing columns with many thousands of different values.

Disadvantage of Bitmap Indexes 
The reason for confining bitmap indexes to data warehouses is that the overhead on maintaining them is enormous. A modification to a bitmap index requires a great deal more work on behalf of the system than a modification to a b-tree index. In addition, the concurrency for modifications on bitmap indexes is dreadful.

15.   Integrity Constraints (Primary Key, Foreign Key, Unique Key, Not Null Constraint, Check Constraint)

16.   Query Performance tuning

·         All columns involved in indexes should appear on WHERE and JOIN clauses on the same sequence they appear on index.

·         Avoid too much JOINs on query.

·         Avoid cursors at all costs!

·         Always restrict the number of rows and columns of your result. That way, you save disk, memory and network of the database server. Always verify WHERE clause and use TOP if necessary.

·         Verify if your server isn’t suffering from not-enough-disk-space illness.

·         SQL Server is case insensitive: he does not care about ‘A’ or ‘a’. Save time and don’t use functions like LOWER and UPPER when comparing VARCHARs.

·         The decreasing performance order of operators is:          = (faster)>, >=, <, <=LIKE<> (slower)

·         Use EXISTS or NOT EXISTS instead of IN or NOT IN. IN operators creates a overload on database.

·         Try to use BETWEEN instead of IN, too.

·         When using LIKE operator, try to leave the wildcards on the right side of the VARCHAR.

·         Always avoid using functions on your queries. SUBSTRING is your enemy. Try to use LIKE instead.

·         Sometimes is better to make various queries with UNION ALL than a unique query with too much OR operations on WHERE clause. Test it.

·         When there is a HAVING clause, it is better to filter most results on the WHERE clause and use HAVING only for what it is necessary.

·         Use UNION ALL instead of UNION. Use less subqueries.

·         Avoid to do much operations on your WHERE clause. If you are searching for a + 2 > 7, use a > 5 instead.

·         Use more variable tables and less temporary tables.

·         To delete all rows on a table, use TRUNCATE TABLE statement instead of DELETE.

17.Difference between in IN and EXISTS?

      Performance wise both should be same with less no of records.If no of records will be more, EXISTS is faster than IN.

      Mostly IN is used in case of subqueries and EXISTS is used in case of correlated subqueries.

     

     18.How to find average sal without using avg function?

      We can find the average salary by using  "sum (salary)/count(salary)" 

       Join can be used in either Where or From clause. But Join specification in From clause is recommended. We can also use Having and Where clause for filtering the data.

     19. Inline view?
      An inline view is a statement in the FROM-clause of another SELECT statement. In-line views are commonly used simplify complex queries by removing join operations and condensing several separate queries into a single query. This feature was introduced in Oracle 7.2.An example of inline view is given blow

      SELECT * FROM (select dept_no, count(*) emp_count
     from employeesgroup by dept_no) employees,
     department WHERE department.dept_no = employee.dept_no;

     20. With Clause?
     21. Difference between Package and Stored Procedure?