Most frequently asked SQL interview questions in 2021

Rohan Mathew

Uncategorized

 

%title% %sep% %sitename%

most-frequently-asked-sql-interview-questions-in-2021

If you are reading this article then, I guess you are preparing for SQL interview questions!
All the best for your interview!

Here I have made a small attempt to bring down a consolidated list of the most frequently asked sql interview questions

Hope it helps you!

Here we go…

  1. What is SQL?
  • SQL stands for Structured Query Language.
  • Its main aim to store, access, and manipulate the data in relational databases.
  • SQL is an American National Standards Institute ( ANSI ) standard, developed by Raymond F.Boyce and Donald D.chamberlin at IBM.
  • All relational database management systems like MySQL, MSaccess, oracle, and SQL Server use SQL a standard database language.
  1. What SQL can do?
  • To execute queries against a database.
  • To retrieve data from a database.
  • To inserts records in a database.
  • To updates records in a database.
  • To delete records from a database.
  • To create new databases.
  • To create new tables in a database.
  • To create views in a database.
  • To perform complex operations on the database.
  1. What is join in SQL?

SQL Joins – Combining rows from two or more tables, based on the related column between them.

Types of Joins:

  • Inner Join − When there is a match in both tables- “Returns rows”

SELECT column_name(s)

FROM table1

INNER JOIN table2

ON table1.column_name = table2.column_name;

  • Left Join − Returns all records/rows from the left table, even if there are no matches in the right table.

SELECT column_name(s)

FROM table1

LEFT JOIN table2

ON table1.column_name = table2.column_name;

  • Right Join− Returns all records/rows from the right table, even if there are no matches in the left table.

SELECT column_name(s)

FROM table1

RIGHT JOIN table2

ON table1.column_name = table2.column_name;

  • Full outer Join − returns records/rows when there is a match in either left or right table

SELECT column_name(s)

FROM table1

FULL OUTER JOIN table2

ON table1.column_name = table2.column_name

WHERE condition;

  • Self Join − Table is joined itself this is  Self Join ( Regular Join)SELECT column_name(s)

FROM table1 T1, table1 T2

WHERE condition;

  • CARTESIAN JOIN (CROSS JOIN) − Sets of rows/records from the two or more joined tables will be returned, this is cartesian join.SELECT *

FROM table1 

CROSS JOIN table2;

  1. What are the different types of SQL commands?

SQL commands are segregated into the following types:

  • DDL – Data Definition Language
  • DML – Data Manipulation Language
  • DQL – Data Query Language
  • DCL – Data Control Language
  • TCL – Transaction Control Language
  1. What are the subsets of SQL?

There are three significant subsets of the SQL:

  • Data definition language (DDL): Data structure can be defined by DDL and it also consists of commands like CREATE, ALTER, DROP, etc.
  • Data manipulation language (DML): DML helps in manipulating existing data in the database and it also consists of commands like SELECT, UPDATE, INSERT, etc.
  • Data control language (DCL): DCL helps in controlling the data access and commands such as GRANT, REVOKE are useful for controlling the data.
  1. What are the different DDL commands in SQL?

DDL commands help in defining or altering the structure of the database.

  • CREATE: Helps in creating databases and database objects.
  • ALTER: Helps in altering existing database objects.
  • DROP: To drop databases and database objects.
  • TRUNCATE: Helps in removing all records from a table but not its database structure.
  • RENAME: To rename database objects.
  1. What are the different DML commands in SQL?

DML commands help in managing data present in the database.

  • SELECT: Can select specific data from a database.
  • INSERT: Can insert new records into a table.
  • UPDATE: Can update existing records.
  • DELETE: Can delete existing records from a table.
  1. What are the different DCL commands in SQL?

DCL commands are helping in creating roles, granting permission, and control access to the database objects.

  • GRANT: Can provide user access.
  • DENY: Can deny permissions to users.
  • REVOKE: Can remove user access.
  1. What are the different TCL commands in SQL?

TCL commands help in managing the changes made by DML statements.

  • COMMIT: Helps in writing and storing the changes to the database.
  • ROLLBACK: Helps in restoring the database since the last commit.
  1. What is an Index?

An index is an on-disk structure used to speed up the performance of queries which helps in the retrieval of data from the table.

  1. What are all the different types of indexes?

There are three types of indexes

  • Unique Index: Unique Indexes keep up data integrity by ensuring that no two rows of data in a table have identical key values.
  • Clustered Index: Clustered Index is only one per table which helps in reordering the physical order of the table and searches based on the key values.
  • Non-Clustered Index: Unlike the Clustered index, this has many non-clustered indexes in each table and it doesn’t alter the physical order of the table and maintains a logical order of the data.
  1. What are the differences between Cluster and Non-Cluster Index?
