Sql And Python Interview Questions

What Is An Operator In Python

An operator is a particular symbol which is used on some values and produces an output as a result. An operator works on operands. Operands are numeric literals or variables which hold some values. Operators can be unary, binary or ternary. An operator which requires a single operand known as a unary operator, which require two operands known as a binary operator and which require three operands is called ternary operator.

Example:

Output:

# Unary Operator-12# Binary Operator25156# Ternary Operator12

Write A Query On The Given Tables To Get The Car Model With The Fastest Average Times For The Current Day

In this two-part table schema question, were tracking not just enter/exit times, but also car make, model and license plate info.

The car model to license plate information will be one-to-many, given that each license plate represents a single car, and a car model can be replicated many times. Heres an example for crossings and model/license plate :

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:

Don’t Miss: What To Expect During An Interview

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

How Are Arguments Passed In Python

In Python, arguments are passed by

• Pass by value- here a copy of an object is passed and changing the value of the copy object doesnt change the value of the original object.
• Pass by reference- here the reference of the actual object is passed and changing the value of the reference object changes the value of the original object.

Also Check: How To Answer Questions During An Interview

Sql Basics Interview Questions

The SQL basics interview questions will require you to put some of the above theoretical concepts into practice. That doesnât necessarily mean that these questions have to be coding questions. They can be descriptive too. But they usually cover concepts you will need to know if you want to write a code. Those concepts are:

• using SUM, COUNT, AVG, MIN, MAX and other aggregate functions

Here is an example from a Postmates SQL Data Science Interviewâ

Customer Average Orders

“How many customers placed an order and what is the average order amount?”

Link to the question:

Solution: To answer this SQL interview question youâll have to use the table postmates_orders.

SELECT count, avgFROM postmates_orders

As one can see, this is really an easy one. It tests the aggregate functions COUNT and AVG, and it also requires the knowledge of how to use the DISTINCT clause.

Hereâs another one, this time from Credit Karma:

Submission Types

“Write a query that returns the user ID of all users that have created at least one âRefinanceâ submission and at least one âInSchoolâ submission.”

Link to the question:

Solution: To answer this SQL interview question youâll have to use the table loans.

SELECT user_idFROM loansWHERETYPEin GROUPBY user_idHAVING count =2

Explain The Difference Between Oltp And Olap

OLTP: It stands for online transaction processing, and we can consider it to be a category of software applications that are efficient for supporting transaction-oriented programs. One of the important attributes of the OLTP system is its potential to keep up the consistency. The OLTP system often follows decentralized planning to keep away from single points of failure. This system is generally designed for a large audience of end users to perform short transactions. The queries involved in such databases are generally simple, need fast response time, and, in comparison, return in only a few records. So, the number of transactions per second acts as an effective measure for those systems.

OLAP: It stands for online analytical processing, and it is a category of software programs that are identified by a comparatively lower frequency of online transactions. For OLAP systems, the efficiency of computing depends highly on the response time. Hence, such systems are generally used for data mining or maintaining aggregated historical data, and they are usually used in multidimensional schemas.

Also Check: What Can You Ask In An Interview

What Are Functions In Python Explain Their Types

Functions are a block of code with a valid name, parameters list, and a body once written , they can be executed whenever required in a program.

There are three types of functions:

• Built-in functions like copy and lean that are built into Python.
• User-defined functions are defined by the user.
• Anonymous functions are the functions that are not defined with a def name.

How Many Aggregate Functions Are Available In Sql

The aggregate function is used to determine and calculate several values in a table and return the result as a single number. For example, the average of all values, the sum of all values, and the maximum and minimum value among particular groupings of values.

The following syntax illustrates how to use aggregate functions:

SQL provides seven aggregate functions, which are given below:

• AVG: This function is used to returns the average value from specified columns.
• COUNT: This function is used to returns the number of table rows, including rows with null values.
• MAX: This function is used to returns the largest value among the group.
• MIN: This function is used to returns the smallest value among the group.
• SUM: This function is used to returns the total summed values of the specified column.
• FIRST: This function is used to returns the first value of an expression.
• LAST: This function is used to returns the last value of an expression.

Recommended Reading: How To Be Ready For A Phone Interview

What Is The Difference Between Ddl Dml Dcl And Tcl

The only common letter in all acronyms is L which stands for Language. These represent four categories into which the SQL commands have been separated:

• Data Definition Language Involves SQL commands used to define data structures CREATE, ALTER, TRUNCATE, and DROP.
• Data Manipulation Language Involves SQL commands used to manipulate data SELECT, INSERT, UPDATE, DELETE. This is the part of the language data scientists will use the most.
• Data Control Language Involves SQL commands used commonly by database administrators to manage permissions GRANT, REVOKE.
• Transaction Control Language Also commonly used by database administrators, but to ensure the transactions that occur in the database happen in such a way that minimizes the danger of suffering from data loss.

Q53 What Is The Difference Between Clustered And Non

The differences between the clustered and non clustered index in SQL are :

Read Also: What Are Some Great Interview Questions

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.

Want You To Write Me A Simple Spell Checking Enginethe Query Language Is A Very Simple Regular Expression

Here is the Python Code :def setUp: word = word.strip temp_list = Ismatch = False if word in input_list: Ismatch = True elif word is None or len == 0: Ismatch = False else: for w in input_list: if len == len: temp_list.append for j in range): count=0 for i in range): if word == temp_list or word == .: count += 1 else: break if count == len: Ismatch = True printdef isMatch: return setUpisMatchLess

