SQL (Structured Query Language) is mostly asked whenever you give your interview related to DBMS(Database Management). So in this blog, we will cover SQL interview Questions of advanced level.
Advanced Level SQL Interview Questions
1. Stepwise, explain the process of removing duplicate rows from any table?
Ans. Let’s select a table and name it STUDENT. Presuming it has duplicating rows, say roll_No. We can use the MAX concept of the table from rowID.
Step 1: Select roll_No FROM Student WHERE rowID <> (Selecting max (rowid) from the Student b where rollno=b.rollno); Step 2: Delete duplicate rows Delete FROM STUDENT WHERE rowID <> (Select max (rowid) from Student b where rollno=b.rollno)
2. Name different types of Triggers in SQL?
Ans. A trigger is a stored procedure that automatically runs or executes when a specific action occurs in the database, such as CREATE, READ, UPDATE and DELETE.
Following are the different types of Triggers in SQL:
a) DML Triggers
b) DDL Triggers
c) Logon Triggers
3. Define Logon Triggers. Mention their use?
Ans. A predetermined set of instructions stored in SQL that is triggered (executed) before the user session every time a logon event occurs are called Logon Triggers.
They are used for security purposes. They help by preventing unauthorized access to the database.
4. Define DML Triggers. Mention their use?
Ans. A pre-determined set of instructions stored in SQL that is triggered (executed) automatically every time a data manipulation language event occurs (INSERT, UPDATE or DELETE) are termed as Data Manipulation Triggers.
They are used for the prevention of malicious attacks. It protects data integrity. They also help in performing complex SQL queries. They are similar to constraints. However, some may consider them to be more complicated.
5. Define DDL Triggers. Mention their use?
Ans. A predetermined set of instructions stored in SQL that is triggered (executed) automatically every time a data definition language event occurs (CREATE, ALTER, GRANT, and DROP) are called Data Manipulation Triggers.
They are used for recording the list of DDL events, thereby helping maintain data integrity.
6. Define ‘Synonym’?
Ans. A synonym permits you to make substitute names for objects within the database. It references the database object only. In the case of another synonym, it does not accept the input. It allows multiple names as long as they directly refer to the database object.
CREATE [OR REPLACE] [PUBLIC] SYNONYM schema.synonym_name
FOR schema. object;
DROP SYNONYM abcdef;
7. Name at least ten commonly used Aggregate Functions?
Ans. Some of the commonly used aggregate functions are as follows:
a) AVG: Find average of a set of values.
Syntax: “ SELECT AVG(columnName) FROM tableName WHERE conditionProvided; ”
b) CHECKSUM_AGG: It applies aggregated checksum to each group.
Syntax: “ SELECT CHECKSUM_AGG(columnName) quantity_checksum_agg FROM tableName; ”
c) COUNT: It counts the number of rows.
Syntax: “ SELECT COUNT(columnName) FROM tableName; ”
d) COUNT_BIG: It returns the number of items in a group. It is like the COUNT function, but it returns a bigInt datatype.
Syntax: “ SELECT COUNT_BIG(columnName) FROM tableName; ”
e) GROUPING: Indicates whether the GROUP_BY’s column is aggregated or not.
Syntax: “ GROUPING(columnName) ”
f) MAX: Finds maximum value in an expression.
Syntax: “ SELECT MAX(columName) FROM tableName ”
g) MIN: Finds minimum value in an expression.
Syntax: “ SELECT MIN(columName) FROM tableName ”
h) SUM: Finds the sum of any given expression.
Syntax: “ SUM(expression) ”
i) STDEV: Helps find the standard variation for the expression.
Syntax: “ SELECT STDEV ([columnName]) FROM [tableName] ”
j) STDEVP: Helps find the standard variation for all the records of a specified column.
Syntax: “ SELECT STDEVP ([columnName]) FROM [tableName] ”
8. Define Scalar Subqueries.
Ans. Scalar Subqueries: Its subquery returns exactly one column and one row of data. A scalar subquery can be used in the following contexts:
a) Removing outer joins.
b) Useful while using Aggregate multiple tables.
c) Insertion in the table depending on the values.
Outer join needs to be removed using scalar subquery as it is:
a) It is more manageable.
b) Increases performance.
9. Explain what you understand by CTE?
Ans. CTE is an abbreviation for Common Table Expression. It helps provide a syntactical option for the developer to work with temporary DS logically. The developer now can use CTE and simplify the logic.
Syntax for CTE is as follows: WITH expression_name [ ( column_name [,…n] ) ] AS ( CTE_query_definition ) SELECT <column_list> FROM expression_name;
10. How do NVL2 and NVL differ in functionalities?
Ans: They can be differentiated on the following basis:
- NVL converts the expression to the target expression. And if the expression contains NULL, it will have the same data type as the return value.
- NVL2 function checks the first expression, and if it is not null, the second expression is returned as a result. But if the first expression is null, then the third expression is produced as a result.
11. Explain SQL Injections prevention of SQL Injection Attacks?
Ans. SQL Injection is the process of making sure that the data from the database is secured.
Prevention from SQL Injection Attacks can be ensured by:
a) Providing proper validations for input fields.
b) Using parameterized queries.
c) Using stored procedures.
d) Using frequent code reviews.
e) Not displaying database error messages in the front end.
12. Differentiate between Scalar Valued Functions & Table Valued Functions in SQL?
|Sr. No.||Points||Scalar Valued Functions||Table-Valued Functions|
|1.||Processing||It will return only one value of any database and processes one row at a time.||It can return multiple rows from the in this, able. Can process only various rows at a time.|
|3.||Syntax||SELECT dbo. funname(values);||SELECT * FROM dbo.funname(values);|
13. Differentiate between Database testing and GUI testing?
Ans. They can be differentiated as:
- Testing: GUI is front end whereas Database is back-end testing.
- User Preference: GUI testing since they’re clearer. Database testing deals with the testable items that are hidden from the users.
- Invalidating test boxes: They’re the GUI database’s technique while the Database testing is different.
- Structured Query Language essentially matters in the Database approach as it does not have an application with the GUI.
14. Differentiate b/w Long & Lob Datatypes?
Ans. They can be differentiated as:
|1.||Size||2 GB of data.||4 GB of data.|
|2.||Content||It can contain only one long column.||Can have more than Lob column.|
|3.||Selection type||Cannot select a Long data type column.||A select Lob Column.|
15. Define Coalesce().
Ans. Coalesce function: It is like the NVL2 function except that the first and second expressions must belong to the same data type.
Use: It helps to return the first non-null values from the list.
Syntax: “ COALESCE(exp1, exp2, ...., exp-n) ”
16. Define Tuple?
Ans. Tuple: It’s an entity type that has attributes that a set of these attributes can represent. Tuples are members’ relations.
It is a single row in a table that carries the relation for that table.
Following is a perfect example of a single record table:
|Sr. No.||Name||ID||Join Year.|
17. What is Normalization?
Ans. Normalization is a method that helps organize the data so that data redundancy will never occur and avoids anomalies in the inserting, updating, and deleting of data.
18. Differentiate Between Views & Materialized Views?
Ans. They can be differentiated as:
|Sr. No.||Points||Views||Materialized Views|
|1.||Storage||It does not store data.||It does store data.|
|3.||Accessibility||Cannot select a Long data type column||A select Lob Column.|
|4.||DML operations||Can perform DML Operation.||Can’t perform DML operation.|
19. Define Data independence?
Ans. Data independence is a database that keeps the data separate from software DS. It helps maintain a level of transparency used for centralized Databases.
Data Independence is a term for a specific DBMS property that causes one to change database schema in a level without changing or interfering with the higher-level schema.
Data Independence is also classified into two types:
a) Physical data independence: It helps separate the conceptual level from the internal or physical level.
b) Logical data independence: It is the ability to change the conceptual scheme without interfering/ changing the External views and External API or programs.
20. Define Data Integrity?
Ans. Data must satisfy the integrity constraints of the system for data Quality. In other words, it is used to maintain the consistency and the accuracy of the said data.
21. Explain the difference between Deadlock and Starvation
- Deadlock is a state of a database system that has two or more transactions. And when each transaction is waiting because a separate transaction is locking the needed data item. On the other hand, Starvation occurs when a process waits for an indefinite period to obtain a required resource.
- Prevention: Starvation Can be prevented using FIFO, while Deadlock can be prevented if one of the processes is locked.
- Prevention is known as LIVED LOCK while Deadlock is called CIRCULAR WAITING.
22. Define Projection?
Ans. Projection is defined on the terms of relation. A projection of any given relationship is nothing but a projection of its specific tuples over a set of attributes. It’s used for viewing the magnitude of characteristics in the resultant relation or to reorder them.
23. What is Encryption?
a) Definition: Encryption is the process of coding or wrapping the data using some algorithm to protect it from unwanted readers.
b) Example: TDE or Transparent Data Encryption is the encryption that the Database, Azure Synapse Analytics data files, Azure SQL Database, and SQL servers use.
c) This kind of encryption is also termed “encrypting the data while resting”.
d) This not only helps in securing and maintaining the data integrity but even helps prevent attacks like MITM (man in the middle attack).
24. Explain the term Cardinality.
Ans. It is the number of times each entity is involved in an instance of a relation. It describes how often an entity can participate in a relationship.
It is of the following types:
b) 1: many
c) many: many
In simple terms, the term cardinality displays the uniqueness of the data. The lower this value is, it indicates more there is the number of duplicated values.
25. Write a query to sort data in STUDENT from LUCKNOW city, having the same course name.
SELECT stdID, coursename FROM STUDENT
WHERE CITY = ‘LUCKNOW’ ORDER BY stdID;
26. Query the list of students already passed.
CREATE [stdList] AS
SELECT stdID, stdName, Course
WHERE Discontinued = Yes;
27. What do you understand by SQL Server Substring?
Ans. The substring is the adjoining characters in any string.
Consider a string ‘Hello there people.’ then the substring in this string can be ‘Hello’ or ‘there’ or ‘hell’ or ‘ople’ etc. i.e. any sequence of adjoining characters. These can be extracted by using SUBSTRING() in SQL Server.
28. Calculate the total cash flow for separate clients.
Ans. Let’s understand this using an example.
SELECT Month, ClientName, Budgeted_cash_flow, SUM (Budgeted_cash_flow) OVER (PARTITION BY client ORDER BY month) AS running_total FROM budget;
29. How do you display Unique keys in SQL?
Ans. The following query can help display unique keys in SQL:
SELECT * FROM Sys.Objects WHERE Type=’uq’
30. Return records from the table for the customers that have ordered > 50 of the product.
SELECT Item FROM Orders WHERE id = ALL (SELECT ID FROM Orders WHERE quantity > 50)
31. What do you understand about the Cartesian Product of tables?
Ans. The result of Cross join is known as the Cartesian Product of the table. For example, on joining two tables having 20 and 30 columns, then the cartesian product will be 20 X 30 = 600 rows.
Here you can see on joining two tables we get as 3 X3 = 9.
32. What are the types of normalization?
Ans: The types of Normalization are:
- First normal form (1NF):-A relation is in 1NF if it contains an atomic value.
- Second normal form (2NF):- A relation will be in 2NF if it is in 1NF and all non-key attributes are fully functionally dependent on the primary key.
- Third normal form (3NF):-A relation will be in 3NF if it is in 2NF and no transition dependency exists.
- Boyce-Codd normal form (BCNF) A relation will be in 4NF if it is in Boyce-Codd normal form and has no multi-valued dependency.
33. What is JOIN in SQL?
Ans:- JOIN clause helps combine rows from two or more tables based on a related column between them.
There are four major types of joins used in SQL:
- Inner join- It is also called SIMPLE JOIN, which returns all rows when there is a match in at least one column from both tables.
- Left join- This returns all rows present in the left table and corresponding rows from a right table.
- Right join- This returns all rows present in the right table and its corresponding rows from a left table.
- Full join- This returns all records whenever there is a match in the left or right table.
Frequently Asked Questions
There are two different types of variables in SQL:
Data Warehousing: It is the system that is used for data analysis and data reporting. In this, the data can be stored from various locations. Hence we always have a central repository that is always ready for usage.
This was the last blog of our series Top SQL interview questions, and in this blog, we covered the advanced-level questions. If you think this blog is a little more advanced, you can begin with the beginner and intermediate SQL interview questions blog we discussed before.
Don’t forget to check our guided path on DBMS (Database Management System).
By: Yogesh Kumar