Interview Prep
Career
GenAI
Prompt Engg
ChatGPT
LLM
Langchain
RAG
AI Agents
Machine Learning
Deep Learning
GenAI Tools
LLMOps
Python
NLP
SQL
AIML Projects

Reading list

Machine Learning Basics for a Newbie
6 Steps of Machine learning Lifecycle Introduction to Predictive Modeling
Introduction to Exploratory Data Analysis & Data Insights Descriptive Statistics Inferential Statistics How to Understand Population Distributions?
Reading Data Files into Python Different Variable Datatypes
Probability for Data Science Basic Concepts of Probability Axioms of Probability Conditional Probability
Central Tendencies for Continuous Variables Spread of Data KDE plots for Continuous Variable Overview of Distribution for Continuous variables Normal Distribution Skewed Distribution Skeweness and Kurtosis Distribution for Continuous Variable
Central Tendencies for Categorical Variables Understanding Discrete Distributions Performing EDA on Categorical Variables
Dealing with Missing Values Understanding Outliers Identifying Outliers in Data Outlier Detection in Python Outliers Detection Using IQR, Z-score, LOF and DBSCAN
Sample and Population Central Limit Theorem Confidence Interval and Margin of Error
Bivariate Analysis Introduction
Covariance Pearson Correlation Spearman's Correlation & Kendall's Tau Correlation versus Causation Tabular and Graphical methods for Bivariate Analysis Performing Bivariate Analysis on Continuous-Continuous Variables
Tabular and Graphical methods for Continuous-Categorical Variables Introduction to Hypothesis Testing P-value Two sample Z-test T-test T-test vs Z-test Performing Bivariate Analysis on Continuous-Catagorical variables
Chi-Squares Test Bivariate Analysis on Categorical Categorical Variables
Multivariate Analysis A Comprehensive Guide to Data Exploration The Data Science behind IPL
Supervised Learning vs Unsupervised Learning Reinforcement Learning Generative and Descriminative Models Parametric and Non Parametric model
Machine Learning Pipeline Preparing Dataset Build a Benchmark Model: Regression Build a Benchmark Model: Classification
Evaluation Metrics for Machine Learning Everyone should know Confusion Matrix Accuracy Precision and Recall AUC-ROC Log Loss R2 and Adjusted R2
Dealing with Missing Values Replacing Missing Values Imputing Missing Values in Data Working with Categorical Variables Working with Outliers Preprocessing Data for Model Building
Understanding Cost Function Understanding Gradient Descent Math Behind Gradient Descent Assumptions of Linear Regression Implement Linear Regression from Scratch Train Linear Regression in Python Implementing Linear Regression in R Diagnosing Residual Plots in Linear Regression Models Generalized Linear Models Introduction to Logistic Regression Odds Ratio Implementing Logistic Regression from Scratch Introduction to Scikit-learn in Python Train Logistic Regression in python Multiclass using Logistic Regression How to use Multinomial and Ordinal Logistic Regression in R ? Challenges with Linear Regression Introduction to Regularisation Implementing Regularisation Ridge Regression Lasso Regression
Introduction to K Nearest Neighbours Determining the Right Value of K in KNN Implement KNN from Scratch Implement KNN in Python
Bias Variance Tradeoff Introduction to Overfitting and Underfitting Visualizing Overfitting and Underfitting Selecting the Right Model What is Validation? Hold-Out Validation Understanding K Fold Cross Validation
Introduction to Feature Selection Feature Selection Algorithms Missing Value Ratio Low Variance Filter High Correlation Filter Backward Feature Elimination Forward Feature Selection Implement Feature Selection in Python Implement Feature Selection in R
Introduction to Decision Tree Purity in Decision Tree Terminologies Related to Decision Tree How to Select Best Split Point in Decision Tree? Chi-Squares Information Gain Reduction in Variance Optimizing Performance of Decision Tree Train Decision Tree using Scikit Learn Pruning of Decision Trees
Introduction to Feature Engineering Feature Transformation Feature Scaling Feature Engineering Frequency Encoding Automated Feature Engineering: Feature Tools
Introduction to Naive Bayes Conditional Probability and Bayes Theorem Introduction to Bayesian Adjustment Rating: The Incredible Concept Behind Online Ratings! Working of Naive Bayes Math behind Naive Bayes Types of Naive Bayes Implementation of Naive Bayes
Understanding how to solve Multiclass and Multilabled Classification Problem Evaluation Metrics: Multi Class Classification
Introduction to Ensemble Techniques Basic Ensemble Techniques Implementing Basic Ensemble Techniques Finding Optimal Weights of Ensemble Learner using Neural Network Why Ensemble Models Work well?
Introduction to Stacking Implementing Stacking Variants of Stacking Implementing Variants of Stacking Introduction to Blending Bootstrap Sampling Introduction to Random Sampling Hyper-parameters of Random Forest Implementing Random Forest Out-of-Bag (OOB) Score in the Random Forest IPL Team Win Prediction Project Using Machine Learning Introduction to Boosting Gradient Boosting Algorithm Math behind GBM Implementing GBM in python Regularized Greedy Forests Extreme Gradient Boosting Implementing XGBM in python Tuning Hyperparameters of XGBoost in Python Implement XGBM in R/H2O Adaptive Boosting Implementing Adaptive Boosing LightGBM Implementing LightGBM in Python Catboost Implementing Catboost in Python
Different Hyperparameter Tuning methods Implementing Different Hyperparameter Tuning methods GridsearchCV RandomizedsearchCV Bayesian Optimization for Hyperparameter Tuning Hyperopt
Understanding SVM Algorithm SVM Kernels In-depth Intuition and Practical Implementation SVM Kernel Tricks Kernels and Hyperparameters in SVM Implementing SVM from Scratch in Python and R
Introduction to Principal Component Analysis Steps to Perform Principal Compound Analysis Computation of Covariance Matrix Finding Eigenvectors and Eigenvalues Implementing PCA in python Visualizing PCA A Brief Introduction to Linear Discriminant Analysis Introduction to Factor Analysis
Introduction to Clustering Applications of Clustering Evaluation Metrics for Clustering Understanding K-Means Implementation of K-Means in Python Implementation of K-Means in R Choosing Right Value for K Profiling Market Segments using K-Means Clustering Hierarchical Clustering Implementation of Hierarchial Clustering DBSCAN Defining Similarity between clusters Build Better and Accurate Clusters with Gaussian Mixture Models
Understand Basics of Recommendation Engine with Case Study
8 Ways to Improve Accuracy of Machine Learning Models
Introduction to Dask Working with CuML
Introduction to Machine Learning Interpretability Framework and Interpretable Models model Agnostic Methods for Interpretability Implementing Interpretable Model Understanding SHAP Out-of-Core ML Introduction to Interpretable Machine Learning Models Model Agnostic Methods for Interpretability Game Theory & Shapley Values
Introduction to Machine Learning Interpretability Framework and Interpretable Models model Agnostic Methods for Interpretability Implementing Interpretable Model Understanding SHAP Out-of-Core ML Introduction to Interpretable Machine Learning Models Model Agnostic Methods for Interpretability Game Theory & Shapley Values
Introduction to AutoML Implementation of MLBox Introduction to PyCaret TPOT Auto-Sklearn EvalML
Pickle and Joblib Introduction to Model Deployment
Deploying Machine Learning Model using Streamlit Deploying ML Models in Docker Deploy Using Streamlit Deploy on Heroku Deploy Using Netlify Introduction to Amazon Sagemaker Setting up Amazon SageMaker Using SageMaker Endpoint to Generate Inference Deploy on Microsoft Azure Cloud Introduction to Flask for Model Deploying ML model using Flask
Model Deployment in Android Model Deployment in Iphone

