EXERCISES:
-
Use the SQL AND, OR and NOT Operators in your query (The WHERE clause can be combined with AND, OR, and NOT operators)
Where courseID is equals to a number below 5 and lesson time is less then 45 min
Introduction
You have been asked to develop a database for a local leisure centre, as they are in the process of upgrading their existing paper base record keeping system building for their swimming pool. They would like to use the database to help them manage their swimming lessons. Below are the entities and their attributes which were extracted from their paper-based system by a database developer who never got to implement the database for the local leisure centre. Use the entities and their attributes provided to create the respective tables. In addition, the leisure centre has now decided that they want two databases. A database implemented using SQL.
Course (courseID, Level, Sessions, Instructor, startDate, LessonTime)
Lessons (LessonID, CourseID, MemberID)
Members (MemberID, Firstname, Surname, DOB, Address, City)
The above highlighted in green are the primary keys.
EXERCISES:
Use the SQL AND, OR and NOT Operators in your query (The WHERE clause can be combined with AND, OR, and NOT operators)
Where courseID is equals to a number below 5 and lesson time is less then 45 min
Order by the above results by:
Insturctor in “course” table in descending order
UPDATE the following:
Members table, change the addresses of any three members.
Use the SQL MIN () and MAX () Functions to return the smallest and largest value
Of the membersID column in the “members” table
Use the SQL COUNT (), AVG () and SUM () Functions for these:
Count the total number of members in the “members” table
WILDCARD queries (like operator)
Find all the people from the “members” table whose last name
starts with B.
Find all the people from the “members” table whose first name
containing the pattern "ic"
GROUP BY.
To get data of 'city' and number of members for this 'city'
Nested.
To identify the members who get lessonID more than 1002
Triggers.
Write a trigger that will be fired whenever a new member gets added in the members table.
Joins.
Combine rows from two or more tables, based on a related column between them.
