Class Seminar

Alexander Schwartz

******@***.***

*. Create a seminar team roster, with positions, for one weekend. Include the date of the seminar and the institution name.

SELECT CONCAT(people.fname, ' ', people.mname, '. ',people.lname) as 'Person', roster.talk as 'Talk',

roster.meditation as 'Meditation', roster.job as 'Job', roster.class as 'Class',

seminar.institution as 'Institution', seminar.date as 'Date'

FROM seminar INNER JOIN

(roster INNER JOIN people ON roster.personid=people.personid)

ON seminar.seminarid=roster.seminarid

WHERE roster.seminarid=1;

2. List all weekends and positions for one individual who has worked more than one seminar.

SELECT CONCAT(people.fname, ' ', people.mname, '. ',people.lname) as 'Person', roster.talk as 'Talk',

roster.meditation as 'Meditation', roster.job as 'Job', roster.class as 'Class',

seminar.institution as 'Institution', seminar.date as 'Date'

FROM seminar INNER JOIN

(roster INNER JOIN people ON roster.personid=people.personid)

ON seminar.seminarid=roster.seminarid

WHERE people.personid=1;

3. List all people that have been a seminar leader. Include the weekend date and the institution name.

SELECT CONCAT(people.fname, ' ', people.mname, '. ',people.lname) as 'Person', roster.job as 'Job', roster.class as 'Class',

seminar.institution as 'Institution', seminar.date as 'Date'

FROM seminar INNER JOIN

(roster INNER JOIN people ON roster.personid=people.personid)

ON seminar.seminarid=roster.seminarid

WHERE roster.job=2;

4. List all talks that have been given by one individual who has worked more than one seminar. Include the institution and date.

SELECT CONCAT(people.fname, ' ', people.mname, '. ',people.lname) as 'Person', roster.talk as 'Talk',

seminar.institution as 'Institution', seminar.date as 'Date'

FROM seminar INNER JOIN

(roster INNER JOIN people ON roster.personid=people.personid)

ON seminar.seminarid=roster.seminarid

WHERE people.personid=5;

5. List all people that have given a specific talk. This is for all seminars. You select the talk.

SELECT CONCAT(people.fname, ' ', people.mname, '. ',people.lname) as 'Person', roster.talk as 'Talk',

seminar.institution as 'Institution', seminar.date as 'Date'

FROM seminar INNER JOIN

(roster INNER JOIN people ON roster.personid=people.personid)

ON seminar.seminarid=roster.seminarid

WHERE roster.talk=5;

6. List all people that have worked more than one seminar. Include the number of seminars each one has worked.

SELECT CONCAT(people.fname, ' ', people.mname, '. ',people.lname) as 'Person', COUNT(roster.rosterid) as 'No. Seminars Worked'

FROM seminar INNER JOIN

(roster INNER JOIN people ON roster.personid=people.personid)

ON seminar.seminarid=roster.seminarid

GROUP BY roster.personid

HAVING COUNT(roster.rosterid)>1;

Contact this candidate