Extra Practice for CS301 Exam 1

CS_Schema.sql

CREATE TABLE Professors(
   Id         INTEGER  NOT NULL PRIMARY KEY 
  ,Last       VARCHAR(20) NOT NULL
  ,First      VARCHAR(20) NOT NULL
  ,Department VARCHAR(32) NOT NULL
  ,Rating     NUMERIC(3,1)
  ,Reviews    INTEGER  NOT NULL
);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (93,'Anderson','Monica','Computer Science',2.2,28);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (129,'Atkison','Travis','Computer Science',4,1);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (202,'Bartel','Rebecca','Computer Science',4.3,18);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (213,'Battles','Tommy','Computer Science',4.5,8);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (267,'Biehn','Maegan','Computer Science',2.5,33);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (332,'Borie','Richard','Computer Science',2.4,79);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (406,'Brown','Marcus','Computer Science',3,39);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (535,'Carver','Jeffrey','Computer Science',3.3,2);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (713,'Cordes','David','Computer Science',4,44);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (742,'Crawford','Chris','Computer Science',5,2);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (859,'Dewitt','Regina','Computer Science',3.6,7);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (883,'Dixon','Leslie','Computer Science',4,94);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (884,'Dixon','Brandon','Computer Science',2.7,32);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (949,'Eddy','Brian','Computer Science',4.8,7);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (1005,'Evans-Freeman','Hazel','Computer Science',3.9,12);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (1006,'Evans-Young','Darren','Computer Science',4.7,32);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (1012,'Ewing','Ashley','Computer Science',4,1);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (1118,'Freeman','Hazel','Computer Science',2.9,49);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (1122,'Freeman','Brandi','Computer Science',4.6,26);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (1145,'Galloway','Michael','Computer Science',1.8,4);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (1158,'Gehrke','Brandi','Computer Science',4,1);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (1247,'Gray','Jeff','Computer Science',4.1,6);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (1320,'Hale','Michelle','Computer Science',5,3);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (1494,'Hinton','Tracy','Computer Science',5,1);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (1524,'Hong','Xiaoyan','Computer Science',2,13);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (1530,'Hooper','Ralph','Computer Science',3.3,79);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (1546,'Horton','Chris','Computer Science',4.6,48);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (1659,'Jiang','Zhe','Computer Science',4.5,2);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (1848,'Kraft','Nicholas','Computer Science',3.1,7);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (1958,'Lewis','Timothy','Computer Science',5,6);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (2013,'Loewen','Gabriel','Computer Science',1.8,6);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (2046,'Lusth','John','Computer Science',4,24);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (2128,'Mason','Adria','Computer Science',2,5);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (2139,'May','Matthew','Computer Science',4.2,8);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (2140,'May','Maclane','Computer Science',3.7,13);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (2152,'McCary','Eric','Computer Science',4.6,6);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (2368,'Morris','Kathy','Computer Science',4.7,138);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (2369,'Morris','Kathleen','Computer Science',4.3,45);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (2398,'Musaev','Aibek','Computer Science',5,1);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (2498,'Odom-Bartel','Rebecca','Computer Science',4.2,17);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (2499,'Odor-Bartel','Becky','Computer Science',3.7,7);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (2699,'Prater','Jim','Computer Science',3.5,49);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (2707,'Price','Barrie Jo','Computer Science',3.6,9);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (2735,'Raines','Michael','Computer Science',4,1);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (2747,'Rao','Madhav','Computer Science',4,2);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (3051,'Shipwith','Marc','Computer Science',5,4);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (3089,'Skipwith','Marc','Computer Science',4.5,83);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (3100,'Smith','Karl','Computer Science',4.5,1);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (3117,'Smith','Randy','Computer Science',2.8,10);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (3274,'Syriani','Eugene','Computer Science',1,1);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (3287,'Tao','Dingwen','Computer Science',4.5,2);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (3470,'Vrbsky','Susan','Computer Science',3.7,19);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (3507,'Wang','Qin','Computer Science',3.2,5);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (3533,'Watson','Jennifer','Computer Science',4.4,19);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (3631,'Williams','Elizabeth','Computer Science',4,7);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (3713,'Wright','Kim','Computer Science',4.7,98);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (3714,'Wright','Kimberly','Computer Science',4.5,16);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (3726,'Xiao','Yang','Computer Science',3.6,5);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (3759,'Yue','Frank','Computer Science',5,1);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (3783,'Zhang','Jingyuan','Computer Science',2.8,46);
INSERT INTO Professors(Id,Last,First,Department,Rating,Reviews) VALUES (3797,'Zunnurhain','Kazi','Computer Science',4.6,4);

problems.sql

-- 0. Run CS-Schema.sql in sqldeveloper

-- 1. Create a table named 'Dept' with a single column header named "DName".
-- 'DName' is a VARCHAR(32), can not be NULL, and is a Primary Key.

-- 2. Add the following two rows to the "Dept" table:
-- 'Computer Science' and 'Mathmatics'

-- 3. Using a join condition, select the last names of the professors in the
-- Computer Science department.

-- 4. Using a join condition, select the last names of the professors in the
-- Mathmatics department.

-- 5. List all of the attributes of professors with a rating of exactly 3.7

-- 6. Select all attributes of all professors but sort them by rating then by reviews.

-- 7. Dr. Borie (Id: 332) has retired, remove him from the database.

-- 8. Your GPA has dropped significantly and you are now extremely 
-- paranoid when it comes to choosing professors. 
-- Create a view named 'HighRatedProfs' that shows all attributes of professors
-- with >= 4.0 Ratings and >= 10 reviews. Then display this view.

-- 9. RateMyProfessor.com does not want you to use their data and has
-- sent you a cease and desist letter, drop both tables and the view we created.





/*
-- Answers
-- 1.
CREATE TABLE Dept(
DName VARCHAR(32) NOT NULL PRIMARY KEY
);

-- 2.
INSERT INTO Dept VALUES ('Computer Science');
INSERT INTO Dept VALUES ('Mathmatics');

-- 3. 
SELECT Last
FROM Professors, Dept
WHERE Department = DName and DName = 'Computer Science';

-- 4.
SELECT Last
FROM Professors join Dept on Department = DName
WHERE DName = 'Mathmatics';

-- 5.
SELECT *
FROM Professors
WHERE Rating = 3.7;

-- 6.
SELECT *
FROM Professors
ORDER BY Rating, Reviews;

-- 7.
DELETE FROM Professors
WHERE Id = 332;

-- 8.
CREATE VIEW HighRatedProfs
AS SELECT *
FROM Professors
WHERE Rating >= 4.0 and Reviews >= 10;

SELECT * FROM HighRatedProfs;

-- 9.
DROP TABLE Dept;
DROP TABLE Professors;
DROP VIEW HighRatedProfs;
*/