SQL exercises for beginners one

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))

*************

Go back to SQL Exercise home page.