Sql Interview Questions For Data Science

What Is Sql Server Agent

SQL Server Agent plays an important role in the daily work of SQL Server administrators or DBAs. This is one of the important parts of SQL Server. The aim of the server agent is to easily implement tasks using a scheduler engine that enables the tasks to be performed at scheduled times. SQL Server Agent uses SQL Server to store scheduled management task information.

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

What Are All The Different Types Of Indexes

There are three types of indexes -.

• Unique Index.

This indexing does not allow the field to have duplicate values if the column is unique indexed. Unique index can be applied automatically when primary key is defined.

• Clustered Index.

This type of index reorders the physical order of the table and search based on the key values. Each table can have only one clustered index.

• NonClustered Index.

NonClustered Index does not alter the physical order of the table and maintains logical order of data. Each table can have 999 nonclustered indexes.

Don’t Miss: How To Prepare For A Substitute Teacher Interview

What Is The Point Of Using A Foreign Key Constraint

After you go through the fundamental SQL interview questions, you are likely to be asked something more specific. Therefore, your next task wont be about explaining what SQL constraints and keys mean in general, although you must be very familiar with the concept. You will rather be given the chance to demonstrate your ability to elaborate on a specific type of an SQL constraint the foreign key constraint.

The foreign key constraint comprises a set of rules, or limits, that will ensure that the values in the child and parent tables match. Technically, this means that the foreign key constraint will maintain the referential integrity within the database.

If you want to dig deeper into this subject, here we explain primary, foreign, and unique keys in more detail

Database Modeling Interview Questions

These questions are designed to test how good you are at database design or database modeling. What is meant by that? You need to show the ability to design and build the database from scratch according to the business processes and business needs. This requires a high level of both technical and business knowledge. You will be working with both technical and non-technical colleagues. So, you need to understand both the business side of their requirement and how to, in the soundest way, technically cater to their business needs regarding the data. Generally, this is a process that goes through these steps :

Question Examples

One of the typical questions that occur in the SQL interviews is this one by Audible:

Can you walk us through how you would build a recommendation system?

Answer: Since there is a wide variety of approaches to answer this question, we will leave you to come up with your own way of building one.

The database design question can also include SQL coding, such as :

Write a SQL query to compute a frequency table of a certain attribute involving two joins. What if you want to GROUP or ORDER BY some attribute? What changes would you need to make? How would you account for NULLs?

Answer: Due to the nature of the question, we will let you answer this one on your own.

Don’t Miss: How To Be Good In An Interview

Q9 What Is Cursor How To Use A Cursor

After any variable declaration, DECLARE a cursor. A SELECT Statement must always be coupled with the cursor definition.

To start the result set, move the cursor over it. Before obtaining rows from the result set, the OPEN statement must be executed.

To retrieve and go to the next row in the result set, use the FETCH command.

To disable the cursor, use the CLOSE command.

Finally, use the DEALLOCATE command to remove the cursor definition and free up the resources connected with it.

Whats The Difference Between Inner And Left Join

An SQL join allows you to display a result set that contains fields from two or more tables. Take a look at our Customer-Sale relationship without joins it would be impossible to display the customer name and the total sale amount in one result set. We need joins for that, and the two most widely used ones are INNER and LEFT join.

An inner join fetches only an intersection of two tables in our example that would be only the customers that have made the sale customer_id exists both in customer and sale tables.

A left join fetches all rows from the left table and tries to match them with the rows on the right table. If the value exists only in the left table, the columns from the right table will have the value of NULL. In our example that would mean that we fetch all the customers and match them with their sales, and put nulls to those customers who havent made the sale yet.

Lets examine this visually. Weve added another customer Jane that hasnt placed any orders yet. In the case of an inner join, she isnt displayed in the result set:

Image 4 Inner join demonstration

In the case of a left join, Jane is displayed in the result set, but her values for date of sale and the amount are NULL, as she didnt make any sales yet:

Image 5 Left join demonstration

SQL-wise, you would write these joins as follows:

Also Check: What Questions To Ask As The Interviewer

What Are The Assumptions Required For A Linear Regression

There are four major assumptions.

1. There is a linear relationship between the dependent variables and the regressors, meaning the model you are creating actually fits the data.

2. The errors or residuals of the data are normally distributed and independent from each other. 3. There is minimal multicollinearity between explanatory variables

4. Homoscedasticitythe variance around the regression lineis the same for all values of the predictor variable.

Sql Questions For Data Scientist Role

1. Write a query to copy data from one table to another

2. When is the INITCAP function used in SQL?

