Advanced Database Management System - Tutorials and Notes: 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: Π NAMEMODE_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_NOSALARY > 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_VISITORSPAYMENT 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_DISCOUNTNo_of_visitors > 5 ^ Card_type = ‘VISA’ V Card_type = ‘Mastercard’ (BOOKINGS))

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