Sql Interview Questions For Testing

What Are Composite Keys

Top 15 SQL Interview Questions and Answers for Software Testing professionals || Part-A

A composite key is the combination of two or more columns in a table used to identify a row in a table. Know that a combination of columns is essential in creating composite keys because a single column in a composite key cannot identify a row in a table. We can say that the composite key is the primary key with a few more attributes or columns. Also, a composite key can be a combination of candidate keys.

What Are Aggregate And Scalar Functions

Aggregate functions are used to evaluate mathematical calculation and return single values. This can be calculated from the columns in a table. Scalar functions return a single value based on the input value.

Example -.

Aggregate max, count Calculated with respect to numeric.

Scalar UCASE, NOW Calculated with respect to strings.

What Are Database Normalisation And Various Forms Of Normalisation

It is the process that reduces data redundancy and improves data integrity by restructuring the relational database.

The following are the different forms of normalization:

First normal form 1NF
Second normal form 2 NF
Third normal form 3 NF
Boyce Codd Normal Form/Fourth Normal form BCNF/4NF

Also Check: What Are Some Job Interview Questions

What Are The Set Operators In Sql

We use the set operators to merge data from one or more tables of the same kind. Although the set operators are like SQL joins, there is a significant distinction. SQL joins combine columns from separate tables, whereas SQL set operators combine rows from different queries. SQL queries that contain set operations are called compound queries. The set operators in SQL are categories into four different types:

A. UNION: It combines two or more results from multiple SELECT queries into a single result set. It has a default feature to remove the duplicate rows from the tables. The following syntax illustrates the Union operator:

B. UNION ALL: This operator is similar to the Union operator, but it does not remove the duplicate rows from the output of the SELECT statements. The following syntax illustrates the UNION ALL operator:

C. INTERSECT: This operator returns the common records from two or more SELECT statements. It always retrieves unique records and arranges them in ascending order by default. Here, the number of columns and data types should be the same. The following syntax illustrates the INTERSECT operator:

D. MINUS: This operator returns the records from the first query, which is not found in the second query. It does not return duplicate values. The following syntax illustrates the MINUS operator:

To read more information, .

Sql Interview Questions: Defining Sql Terms

Software Testing: SQL Interview Questions and Answers

Sometimes, the initial interview screen will involve conceptual questions to test whether you have sufficient knowledge of SQL and how it relates to databases. This may involve questions as basic as listing the types of joins in SQL, what a common table expression is , how window functions work, what an index is, or trickier questions like the difference between similar clauses .

Try not to overlook basic questions like these when preparing for SQL interviews, as companies want to ensure that your knowledge of SQL is not surface-level or overly focused on writing queries without understanding how it fits into the world of database management.

Don’t Miss: Interviewing Skills Training Course Outline

What Are Superkey And Candidate Key

A super key may be a single or a combination of keys that help to identify a record in a table. Know that Super keys can have one or more attributes, even though all the attributes are not necessary to identify the records.

A candidate key is the subset of Superkey, which can have one or more than one attributes to identify records in a table. Unlike Superkey, all the attributes of the candidate key must be helpful to identify the records.

Note that all the candidate keys can be Super keys, but all the super keys cannot be candidate keys.

How Can You Say That Database Testing Is Different From That Of Gui Testing

View answer

Hide answer

GUI testing is always performed at the front end whereas the Database testing is performed at the back end When it comes to dealing with the testable items, generally the users prefer GUI testing. These items are present clearly./ On the other hand, the Database testing deals with the testable items that are hidden and are not directly visible to the users Structured Query Language largely matters in Database approach where the same doesnt have any application with the GUI Invalidating the test boxes are a part of GUI database whereas the Database testing is totally different in this manner

Don’t Miss: An Interview With God Explained

Basic Sql Interview Questions

All set to kickstart your career in SQL? Look no further and start your professional career with these SQL interview questions for freshers. We will start with the basics and slowly move towards slightly advanced questions to set the pace. If you are an experienced professional, this section will help you brush up on your SQL skills.

What Is The Usage Of The Nvl Function

ETL Testing | Complex SQL Interview Questions & Answers | Correlated subquery and decode function

The NVL function is used to convert the NULL value to the other value. The function returns the value of the second parameter if the first parameter is NULL. If the first parameter is anything other than NULL, it is left unchanged. This function is used in Oracle, not in SQL and MySQL. Instead of NVL function, MySQL have IFNULL and SQL Server have ISNULL function.

Don’t Miss: How To Prepare For A Job Interview

Q20 Write A Query To Retrieve Two Minimum And Maximum Salaries From The Employeeposition Table

To retrieve two minimum salaries, you can write a query as below:

