Top SQL Interview Questions in 2021 (Intermediate)

Top SQL Interview Questions in 2021 (Intermediate)
Top SQL Interview Questions in 2021 (Intermediate)

Introduction

SQL(Structured Query Language), which is directly related to DBMS(Database Management System), is a topic that is undoubtedly asked in the interview rounds.

In this blog, the focus is on intermediate-level SQL Interview questions, and in the previous blog, we have covered the SQL Questions for the beginner level. In continuation with this blog, we also have a third part consisting of advanced-level questions.

Intermediate Level SQL Interview Questions

Q1. Name 5 SQL statements along with the command(s) they entail.

Ans: The 5 SQL statements, along with their commands, are given below:

1. DQL – Data Query Language is used to fetch data from a database. The commands used in it are:

  • SELECT Statement – This statement selects the attributes based on the condition described by the WHERE clause.

2. DML – Data Manipulation Language. DML is used for the manipulation of the data itself. The commands used in it are:

  • INSERT Statement – This command is used to insert data into the row of a table.
  • UPDATE Statement – This helps in updating the existing data present in the column.
  • DELETE Statement – The DELETE command is used to delete rows from the table.

 3. DDL – Data Definition Language helps define schema or database structure and defines the structure that holds the data. The commands used in it are:

  • CREATE Statement – It helps in defining the structure schema.
  • ALTER Statement – With this command, we can alter the structure of the database.
  • DROP Statement – This command is used to remove the tables from the database.
  • TRUNCATE Statement – This helps in deleting all rows from the table.

 4. DCL – Data Control Language is used to control the visibility of data. The commands used in it are:

  •  GRANT Statement – This command gives user access privileges. 
  •  REVOKE Statement – It is used to take back the privileges from the user.   

5. TCL – Transaction Control Language helps to deal with transactions within the database. The commands used in it are:

  • COMMIT Statement – Commit command is used to save all the changes in the database.
  • ROLLBACK Statement – It is used to undo the changes in case of a failure of a transaction.
  • SAVEPOINT Statement  – It helps in setting the savepoint two within a transaction.

Q2. Explain DDL in brief, along with functions.

blog banner 1

 Ans: DDL: These are the commands used to define the structure of the table you create. Its functions are as follows:

1. CREATE: Used for creating database objects, such as Views, Tables, Indexes, and Synonyms.

Syntax-
“ Create table table_name(columname1 datatype(size), columname2 datatype(size),....); ”  

2. ALTER: Used for changing the existing structure of the table. It entails the following commands:

  • Add: Used for adding columns to a pre-existing table.
Syntax:
“   Alter table table_name add(columnname1 datatype(size), columname2 datatype(size),....);   ”
  •  Modify: Used for changing datatype size or column datatype.
Syntax:
“   Alter table table_name modify(columnname1 datatype(size), columnname2 datatype(size),....);   ”
  • Drop: Used for dropping columns from the table. It has 2 ways of execution for dropping a single column at a time. (Note: all columns cannot be dropped in the table)
Syntax 1: (without using parentheses)
“   alter table   table_namedrop column   col_name1;  -- drop ONE column   ”
Syntax 2: (by using parentheses)
“   alter table table_name drop(column_name_list);   ”

3. DROP: Used for removing database objects from the database.

Syntax:
“   Drop object object_name;   ”
    (or)
“   Drop table table_name;   ” 
    (or)
“   Drop view view_name;   ”

4. RENAME: As the name suggests, it is used for renaming the table.

Syntax:
 Rename old table_name to new table_name;
Renaming a column:
 Syntax:
 “   Alter table table_name rename column old column_name to new column_name;  ”

5. TRUNCATE: As introduced in Oracle 7.0, Truncate is used for deleting all the rows permanently.

Syntax:

TRUNCATE TABLE table_name;

 Q3. Define Stored Procedure?

Ans: Stored procedures are the prepared SQL codes that can be saved and used repeatedly. Hence if there is a query that’s used multiple times, a Stored Procedure can be used.

Q4. Differentiate Between Stored Procedure & Functions?

Ans: The difference between Stored Procedure & Functions are:- 

S. No.PointStored ProcedureFunctions
1.DefinitionSet of pre-compiled SQL Statements, which gets executed when called.Takes input from the user and returns only one value of any data type.
2.CompilationOnly one in timeEvery time
3.Execution PlanHas a planDoes not have a plan
4.DML CommandsSupportsDoes not Supports
5.TCL CommandsSupportsDoes not supports
6.Input ParametersMay/ May not haveMust have at least 1
7.Output ParametersHaveDoes not have
8.Procedure in another stored procedureCan store function in anotherCan call a function in another
9.Exception HandlingSupportsDoes Not supports
10.Call a functionAllowedNot allowed

 Q5. Define ‘TERM’ and explain how it is different from ‘INDEX’? 