Top 10 Interview Queries on SQL

Shikha
Shikha Last Updated : 04 Nov, 2022
6 min read

This article was published as a part of the Data Science Blogathon.

Introduction

With the advancement of technology and data, job opportunities are increasing rapidly. Nowadays, everyone is more interested in implementing practical knowledge than notebook theories. Likewise, interviewers are also getting smarter, now, they are no longer interested in knowing what SQL is, what are its benefits, pros or cons of SQL, but they are pretty interested in knowing how can use the basic concepts to solve real-world problems. Interviewers ask you to share your screen, open a notepad, and start writing the solution to asked queries. So, in this blog, we will cover some frequently asked SQL queries.

SQL Interview Questions and Answers | PassMyInterview.com

Source:- https://passmyinterview.com/sql-interview-questions-and-answers/

What is SQL?

Structured Query Language or SQL is the standard language for relational database management systems (RDBMS) used to interact or communicate with the database. It was invented in 1970 and is usually pronounced as “Sequel.” In terms of usage, SQL is well-known for maintaining relational databases. With SQL, one can create databases, and tables, insert records, delete records, fetch records, update records, etc.

SQL plays an important role in firms as many companies use databases to store huge amounts of data, and SQL helps them to maintain their records smoothly.

Data

As we know, SQL is based on RDBMS, so we have to create a few tables from which we can fetch the data. Most interviewees share their desktops and show two or three demo tables on which we have to perform queries. This blog considers two tables, “Employee_detail” and “Employee_salary”. The first table contains the essential detail of the IT employee like ID, name, designation, city, and date of joining. The second table includes the salary detail of the employee, like net pay, variable, and ID.

