This is the SQL code to set up a database for a problem I had on a database management exam. Here's the problem: write an SQL query that will return a table of the names and salaries of the professors who teach 20 or more students.

prof.sql

CREATE DATABASE profs;
USE profs;

CREATE TABLE professors (
	name varchar(30) NOT NULL,
	specialization varchar(20),
	salary double(8,2),
	CONSTRAINT prof_pk PRIMARY KEY (name));

CREATE TABLE students (
	name varchar(30) NOT NULL,
        study_type varchar(20),
	CONSTRAINT stud_pk PRIMARY KEY (name));

CREATE TABLE courses (
	num int NOT NULL AUTO_INCREMENT,
	stud_name varchar(30),
	prof_name varchar(30),
        grade varchar(10),
	CONSTRAINT courses_pk PRIMARY KEY (num),
	CONSTRAINT stud_fk FOREIGN KEY (stud_name) REFERENCES students(name),
	CONSTRAINT prof_fk FOREIGN KEY (prof_name) REFERENCES professors(name));
	
INSERT INTO professors VALUES
	("Prof 1", "spec 1", 10000.00),
	("Prof 2", "spec 2", 10000.00),
	("Prof 3", "spec 3", 10000.00),
	("Prof 4", "spec 4", 10000.00),
	("Prof 5", "spec 5", 10000.00),
	("Prof 6", "spec 6", 10000.00),
	("Prof 7", "spec 7", 10000.00),
	("Prof 8", "spec 8", 10000.00),
	("Prof 9", "spec 9", 10000.00),
	("Prof 10", "spec 10", 10000.00);

INSERT INTO students(name) VALUES
	("Stud 1"),
	("Stud 2"),
	("Stud 3"),
	("Stud 4"),
	("Stud 5"),
	("Stud 6"),
	("Stud 7"),
	("Stud 8"),
	("Stud 9"),
	("Stud 10"),
	("Stud 11"),
	("Stud 12"),
	("Stud 13"),
	("Stud 14"),
	("Stud 15"),
	("Stud 16"),
	("Stud 17"),
	("Stud 18"),
	("Stud 19"),
	("Stud 20"),
	("Stud 21"),
	("Stud 22"),
	("Stud 23"),
	("Stud 24"),
	("Stud 25"),
	("Stud 26"),
	("Stud 27"),
	("Stud 28"),
	("Stud 29"),
	("Stud 30"),
	("Stud 31"),
	("Stud 32"),
	("Stud 33"),
	("Stud 34"),
	("Stud 35"),
	("Stud 36"),
	("Stud 37"),
	("Stud 38"),
	("Stud 39"),
	("Stud 40"),
	("Stud 41"),
	("Stud 42"),
	("Stud 43"),
	("Stud 44"),
	("Stud 45"),
	("Stud 46"),
	("Stud 47"),
	("Stud 48"),
	("Stud 49"),
	("Stud 50"),
	("Stud 51"),
	("Stud 52"),
	("Stud 53");

INSERT INTO courses(stud_name, prof_name) VALUES
	("Stud 1", "Prof 1"),
	("Stud 2", "Prof 1"),
	("Stud 3", "Prof 1"),
	("Stud 4", "Prof 1"),
	("Stud 5", "Prof 1"),
	("Stud 6", "Prof 1"),
	("Stud 7", "Prof 1"),
	("Stud 8", "Prof 1"),
	("Stud 9", "Prof 1"),
	("Stud 10", "Prof 1"),
	("Stud 11", "Prof 1"),
	("Stud 12", "Prof 1"),
	("Stud 13", "Prof 1"),
	("Stud 14", "Prof 1"),
	("Stud 15", "Prof 1"),
	("Stud 16", "Prof 1"),
	("Stud 17", "Prof 1"),
	("Stud 18", "Prof 1"),
	("Stud 19", "Prof 1"),
	("Stud 20", "Prof 1"),
	("Stud 21", "Prof 1"),
	("Stud 22", "Prof 1"),
	("Stud 23", "Prof 1"),
	("Stud 24", "Prof 2"),
	("Stud 25", "Prof 2"),
	("Stud 26", "Prof 2"),
	("Stud 27", "Prof 2"),
	("Stud 28", "Prof 2"),
	("Stud 29", "Prof 2"),
	("Stud 30", "Prof 2"),
	("Stud 31", "Prof 2"),
	("Stud 32", "Prof 2"),
	("Stud 33", "Prof 2"),
	("Stud 34", "Prof 2"),
	("Stud 35", "Prof 2"),
	("Stud 36", "Prof 2"),
	("Stud 37", "Prof 2"),
	("Stud 38", "Prof 2"),
	("Stud 39", "Prof 2"),
	("Stud 40", "Prof 2"),
	("Stud 41", "Prof 2"),
	("Stud 42", "Prof 2"),
	("Stud 43", "Prof 2"),
	("Stud 44", "Prof 2"),
	("Stud 45", "Prof 2"),
	("Stud 46", "Prof 3"),
	("Stud 46", "Prof 4"),
	("Stud 47", "Prof 5"),
	("Stud 48", "Prof 6"),
	("Stud 49", "Prof 7"),
	("Stud 50", "Prof 8"),
	("Stud 51", "Prof 9"),
	("Stud 52", "Prof 10"),
	("Stud 53", "Prof 10");

SELECT name, salary FROM professors
WHERE name IN (SELECT prof_name FROM courses GROUP BY prof_name HAVING COUNT(prof_name) >20);

SELECT
professors.name,
SUM(profs.professors.salary) AS salary #, count(profs.students.name) as "student count"
FROM
professors,
students
CROSS JOIN courses
WHERE
professors.name = prof_name
AND students.name = stud_name
GROUP BY
professors.name #having salary >= 20000 #// avg salary( 10000 ) * 20
HAVING
count(profs.students.name) >= 20

SELECT professors.name, professors.salary FROM professors
INNER JOIN courses ON courses.prof_name = professors.name
GROUP BY professors.name
HAVING COUNT(courses.prof_name) >= 20