Ans: Indexes are frequently used in SQL. When it comes to answering queries faster, TERM simply ensures quick retrieval of data and relevant information from the table contained in the database. The index can be created on a single column or a group of the same.

Q6. Define Cross Join.

Ans: – The Cartesian product of two tables included in the join is called Cross Join. The resultant table contains rows same in-number as in the-cross-product of no. of rows-in two-tables.

Note: If the WHERE clause is used in cross join, the query will work like an INNER-JOIN.

cross_join
Source: Cross join

Q7. Define ‘Index’ with types.

Ans: Index is a database structure that is used to improve the performance of database activity. A database table can have one or more indexes associated with it. A field expression defines the index that the user can specify when they create the index. Typically, the field expression is a single field name, like OFFICE_EMP_ID.

Indexes can be created and differentiated for different purposes:

1. Unique and Non-Unique Index:

Unique indexes are indexes that maintain data integrity. It ensures that two rows in a table do not have identical vital values. Once a unique index has been used /defined for a table, it is assured that no two rows, whether new or pre-existing, have the same fundamental values. 

Non-unique indexes, on the other hand, are used only to improve the performance of the query. It is achieved by maintaining the data values that are used frequently in a sorted manner.

2. Clustered and Non-Clustered Index:

Clustered indexes-are indexes whose order of the rows in the database-correspond to the order of the rows in the given index. This is why only one clustered index can exist in a given table, whereas multiple-non-clustered-indexes can exist in a table.

The only difference between both types of indexes is that the database manager attempts to keep the data in the same order as the corresponding keys appear in the clustered index.

Clustering indexes can improve the performance of most query operations because they provide-a linear access path to data stored in the database.

Q8. Define Subquery with types.

Ans: – A subquery, also known as a nested query or inner query, is the type of query which resides within another query (as suggested by the name). It is generally used to restrict or enhance any kind of data that is supposed to be queried by the main query, limiting or improving the throughput of the main question.

A subquery is divided into two types: Correlated and Non-Correlated.

1.    Correlated: Although the correlated subquery can’t be termed as an independent query, it can still help refer to the column in a table listed in the FROM of the main query.

2.    Non-Correlated: It can easily be considered as an independent query, and its output is substituted in the main query

Q9. Differentiate between SQL and MYSQL.

 Ans: The following table shows the difference between SQL and MYSQL:

Sr. No.PointSQLMYSQL
1.Full-FormStructured Query LanguageMy Structured Query Language
2.UseTo operate databaseIt is the first open-source database available in the market that allows Data Handling.
3.FunctionsIt’s a DBMS prompter.It allows multi-user access for a large number of databases.
4.TypeQuery language.Database software
5.UpdateFixedFrequent Updates

Q10. Define Cursor and explain how it is used?

Ans: Cursor or a Database Cursor is a control structure used for traversal records in the database. In addition, it also facilitates processing after the traversal—facilities such as addition, retrieval, and deletion of the database records.

Working of SQL Cursor:

  1. DECLARE the cursor.
  2. OPEN cursor to initialize the result set. Call before fetching rows from the result set.
  3. FETCH statements to be retrieved and moved to the next row in the result set.
  4. Call the CLOSE statement to deactivate the cursor.
  5. Finally, use the DEALLOCATE statement to delete the cursor definition and release the associated resources.

Q11. Define the Entities.

Ans: – Entity: Entity can also be a real-world object. It can be either tangible or intangible. For example, in a database called “College,” professors, students, departments, workers, and so on can be entities. Each entity has some associated properties with it. 

Q12. How to use the LIKE operator in SQL? 

Ans: LIKE operator is used to check if an attribute value matches a given string pattern. 

For example:

SELECT * FROM students WHERE first_name like ‘Sarthak’;

With this command, we will extract all the records where the first name is like “Sarthak”.

Q13. How can you insert NULL values in a column while inserting the data? 

Ans: NULL values in a column can be inserted in the following ways: 

  • Implicitly by omitting column from column list.
  • Explicitly by specifying NULL keyword in the VALUES clause

Q 14. What is a join in SQL? 

Ans: Join is a keyword used to query data from more tables based on the relationship between the fields of the tables. Keys play a major role when Joins are used. 

Q15. Explain System R?

Ans. System R is a DBMS (Database Management System). It helps provide a high level of data independence and abstraction. It has data control features like data consistency, triggered transactions, authorization, and integrity assertions.

Q16. What is 2 tier architecture?