Table – Employee_detail

Row_num          EmpId
 FullName
 Designation
 DateOfJoining
 City
1 121 Rachit Tomar Apprentice 01/31/2014 Paris
2 321 Jag Reddy Developer 01/30/2015 New Delhi
3 421 Vasa Trisha Team Lead 27/11/2016 Chennai

Table – Employee_salary

EmpId  NetPay Variable
121 85000 2000
321 10000 5000
421 70000 3000

Interview Questions on SQL

Q1-Give at least two approaches to finding out the second highest salary of an employee with the help of the above tables.

Solution:

  1. Query using the N-1 rule

Select distinct NetPay from Employee_salary e1 where 1=Select count(distinct Salary) from Employee_salary e2 where e2.NetPay>e1.NetPay;

Explanation:- According to the N-1 rule, whatever the highest salary we need to find, replace that with N, subtract it by 1, and put it into the where condition. Firstly the inner query executes and counts the unique highest salary of the employee. Lastly, it compares the value of count with N-1 and if both are equal, then displays the output.

In simple terms, we are creating two aliases e1, and e2 of the same table Employee_salary, and then comparing a single row of e1 with all the rows of e2.

Note:- Here, the value of N is 2

  1. Query using the max aggregate function

select max(NetPay)from Employee_salary where NetPay Not in(Select  max(NetPay) from Employee_salary );

Explanation:- By executing the inner query, we simply get the first highest salary but when it is compared with the where condition, it fails, so next time it will fetch the second highest salary.

Q2-Give at least two approaches to fetch the employee details whose salary is more than 60 k and less than 95 k.

Solution:

  1. Query with the help of Greater than and less than operators

Select * from Employee_salary where NetPay >= 60000 and NetPay <= 95000;

  1. Query with the help of Between and operator

Select * from Employee_salary where NetPay between 60000 and 95000;

Explanation:- In this query, we use simple relational operators and between.. and keywords that check the salary range.

Q3-Write a SQL query to fetch the details of Employees whose name does not start with V, R, or S.

Solution :

  1. Query using the symbol of Not operator

Select * from Employee_detail where FullName like ‘[!VRS]%’;

  1. Query using the Not Operator as a keyword

Select * from Employee_detail where FullName not like ‘[VRS]%’;

Explanation:- ‘Like’ keyword is used for pattern matching, it compares the first letter of the Full Name with the characters V, R, and S, and if they match, it won’t display that row.

Q4:-Write a query to create a table with the same structure as the Employee_detail table with and without data.

Solution:

  1. Query to create a table without data

 Create table Employee_details2 as Select * from Employee_detail where 1=2;

  1. Query to create a table with data

Create table Employee_details2 as select * from Employee_detail;

Explanation:- We use the ‘as’ keyword to take reference from another table and using a false condition, i.e., 1 equals 2, we restrict the entry of data in the new table.

Q5:- Write a query to display the first 50% of records and the last 50% records from the Employee_detail table.

Solution:

  1. Query to display the first 50% of records

select row_num, e1.* from Employee_detail e1 where row_num<=(select count(*)/2 from Employee_detail);

  1. Query to display the last 50% of records

Select row_num,e1.* from Employee_detail e1
minus
Select row_num,e2.* from Employee_detail e2 where row_num<=(Select count(*)/2) from Employee_detail)

Explanation:- In this query, we use the count(*) function, which will count the total number of rows and then divide them into two parts using the ‘/’ operator.

Q6:- Write a SQL query to fetch distinct/unique records with or without using distinct keywords.

Solution :

  1. Query using a Distinct keyword

select distinct * from Employee_detail;

  1. Query without using the Distinct keyword

select * from Employee_detail union select * from Employee_detail;

Explanation:- In this query, we use the union keyword to compare employee_detail with itself and print only the distinct records.

Q7:-Write a SQL query to split a string ”SHIKHA” into multiple columns.

For example, Input: SHIKHA

Output:

S

H

I

K

H

A

Solution:

Select Substr(‘SHIKHA’,Level,1) From Dual Connect By Level<= Length(‘SHIKHA’);

Explanation: To understand this query, we need to know about the following terms:

