Nimbus SQL Scripts

create_Professor_Teaches_Section_view.sql

DROP VIEW IF EXISTS Professor_Teaches_Section;
CREATE VIEW Professor_Teaches_Section AS SELECT * FROM (
SELECT first_name, last_name, phone_number, research_interests, email, prof_alias_id, prof_email_alias, id_sections, section_name, instructor, alias, title, phone, office, `type`, days, `start`, `end`, location, department 
FROM (
	SELECT * FROM Professors 
	JOIN (SELECT id AS prof_alias_id, substring_index(email, "@", 1) AS prof_email_alias FROM Professors) AS ProfAlias
	ON Professors.id = ProfAlias.prof_alias_id
) AS ProfWithAlias
JOIN Sections
ON Sections.alias = ProfWithAlias.prof_email_alias
) AS Professor_Teaches_Section

create_Profs_view.sql

DROP VIEW IF EXISTS Profs;
CREATE VIEW Profs
AS SELECT DISTINCT a.first_name, a.last_name, a.phone_number, a.email, c.title, c.department, b.office, b.office_hours, b.platform, b.latest_quarter, b.name, a.research_interests
FROM Professors a, OfficeHours b, Sections c
WHERE (substring_index(a.email, "@", 1) = c.alias)
AND (substring_index(b.email, "@", 1) = c.alias);