Question
What is SQL
ANS :-
Structure of eCommerce databases
ANS : -
Create a database of campus influencers
ANS :-
DBMS Vs RDBMS
ANS :-
Normalization why? how etc
ANS :-
What are ACID properties in a RDBMS?
ANS :-
Transactional databases / db with timestamps:
Key timestampfunctiond such as year(colname) month(colname)
and fucntions such as timestamp diff.
ANS :-
1. Given a customer service database. Which has details of call
from (customer_id / executive_id), call to, start_time, end_time.
Write a query to find if an executive got back to a customer within
30 minutes from the first conversation
ANS :-
What does Rollback do?
ANS :-
DDL vs DML in SQL
ANS :-
Types of SQL Commands
ANS :-
Difference between truncate and delete / truncate and drop / drop
and delete
ANS :-
Explain primary and foreign key constraint. What are the key
differences?
ANS :-
Explain SQL constraints
ANS :-
Difference between primary key and unique key
ANS :-
Given 2 tables with one containing URL & the other domain &
country. Based on the URL, fetch country name and domain
ANS :-
Explain different types of joins with examples
ANS :-
Given an assumption: There are 2 tables, first table has 10 records
and second table has 15 records.
ANS :-
There are 5 records common in both the tables.
ANS :-
Number of records that would be fetched when you perform left
join/right join/inner join/cross-join.
ANS :-
Given 2 tables, table A with 12 records & Table B with 8 records.
Both tables unique
ANS :-
Write the no of records that would be fetched when you perform
left join/right join/inner join/cross-join.
ANS :-
There are two tables, Table 1 has a column having row of three 1's
and table 2 has column having two 1's .
ANS :-
What will be the result of inner join?
ANS :-
Given two tables, write the number of rows that'll be returned after
performing a left, right, full outer, inner and cross joins.
Table 1: 1,1,1,2,NULL
Table 2: 1,1,3,NULL
ANS :-
Given 2 tables of sizes 3x5 and 4x5 (all the cell values are "1"),
what will be the output size when you perform an inner join.
ANS :-
Given 2 tables one with 10 rows and the other with 15.
What is the maximum number of rows possible when you perform
a join (can use any of the joins)
ANS :-
Given an employee table, fetch the total number of employees
working under a manager
ANS :-
Given an employee table, fetch all employees names along with
their manager names
ANS :-
Inner vs Outer join
ANS :-
Understanding null / duplicate matches during joins is very crucial
ANS :-
1. Filter records starting with L
ANS :-
2. Filter records starting and / or ending with a vowel
ANS :-
3. Filter records with name containing 3rd letter as D"
ANS :-
Write a query to find employees with name starting with "s"
ANS :-
Report of all employees reporting to a "Adam"
ANS :-
There are 2 table, one with customer segment, customer id
information and other with customer id and transaction details.
ANS :-
Write a query to show average transaction amount per segment.
ANS :-
Write a query to show average number of transactions per
segment.
ANS :-
Given an employee table, fetch the total number of employees
working in a department
ANS :-
Given a table, with columns storeid, revenue & city. Write a query
to display city-wise revenue
ANS :-
Given a table containing employee name and salary. Find the sum
of salary and display it along with the salary of each employee
ANS :-
Write a query to fetch employees earning salary between 20000
and 30000
ANS :-
Fetch department-wise salary from an employee table
ANS :-
Write a query to count the total number of people in a table with a
given condition.
ANS :-
Difference between rank, dense_rank and row_number?
ANS :-
Find the Second highest salary.
ANS :-
Find the 10th highest salary.
ANS :-
Find the 25-30th highest salary.
ANS :-
Find out the employee whose salary is greater then the average
salary of their department
ANS :-
Given a table of data containing employee salary, write a query to
print the moving average. i.e Average salary every 5 months
ANS :-
What is rank and dense rank?
ANS :-
Retrieve the details based on the date and and the customer id ,
where the spending amount is above 30 % of balance
ANS :-
Given a table of all purchases, write a query to fetch the month-
wise revenue.
ANS :-
Given a table of all purchases, write a query to fetch the month-
wise profit and loss percentages
ANS :-
Write a query to fetch top n / bottom n records
ANS :-
How to fetch ALTERNATE records from a table that is EVEN
NUMBERED OF ROWS?
ANS :-
% based calculations using row number
ANS :-
Given the following data definition, write a query that returns the
number of students whose first name is John. String comparisons
should be case sensitive.
TABLE students
id INTEGER PRIMARY KEY,
firstName VARCHAR(30) NOT NULL,
lastName VARCHAR(30) NOT NULL
ANS :-
Suppose we have a Customer table containing the following data:
CustomerID CustomerName
1 Prashant Kaurav
2 Ashish Jha
3 Ankit Varma
4 Vineet Kumar
5 Rahul Kumar
ANS :-
Write a single SQL statement to concatenate all the customer
names into a single semicolon-separated string.
ANS :-
Table is as follows:
ID C1 C2 C3
1 Red Yellow Blue
2 NULL Red Green
3 Yellow NULL Violet
Print the rows which have ‘Yellow’ in one of the columns C1, C2,
or C3, but without using OR.
ANS :-
A table containing the students enrolled in a yearly course has
incorrect data in records with ids between 20 and 100 (inclusive).
ANS :-
TABLE enrollments
id INTEGER NOT NULL PRIMARY KEY
year INTEGER NOT NULL
studentId INTEGER NOT NULL
ANS :-
Write a query that updates the field 'year' of every faulty record to
2015.
ANS :-
Information about pets is kept in two separate tables:
TABLE dogs
id INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(50) NOT NULL
ANS :-
TABLE cats
id INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(50) NOT NULL
ANS :-
Write a query that select all distinct pet names.
ANS :-
Sales table contains following columns(Product id, product name ,
store id , store name , product price(usd), weight ( decimal ) and
city name)
ANS :-
- Write a query to split kg & grms and display it in two columns( kg
& grms)
ANS :-
A table contains a column which contains both numeric as well as
alphabets. Write a sql query to print ‘welcome’ for numeric value
and ‘india’ for alphabet.
ANS :-
Suppose we have a Customer table containing the following data:
CustomerID CustomerName
1 Prashant Kaurav
2 Ashish Jha
3 Ankit Varma
4 Vineet Kumar
5 Rahul Kumar
ANS :-
Write a single SQL statement to concatenate all the customer
names into a single semicolon-separated string.
ANS :-
Given a table containing user ID, order date and order time. How
much time did it take a user to place the second order?
ANS :-
Given a table(cars) with 4 columns(model_id, model_name,color,
price) , perform groupby using model_name and color, order by
highest price, get 3rd highest.
ANS :-
How would you retrieve data from a date time column converted to
string and formatted as dd/mm/yy hh:mm:ss
ANS :-
Given a table with order-id , order item-id and quantity
Find the quantity for distinct order-id
ANS :-
Given 2 tables and the following query. What will be the output
(select * from table 1 full outer join table 2) where values not in
(select * from table 1 inner join table 2)
ANS :-
Given a table containing details of customers and their income,
write a query to update the insurance of "John" who earns 5000
salary.
ANS :-
Given a column containing positive and negative numbers, write a
query to fetch the sum of all positive numbers and sum of all
negative numbers
ANS :-
Sales table contains following columns(Product id, product name ,
store id , store name , product price(usd), weight ( decimal ) and
city name)
- Write a query to split kg & grms and display it in two columns( kg
& grms)
ANS :-
A table contains a column which contains both numeric as well as
alphabets. Write a sql query to print ‘welcome’ for numeric value
and ‘india’ for alphabet.
ANS :-
1. There are multiple events happening in autralia.
The given database contains start date, end date, event_id, and
city in which the event is happening.
Write a SQL query to get the number of days having overlapping
events in each city
ANS :-
2. Given a table of all purchases, write a query to fetch the month-
wise profit and loss percentages
ANS :-
3. Using the data available below, write a SQL query to calculate
the balance in a card as of the end of each fiscal quarter (FQ)?
In case the card has not done any transaction in a particular
quarter, the balance on the card is the same as the balance in the
previous quarter.
ANS :-
4. For every customer, calculate the average visit frequency and
average basket size - 1 month after registration, 2 months after
registration,
3 months after registration and so on for a maximum of 24 months
of tenure.
ANS :-
4. Create a frequency distribution chart and plot a pivot table of the
given data
ANS :-
Explain grouping in SQL
ANS :-
Questions around order of execution, highlight difference between
where and having. Calculated columns in select statement
ANS :-
Given a table of data containing employee salary, write a query to
print the moving average. i.e Average salary every 5 months
ANS :-
What union is, diff between union/union all, union to perform joins
ANS :-
What is an index? Explain the different kinds of indexes
ANS :-
Based on salary, create an hierarchy in the company. (Case
statement, Tier1, Tier2 etc)
ANS :-
SELECT CASE WHEN null=null THEN ‘Milk’ Else ‘Egg’ END from
DUAL;
ANS :-
What are Stored procedures
ANS :-
What are triggers
ANS :-
Dealing with duplicate records: Find out if the database has any
duplicate record names, Write a query to delete duplicate records
ANS :-
What is the output of "Select NULL = NULL"
ANS :-
SQL Query to extract NULL values
ANS :-
What is your understanding of Query Optimization? Highlight some
of the methods
ANS :-
Write a SQL query to find cumulative sum/value(s) of a column
ANS :-
Create a new table, insert values, add a new column
ANS :-
How do you trim white spaces?
ANS :-
How will you go about searching for table and column names that
you don’t know where they really are? For example, search for a
column name to find out in which tables they do exist.
ANS :-
How would you retrieve data from a date time column converted to
string and formatted as dd/mm/yy hh:mm:ss
ANS :-
What is the difference between CHAR and VARCHAR2 datatype
in SQL?
ANS :-
Given a table with order-id , order item-id and quantity
ANS :-
Find the quantity for distinct order-id
ANS :-
What are the different type of Joins in SQL and explain them?
(Mainly focused on full outer join )
ANS :-
Given 2 tables and the following query. What will be the output
(select * from table 1 full outer join table 2) where values not in
(select * from table 1 inner join table 2)
ANS :-
Given a table containing details of customers and their income,
write a query to update the insurance of "John" who earns 5000
salary.
ANS :-
Implicit vs Explicit Join
ANS :-
With respect to SQL, which one is more preferable - Sub queries
or Joins? Why?
ANS :-
Does SQL have User Defined Functions?
ANS :-
Query to find the employees in the office given check in and check
out as fields.
ANS :-
Given a table of an event having columns date-ts/ event id.
ANS :-
Find the event that happened 3rd on every month
ANS :-
Split a full name into 2. First and last
ANS :-
Fetch the first 3 letters of a name
ANS :-
Given a word "JOE", find the word in a given string irrespective of
word being upper case or lower case or capitalize?
ANS :-
Find the Salary greater than Average salary using Joins or Sub-
Queries
ANS :-
What is CTE?
ANS :-
Application of CTE (Recursive CTE)
ANS :-
Given a column containing positive and negative numbers, write a
query to fetch the sum of all positive numbers and sum of all
negative numbers
ANS :-
Explain the working of a correlated sub-query
ANS :-
When you're trying to perform joins if there's a mismatch in the
joining columns, due to manual error, how do you deal with it?
ANS :-
Basics of SQL wildcards
ANS :-
SQL Alter command
ANS :-
Can you send an email using SQL? If yes, how?
ANS :-
