CTS SQL Questions
Q1. Write an SQL Query to display, the like Id and post Id for the posts liked before
"2024-02-20".
Your output should have columns as given below:
like_id post_id
Answer:
SELECT like_id, post_id
FROM likes
WHERE like_date < '2024-02-20';
Q2. Write an SQL query to display: The ID, Name, Department name and Basic salary for
employees working in the "HR" department.
Your output should have columns as given below:
EMPID, EMPNAME, DEPTNAME, BASIC
Tables:
- employee_info(EMPID, EMPNAME, DEPTID, JOINING_DT, DOB, YRS_OF_EXP,
EMPLOYEE_CATEGORY)
- dept_info(DEPTID, DEPTNAME, LOCATION)
- salary_info(EMPLOYEE_CATEGORY, BASIC, …)
Answer:
SELECT ei.EMPID, ei.EMPNAME, di.DEPTNAME, si.BASIC
FROM employee_info ei
JOIN dept_info di ON ei.DEPTID = di.DEPTID
JOIN salary_info si ON ei.EMPLOYEE_CATEGORY = si.EMPLOYEE_CATEGORY
WHERE di.DEPTNAME = 'HR';
Q3. Write an SQL query to display: The employee ID, Name, Department name and
House rent allowance for employees who work in departments located in either
BANGLORE or COCHIN.
Your output should have columns as given below:
EMPID, EMPNAME, DEPTNAME, HOUSE_RENT_ALLOWANCE
Tables:
- employee_info(EMPID, EMPNAME, DEPTID, JOINING_DT, DOB, YRS_OF_EXP,
EMPLOYEE_CATEGORY)
- dept_info(DEPTID, DEPTNAME, LOCATION)
- salary_info(EMPLOYEE_CATEGORY, HOUSE_RENT_ALLOWANCE, …)
Answer:
SELECT ei.EMPID, ei.EMPNAME, di.DEPTNAME, si.HOUSE_RENT_ALLOWANCE
FROM employee_info ei
JOIN dept_info di ON ei.DEPTID = di.DEPTID
JOIN salary_info si ON ei.EMPLOYEE_CATEGORY = si.EMPLOYEE_CATEGORY
WHERE di.LOCATION IN ('BANGLORE','COCHIN');
Q4. Write an SQL Query to display the distinct first names and contact numbers of
passengers who are departing from "Hong Kong" boarding a flight with Flight ID "4" and
have requested a "Vegetarian" meal.
Your output should have columns as given below:
FIRST_NAME CONTACT
Answer:
SELECT FIRST_NAME, CONTACT
FROM passengers
WHERE DEPART='Hong Kong' AND FLIGHT_ID=4 AND FOOD='Vegetarian';
Q5. Write an SQL query to find: The train ID and name of all trains that have a name
starting with the alphabet 'M' and that go to the station with name "PUNE".
Your output should have columns as given below:
train_id train_name
Tables:
- train_details_tbl(train_id, train_name, train_type, train_time, train_from, train_to,
train_speed)
- train_type_tbl(train_type, type_description)
- train_station_tbl(station_id, station_name)
Answer:
SELECT train_id, train_name
FROM train_details_tbl
WHERE train_name LIKE 'M%' AND train_to='PUNE';
Q6. Write an SQL query to display: The employee ID, Type of leave and total number of
leaves for employees who have taken more than 10 leaves, where the leave type is
either 'Casual Leave'(CL) or 'Medical Leave' (ML).
Your output should have columns as given below:
EMPID LEAVE_TYPE TOTAL_LEAVES
Tables:
- emp_leave_info(EMPID, FROM_DATE, TO_DATE, TOTAL_LEAVES, LEAVE_TYPE)
Answer:
SELECT EMPID, LEAVE_TYPE, TOTAL_LEAVES
FROM emp_leave_info
WHERE TOTAL_LEAVES > 10 AND LEAVE_TYPE IN ('CL','ML');
Q7. Write an SQL Query to display: The transaction id, transaction amount and
transaction type of all the transactions whose transaction type is "Debit" and
transaction amount is greater than 10000 but less than 50000.
Your output should have columns as given below:
TRANSACTION_ID AMOUNT TRANSACTION_TYPE
Tables:
- transaction(Transaction_ID, Account_ID, Transaction_Date, Amount, Transaction_Type)
Answer:
SELECT Transaction_ID, Amount, Transaction_Type
FROM transaction
WHERE Transaction_Type='Debit' AND Amount > 10000 AND Amount < 50000;
Q8. Write an SQL Query to display: The first name, contact number and balance of all
customers whose account type starts with "Sa". The output should be ordered by the
customer's first name.
Your output should have columns as given below:
FIRST_NAME CONTACT BALANCE
Tables:
- customer(Customer_Id, First_Name, Last_Name, Contact, Email)
- account(Account_Id, Customer_ID, Branch_Id, Account_Type_ID, Balance)
- account_type(Account_Type_ID, Account_Type_Name)
Answer:
SELECT c.First_Name, c.Contact, a.Balance
FROM customer c
JOIN account a ON c.Customer_Id = a.Customer_ID
JOIN account_type at ON a.Account_Type_ID = at.Account_Type_ID
WHERE at.Account_Type_Name LIKE 'Sa%'
ORDER BY c.First_Name;
Q9. Write an SQL query to display: The names of employees who have more than 5 years
of experience and joined after January 1, 2001.
Your output should have columns as given below:
Employee ID Employee Name
Tables:
- employee_info(EMPID, EMPNAME, DEPTID, JOINING_DT, DOB, YRS_OF_EXP,
EMPLOYEE_CATEGORY)
Answer:
SELECT EMPID AS "Employee ID", EMPNAME AS "Employee Name"
FROM employee_info
WHERE YRS_OF_EXP > 5 AND JOINING_DT > '2001-01-01';
Q10. Write an SQL query to display: The course ID, course name and schedule details
(day and start time) of all courses that are taught on 'Wednesday'.
Your output should have columns as given below:
Course ID Course Name Day Start Time
Tables:
- course(course_id, name, type, term)
- schedule(schedule_id, day, starttime, endtime)
- section(section_id, course_id, schedule_id, instructor_id, room)
Answer:
SELECT c.course_id AS "Course ID", c.name AS "Course Name", s.day AS "Day", s.starttime AS "Start Time"
FROM course c
JOIN section sec ON c.course_id = sec.course_id
JOIN schedule s ON sec.schedule_id = s.schedule_id
WHERE s.day='wed';
