25 SQL Interview Questions and Answer - Structured Query Language

SQL stands for

a) Structured Query Language
b) Simple Query Language
c) Standard Query Language
d) Secondary Query Language

ANSWER: a) Structured Query Language

SQL was initially developed by

a) Donald D. Chamberlin and Dr. E.F. Codd
b) Dr. E.F. Codd and Raymond F. Boyce
c) Donald D. Chamberlin and Raymond F. Boyce
d) Michael Widenius

ANSWER: c) Donald D. Chamberlin and Raymond F. Boyce

SQL was designed with the purpose of managing data held in__

a) Relational Database Management System
b) Object Oriented Database Management System
c) Object Relational Database Management System
d) File system

ANSWER: a) Relational Database Management System

Which is the correct syntax to retrieve all rows from the table?

a) select * from table_name;
b) select from table_name;
c) select column_name from table_name;
d) select column_name , from table_name;

ANSWER: a) select * from table_name;

What is the difference between delete and truncate command of SQL?

a) DROP command removes a table from the database & TRUNCATE removes all rows from a table
b) TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.& Delete activates a trigger because the operation are logged individually
c) TRUNCATE TABLE always locks the table and page but not each row & DELETE statement is executed using a row lock, each row in the table is locked for deletion
d) All of the above

ANSWER: d) All of the above

Restrictions on Dropping Tablespace

a) You cannot drop the SYSTEM tablespace.
b) You cannot drop a tablespace that contains a domain index or any objects created by a domain index.
c) You cannot drop an undo tablespace if it is being used by any instance or if it contains any undo data needed to roll back uncommitted transactions.
d) All of the above

ANSWER: d) All of the above

If you want to add new data in a database which command will you use

a) Insert
b) Add
c) Update
d) Select

ANSWER: a) Insert

Suppose your assistant, named Jene has not been previously authorized to add data about new customers into the database, which of the following statement can be used to give her that permission

a) Grant Insert
b) Grant Update
c) Revoke Insert
d) Revoke All

ANSWER: a) Grant Insert

SQL select statement is used to

a) Retrieve data from database
b) Update data in database
c) Delete data from database
d) Modify data in database

ANSWER: a) Retrieve data from database

To control access to the database which SQL statement/s used

a) Grant
b) Revoke
c) Both a & b
d) Deny

ANSWER: c) Both a & b

Select the non-aggregate function from the following

a) Avg( )
b) Min( )
c) Max( )
d) Round( )\

ANSWER: d) Round( )

Select the non-scalar function from the following

a) UCASE()
b) LCASE()
c) FORMAT()
d) FIRST()

ANSWER: d) FIRST()

If the primary key is not included in the query result , duplicate rows can occur in result set then how you can eliminate the duplicate rows of query result.

a) By using Distinct statement
b) By using Unique statement
c) Neither a nor b
d) By using where clause

ANSWER: a) By using Distinct statement

What is true about FLOOR(n) function?

A) Returns smallest integer greater than or equal to n
B) Returns largest integer less than or equal to n
C) It is used with numeric data
D) It operates on character data

a) Only A
b) Only B
c) A , C & D
d) B & C

ANSWER: d) B & C

Pseudo-column ________ returns a level of row in a tree-structured query.

a) ROWID
b) LEVEL
c) ROWNUM
d) ROWSCN

ANSWER: b) LEVEL

Pseudo-column LEVEL can be used in ____statement where ______ is used.

a) Select, group by
b) Select , connect by
c) Update, order by
d) Select , group by

ANSWER: b) Select , connect by

____ column displays the location of row in a database.

a) ROWID
b) ROWNUM
c) ROWSCN
d) UID

ANSWER: a) ROWID

To use the result of certain query repeatedly which clause will you use?

a) Where
b) With
c) Having
d) None of the above

ANSWER: b) With

Which command will you use to delete entire table from database?

a) Delete
b) Drop
c) Truncate
d) None of the above

ANSWER: b) Drop

SQL ______performs a JOIN against equality or matching column(s) values of the associated tables.

a) Equi join
b) Inner Join
c) Self-Join
d) Cross Join

ANSWER: a) Equi join

Exists clause is used for

a) Testing whether a given set is empty or not
b) Testing whether given set is valid
c) Testing whether a given set is invalid
d) Testing whether a given set is exists

ANSWER: a) Testing whether a given set is empty or not.

Which of the following queries will correctly show the tables owned by the user?

a) SELECT table_name from system_user_tables;
b) SELECT table_name from user_objects;
c) SELECT table_name from user_catalog;
d) SELECT table_name from user_tables;

ANSWER: d) SELECT table_name from user_tables;

Data files are logically grouped together into an oracle logical structure called a

a) Tablespace
b) Table
c) Database
d) Indexes

ANSWER: a) Tablespace

To create a table name Customer having fields Cust-name, Cust_address from the table Employee and Customer table should not be populated with any record from Employee table which of the following query will be correct?

a) Create table Customer (Cust-name, Cust_address) As Select emp_name, emp_address from Employee;
b) Create table Customer (Cust-name, Cust_address) As Select emp_name, emp_address from Employee 1=1;
c) Create table Customer (Cust-name, Cust_address) As Select emp_name, emp_address from Employee where 1=2;
d) Create table Customer (Cust-name, Cust_address) As Select emp_name, emp_address from Employee where a=b;

ANSWER: c) Create table Customer (Cust-name, Cust_address) As Select emp_name, emp_address from Employee where 1=2;

Which of the following tasks cannot be performed when using Alter Table clause?

