Basic SQL Question with Answer

Differentiate SQL and SQL*plus?

1.             SQL is a language where as ISQL*Plus is an environment only

2.             SQL is an ANSI standard where ad ISQL*Plus is Oracle Proprietary only

3.             In SQL keyword cannot be abbreviated where as in ISQL*Plus keyword can be abbreviated.

4.             Statements manipulate data and Table definition is possible where as incase of ISQL*Plus statement does not manipulate data and table definition is also not possible.

5.              

Define Null in term of SQL?

A null is a value that is unassigned, unknown or inapplicable. A null is not the same as Zero. Arithmetic expression containing null value evaluate to null.

How many types of Queries Types in SQL?

Selection, Projection, Join table

What is Constraints?

Constraints are used to restrict or bound inefficient data. Basically two types  of constraints are provided : column constraints are associated with a single column

Where as table constraints are typically associated with more than one column.

Integrity Constraints1:

1.             Primary Key. [Unique + Not Null]

2.             Not Null.[Cannot be Null]

3.             Unique Key[Unique can be Null]

Difference between Primary and Unique key?

Primary Key Defines a column value Unique as well as Not Null where as Unique key defines a column values only Unique. Means it can accept null value.

A Table may have only one primary key where as more than one unique key can be assigned within a table.

List the Difference between Delete and Truncate?

DELETE:
1. DELETE is a DML Command.
2. DELETE statement is executed using a row lock, each row in the table is locked for deletion.
3. We can specify filters in where clause
4. It deletes specified data if where condition exists.
5. Delete activates a trigger because the operations are logged individually.
6. Slower than truncate because, it keeps logs.
7. Rollback is possible.
TRUNCATE:
1. TRUNCATE is a DDL command.
2. TRUNCATE TABLE always locks the table and page but not each row.
3. Cannot use Where Condition.
4. It removes all the data.
5. TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
6. Faster in performance wise, because it doesn't keep any logs.
7. Rollback is not possible.

Difference Commit or Internal Commit?

As long as the user has not issued the commit statement, it is possible to undo all modifications since the last commit. To undo modifications, one has to issue the statement rollback; Note that any data definition command such as create table results in an internal commit. A commit is also implicitly executed when the user terminates an Oracle session.

What is Outer Join?

View data that does not generally meet a join condition by using outer join.

Difference between Equijoin and Non-Equijoin?

Equijoin is possible when same column name appears in more than one table thus there is a need of primary key or foreign key where as in case of Non equijoin there is no need of same column in both the table.

Equijoin possible for more than 2 table where as limit for only 2 table.

What is Natural Join?

It based on all columns in two tables that have the same name or if the column having the same name but its data type is different then it returns an error.

What is Using Clause join?

If the column have the same name but data type is different incase “using clause” is implemented. It can match only for one column incase more than one column are same.

Difference Inner vs Outer Join?

The join of two tables that return only matched result is known as inner join

Where as outer join returns the inner matched result of inner join as well as unmatched return of left and right table.

Define SQL function? Differentiate between Single row Function vs Multiple Row Function?

Multiple functions can manipulate on group of rows to give one result per group of rows where as a single row function can operate on a single row only and return one result per row. Single row function is used to manipulate data item. They accept one or more data item and return one value for each row. The single row function can be nested. Nesting can be evaluated from deeper to higher. Types of Single Row functions

1. Character Functions

a) Case Manipulation Function

LOWER, UPPER, INITCAP

b) Character Manipulation Function

CONCAT,SUBSTR,LENGTH,INSTR,LPAD, RPAD,TRIM,REPLACE

2. Number Functions

ROUND, TRUNC, MOD

3. Date Functions

SYSDATE,MONTHS_BETWEEN,ADD_MONTHS,NEXT_DAY,LAST_DAY,ROUND,TRUNC

4. Conversion

a) Implicit Data type Conversion

VARCHAR OR CHAR TO NUMBER,VARCHAR OR CHAR TO DATE

b) Explicit Data type Conversion

TO_NUMBER,TO_CHAR,TO_DATE

5. General Functions

NVL,NVL2,NULLIF,COALESCE,DECODE,CASE

NVL FUNCTION

The NVL function is used to convert null values to actual values, data type must match incase of NVL function. The date, char and number data type can be used in case of NVL.

NVL2 FUNCTION

The NVL2 function examines the first expression, if the first expression is not null then the NV2 function returns the second expression. If the first expression is null then it return the third expression.

NULLIF FUNCTION

The NULLIF function compares the two expression if they are equal then return null otherwise it return the first expression.

COALESCE FUNCTION

The COALESCE FUNCTION takes n number of arguments. It returns the 1^st expression if it is not null. It returns the 2^ndexpression if the first expression is null and 2^nd expression is not null. It returns the nth expression if all of the above expression is null and last expression is not null.

CASE FUNCTION

