If you're looking for SQL Interview Questions and Answers for Experienced or Freshers, you are at the right place. There are a lot of opportunities from many reputed companies in the world. SQL database market continues even stronger by 2020 will be 70%.
According to research SQL by Microsoft grew at 10.32%, while Oracle grew 3.5%. In the upcoming era, the competition will be more heated than it is has been for years. So, You still have the opportunity to move ahead in your career in SQL certification guide. Mindmajix offers Advanced SQL Interview Questions and Answers 2021 that helps you in cracking your interview & acquire a dream career as SQL Developer.
Below mentioned are the Top Frequently asked SQL Interview Questions and Answers that will help you to prepare for the SQL interview. Let's have a look at them.
Types of SQL Interview Questions
|Want to enrich your career and become a professional in SQL Server DBA, then enrol "SQL Server DBA Training" - This course will help you to achieve excellence in this domain.|
|Type of DataBase||Relational database||Nonrelational database/Distributed database|
|Standardization||Standard Query Language exists||No proper standards defined|
|Reporting Tools||Various tools available to analyze the performance||Unavailability of tools to analyze 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 hierarchical data storage||Best suitable for hierarchical data.|
SQL- A Structured Query Language, It is also pronounced as “SEQUEL” and it a Non-procedural Language that is used to operate all relational databases. 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 a database.
Features of SQL:
SQL Server is Microsoft's relational database management system (RDBMS). End-user cannot interact directly with the database server. If we want to interact with the SQL database server then we have to interact with SQL.
It is basically a program that is considered when it comes to maintaining, creating, deploying, controlling as well as monitoring the use of a database. It can also be considered as a file manager which is good enough to be trusted for managing the data kept in a database than a file system.
The database approach is really a good one as it is numerous benefits for the organizations. The entire data can easily be managed simply irrespective of its size and complexity.
Basically, a table is a set of different rows and columns and is organized in a model. The manner of columns and rows are vertical and horizontal. In a table, there are some specific numbers of columns which remains present and is generally known as fields. There is no strict upper limit on the overall number of records which are defined by rows in the table.
|It is more scalable and secure than Oracle||Oracle too is secure and scalable but not up to the extent SQL|
|It widely supports procedural extensions||The support to the same is limited|
An Inner join is the one that is useful for the purpose of returning the rows provided at least two tables are met critically. On the other hand, the outer join is the one that is useful for returning the value of rows and tables that generally include the records that must be the same in all the tables.
It is basically an array or a group of fields that generally specify a row. It is considered as one of the unique keys that always have some defined or specific value. Generally, the users need not worry about anything when it is enabled as it cannot have a null value.
It is capable to identify all the records in a database simply and the users are free to get the best possible outcome with minimum effort. This is exactly what makes sure of uniqueness.
It is basically nothing but back-end testing or data testing. It generally involves keeping an eye on the integrity of the data an organization uses. It generally validates some of the very useful tasks such as database, indexes, columns, tables as well as triggers. IT also make sure that no duplicate data exist in the database which causes a very large number of problems and the best part is the junk records can also be trashed in a very reliable manner. The updating of the record is also a task that can be made easy with the help of this approach.
These are some important rules in the SQL which are responsible for the restrictions when it comes to deleting, updating, or changing the primary data present in the database.
The key which is created on multiple columns in a table is generally considered as the Composite primary key. However, it is not always necessary that all of them have the same meaning.
|It is a set of pre-compiled SQL Statements which will get executed when we call it||It will take input from the user and return only one value of any data type|
|Compile only one time||Compile every time|
|Stored Procedure will have an execution plan||The function will not have an execution plan|
|Support DML Commands||Not supported DML Commands|
|Support TCL Commands||Not supported TCL Commands|
|It is may or may not have an input parameter||The function must have at least one input parameter|
|It accepts both input and output parameters||Doesn’t have output parameters|
|We call the stored procedure in another stored procedure||We can call a function in another function|
|It supports Exception Handling||It is doesn’t support Exception Handling|
|We can call a function in stored Procedure||We can’t call stored procedure in function|
It is basically a space that is allotted for storing some records that are present within a table. There are actually different fields and it is not always necessary that all the fields are the same in terms of size and allocation pattern.
You can answer these questions based on the commands you have used in your past if you having a bit of experience in SQL. Else, the following commands are there which are widely adopted and are very useful.
They are generally preferred when it comes to defining or changing the structure of a specific database in the shortest possible times due to security and other concerns. Some of the commands that can be applied and considered directly for this are as follows.
It is basically a structure in the SQL that is used for storing any sort of data that is not permanent or needs to be stored for a specific time period. Depending on the needs, it is possible to extend the space up to any extend. Generally, limited space is kept reserved as the temp table.
Commands that are used for the purpose of managing the data present in the database:-
When it comes to handling the queries at a faster rate, the Indexes are preferred widely in SQL and they simply make sure of quick retrieval of the data and the concerned information from the table present in the database. It is possible to create the index on a single column or a group of the same.
On the other side, a View is basically nothing but the subset of a table and is used for the purpose of storing the database in a logical manner. It is actually a virtual table that has rows as well as columns that are similar to that of a real table. However, the views contain data that actually don’t belong to them. The same is considered when it comes to restricting access to a database.
No, the same is not possible
It stands for Structured Query Language and is a powerful language to communicate the database and monitor the concerned tasks easily and reliably. A lot of important tasks such as updating the database, controlling, modifications, as well as deletion of data, can be performed with this task.
It comes with so many dedicated features that are good enough to make a database completely useful and reliable to consider. There are many commands that can be considered and help to save a lot of time when it comes to getting the best out of a database.
It is basically a SQL query and is generally regarded as the subset of the select statement and the process of those tasks that generally make sure of filtering the conditions related to the main query.
Yes, the same is possible and there are many methods that can help users to get the favorable fortune in this matter. The best one is to deploy the SQL SELECT DISTINCT query which issued to return the unique values. All the repeated values or the ones which are duplicates get deleted automatically.
It is used when it comes to including a default value in a column in case there is no new value provided at the time a record is inserted.
There are certain things that largely matter. The first and the foremost is nothing but the size of the database in terms of its storing capacity. Of course, for a bigger database, the needs are complex and so does its management.
Thus, the first thing that can help to keep up the pace in this matter is a powerful query language or a controlling procedure. The next thing is the security of the database. In addition to this, the experience of the experts handling the important operations can also largely impact the database. Moreover, there are conditions on the operation of the same that also largely matter.
A permanent name that is given to a table or a column in SQL is considered as “Rename” whereas the temporary name
given to the same is considered as “Alias”
Join is basically a query that is useful for the purpose of retrieving the columns and the rows. It is useful when users have to handle a very large number of tables at the same time.
The different types of Joins that are present in the SQL are
It is basically a field that doesn’t have any value in SQL. It is totally different from that of a zero value and must not be put equal or confused with the same. These fields are left blank during the creation of the records.
It is basically an approach with one of its primary aims is to simply impose a strict upper limit on the redundancy of the data. The users are free to go ahead with many of the normalizations forms present in the SQL and a few of them are First, second, third, and Boyce Normal Form.
It is nothing but an array of some important SQL statements that are stored in the database and are responsible for performing a specific task.
The same is Trigger
This can be TRUE or FALSE
There are three important types of Indexes and they are
There are several good things about them. The very first thing is they consume almost no space which makes them good enough to be considered in every situation. At the same time, the users are able to consider views for simply retrieving the outcomes that belong to queries that are complicated in nature.
The same may need to be executed frequently. It is possible to consider this when it comes to restricting access to the database.
SQL is more natural than MYSQL. MySQL is a computer application. whose DBMS allows multiple users. It enables access to several database applications and management systems.
SQL is a 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 stands for Structured Query Language||MySQL is an 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|
1. DQL - Data Query Language ( or) Data Retrieval 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
DDL Commands are used to define the 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 the existing table structure.
It is used to add columns into an 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 a single column at a time without using parentheses then we are using the 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 parenthesis then we are using the 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 the database.
Drop object object_name; (or) Drop table table_name; (or) 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;
DML Commands are used to manipulate data within a table.
There are: INSERT, UPDATE, DELETE
1. INSERT − It is used to insert data into 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 the update statement for inserting data into a 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;
|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 the delete command we can delete a specific record||By using truncate we cannot delete a specific record|
|Where condition we can use with the 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.|
Goto -> Start -> All Programms -> Microsoft SQL Server 2008 R2 -> SQL Server management Studio.
It is a mechanism for getting secure data from the database.
SQL Injection Attacks::
|Scalar Valued Functions||Table-Valued Functions|
|It will process on a single row at a time & return only one value of any database||It will process on multiple rows at a time & return multiple rows (or) single row from the table|
|The return type of scalar-valued function is a datatype||The return type of table-valued function is a table|
|The scalar-valued function will have as begin block end||The table-valued function will not have as begin end|
Syntax to call Scalar Valued Functions is::
Syntax to call Table-Valued Functions is::
stored procedure_helpindex table_name
|It stores up to 2GB of Data||It stores up to 4 GB of Data|
|A table can contain only one long column||A table can contain more than Lob column|
|A subquery cannot select a Long data type column||A subquery can select Lob Column|
nvl is an oracle function whereas Coalesce is an ANSI Function and also coalesce performance is very high as compare to NVL Function.
NVL Function internally uses implicit conversions i.e NVL Function returns a value if the exp1, exp2 does not belong to the same datatype also if exp2 automatically converted into exp1 whereas in coalesce function exp1, exp2 must belong to the same data type.
SELECT nvl(‘a’, sysdate) FROM dual;
SELECT Coalesce(‘a’, sysdate) FROM dual;
Error: inconsistent datatypes: expected CHAR got DATE
Tuples are the members of a relation. An entity type having attributes can be represented by a set of these attributes called tuple.
|The 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|
AVG(), MIN(), MAX(), SUM(), COUNT()
A database system keeps data separate from the software data structure.
Data must satisfy the integrity constraints of the system for data Quality.
It is the situation where two transactions are waiting for the other to release a lock on an item.
Taking encoded text and converting it into text that you are able to read.
The Projection of a relation is defined as a projection of all its tuples over a set of attributes. It yields a vertical subset of the relation. The projection operation is used to view the number of attributes in the resultant relation or to reorder attributes.
Encryption is the coding or scrambling of data so that humans can not read them directly.
The number of instances of each entity involved in an instance of a relation of a relationship describes how often an entity can participate in the relationship. (1:1, 1: many, many: many).
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.
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
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.
|Explore Oracle PL SQL Interview Questions|
|Its complete name is a structured query language||Its complete name is procedural Language / Structured Query Language|
|It doesn’t have 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 increases 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 a procedural language||It fully supports procedural language|
|In SQL, there is no facility of error management. In case of an 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|
CURSOR is a Handle OR Pointer To The CONTEXT AREA
Using a CURSOR, The PL/SQL program can control the CONTEXT AREA, As the SQL Statement is being processed.
Output: enter value forename:murali
Employee doe snot exists
Output: enter value forename:KING
Employee exists KING 7400
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 opens the cursor, and then fetch data from the cursor and close the cursor.
Syntax: For an index
varname in cursor name Loop stmts; end loop;
In cursor for the loop index variable internally behaves like a record type variable. (%row type)
|Attribute Name||Return Value||Condition|
If fetch statement return at least one row
If the fetch statement doesn't return any row
If the fetch statement doesn't return any row
If fetch statement return at least one row
If the cursor is already opened
If the cursor is not opened
|%rowcount||Number||If counts number of records number fetches from the cursor|
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 an uninitialized variable.
SQL Loader is a utility program that 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 control 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.
Oracle introduced sql%bulk_rowcount the attribute which is used to count the affected number of rows within each group in bulk bind process. (all statements).
In this method, a single variable can represent all different datatype into a single unit. This variable is also called a record type variable.
Row Level Attribute is represented by using %rowtype.
PL/SQL has 2 types of blocks
|Anonymous Block||Name Block|
|This block does not have a name||This block having a name|
|These blocks are not stored in the oracle database||These blocks are automatically permanently stored in Database|
|These blocks are not allowed to call in the client application||These blocks are allowed to call in the client application|
sql> declare cursor c1 is select*from emp n number(10):=0; begin for i in c1 loop n:=n+i.sal; end loop; dbms_output.put_line (‘total salary is: ‘||’ ‘||n); end; /?
Output: total salary is: 42075
Normalization is a scientific process that is called decomposing a table into a number of tables. This process automatically reduces duplicate data and also automatically avoids insertion, update, deletion problems.
In the design phase of SDLC, database designers design the LOGICAL MODEL of the database in this logical model only database designers use the normalization process by using normal forms.
A column or a combination of columns that uniquely identify a record in a table is called a Super Key.
A minimal super key uniquely identifying a record a table is called a candidate key
A super key is a subset of another super key then those super keys are not a candidate key.
This file extension is .bad
Bad file stores rejected records based on
The bad file is automatically created as the same name as the Flat file, we can also create Bad file explicitly by using the bad file clause within the control file.
This file extension is .dsc
Discards file we must specify within control file by using the discard file clause.
Discard file also stores rejected record based on when clause condition within the control file. This condition must be satisfied with a table table_name clause.
In all databases generating primary key value automatically is called the auto-increment concept. In Oracle, we are implementing the 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));
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 allowed to use DDL, DCL statements using Dynamic SQL DDL, DCL statement within PL/SQL block.
|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|
SQL> declare z number(10); begin execute immediate ‘select count * FROM emp’ into z; dbms_output.put_line(z); end; /?
SQL> declare v_deptno number(10):=20; v_dname varchar2(10); v_loc varchar2(10); begin execute immediate ‘select dname, loc FROM dept where deptno=1’ into v_dname, v_loc using v_deptno; dbms_output.put_line(v_dname ||’ ‘|| v_loc); end; /?
After attending the interview, if you found any other questions asked other than this in the blog, feel free to post them in the comment section below and our experts will include them in the blog along with the best possible solution to help other students learn from your experience.
|Team Foundation Server||BizTalk Server Administrator|
Ravindra Savaram is a Content Lead at Mindmajix.com. His passion lies in writing articles on the most popular IT platforms including Machine learning, DevOps, Data Science, Artificial Intelligence, RPA, Deep Learning, and so on. You can stay up to date on all these technologies by following him on LinkedIn and Twitter.