SELECT DISTINCT Salary FROM EmployeePosition E1  WHERE 2 > = FROM EmployeePosition E2   WHERE E1.Salary > = E2.Salary) ORDER BY E1.Salary DESC

To retrieve two maximum salaries, you can write a query as below:

SELECT DISTINCT Salary FROM EmployeePosition E1  WHERE 2 > =  FROM EmployeePosition E2   WHERE E1.Salary < = E2.Salary) ORDER BY E1.Salary DESC

What Are Database Testing And Its Benefits

Database testing is also known as back-end testing. It consists of the SQL queries executed to validate database operations, data structures, and attributes of a database. It helps to ensure the data integrity by eliminating duplicate entries of data in a database, failing which will create many problems while managing the database. Besides, it deals with testable items hidden and not visible to users.

Don’t Miss: How To Crack Software Engineer Interview

What Are The Different Ddl Commands In Sql

DDL commands are used to define or alter the structure of the database.

CREATE: To create databases and database objects
ALTER: To alter existing database objects
DROP: To drop databases and databases objects
TRUNCATE: To remove all records from a table but not its database structure
RENAME: To rename database objects

What Does Schema Mean

TOP 600 SQL SERVER INTERVIEW QUESTIONS ANSWERS EXAMPLES 4 FRESHER AND ...

Sample answer:

A schema refers to a collection of database objectssuch as tables, functions, indexes, and proceduresassociated with a database.

The schema helps segregate database objects for different applications and access rights its generally used to define who can and who cannot view specific objects in the database.

You May Like: How To Say Thanks After Interview

What Is The Use Of Clause In Sql

Clauses are nothing but they are the built-in functions of SQL. They help to retrieve data very quickly and efficiently. Clauses are much-needed for developers when there is a large volume of data in a database. The result set of clauses would be a pattern, group, or an ordered format.

The following are the various clauses used in SQL:

It has the excellent processing speed

How Would You Write A Sql Query To Find Entrants Whose Names Begin With A

Sample answer:

You can retrieve data entries beginning with a particular letter using the LIKE command by following these steps:

Use the SELECT statement to specify the column with the names you want to vet
Use the FROM statement to specify the table containing that column
Use WHERE column name followed by LIKE x%, with x representing the letter you are searching for
Use ORDER by column name to complete the query

Solution Of Sql Interview Question #1

The solution code is:

SELECT authors.author_name, SUM AS sold_sumFROM authorsJOIN booksON books.book_name = authors.book_nameGROUP BY authors.author_nameORDER BY sold_sum DESCLIMIT 3

And here is a short explanation:

1. First you have to initiate the JOIN. I joined the two tables by using:

SELECT *FROM authorsJOIN booksON books.book_name = authors.book_name

2. After that, I used a SUM function with a GROUP BY clause. This means that in the SELECT statement I had to replace the * with the author_name and sold_copies columns.

3. Eventually, I ORDERed the results in DESCending order.

At Which Stage Of The Hiring Process Should You Use Sql Server Interview Questions

The interview is one of the last and most important stages in the recruitment process. In it, you assess both soft and hard skills, so you need to choose your interview questions wisely: they give you the opportunity to assess candidates SQL skills and behavior. Finding the right match on both fronts is imperative.

The questions above cover the first part, and allow you to accurately assess candidates SQL coding skills.

SQL requirements will vary depending on the database system used by your organization. With this in mind, tailor your questions where possible to the requirements of the role. Its also possible to tailor your selection process in its early stages by incorporating a targeted SQL skills assessment.

SQL skills assessments are an invaluable resource as they allow you to gauge the candidates competency first-handsomething that interview questions are unable to offer. TestGorilla offers several SQL assessments tailored to different business requirements and ability levels:

Don’t Miss: What’s An Exit Interview

What Is Inner Join In Sql

Inner join returns only those records from the tables that match the specified condition and hides other rows and columns. In simple words, it fetches rows when there is at least one match of rows between the tables is found. INNER JOIN keyword joins the matching records from two tables. It is assumed as a default join, so it is optional to use the INNER keyword with the query.

The below visual representation explain this join more clearly:

The following syntax illustrates the INNER JOIN:

To read more information, .

What Is The Difference Between Local Variables And Global Variables

Pl/SQL Interview questions asked In Accenture at 3 years experience ...

Local Variables: Local variables can be used or exist only inside the function. These variables are not used or referred by any other functions. These are not known to other functions. Variables can be created whenever that function is called.

Global Variables: Global variables can be used or exist throughout the program. Same variable declared in global cannot be used in functions. Global variables cannot be created whenever that function is called.

Recommended Reading: How To Prepare For My First Interview

Advanced Sql Mcq Questions And Answers