The INITCAP function is used when we want the first letter of each word in the string in uppercase and the remaining letters in lowercase.

3. How to write an SQL INSERT query?

4. What is the difference between the LOWER and UPPER case manipulation functions?

The LOWER function returns the string in lower case.

On the other hand, the UPPER function returns the string in uppercase.

5. Define a View in SQL

A view is a virtual table consisting of rows and columns, and fields. The fields are from one or more real tables in the database.

6. How to create a View in SQL?

7. Define Join in SQL.

The Join clause in SQL is used to combine/join rows from two or more tables in the database. The records are joined on the basis of a related column between the tables.

8. What is the difference between Left Join and Right Join?

Left Join is used toextract all the rows from the left and the matched rows from the right table.

On the other hand, a right join is used to retrieve the records from the right table and matched records from the left.

9. What is a Self-Join?

In a self join a table is joined to itself on the basis of some relation between its own column. Self-join is a regular join and uses the INNER JOIN or LEFT JOIN clause.

10. What is a Cross-Join?

11. What are the data types in PL/SQL?

12. Write a PL/SQL block to display records in the emp table using cursor.

Read Also: How To Make Resume For Job Interview

Write A Query That Returns All Of The Neighborhoods That Have Zero Users

More Context: You are given two tables: the first is a users table with demographic information and the neighborhoods they live in, and the second is a neighborhoods table.

This is an intermediate SQL problem that requires you to write a simple query. Our task is to find all the neighborhoods without users. To reframe the task, you need all the neighborhoods that do not have a single user living in them. This means you have to introduce a column in one table, but not in the other, such that you can see user counts by neighborhood.

What Do You Know About The Stuff Function

The stuff function deletes a part of the string and then inserts another part into the string, starting at a specified position.

Syntax:

STUFF

Here, String1 is the one that will be overwritten. Position indicates the starting location for overwriting the string. Length is the length of the substitute string, and String2 is the string that will overwrite String1.

Example:

select stuff

This will change SQL Tutorial to Python Tutorial

Output:

Python Tutorial

Don’t Miss: How To Prepare For Medical Assistant Interview

What Command Would You Use To Update Data In A Table

You update an existing table with the UPDATE command in SQL. It is used with SET and WHERE to select the specific instance.

Example: In the table Employees, you want to change the emergency contact, ContactName, for an employee with EmployeeID 3.

UPDATEEmployeesSETContactName="Bob Smith"WHEREEmployeeID=3 
What Certificate Will I Receive
       
Upon successful completion of this program in Chicago, you will be awarded a Post Graduate Program in Data Analytics certification by Purdue University. You will also receive industry-recognized certificates from IBM and Simplilearn for the courses on the learning path after successful course completion.
Recommended Reading: How To Pass A Coding Interview 
What Is The Admission Process For This Post Graduate Program In Data Analytics In Chicago
The admission process consists of three simple steps:
All interested candidates are required to apply through the online application form
An admission panel will shortlist the candidates based on their application
An offer of admission will be made to the selected candidates and is accepted by the candidates by paying the program fee
Does Qtp Support Sql Queries
QTP doesnt have built-in functionality for database connectivity, but VBScript language can be used to connect and interact with databases using ADODB objects, divided into four methods:


Connection: forms a connection with a database


Command: executes an SQL command


Fields: retrieves the correct column from a record


Recordset: retrieves data from a database


Read Also: Where To Buy Clothes For Interview 
How Do You Use A Confusion Matrix To Calculate Accuracy
There are four terms to be aware of related to confusion matrices. They are: 
True positives : When a positive outcome was predicted and the result came positive 
True negatives : What a negative outcome was predicted and the result turned out negative
False positives : When a positive outcome was predicted but the result is negative
False negative : When a negative outcome was predicted but the result is positive
The accuracy of a model can be calculated using a confusion matrix using the formula: 
Accuracy = TP + TN / TP + TN + FP + FN
When Would You Use The Group By Statement
    SQL Interview Questions And Answers For Data Science | SQL Interview Preparation | Simplilearn   
