
CREATE TABLE "Allergy_Type" (
	"Allergy" VARCHAR(20), 
	"AllergyType" VARCHAR(20), 
	PRIMARY KEY ("Allergy")
)


CREATE TABLE "Has_Allergy" (
	"StuID" INTEGER, 
	"Allergy" VARCHAR(20), 
	FOREIGN KEY("StuID") REFERENCES "Student" ("StuID"), 
	FOREIGN KEY("Allergy") REFERENCES "Allergy_Type" ("Allergy")
)


CREATE TABLE "Student" (
	"StuID" INTEGER, 
	"LName" VARCHAR(12), 
	"Fname" VARCHAR(12), 
	"Age" INTEGER, 
	"Sex" VARCHAR(1), 
	"Major" INTEGER, 
	"Advisor" INTEGER, 
	city_code VARCHAR(3), 
	PRIMARY KEY ("StuID")
)

CREATE TABLE "Allergy_Type" (
	"Allergy" VARCHAR(20), 
	"AllergyType" VARCHAR(20), 
	PRIMARY KEY ("Allergy")
)

/*
2 rows from Allergy_Type table:
Allergy	AllergyType
Eggs	food
Nuts	food
*/


CREATE TABLE "Has_Allergy" (
	"StuID" INTEGER, 
	"Allergy" VARCHAR(20), 
	FOREIGN KEY("StuID") REFERENCES "Student" ("StuID"), 
	FOREIGN KEY("Allergy") REFERENCES "Allergy_Type" ("Allergy")
)

/*
2 rows from Has_Allergy table:
StuID	Allergy
1001	Cat
1002	Shellfish
*/


CREATE TABLE "Student" (
	"StuID" INTEGER, 
	"LName" VARCHAR(12), 
	"Fname" VARCHAR(12), 
	"Age" INTEGER, 
	"Sex" VARCHAR(1), 
	"Major" INTEGER, 
	"Advisor" INTEGER, 
	city_code VARCHAR(3), 
	PRIMARY KEY ("StuID")
)

/*
2 rows from Student table:
StuID	LName	Fname	Age	Sex	Major	Advisor	city_code
1001	Smith	Linda	18	F	600	1121	BAL
1002	Kim	Tracy	19	F	600	7712	HKG
*/
What are the first name and major of the students who are able to consume soy?
SELECT fname ,  major FROM Student WHERE StuID NOT IN (SELECT StuID FROM Has_allergy WHERE Allergy  =  "Soy")
[{'Fname': 'Linda', 'Major': 600}, {'Fname': 'Tracy', 'Major': 600}, {'Fname': 'Shiela', 'Major': 600}, {'Fname': 'Dinesh', 'Major': 600}, {'Fname': 'Paul', 'Major': 600}, {'Fname': 'Andy', 'Major': 600}, {'Fname': 'Lisa', 'Major': 600}, {'Fname': 'Jandy', 'Major': 600}, {'Fname': 'Eric', 'Major': 600}, {'Fname': 'Derek', 'Major': 600}, {'Fname': 'David', 'Major': 600}, {'Fname': 'Steven', 'Major': 600}, {'Fname': 'Charles', 'Major': 600}, {'Fname': 'Mark', 'Major': 600}, {'Fname': 'Bruce', 'Major': 600}, {'Fname': 'Arthur', 'Major': 600}, {'Fname': 'Ian', 'Major': 520}, {'Fname': 'George', 'Major': 520}, {'Fname': 'Michael', 'Major': 540}, {'Fname': 'David', 'Major': 520}, {'Fname': 'Stacy', 'Major': 540}, {'Fname': 'Mark', 'Major': 520}, {'Fname': 'Eric', 'Major': 520}, {'Fname': 'Paul', 'Major': 520}, {'Fname': 'Eric', 'Major': 550}, {'Fname': 'Lisa', 'Major': 550}, {'Fname': 'Eric', 'Major': 550}, {'Fname': 'William', 'Major': 550}, {'Fname': 'Eric', 'Major': 50}, {'Fname': 'Sarah', 'Major': 50}]