Career in SQL (SQL Interview Questions)

How would you find out the total number of rows in a table?
Use SELECT COUNT(*) ... in query

How do you eliminate duplicate values in SELECT ?
Use SELECT DISTINCT ... in SQL query

How you insert records into a table
Using SQL INSERT statement

How do you delete record from a table ?
Using DELETE statement Example : DELETE FROM EMP

How do you select a row using indexes?
Specify the indexed columns in the WHERE clause of query.

How do you find the maximum value in a column?
Use SELECT MAX(...) .. in query

How do you retrieve the first 5 characters of FIRSTNAME column of table EMP ?
SELECT SUBSTR(FIRSTNAME,1,5) FROM EMP

What is UNION,UNION ALL in SQL?
UNION : eliminates duplicates
UNION ALL: retains duplicates
Both these are used to combine the results of different SELECT statements.


Suppose I have five SQL SELECT statements connected by UNION/UNION ALL, how many times should I specify UNION to eliminate the duplicate rows?
Once.

In the WHERE clause what is BETWEEN and IN?
BETWEEN supplies a range of values while IN supplies a list of values.

Is BETWEEN inclusive of the range values specified?
Yes.

What is 'LIKE' used for in WHERE clause? What are the wildcard characters?
LIKE is used for partial string matches. ‘%’ ( for a string of any character )
and ‘_’ (for any single character ) are the two wild card characters.

When do you use a LIKE statement?
To do partial search e.g. to search employee by name, you need not specify
the complete name; using LIKE, you can search for partial string matches.
Example SQL : SELECT EMPNO FROM EMP WHERE EMPNAME LIKE 'RAMESH%'
% is used to represent remaining all characters in the name. This query fetches all records contains RAMESH in six characters.

What do you accomplish by GROUP BY ... HAVING clause?
GROUP BY partitions the selected rows on the distinct values of the column on which you group by. HAVING selects GROUPs which match the criteria specified

Consider the employee table with column PROJECT nullable. How can you get a list
of employees who are not assigned to any project?
SQL : SELECT EMPNO FROM EMP WHERE PROJECT IS null;

What are the large objects supported by oracle and db2?
Blob , Clob ( Binary Large Objects, Character Large Objects)

What's the difference between a primary key and a unique key?
Primary key wont allow nulls, unique key allow nulls.
Both Primary key and Unique key enforce the uniqueness of the column on which they are defined.

What is a join and explain different types of joins?
INNER JOIN
OUTER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
INNER JOIN

What is a self join?
Joining two instances of a same table.
Sample SQL : SELECT A.EMPNAME , B.EMPNAME
FROM EMP A, EMP B
WHERE A.MGRID = B.EMPID