Clustered Index Non-Clustered Index
Data from the database can be retrieved faster Data from the database can be retrieved slower
One table can only have one clustered index One table can have multiple non clustered index
Type of index which helps in sorting the data rows in the table Type of index which helps in sorting data at one location and indices at another location

 

  1. What is a View in SQL?
  • The view is a subset of a table that is stored logically in a database.
  • It is a virtual table.
  • A view contains rows and columns.
  • Views do not contain data of their own.

 

  1. What are the advantages of Views?

Some of the advantages of Views are

  • Views occupy no space.
  • Views help in retrieving complicated queries that need to be executed often.
  • The view helps in hiding data complexity and restricting access to the database
  1. What is a Database?
    A Database is defined as a systematic collection of data that is stored in a computer in an organised manner and can be accessed in various ways.
    Database helps in
    – Collection of schemas.
    – Tables.
    – Queries.
    – Views.
    – Accessing and manipulation of data.
  2. Does SQL support programming language features?
  • SQL is a command language, not programming knowledge!
  • SQL has commands like query, update, delete, etc. not conditional statements like for loops or if..else, etc…
  • SQL allows us to manipulate data in a database.

So, SQL does not support programming languages.

  1. What is a Data warehouse?

The data warehouse is a  central consolidated repository of data from multiple sources of information and these data are helpful in data mining as well as online processing.

  1. What is a Table in a Database?

A table is a database object that contains all the data in a database in the form of columns and rows that holds data.

  1. What is a Field in a Database?

A field in a database is a single piece of information from a record.

  1. What is a Record in a Database?

A row is generally called a record in a database which helps in the collection of fields of different data types which is typically in a fixed number and sequence.

21.What is a column in a Table?

A column is an “attribute” that we keep track of all information associated with a specific field in a table.
22. What is an SQL server?

  • SQL Server is a relational database management system, or RDBMS, developed (By Microsoft ) which has worked on windows for more than 20 years and Microsoft made it available on Linux in October 2016
  • SQL Server helps in interacting with relational databases.
  1. What is DBMS?

Database Management System is software that enables the user to store, retrieve, update, and delete information from a database.

  1. What are the types of DBMS?

There are two types of DBMS

  1. Relational Database Management System (RDBMS)
  2. Non-Relational Database Management System
  3. What is RDBMS?

RDBMS stands for Relational Database Management System that is based on the relational model which can be accessed using Structured Query Language (SQL).

  1. What are the popular Database Management Systems in the IT Industry?
  • Oracle
  • MySQL
  • Microsoft SQL Server
  • PostgreSQL
  • MongoDB
  • Microsoft Access
  1. What is a query?

A database query is either a “ SELECT” or “ ACTION” query that helps in requesting data or information from a database table or combination of tables.

  1. What is a Subquery?

A Subquery is a SQL query, nested inside another subquery that can be used anywhere where “expression” is allowed.
A subquery may occur in :

  • – A SELECT clause
  • – A FROM clause
  • – A WHERE clause
  1. What are the types of subquery?
  • Single-row subquery: Returns zero or one row.
  • Multiple row subquery: Returns one or more rows.
  • Multiple column subqueries: Returns one or more columns.
  • Correlated subqueries: subquery is related to the outer SQL statement.
  • Nested subqueries: Subqueries are placed within another subquery.
  1. What is SQL Injection?

SQL Injection is a type of code injection technique where malicious SQL statements are inserted into an entry field of a database which helps in accessing sensitive data and perform administrative tasks on databases.

  1. What is the difference between Local Variables and Global Variables?

Local Variables: Local variables exist only inside the function and are not referred to by any other functions.
Global Variables: Global variables exist throughout the program and can access globally.

  1. What is Data Integrity?

Data integrity is used to maintain the accuracy and consistency of the data stored in a database.

  1. What is Auto Increment in SQL?

Auto increment keyword allows creating a unique number when a new record is inserted into a table.

  1. What is a temp table?

A temp table helps in storing the data temporarily.

  1. Difference between SQL Vs NoSQL?

 

Feature SQL NoSQL
Type of DataBase Relational database Non Relational 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.

 

  1. What is Normalization?

Normalization database design technique to avoid duplication and redundancy.

  1. What are the advantages of Normalization?

Some of the advantages are:

  • Better Database organization.
  • Efficient data access.
  • Greater Flexibility for Queries.
  • Quickly find the information.
  • Easier to implement Security.
  • Allows easy modification.
  • Reduction of redundant and duplicate data.
  • Ensure Consistent data after modification.
  1. What is the difference between DROP and TRUNCATE commands?
  • DROP command is used to remove the table and it cannot be rolled back from the database.
  • TRUNCATE command helps in deleting all the rows from the table.
  1. Explain different types of Normalization?

