Sql Test Questions For Interview

Q20 What Is The Difference Between The Rank And Dense: Rank Functions

The RANK function in the result set defines the rank of each row within your ordered partition. If both rows have the same rank, the next number in the ranking will be the previous rank plus a number of duplicates. If we have three records at rank 4, for example, the next level indicated is 7.

The DENSE_RANK function assigns a distinct rank to each row within a partition based on the provided column value, with no gaps. It always indicates a ranking in order of precedence. This function will assign the same rank to the two rows if they have the same rank, with the next rank being the next consecutive number. If we have three records at rank 4, for example, the next level indicated is 5.

Q21. What are Tables and Fields?

A table is a collection of data components organized in rows and columns in a relational database. A table can also be thought of as a useful representation of relationships. The most basic form of data storage is the table. An example of an Employee table is shown below.

A Record or Row is a single entry in a table. In a table, a record represents a collection of connected data. The Employee table, for example, has four records.

A table is made up of numerous records , each of which can be split down into smaller units called Fields. ID, Name, Department, and Salary are the four fields in the Employee table above.

What Do You Mean By Etl In Sql

ETL in SQL represents Extract, Transform and Load.

Extracting It is about extracting data from the source, which can be a data warehouse, CRMs, databases, etc.

Transforming It includes many processes such as cleansing, standardization, deduplication, verification, and sorting.

Loading It is the process of loading the transformed data into the new destination. There are two types of loading data: full loading and incremental loading.

What Are Stored Procedures

It is a function that consists of a group of statements that can be stored and executed whenever it is required. Know that stored procedures are compiled only once. They are stored as Named Object in the SQL server database. Stored procedures can be called at any time during program execution. Moreover, a stored procedure can be called another stored procedure.

Also Check: What To Say When Scheduling An Interview

What Are The Differences Between Sql Mysql And Sql Server

The following comparison chart explains their main differences:

How To Create A Table In Sql

The command to create a table in SQL is extremely simple:

 CREATE TABLE table_name  

We will start off by giving the keywords, CREATE TABLE, and then we will give the name of the table. After that in braces, we will list out all the columns along with their data types.

For example, if we want to create a simple employee table:

CREATE TABLE employee ,age int,gender varchar,   ....) 

You May Like: Java Programming Questions For Interview

Explain The Types Of Indexes

Single-column Indexes: A single-column index is created for only one column of a table.

Syntax:

CREATE INDEX index_nameON table_name 

Composite-column Indexes: A composite-column index is created for two or more columns of a table.

Syntax:

CREATE INDEX index_nameON table_name 

Unique Indexes: A unique index is used for maintaining the data integrity of a table. A unique index does not allow multiple values to be inserted into the table.

Syntax:

CREATE UNIQUE INDEX indexON table_name

Courses you may like

Joining Tables & Queries

Inner join

An Inner join returns only those rows where the data from one table matches the data from the second table.

Left outer join

The left join is a table join that will retrieve all the rows from the left table and only the matching rows from the right table.

Right outer join

This join is the one that returns all the rows from the right table and only the matching rows from the left table.

Full outer join

The full outer join will join the data so that the result will include all the rows from one table and all the rows from the second table.

Cross join

This results in a Cartesian product. This means it will return all the combinations of rows from one table with all the combinations of rows from the other table.

Union

This is an SQL command that will combine the result of one query with the result of another query. Therefore, it will show only unique records.

Union all

This one also combines the results from two or more queries. The difference between UNION and UNION ALL is it will also include duplicates.

You May Like: What Are Interview Questions For Accounts Payable

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.

What Is A Dbms

DBMS stands for ‘database management system,’ which is a program used to help maintain, monitor, create, deploy and control the use of a database. It also acts as a file manager for the data kept in a database, no matter the size or complexity of the database.

Read more: What Is a Database Management System?

Also Check: How To Nail Amazon Interview

What Is Sql Injection

SQL injection is a type of vulnerability in website and web app code that allows attackers to control back-end operations and access, retrieve, and destroy sensitive data from databases. In this technique, malicious SQL statements are inserted into a database entry field, and once they are performed, the database becomes vulnerable to an attacker. This technique is commonly used to access sensitive data and perform administrative activities on databases by exploiting data-driven applications. It is also known as SQLi attack.