The GROUP BY clause enables us to compute aggregate values alongside discrete data by collapsing the distinct rows of our database into summary rows that share a particular characteristic.
If you were to attempt to formulate a query selecting a number of rows with a WHERE clause, and then display an aggregate value alongside, you would find that the query would return an error. This is because SQL is not able to display, in a single table, the results of our WHERE query as a list of values conjoined with the aggregate value you are looking for.
Also Check: Where Can I Watch Oprah’s Interview With Meghan And Harry 
How To Delete A Row In Sql
We will be using the DELETE query to delete existing rows from the table:
DELETE FROM table_nameWHERE  
We will start off by giving the keywords DELETE FROM, then we will give the name of the table, after that we will give the WHERE clause and give the condition on the basis of which we would want to delete a row.
For example, from the employee table, if we would like to delete all the rows, where the age of the employee is equal to 25, then this will the command:
DELETE FROM employeeWHERE  
What Do You Understand About A Temporary Table Write A Query To Create A Temporary Table
A temporary table helps us store and process intermediate results. Temporary tables are created and can be automatically deleted when they are no longer used. They are very useful in places where temporary data needs to be stored.
Syntax:
CREATE TABLE #table_name The below query will create a temporary table:create table #bookNow, we will insert the records.insert into #book valuesinsert into #book valuesselect * from #book
Output:
Recommended Reading: How To Word A Follow Up Email After Interview 
Using The Below Pandas Data Frame Find The Company With The Highest Average Sales Derive The Summary Statistics For The Sales Column And Transpose The Statistics

Group the company column and use the mean function to find the average sales


Use the describe function to find the summary statistics


Apply the transpose function over the describe method to transpose the statistics

So, those were the 60 data analyst interview questions that can help you crack your next data analyst interview and help you become a data analyst. 
    Enroll in the PG Program in Data Analytics to learn over a dozen of data analytics tools and skills, and gain access to masterclasses by Purdue faculty and IBM experts, exclusive hackathons, Ask Me Anything sessions by IBM.   
Get Ready For Your Next Sql Interview
       
If you want to crack your next software developer interview, register for our free technical interview webinar to find out how we can help you. Interview Kickstart is the gold standard in tech interview prep. Our programs include a comprehensive curriculum, unmatched teaching methods, FAANG+ instructors, and career coaching to help you nail your next tech interview.
Check out some reviews from our students to understand how weâve shaped the careers of thousands of engineers by helping them land high-paying offers from the biggest tech companies.
Don’t Miss: How To Have Good Interview Skills 
What Is A Stored Procedure Give An Example
A stored procedure is a prepared SQL code that can be saved and reused. In other words, we can consider a stored procedure to be a function consisting of many SQL statements to access the database system. We can consolidate several SQL statements into a stored procedure and execute them whenever and wherever required.
A stored procedure can be used as a means of modular programming, i.e., we can create a stored procedure once, store it, and call it multiple times as required. This also supports faster execution when compared to executing multiple queries.
Syntax:
CREATE PROCEDURE procedure_nameASSql_statementGO To execute we will use this:EXEC procedure_name
Example:
We are going to create a stored procedure that will help us extract the age of the employees.create procedure employee_ageasselect e_age from employeegoNow, we will execute it.exec employee_age
Output:
What Are The Types Of Subquery
There are two types of subquery â Correlated and Non-Correlated.
A correlated subquery cannot be considered as independent query, but it can refer the column in a table listed in the FROM the list of the main query.
A Non-Correlated sub query can be considered as independent query and the output of subquery are substituted in the main query.
Read Also: What Are The Most Common Interview Questions And Answers 
Code In Logical Parts
When youre writing the code, pay attention to its structure. Divide the code into logical parts. That way, you will make your code easier to read, which is also one of the requirements to get the job. There is no point in writing a correct code that is a mess, and nobody can read it and understand it after you write it. Not even you!! If your code is divided into logical parts, it will be easier for you to explain to the interviewer what you did.
You Are Given A Table Of User Experiences Representing Each Persons Past Employment History Answer The Following:
    Data Science SQL Interview Question Walkthrough [Microsoft] – Window Function: Ranking   
Write a query to prove or disprove this hypothesis: Data scientists who switch jobs more frequently become managers faster than data scientists that stay at one job for longer.
For this question, you are interested in analyzing the career paths of data scientists. Lets say that the titles you care about are bucketed into data scientist, senior data scientist, and data science manager.
Heres a partial solution for this question:
This question requires a bit of creative problem solving to understand how you can prove or disprove the hypothesis. The hypothesis is that data scientists who end up switching jobs more often get promoted faster. Therefore, in analyzing this dataset, you can prove this hypothesis by separating the data scientists into specific segments on how often they switch jobs in their careers.
For example, if you looked at the number of job switches for data scientists that have been in their field for five years, you could prove the hypothesis if the number of data science managers increased with the number of times they had switched jobs.

Never switched jobs: 10% are managers
Switched jobs once: 20% are managers
Switched jobs twice: 30% are managers
Switched jobs three times: 40% are managers

Also Check: How To Be Confident In An Interview