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
#mysqlWorkbenchExercises #mysqlPracticeDatabase #mysqlExamQuestions
コメント