Question

SQL Data: you can just copy paste it into mysql to.

CREATE DATABASE University;USE University;
CREATE TABLE Student (  sid INT PRIMARY KEY,  name VARCHAR(20) NOT NULL,  address VARCHAR(20) NOT NULL,  major CHAR(2));
CREATE TABLE Professor (  pid INT PRIMARY KEY,  name VARCHAR(20) NOT NULL,  department VARCHAR(10) NOT NULL);
CREATE TABLE Course (  cid INT PRIMARY KEY,  title VARCHAR(20) NOT NULL UNIQUE,  credits INT NOT NULL,  area VARCHAR(5) NOT NULL);
CREATE TABLE Transcript (  sid INT,  cid INT,  pid INT,  semester VARCHAR(9),  year YEAR,  grade CHAR(1) NOT NULL,  PRIMARY KEY (sid, cid, semester, year),  FOREIGN KEY (sid) REFERENCES Student (sid),  FOREIGN KEY (cid) REFERENCES Course (cid),  FOREIGN KEY (pid) REFERENCES Professor (pid));

INSERT INTO Student (sid, name, address, major) VALUES (101, 'Nathan','Edinburg', 'CS');INSERT INTO Student (sid, name, address, major) VALUES (105, 'Hussein','Edinburg', 'IT');INSERT INTO Student (sid, name, address, major) VALUES (103, 'Jose', 'McAllen','CE');INSERT INTO Student (sid, name, address, major) VALUES (102, 'Wendy', 'Mission','CS');INSERT INTO Student (sid, name, address, major) VALUES (104, 'Maria', 'Pharr','CS');INSERT INTO Student (sid, name, address, major) VALUES (106, 'Mike', 'Edinburg','CE');INSERT INTO Student (sid, name, address, major) VALUES (107, 'Lily', 'McAllen',NULL);
INSERT INTO Professor (pid, name, department) VALUES (201, 'Artem', 'CS');INSERT INTO Professor (pid, name, department) VALUES (203, 'John', 'CS');INSERT INTO Professor (pid, name, department) VALUES (202, 'Virgil', 'MATH');INSERT INTO Professor (pid, name, department) VALUES (204, 'Pearl', 'CS');INSERT INTO Professor (pid, name, department) VALUES (205, 'Christine', 'CS');
INSERT INTO Course (cid, title, credits, area) VALUES (4333, 'Database', 3,'DB');INSERT INTO Course (cid, title, credits, area) VALUES (1201, 'Comp literacy', 2,'INTRO');

INSERT INTO Course (cid, title, credits, area) VALUES (6333, 'AdvancedDatabase', 3, 'DB');INSERT INTO Course (cid, title, credits, area) VALUES (6315, 'Applied Database',3, 'DB');INSERT INTO Course (cid, title, credits, area) VALUES (3326, 'Java', 3, 'PL');INSERT INTO Course (cid, title, credits, area) VALUES (1370, 'CS I', 4,'INTRO');
INSERT INTO Transcript (sid, cid, pid, semester, year, grade) VALUES (101, 4333,201, 'Spring', '2009', 'A');INSERT INTO Transcript (sid, cid, pid, semester, year, grade) VALUES (101, 6333,201, 'Fall', '2009', 'A');INSERT INTO Transcript (sid, cid, pid, semester, year, grade) VALUES (101, 6315,201, 'Fall', '2009', 'A');INSERT INTO Transcript (sid, cid, pid, semester, year, grade) VALUES (103, 4333,203, 'Summer I', '2010', 'B');INSERT INTO Transcript (sid, cid, pid, semester, year, grade) VALUES (102, 4333,201, 'Fall', '2009', 'A');INSERT INTO Transcript (sid, cid, pid, semester, year, grade) VALUES (103, 3326,204, 'Spring', '2008', 'A');INSERT INTO Transcript (sid, cid, pid, semester, year, grade) VALUES (104, 1201,205, 'Fall', '2009', 'B');INSERT INTO Transcript (sid, cid, pid, semester, year, grade) VALUES (104, 1370,203, 'Summer II', '2010', 'A');INSERT INTO Transcript (sid, cid, pid, semester, year, grade) VALUES (106, 1201,205, 'Fall', '2009', 'C');INSERT INTO Transcript (sid, cid, pid, semester, year, grade) VALUES (106, 1370,203, 'Summer II', '2010', 'C');INSERT INTO Transcript (sid, cid, pid, semester, year, grade) VALUES (105, 3326,204, 'Spring', '2001', 'A');INSERT INTO Transcript (sid, cid, pid, semester, year, grade) VALUES (105, 6315,203, 'Fall', '2008', 'A');

Question:

Find names of professors who taught both INTRO and DB courses.

Find professors who taught at least two (>=2) different courses.

Find the total number of courses offered in Fall 2009.

Find the total number of courses offered in each semester-year; sort the result in
descending-year and ascending-semester order.

Find the total number of credits completed by student with id 101.

Find the total number of credits completed per student (output student names);
sort the result in descending-credits order.

Answer 1

1) Find names of professors who taught both INTRO and DB courses.

Select name from Professor where department=” INTRO” and department=” DB”;

2) Find professors who taught at least two (>=2) different courses.

Select name from Professor where department=” INTRO” or department=” DB” or department=” Pearl”;

The abovequery gives the name of the professors teaching subjects INTRO or DB or Pearl. i.e. >=2 different courses.

3) Find the total number of courses offered in Fall 2009.

Select count(cid) from Transcript where semester=”Fall” and year=2009;

5) Find the total number of credits completed by student with id 101.

Select sid,cid from Transcript, Course where Transcript.cid= Course.cid AND sid=101;

Thank you.