Last Updated: 26.04.2018
If you're looking for SQL Interview Questions for Experienced or Freshers, you are at right place. There are lot of opportunities from many reputed companies in the world. SQL database market continue even stronger by 2020 will be 70%. According to a research SQL by Microsoft grew at 10.32%, while Oracle grew 3.5%. In the upcoming era, competition will be more heated than is has been for years. So, You still have opportunity to move ahead in your career in SQL certification guide. Mindmajix offers Advanced SQL Interview Questions 2018 that helps you in cracking your interview & acquire dream career as SQL Developer.
|SQL Vs NoSQL|
|Type of Data Base||Relational data base||Non relational data base/Distributed data base|
|Standardardization||Standard Query Language exist||No proper standards defined|
|Reporting Tools||Various tools available to analyse performance||Unaivalability of tools to analyse data and performance|
|Development model||Fine grained database model||Architects can create new DB models|
|Price||Expensive compared to NoSQL||Low Cost - Mostly Open Source|
|Schema||Predefined Schema available||Unstructured data with dynamic schema|
|Database Examples||PostGres, Sqlite, Oracle, etc.,||BigTable, Cassandra, MongoDB, etc.,|
|Type of Data storage||Not suitable for hierarchial data storage||Best suitable for hierarchial data.|
SQL- A Structured Query Language, It is also pronounced as “SEQUEL” and it an Non-procedural Language which is used to operate all relational database. Used for Database communication. Its a standard language that can be used to perform the tasks like data retrieval, data update, insert or delete data from an database.
SQL is more natural than MYSQL. MySQL is a computer application. whose DBMS allows multiple users. It enables access to several database application and management system. SQL is more natural and standard language that is used with different applications alike. But, however no organization actually employs this standard language, rather every software firm follows its own kind of SQL version.
|SQL Vs MySQL|
|SQL stands for Structured Query Language||MySQL is a RDMS (Relational Database Management System)|
|Allow for accessing and manipulating db's||MySQL is a database management system, like SQL Server, Oracle, Postgres, Informix etc|
|Basically works as the prompter to a DBMS||It Facilitates multi-user access to a huge number of DBs|
|SQL codes & commands are used in various DBMS and RDBMS systems such as MySQL.||MySQL has SQL at its core, and requires future upgrades mostly|
Q. What is SQL Server?
SQL Server is Microsoft's relational database management system (RDBMS). End user cannot interact directly with database server. If we want to interact with SQL database server then we have to interact with SQL.
Q. What are the Different SQL Servers Versions avialable in the market ?
|SQL Servers Versions|
|SQL Server||Code Name|
|SQL Server 2017||vNext|
|SQL Server 2016||Helsinki|
|SQL Server 2014||Hekaton|
|SQL Server 2012||Denali|
|SQL Server 2008 R2||Kilimanjaro|
|SQL Server 2008||Katmai|
|SQL Server 2005||Yukon|
|SQL Server 2000||Shiloh|
|SQL Server 7.0||Sphinx|
Q. What are the different types of SQL’s statements?
1. DQL - Data Query Language ( or) Data Retrival Language
2. DML – Data Manipulation Language
DML is used for manipulation of the data itself.
3. DDL – Data Definition Language
DDL is used to define the structure that holds the data.
4. DCL – Data Control Language
DCL is used to control the visibility of data.
5. TCL - Transaction Control Language
Q. What are various DDL commands in SQL? Give brief description of their purposes.
DDL Commands are used to define structure of the table
It is used to create database objects like tables, views, synonyms, indexes.
Create table table_name(columname1 datatype(size), columname2 datatype(size),....);
It is used to change existing table structure.
Alter:: a) add
It is used to add columns into existing table
Alter table table_name add(columnname1 datatype(size), columname2 datatype(size),....);
It is used to change column Datatype or datatype size only.
Alter table table_name modify(columnname1 datatype(size), columnname2 datatype(size),....);
It is used to drop columns from the table.
If we want to drop single column at a time without using parentheses then we are using following syntax.
alter table table_namedrop column col_name1; -- drop ONE column
If we want to drop single or multiple columns at a time with using paranthesis then we are using following syntax.
alter table table_name drop(column_name_list);
In all databases we can’t drop all columns in the table.
It is used to remove database objects from database.
Drop object object_name;
Drop table table_name;
Drop view view_name;
It is used to renaming a table.
Rename old table_name to new table_name;
Renaming a column:
Alter table table_name rename column old column_name to new column_name;
Oracle 7.0 introduced truncate table command it is used to delete all rows permanently from the table.
truncate table table_name;
Q. What are various DML commands in SQL? Give brief description of their purposes.
DML Commands are used to manipulate data within a table.
There are:: INSERT, UPDATE, DELETE
1. INSERT − It is used to insert data into in the table
Insert into table_name values(values1, value2, value3,……);
Method2:- Using Substitutional operator (&)
Insert into table_name values(& columnname1, columnname2,.....);
Method3:- Skipping columns
Insert into table_name(col1, col2,...) values(val1, val2, val3,...);
2. UPDATE - It is used to change data in a table.
Update table_name set columnname=new value where columnname=old value;
Note: In all databases we can also use update statement for inserting data into particular shell.
3. DELETE - It is used to delete rows or particular rows from a table.
Delete from table_name;
Delete from tablename where condition;
Q. Difference Between Delete & Truncate?
|SQL Delete Vs SQL Truncate|
|It is DML Command||It is DDL Command|
|It is used to delete all the records row by row||It is used to delete all the records at a time|
|By using delete command we can delete specific record||By using truncate we cannot delete specific record|
|Where condition we can use with delete command||Where condition will not work with truncate|
|Delete will work slow compare with truncate||Truncate will work fast compare with delete|
|Delete will not reset auto generate id. Once when we delete all the records from the table.||Truncate will reset auto generate id from starting number.|
Q. About The SQL Buffer?
Q. What are Important SQL Functions?
Q. How to Open SQL Server?
Goto -> Start -> All Programms -> Microsoft SQL Server 2008 R2 -> SQL Server management Studio.
Q. What is SQL Injections? And How to Prevent SQL Injection Attacks?
It is a mechanism of getting secure data from database.
SQL Injection Attacks::
Q. Difference Between Scalar Valued Functions & Table Valued Functions in SQL?
|SQL Scalar Valued Functions Vs SQL Table Valued Functions|
|Scalar Valued Functions||Table Valued Functions|
|It will process on single row ata time & return only one value of any database||It will process on multiple rows at a time & return multiple rows (or) single row from table|
|The return type of scalar valued function is datatype||The return type of table valued function is table|
|Scalar valued function will have as begin block end||Table valued function will not have as begin end|
Syntax to call Scalar Valued Functions is::
Syntax to call Table Valued Functions is::
Q. Difference Between Stored Procedure & Functions?
|It is a set of pre-compiled SQL Statements which will gets executed when we call it||It will take input from user and return only one value of any data type|
|Compile only one time||Compile every time|
|Stored Procedure will have execution plan||Function will not have execution plan|
|Support DML Commands||Not supported DML Commands|
|Support TCL Commands||Not supported TCL Commands|
|It is may or may not have input parameter||Function must have at least one input parameter|
|It is accept both input and output parameters||Doesn’t have output parameters|
|We call call stored procedure in another stored procedure||We can call function in another function|
|It is support Exception Handling||It is doesn’t support Exception Handling|
|We can call function in stored Procedure||We can’t call stored procedure in function|
Q. Write a Query to view the indexes that are applied on the table?
stored procedure_helpindex table_name
Q. Difference Between Long & Lob Datatypes?
|It stores upto 2GB Data||It stores upto 4 GB Data|
|A table can contain only one long column||A table can contain more than Lob column|
|Subquery cannot select a Long datatype column||Subquery can select Lob Column|
Q. Difference Between (Null Value Function) nvl() & Coalesce()
SELECT nvl(‘a’, sysdate) FROM dual;
SELECT Coalesce(‘a’, sysdate) FROM dual;
Error: inconsistent datatypes: expected CHAR got DATE
Q. Difference Between Views & Materialized Views?
|View does not store data||Materialized view stores data|
|Security purpose||Improved performance purpose|
|When we ar4e dropping base table then view can’t be accessible||When we are dropping base table also materialized view can be accessible|
|Through the view we can perform DML Operation||We can’t perform DML operation|
Q. What is Tuple?
Tuples are the members of a relation. An entity type having attributes can be represented by set of these attributes called tuple.
Q. What is Query & Query Language?
Q. What are the different aggregate functions in SQL?
AVG(), MIN(), MAX(), SUM(), COUNT()
Q.What is data independence?
A database system keeps data separate from software data structure.
Q. What is data integrity?
Data must satisfy the integrity constraints of the system for data Quality.
Q. What is Dead locking?
It is the situation where two transactions are waiting for other to release a lock on an item.
Q. What is decryption?
Taking encoded text and converting it into text that you are able to read.
Q. What is two phase locking?
It is a most common mechanism that is used control currency in two phases for achieving the serializability. The two phases are growing and shrinking.
A transaction acquires locks on data items it will need to complete the transaction. This is called growing process. A transaction may obtain lock but may not release any lock.
One lock is released no other lock may be acquired this is called shrinking process. A transaction may release locks but may not obtain any new locks.
Q. What is projection?
The Projection of a relation is defined as projection of all its tuples over a set of attributes. It yields vertical subset of the relation. The projection operation is used to view the number of attributes in the resultant relation or to reorder attributes.
Q. What is Encryption?
Encryption is the coding or scrambling of data so that humans can not read them directly.
Q. What is cardinality?
The number of instances of each entity involved in an instance of a relation of a relationship describe how often an entity can participate in relation ship. (1:1, 1:many, many:many).
Q. What is Transaction Control?
Oracle Server Ensures Data Consistency Based Upon Transaction.
Transactions Consist of DML Statements That Make Up One Consistent Change To The Data,
Q. What are the Transaction Start & End Cases?
Q. GRANT Command?
SQL> GRANT< Privilage Name1>, ,
GRANT Command is Used When We Want The Database To Be Shared With Other Users.
The Other Users Are GRANTED With Certain Type of RIGHTS.
GRANT Command Can Be issued Not Only on TABLE OBJECT, But Also on VIEWS, SYNONYMS, INDEXES, SEQUENCES Etc.
Q. REVOKE Command?
SQL> REVOKE< Privilage Name1>, ,
REVOKE Command is Used When We Want One Database To Stop Sharing The Information With Other Users.
Revoke Privileges is Assigned Not Only On TABLE Object, But Also on VIEWS, SYNONYMS, INDEXES Etc.
SQL> REVOKE INSERT, DELETE
Q. Connecting to Oracle OR SQL * Plus?
Double Click the SQL*Plus ShortCut on the Desktop.
Start -> Run -> Type SQLPlus OR SQLPlusW in Open Box and Click OK.
Start -> Programs -> Oracle -> Application Development -> SQL*Plus
In the Login Box OR Login Prompt Type the User Name and Password as Supplied by the Administrator.
The Host String is Optional and is provided by the Administrator.
Q. About PL/SQL Tables?
Q. Difference Between SQL and PL/SQL?
|It’s complete name is structured query language||It’s complete name is procedural Language / Structured Query Language|
|It doesn’t have the any facility of branching or looping||It has the complete facility of branching or looping|
|In SQL, only one statement can be sent to Oracle Engine. It increase the execution time||In PL/SQL, a complete block of statements can be sent to Oracle engine at a time, reducing traffic|
|In SQL, the use of variables is not possible||In PL/SQL, the results of the statements can be stored in variables and can be used further as per the requirement|
|It doesn’t have the capacity for procedural language||It is fully support procedural language|
|In SQL, there is no facility of error management. In case of error condition, It is the Oracle Engine that tracks it.||In PL/SQL, the results of the statements can be stored in variables and can be used further as per the requirement|
Q. What is the Temporary Tables?
Syntax: CREATE TABLE #TempTab()
A Temporary Table or Temp-Table is created on disk in the tempDB system database. The name of this Temp-Table is suffixed with a session-specific ID so that it can be differentiated with other similar named tables created in other sessions. The name is limited 116 chars.
Here is an example showing you the usage of a temporary table.
mysql> CREATE TEMPORARY TABLE SALESSUMMARY (
-> product_name VARCHAR(50) NOT NULL
-> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
-> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
-> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO SALESSUMMARY
-> (product_name, total_sales, avg_unit_price, total_units_sold)
-> ('cucumber', 100.25, 90, 2);
mysql> SELECT * FROM SALESSUMMARY;
| product_name | total_sales | avg_unit_price | total_units_sold |
| cucumber | 100.25 | 90.00 | 2 |
1 row in set (0.00 sec)
Q. What are the types of SQL operators?
SQL Arithmetic Operators
SQL Bitwise Operators
|^||Bitwise exclusive OR|
SQL Compound Operators
|&=||Bitwise AND equals|
|^-=||Bitwise exclusive equals|
||*=||Bitwise OR equals|
SQL Comparison Operators
|>=||Greater than or equal to|
|<=||Less than or equal to|
|<>||Not equal to|
SQL Logical Operators
|ALL||TRUE if all of the subquery values meet the condition|
|AND||TRUE if all the conditions separated by AND is TRUE|
|ANY||TRUE if any of the subquery values meet the condition|
|BETWEEN||TRUE if the operand is within the range of comparisons|
|EXISTS||TRUE if the subquery returns one or more records|
|IN||TRUE if the operand is equal to one of a list of expressions|
|LIKE||TRUE if the operand matches a pattern|
|NOT||Displays a record if the condition(s) is NOT TRUE|
|OR||TRUE if any of the conditions separated by OR is TRUE|
|SOME||TRUE if any of the subquery values meet the condition|
Q. What is a CURSOR?
CURSOR is a Handle, OR Pointer To The CONTEXT AREA
Q. What is The CURSOR Usage?
Using a CURSOR, The PL/SQL program can control the CONTEXT AREA, As the SQL Statement is being processed.
Q. What are the CURSOR Features?
Q. What are the Cursor Types?
CURSORS are broadly recognized as 2 types
Q. What are the Different Types of Constraints?
Q. Explain About Different Types of Constraints?
1. Null Constraint - It allows Null Values
create table table_name(columnname datatype, columnname datatype null);
2. NOT NULL Constraint - A NOT NULL Constraint Prohibits a Column From Containing NULL Values.
3.UNIQUE Constraint - The UNIQUE Constraint Designates a Column A s a UNIQUE Key.
4. PRIMARY KEY Constraint - A PRIMARY KEY Constraint Designates a Column A sThe PRIMARY KEY of a TABLE or VIEW
5. FOREIGN KEY Constraint - It is Also Called As REFERENTIAL INTEGRITY CONSTRAINT. It Designates a Column as FOREIGN KEY And Establishes a RELATION Between The FOREIGN KEY And a Specified PRIMARY OR UNIQUE KEY. A COMPOSITE FOREIGN KEY Designates a Combination of Column As The FOREIGN KEY.
A FOREIGN KEY CONSTRAINT Can Be Defined on a Single Key Column Either Inline or Out of Line
A COMPOSITE FOREIGN KEY on Attributes Should Be Declared at Table LEVEL or Out of Line Style. We Can Designate The Same Column or Combination of Columns as Both a FOREIGN KEY and a PRIMARY or UNIQUE KEY. A COMPOSITE FOREIGN KEY CONSTRAINT, Must Refer To a COMPOSITE UNIQUE KEY or a COMPOSITE PRIMARY KEY in the PARENT TABLE or VIEW.
Create table table_name(columnname datatype foreign key references primary key
table_name(primarykey column name);
6. COMPOSITE PRIMARY KEY Constraint - Applying PRIMARY KEY Constraint for the combination of columns is called as COMPOSITE PRIMARY KEY Constraints.
We cannot apply more than one COMPOSITE PRIMARY KEY Constraints on a single table.
Create table table_name(colname1 datatype, colname2 datatype PRIMARY KEY(col1, col2));
7. CHECK Constraint -Check constraints are used to ensure the validity of data in a database and to provide data integrity.
Create table table_name(columnname datatype check(condition));
8. DEFAULT Constraint:
It is used to insert default value instead of null values.
Create table table_name(columnname datatype, default value);
Output: enter value for ename:murali
Employee doe snot exists
Output: enter value for ename:KING
Employee exists KING 7400
Q. Explain Eliminating Explicit Cursor Life Cycle (or) Cursor FOR Loops?
Using cursor for loop we are eliminating explicit cursor life cycle i.e whenever we are using cursor for loop no need to use open, fetch, close statement explicitly i.e when we are using cursor for loop oracle server only internally automatically open the cursor, and then fetch data from the cursor and close the cursor.
varname in cursorname
In cursor for loop index variable internally behaves like a record type variable. (%row type)
Q. What are the Cursor Attributes?
|Attribute Name||Return Value||Condition|
If fetch statement return at least one row
If fetch statement doesn't returns any row
If fetch statement doesn't returns any row
If fetch statement return at least one row
If cursor is already opened
If cursor is not opened
|If counts number of records number fetches from the cursor|
Autonomous transactions are independent transaction used in anonymous blocks, procedures, functions, triggers.
Generally we are defining autonomous transaction is child procedure.
Whenever we are calling autonomous procedure in main transaction and also main transaction TCL commands never affected on autonomous TCL commands procedure, because these are independence procedure.
If we want to procedure autonomous then we are using autonomous transaction pragma, commit i.e in declare section of the procedure we are defining autonomous transaction pragma and also we must use commit in procedure coding.
Q. What is Out Mode?
We can also use out mode parameter i function, but these functions are not allowed to execute by using select statement. If we want to return more no.of values from a function then only we are allowed to use out parameter. Here also out parameter behaves like a uninitialized variables.
Q. What is SQL Loader?
SQL Loader is an utility program which is used to transfer data from flat into oracle database. SQL Loader always executes control file based on the type of flat file we are creating control file and then submit conrol file to SQL loader then only SQL loader transfer file into flat file into oracle Data Base during this file some other files also created.
sql%bulk_rowcount attribute which is used to count affected number of rows within each group in bulk bind process. (forall statements).
Q. Authid current_user
When a procedure have a authid current_user clause then those procedures are allowed to execute only owner of the procedure.
These procedures are not allowed to executes by another users if any user givings permission also. Generally whenever we are reading data from table and performs some DML operations then only data security principles of view developers uses this clause in procedures.
This clause are used in procedures specification only.
Q. What is Row-Level-Attribute?
In this method a single variables can represent all different datatype into single unit. This variable is also called as record type variable.
Row Level Attribute are represented by using %rowtype.S
Q. What are types of Blocks in PL/SQL?
PL/SQL having 2 types of blocks
1. Anonymous Block
2. Nammed Block
|Anonymous Block||Nammed Block|
|This block doesnot have a name||This block having a name|
|These blocks are not stored in oracle database||These blocks are automatically permanently stored in Database|
|Thess blocks are not allowed to call in client application||These blocks are allowed to call in client application|
Q. Write a PL/SQL cursor program which is used to display total salary from emp table without using sum() function by using cursor for loop?
Q. What is Normalization?
Normalization is a scientific process which is called to decomposing a table into number of tables. This process automatically reduces duplicate data and also automatically avoids insertion, updation, deletion problems.
In design phase of SDLC database designers designs LOGICAL MODEL of the database in this logical model only database designers uses normalization process by using normal forms.
Q. What is Super Key?
A columns or a combination of columns which uniquely identifying a record in a table is called a Super Key.
Q. What is Candidate Key?
A minimal super key uniquely identifying a record a table is called candidate key
A super key which is a subset of another super key then those super keys are not a candidate key.
Q. What is Bad File?
This file extension is .bad
Bad file stores rejected records based on
Bad file is automatically created as same name as Flat file, we can also create Bad file explicitally by using bad file clause within control file.
Q. What is Discard File?
This file extension is
Discards file we must specify within control file by using discard file clause.
Discard file also stores rejected record based on when clause condition within control file. This condition must be satisfied into table table_name clause.
Q. What is Autoincrement?
In all databases generating primary key value automatically is called auto increment concept. In Oracle we are implementing auto increment concept by using row level triggers, sequences. i.e here we creating sequence in sql and use this sequence in PL/SQL row level trigger.
SQL> create table test (sno number(10), primary key, name varchar2(10));
Q. What is Dynamic SQL?
It is the combination of SQL, PL/SQL i.e SQL statements are executed dynamically with PL/SQL block using execute immediate clause.
Generally in PL/SQL block we are not allow to use DDL, DCL statements using Dynamic SQL DDL, DCL statement within PL/SQL block.
Q. Write a dynamic SQL program to display number of records from emp table?
Q. Write a dynamic SQL program for passing department number 20 retrieve deptnames, Loc from dept table?
|SSRS||SQL Server DBA|
|Team Foundation Server||BizTalk Server Administrator|
Get Updates on Tech posts, Interview & Certification questions and training schedules