SQL and DBMS Interview Questions with Answers

SQL & DBMS Interview Questions

Certiology’s free SQL & DBMS interview questions with answers will help you to prepare for SQL and DBMS interview to land your database management job.

S.#	Question	Expected Answer
1	What are the most important DDL statements in SQL?	DROP INDEX – deletes an index ALTER TABLE – alters (changes) a db table CREATE TABLE – creates a new db table CREATE INDEX – creates an index (search key) DROP TABLE – deletes a db table
2	Which operators are applicable in SELECT statement?	<> or != Not equal = Equal >= Greater than or equal > Greater than <= Less than or equal BETWEEN: Between an inclusive range LIKE: Search for a pattern
3	What is the Syntax for SELECT Statement	SELECT column_name(s) FROM table_name
4	What is the purpose of SELECT INTO Statement?	It is often used to archiving or creating backup of tables
5	What would be the syntax for SELECT INTO Statement that is used to create backup copy of a table	SELECT col_name(s) INTO new_table [IN ext_db] FROM src_db WHERE col_name operator value
6	What is the syntax for INSERT INTO?	INSERT INTO tbl_name VALUES (val1, val2, …)
7	What is the Syntax for UPDATE?	UPDATE tbl_name SET col_name = new_val WHERE col_name = old_val
8	What is the Syntax for DELETE?	DELETE FROM tbl_name WHERE col_name = value Delete All Rows: DELETE FROM tbl_name DELETE * FROM tbl_name
9	How to Sort Records in Ascending and Descending orders?	SELECT * FROM tbl_name ORDER BY col_name ASC SELECT * FROM tbl_name ORDER BY col_name DESC
10	How to use BETWEEN and AND operators within a SQL statement?	SELECT * FROM tbl_name WHERE col_name BETWEEN val1 AND val2
11	How a CASCADE CONSTRAINTS works?	When CASCADE CONSTRAINTS clause is used along with a DROP instruction, a parent table may be dropped even when it has a child table exists.
12	What is the reason for following compilation error in the following command?	DROP TABLE & tbl_name; The table name starts with an ‘&’ sign. Variable name should start with an alphabet.
13	Which system tables handle information related to privileges granted and privileges obtained?	USER_TAB_PRIVS_RECD USER_TAB_PRIVS_MADE
14	Which system table handle information related to constraints on all the tables created?	USER_CONSTRAINTS
15	How the TRUNCATE and DELETE commands are different from each other?	TRUNCATE command delete rows fast but doesn’t keep the logs whereas delete command is slow but keep log of every row delted.
16	What is the syntax for Not Equal to Operator?	1. !=, <>
17	Are EXISTS, SOME and ANY, SQL operators?	True
18	What is the function of following query? SELECT SAL+ NVL(COMM,0) FROM EMP;	The output would be the total salary of employees including the commission. The null values in the commission will be replaced by 0 before added in the salary.
19	The benefit of specifying WITH GRANT OPTION in the GRANT command is:	Privileged user further grants his/her privileges to any other user.
20	Which operator is used to execute the contents of a specified file?	@ or START
21	Which function displays the SQL command in the SQL buffer prior to execution?	RUN
22	Which command is used to revert the privileges offered through GRANT command?	REVOKE.
23	The output of following query would be: SELECT DECODE(TRANSLATE (‘B’, ‘1234567890’, ‘2222222222’), ‘2’, ‘YES’, ‘NO’ );	NO. The query verifies whether the given string is numeric or not
24	Which date function identifies the difference between two dates?	MONTHS_BETWEEN
25	Which operator is used for pattern matching?	LIKE.
26	What is the purpose of DROP option in ALTER TABLE query?	It is used to remove any constraints specified on the table.
27	Which operator is used for checking of non-existence of data in any column?	IS NULL operator.
28	What privileges that can be granted on a table by the owner to other users?	Select, Alter, Insert, Delete, Update, References, Index, Execute, All.
29	Which function is used to identify the largest integer which is less than or equal to the specified value?	FLOOR
30	The subset of SQL commands that is used to manipulate the structure of Oracle Database is?	Data Definition Language (DDL).
31	What is the function of DESC keyword in SQL?	DESC keyword is referred to Descending order. The purpose is to display records in descending order based on the given col_name
32	What would be the syntax to create a table by using the structure of another table?	CREATE TABLE tbl_name AS SELECT * FROM existing_tbl WHERE col_name = val;
33	Will the output of TRUNCATE TABLE tbl_name; query differ from the output of query DELETE FROM tbl_name;?	The output would be same. All rows will be deleted from the target table.
34	Which wildcards are used for pattern matching?	_ for single character % for multi-character
35	What is the parameter substitution symbol used with INSERT INTO command?	&
36	What is an SQL injection?	SQL Injection is the SQL exploitation using form data. It contains an SQL escape sequence and injects a new SQL query.
37	What is a join?	Join retrieves related columns or rows from multiple tables with in a database.
38	What is the sub-query?	Sub-query is a query whose return values are used in filtering conditions of the main query.
39	What is meant by correlated sub-query?	Correlated sub-query is a query that has a reference to main query.
40	What is meant by CONNECT BY PRIOR?	It is used for hierarchical retrieval of rows
41	What is the difference between SUBSTR and INSTR?	INSTR function returns the position of the n^th occurrence of string_2 in string_1 on a specified starting point. SUBSTR function returns a character string of size n in string_1, starting from n^th position of string_1.
42	Explain INTERSECT, UNION, UNION ALL, MINUS?	INTERSECT: returns distinct rows selected by both queries. UNION – returns distinct rows selected by either queries UNION ALL – returns rows selected by either queries, including all duplicates. MINUS: returns distinct rows selected by the 1^st query but not by the 2^nd query.
43	Explain ROWID?	ROWID is a pseudo column attached to each row. It is 18 chars in length having block_no and row_number as its components.
44	How to access a row in a table in a fastest manner?	By using ROWID or by using CONSTRAINTS
45	What is meant by integrity constraint?	Integrity constraint restricts values to a column
46	What is meant by referential integrity constraint?	It is meant for maintaining data integrity through set rules that restrict values of one or more columns of based on primary key or unique key of the referenced table.
47	What is the purpose of SAVEPOINTS?	SAVEPOINTS are meant to subdivide a transaction in small parts. It allows roll-back function of a transaction.
48	What is meant by ON DELETE CASCADE?	An ON DELETE CASCADE instructs Oracle to maintain referential integrity by auto removal of dependent foreign key values when a referred primary key value is removed.
49	What data types are generally allowed in a table?	INT, LONG, NUMBER, CHAR, VARCHAR, DATE, RAW, and BOOLEAN.
50	What is the difference between CHAR datatype and VARCHAR datatype?	CHAR fills unused space with padding where as VARCHAR only occupies the required space and leave the unused space.

