8/16/18

SQL Interview Questions and Answers


Q1. How to delete duplicate records?
  • Using rowid
——————-
SQL > delete from emp where rowid not in (select max (rowid) from emp group by empno);
This technique can be applied to almost scenarios. Group by operation should be on
The columns which identify the duplicates.
  • using self-join
——————-
SQL > delete from emp e1 where rowid not in (select max (rowid) from emp e2 where e1.empno = e2.empno );
  • Using row_number()
———————–
SQL > delete from emp where rowid in (select rid from ( select rowid rid, row_number() over(partition by empno order by empno) rn from emp )where rn > 1);
This is another efficient way to delete duplicates
  • Using dense_rank ( )
———————-
SQL > delete from emp where rowid in (select rid from (select rowid rid, dense_rank() over(partition by empno order by rowid) rn from emp ) where rn > 1 );
Here you can use both rank ( ) and dens_rank()
since both will give unique records when order by rowid.
  • Using group by
——————-
SQL > delete from emp where empno,empname,salary) in ( select max(empno),empname,salary from emp group by empname,salary );
This technique is only applicable in few scenarios.
Q2. What is Materialized View in database?
Materialized views are also logical view of our data driven by select query but the result of the query will get stored in the table or disk, also definition of the query will also store in the database .When we see the performance of Materialized view it is better than normal View because the data of materialized view will stored in table and table may be indexed so faster for joining also joining is done at the time of materialized views refresh time so no need to every time fire join statement as in case of view.
Q3. Difference between View vs Materialized View in database
1) In Views query result is not stored in the disk or database but Materialized view allow to store query result in disk or table. When we create view using any table, rowid of view is same as original table but in case of Materialized view rowid is different. In case of View we always get latest data but in case of Materialized view. we need to refresh the view for getting latest data. Performance of View is less than Materialized view. In case of view its only the logical view of table no separate copy of table but in case of Materialized view we get physically separate copy of table. In case of Materialized view we need extra trigger or some automatic method so that we can keep MV refreshed, this is not required for views in database.
Q4. What are oracle defined or pre-defined Exceptions
-to_many_rows
-zero_divid_errors
-no_data_found
-invalid_cursor
-dup_value_on_index
Q5. Display the 10th record of the emp table? ( without using row id)
SELECT *
FROM EMP
WHERE ROWNUM <= 10
MINUS
SELECT *
FROM EMP
WHERE ROWNUM < 10;
Q6. Delete the 10th record of EMP table?
DELETE
FROM EMP
WHERE ENAME= (SELECT ENAME
FROM EMP
WHERE ROWNUM<=10
MINUS SELECT ENAME
FROM EMP
WHERE ROWNUM<10 span="">
Q7. What are Schema Objects?
Schema objects is Structures of database’s data.  tables, views, sequences, synonyms, indexes are schema objects.
Q8. What is Data?     
Data is collection of information or collection of attribute, example Ravi it’s a data. The information of Ravi is height, weight, education, age, etc..
Q9. What is a Table?
A table is collection of data to storage in any database.  Table data is stored in rows and columns.
Q10. What is a View?
A View is not a original table It’s called virtual table. It’s based on original table
There are three type of tables
  1. Simple view
  2. Complex view
  3. Inline view
Q11. What is an Index?
An index is direct access to rows, used to increase the performance of data retrieval.  Index can be created on one or more columns of a table.
Q12. What is the difference between UNIQUE or PRIMARY KEY Constraint?
UNIQUE constraints allowed NULLs values. PRIMARY KEY constraints cannot contain NULLs.
Q13. What is difference between TRUNCATE and DELETE?
Truncate is after deleting entire table, Cannot be rolled back options. Delete allows the single row and multiple rows. Deleted records can be rolled back or committed
Q14.What is a join? Explain the different types of joins?
Join is retrieves data from related columns or rows from multiple tables.
  1. Cross Join
  2. Inner join
  3. equi join
  4. non equi join
  5. Outer join
  6. left outer join
  7. right outer join
  8. full outer join
  9. Self join
Q15. What is a Subquery?
Subquery is a query  embedded with another query called subquery its return values from mainquery
Q16. What are Difference between SUBSTR and INSTR?
INSTR(str1, str2 [,starting,[howmuch]])
INSTR is returns the position of the character.
SUBSTR(stri1, starting, howmuch)
SUBSTR returns a single or multiple character string
Q17. What is UNION, MINUS, UNION ALL, INTERSECT?
UNION returns all unique rows .
UNION ALL returns all rows included all duplicates.
INTERSECT returns all common row and unique rows selected by both queries.
MINUS returns all unique rows selected by the first query but not by the second.
Q18. What is ROWID?
  ROWID is a pseudo column attached to each row of a table. Its create table database assign the rowed in each rows
Q19. What is PRIMARY KEY, UNIQUE KEY, FOREIGN KEY?
Primary key is a column or a combination of columns of a table which cab be used to uniquely identify a row of the table. PRIMARY KEY cannot be null.
UNIQUE KEY is a column or a combination of columns of a table, which can be used to identify a row of the table uniquely. UNIQUE KEY can be null.
FOREIGN KEY is a column or a combination of column which is the primary key or unique key of the referenced table. FOREIGN KEY can be null.
Q20. What is ON DELETE CASCADE?
Database maintains referential integrity by automatically removing dependent foreign key values if a referenced primary or unique key value is removed.
Q21. What will be returned from NVL2 (-22,0)?
  • 1
  • 22
  • 0
  • NULL
Q22. Which one is  single row subquery operator except one
  • >=
  • <=
  • in
Q23, An operator is used to get and display the common  records
Ans Intersect
Q54. What is database?
A database is a collection of data. Representing some aspect of real world.
Q25. How to  check the leap year
select decode(mod(2004,4),0,’leap year’, ‘not a leap year’) from dual
Q26Differences between DATE and TIMESTAMP in Oracle
Date is used to store date and time values including month, day, year, century, hours, minutes and seconds. It fails to provide granularity and order of execution when finding difference between 2 instances (events) having a difference of less than a second between them. Time Stamp datatype stores everything that Date stores and additionally stores fractional seconds.
Q27. How many columns can a table have?
Maximum 254  in single table
Q28. When to create indexes?
To be created when table is queried for less in the table rows and need fastest Retrival
Q30. What is the difference between foreign key and reference key ?
Foreign key is which refers to another table primary key. Reference key is the primary key of table referred by another table.
Q31.How to  Get the database file names from Oracle:
SELECT NAME FROM SYS.V_$DATAFILE;
SELECT NAME FROM SYS.V_$CONTROLFILE;
SELECT MEMBER FROM SYS.V_$LOGFILE;
Q32. What is SQL*Loader?
SQL*Loader is a loader utility used for moving data from external files into the Oracle database in bulk. It is used for high-performance data loads.
Q33. What are the original Export and Import Utilities?
SQL*Loader, External Tables
Q34. created both procedures. You new invoke the insert_location procedure using the followingcommand:
EXECUTE insert_location (19, .San Francisco .) What is the result in this EXECUTE command?
  1. The locations, departments, and employees tables are empty.
  2. The departments table has one row. The locations and the employees tables are empty.
  3. The location table has one row. The departments and the employees tables are empty.
  4. The locations table and the departments table both have one row. The employees table is empty.
Answer(s) D
Q35). What do you mean by Correlated subquery?
Subqueries, or nested queries, are used to bring back a set of rows to be used by the parent query. Depending on how the subquery is written, it can be executed once for the parent query or it can be executed once for each row returned by the parent query. If the subquery is executed for each row of the parent, this is called a correlated subquery.
Q36) Display the depart numbers with more than three employees in each dept.
SQL>select deptno, count(deptno) from emp group by deptno having
count(*)>3;
Q37) Display the various jobs along with a total number of employees in each job?
The output should contain only those jobs with more than three employees.
SQL>select job,count(empno) from emp group by job having count(job)>3
Q38) Display the names of clerks who earn a salary more than the lowest
salary of any salesman.
SQL>select ename from emp where job=’CLERK’ and sal>(select min(sal)
from emp
where job=’SALESMAN’);
Q40) Display the names of employees from department number 10 with a salary greater than that of any employee working in other departments?
SQL>select ename from emp where deptno=10 and sal>any(select sal from
emp where deptno not in 10).
Q41. Can one rename a column in a table? No, this is listed as Enhancement Request 163519.Workarounds:
  1. Rename t1 to t1_base;
