[go: up one dir, main page]
Scribd
Upload
497 views3 pages

Workshop - Week 8 Mysql Practical Exercise

This document provides an exercise on using SQL commands to create databases and tables, insert data, and write queries using joins. Students are instructed to: 1. Create a database called "sbrcommands" and three tables - sailors, boats, and reserves - to store information about sailors, boats, and reservations. 2. Insert test data into the three tables. 3. Write a query to find the sailor ID, boat name, and color for reservations on September 8, 1998. 4. Write a query to find the names of sailors who have reserved boat 103. 5. Write a query to find the names of sailors who have reserved a red boat. 6. Ex

Uploaded by

Kushal Bajracharya
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
497 views3 pages

Workshop - Week 8 Mysql Practical Exercise

This document provides an exercise on using SQL commands to create databases and tables, insert data, and write queries using joins. Students are instructed to: 1. Create a database called "sbrcommands" and three tables - sailors, boats, and reserves - to store information about sailors, boats, and reservations. 2. Insert test data into the three tables. 3. Write a query to find the sailor ID, boat name, and color for reservations on September 8, 1998. 4. Write a query to find the names of sailors who have reserved boat 103. 5. Write a query to find the names of sailors who have reserved a red boat. 6. Ex

Uploaded by

Kushal Bajracharya
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
You are on page 1/ 3

Workshop – Week 8 MySQL Practical Exercise

Exercise 1
This exercise is similar to week 5. Students at this stage should be illustrated how to
create tables, alter tables, and write complex SQL queries.
Note: Use SQL commands to create the database, tables, records, and Queries.

1. Create the database sbrcommands and the following tables sailors,


boats, and reserves which are reproduced as follows:

sailors(sid: VARCHAR(2) PK, sname: VARCHAR(7), rating:


SMALLINT, age: DOUBLE
boats(bid: VARCHAR(3) PK, bname: VARCHAR (10), colour:
VARCHAR(6)
reserves(sid: VARCHAR(2) FK, bid: VARCHAR(3) FK, day: DATE)

sid sname rating age sid bid Day bid bname colour

22 Dustin 7 45.0 22 101 10/10/98 101 Interlake blue


29 Brutus 1 33.0 22 102 10/10/98 102 Interlake red
31 Lubber 8 55.5 22 103 10/8/98 103 Clipper green
32 Andy 8 25.5 22 104 10/7/98 104 Marine red
58 Rusty 10 35.0 31 102 11/10/98
64 Horatio 7 35.0 31 103 11/6/98 Boats
71 Zorba 10 16.0 31 104 11/12/98
74 Horatio 9 35.0 64 101 9/5/98
85 Art 3 25.5 64 102 9/8/98
95 Bob 3 63.5 74 103 9/8/98

Sailors Reserves

2. Enter the test data for the three tables as shown above.

INSERT INTO `sbr`.`boats`(`bid`,`bname`, `colour`) VALUES


(1,'haha','violet'),
(2,'cook','brown');

INSERT INTO `sbr`.`sailors`(`sid`,`sname`,`rating`,`age`) VALUES


(1,'ramesh','8','45'),
(55,'Homer','6','55');

INSERT INTO `sbr`.`reserves`(`sid`,`bid`,`reserve_day`)VALUES

1
(1,1,'1998-08-11'),
(2,22,'1998-01-11');

3. Formulate SQL query to find the sailor ID, boat name, and colour of those
boats reserved on 9/8/98.

SELECT sailors.sid, boats.bname, boats.colour


from sailors
JOIN reserves on sailors.sid = reserves.sid
JOIN boats on boats.bid = reserves.bid
WHERE reserves.reserve_day='1998-09-08'

4. Formulate SQL query to find the names of sailors who have reserved boat
103.
SELECT sailors.sname
from sailors
JOIN reserves on sailors.sid = reserves.sid
WHERE reserves.bid=103

2
5. Formulate SQL query to find the names of sailors who have reserved a red
boat.
SELECT sailors.sname
from sailors
JOIN reserves on sailors.sid = reserves.sid
JOIN boats on boats.bid = reserves.bid
WHERE boats.colour='red';

6. The above commands are JOIN queries. Explain the concept of join.
A join is a SQL procedure that establishes a relation between two or more
rows and columns by creating a relationship between them depending on
matched attributes. In a SQL database system, join statements are used for the
bulk of complex queries. Join is a query that retrieves data from two tables
and combines them together just to create a single collection of information.
It's being used to merge columns from two or more tables by using data that
seem to be similar between both.

You might also like