CASE Expression facilitates conditional inquiries by doing the work of an IF THEN ELSE statement. It compares the expression if the condition is matched return that expression. If none of then match the condition it return the else part. This function worked on ANSI standard. This feature introduce in oracle 9i.

DECODE FUNCTION

Decode function is the similar to CASE function only difference of syntax.

It also worked as IF then else statement.

To see current user name

Sql> show user;

Change SQL prompt name

SQL> set sqlprompt “Manimara > “

Manimara >

Switch to DOS prompt

SQL> host

How do I eliminate the duplicate rows from a table?

SQL> delete from table_name where rowid not in (select max(rowid) from table group by

duplicate_values_field_name);

or

SQL> delete duplicate_values_field_name dv from table_name ta where rowid <(select min(rowid) from table_name tb where ta.dv=tb.dv);

SQL> delete ename from emp a where rowid < ( select min(rowid) from emp b where a.ename = b.ename);

How do I display row number with records?

SQL> select rownum, ename from emp;

Display the records between two range

select rownum, empno, ename from emp where rowid in

(select rowid from emp where rownum <=&upto

minus

select rowid from emp where rownum<&Start);

Enter value for upto: 10

Enter value for Start: 7

The nvl function only allows the same data type (ie. number or char or date

Nvl(comm, 0)), if commission is null then the text “Not Applicable” want to display, instead of blank space. How do I write the query?

SQL> select nvl(to_char(comm.),'NA') from emp;

Output :

NVL(TO_CHAR(COMM),'NA')

-----------------------

NA

300

500

NA

Différence between Implicit & Explicit cursors ?

For SQL queries returning a single row, PL/SQL declares all implicit cursors.

For queries that returning more than one row, the cursor needs to be explicitly declared.

Find out nth highest salary from emp table

SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);

Enter value for n: 2

SAL

---------

3700

To view installed Oracle version information

SQL> select banner from v$version;

Display the number value in Words

SQL> select sal, (to_char(to_date(sal,'j'), 'jsp')) from emp;

SAL (TO_CHAR(TO_DATE(SAL,'J'),'JSP'))

--------- -----------------------------------------------------

800 eight hundred

1600 one thousand six hundred

1250 one thousand two hundred fifty

If you want to add some text like,

Rs. Three Thousand only.

SQL> select sal "Salary ", (' Rs. '|| (to_char(to_date(sal,'j'), 'Jsp'))|| ' only.'))

"Sal in Words" from emp;

Salary Sal in Words

------- ------------------------------------------------------

800 Rs. Eight Hundred only.

1600 Rs. One Thousand Six Hundred only.

1250 Rs. One Thousand Two Hundred Fifty only.

14. Display Odd/ Even number of records

Find Odd number of records?

select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);

Find Even number of records?

select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)

Which date function returns number value?

months_between

Any three PL/SQL Exceptions?

Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others

What are PL/SQL Cursor Exceptions?

Cursor_Already_Open, Invalid_Cursor

Other way to replace query result null value with a text

SQL> Set NULL ‘N/A’

to reset SQL> Set NULL ‘’

What are the more common pseudo-columns?

SYSDATE, USER , UID, CURVAL, NEXTVAL, ROWID, ROWNUM

What is the output of SIGN function?

1 for positive value,

0 for Zero,

-1 for Negative value.

What is the maximum number of triggers, can apply to a single table?

12 triggers.

What are Synonyms?

Synonym is the alias or alternative name for table, views, sequences & procedures and other database object. It is created for reasons of Security and Convenience. Two levels of security are Public - created by DBA & accessible to all the users. Private - Accessible to creator only. Advantages are referencing without specifying the owner and Flexibility to customize a more meaningful naming convention.

What are Sequences?

Sequences are used for generating sequence numbers without any overhead of locking. Drawback is that after generating a sequence number if the transaction is rolled back, then that sequence number is lost.

What is the difference between view and synonym in oracle?

A view is a stored query you can access as a (read-only) table. It is often used as an interface to other modules/programs. The view stays the same (name, columns, etc.) but the underlying query and/or tables can change without affecting the calling module.
A synonym is just that another name for an existing object (table, view, stored procedure) in oracle. You usually create public synonyms to access objects in another schema without referencing the schema.

How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?

One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships. Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

Define candidate key, alternate key and Composite key?

A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys. A key formed by combining at least two or more columns is called composite key.

Explain different isolation levels?

An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed. Here are the other isolation levels (in the ascending order of isolation): Read Uncommitted, Read Committed, Repeatable Read, and Serializable.

What is an index? What are the types of indexes? How many clustered indexes can be created on a table? I create a separate index on each column of a table. What are the advantages and disadvantages of this approach?

Index in oracle helps to trace the information faster just like and index in book. It is a pointer to the location of data purpose to make SQL queries run faster. There is several type of index in oracle such as: b*tree indexes, b*tree cluster indexes, hash cluster indexes, reverse key indexes, bitmap indexes, partitioned indexes, function-based indexes

What is blocking and how would you troubleshoot it?

Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first.