Sql interview questions with answers

(1)    How do I list all of my tables?

Ans: SQL> select * from cat;

(2)    Create one table from another table without copying the data from the first table.?

Ans: SQL> create table dept_info( dept_id number(10),

  2  dept_name varchar(25),

  3  constraint pk_dept_id primary key(dept_id)

  4  );

SQL> insert into dept_info values(1,'Account');

SQL> insert into dept_info values(2,'HR');

SQL> insert into dept_info values(3,'Payroll');

.SQL> select * from dept_info;

 

(3)    How do I select from different user’s tables?

(4)    What is SQL?

Ans:Sql is Structured query language:

Query:We ask question to get response

Database understands only QUERYS and RDBMS is widely used in system n/w.

According to ANSI (American National Standards Institute), it is the standard language for relational database management systems.

(5)    What is a function in oracle?

Ans:Function is a subprogram that returns a single value.we must declare and define before we invoke it

Functions are very powerfull feature of sql & can be used to (i) perform calculation on data and modifying individual data items.

Types of Functions:

(i)SINGLE ROW FUNCTION

(ii)MULTIPLE ROW FUNCTION

(iii)Nesting functions

(iv)numeric functions

(v)character or text functions

(vi)conversion functions

(vii)date functions

(6)    What is the default format of date in Oracle? How can I change my default date format?

Ans:The default date format is DD-MON-YY

SQL>select sysdate,systimestamp;

(7)

       1)Rollback:

         ROLLBACK command execute at the end of current transaction and undo/undone any changes made since the begin transaction.

       Sql>rollback;

       2)Savepoint:

         SAVEPOINT command save the current point with the unique name in the processing of a transaction.

Sql>savepoint a;

3) COMMIT:

ANS: COMMIT command to make changes permanent save to a database during the current transaction.

4) Truncate:

Ans:Removes all rows from a table and rleases the storage space used by tat table.

Sql>droptable emp1;

5) Delete:

Ans: The DELETE statement is used to delete rows in a table.

Sql>delete emp1;

6) Drop:

Ans:All data and structure in the table is deleted and any pending transaction are commited and all indexs are droped

      You can’t rollback the drop table.

Sql>drop column commit;

7) PK:Primary key

Ans:It represents (or) identify each row uniquely in a table,

      Only empno is considered as primary key.

      Pk=unique+notnull

      Ck=eligible to become pk

      Ak=ck-pk

8) ROUND:

Ans:Rounds value to specified decimal

      Round(45.926)à46

9)TRUNC:

Ans:Truncates value to specified decimal

      Trunc(45.926)à45

10) Alternate Key:

ANs:Eligible to become to pk but not chosen as pk.

      AK=CK-PK

11) Candidate Key:

Ans:It is a relational model of data base and it is eligible to become PK.

12)Composite key:

Ans: A composite key, in the context of relational databases,

It is a combination of two or more columns in a table that can be used to uniquely identify each row in the table

13)Triggers:

Ans:Triggers provide a way of executing PL/SQL code on the occurrence of specific database

      It invokes many events in table like INSERT,UPDATE,DELETE operations.

14) Equi-Join:

Ans:If we are using “=” operator in join condition is called equijoin.

      Ex:emp.d ptno=dept.deptno

15)OUTER-JOIN:

Ans:It displays both matched and non matching

      (i)full outer,left outer,Right outer.

16) What is a view? What are its advantages?

Ans: the ability to see something or to be seen from a particular place.

        Advantages of views::

         View the data without storing the data into the object.

        2. Restict the view of a table i.e. can hide some of columns in the tables.

        Disadvatages:

        1. Can not use DML operations on this.

        2. When table is dropped view becomes inactive.. it depends on the table objects.

17) What is a synonym?

Ans: synonym are alternate name for tables to view other object in database

Ex:create synonym anyname for scott.emp.

18) What is PL/SQL?

Ans:Procedural language /Structured Query Language.

      It works only on oracle RDBMS

      It is the procedural execution to sql with design features of programming language.

      Data manipulation and Query statements of sql

18) What is an Index? Why it is useful?

Ans: The CREATE INDEX statement is used to create indexes in tables.

Indexes allow the database application to find data fast; without reading the whole table.

19) Mention some features of Oracle 9i database.?

Ans:Varchar 2000bytes

      Varchar2-4000bytes

      No recyclebin

20) Mention some features of Oracle 10g database.

Ans:varchar&varchar2}à4000bytes

      Recylebin is possible.

21) What is the meaning of “i” and “g” in 9i & 10g respectively?

Ans: i means Internet support
        g means Grid technology

22) What is the purpose of joining the tables? Explain different types of Joins.

Ans: An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.

      The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN return all rows from multiple tables where the join condition is met.

Types of joins:

Inner join

Outerjoin

Equi join

Non-equijoin

Self join

23) What are the data types available in Oracle

Ans:Character data type

      Numeric datatype

      Date dattype

      Lob dattype