This section provides multiple-choice questions and answers based on advanced query optimization.

1) What type of join do you need when you want to include rows with values that don’t match?

Answer: b

Explanation: Outer Join returns all rows that match the specified condition, including unmatched rows from one or both tables. Hence, option is the right answer because the outer join is the only join that can display unmatched records.

2) Which of the following option matched a CASE SQL statement?

A way to establish an IF-THEN-ELSE in SQL.

A way to establish a loop in SQL.

A way to establish a data definition in SQL.

None of the above.

Answer: a

Explanation: The CASE expression is a control flow function that evaluates a set of conditions and displays the output when the first condition is met. It is primarily used to handle conditional statements, same as IF-THEN-ELSE statements in other programming languages. Hence, option is the right answer.

3) Which of the following is an illegal data type in SQL?

Answer: d

4) The view is updated immediately if the actual relations used in the view definition change. These views are referred to as _________.

Answer: d

Explanation: Persistent Storage Module is a feature that allows users to extend the basic SQL functionality with their own additions.

6) Which of the following is true regarding a correlated subquery?

Uses the result of an outer query to determine the processing of an inner query.

Answer: a

Answer: b

All of the above

What Is The Difference Between Delete And Truncate Commands

DELETE: This query is used to delete or remove one or more existing tables.
TRUNCATE: This statement deletes all the data from inside a table.

The difference between DELETE and TRUNCATE commands are as follows:

TRUNCATE is a DDL command, and DELETE is a DML command.
With TRUNCATE, we cannot really execute and trigger, while with DELETE, we can accomplish a trigger.
If a table is referenced by foreign key constraints, then TRUNCATE will not work. So, if we have a foreign key, then we have to use the DELETE command.

The syntax for the DELETE command:

DELETE FROM table_name

Example:

This deletes all the records from a table.

Don’t Miss: Interview Questions For Payroll Coordinator

Q32 How To Create A Temp Table In Sql Server

Temporary tables are created in TempDB and are erased automatically after the last connection is closed. We may use Temporary Tables to store and process interim results. When we need to store temporary data, temporary tables come in handy. The following is the syntax for creating a Temporary Table:CREATE TABLE #Employee ) INSERT INTO #Employee VALUES ,

Lets move to the next question in this SQL Interview Questions.

Q33. How to install SQL Server in Windows 11?

Install SQL Server Management Studio In Windows 11

Step 1: Click on SSMS, which will take you to the SQL Server Management Studio page.

Step 2: Moreover, click on the SQL Server Management Studio link and tap on Save File.

Step 3: Save this file to your local drive and go to the folder.

Step 4: The setup window will appear, and here you can choose the location where you want to save the file.Step 5: Click on Install.Step 6: Close the window after the installation is complete.Step 7: Furthermore, go back to your Start Menu and search for SQL server management studio.

Step 8: Furthermore, double-click on it, and the login page will appear once it shows up.

Step 9: You should be able to see your server name. However, if thats not visible, click on the drop-down arrow on the server and tap on Browse.

Step 10: Choose your SQL server and click on Connect.

After that, the SQL server will connect, and Windows 11 will run good.

What Is The Difference Between Delete Truncate And Drop Command

ETL Testing | SQL Interview Questions on date using to_char and add_months functions

The difference between the Delete, Truncate and Drop command is

Delete command is a DML command, it is used to delete rows from a table. It can be rolled back.
Truncate is a DDL command, it is used to delete all the rows from the table and free the space containing the table. It cant be rolled back.
Drop is a DDL command, it removes the complete data along with the table structure. All the tables rows, indexes, and privileges will also be removed.

Recommended Reading: How To Make A Short Interview Video

Subqueries Common Table Expressions & Window Functions

Subquery

A subquery is a query found within the query. It can occur in a SELECT clause, FROM clause, or WHERE clause.

A CTE or a Common Table Expression is a temporary result set returned by a query and used by another query. In that way, itâs similar to subquery. But the main difference is CTE can be named and can reference itself.

Window functions

The window functions are SQL functions performing calculations over the defined set of rows . Compared to the aggregate functions, which return a single value as a result, the window functions allow you to add the aggregated value to each row in a separate column. This means the rows are not grouped and all the rows are kept as a query result. The window functions are:

Write The Sql Query To Get The Third Maximum Salary Of An Employee From A Table Named Employees

The following query is the simplest way to get the third maximum salary of an employee:

Here is the demo example that shows how to get the third maximum salary of an employee.

The following are the alternative way to get the third-highest salary of an employee:

A. Using LIMIT Keyword

B. Using Subquery

C. Using TOP Keyword

Don’t Miss: How To Interview A Financial Planner