Database schema:

CREATE TABLE attendance (
	link_to_event TEXT -- link to event, example: ['rec5XDvJLyxDsGZWc', 'rec2N69DMcrqN9PJC'],
	link_to_member TEXT -- link to member, example: ['recP6DJPyi5donvXL', 'recD078PnS3x2doBe'],
	PRIMARY KEY (link_to_event, link_to_member), 
	CONSTRAINT fk_attendance_event FOREIGN KEY(link_to_event) REFERENCES event (event_id),
	CONSTRAINT fk_attendance_member FOREIGN KEY(link_to_member) REFERENCES member (member_id)
)


CREATE TABLE budget (
	budget_id TEXT -- budget id, example: ['rec1bG6HSft7XIvTP', 'rec0QmEc3cSQFQ6V2'],
	category TEXT -- example: ['Advertisement', 'Food'],
	spent REAL -- example: ['67.81', '121.14'],
	remaining REAL -- example: ['7.19', '28.86'],
	amount INTEGER -- example: ['75', '150'],
	event_status TEXT -- event status, example: ['Closed', 'Open'],
	link_to_event TEXT -- link to event, example: ['recI43CzsZ0Q625ma', 'recggMW2eyCYceNcy'],
	PRIMARY KEY (budget_id), 
	CONSTRAINT fk_budget_event FOREIGN KEY(link_to_event) REFERENCES event (event_id)
)


CREATE TABLE event (
	event_id TEXT -- event id, example: ['rec0akZnLLpGUloLH', 'rec0Si5cQ4rJRVzd6'],
	event_name TEXT -- event name, example: ['March Meeting', 'Officers meeting - January'],
	event_date TEXT -- event date, example: ['2020-01-14T09:30:00', '2020-03-10T12:00:00'],
	type TEXT -- example: ['Meeting', 'Election'],
	notes TEXT -- example: ['All active members can vote for new officers betwe', "Attend Women's soccer game as a group."],
	location TEXT -- example: ['MU 215', 'Campus Soccer/Lacrosse stadium'],
	status TEXT -- example: ['Open', 'Planning'],
	PRIMARY KEY (event_id)
)


CREATE TABLE expense (
	expense_id TEXT -- expense id, example: ['rec1nIjoZKTYayqZ6', 'rec017x6R3hQqkLAo'],
	expense_description TEXT -- expense description, example: ['Water, Cookies', 'Post Cards, Posters'],
	expense_date TEXT -- expense date, example: ['2019-08-20', '2019-10-08'],
	cost REAL -- example: ['20.2', '122.06'],
	approved TEXT -- example: ['true'],
	link_to_member TEXT -- link to member, example: ['rec4BLdZHS2Blfp4v', 'recro8T1MPMwRadVH'],
	link_to_budget TEXT -- link to budget, example: ['recvKTAWAFKkVNnXQ', 'recy8KY5bUdzF81vv'],
	PRIMARY KEY (expense_id), 
	CONSTRAINT fk_expense_budget FOREIGN KEY(link_to_budget) REFERENCES budget (budget_id),
	CONSTRAINT fk_expense_member FOREIGN KEY(link_to_member) REFERENCES member (member_id)
)


CREATE TABLE income (
	income_id TEXT -- income id, example: ['rec7f5XMQZexgtQJo', 'rec0s9ZrO15zhzUeE'],
	date_received TEXT -- date received, example: ['2019-09-04', '2019-10-17'],
	amount INTEGER -- example: ['50', '200'],
	source TEXT -- example: ['Dues', 'Fundraising'],
	notes TEXT -- example: ['Secured donations to help pay for speaker gifts.', 'Annual funding from Student Government.'],
	link_to_member TEXT -- link to member, example: ['recTjHY5xXhvkCdVT', 'reccW7q1KkhSKZsea'],
	PRIMARY KEY (income_id), 
	CONSTRAINT fk_income_member FOREIGN KEY(link_to_member) REFERENCES member (member_id)
)


CREATE TABLE major (
	major_id TEXT -- major id, example: ['rec09LedkREyskCNv', 'rec06DF6vZ1CyPKpc'],
	major_name TEXT -- major name, example: ['Outdoor Product Design and Development', 'Agricultural Communication'],
	department TEXT -- example: ['School of Applied Sciences, Technology and Educati', 'Watershed Sciences Department'],
	college TEXT -- example: ['College of Natural Resources', 'College of Agriculture and Applied Sciences'],
	PRIMARY KEY (major_id)
)


CREATE TABLE member (
	member_id TEXT -- member id, example: ['rec1x5zBFIqoOuPW8', 'rec280Sk7o31iG0Tx'],
	first_name TEXT -- first name, example: ['Grant', 'Angela'],
	last_name TEXT -- last name, example: ['Sanders', 'Gilmour'],
	email TEXT -- example: ['grant.gilmour@lpu.edu', 'angela.sanders@lpu.edu'],
	position TEXT -- example: ['Member', 'Inactive'],
	t_shirt_size TEXT -- example: ['X-Large', 'Medium'],
	phone TEXT -- example: ['403-555-1310', '(651) 928-4507'],
	zip INTEGER -- example: ['29440', '55108'],
	link_to_major TEXT -- link to major, example: ['rec7BxKpjJ7bNph3O', 'recxK3MHQFbR9J5uO'],
	PRIMARY KEY (member_id), 
	CONSTRAINT fk_member_major FOREIGN KEY(link_to_major) REFERENCES major (major_id),
	CONSTRAINT fk_member_zip_code FOREIGN KEY(zip) REFERENCES zip_code (zip_code)
)


CREATE TABLE zip_code (
	zip_code INTEGER -- zip code, example: ['544', '501'],
	type TEXT -- example: ['Unique', 'Standard'],
	city TEXT -- example: ['Holtsville', 'Adjuntas'],
	county TEXT -- example: ['Adjuntas Municipio', 'Suffolk County'],
	state TEXT -- example: ['Puerto Rico', 'New York'],
	short_state TEXT -- short state, example: ['NY', 'PR'],
	PRIMARY KEY (zip_code)
)

Question: List the last name of members with a major in environmental engineering and include its department and college name.
Evidence: 'Environmental Engineering' is the major_name;

SQL:
SELECT T2.last_name, T1.department, T1.college FROM major AS T1 INNER JOIN member AS T2 ON T1.major_id = T2.link_to_major WHERE T2.position = 'Member' AND T1.major_name = 'Environmental Engineering'