Create view t1 as select * from t1_base;
  1. create table t2 as select * from t1;
Drop table t1;
Rename t2 to t1;
Q42.To find The Max Salary without MAX Function.
  1. SELECT DISTINCT SAL FROM EMP1 WHERE SAL NOT IN (SELECT SAL FROM EMP1 WHERE SAL < ANY (SELECT SAL FROM EMP1))
2.SELECT SAL FROM EMP WHERE SAL >= ALL (SELECT SAL FROM EMP)

Q43. Alternate for DESC. SELECT COLUMN_NAME NAME, DECODE (NULLABLE,’N’,’NOT NULL’,’Y’,’ ‘)
“NULL”, CONCAT (DATA_TYPE, DATA_LENGTH) TYPE FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = ‘DEPT’
Q44.SQL> Example for startwith, connect by and prior SELECT ENAME, JOB, LEVEL, EMPNO, MGR FROM EMP111 CONNECT BY PRIOR EMPNO=MGR START WITH ENAME = ‘RAJA’

SELECT EMPNO, LPAD (‘ ‘, 6*(LEVEL – 1)) || ENAME “EMPLOYEE NAME” FROM EMP START
WITH ENAME=’KING’ CONNECT BY PRIOR EMPNO = MGR
Q45. What is ER Diagram?
Entity – Relational diagram
Could be (a) one to one
(b) One to many (crowfoot style)
(c) Many to many
Q46. Describe Referential Integrity?
A rule defined on a column (or set of columns) in one table that allows the insert or update of a row only if the value for the column or set of columns matches a value in a column of a related table.  It also specifies the type of data manipulation allowed on referenced data and the action to be performed on dependent data as a result of any action on referenced data.
Q46.What are the Referential actions supported by FOREIGN KEY integrity constraint?
UPDATE and DELETE Restrict – A referential integrity rule that disallows the update or deletion of referenced data.
DELETE CASCADE        –  when a referenced row is deleted, all associated dependent rows are deleted.
Q47.What is self-referential integrity constraint?
If a foreign key reference a parent key of the same table it is called self-referential integrity constraint.
Q48.What are the limitations of a CHECK constraint?
The condition must be a Boolean expression evaluated using the values in the row being inserted or updated, and cannot contain subqueries, sequences, the SYSDATE, UID, USER, or USERENV SQL functions, or the pseudocolumns LEVEL or ROWNUM.
Q49. Which script displays ’01-JAN-02′ when the ENROLL_DATE value is ’01-JUL-01′?A. SELECT ROUND(enroll_date, ‘DAY’) FROM student;
B. SELECT ROUND(enroll_date, ‘YEAR’) FROM student;
C. SELECT ROUND(enroll_date, ‘MONTH’) FROM student;
D. SELECT ROUND(TO_CHAR(enroll_date, ‘YYYY’)) FROM student;
Q50.Which statement is true?
A. The statement will achieve the desired results.
B. The statement will execute, but will NOT enable the PRIMARY KEY constraint.
C. The statement will execute, but will NOT verify that values in the ID column do NOT violate the constraint.
D. The statement will return a syntax error.
Q51. What is the difference between a view and a synonym?
The synonym is another name of the table used for multiple links of a database. A view is created with many tables, and virtual columns and with conditions.
Q52. Display the names of employees who earn a salary more than that of Jones or that of salary greater than that of scott.?
SQL>select ename,sal from emp where sal>(select sal from emp where ename=’JONES’)and sal> (select sal from emp where ename=’SCOTT’);

0 comments:

Post a Comment