Boat Managment System Queries with Solutions using MySql

 The boat management system is a schema to manage the data regarding sailors, boats and reserves.

By creating various tables in the database we can easily manage these data.

First, we have to create the database "boats" :

mysql> CREATE DATABASE boats;

Then we have to use the boats database:

USE boats;

In the database we have to create different tables:

Creating table sailors

CREATE TABLE sailors
  (
  sid integer,
  sname varchar(20),
  rating integer,
  age integer
  );

Creating table boats

CREATE TABLE boats
  (
  bid integer,
  bname varchar(20),
  color varchar(20)
  );

Creating table reserves

CREATE TABLE reserves
  (
  sid integer,
  bid integer,
  day1 date
  );

After creating all the tables, we have to insert records into these tables:
 

Insert records into the sailors table:

insert into sailors values(22,'dustin',7,45);
 insert into sailors values(29,'brutus',1,33);
 insert into sailors values(31,'lubber',79,55);
 insert into sailors values(32,'andy',8,25);
 insert into sailors values(58,'rusty',10,35);
 insert into sailors values(58,'buplb',10,35);
 insert into sailors values(58,'buplerb',10,35);
 insert into sailors values(22,'bb',10,35);

Insert records into the boats table:

insert into boats values(101,'interlake','blue');
 insert into boats values(102,'interlake','red');
 insert into boats values(103,'clipper','green');
 insert into boats values(104,'marine','red');

Insert records into the reserves table:

insert into reserves values(22,101,'2004-01-01');
 insert into reserves values(22,102,'2004-01-01');
 insert into reserves values(22,103,'2004-02-01');
 insert into reserves values(22,105,'2004-02-01');
 insert into reserves values(31,103,'2005-05-05');
 insert into reserves values(32,104,'2005-04-07');

After all creating database, creating tables and then inserting records into them, we have to perform queries on these tables:

Problem#1:

Find the names of sailors who have reserved a red boat.

Solution:

SELECT s.sname
 FROM sailors s
 JOIN reserves r
 ON r.sid=s.sid join boats b
 ON r.bid=b.bid where b.color='red';

Problem#2:

Find the names of the Sailors who have reserved at least one boat.

Solution:

SELECT sname
 FROM sailors
 WHERE sid
 IN (
  SELECT sid
  FROM reserves
  GROUP BY sid
  );

Problem#3:

Compute increments for the ratings of persons who have sailed two different boats on the same day.

Solution:

SELECT r.sid, r.day1, COUNT(*), s.rating
 FROM reserves r
 JOIN sailors s
 ON r.sid=s.sid
 GROUP BY day1
 HAVING COUNT(r.day1)=2;
 
 /*Or*/
 
 SELECT s.sid, s.sname, COUNT(r.sid) c, s.rating+1 "rating"
 FROM sailors s
 JOIN reserves r ON s.sid=r.sid GROUP BY day1
 HAVING c>1;
 
 SELECT * FROM sailors;

Problem#4:

Find the ages of sailors whose name begins and ends with B and has at least 3 characters.

Solution:

SELECT sname, age
 FROM sailors
 WHERE sname LIKE 'B%_%B';

Problem#5:

Find the names of sailors who have reserved a red and a
 green boat.

Solution:

SELECT s.sname, b.color, s.sid
 FROM sailors s
 JOIN reserves r ON r.sid=s.sid
 JOIN boats b ON r.bid=b.bid
 AND b.color='red'
 WHERE r.sid IN(
  SELECT s.sid
  FROM sailors s
  JOIN reserves r ON r.sid=s.sid
  JOIN boats b ON r.bid=b.bid
  WHERE b.color='green'
  );

Problem#6:

Find the sids of all sailors who have reserved red boats but not green boats.

Solution:

SELECT s.sname, b.color, s.sid
 FROM sailors s
 JOIN reserves r ON r.sid=s.sid
 JOIN boats b ON r.bid=b.bid
 AND b.color='red'
 WHERE r.sid NOT IN(
  SELECT s.sid
  FROM sailors s
  JOIN reserves r ON r.sid=s.sid
  JOIN boats b ON r.bid=b.bid
  WHERE b.color='green'
  );
 SELECT s.sname, b.color, s.sid
 FROM sailors s
 JOIN reserves r ON r.sid=s.sid
 JOIN boats b ON r.bid=b.bid
 WHERE b.color='green';

Problem#7:

Find the sailors with the highest rating.

Solution:

SELECT sname, max(rating)
 FROM sailors;

Problem#8:

Find the name of the oldest sailor.

Solution:

SELECT sname
 FROM (
  SELECT sname,max(age)
  FROM sailors
  ) t1;

Problem#9:

Count the number of different sailor names.

Solution:

SELECT COUNT(*)
 FROM (
  SELECT sname
  FROM sailors
  GROUP BY sname
  ) t1;

Problem#10:

Find the no. of sailors who is eligible to vote for each
 rating level.

Solution:

SELECT COUNT(sname), rating
 FROM sailors
 WHERE age>18 group by rating;

We are codersarts provides the best solutions for any type of database questions solutions and answers like mysql query, query optimisation, MySQL Homework Help, MySQL Assignment Help, need help in sql query. Contact us now

#mysqlPracticeExercises #mysqlworksheets #mysqlProjectsForPractice

#mysqlWorkbenchExercises #mysqlPracticeDatabase #mysqlExamQuestions

#mysqlExercisesWithAnswers #mysqlLabExercisesWithSolutions