CodeBetter

RDBMS and SQL

Syllabus, Assignments Questions

Section 1 -

- Create Database and Tables - Create and Alter Table with all constraint - Select, Insert, Update, and Delete - OrderBy, GroupBy - Primary and Foreign Key - Joins and SubQuery

S.No

Question

*1.

How to select UNIQUE records from a table using a SQL Query?

*2.

How to delete DUPLICATE records from a table using a SQL Query?

*3.

How to read TOP 5 records from a table using a SQL Query?

*4.

How to read Last 5 records from a table using a SQL Query?

*5.

How to find the employee with second MAX Salary using a SQL Query?

*6.

How to find the employee with third MAX Salary using a SQL Query?

*7.

Assume you have the below tables on sessions that users have, and a user’s table. Write a query to get the active user count of daily cohorts.

Table 1 - Session

column_name	type
user_id	integer
session_id	integer
date	datetime

Table 2 - Users

column_name	type
user_id	integer
email	string
date	datetime

*8.

Assume you are given the below table on transaction from users for purchase. Write a query to get the list of customers where their earliest purchase was at least $50.

Table 1 - Users_transactions

column_name	type
transaction_id	integer
product_id	integer
user_id	integer
spend	float
transaction_date	datetime

Assume you are given the below table on transactions from users. Write a query to get the number of users and total products bought per latest transaction date where each user is bucketed into their latest transaction date.

Table 1 - Users_transactions

column_name	type
transaction_id	integer
product_id	integer
user_id	integer
spend	float
transaction_date	datetime

*10.

Assume you are given the below tables on users and their time spent on sending and opening Snaps. Write a query to get the breakdown for each age breakdown of the percentage of time spent on sending versus opening snaps.

Table 1 - activities

column_name	type
activity_id	integer
user_id	integer
type	string('open','close')
time_spent	float
activity_date	datetime

Table 2 - age_breakdown

column_name	type
user_id	integer
age_bucket	string

*11.

Assume you are given the below table on reviews from users. Define a top-rated place as a business whose reviews only consist of 4 or 5 starts. Write a query to get the number and percentage of businesses that are toprated places.

*12.

Given the following tables:
Sql> Select * from runners;

Table 1 - runners

Id	Name
1	John Doe
2	John Doe
3	Alice Jones
4	Bobby Louis
5	Lisa Romero

Sql> select * from races;

Table 2 - Users

id	Event	Winner_id
1	100 meter dash	2
2	500 meter dash	3
3	Cross-country	2
4	Triathalon	NULL

What will be the result of the query below?
Select * from eunners where id not in (select winner_id from races)

*13.

Assume a schema of EMP (ID, Name, DeptId), Dept (Id, Name).
If there are 10 records in the Emp table and 5 records in the Dept table, how many rows will be displayed in
the result of the following SQL query:
Query: Select * from Emp,Dept

*14.

Suppose you have a table “Loan_Records”.

Borrower	Bank_Manager	Loan_Amount
Ramesh	Sunderajan	10000.00
Suresh	Ramgopal	5000.00
Mahesh	Sunderajan	7000.00

Query: select Count(*) from ((select Borrower, Bank_Manager from Loan_Records) As Natural Join (Select Bank_Manager, Loan_Amount from Loan_Records) as T);
What is the output of the following SQL query?

S.No

Question

#1.

What will be the output of the below query?
Query: Select Company, Avg(salary) from AV1 having avg(salary) > 1200 group by Company where Salary > 1000;

#2.

SQL Query to find the second highest salary of Employee

#3.

SQL Query to find Max Salary from each department.

#4.

Write SQL Query to display the current date?

#5.

Write an SQL Query to print the name of the distinct employee whose DOB is between 01/01/1960 to 31/12/1975.

#6.

Write an SQL Query to find an employee whose salary is equal to or greater than 10000.

#7.

Write SQL Query to find duplicate rows in a database? And then write SQL Query to delete them?

#8.

How do you find all employees who are also managers?

#9.

Write a SQL Query to find all duplicates emails in a table named Person.

Table 1 - Customers

Id	Email
1	a@b.com
2	c@d.com
3	a@b.com

For example, your query should return the following for the above table.

Email
a@b.com

#10.

Given a Weather table, write a SQL query to find all dates’ Ids with higher temperature compared to its previous (yesterday’s) dates.

Id(Int)	RecordDate(DATE)	Temperature(INT)
1	2015-01-01	10
2	2015-01-02	25
3	2015-01-03	20
4	2015-01-04	30

#11.

The Employee table holds all employee including their managers. Every employee has ab Id, and there is also a column for the managerId.

Id	Name	Salary	ManagerId
1	Joe	70000	3
2	Henry	80000	4
3	Sam	60000	NULL
4	Max	90000	NULL

Given the Employee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earn more than his manager.

Employee
Joe

#12.

X city opened a new cinema, many people would like to go to this cinema. The cinema also gives out a poster indicating the ‘movies’ ratings and descriptions. Please write a SQL Query to output movies with an odd numbered ID and a description that is not ‘boring’. Order the result by rating.

Table 1 - Cinema

id	movie	description	rating
1	War	great 3D	8.9
2	Science	fiction	8.5
3	irish	boring	6.2
4	Ice song	Fantacy	8.6
5	House card	Interesting	9.1

#13.

Write a SQL query to get the nth highest salary from the Employee table.

Id	Salary
1	100
2	200
3	300

For example, given the above Employee table, the nth highest salary where n=2 is 200. If there is no nth highest salary, then the query should return null.

#14.

From the following table of user IDs, actions, and dates, write a query to return the publication and cancellation rate for each user.

Table 1 - users

user_id	action	date
1	start	1-1-20
1	cancel	1-2-20
2	start	1-3-20
2	publish	1-4-20
3	start	1-5-20
3	cancel	1-6-20
4	start	1-7-20

Table 2 - Desired output

user_id	publish_rate	cancel_rate
1	0.5	0.5
2	1.0	0.0
3	0.0	1.0

#15.

From the following table of transactions between two users, write a query to return the change in net worth for each user, ordered by decreasing net change.

Table 1 - transactions

sender	receiver	amount	transaction_date
5	2	10	2-12-20
1	3	15	2-13-20
2	1	20	2-13-20
2	3	25	2-14-20
3	1	20	2-15-20
3	2	15	2-15-20
1	4	5	2-16-20

Table 2 - Desired output

user	net_change
1	20
3	5
4	5
5	-10
2	-20

#16.

From the following table of transactions between two users, write a query to return the change in net worth for each user, ordered by decreasing net change.

Table 1 - items

date	Item
1-1-20	Apple
1-1-20	Apple
1-1-20	Pear
1-1-20	Pear
1-2-20	Pear
1-1-20	Pear
1-1-20	pear
1-1-20	orange

RDBMS and SQL

Syllabus, Assignments Questions

Section 1 -

Table 1 - Session

Table 2 - Users

Table 1 - Users_transactions

Table 1 - Users_transactions

Table 1 - activities

Table 2 - age_breakdown

Table 1 - runners

Table 2 - Users

Table 1 - Customers

Table 1 - Cinema

Table 1 - users

Table 2 - Desired output

Table 1 - transactions

Table 2 - Desired output

Table 1 - items

Contact Us

Our Address

Email Us

Call Us