A) Change the name of the table
B) Change the name of the column
C) Decrease the size of a column if table data exists

a) A & B
b) A,B, & C
c) Only C
d) B & C

ANSWER: b) A,B, & C

The _______ command is used to change or modify data values in a table

a) Update
b) Modify
c) Rename
d) Describe

ANSWER: a) Update

Up to how many columns can be combined to form a composite primary key for a table?

a) 16
b) 8
c) 18
d) 14

ANSWER: a) 16

Select from the following option which not true about primary key

a) Primary key can be Long & long Raw data type
b) Unique index is created automatically if there is a Primary Key
c) Primary key will not allow Null values
d) Primary key will not allow duplicate values

ANSWER: a) Primary key can be Long & long Raw data type

To compare one value with more than one or list of values then which of the following operator will fulfil the need?

a) Like
b) IN
c) AND
d) Between

ANSWER: b) IN

Which of the following query is correctly give the user name of the currently logged in user?

a) SELECT USERENV FROM DUAL;
b) SELECT COALESCE FROM DUAL;
c) SELECT USER FROM DUAL;
d) SELECT USERENV FROM TABLE_NAME;

ANSWER: c) SELECT USER FROM DUAL;

The ___ operator is used to calculate aggregates and super aggregates for expressions within a ________.

a) ROLLUP, GROUP BY
b) ROLLUP, ORDER BY
c) CUBE , GROUP BY
d) ROLLUP,CUBE

ANSWER: a) ROLLUP, GROUP BY

________ allows grantee to in turn grant object privileges to other users.

a) With grant option
b) Grant
c) Revoke
d) Grant All

ANSWER: a) With grant option

Grant all on Customer to Reeta with grant option, what is the significance of ‘with grant option’ in this query?

a) Give the user “Reeta” privileges to view only data on table Customer along with an option to further grant permissions on the Customer table to other users.
b) Give the user “Reeta” all data manipulation privileges on table Customer.
c) Give the user “Reeta” all data manipulation privileges on table Customer along with an option to further grant permissions on the Customer table to other users.
d) Give the user “Reeta” all data manipulation privileges on table Customer along with an option to not further grant permissions on the Customer table to other users.

ANSWER: c) Give the user “Reeta” all data manipulation privileges on table Customer along with an option to further grant permissions on the Customer table to other users.

A View is mapped to __________ statement.

a) Update
b) Alter
c) Create
d) Select

ANSWER: d) Select

What are the prerequisite for a View to be updateable?

a) Views defined from a single table. If user wants to Insert records with the help of a view, then the primary key column and all the Not Null columns must be included in the view
b) The user can Update, Delete records with the help of a view even if the primary key column and Not Null column(s) are executed from the view definition
c) Both a & b
d) None of the above

ANSWER: c) Both a & b

Which of the following query will correctly create the view of Employee table having fields fname,lname,dept?

a) Create View emp_v In select fname,lname,dept from Employee;
b) Create View emp_v As select fname,lname,dept from Employee;
c) Create View emp_v like select fname,lname,dept from Employee;
d) Create View emp_v As select , , fname,lname,dept , ,from Employee;

ANSWER: b) Create View emp_v As select fname,lname,dept from Employee;

Which of the following column is not a part of USER_CONSTRAINTS?

a) OWNER
b) TABLE_NAME
c) SEARCH_CONDITION\
d) DB_DOMAIN

ANSWER: d) DB_DOMAIN

_______ used to sort the data in the table

a) Order by clause
b) Group by clause
c) Aggregate functions
d) Sequence

ANSWER: a) Order by clause

______ clause can be used to find unique values in situations to which ____ apply.

a) HAVING, DISTINCT does not
b) HAVING, DISTINCT
c) GROUP BY, DISTINCT
d) HAVING, GROUP BY

ANSWER: a) HAVING, DISTINCT does not.

Which of the following is not a valid SQL data type?

a) NUMBER
b) DATE
c) LONG
d) FRACTION

ANSWER: d) FRACTION

______ condition can’t contain sub queries or sequence.

a) Check
b) Unique
c) References
d) Index

ANSWER: a) Check

What is true about join?

a) You can join a maximum of two tables
b) You can join a maximum of two columns through
C) You can join two or more tables
d) None of the above

ANSWER: You can join two or more tables.

Joining a table to itself is referred to as __________.

a) Self-join
b) Cross-join
c) Outer Join
d) Full Outer Join

ANSWER: a) Self-join

A sub query is a form of an SQL statement that appears ______ another SQL statement.

a) At the start of
b) Inside
c) Outside
d) After

ANSWER: b) Inside

The____ data types are used to store binary data.

a) Raw
b) LONG
c) bfile
d) rowid

ANSWER: a) Raw

A____ level constraint must be applied if data constraint spans across multiple columns in a table.

a) Table
b) Row
c) Column
d) Database

ANSWER: a) Table

______ constraint can only applied at column level.

a) NOT NULL
b) CHECK
c) UNIQUE
d) PRIAMRY KEY

ANSWER: a) NOT NULL

EXTRACT() function returns ___________.

a) a value extracted from a date or an interval value
b) number of columns in a table
c) number of tables in a database
d) number of rows in a table

ANSWER: a) a value extracted from a date or an interval value

Which of the following is a string function?

a) UPPER( )
b) FLOOR( )
c) LEAST( )
d) ABS( )

ANSWER: a) UPPER( )

VSIZE( ) function returns ___________.

a) number of bytes in the internal representation of an expression
b) number of rows where expr is not null
c) largest integer value
d) returns a Unicode string

ANSWER: a) number of bytes in the internal representation of an expression.