The blog provides the commonly asked Oracle RDBMS SQL Interview questions and answers for the interview preparation. SQL is Structured Query Language used by the Oracle Database Developer , Oracle DBA Administrators. The below given SQL Interview questions and answers can be helpful in preparing for Database Job Interviews for MNC IT Companies like Google, Amazon, Microsoft, Infosys , Wipro , TCS, etc.
The blog contains the basic as well as advanced questions on SQL which could be helpful for technical interviews for both freshers as well as experienced professionals
SQL Interview Questions and Answers for beginners and experienced professionals
What is SQL?
SQL stands for Structured Query Language used in Relational Databases like Oracle and helps in performing the data operation ( Create , Update, Delete) on the Oracle tables, Views, Indexes, etc.
SQL (Structured Query Language) was initially developed in 1974 for the relational databases.
SQL became the standard of the American National Standards Institute (ANSI) in 1986 and ISO (International Organization for Standardization) in 1987
What is DBMS?
DBMS stands for Database Management System which helps in managing the Creation , maintenance and use of the database.
What is RDBMS?
RDBMS stands for Relational Database Management System which stores the data into the collection of tables. As multiple times can be used to retrieve user specific information and each table is linked to another with the use of primary key, foreign key , unique key and makes the relation among each other for the data linkage.
What are the components of the DBMS?
The Database Management System (DBMS) broadly uses the below components:
Software
Data
Procedures
Database Languages
Query Processor
Database Manager
Database Engine
Reporting
What are the different types of DBMS?
The below given are the different types of Database Management System (DBMS)
Relational databases
NoSQL Databases
Document databases
Graph databases
ER model databases
Network databases
Hierarchical databases
Object-oriented databases
What is advantages of using SQL in relational databases?
The advantages of SQL are given below
SQL helps in creating / dropping relational database
SQL helps in performing operations against relational database
SQL helps in inserting data into relational database table
SQL helps in data retrieval from relational database table
SQL helps in updating data into relational database table
SQL helps in deleting data into relational database
SQL helps in creating / dropping tables into relational database
SQL helps in creating / dropping views into relational database
SQL helps in creating / dropping indexes into relational database table
Does SQL provides programming language features ?
SQL is not a programming features and only support DDL and DML Statement like CREATE , INSERT , UPDATE, DELETE and consider as a Query Language which allows to perform operation on relational database.
PL/SQL is required if conditional branching , loop statements are to be used to write complex programming logic.
What does SQL contains ?
The SQL is comprised of the below given components:
Data Definition Language (DDL) : used to define the data structure and provide SQL commands like CREATE, ALTER, DROP, etc.
Data Manipulation Language (DML): allows to update the database table data by performing operation like SELECT, UPDATE, INSERT, DELETE
Data Control Language (DCL) : manages the data access in the database with SQL commands like REVOKE , GRANT
Transaction Control Language( TCL) : manages the transaction processing in the database with SQL commands like COMMIT, ROLLBACK, SET TRANSACTION, SAVEPOINT
What is a Primary Key in relational database?
A primary key must enforce the below points
A primary key could be a single field or a combination of fields which should uniquely define a table record
A table can have only one primary key
A primary key cannot have NULL values
CREATE Table Primary Key example:
CREATE TABLE student_table
( student_id int NOT NULL,
student_name char(50) NOT NULL,
course char(50),
CONSTRAINT student_table_pk PRIMARY KEY (student_id)
);
Give example of ALTER command to add Primary Key?
ALTER TABLE student_table
ADD CONSTRAINT student_table_pk
PRIMARY KEY (student_id);
How to DROP a primary key from the table ?
ALTER TABLE student_table
DROP PRIMARY KEY;
What is a Unique Key ?
A unique key must enforce the below points
A unique key could be a single column or combination of columns in a table
A unique key does not allow duplicate values
A table can contain multiple unique key columns but only 1 primary key
CREATE Table Unique Key example:
CREATE TABLE student_table
( student_id int NOT NULL,
student_name char(50) NOT NULL,
course char(50),
email varchar(100) UNIQUE ,
CONSTRAINT student_table_pk PRIMARY KEY (student_id)
);
What is a Foreign Key ?
A Foreign Key also known as Reference Key is used to link one or more tables.
A Foreign Key used in the table must be an existing primary key of another table.
A Foreign Key maintains the referential integrity by identifying each row of another table uniquely
Explain the difference between Primary Key and Unique Key ?
The below given are the main differences between a primary key and a unique key:
A primary key could be a single field or a combination of fields which should uniquely define a table record whereas a unique key prevents two rows from having duplicate entries in a column
A table can have only one primary key but can have multiple unique keys per table
A primary key does not support NULL values, whereas a unique key can have NULL values but only one NULL is allowed in a table
A primary key should be unique, but a unique key cannot necessarily be the primary key
A primary key by default is a clustered index (sequential data) whereas a unique key is a non-clustered index
A primary key implements entity integrity, whereas a unique key enforces unique data
Explain the difference between Primary Key and Foreign Key ?
The below given are the main differences between a primary key and the foreign key:
A primary key could be a single field or a combination of fields which should uniquely define a table record whereas the foreign key is used to link one or more tables
A primary key identifies a record in the relational database table whereas the foreign key identifies the primary key of another table
A table can have only one primary key but can have multiple foreign keys per table
A primary key does not support NULL values, whereas the foreign key can have NULL values
What is an Index in SQL ?
An INDEX is:
– a pointer to the data into the table which helps database search engine to retrieve the data at faster speed.
– technique of minimizing the query’s cost
– always UNIQUE for the table data
– creation of Index or dropping an Index does not impact the table data
– works faster when SELECT and WHERE clause are used in SQL statements compared to
INSERT and UPDATE SQL statements
Syntax for Create Index Command :
CREATE INDEX index_name ON <table_name>;
What are the different types of indexes in SQL?
The below given are the different types of indexes in SQL
– Unique Index
– Clustered Index
– Non-Clustered Index
– Bit-Map Index
– Normal Index
– Composite Index
– B-Tree Index
– Function-Based Index
What is a Unique Index in SQL?
A unique index ensure that the index key column does not contain DUPLICATE values
A unique index could be a combination of one or more table columns
A unique index with multiple columns should have Unique combination of value for the columns
Example:
CREATE TABLE student_table
( student_id int NOT NULL,
student_name char(50) NOT NULL,
course char(50),
email char(50)
);
CREATE UNIQUE INDEX student_id_indx ON student_table (student_id);
What is a clustered Index in SQL?
– Clustered index sort and stores the data into table or vies based on the key values.
– A table supports only one clustered index
– Clustered index could have one or more table columns
– A table with clustered index is also known as clustered table
– A table with no clustered index, stores the data in an unordered structure called a heap
What are the advantages of clustered Index in SQL?
The advantages of a Clustered index are:
– A clustered index is useful for range or group queries to get max, min, and count values
– A clustered index uses location mechanism for finding an index entry for data search
– A clustered index is useful to maximize the cache hits and minimizes the page transfer
What is a non-clustered Index in SQL?
– The indexes created on the table other than the Clustered Indexes are called as Non-
Clustered Indexes.
– The Non-Clustered index contains the non-clustered index key values and each key
value entry has a pointer to the data row that contains the key value
– The Non -Clustered indexes are created when SQL query requires multiple join condition
for the data retrieval
– The Non- Clustered indexes can be unique
What is a View in SQL?
A View is:
– a database object that has no value of its own
– always have a unique name
– represents the sub set of columns from one or more tables
– a virtual table which depicts data at run-time
– reflects the change in data if the underlying table data is changed
Syntax for Creating View in SQL:
CREATE or REPLACE VIEW view_name
AS SELECT column_name(s)
FROM table_name
WHERE condition
Explain the OFFSET and FETCH clause in SQL?
– The OFFSET clause allows to define the starting point to return rows from a result set
– The FETCH clause allows to return a set of number of rows and should be used in
conjunction with OFFSET
– The FETCH Clause requires the mandate use of OFFSET clause
– The FETCH Clause CANNOT be used with the ORDER BY clause
– The OFFSET/FETCH row count expression can be only be any arithmetic, constant, or
parameter expression and should return positive integer value
– ORDER BY is mandatory to be used with OFFSET and FETCH clause
– TOP cannot be combined with OFFSET and FETCH
– OFFSET value CANNOT be negative, else return error
What is the difference between ORDER BY and GROUP BY in SQL ?
ORDER BY CLAUSE :
The Order By Clause is used to sort the table data in ascending or descending order.
Syntax for Order by Clause:
SELECT column1, column2, column3, column4, …..columnN
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
GROUP BY CLAUSE :
The Group By Clause is used to arrange the table columns into group.
The Group by Clause is followed by the WHERE Clause
The Group by Clause precedes the Order by Clause
Syntax for Group by Clause:
SELECT column1, column2, column3, column4, columnN
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2
ORDER BY column1, column2
What is the difference between HAVING and GROUP BY clause in SQL ?
HAVING CLAUSE :
The Having Clause is used with Aggregate functions followed by the Group By clause in order to find rows with certain conditions.
When Having clause is used then WHERE Clause is not required.
Example for Having Clause:
SELECT COUNT (SALARY) AS Salary_Count
FROM EMPLOYEES
GROUP BY SALARY
HAVING COUNT(SALARY) > 50000;
GROUP BY CLAUSE :
The Group By Clause is used to arrange the table columns into group.
The Group by Clause is followed by the WHERE Clause
The Group by Clause precedes the Order by Clause
Syntax for Group by Clause:
SELECT COUNT (SALARY) AS Salary_Count
FROM EMPLOYEES
GROUP BY SALARY
What is Online Transaction Processing (OLTP)?
Online Transaction Processing (OLTP) is a software system providing transaction-oriented applications which can be used for data entry, data retrieval and data processing.
OLTP allows to perform real -time transactions and also ensures consistency and atomicity for the data transactions.
Examples: Order Entry, Retail Sales, Financial Transaction Systems
What are the differences between OLTP and OLAP?
Online Transaction Processing (OLTP) is a software system providing transaction-oriented applications which can be used for data entry, data retrieval and data processing.
OLTP allows to perform real -time transactions and also ensures consistency and atomicity for the data transactions.
Examples: Order Entry, Retail Sales, Financial Transaction Systems
Online Analytical Processing (OLAP) is a software system primarily used for data mining or maintaining aggregated, historical data, usually in multi-dimensional schemas.
OLAP generated reports which allows Business Analyst to conclude on the business trends and creating business strategies for the product growth
What are Aggregate Functions and its types in SQL ?
Aggregate functions allows to perform operations on the table column values and returns the single value as result.
The below given are the types of Aggregate functions
– AVG(): returns the AVERAGE for all the values from the table column
– COUNT() : returns the COUNT for all the values from the table column
– FIRST() : returns the FIRST value from the table column
– LAST() : returns the LAST value from the table column
– MAX() : returns the MAXIMUM value from the table column
– MIN() : returns the MINIMUM value from the table column
– SUM() : returns the SUM of all the table column values
What are Scalar Functions and its types in SQL ?
Scalar functions allows to perform operations based on user input and returns the single value as result
The below given are the types of Aggregate functions
– UCASE() : returns the UPPERCASE Text for the table column
– LCASE() : returns the LOWERCASE Text for the table column
– LEN() : returns the LENGTH for the table column
– ROUND() : returns the ROUND value for the table column
– NOW() : returns the Current Time
– FORMAT() : returns the FORMATTED value for the table column
What are CONSTRAINTS in SQL ?
Constraints in SQL are provided to restrict the table data based on specific rule to maintain the accuracy and data integrity of the rows in the table.
Constraints can be classified as:
Column level constraints: Limits the column data only
Table level constraints: Limits the complete table data
Name few SQL constraints ?
The below given are the commonly used Constraints in SQL :
– DEFAULT: Automatically assigns a default value for the table column if no value is
specified
– UNIQUE : Ensures unique values to be inserted into the table column.
– PRIMARY KEY: Uniquely identifies each record in a table.
– FOREIGN KEY : Ensures referential integrity for a record in another table.
– INDEX : Indexes a field providing faster retrieval of records.
– NOT NULL: Restricts NULL value for the table column
– CHECK : Verifies that all values in a field satisfy a condition
How to create an empty table from an existing table?
Select * into Employee_temp
from Employee
where 1=2
How to select UNIQUE records from a table?
Select DISTINCT EMP.ID, EMP.Name
from EMP
Which SQL comamnd can be used to get first 5 characters of the Employee Name?
Select SUBSTRING(EMP.NAME,1,5) as EmployeeName
from EMP
What is the difference between TRUNCATE , DROP and DELETE commands in SQL ?
– The TRUNCATE SQL command allows to eliminate all the rows from the table. Once TRUNCATE command is executed, then eliminated data CANNOT be rolled back.
– The DROP SQL command allows to DROP the table itself from the database and eliminated data CANNOT be rolled back
– The DELETE SQL command allows to delete all rows or specific rows based on WHERE clause from the database table. The Deleted data CAN be rolled back
What is the difference between UNION, MINUS and INTERSECT commands in SQL?
The UNION, MINUS and INTERSECT SQL commands allows to combine the result of separate SQL queries to provide a common table data result set
– The UNION SQL command allows to combine the results of two tables by eliminating the duplicate rows from the tables
– The MINUS SQL command allows to return rows based on matched records that are found in the first query and not in the second query and thus eliminates rows that are found only in the second query
– The INTERSECT SQL command allows to return rows that are matched in both queries
What are Set Operators in SQL ?
The Set Operators in SQL are used to perform operation on table data based on specific conditions
There are 4 types of Set Operators in SQL:
– UNION
– UNION ALL
-INTERSECT
-MINUS
What are Operators in SQL ?
Explain Arithmetic Operators in SQL ?
Arithmetic Operators in SQL are used to perform mathematical operation on the table columns.
The below given are the types of Arithmetic Operators:
– Addition (+) : allows to ADD the table column data values
– Subtraction (-) : allows to SUBTRACT the table column data values
– Division (/) : allows to calculate the DIVISION of the table column data values
– Multiplication : allows to MULTILY the table column data values
– Modulus (%) : allows to calculate the MODULUS of the table column data values
Explain Wildcard Operators in SQL ?
Wildcard Operator (LIKE) is used to perform the operation on the table column by providing the range of characters or partial text to get the related data from the table
Example:
SELECT * FROM Student WHERE FIRST_NAME LIKE ‘A%‘;
SELECT * FROM Student WHERE NAME LIKE ‘ANU_‘;
What is a SEQUENCE in SQL ?
A SEQUENCE in SQL is an object that allows to generate a number sequence for the table column value
Example for creating sequence in SQL :
CREATE SEQUENCE student_seq
MINVALUE 1
MAXVALUE 9999999
START WITH 1
INCREMENT BY 1
CACHE 20;
Example for Dropping Sequence in SQL:
DROP SEQUENCE student_seq
what does CACHE and NOCACHE options mean in SEQUENCE ?
WHEN CACHE Option is provided, it specifies how many sequence values to be stored in memory for access
When NOCACHE is provided, then no sequence value is stored in memory and is retrieved by calling the Sequence directly
Explain Transaction Control Language(TCL) in SQL ?
The Transaction Control Language (TCL) are used to manage the transaction processing in the relational database.
The types of TCL commands are :
– COMMIT : The changes done by the DML commands like INSERT, UPDATE and DELETE and not permanent and transaction can be rolled back in the current session and thus COMMIT saves the transaction data permanently and data CANNOT be rolled back
– ROLLBACK : allows the transaction to be rolled back in the current session until COMMIT is not performed
– SAVEPOINT: allows to save the transaction with a save point ( depicts till what point last committed transaction is saved) so that if required transaction can be Rolled Back to the save point
What is AUTO INCREMENT in SQL?
Auto increment keyword allows the user to generate a unique number for a specific column whenever a new record is inserted into the table.
AUTO INCREMENT keyword is used in Oracle whereas IDENTITY keyword is used in SQL SERVER for generating auto incremented number.
What is a subquery in SQL?
A subquery is a query within another SQL query
The Outer query is called as MAIN query and INNER query is called subquery
A subquery is always executed first.
A subquery result becomes the input to the Outer query
The below given are the types of subquery:
Correlated subquery:
A correlated subquery CANNOT be executed independently and is referred as INNER Query in the MAIN Query
A correlated subquery is allowed to refer the table column FROM the list of the MAIN query
Non-Correlated subquery:
A Non-Correlated subquery CAN be executed independently
A Non-Correlated subquery result is substituted in the MAIN query
What is the difference between SQL and MySQL?
SQL (Structured Query Language) is a standard language for retrieving and manipulating structured databases whereas MySQL is a Relational Database Management System (RDBMS) like SQL Server, Oracle or IBM DB2 for managing and maintaining SQL databases
What is the difference between UNION and UNION ALL in SQL ?
UNION: The UNION command allows to fetch results from multiple tables eliminating the duplicate rows from the result
SELECT customer_Id FROM Customers
UNION
SELECT customer_Id FROM Orders;
UNION ALL: The UNION command allows to fetch results from multiple tables including the duplicate rows in the result
SELECT customer_Id FROM Customers
UNION ALL
SELECT customer_Id FROM Orders;
What is Collation? What are the different types of Collation Sensitivity?
Collation provides the rules that helps in determining how the stored data is to be sorted and compared.
Collation Rules defining the correct character sequence are used to sort the character data.
Collation Rules provides options for specifying case-sensitivity, accent marks, kana character types and character width
The types of collation sensitivity are:
– Case Sensitivity: A and a are treated differently.
– Accent Sensitivity: a and รก are treated differently.
– Kana Sensitivity: Japanese kana characters Hiragana and Katakana are treated differently.
– Width Sensitivity: Same character represented in single-byte (half-width) and double-byte (full-width) are treated differently
Explain TOP clause in SQL ?
The TOP clause in SQL is useful when we need to fetch a TOP N number or X percent records from the table.
MySQL supports the LIMIT clause to fetch limited number of records
ORACLE supports the ROWNUM command to fetch limited number of records
Syntax for TOP Clause in SQL:
SELECT TOP number|percent column_name(s)
FROM table_name
WHERE [condition]