9 HINT use a Join statement I had a query that would show me all of the trips reservations and all of the customers even if the customer did not make a reservation 1 0 HINT use a Join statement I had a query that showed me all of the trips and all of the guides Please submit an SQL statement as well as your answer to this question, which trip did not have a guide Please help, thank you text file below Create Schema Colonial Use Colonial CREATE TABLE GUIDE ( GUIDE NUM CHAR ( 4 ) PRIMARY KEY, LAST NAME CHAR ( 1 5 ) , FIRST NAME CHAR ( 1 5 ) , ADDRESS CHAR ( 2 5 ) , CITY CHAR ( 2 5 ) , STATE CHAR ( 2 ) , POSTAL CODE CHAR ( 5 ) , PHONE NUM CHAR ( 1 2 ) , HIRE DATE DATE ) CREATE TABLE CUSTOMER ( CUSTOMER NUM CHAR ( 4 ) PRIMARY KEY, LAST NAME CHAR ( 3 0 ) NOT NULL, FIRST NAME CHAR ( 3 0 ) , ADDRESS CHAR ( 3 5 ) , CITY CHAR ( 3 5 ) , STATE CHAR ( 2 ) , POSTAL CODE CHAR ( 5 ) , PHONE CHAR ( 1 2 ) ) CREATE TABLE RESERVATION ( RESERVATION ID CHAR ( 7 ) PRIMARY KEY, TRIP ID DECIMAL ( 3 , 0 ) , TRIP DATE DATE, NUM PERSONS DECIMAL ( 3 , 0 ) , TRIP PRICE DECIMAL ( 6 , 2 ) , OTHER FEES DECIMAL ( 6 , 2 ) , CUSTOMER NUM CHAR ( 4 ) ) CREATE TABLE TRIP ( TRIP ID DECIMAL ( 3 , 0 ) PRIMARY KEY, TRIP NAME CHAR ( 7 5 ) , START LOCATION CHAR ( 5 0 ) , STATE CHAR ( 2 ) , DISTANCE DECIMAL ( 4 , 0 ) , MAX GRP SIZE DECIMAL ( 4 , 0 ) , TYPE CHAR ( 2 0 ) , SEASON CHAR ( 2 0 ) ) CREATE TABLE TRIP GUIDES ( TRIP ID DECIMAL ( 3 , 0 ) , GUIDE NUM CHAR ( 4 ) , PRIMARY KEY ( TRIP ID , GUIDE NUM ) ) INSERT INTO GUIDE VALUES ( ' AM 0 1 ' , 'Abrams','Miles', ' 5 4 Quest Ave ' , 'Williamsburg', ' MA ' , ' 0 1 0 9 6 ' , ' 6 1 7 5 5 5 6 0 3 2 ' , ' 2 0 1 2 6 3 ' ) INSERT INTO GUIDE VALUES ( ' BR 0 1 ' , 'Boyers','Rita', ' 1 4 0 Oakton Rd ' , 'Jaffrey', ' NH ' , ' 0 3 4 5 2 ' , ' 6 0 3 5 5 5 2 1 3 4 ' , ' 2 0 1 2 3 4 ' ) INSERT INTO GUIDE VALUES ( ' DH 0 1 ' , 'Devon','Harley', ' 2 5 Old Ranch Rd ' , 'Sunderland', ' MA ' , ' 0 1 3 7 5 ' , ' 7 8 1 5 5 5 7 7 6 7 ' , ' 2 0 1 2 1 8 ' ) INSERT INTO GUIDE VALUES ( ' GZ 0 1 ' , 'Gregory','Zach', ' 7 Moose Head Rd ' , 'Dummer', ' NH ' , ' 0 3 5 8 8 ' , ' 6 0 3 5 5 5 8 7 6 5 ' , ' 2 0 1 2 1 1 4 ' ) INSERT INTO GUIDE VALUES ( ' KS 0 1 ' , 'Kiley','Susan', ' 9 4 3 Oakton Rd ' , 'Jaffrey', ' NH ' , ' 0 3 4 5 2 ' , ' 6 0 3 5 5 5 1 2 3 0 ' , ' 2 0 1 3 4 8 ' ) INSERT INTO GUIDE VALUES ( ' KS 0 2 ' , 'Kelly','Sam', ' 9 Congaree Ave ' , 'Fraconia', ' NH ' , ' 0 3 5 8 0 ' , ' 6 0 3 5 5 5 0 0 0 3 ' , ' 2 0 1 3 6 1 0 ' ) INSERT INTO GUIDE VALUES ( ' MR 0 1 ' , 'Marston','Ray', ' 2 4 Shenandoah Rd ' , 'Springfield', ' MA ' , ' 0 1 1 0 1 ' , ' 7 8 1 5 5 5 2 3 2 3 ' , ' 2 0 1 5 9 1 4 ' ) INSERT INTO GUIDE VALUES ( ' RH 0 1 ' , 'Rowan','Hal', ' 1 2 Heather Rd ' , 'Mount Desert', ' ME ' , ' 0 4 6 6 0 ' , ' 2 0 7 5 5 5 9 0 0 9 ' , ' 2 0 1 5 6 2 ' ) INSERT INTO GUIDE VALUES ( ' SL 0 1 ' , 'Stevens','Lori', ' 1 5 Riverton Rd ' , 'Coventry', ' VT ' , ' 0 5 8 2 5 ' , ' 8 0 2 5 5 5 3 3 3 9 ' , ' 2 0 1 4 9 5 ' ) INSERT INTO GUIDE VALUES ( ' UG 0 1 ' , 'Unser','Glory', ' 3 4 2 Pineview St ' , 'Danbury', ' CT ' , ' 0 6 8 1 0 ' , ' 2 0 3 5 5 5 8 5 3 4 ' , ' 2 0 1 5 2 2 ' ) INSERT INTO CUSTOMER VALUES ( ' 1 0 1 ' , 'Northfold','Liam', ' 9 Old Mill Rd ' , 'Londonderry', ' NH ' , ' 0 3 0 5 3 ' , ' 6 0 3 5 5 5 7 5 6 3 ' ) INSERT INTO CUSTOMER VALUES ( ' 1 0 2 ' , 'Ocean','Arnold', ' 2 3 3 2 South St Apt 3 ' , 'Springfield', ' MA ' , ' 0 1 1 0 1 ' , ' 4 1 3 5 5 5 3 2 1 2 ' ) INSERT INTO CUSTOMER VALUES ( ' 1 0 3 ' , 'Kasuma','Sujata', ' 1 3 2 Main St 1 ' , 'East Hartford', ' CT ' , ' 0 6 1 0 8 ' , ' 8 6 0 5 5 5 0 7 0 3 ' ) INSERT INTO CUSTOMER VALUES ( ' 1 0 4 ' , 'Goff','Ryan', ' 1 6 4 A South Bend Rd ' , 'Lowell', ' MA ' , ' 0 1 8 5 4 ' , ' 7 8 1 5 5 5 8 4 2 3 ' ) INSERT INTO CUSTOMER VALUES ( ' 1 0 5 ' , 'McLean','Kyle', ' 3 4 5 Lower Ave ' , 'Wolcott', ' NY ' , ' 1 4 5 9 0 ' , ' 5 8 5 5 5 5 5 3 2 1 ' ) INSERT INTO CUSTOMER VALUES ( ' 1 0 6 ' , 'Morontoia','Joseph', ' 1 5 6 Scholar St ' , 'Johnston', ' RI ' , ' 0 2 9 1 9 ' , ' 4 0 1 5 5 5 4 8 4 8 ' ) INSERT INTO CUSTOMER VALUES ( ' 1 0 7 ' , 'Marchand','Quinn', ' 7 6 Cross Rd ' , 'Bath', ' NH ' , ' 0 3 7 4 0 ' , ' 6 0 3 5 5 5 0 4 5 6 ' ) INSERT INTO CUSTOMER VALUES ( ' 1 0 8 ' , 'Rulf','Uschi', ' 3 2 Sheep Stop St ' , 'Edinboro', ' PA ' , ' 1 6 4 1 2 ' , ' 8 1 4 5 5 5 5 5 2 1 ' ) INSERT INTO CUSTOMER VALUES ( ' 1 0 9 ' , 'Caron','Jean Luc', ' 1 0 Greenfield St ' , 'Rome', ' ME ' , ' 0 4 9 6 3 ' , ' 2 0 7 5 5 5 9 6 4 3 ' ) INSERT INTO CUSTOMER VALUES ( ' 1 1 0 ' , 'Bers','Martha', ' 6 5 Granite St ' , 'York', ' NY ' , ' 1 4 5 9 2 ' , ' 5 8 5 5 5 5 0 1 1 1 ' ) INSERT INTO CUSTOMER VALUES ( ' 1 1 2 ' , 'Jones','Laura', ' 3 7 3 Highland Ave ' , 'Somerville', ' MA ' , ' 0 2 1 4 3 ' , ' 8 5 7 5 5 5 6 2 5 8 ' ) INSERT INTO CUSTOMER VALUES ( ' 1 1 5 ' , 'Vaccari','Adam', ' 1 2 8 2 Ocean Walk','Ocean CITY', ' NJ ' , ' 0 8 2 2 6 ' , ' 6 0 9 5 5 5 5 2 3 1 ' ) INSERT INTO CUSTOMER VALUES ( ' 1 1 6 ' , 'Murakami','Iris', ' 7 Cherry Blossom St ' , 'Weymouth', ' MA ' , ' 0 2 1 8 8 ' , ' 6 1 7 5 5 5 6 6 6 5 ' ) INSERT INTO CUSTOMER VALUES ( ' 1 1 9 ' , 'Chau','Clement', ' 1 8 Ark Ledge Ln ' , 'Londonderry', ' VT ' , ' 0 5 1 4 8 ' , ' 8 0 2 5 5 5 3 0 9 6 ' ) INSERT INTO CUSTOMER VALUES ( ' 1 2 0 ' , 'Gernowski','Sadie', ' 2 4 Stump Rd

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Aug 28, 2024

9 . HINT use a Join statement. I had a query that would show me all of the trips reservations and all of the customers

9 .

HINT use a Join statement. I had a query that would show me all of the trips reservations and all of the customers even if the customer did not make a reservation.

10 .

HINT use a Join statement. I had a query that showed me all of the trips and all of the guides. Please submit an SQL statement as well as your answer to this question, which trip did not have a guide?

Please help, thank you!

text file below:

Create Schema Colonial;

Use Colonial;

CREATE TABLE GUIDE

(

GUIDE

_

NUM CHAR

(4)

PRIMARY KEY,

LAST

_

NAME CHAR

(15),

FIRST

_

NAME CHAR

(15),

ADDRESS CHAR

(25),

CITY CHAR

(25),

STATE CHAR

(2),

POSTAL

_

CODE CHAR

(5),

PHONE

_

NUM CHAR

(12),

HIRE

_

DATE DATE

)

;

CREATE TABLE CUSTOMER

(

CUSTOMER

_

NUM CHAR

(4)

PRIMARY KEY,

LAST

_

NAME CHAR

(30)

NOT NULL,

FIRST

_

NAME CHAR

(30),

ADDRESS CHAR

(35),

CITY CHAR

(35),

STATE CHAR

(2),

POSTAL

_

CODE CHAR

(5),

PHONE CHAR

(12))

;

CREATE TABLE RESERVATION

(

RESERVATION

_

ID CHAR

(7)

PRIMARY KEY,

TRIP

_

ID DECIMAL

(3, 0),

TRIP

_

DATE DATE,

NUM

_

PERSONS DECIMAL

(3, 0),

TRIP

_

PRICE DECIMAL

(6, 2),

OTHER

_

FEES DECIMAL

(6, 2),

CUSTOMER

_

NUM CHAR

(4))

;

CREATE TABLE TRIP

(

TRIP

_

ID DECIMAL

(3, 0)

PRIMARY KEY,

TRIP

_

NAME CHAR

(75),

START

_

LOCATION CHAR

(50),

STATE CHAR

(2),

DISTANCE DECIMAL

(4, 0),

MAX

_

GRP

_

SIZE DECIMAL

(4, 0),

TYPE CHAR

(20),

SEASON CHAR

(20))

;

CREATE TABLE TRIP

_

GUIDES

(

TRIP

_

ID DECIMAL

(3, 0),

GUIDE

_

NUM CHAR

(4),

PRIMARY KEY

(

TRIP

_

,

GUIDE

_

NUM

))

;

INSERT INTO GUIDE

VALUES

('

01',

'Abrams','Miles',

' 54

Quest Ave.

',

'Williamsburg',

'

',' 01096',' 617 - 555 - 6032',' 2012 - 6 - 3')

;

INSERT INTO GUIDE

VALUES

('

01',

'Boyers','Rita',

' 140

Oakton Rd

.',

'Jaffrey',

'

',' 03452',' 603 - 555 - 2134',' 2012 - 3 - 4')

;

INSERT INTO GUIDE

VALUES

('

01',

'Devon','Harley',

' 25

Old Ranch Rd

.',

'Sunderland',

'

',' 01375',' 781 - 555 - 7767',' 2012 - 1 - 8')

;

INSERT INTO GUIDE

VALUES

('

01',

'Gregory','Zach',

' 7

Moose Head Rd

.',

'Dummer',

'

',' 03588',' 603 - 555 - 8765',' 2012 - 11 - 4')

;

INSERT INTO GUIDE

VALUES

('

01',

'Kiley','Susan',

' 943

Oakton Rd

.',

'Jaffrey',

'

',' 03452',' 603 - 555 - 1230',' 2013 - 4 - 8')

;

INSERT INTO GUIDE

VALUES

('

02',

'Kelly','Sam',

' 9

Congaree Ave.

',

'Fraconia',

'

',' 03580',' 603 - 555 - 0003',' 2013 - 6 - 10')

;

INSERT INTO GUIDE

VALUES

('

01',

'Marston','Ray',

' 24

Shenandoah Rd

.',

'Springfield',

'

',' 01101',' 781 - 555 - 2323',' 2015 - 9 - 14')

;

INSERT INTO GUIDE

VALUES

('

01',

'Rowan','Hal',

' 12

Heather Rd

.',

'Mount Desert',

'

',' 04660',' 207 - 555 - 9009',' 2015 - 6 - 2')

;

INSERT INTO GUIDE

VALUES

('

01',

'Stevens','Lori',

' 15

Riverton Rd

.',

'Coventry',

'

',' 05825',' 802 - 555 - 3339',' 2014 - 9 - 5')

;

INSERT INTO GUIDE

VALUES

('

01',

'Unser','Glory',

' 342

Pineview St

.',

'Danbury',

'

',' 06810',' 203 - 555 - 8534',' 2015 - 2 - 2')

;

INSERT INTO CUSTOMER

VALUES

(' 101',

'Northfold','Liam',

' 9

Old Mill Rd

.',

'Londonderry',

'

',' 03053',' 603 - 555 - 7563')

;

INSERT INTO CUSTOMER

VALUES

(' 102',

'Ocean','Arnold',

' 2332

South St

.

Apt

3',

'Springfield',

'

',' 01101',' 413 - 555 - 3212')

;

INSERT INTO CUSTOMER

VALUES

(' 103',

'Kasuma','Sujata',

' 132

Main St

.

1',

'East Hartford',

'

',' 06108',' 860 - 555 - 0703')

;

INSERT INTO CUSTOMER

VALUES

(' 104',

'Goff','Ryan',

' 164

A South Bend Rd

.',

'Lowell',

'

',' 01854',' 781 - 555 - 8423')

;

INSERT INTO CUSTOMER

VALUES

(' 105',

'McLean','Kyle',

' 345

Lower Ave.

',

'Wolcott',

'

',' 14590',' 585 - 555 - 5321')

;

INSERT INTO CUSTOMER

VALUES

(' 106',

'Morontoia','Joseph',

' 156

Scholar St

.',

'Johnston',

'

',' 02919',' 401 - 555 - 4848')

;

INSERT INTO CUSTOMER

VALUES

(' 107',

'Marchand','Quinn',

' 76

Cross Rd

.',

'Bath',

'

',' 03740',' 603 - 555 - 0456')

;

INSERT INTO CUSTOMER

VALUES

(' 108',

'Rulf','Uschi',

' 32

Sheep Stop St

.',

'Edinboro',

'

',' 16412',' 814 - 555 - 5521')

;

INSERT INTO CUSTOMER

VALUES

(' 109',

'Caron','Jean Luc',

' 10

Greenfield St

.',

'Rome',

'

',' 04963',' 207 - 555 - 9643')

;

INSERT INTO CUSTOMER

VALUES

(' 110',

'Bers','Martha',

' 65

Granite St

.',

'York',

'

',' 14592',' 585 - 555 - 0111')

;

INSERT INTO CUSTOMER

VALUES

(' 112',

'Jones','Laura',

' 373

Highland Ave.

',

'Somerville',

'

',' 02143',' 857 - 555 - 6258')

;

INSERT INTO CUSTOMER

VALUES

(' 115',

'Vaccari','Adam',

' 1282

Ocean Walk','Ocean CITY',

'

',' 08226',' 609 - 555 - 5231')

;

INSERT INTO CUSTOMER

VALUES

(' 116',

'Murakami','Iris',

' 7

Cherry Blossom St

.',

'Weymouth',

'

',' 02188',' 617 - 555 - 6665')

;

INSERT INTO CUSTOMER

VALUES

(' 119',

'Chau','Clement',

' 18

Ark Ledge Ln

.',

'Londonderry',

'

',' 05148',' 802 - 555 - 3096')

;

INSERT INTO CUSTOMER

VALUES

(' 120',

'Gernowski','Sadie',

' 24

Stump Rd

.<Step by Step Solution There are 3 Steps involved in it Step: 1 Get Instant Access to Expert-Tailored Solutions See step-by-step solutions with expert insights and AI powered tools for academic success View Solution Step: 2 Sign Up to view Step: 3 Sign Up to viewAce Your Homework with AI Get the answers you need in no time with our AI-driven, step-by-step assistance Get Started Recommended Textbook for Graph Database Modeling With Neo4j Authors: Ajit Singh 2nd Edition B0BDWT2XLR, 979-8351798783 More BooksStudents also viewed these Databases questions Question ★ ★ ★ ★ ★ Following Table 27.10, compute the prices of first, second, and Nth-to-default bonds assuming that defaults are uncorrelated and that there are 5, 10, 20, and 50 bonds in the portfolio. How are the... Answered: 1 week ago Question ★ ★ ★ ★ ★ Applebees is the largest casual dining chain in the world, with over 1,800 locations in 21 countries throughout the world. The menu features beef, chicken, and pork items, as well as burgers, pasta,... Answered: 1 week ago Question ★ ★ ★ ★ ★ Share the same race with most of your immediate colleagues and high-level leaders in your field? Answered: 1 week ago Question ★ ★ ★ ★ ★ Old Camp Company manufactures awnings for its own line of tents. The company is currently operating at capacity and has received an offer from one of its suppliers to make the 10,000 awnings it needs... Answered: 1 week ago Question ★ ★ ★ ★ ★ What does the following line of code mean?: system("kill -9 $(ps -l|grep -w Z|tr -s ' '|cut -d ' ' -f 5)"); Please provide another way of writing this line. Full code for reference: #include #include... Answered: 1 week ago Question ★ ★ ★ ★ ★ T, and T in the given figure are 5 kg 120 N+ 777 3 kg (1) 28 N, 48 N (3) 96 N, 56 N T 1 T 2 7 kg Smooth surface (2) 48 N, 28 N (4) 56 N, 96 N Answered: 1 week ago Question ★ ★ ★ ★ ★ In the mechanism shown in Fig. 2.53, the crank AB, 70 mm long, rotates clockwise at 110 rev/min. CD is 140 mm long. Link BD is 260 mm long and slides through a swivelling pin E at the lower end of... Answered: 1 week ago Question ★ ★ ★ ★ ★ At January 1 , 2 0 2 4 , Brant Cargo acquired equipment by issuing a four - year, $ 1 5 0 , 0 0 0 ( payable at maturity ) , 6 % note. The market rate of interest for notes of similar risk is 1 2 % .... Answered: 1 week ago Question ★ ★ ★ ★ ★ A simulation of a major traffic intersection is to be conducted, with the objectives of improving current traffic flow. Provide three iterations, in increasing order of complexity, of steps 1 and 2... Answered: 1 week ago Question ★ ★ ★ ★ ★ In which of the following situations a company needs to resort to the eDiscovery in an ECM system? Select one: a . The company needs to find the email addresses of its customers to send out the... Answered: 1 week ago Question ★ ★ ★ ★ ★ You run a restaurant, an event space and do catering for private events. You use a single, group-wide overhead allocation rate, with pricing at 145% of cost. You can make improvements by implementing... Answered: 1 week ago Question ★ ★ ★ ★ ★ Teamwork. As directed by your instructor, meet in a small group to discuss the following questions. (Objective 2) a. How do you feel when a teacher, manager, colleague, or service representative... Answered: 1 week ago Question ★ ★ ★ ★ ★ Technology. During the next conversation you have with a coworker, a family member, or someone not from this class, use paraphrasing at least once. E-mail your instructor to describe the effect... Answered: 1 week ago Question ★ ★ ★ ★ ★ Discuss five ways nonverbal messages could be presented in a positive manner when interviewing for a supervisory position in a business organization. Answered: 1 week agoPrevious Question Next QuestionView Answer in SolutionInn App Download on the App Store Continue with the mobile websiteServices Sitemap Fun Definitions Become Tutor Study Help Categories Recent Questions Expert Questions Company Info Security Copyrights Privacy Policy Terms & Conditions SolutionInn Fee Scholarship Get In Touch About Us Contact Us Career Jobs FAQ Campus Ambassador Secure Payment Download Our App © 2024 SolutionInn. All Rights Reservedwindow.addEventListener("load",function(){jQuery(document).ready(function(t){t.ajax({type:"POST",url:"/",data:{trackUserActivity:!0,reqUri:document.URL,referer:document.referrer},success:function(t){}})})},!1),window.addEventListener("load",function(){jQuery(document).ready(function(t){t.ajax({type:"POST",url:"/",data:{insertCrawler:!0,reqUri:document.URL,parseTime:"0.056",queryTime:"0.01654768548584",queryCount:"30"},success:function(t){}})})},!1),window.addEventListener("load",function(){jQuery(document).ready(function(){function t(t="",n=!1){var i="itms-apps://itunes.apple.com/app/id6462455425",e="openApp://action?"+t;isAndroid()?(setTimeout(function(){return window.location="market://details?id=com.solutioninn.studyhelp",!1},25),window.location=e):isIOS()?(setTimeout(function(){return window.location=i,!1},25),window.location=e):(i="https://apps.apple.com/in/app/id6462455425",n&&(i="https://play.google.com/store/apps/details?id=com.solutioninn.studyhelp"),window.open("about:blank","_blank").location.href=i)}jQuery("#appModal").modal("show"),jQuery(".download-app-btn").click(function(){t(jQuery(this).attr("question_open_url"))}),jQuery(".redirection").click(function(){var n=jQuery(this).attr("question_open_url"),i=jQuery(this).attr("data-id");void 0!=n?1==i?t(n,!0):t(n,!1):1==i?t("",!0):t("",!1)}),jQuery(".app-notification-close").click(function(){jQuery(".app-notification-section").css("visibility","hidden");var t=new FormData;t.append("hide_notification",!0),jQuery.ajax({type:"POST",url:"/",data:t,cache:!1,contentType:!1,processData:!1,beforeSend:function(){},success:function(t){location.reload()}})})})},!1);