/* Advanced SQL
Division problems

Create two more tables for the Student-Teams database.
*/


/*  Create a table for team-building workshops taught. */

CREATE TABLE dbo.workshops
 (
	wksp_ID varchar(2) NOT NULL,
	wksp_name varchar(30) NOT NULL,
 CONSTRAINT PK_workshops PRIMARY KEY(wksp_ID)
 ); 



CREATE TABLE dbo.attendances
  (
	attnd_wksp_ID varchar(2) NOT NULL,
	attnd_stdid varchar(10) NOT NULL,
 CONSTRAINT PK_attend PRIMARY KEY(attnd_wksp_ID, attnd_stdid) ,
 CONSTRAINT FK_workshops FOREIGN KEY(attnd_wksp_ID) REFERENCES workshops,
 CONSTRAINT FK_students FOREIGN KEY(attnd_stdid) REFERENCES students
   ); 


/*  Insert workshop data */

insert into workshops
  (wksp_id, wksp_name)
  values ('EL', 'Effective Leadership');

insert into workshops
  (wksp_id, wksp_name)
  values ('CE', 'Communicating Effectively');

insert into workshops
  (wksp_id, wksp_name)
  values ('AL', 'Active Listening');

insert into workshops
  (wksp_id, wksp_name)
  values ('GO', 'Get Organized');


/* insert attendance data */

insert into attendances
   (attnd_wksp_id, attnd_stdid)
   values ('EL', 'bsmith');

insert into attendances
   (attnd_wksp_id, attnd_stdid)
   values ('EL', 'csanchez');

insert into attendances
   (attnd_wksp_id, attnd_stdid)
   values ('EL', 'kalt');

insert into attendances
   (attnd_wksp_id, attnd_stdid)
   values ('EL', 'bkry');

insert into attendances
   (attnd_wksp_id, attnd_stdid)
   values ('EL', 'lkomp');

insert into attendances
   (attnd_wksp_id, attnd_stdid)
   values ('EL', 'sdavis');

insert into attendances
   (attnd_wksp_id, attnd_stdid)
   values ('EL', 'aagassi');

insert into attendances
   (attnd_wksp_id, attnd_stdid)
   values ('EL', 'rfederer');

insert into attendances
   (attnd_wksp_id, attnd_stdid)
   values ('CE', 'lkomp');

insert into attendances
   (attnd_wksp_id, attnd_stdid)
   values ('CE', 'aagassi');

insert into attendances
   (attnd_wksp_id, attnd_stdid)
   values ('AL', 'lkomp');

insert into attendances
   (attnd_wksp_id, attnd_stdid)
   values ('AL', 'aagassi');

insert into attendances
   (attnd_wksp_id, attnd_stdid)
   values ('CE', 'doliver');

insert into attendances
   (attnd_wksp_id, attnd_stdid)
   values ('CE', 'wcronan');

insert into attendances
   (attnd_wksp_id, attnd_stdid)
   values ('AL', 'rfederer');

insert into attendances
   (attnd_wksp_id, attnd_stdid)
   values ('AL', 'kalt');

insert into attendances
   (attnd_wksp_id, attnd_stdid)
   values ('GO', 'akry');

insert into attendances
   (attnd_wksp_id, attnd_stdid)
   values ('GO', 'bkry');

insert into attendances
   (attnd_wksp_id, attnd_stdid)
   values ('GO', 'bsmith');

insert into attendances
   (attnd_wksp_id, attnd_stdid)
   values ('GO', 'calvarez');

insert into attendances
   (attnd_wksp_id, attnd_stdid)
   values ('GO', 'cmoya');

insert into attendances
   (attnd_wksp_id, attnd_stdid)
   values ('GO', 'csanchez');

insert into attendances
   (attnd_wksp_id, attnd_stdid)
   values ('GO', 'doliver');

insert into attendances
   (attnd_wksp_id, attnd_stdid)
   values ('GO', 'kalt');

insert into attendances
   (attnd_wksp_id, attnd_stdid)
   values ('GO', 'lkomp');

insert into attendances
   (attnd_wksp_id, attnd_stdid)
   values ('GO', 'sdavis');

insert into attendances
   (attnd_wksp_id, attnd_stdid)
   values ('GO', 'wcronan');

insert into attendances
   (attnd_wksp_id, attnd_stdid)
   values ('GO', 'tdouglas');

insert into attendances
   (attnd_wksp_id, attnd_stdid)
   values ('GO', 'troberts');

insert into attendances
   (attnd_wksp_id, attnd_stdid)
   values ('GO', 'aagassi');

insert into attendances
   (attnd_wksp_id, attnd_stdid)
   values ('GO', 'rfederer');