SQL Interview questions -1

1.      Write a query to eliminate duplicate rows in a table?

Delete from emp x where rowid <= ( select max(rowid) from emp y where x.rowid = y.rowid)

2.      Write a query to find max 5 salaries of employees ?

Select * from (select * from emp order by sal desc) where rownum < 6

3)Show all data for Clerks hired after the year 1997

  Select * from employees where job_id=’ST_CLERK’ and hire_date >’31-dec-1997’;

4) show the last name,job,salary and commission of those employees who earn commission sort the data by the salary in descending order

        SELECT LAST_NAME,JOB_ID,SALARY,COMMISSION_PCT FROM EMPLOYEES WHERE COMMISSION_PCT IS NOT NULL ORDER BY SALARY DESC;

5) Show the employees who have no commission not have a 10% raise in salary (round off salaries)

      Select ‘the salary of’|| last_name||’after a 10% raise is’ from employees

Where  commission_pct is null;

6)What is SQL and where does it come from?

Structured Query Language (SQL) is a language that provides an interface to relational database systems. SQL was developed by IBM in the 1970s for use in System R, and is a de facto standard, as well as an ISO and ANSI standard. SQL is often pronounced SEQUEL.

In common usage SQL also encompasses DML (Data Manipulation Language), for INSERTs, UPDATEs, DELETEs and DDL (Data Definition Language), used for creating and modifying tables and other database structures.
The development of SQL is governed by standards. A major revision to the SQL standard was completed in 1992, called SQL2. SQL3 support object extensions and are (partially?) implemented in Oracle8 and 9. 

7)What are the difference between DDL, DML and DCL commands?

DDL is Data Definition Language statements. Some examples:

• 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
• GRANT - gives user's access privileges to database
• REVOKE - withdraw access privileges given with the GRANT command

DML is Data Manipulation Language statements. Some examples:

• 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
• CALL - call a PL/SQL or Java subprogram
• EXPLAIN PLAN - explain access path to data
• LOCK TABLE - control concurrency

DCL is Data Control Language statements. Some examples:

• COMMIT - save work done
• SAVEPOINT - identify a point in a transaction to which you can later roll back
• ROLLBACK - restore database to original since the last COMMIT
• SET TRANSACTION - Change transaction options like what rollback segment to use 

8)How does one escape special characters when building SQL queries?

The LIKE keyword allows for string searches. The '_' wild card character is used to match exactly one character, '%' is used to match zero or more occurrences of any characters. These characters can be escaped in SQL. Example:

        SELECT name FROM emp WHERE id LIKE '%\_%' ESCAPE '\';

Use two quotes for every one displayed. Example:

        SELECT 'Franks''s Oracle site' FROM DUAL;

        SELECT 'A ''quoted'' word.' FROM DUAL;

        SELECT 'A ''''double quoted'''' word.' FROM DUAL;                           

9)How does one eliminate duplicates rows from a table?

Choose one of the following queries to identify or remove duplicate rows from a table leaving only unique records in the table:

Method 1:

   SQL> DELETE FROM table_name A WHERE ROWID > (

     2    SELECT min(rowid) FROM table_name B

     3    WHERE A.key_values = B.key_values);

Method 2:

   SQL> create table table_name2 as select distinct * from table_name1;

   SQL> drop table_name1;

   SQL> rename table_name2 to table_name1;

   SQL> -- Remember to recreate all indexes, constraints, triggers, etc on table... 

Method 3: (thanks to Dennis Gurnick)

   SQL> delete from my_table t1

   SQL> where  exists (select 'x' from my_table t2

   SQL>                 where t2.key_value1 = t1.key_value1

   SQL>                   and t2.key_value2 = t1.key_value2

   SQL>                   and t2.rowid      > t1.rowid);

Note: One can eliminate N^2 unnecessary operations by creating an index on the joined fields in the inner loop (no need to loop through the entire table on each pass by a record). This will speed-up the deletion process.

Note 2: If you are comparing NOT-NULL columns, use the NVL function. Remember that NULL is not equal to NULL. This should not be a problem as all key columns should be NOT NULL by definition.

10)How does one generate primary key values for a table?

Create your table with a NOT NULL column (say SEQNO). This column can now be populated with unique values:

        SQL> UPDATE table_name SET seqno = ROWNUM;

or use a sequences generator:

        SQL> CREATE SEQUENCE sequence_name START WITH 1 INCREMENT BY 1;

        SQL> UPDATE table_name SET seqno = sequence_name.NEXTVAL;

Finally, create a unique index on this column.