Some common examples of SQL injection are:

• Accessing confidential data to modify an SQL query to get desired results.
• UNION attacks to steal data from different database tables.
• Examine the database to extract information regarding the version and structure of the database.

Advanced Sql Whiteboard Questions

OK, so weâve already had some SQL practice for interviews but our exercises covered SQL basics. But what about a more advanced job, like SQL developer or database administrator? Recruiters will be interested in more than just your SQL knowledge they will also need to know how savvy you are with databases in general. In that case, see how well you can verbally answer SQL whiteboard questions like these:

• What are primary and foreign keys?
• What is normalization? What is denormalization?
• What is an index? Can you briefly explain the different index types?
• Can you explain database relationships and relationship types?
• What is a database cursor?
• What is a constraint?

Read Also: How To Answer Questions During An Interview

Who This Course Is For:

• Developers preparing for IT Interview and use SQL as backend
• Developers working mainly on SQL
• Anyone working on IT Projects and want to test SQL skills
• Anyone looking for a job and mentions SQL in their skillset

• 4.2 Instructor Rating

Greetings to all! I have 14+ years of experience working in IT Industry. I am an experienced Freelance Instructor and Career Counsellor. I frequently conduct online Job Search training sessions and conduct Mock Interviews for different IT roles. I am skilled in Agile, Scrum, Microsoft Technologies – C#, ASP.NET, Visual Studio, SQL Server and Azure DevOps.

What Are The Different Types Of Keys In Sql And When Do You Use Them

You can use this technical question to gauge whether an applicant has the right amount of SQL knowledge. Keys are a relatively basic concept in SQL, so anyone with experience in this technology should know what they are. What to look for in an answer:

• Strong understanding of what keys are and how theyre used in SQL
• Skill at appropriately using keys in SQL
• Ability to explain the keys concept in an interview environment

Example:

A unique key is a unique field or column that a table doesnt share with any other table in the SQL database. I use this key to create a unique identifier for each table for easier retrieval later.

You May Like: System Design Interview Url Shortener

How Can You Avoid Duplicate Keys In Sql

We can eliminate duplicate keys in SQL by using the following methods:

• Using INSERT INTO SELECT

• Code compilation

76. Differentiate: UNION and INTERSECT statements?

UNION: It is the operator that returns a single result set for two separate queries. And this operator functions based on specific conditions.

Syntax: query 1 UNION query2

INTERSECT: It is the operator that returns only the distinct rows from two separate queries.

Syntax: query 1 INTERSECT query2

Explain Union Minus Union All Intersect

INTERSECT returns all distinct rows selected by both queries.MINUS: returns all distinct rows selected by the first query but not by the second.

UNION: returns all distinct rows selected by either query

UNION ALL: returns all rows selected by either query, including all duplicates

Also Check: How To Ace Project Manager Interview

How Can You Update A Table Using Sql

The UPDATE statement allows you to update a database table in SQL. After the execution, one or more columns in a table will be replaced by new values.

The syntax for the UPDATE statement is given as follows:

You can use the following statement to run the newly created stored procedure.

What Do You Mean By Lock Escalation

It is the process of converting row and page locks into table locks. Know that Reduction of lock escalation would increase the server performance. To improve performance, we need to keep transactions short and reduce lock footprints in queries as low as possible. Besides, we can disable lock escalation at the table and instance levels, but it is not recommended.

Recommended Reading: What To Wear To A Tech Interview

What Is The Difference Between Having And Where Clause

Where clause is used to fetch data from a database that specifies particular criteria whereas a Having clause is used along with GROUP BY to fetch data that meets particular criteria specified by the Aggregate functions. Where clause cannot be used with Aggregate functions, but the Having clause can.

What Is Primary Key In Sql

Primary Key is a constraint in SQL. So, before understanding what exactly is a primary key, lets understand what exactly is a constraint in SQL. Constraints are the rules enforced on data columns on a table. These are used to limit the type of data that can go into a table. Constraints can either be column level or table level.

Lets look at the different types of constraints which are present in SQL:

You can consider the Primary Key constraint to be a combination of UNIQUE and NOT NULL constraint. This means that if a column is set as a primary key, then this particular column cannot have any null values present in it and also all the values present in this column must be unique.

Read Also: Front End Javascript Interview Questions

What Is Alias In Sql

SQL aliases help to assign temporary names for a table or column. It is used to simplify table or column names. And aliases can exist only for that query period. It can be created using the AS keyword. Know that creation of an alias is in no way affecting the column names in the database. It can be applied when more than one table is involved in a query.

Sql Exercise 2 Write A Statement

Write an SQL statement that lists student names, subject names, subject lecturers, and the max amount of points for all subjects except Computer Science and any subjects with a max score between 100 and 200.

Table: subjectsubject_id  subject_name          max_score    lecturer----------  ----------            ----------   -----------   11         Math                  130          Christena Solem  12         Computer Science      150          Jaime Pille  13         Biology               300          Carrol Denmark  14         Geography             220          Yuette Galang  15         Physics               110          Colton Rather  16         Chemistry             400          Nan Mongeau 

Table: studentstudent_id   student_name    city           subject_id-----------  ------------    ----------    -----------2001         Thurman Thorn   New York               112002         Sharda Clemens  San Francisco          122003         Buck Elkins     New York               132004         Fabian Johns    Boston                 152005         Brad Cameron    Stanford               112006         Sofia Roles     Boston                 162007         Rory Pietila    New Haven              12

Read Also: How To Conduct Yourself In An Interview

Q16 What Are Sql Comments

SQL Comments are used to clarify portions of SQL statements and to prevent SQL statements from being executed. Comments are quite important in many programming languages. The comments are not supported by a Microsoft Access database. As a result, the Microsoft Access database is used in the examples in Mozilla Firefox and Microsoft Edge. Single Line Comments: It starts with two consecutive hyphens . Multi-line Comments: It starts with /* and ends with */.

What Is Wrong With The Following Sql Query

SELECT gender, AVG FROM employee WHERE AVG> 30 GROUP BY gender

When this command is executed, it gives the following error:

Msg 147, Level 16, State 1, Line 1

Aggregation may not appear in the WHERE clause unless it is in a subquery contained in the HAVING clause or a select list the column being aggregated is an outer reference.

Msg 147, Level 16, State 1, Line 1Invalid column name gender.

This basically means that whenever we are working with aggregate functions and are using the GROUP BY clause, we cannot use the WHERE clause. Therefore, instead of the WHERE clause, we should use the HAVING clause.

When we are using the HAVING clause, the GROUP BY clause should come first, followed by the HAVING clause.

select e_gender, avg from employee group by e_gender having avg> 30

Output:

Don’t Miss: Data Structures To Know For Coding Interviews

What Do You Mean By Autonomous Transaction

An autonomous transaction is an independent transaction initiated by a transaction that is the main transaction. Autonomous transaction holds the main transaction, performs SQL operations, and commits or rolls back. After that, it resumes the main transaction. Note that autonomous transaction doesnt share locks and resources with the main transaction.

What Is Etl In Sql

ETL stands for Extract, Transform and Load. It is a three-step process, where we would have to start off by extracting the data from sources. Once we collate the data from different sources, what we have is raw data. This raw data has to be transformed into the tidy format, which will come in the second phase. Finally, we would have to load this tidy data into tools which would help us to find insights.

Don’t Miss: What Are The Possible Interview Questions

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.

Explain Inner Join With An Example

Inner Join basically gives us those records that have matching values in two tables.

Let us suppose that we have two tables, Table A and Table B. When we apply Inner Join on these two tables, we will get only those records that are common to both Table A and Table B.

Syntax:

SELECT columnsFROM table1INNER JOIN table2ON table1.column_x=table2.column_y 

Example:

select * from employeeselect * from department

Output:

Now, we will apply Inner Join to both these tables, where the e_dept column in the employee table is equal to the d_name column of the department table.

Syntax:

select employee.e_name, employee.e_dept, department.d_name, department.d_locationfrom employee inner join departmentonemployee.e_dept=department.d_name

Output:

After applying Inner Join, we have only those records where the departments match in both tables. As we can see, the matched departments are Support, Analytics, and Sales.

Recommended Reading: How To Pass A Supervisor Interview