Substr():- This function fetches the substring from the given string.

Level:- It is used to find the hierarchy level of columns in numeric format.

Connect By:- As the name suggests, it defines the parent-child hierarchy relationship.

Length:- This function calculates the size of the string passed.

Dual:- It is nothing but a dummy table created by Oracle.

Q8:-Write a query to add the email validation with the help of only one query.

Solution:

SELECT Email_id FROM Employee_detail where NOT REGEXP_LIKE(Email_id, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+.[A-Z]{2,4}’, ‘i’);

Explanation:- To perform the email validation in SQL, we use the aggregate function REGEXP_LIKE. The regular expression will set a rule of valid email, and like will compare and match the string.

Q9:- Write a query to count and remove duplicate rows from the Employee_detail table.

Solution:

  1. Query to count the duplicate or redundant rows:

Select EmpId, count (EmpId) from Employee_detail Group by EmpId Having count (EmpId)>1 Order by count (EmpId) desc;

Explanation:- To count the duplicate rows, we use the aggregate functions along with Group By and Having clause. Here, we group the employee details with the same EmpId and arrange them in descending order.

  1. To remove the redundant rows:

  1. Firstly we have to select the duplicate rows:

Select EmpId FROM Employee_detail e1 WHERE EmpId 
(Select max (EmpId) from Employee_detail e2 where e1.EmpId=e2.EmpId);

  1. Lastly, delete the duplicate rows:

Delete FROM Employee_detail e1 WHERE EmpId  (Select max (EmpId) from Employee_detail e2 where e1.EmpId=e2.EmpId);

Explanation:- In this query, we’re creating the aliases of the Employee table as e1 and e2, we compare the Id of the Employee, and if it is the same, then we delete that row from the table permanently.

Q10:- Write a query to find the even rows and odd rows from the Employee_detail table.

Solution:

  1. Query to find the even rows from the Employee_detail table :

Select * from(Select row_num as rn, Ed.* from Employee_detail Ed) where Mod(rn,2)=0;

  1. Query to find the odd rows from the Employee_detail table:

Select * from(Select row_num as rn, Ed.* from Employee_detail Ed) where Mod(rn,2)=1;

Explanation:- In both queries, we use the aggregate function “mod,” which calculates the modulo of the row number, and if it is divisible by true, then displays in even(output of query1); otherwise, if the remainder is 1, i.e., not divisible by 2 then displays the employee information in query 2.

Conclusion

SQL is a mandatory language for any data enthusiast. It is the way to handle the huge volume of structured data. If you want to ace any data interview or software development interview, you should be at least familiar with all the SQL commands. This blog discussed the frequently asked queries in Data Engineering interviews. Key insights are:

  1. What is SQL, and what are the possible ways to find the nth highest salary of an employee?

  2. We have seen what the ways to perform pattern matching are.

  3. We discussed how we could create a table from another existing table without data.

  4. We discussed a brief about regular expressions.

The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.

Shikha
Shikha

I am a tech enthusiast, a student, and a learner. I am a critical reader and a lover of words who finds writing blogs interesting. I possess the capability to research and learn new technologies quickly.

Free Courses

Generative AI 4.7

Generative AI - A Way of Life

Explore Generative AI for beginners: create text and images, use top AI tools, learn practical skills, and ethics.
Large Language Models 4.5

Getting Started with Large Language Models

Master Large Language Models (LLMs) with this course, offering clear guidance in NLP and model training made simple.
Prompt Engineering 4.6

Building LLM Applications using Prompt Engineering

This free course guides you on building LLM apps, mastering prompt engineering, and developing chatbots with enterprise data.
LlamaIndex 4.8

Improving Real World RAG Systems: Key Challenges & Practical Solutions

Explore practical solutions, advanced retrieval strategies, and agentic RAG systems to improve context, relevance, and accuracy in AI-driven applications.
RAG systems using LlamaIndex 4.7

Microsoft Excel: Formulas & Functions

Master MS Excel for data analysis with key formulas, functions, and LookUp tools in this comprehensive course.

Recommended Articles

Responses From Readers

Clear

Write for us

Write, captivate, and earn accolades and rewards for your work
  • Reach a Global Audience
  • Get Expert Feedback
  • Build Your Brand & Audience
  • Cash In on Your Knowledge
  • Join a Thriving Community
  • Level Up Your Data Science Game
imag

We use cookies essential for this site to function well. Please click to help us improve its usefulness with additional cookies. Learn about our use of cookies in our Privacy Policy & Cookies Policy.

Show details