RDBMS

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
*9 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

Contact Us

Our Address

Office no.- 401,Shekhar Central Building ,Palasiya, Pin-code:452001, Indore

Email Us

contact@codebetter.in

Call Us

+91 88230 75444, +91 99939 28766

Loading
Your message has been sent. Thank you!