SQL & DBMS Interview Questions

S.#	Question	Expected Answer
1	Is it possible to add multiple LONG columns in a table?	No. Only 1 LONG column can be used in one table
2	Can a LONG column be used in WHERE clause or in ORDER BY clause?	No. It is not possible to use it in either
3	How is it possible to modify the datatype of a column?	Column must be empty in order to modify its datatype.
4	How to add a column with NOT NULL constraint	Table must be empty in order to add a column with NOT NULL constraint
5	Where does the integrity constraints store in a data dictionary?	The integrity constraints store in USER_CONSTRAINTS
6	How to activate or deactivate an integrity constraints?	To enable integrity constraint, we use ALTER TABLE ENABLE CONSTRAINT To disable integrity constraint, we use ALTER TABLE DISABLE CONSTRAINT
7	If a constraint of Unique Key is applied on DATE, will this constraint validate the rows inserted by SYSDATE?	No. It will not. SYSDATE format also contains time.
8	What is meant by a database link?	It is a named path through which a remote database may be accessed.
9	What is meant by CYCLE and NO CYCLE in a Sequence?	CYCLE specifies the sequence as an on-going process that keep on generating values after reaching either max or min values. NO CYCLE specifies that the sequence will terminate after reaching its maximum or minimum value.
10	What are the benefits of using VIEW?	It protects columns of a table from users. It hides the complex queries and calculations, and only shows the results.
11	If there is any manipulation on a single table based view, will these changes be reflected on the actual table?	Yes. The changes will be reflected.
12	How to restrict output to display?	By setting the option TERMOUT to TERM.
13	What is basic method of preventing oracle from generating verbose after SQL execution?	By setting FEEDBACK and VERIFY to OFF mode.
14	How to generate a file output from SQL?	By using SPOOL command
15	How a dba will use a referential datatype declaration on a variable? The variable name is EMP_LNAME, and the corresponding table and column is EMP, and LNAME, respectively.	Use emp.lname%type. Use emp.lname%rowtype. Look up the datatype for LNAME column on EMP table and use that. Declare it as a LONG.
16	Which of the following are not implicit cursor attributes? %found %too_many_rows %notfound %rowcount %rowtype	%too_many_rows %rowtype
17	If left un-terminated, which of the following will result in an infinite loop? LOOP END LOOP IF-THEN EXIT	LOOP
18	To open a cursor FOR loop, which command will be used?	None, cursor for loops handle cursor opening implicitly.
19	What is the outcome of rows using FETCH statement?	It loads the current row values into variables
20	NEW and OLD qualifiers are referenced for which of the timing triggers?	Oracle Forms trigger
21	Which procedure can be used to create a customized error message in Oracle?	RAISE_APPLICATION_ERROR
22	The trigger CHECK_THEATER of table THEATER is disabled. Which command will be used to enable this trigger?	ALTER TABLE theater ENABLE check_theater;
23	Under what conditions, it becomes necessary to recompile the package body after the recompilation of package specification?	Any change made to one of the package constructs Altering the argument list of one of the package constructs Removing a local variable from the DECLARE section of one of the package constructs Any SQL statement change made to one of the package constructs
24	Unlike Procedures and Functions that executes explicitly, when does a database trigger is executed?	When transaction commits When data manipulation statement executes When an Oracle provided package access the trigger
25	Which package in Oracle, can be used to output messages and values from database triggers, functions and stored procedures within SQL*Plus?	DBMS_OUTPUT
26	What will happened when a procedure or function terminates with an unhandled error.	The construct will roll back all DML statements processed and returns the unhandled exception to the calling environment.
27	Which package in Oracle provides support for managing Stored Procedures?	DBMS_SQL
28	How to use ISNUMERIC function in SQL*Plus?	Select length(translate(trim(col_name),’+-.01234567 89′,”))from dual; This will return 0 if it is a numeric and greater than 0 if is not numeric. This will basically look for character length
29	What is the use of %ROWTYPE and %TYPE in PL/SQL	%ROWTYPE allows to associate a variable to an entire table row whereas the %TYPE associates a variable to a single column.
30	What is a mutating table error and how can you get around it?	Mutating table error occurs with triggers. It occurs when a trigger ties to update a currently selected row. This can be fixed by using either Views or temporary tables
31	What developer packages has Oracle provided?	DBMS_ series of packages are provided by Oracle. These includes DBMS_SQL, DBMS_TRANSACTION, DBMS_PIPE, DBMS_JOB, DBMS_DDL, DBMS_LOCK, DBMS_OUTPUT, UTL_FILE, DBMS_UTILITY, DBMS_ALERT.
32	What is purpose of PL/SQL tables	They are used to hold values for later use in queries. They are scalar arrays that can be accessed through a binary integer.
33	What is the purpose of Declare statement?	It is used in anonymous blocks i.e. with standalone, non-stored PL/SQL procedures.
34	If a NOTFOUND cursor variable is used in EXIT WHEN statement, what should be the implementation order of fetch/loop/open set of commands in a PL/SQL block?	OPEN > FETCH > LOOP followed by the EXIT WHEN.
35	How can we identify if a cursor is open in a PL/SQL block?	By using %ISOPEN cursor status variable
36	How a debugging output from PL/SQL can be generated?	By using DBMS_OUTPUT package.
37	What are the different types of triggers?	There are 12 types of triggers ROW, TABLE, BEFORE, AFTER, INSERT, UPDATE, DELETE and ALL key words: AFTER ALL ROW INSERT BEFORE INSERT, BEFORE ALL ROW INSERT, AFTER INSERT etc.
38	How to pass variables to a SQL routine?	By using “&” symbol.
39	How to include a carriage return/linefeed in a SQL script output?	By using the CHR() function. For concatenation function use “\|\|” and (CHR(10) for return/linefeed).
40	How to call a PL/SQL procedure from within SQL?	By using the HOST (HO) command or sign of exclamation “!”.
41	“I want to build SQL using SQL”, what does this statement mean?	5. This refers to dynamic SQL
42	Which of the SQL*Plus command formats the output of a select statement?	COLUMN command.
43	Which of the following can be grouped? min(sum_of_cost), max(sum_of_cost), count(item_no), item_no	Only “item_no” can be grouped as rest are the aggregate functions.
44	Which Oracle feature allows to specify as how the cost based system will treat a SQL statement?	COST based system allows the use of HINTs to control the optimizer path selection. IT will be better to give some example hints like FIRST ROWS, ALL ROWS, USING INDEX, and STAR.
45	How to determine the location of identical rows in a table prior to attempting to place a unique index on the table?	In Oracle, tables always have one unique “rowed” column. This can be used to identify the identical rows by setting ORDERY BY clause on rowid and then on primary key
46	What is meant by Cartesian product?	It is the result of an unrestricted join of two or more tables.
47	In a scenario where a local and a remote table are joined, this creates network traffic issues, how these issues can be reduced?	Push the processing of the remote data to the remote instance via a view.
48	What is the default behavior of an ORDER BY clause?	Ascending Order
49	What is meant by tkprof?	It is a tuning tool used to determine cpu and execution times for SQL statements.
50	How to set the no. of lines on an output page?	This can be achieved using SET command in SQLPLUS. Set command controls the number of lines generated per page.