October 6, 2024

SQL Assessment Task

Response Time

98%

12212

Tutor Profile

Subjects

Database, Web Development, Java, Networking

SQL

Many To Many Relation Solved:

SQL

Query 1. Write an SQL statement that correctly uses the ‘LIKE’ symbol on an alphanumeric field to select a subset of data. Explain the purpose of your query for your database solution.

select clubID, clubName, address, state from club where Services LIKE '%Swimming%' order by clubID DESC

SQL

Reason behind writing this query is to know which club is proving swimming services.

Query 2. Write an SQL statement that correctly implements a JOIN statement over two or more entities to select a subset of data. Explain the purpose of your query for your database solution.

select t.type, m.firstName,m.state,c.clubName from traineemember t inner join member m On t.memberID = m.memberID and FirstName = 'Olivia' inner join club c On c.clubID = m.clubID;

SQL

Reason behind writing this query is to know the member name olivia joined which gym.

Query 3. Write an SQL statement that correctly uses the ‘>’ operator to select a subset of data for a single entity. Explain the purpose of your query for your database solution.

select c.clubName,m.firstName as memberName,m.state, c.Services,p.PerHour as Fees from trainer tr inner join personaltrained p

on tr.TrainerID = p.TrainerID and perhour > 150 inner join traineemember t on t.traineeID =p.traineeID inner join member m on m.memberID =t.traineeID inner join club c on c.clubID = m.clubID;

SQL

Reason behind writing this query is to know which trainer is charging perhours fees > 150

Query 4. Write an SQL statement that correctly uses the ‘GROUP BY’ operator to select an aggregation of data (e.g. SUM, AVG, COUNT). Explain the purpose of your query for your database solution.

select count(TraineeID),trainerID,PerHour as Fee from personaltrained Group by TrainerID

SQL

Reason behind writing this query is to know which trainer have multiple trainee.

Query 5. Write an SQL statement that correctly uses the ‘IN’ symbol to select a subset of data for a single entity. Explain the purpose of your query for your database solution.

select count(MemberID) ,firstName, Email,State,Status from member where state in ('QLD','NSW') group by firstName

SQL

Reason Behind writing this query is to know which to count how many members are active in QLD and NSW state

Query 6. Write an SQL statement that correctly uses the ‘UPDATE’ symbol to modify at least three rows of data in one of your tables. Explain the purpose of your query for your database solution.

Update personaltrained set feeHour = 350 where PersonalTrainedID = 1;