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

Get solutions of database query related to mysql assignment help

#mysqlPracticeExercises #mysqlworksheets #mysqlProjectsForPractice

#mysqlWorkbenchExercises #mysqlPracticeDatabase #mysqlExamQuestions

#mysqlExercisesWithAnswers #mysqlLabExercisesWithSolutions