Write an SQL statement to show all Heather Sweeney Designs seminars and the -- c

Question

Write an SQL statement to show all Heather Sweeney Designs seminars and the -- customers that attended them. The output from this statement should also include any seminars that do not have any customers attending them (for example seminars with id 5 and 6). The SQL statement output should list SeminarID, SeminarDate, Location, SeminarTitle, CustomerID, LastName, and FirstName. Use JOIN ON syntax (Hint: you need to join three tables)

MySQL Workbench t View Query Database Server Tools Scripting Help File SQL, File 8 SQL Additions Navigator SCHEMAS Jump to t wrap Cell Contenta lt Grid Filter Rows Filter objects CustomerID ContactNumber Contactoate contacmype SeminarID Automatic context help is disabled. Us Rezuh 3-7 2013-10-12 the toolbar to manually get help for the current caret position or to toggle 2013-10-15 asswork3 2 utomatic help. hsd 2013-10-15 Form etter Seminar hsd2 2013-10-12 seminar hsd3 EmailseminarMessage Tables 2013-10-15 contact 2013-10-15 Formletterseminar customer 2013-10-12 2013-10-15 EmaiseminarMessage E ne item p oduct 2013-10-15 Form etter Seminar 2014-02-20 WebAccountCreation E seminar customer views 2014-02-20 EmalAccountMessage stored Procedures 2013-10-12 Seminar nctions EmailseminarMessage 2013-10-15 akila Apply Context Help Snippets contact 1 X toy db world Action output Information Duration/Fetch a 4 2 5.51 SELECT DISTINCT FirstName. Lastname. Phone FROM Customers NATURAL JOIN Title- Ermor Code: 1064. You have an emorin your SQL syntax, check the manual that comesponds 0.016 sec Table: 5 21:2248 CREATE TABLE CUSTOMER(CustomerIDIntNOTNULL auto increment. LastNameChar2... Emor Code: 1050. Table customer already exists 0.047 sec 6 21.2249 CREATE TABLECUSTOMER(CustomerlDintNOT NULL auto increment. LastNameChar2... Emor Code: 1050. Table customer already exsts 0.000 sec 7 21:23 20 SELECTDISTINCT First Name. Lastname. Phone FROM Customers JOIN Trke- Kitchen on Emor Code: 1064. You have an emorin your SQLsyntax: check the manual that comesponds 0.000 sec ContactDate ContactType 8 21:23.20 SELECT DISTINCT FirstName. Lastname. Phone FROM Customers JOIN Ttle "Kitchen on Emor Code: 1064. You have an emorin your SQL syntax check the manual that come 0.000 sec sponds Q 9 21:23 28 SELECT ROM hsd3 contact LIMITO. 1000 42 now netumed 0.015 sec 0.000 sec Object Info Query Completed

Explanation / Answer

Required Tables:

Tables
Seminar
SemarID
SeminarID
SeminarDate
SeminarTime
Location
SeminarTitle


Customer:
CustomerID
LastName
FirstName
EmailAddress

Seminar_Customer:
SeminarID
CustomerID

Query:

   1) We need to keep the left outer join in between Seminar and Seminar_Customer table to get all the semiar which are not attended by any customer.

2) To get the required outcome we need to perform the inner join later with the Customer table.

Query:

Select
S.SeminarID As SeminarID,
S.SeminarDate As SeminarDate,
S.Location As SeminarLocation,
S.SeminarTitle As SeminarTitle,
C.CustomerID As CustomerID,
C.LastName As LastName,
C.FirstName As FirstName
From Seminar_Customer CS
LEFT OUTER JOIN Seminar S ON S.SeminarID = CS.SeminarID
INNER JOIN Customer C ON CS.CustomerID = C.CustomerID
Where S.SeminarTitle = 'Heather Sweeney Designs'

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.