Tutorials

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

My SQL statement SELECT AVG(SALARY) FROM EMP yields inaccurate results. Why?

Because SALARY is not declared to have NULLs and the employees for whom the salary is not known are also counted.

How do you concatenate the FIRSTNAME and LASTNAME from EMP table to give a complete name?

SELECT FIRSTNAME ‘ ‘ LASTNAME 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

What is a transaction and ACID?

Transaction - A transaction is a logical unit of work. All steps must be committed or rolled back.
ACID - Atomicity, Consistency, Isolation and Durability, these are properties of a transaction.

Materialized Query Tables in db2 ( This feature might not be available in oracle)?

Materialized Query Tables or MQTs are also known as automatic summary tables. A materialized query table (MQT) is a table whose definition is based upon the result of a query. The data that is contained in an MQT is derived from one or more tables on which the materialized query table definition is based. MQT improve the query performance.

Sample SQL to creat MQT.

CREATE TABLE CUSTOMER_ORDER AS
(SELECT SUM(AMOUNT) AS TOTAL_SUM,
TRANS_DT,
STATUS
FROM DB2INST2.CUSTOMER_ORDER
WHERE TRANS_DT BETWEEN ‘1/1/2001′ AND ‘12/31/2001′
GROUP BY TRANS_DT,
STATUS)
DATA INITIALLY DEFERRED REFRESH DEFERRED;