SQL Exercises for Beginners / Simple SQL Exercises with Answers / Solved SQL exercises / SQL solved exercises with simple conditions / Solved relational algebra queries
LIST
OF TABLES:
VISITORS (Visitor Id, Name, Age, Gender, Address,
Contact_no, Email, Entry_time, Exit_time, Date_of_visit, Mode_of_Transport)
EVENTS (Event_ID, Event_Name, Capacity, Timings,
Category)
EMPLOYEE (Emp_ID, Emp_Name, Address, Contact_No,
Gender, Department, Shift, Salary, DOB, Join_Date, Event_Id)
MAINTENANCE_PROBLEMS (Complaint_Id, Complaint_Name,
Date_of_complaint, Status, Date_of_completion, Employee_Id, Event_Id)
BOOKINGS (Ticket_No, Payment_Mode, Card_type,
Price_Before_discount, Price_After_Discount, No_of_visitors, Visitor_Id, Event_Id)
SQL
and Relational Algebra queries:
1) Give the name of the visitors whose mode
of transport to amusement park is car?
What is to be found? [columns]
|
Name
|
Where to search? [tables]
|
Visitors
|
What are search conditions?
|
Mode_of_transport = ‘car’
|
SQL: SELECT Name FROM
Visitors WHERE Mode_of_transport = ’car’;
RA: Π NAME
(σ MODE_OF_TRANSPORT = ’CAR’ (VISITORS))
2) Give the name and address of the
visitors who visited the amusement park on 2-10-2018?
What is to be found? [columns]
|
Name, Address
|
Where to search? [tables]
|
Visitors
|
What are search conditions?
|
Date_of_visit = ’01-OCT-2018’
|
SQL: SELECT Name,
Address FROM Visitors WHERE Date_of_visit = ’01-OCT-2018’;
RA: Π NAME,
ADDRESS (σ DATE_OF_VISIT = ‘01-OCT-2018’ (VISITORS))
3) Give the Name of the events which has
the accommodation for more than 200 visitors?
What is to be found? [columns]
|
Event_name
|
Where to search? [tables]
|
Events
|
What are search conditions?
|
Capacity > 200
|
SQL: SELECT Event_name From
Events WHERE Capacity > 200;
RA: Π EVENT_NAME(
σ CAPACITY > 200 (EVENTS))
4) What is the time of event E150?
What is to be found? [columns]
|
Timings
|
Where to search? [tables]
|
Events
|
What are search conditions?
|
Event_ID = ‘E150’
|
SQL: SELECT Timings From
Events WHERE event_ID = ‘E150’;
RA: Π TIMINGS(
σ EVENT_ID = 150 (EVENTS))
5) List the details of male employees who
work during night shift?
What is to be found? [columns]
|
All columns
|
Where to search? [tables]
|
Employee
|
What are search conditions?
|
Gender = ‘Male’ AND Shift = ‘night’
|
SQL: SELECT * FROM
Employee WHERE Gender = ‘Male’ AND Shift = ‘night’;
RA: (σ GENDER = ’MALE’
^ SHIFT = ’NIGHT’ (EMPLOYEE))
6) Give the contact number of the employees
whose salary is more than 30000 and less than 45000?
What is to be found? [columns]
|
Contact_no
|
Where to search? [tables]
|
Employee
|
What are search conditions?
|
Salary > 30000 and Salary < 45000
|
SQL: SELECT Contact_no FROM
Employee WHERE Salary > 30000 AND salary < 45000;
(or)
SELECT
Contact_no FROM Employee WHERE Salary BETWEEN 30000 AND 45000;
RA: Π CONTACT_NO
(σ SALARY > 30000 ^ SALARY < 45000 (EMPLOYEE))
7) List down the details of complaints
filed on 2-10-2018?
What is to be found? [columns]
|
All columns
|
Where to search? [tables]
|
Maintenance_Problems
|
What are search conditions?
|
Date_of_complaint = ’01-OCT-2018’
|
SQL: SELECT * FROM
Maintanence_Problems WHERE Date_of_Complaint = ‘02-OCT-2018’;
RA: (σ DATE_OF_COMPLAINT
= ‘02-0CT-2018’ (MAINTANENCE_PROBLEMS))
8) Give the complaint ids of the complaints
that are completed on ’02-10-2018’ and ’03-10-2018’?
What is to be found? [columns]
|
Complaint_id
|
Where to search? [tables]
|
Maintenance_Problems
|
What are search conditions?
|
(Date_of_Complaint = ‘02-OCT-2018’ OR Date_of_Complaint
= ‘03-OCT-2018’) AND status = ‘Completed’
|
SQL: SELECT complaint_id
FROM Maintanence_Problems WHERE Date_of_Complaint = ‘02-OCT-2018’ OR Date_of_Complaint
= ‘03-OCT-2018’ AND status = ‘Completed’;
RA: (σ DATE_OF_COMPLAINT
= ‘02-0CT-2018’ V DATE_OF_COMPLAINT = ‘02-0CT-2018’ ^ STATUS = ’Completed’
(MAINTANENCE_PROBLEMS))
9) Give the Ticket Nos and the number of
visitors for each ticket whose payment mode is net-banking?
What is to be found? [columns]
|
Ticket_No, No_of_visitors
|
Where to search? [tables]
|
Bookings
|
What are search conditions?
|
Payment_Mode = ’Net Banking’
|
SQL: SELECT Ticket_No,
No_of_visitors FROM Bookings WHERE Payment_Mode = ’Net Banking’;
RA: Π TICKET_NO,
NO_OF_VISITORS (σ PAYMENT MODE=’NET BANKING’ (BOOKINGS))
10) Find the price after discount for all
tickets for which the number of visitors are more than 5 and the card types are
‘VISA’ and ‘MasterCard’?
What is to be found? [columns]
|
Price_After_Discoun
|
Where to search? [tables]
|
Bookings
|
What are search conditions?
|
No_of_visitors > 5 AND (Card_type = ‘VISA’
OR Card_type = ‘Mastercard’)
|
SQL: SELECT Price_After_Discount
FROM Bookings WHERE No_of_visitors > 5 AND Card_type = ‘VISA’ OR Card_type =
‘Mastercard’;
RA: Π PRICE_AFTER_DISCOUNT
(σ No_of_visitors > 5 ^ Card_type = ‘VISA’ V Card_type = ‘Mastercard’
(BOOKINGS))
*************
No comments:
Post a Comment