bear in mind for your solution, checking the lengths of words in the dictionary is very fast. Thats what you can use your setup for. Theres no need to iterate through the whole loop of checks if the word fails the length already. See my solution aboveLess

This was the fastest I could do without regex:def func: if len not in : return False elif wrd in lst: return True else: lst1 = for z in lst1: c=0 for i in range): if wrd != . and wrd == z: c=c+1 if len-wrd.count == c: return True return FalseLess

Read Also: What Makes A Good 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?

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?

Answer: a

Answer: b

Adaptability And Ability To Learn

Questions related to adaptability and learning give you a chance to show your interviewer that you can adjust to difficult situations and grasp new skills and concepts. You can go into as much detail as youd like while answering these questions, but also keep in mind that direct, tangible examples from your past experience will help draw your audience in.

Describe a time when you had to learn a new programming language.

As you answer this question, try to showcase your ability to learn under pressure. If youre new to coding, no problem. Discuss the reasons that drove you to learn and how you made sure you gained a working knowledge of the language.

Talk about a time when you had to incorporate feedback.

Being able to accept feedback while on a development team is sometimes more important than your coding knowledge. Before going into the interview, try to have at least one example in mind where you had to change what you did or alter your approach to a problem based on someone elses ideas.

Don’t Miss: How To Crack Data Science Interview

How To Remove Whitespaces From A String In Python

To remove the whitespaces and trailing spaces from the string, Python providies strip built-in function. This function returns a copy of the string after removing whitespaces if present. Otherwise returns original string.

Example:

Output:

javatpoint     javatpoint               javatpointAfter stripping all have placed in a sequence:Javatpointjavatpointjavatpoint

What Is Python’s Parameter Passing Mechanism

There are two parameters passing mechanism in Python:

• Pass by references

The pass by value is that whenever we pass the arguments to the function only values pass to the function, no reference passes to the function. It makes it immutable that means not changeable. Both variables hold the different values, and original value persists even after modifying in the function.

Python has a default argument concept which helps to call a method using an arbitrary number of arguments.

Read Also: How To Study For Technical Interview

What Are The Various Steps Involved In Any Analytics Project

This is one of the most basic data analyst interview questions. The various steps involved in any common analytics projects are as follows:

Understanding the Problem

Understand the business problem, define the organizational goals, and plan for a lucrative solution.

Collecting Data

Gather the right data from various sources and other information based on your priorities.

Cleaning Data

Clean the data to remove unwanted, redundant, and missing values, and make it ready for analysis.

Exploring and Analyzing Data

Use data visualization and business intelligence tools, data mining techniques, and predictive modeling to analyze data.

Interpreting the Results

Interpret the results to find out hidden patterns, future trends, and gain insights.

FREE Course: Introduction to Data Analytics

Is Python Interpreted Language

Python is an interpreted language. The Python language program runs directly from the source code. It converts the source code into an intermediate language code, which is again translated into machine language that has to be executed.

Unlike Java or C, Python does not require compilation before execution.

Don’t Miss: How To Type A Thank You Letter After An Interview

State The Differences Between Views And Tables

What Is Normalization And Its Types

Normalization is used in reducing data redundancy and dependency by organizing fields and tables in databases. It involves constructing tables and setting up relationships between those tables according to certain rules. The redundancy and inconsistent dependency can be removed using these rules to make normalization more flexible.

The different forms of normalization are:

Don’t Miss: How To Conduct A Phone Screen Interview

Is Pyspark The Same As Spark

No. PySpark is Python API for Spark. PySpark allows you to create applications using Python APIs.

2. What is PySpark, and how it works?

PySpark is a Python API for Apache Spark. It lets you develop Spark applications using Python APIs, but it also includes the PySpark shell, which allows you to analyze data in a distributed environment interactively. Most of Spark’s capabilities, such as Spark SQL, DataFrame, Streaming, MLlib , and Spark Core, are supported by PySpark.

What Is A Recursive Stored Procedure

A stored procedure that calls itself until a boundary condition is reached, is called a recursive stored procedure. This recursive function helps the programmers to deploy the same set of code several times as and when required. Some SQL programming languages limit the recursion depth to prevent an infinite loop of procedure calls from causing a stack overflow, which slows down the system and may lead to system crashes.

DELIMITER $$     /* Set a new delimiter =>  $$ */CREATEPROCEDURE calctotal BEGINDECLARE score INTDEFAULTNULL    /* Set the default value =>  "score" */SELECT awards FROM achievements   /* Update "score" via SELECT query */WHERE id = number INTO score IF score ISNULLTHENSET total =0    /* Termination condition */ELSECALL calctotal    /* Recursive call */SET total = total + score    /* Action after recursion */END IF END $$     /* End of procedure */DELIMITER       /* Reset the delimiter */

Recommended Reading: Interview Questions For Business Consultant

What Are Dbms And Rdbms

DBMS Database Management System.

DBMS is the software that allows storing, modifying, and retrieving data from a database. And it is a group of programs that act as the interface between data and applications. DBMS supports receiving queries from applications and retrieving data from the database.

RDBMS Relational Database Management System

Like DBMS, RDBMS is also the software that allows storing, modifying, and retrieving data from a database but a RELATIONAL database. In a relational database, the data in the tables have a relationship. Besides, RDBMS is useful when data in tables are being managed securely and consistently.