SQL Interview Qs

 

1.         Which command in SQL*Plus is used to save the query output to a file?    

            ANS: SPOOL

2.         How would you extract a SQL statement in the SQL buffer of SQL*Plus?

            ANS: Enter a SLASH (/)

3.         What is the default display length of the DATE Datatype column?

            ANS: Eight

4.         Which Clause in a query limits the rows selected?

            ANS: WHERE

5.         Which SQL*Plus command always overwrites a file?

            ANS: SPOOL

6.         Which single-row function could you use to return a specific portion of a character?

            ANS: SUBSTR

7.         Which of the following is not a Group Function? (AVG, COUNT, LEAST, STDDEV, VARIANCE)

            ANS: LEAST

8.         When using multiple tables to query information, in which Clause do you specify the table            names?

            ANS: FROM

9.         What are the special characters allowed in a table name? (&, #, @, $)

            ANS: #, $

10.       What is the default length of a CHAR and Number   Datatype column, if no length is       specified in the table definition?

            ANS: 1 and 9

11.       At a minimum, how many join conditions should be there in the WHERE Clause to avoid a          Cartesian join if there are Three Tables in the FROM Clause?

            ANS: 2

            There should be at least (n - 1) join conditions when joining (n) tables to avoid a    Cartesian join

12.       A view can only be used to query and update data, you cannot Insert into or delete from a            view. True or false

            ANS: FALSE

13.       Which option is not available in Oracle when modifying tables? (Add new                              Columns, Rename an Existing Column, Drop an Existing Column) Rename an Existing Column

            ANS: You cannot rename an Existing column using ALTER TABLE

14.       EDIT SAVE FILE SQL*PLUS Default:

            ANS: AFIEDT.buf

15.       Eliminate Duplicate Rows in a Table:

            ANS: ROWID (50).

16.       Eliminate Duplicate Rows in a BASE TABLE BLOCK:

            ANS: Create a SELECT DISTINCT VIEW on the base table

17        Set Transaction -To set a current transaction online offline

18        Optimization-Use of index (HINT)

19        Object Privilege - On a particular object- I/U/D/Exec

20        System Privilege -Entire collection object -C/A/D

           

21       Profile -To control system resources like memory, disk space, and CPU time.

22        Role -Collection of privileges.

23        Type of segment- Rollback, Temp, Data, Index

24        Snapshot-It's a read only table, to improve efficiency of query, which referred remote db, therefore reduce remote traffic.

25.       Describe Oracle database's physical and logical structure?

            Physical: Data files, Redo Log files, Control file.

            Logical: Tables, Views, Table spaces, etc.

26.Can you increase the size of a table space? How?

Yes, by adding datafiles to it.

27.Can you increase the size of datafiles? How?

No (for Oracle 7.0)

Yes (for Oracle 7.3 by using the Resize clause ----- Confirm!!).

28.What is the use of Control files?

Contains pointers to locations of various data files, redo log files, etc.

29.What is the use of Data Dictionary?

Used by Oracle to store information about various physical and logical Oracle structures e.g.

Tables, Tablespaces, datafiles, etc

30.What are the advantages of clusters?

Access time reduced for joins.

31.What are the disadvantages of clusters?

The time for Insert increases.

32.Can Long/Long RAW be clustered?

No.

33.Can null keys be entered in cluster index, normal index?

Yes.

34.Can Check constraint be used for self referential integrity? How?

Yes. In the CHECK condition for a column of a table, we can reference some other

 Column of the same table and thus enforce self referential integrity.

35.What are the min. extents allocated to a rollback extent?

Two

36.What are the states of a rollback segment?

      The various states of a rollback segment are:

      ONLINE, OFFLINE, PARTLY AVAILABLE, NEEDS RECOVERY and INVALID.

37.What is the difference between unique key and primary key?

     Unique key can be null; Primary key cannot be null.

38.An insert statement followed by a create table statement followed by rollback? Will the rows be inserted?

No.

39.Can you define multiple savepoints?

Yes.

40.Can you Rollback to any savepoint?

Yes.

41.What is the maximum no? Of columns a table can have?

      254.

42.How many rows will the following SQL return?

Select * from emp Where rownum < 10;

9 rows

43.How many rows will the following SQL return?

Select * from emp Where rownum = 10;

No rows

44.Which symbol precedes the path to the table in the remote database?

@

45.Are views automatically updated when base tables are updated?

Yes

46.Can a trigger written for a view?

No

47.A table has the following data: [5, Null, 10]. What will the average function return?

7.5

48.Is Sysdate a system variable or a system function?

System Function

49.Consider a sequence whose currval is 1 and gets incremented by 1 by using the nextval reference we get the next number 2. Suppose at this point we issue a rollback and again issue a nextval. What will the output be?

3

50.What is the result of the following SQL?

Select 1 from dual

UNION

Select 'A' from dual;

Error

51.Can database trigger written on synonym of a table and if it can be then what would be the effect if original table is accessed.

Yes, database trigger would fire.

52.Can you alter synonym of view or view?

No

53.Can you create index on view

No.

54.What is the difference between a view and a synonym?

Synonym is just a second name of table used for multiple link of database. View can be created with many tables, and with virtual columns and with conditions. But synonym can be on view.

55.What is the difference between alias and synonym?

Alias is temporary and used with one query. Synonym is permanent and not used as alias.

56.What is the effect of synonym and table name used in same Select statement?

Valid

57.What's the length of SQL integer?

32-bit length

58.What is the difference between foreign key and reference key?

Foreign key is the key i.e. attribute which refers to another table primary key.

Reference key is the primary key of table referred by another table.

59.Can dual table be deleted, dropped or altered or updated or inserted?

Yes

60.If content of dual is updated to some value computation takes place or not?

Yes

61.If any other table same as dual were created would it act similar to dual?

Yes

62.For which relational operators in where clause, index is not used?

<>, Like '%...' is NOT functions, field +constant, field || ''

63.Assume that there are multiple databases running on one machine. How can you switch from one to another?

Changing the ORACLE_SID

64.If you insert a row in a table, then create another table and then say Rollback. In this case will the row be inserted?

Yes. Because Create table is a DDL which commits automatically as soon as it is executed. The DDL commits the transaction even if the create statement fails internally (e.g. table already exists error) and not syntactically.

65.What are the various types of queries?

Normal Queries

Sub Queries

Co-related queries

Nested queries

Compound queries

 

68 Varrays:

They allow us to associate a single identifier with an entire collection. This association let you manipulate the collection as a whole and reference individual elements easily.

69.Create database link <link name> Test_link

Connect to <user name > apps

Identified by <passwd> fnd

Using <connect string> fintst

70 How do you tell the difference between a regular order and a return order?

      In so_headers you identify by order_category which will be RMA for

     Returns and R for Regular