QuestionQuestion

Transcribed TextTranscribed Text

Questions: Part 1 Use SQL to express the following queries. You should enter your statements on Oracle VM or other SQL engine and take necessary screenshot for the statement and the query result as the following screenshot and put the screenshots in your word/PDF file. Sailer Reservation S# Name Age S# B# Day S1 Smith 20 S1 B1 1-Jan-15 S2 Jones 30 S1 B2 2-Jan-16 S3 Blake 25 S1 B3 3-Feb-17 S4 Lastname 20 S1 B4 4-Feb-18 S5 Adams 30 S2 B1 5-Mar-16 S2 B2 6-Mar-17 Boat S2 B3 B# Name Color 7-Apr-18 S3 B1 B1 Freedom Blue 8-May-17 S3 B2 9-Jul-17 B2 Paradisc Green S4 B1 B3 Miracle Red 10-Scp-17 B4 Splendor Yellow 1. Get sailer names for sailers who reserve Paradise using EXISTS. 2. Get sailer names for sailers who reserve Paradise using IN 3. Get sailer names for sailers who reserve Paradise using a flat query 4. Get the names of the boats that Jones reserves but "Lastname" does not reserve using MINUS 5. Get all pairs of sailer names such that the sailers concerned reserve at least one same boat. 6. Get sailer names for sailers who reserve all boats except Splendor. 7. Get sailer names for sailers who reserve all boats that Lastname reserves. 8. Get sailer names for sailers who reserve only the boats that Lastname reserves 9. Get sailer names and the number of boats they reserve. 10. Get sailer names for sailers who reserve more than two boats without using HAVING. 11. Get sailer names for sailers who reserve more than two boats using HAVING. 12. Get complete information of each sailer such that when the sailer reserves a boat, list boat detail; when the sailer does not reserve any boat, just list the sailer information. 13. Get sailer names and the number of boats that the sailer reverses. If the sailer does not reserve any boat, leave it null.

Solution PreviewSolution Preview

These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction of bibliographies out of text citations and references. Students may use these solutions for personal skill-building and practice. Unethical use is strictly forbidden.

-- 1. Get sailer names for sailers who reserve Paradise using EXISTS.
select s.NAME from SAILER s
where EXISTS (
    select * from BOAT b, RESERVATION r
    where r.b# = b.B# and s.S# = r.S# and b.NAME = 'Paradise'
);

-- 2. Get sailer names for sailers who reserve Paradise using IN

select s.NAME from SAILER s
where s.S# in (
    select r.S# from BOAT b inner join RESERVATION r
    on r.b# = b.B# where b.NAME = 'Paradise'
);

-- 3. Get sailer names for sailers who reserve Paradise using a flat query

select s.NAME from BOAT b, RESERVATION r, SAILER s
where b.B# = r.B# and r.S# = s.S# and b.NAME = 'Paradise';

-- 4. Get the names of the boats that Jones reserves but “Lastname” does not reserve using MINUS
SELECT b.* FROM BOAT b, RESERVATION r, SAILER s
where b.B# = r.B# and r.S# = s.S# and s.NAME = 'Jones'
MINUS
SELECT b.* FROM BOAT b, RESERVATION r, SAILER s
where b.B# = r.B# and r.S# = s.S# and s.NAME = 'Lastname'...

By purchasing this solution you'll be able to access the following files:
SolutionQueries.sql and SolutionCreation.sql.

$98.00
for this solution

or FREE if you
register a new account!

PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

Find A Tutor

View available Database Development Tutors

Get College Homework Help.

Are you sure you don't want to upload any files?

Fast tutor response requires as much info as possible.

Decision:
Upload a file
Continue without uploading

SUBMIT YOUR HOMEWORK
We couldn't find that subject.
Please select the best match from the list below.

We'll send you an email right away. If it's not in your inbox, check your spam folder.

  • 1
  • 2
  • 3
Live Chats