Normalization helps in organising the data in databases in many successive levels of normalization.
The best three normal forms are :

  • First Normal Form (1NF)
    Second Normal Form (2NF)
  • Third Normal Form (3NF)
  1. What is an ACID property in a database?

ACID stands for Atomicity, Consistency, Isolation, Durability.

  • Atomicity: Atomicity is a process when one part of any transaction fails, the entire transaction fails and the database state is left unchanged.
  • Consistency: Consistency ensures that the data never leaves the database without completing its state, it should complete all the validation process.
  • Isolation: Isolation helps in concurrency control.
  • Durability: Durability has no obstacle, it will occur in any condition such as power loss, crash, or any sort of error.
  1. What do you mean by “Trigger” in SQL?
  • Triggers in SQL are a special type of stored procedure that allows you to execute a batch of code when an insert, update, or any other query is executed against a specific table automatically.
  1. What are the different operators available in SQL?
  • Arithmetic Operators
  • Logical Operators
  • Comparison Operators
  1. Are NULL values the same as that of zero or a blank space? 
  • A NULL value represents a value that is unavailable, unknown, assigned, or not applicable whereas zero is a number and blank space is a character.
  1. What are Constraints in SQL?

Constraints are a set of rules concerning data in the table. Using ALTER and TABLE command constraints can be applied for single or multiple fields in SQL.

The constraints are:

  • Not Null
  • Check
  • Default
  • Unique
  • Index
  • Primary Key
  • Foreign Key
  1. What is a UNIQUE constraint?
  • A UNIQUE constraint helps in determining whether all the values in a column are different. This helps in identifying each row uniquely.
  1. What are UNION, MINUS, and INTERSECT commands?
  • The UNION is a  binary set operator that combines the result set of two select queries

Syntax
SELECT (coloumn_names) from table1 [WHERE condition] UNION SELECT (coloumn_names) from table2 [WHERE condition];

  • The MINUS is a binary set  operator in SQL that performs minus operation between two selections returns the rows that are present in the first selection but not in the second selection

Syntax
SELECT (coloumn_names) from table1 [WHERE condition] MINUS SELECT (coloumn_names) from table2 [WHERE condition];

  • The INTERSECT  clause in SQL that helps in  intersection operation between two selections

Syntax

SELECT (coloumn_names) from table1[WHERE condition] INTERSECT SELECT (coloumn_names) from table2 [WHERE condition];

  1. What are Entities and Relationships?
  • Entity: An entity can be either tangible or intangible and this is a real-world object
    For example- In a college database, students, professors, departments are referred to as entities.
  • Relationships: Relations are the entities that have something to do with each other.
    For example – The employee table in a company’s database can be associated with the salary table in the same database.
  1. List the different types of relationships in SQL.
  • One-to-One Relationship
    Eg: Consider there is two entities ‘Person’ (Id, Name, Age, Address)and ‘Passport’(Passport_id, Passport_no). So, one passport- one person.
  • One-to-Many or Many-to-One Relationship
    Eg: Consider a ‘Customer’ and ‘Account’ then each ‘Customer’ can have more than one ‘Account’ but each ‘Account’ is held by only one ‘Customer’.
  • Many-to-Many Relationship
    Eg: Consider a ‘Customer’ and ‘Product’ then each customer can buy more than one product and a product can be bought by many different customers.
  1. What are transactions and their controls?

A transaction is a process that is performed logically to gain certain results. Operations like Creating, updating, deleting records performed in the database come from transactions.

There are 4 transaction controls such as

  • COMMIT: Save all changes made through the transaction.
  • ROLLBACK: All changes made by the transaction are reverted/rolled back but the database remains the same.
  • SET TRANSACTION: Set the name of the transaction.
  • SAVEPOINT: It is used to set the point where the transaction is to be rolled back.
  1. How many Aggregate functions are available in SQL?

SQL Aggregate functions help in calculating values from multiple columns in a table and return a single value.

There are 7 aggregate functions in SQL:

  • AVG(): Returns the average value from specified columns.
  • COUNT(): Returns the number of table rows.
  • MAX(): Returns the largest value among the records.
  • MIN(): Returns smallest value among the records.
  • SUM(): Returns the sum of specified column values.
  • FIRST(): Returns the first value.
  • LAST(): Returns last value.

This brings us to the end of the SQL Interview questions.

Wondering where to learn SQL certification for free?

Check out this SQL Certification for free at Great learning academy.

 

askcorran

About Us CONTACT US DISCLAIMER PRIVACY POLICY TERMS AND CONDITIONS WRITE FOR US