Sunday, October 7, 2018

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

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







No comments:

Post a Comment

Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents

data recovery