Ans. In a database management system, a 2-Tier architecture is an architecture where the User Interface or view layer runs on the client’s machine such as desktop, laptop, tablet, phone, etc. This data is then stored on a server. To connect to the database, clients have to use the ODBC connectivity APIs. This type of architecture helps enhance the security of the database since the client cannot access the database directly.

Q17. Name eight different Database Keys. 

Ans. 8 different keys in DBMS are as follows:

  • Primary Key
  • Super Key
  • Candidate Key
  • Alternate Key
  • Foreign Key
  • Compound Key
  • Composite Key
  • Surrogate Key 

Q18. Explain CLAUSE in terms of SQL?

Ans. In SQL, CLAUSE is an optional statement that defines a condition to present data. Example:

select * from employee WHERE emp_id = 2012;

Q19. How is a Trigger different from a Stored Procedure?

 Ans. A Stored procedure can be called explicitly by the user. It can take certain parameters and can return such values. Like any other program, a Trigger is called spontaneously (automatically) when a specific event occurs. It cannot take any values from the user.

Q20.  Differentiate between network and hierarchical database models

Ans: The table below shows the difference between network and hierarchical database models:

Sr. No.Points of DifferenceNetwork DB ModelHierarchical DB model
1.Relationship typeParent-childPointers or links
2.InconsistenciesMay happenNo inconsistencies
3.Many to Many RelationshipsDoes not supportDoes Supports
4.StructureTreeGraph

Q21. Explain Index Hunting. 

Ans. It’s a database that helps in speedy data retrieval for any kind of activity on the database. This process of speeding up data retrieval and collection of indexes is known as Index Hunting. It uses query distribution and query optimization techniques for better retrieval.

CODING (QUERY) BASED QUESTIONS

Q22. Get the current date WITHOUT using GETDATE.

SELECT SYSDATE();

Q23. Get the first five characters of  Emp_name from the EmployeeInfo table.

SELECT SUBSTRING(Emp_name, 1, 5) FROM EmployeeInfo;

Q24. Get ONLY the place’s name from the Address column of ‘EmployeeInfo’.

SELECT MID(Address, 0, LOCATE('(',Address)) FROM EmployeeInfo;

Q25. Write a command to do the following:

A.   Number of employees whose date of birth is between 16 Jan 1998 to 22 Dec 2002.

B.   Group them according to their Gender

C.   Extract from the Table named ‘Students..’’

 SELECT COUNT(*), Gender FROM Students WHERE DOB BETWEEN '16/01/1998 ' AND '22/12/202' GROUP BY Gender;

Q26. You are provided with the list called ‘MENULIST’ with a list of dishes that restaurants across the city are serving. From the said list, retrieve two least expensive and two most costly dishes.

Ans:

A.   Least expensive two dishes:

SELECT DISTINCT Cost FROM MENULIST M1
WHERE 2 >= (SELECT COUNT(DISTINCT Cost)FROM MENU LIST M2
WHERE M1.Cost >= M2.Cost) ORDER BY M1.Cost DESC;

B.   Most expensive 2 dishes:

SELECT DISTINCT Cost FROM MENULIST M1
WHERE 2 >= (SELECT COUNT(DISTINCT Cost)FROM MENU LIST M2
WHERE M1.Cost <= M2.Cost) ORDER BY M1.Cost DESC;

Q27. Find and extract duplicate records from a table with such parameters:

Student (Std_ID, Course, Yr_Graduation) 

SELECT Std_ID, Course, Yr_Graduation COUNT(*)
FROM Student GROUP BY Std_ID, Course, Yr_Graduation
HAVING COUNT(*) > 1;

Q28. Find Nth Ranking student in JEE. Without using the TOP keyword or the limit keyword.

SELECT Rank
FROM JEERank J1
WHERE N-1 = (
      SELECT COUNT( DISTINCT ( J2.Rank ) )
      FROM JEERank J2
      WHERE J2.Rank >  J1.Rank );

Q29. Add Email validation to the database.

SELECT Email FROM Std_Info WHERE NOT REGEXP_LIKE(Email, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+.[A-Z]{2,4}’, ‘i’);

Q30. Write a query to fetch N% records from the EmployeeInfo table.

SELECT 
*FROM EmployeeInfo WHERE
EmpID <= (SELECT COUNT(N/EmpID)*100 from EmployeeInfo);

Key Takeaways

This blog covered a mixture of theory and query-based SQL interview questions. After going through the questions we have covered in this blog and the last part, i.e., SQL Interview Questions for beginners, you can check out Part 3, which contains the advanced-level questions. Now, you will be able to ace your interviews. 

You can also check out our course on DBMS, which will help you get interviews ready.

By: Yogesh Kumar