Run this if the pre-req script does not work

Use the force

-- Create Table Address
CREATE TABLE Address  
    ("StudentID" varchar2(6) PRIMARY KEY, "Number" varchar2(4), "Street" varchar2(13), "City" varchar2(15), "PostalCode" int) ;

-- Insert values to Address
-- Hypothetical addresses
INSERT ALL    
    INTO Address ("StudentID", "Number", "Street", "City", "PostalCode")   
         VALUES ('HOG119', '23', 'Apple St', 'Manchester', 123456789)   
    INTO Address ("StudentID", "Number", "Street", "City", "PostalCode")   
         VALUES ('HOG136', '456', 'Orange St', 'Sunderland', 234557329)   
    INTO Address ("StudentID", "Number", "Street", "City", "PostalCode")   
         VALUES ('HOG139', '78', 'Grape Rd', 'London', 234252342)   
    INTO Address ("StudentID", "Number", "Street", "City", "PostalCode")   
         VALUES ('HOG387', '234', 'Mango Blvd', 'Norwich', 234252525)   
    INTO Address ("StudentID", "Number", "Street", "City", "PostalCode")   
         VALUES ('HOG437', '45', 'Cherry Rd', 'Anfield', 235254534)   
    INTO Address ("StudentID", "Number", "Street", "City", "PostalCode")   
         VALUES ('HOG697', '985A', 'Banana Ave', 'London', 255423645)   
    INTO Address ("StudentID", "Number", "Street", "City", "PostalCode")   
         VALUES ('HOG988', '35', 'Pear Ln', 'London', 346347564)   
    INTO Address ("StudentID", "Number", "Street", "City", "PostalCode")   
         VALUES ('HOG563', '235', 'Pineapple Rd', 'Portsmouth', 736495730)   
    INTO Address ("StudentID", "Number", "Street", "City", "PostalCode")   
         VALUES ('HOG239', '642', 'Strawberry Ln', 'London', 726354859)   
    INTO Address ("StudentID", "Number", "Street", "City", "PostalCode")   
         VALUES ('HOG720', '24', 'Blueberry Ave', 'Burnley', 825485029)   
    INTO Address ("StudentID", "Number", "Street", "City", "PostalCode")   
         VALUES ('HOG902', '894', 'Blackberry Rd', 'Brighton', 245826940)   
SELECT * FROM dual ;



-- Create Class table
CREATE TABLE Class   
    ("ClassID" varchar2(5) PRIMARY KEY, "ClassTitle" varchar2(29), "Term" varchar2(6), "Seats" int);

-- Insert values to Class table
-- Some classes have more vacancies than others
INSERT ALL     
    INTO Class ("ClassID", "ClassTitle", "Term", "Seats")    
         VALUES ('C1111', 'Potions', 'Fall', 30)    
    INTO Class ("ClassID", "ClassTitle", "Term", "Seats")    
         VALUES ('C1010', 'Flying', 'Summer', 30)    
    INTO Class ("ClassID", "ClassTitle", "Term", "Seats")    
         VALUES ('C2222', 'Defence against the dark arts', 'Fall', 25)    
    INTO Class ("ClassID", "ClassTitle", "Term", "Seats")    
         VALUES ('C3333', 'Divination', 'Fall', 30)    
    INTO Class ("ClassID", "ClassTitle", "Term", "Seats")    
         VALUES ('C4444', 'Quiddich', 'Spring', 25)    
    INTO Class ("ClassID", "ClassTitle", "Term", "Seats")    
         VALUES ('C5555', 'Care for Magical Creatures', 'Spring', 35)    
    INTO Class ("ClassID", "ClassTitle", "Term", "Seats")    
         VALUES ('C6666', 'Herbology', 'Spring', 15)    
    INTO Class ("ClassID", "ClassTitle", "Term", "Seats")    
         VALUES ('C7777', 'Charms', 'Summer', 10)    
    INTO Class ("ClassID", "ClassTitle", "Term", "Seats")    
         VALUES ('C8888', 'Apparition', 'Summer', 10)    
    INTO Class ("ClassID", "ClassTitle", "Term", "Seats")    
         VALUES ('C9999', 'Muggle Studies', 'Summer', 15)    
    INTO Class ("ClassID", "ClassTitle", "Term", "Seats")    
         VALUES ('C0000', 'The Force', 'Winter', 0)   
SELECT * FROM DUAL;


-- Create Date of Birth table
CREATE TABLE DateOfBirth   
    ("InstructorID" varchar2(6) PRIMARY KEY, "DoB" date); 

-- Insert values to Date of Birth table
-- Probably accurate dates of birth.
-- Please excuse any inaccuracies.
INSERT ALL      
    INTO DateOfBirth ("InstructorID", "DoB")     
         VALUES ('GA2345', TO_DATE('29/04/1931', 'DD/MM/YYYY'))     
    INTO DateOfBirth ("InstructorID", "DoB")     
         VALUES ('SA3456', TO_DATE('30/03/1935', 'DD/MM/YYYY'))     
    INTO DateOfBirth ("InstructorID", "DoB")     
         VALUES ('GA4567', TO_DATE('19/11/1953', 'DD/MM/YYYY'))     
    INTO DateOfBirth ("InstructorID", "DoB")     
         VALUES ('XA5678', TO_DATE('24/01/1965', 'DD/MM/YYYY'))     
    INTO DateOfBirth ("InstructorID", "DoB")     
         VALUES ('RB1234', TO_DATE('10/08/1932', 'DD/MM/YYYY'))     
    INTO DateOfBirth ("InstructorID", "DoB")     
         VALUES ('GB2345', TO_DATE('23/06/1960', 'DD/MM/YYYY'))     
    INTO DateOfBirth ("InstructorID", "DoB")     
         VALUES ('RB3456', TO_DATE('10/02/1940', 'DD/MM/YYYY'))     
    INTO DateOfBirth ("InstructorID", "DoB")     
         VALUES ('RB4567', TO_DATE('09/07/1969', 'DD/MM/YYYY'))     
    INTO DateOfBirth ("InstructorID", "DoB")     
         VALUES ('SB5678', TO_DATE('02/12/1969', 'DD/MM/YYYY'))     
    INTO DateOfBirth ("InstructorID", "DoB")     
         VALUES ('GC1234', TO_DATE('07/10/1962', 'DD/MM/YYYY'))     
    INTO DateOfBirth ("InstructorID", "DoB")     
         VALUES ('SC2345', TO_DATE('17/09/1962', 'DD/MM/YYYY'))     
    INTO DateOfBirth ("InstructorID", "DoB")     
         VALUES ('SA1234', TO_DATE('14/05/1958', 'DD/MM/YYYY'))     
    INTO DateOfBirth ("InstructorID", "DoB")     
         VALUES ('HA6789', TO_DATE('19/07/1952', 'DD/MM/YYYY'))   
    INTO DateOfBirth ("InstructorID", "DoB")     
         VALUES ('MY6666', TO_DATE('06/06/0896', 'DD/MM/YYYY'))     
SELECT * FROM dual;



-- Create Email table
CREATE TABLE Email 
    ("StudentID" varchar2(6) PRIMARY KEY,  "EmailID" varchar2(50));

-- Add values to Email table
INSERT ALL    
    INTO Email ("StudentID", "EmailID")   
         VALUES ('HOG119', 'Malfoy-Drago@hogwarts.edu')   
    INTO Email ("StudentID", "EmailID")   
         VALUES ('HOG136', 'Lovegood-Luna@hogwarts.edu')   
    INTO Email ("StudentID", "EmailID")   
         VALUES ('HOG139', 'Granger-Hermione@hogwarts.edu')   
    INTO Email ("StudentID", "EmailID")   
         VALUES ('HOG387', 'Longbottom-Neville@hogwarts.edu')   
    INTO Email ("StudentID", "EmailID")   
         VALUES ('HOG437', 'Potter-Harry@hogwarts.edu')   
    INTO Email ("StudentID", "EmailID")   
         VALUES ('HOG697', 'Weasley-Ron@hogwarts.edu')   
    INTO Email ("StudentID", "EmailID")   
         VALUES ('HOG988', 'Weasley-Ginny@hogwarts.edu')   
    INTO Email ("StudentID", "EmailID")   
         VALUES ('HOG563', 'Lavender-Brown@hogwarts.edu')   
    INTO Email ("StudentID", "EmailID")   
         VALUES ('HOG239', 'Seamus-Finnigan@hogwarts.edu')   
    INTO Email ("StudentID", "EmailID")   
         VALUES ('HOG720', 'Cho-Chang@hogwarts.edu')   
    INTO Email ("StudentID", "EmailID")   
         VALUES ('HOG902', 'Cedric-Diggory@hogwarts.edu')   
SELECT * FROM dual;



-- Create House table
-- There are 4 houses
CREATE TABLE HogwartsHouse 
    ("HouseID" varchar2(2) PRIMARY KEY, "House" varchar2(10), "Founder" varchar2(17), "Relic" varchar2(20)) ;

-- Add values to HogwartsHouse table

INSERT ALL  
    INTO HogwartsHouse ("HouseID", "House", "Founder", "Relic") 
         VALUES ('H1', 'Gryffindor', 'Godric Gryffindor', 'Sword') 
    INTO HogwartsHouse ("HouseID", "House", "Founder", "Relic") 
         VALUES ('H2', 'Hufflepuff', 'Helga Hufflepuff', 'Cup') 
    INTO HogwartsHouse ("HouseID", "House", "Founder", "Relic") 
         VALUES ('H3', 'Ravenclaw', 'Rowena Ravenclaw', 'Diadem') 
    INTO HogwartsHouse ("HouseID", "House", "Founder", "Relic") 
         VALUES ('H4', 'Slytherin', 'Salazar Slytherin', 'Locket')
    INTO HogwartsHouse ("HouseID", "House", "Founder", "Relic") 
         VALUES ('H5', 'Jedi', 'Master Yeoda', 'Light Sabre') 
SELECT * FROM dual ;



-- Create Instructor table
-- These are professors at Hogwarts
CREATE TABLE Instructor 
    ("InstructorID" varchar2(6) PRIMARY KEY, "InstructorFName" varchar2(8), "InstructorLName" varchar2(10), "HouseID" varchar2(2)) ;

-- Insert values to Instructor table
INSERT ALL   
    INTO Instructor ("InstructorID", "InstructorFName", "InstructorLName", "HouseID")  
         VALUES ('GA2345', 'Albus', 'Dumbledore', 'H1')  
    INTO Instructor ("InstructorID", "InstructorFName", "InstructorLName", "HouseID")  
         VALUES ('SA3456', 'Dolores', 'Umbridge', 'H3')  
    INTO Instructor ("InstructorID", "InstructorFName", "InstructorLName", "HouseID")  
         VALUES ('GA4567', 'Serius', 'Black', 'H1')  
    INTO Instructor ("InstructorID", "InstructorFName", "InstructorLName", "HouseID")  
         VALUES ('XA5678', 'Firenze', 'Centaur', NULL)  
    INTO Instructor ("InstructorID", "InstructorFName", "InstructorLName", "HouseID")  
         VALUES ('RB1234', 'Filius', 'Flitwick', 'H3')  
    INTO Instructor ("InstructorID", "InstructorFName", "InstructorLName", "HouseID")  
         VALUES ('GB2345', 'Rubeus', 'Hagrid', NULL)  
    INTO Instructor ("InstructorID", "InstructorFName", "InstructorLName", "HouseID")  
         VALUES ('RB3456', 'Gilderoy', 'Lockhart', 'H3')  
    INTO Instructor ("InstructorID", "InstructorFName", "InstructorLName", "HouseID")  
         VALUES ('RB4567', 'Sybill', 'Trelawney', 'H3')  
    INTO Instructor ("InstructorID", "InstructorFName", "InstructorLName", "HouseID")  
         VALUES ('SB5678', 'Alecto', 'Carrow', 'H4')  
    INTO Instructor ("InstructorID", "InstructorFName", "InstructorLName", "HouseID")  
         VALUES ('GC1234', 'Remus', 'Lupin', 'H1')  
    INTO Instructor ("InstructorID", "InstructorFName", "InstructorLName", "HouseID")  
         VALUES ('SC2345', 'Horace', 'Slughorn', 'H4')  
    INTO Instructor ("InstructorID", "InstructorFName", "InstructorLName", "HouseID")  
         VALUES ('SA1234', 'Severus', 'Snape', 'H4')  
    INTO Instructor ("InstructorID", "InstructorFName", "InstructorLName", "HouseID")  
         VALUES ('HA6789', 'Poppy', 'Pomfrey', 'H2')  
    INTO Instructor ("InstructorID", "InstructorFName", "InstructorLName", "HouseID")  
         VALUES ('MY6666', 'Master', 'Yeoda', 'H5') 
SELECT * FROM dual ;



-- Create InstructorClass table
-- Each row is an instructor-class
CREATE TABLE InstructorClass   
    ("InstructorClass" varchar2(12) PRIMARY KEY, "InstructorID" varchar2(6), "ClassID" varchar2(5)) ;

-- Insert values to InstructorClass table
-- Each row is an instructor-class
INSERT ALL     
    INTO InstructorClass ("InstructorClass", "InstructorID", "ClassID")    
         VALUES ('SA1234-C1111', 'SA1234', 'C1111')    
    INTO InstructorClass ("InstructorClass", "InstructorID", "ClassID")    
         VALUES ('SB5678-C1010', 'SB5678', 'C1010')    
    INTO InstructorClass ("InstructorClass", "InstructorID", "ClassID")    
         VALUES ('GA2345-C2222', 'GA2345', 'C2222')    
    INTO InstructorClass ("InstructorClass", "InstructorID", "ClassID")    
         VALUES ('SA1234-C4444', 'SA1234', 'C4444')    
    INTO InstructorClass ("InstructorClass", "InstructorID", "ClassID")    
         VALUES ('XA5678-C5555', 'XA5678', 'C5555')    
    INTO InstructorClass ("InstructorClass", "InstructorID", "ClassID")    
         VALUES ('RB1234-C6666', 'RB1234', 'C6666')    
    INTO InstructorClass ("InstructorClass", "InstructorID", "ClassID")    
         VALUES ('RB3456-C8888', 'RB3456', 'C8888')    
    INTO InstructorClass ("InstructorClass", "InstructorID", "ClassID")    
         VALUES ('MY6666-C0000', 'MY6666', 'C0000')    
SELECT * FROM dual;


-- Create MealPlan table
-- Students purchase meal plans
-- Some students don't purchase meal plans
-- Some students purchase more than 1 meal plan
CREATE TABLE MealPlan  
    ("StudentID" varchar2(6), "MealPlanIDNo" varchar2(6) PRIMARY KEY) ;

-- Insert values to MealPlan table
-- Students purchase meal plans
-- Some students don't purchase meal plans
-- Some students purchase more than 1 meal plan
INSERT ALL    
    INTO MealPlan ("StudentID", "MealPlanIDNo")   
         VALUES ('HOG119', 'ABC123')   
    INTO MealPlan ("StudentID", "MealPlanIDNo")   
         VALUES ('HOG136', 'DEF456')   
    INTO MealPlan ("StudentID", "MealPlanIDNo")   
         VALUES ('HOG139', 'GHI789')   
    INTO MealPlan ("StudentID", "MealPlanIDNo")   
         VALUES ('HOG387', 'JKL123')   
    INTO MealPlan ("StudentID", "MealPlanIDNo")   
         VALUES ('HOG437', 'MNO456')   
    INTO MealPlan ("StudentID", "MealPlanIDNo")   
         VALUES ('HOG697', 'PQR789')   
    INTO MealPlan ("StudentID", "MealPlanIDNo")   
         VALUES ('HOG988', 'STU123')   
    INTO MealPlan ("StudentID", "MealPlanIDNo")   
         VALUES ('HOG119', 'VWX456')   
    INTO MealPlan ("StudentID", "MealPlanIDNo")   
         VALUES ('HOG136', 'YYZ789')   
    INTO MealPlan ("StudentID", "MealPlanIDNo")   
         VALUES ('HOG387', 'ABC789')   
    INTO MealPlan ("StudentID", "MealPlanIDNo")   
         VALUES ('HOG988', 'DEF000')   
SELECT * FROM dual ;


-- Create Salary table
-- Some instructors are paid more than others.
CREATE TABLE Salary 
    ("InstructorID" varchar2(6) PRIMARY KEY, "Pay" int, "Bonus" int) ;

-- Insert values to Salary table
-- Some instructors are paid more than others.
INSERT ALL    
    INTO Salary ("InstructorID", "Pay", "Bonus")   
         VALUES ('GA2345', 1200, 180)   
    INTO Salary ("InstructorID", "Pay", "Bonus")   
         VALUES ('SA3456', 1400, 100)   
    INTO Salary ("InstructorID", "Pay", "Bonus")   
         VALUES ('GA4567', 1600, 200)   
    INTO Salary ("InstructorID", "Pay", "Bonus")   
         VALUES ('XA5678', 1800, 500)   
    INTO Salary ("InstructorID", "Pay", "Bonus")   
         VALUES ('RB1234', 2000, 250)   
    INTO Salary ("InstructorID", "Pay", "Bonus")   
         VALUES ('GB2345', 2200, 300)   
    INTO Salary ("InstructorID", "Pay", "Bonus")   
         VALUES ('RB3456', 2400, 320)   
    INTO Salary ("InstructorID", "Pay", "Bonus")   
         VALUES ('RB4567', 2600, 900)   
    INTO Salary ("InstructorID", "Pay", "Bonus")   
         VALUES ('SB5678', 2800, 380)   
    INTO Salary ("InstructorID", "Pay", "Bonus")   
         VALUES ('GC1234', 3500, 700)   
    INTO Salary ("InstructorID", "Pay", "Bonus")   
         VALUES ('SC2345', 3600, 360)   
    INTO Salary ("InstructorID", "Pay", "Bonus")   
         VALUES ('SA1234', 1000, 150)   
    INTO Salary ("InstructorID", "Pay", "Bonus")   
         VALUES ('HA6789', 1200, 200) 
    INTO Salary ("InstructorID", "Pay", "Bonus")   
         VALUES ('MY6666', 888, 88)    
SELECT * FROM dual ;


-- Create Student table
-- A sample of students from different Years for illustration.
-- Obviously there are many more students at Hogwarts.
CREATE TABLE Student 
    ("StudentID" varchar2(6), "Lname" varchar2(10), "Fname" varchar2(8), "HouseID" varchar2(2)) ;

-- Insert values to Student table
-- A sample of students from different Years for illustration.
-- Obviously there are many more students at Hogwarts.
INSERT ALL   
    INTO Student ("StudentID", "Lname", "Fname", "HouseID")  
         VALUES ('HOG119', 'Malfoy', 'Draco', 'H4')  
    INTO Student ("StudentID", "Lname", "Fname", "HouseID")  
         VALUES ('HOG136', 'Lovegood', 'Luna', 'H3')  
    INTO Student ("StudentID", "Lname", "Fname", "HouseID")  
         VALUES ('HOG139', 'Granger', 'Hermione', 'H1')  
    INTO Student ("StudentID", "Lname", "Fname", "HouseID")  
         VALUES ('HOG387', 'Longbottom', 'Neville', 'H1')  
    INTO Student ("StudentID", "Lname", "Fname", "HouseID")  
         VALUES ('HOG437', 'Potter', 'Harry', 'H1')  
    INTO Student ("StudentID", "Lname", "Fname", "HouseID")  
         VALUES ('HOG697', 'Weasley', 'Ron', 'H1')  
    INTO Student ("StudentID", "Lname", "Fname", "HouseID")  
         VALUES ('HOG988', 'Weasley', 'Ginny', 'H1')  
    INTO Student ("StudentID", "Lname", "Fname", "HouseID")  
         VALUES ('HOG563', 'Brown', 'Lavender', 'H1')  
    INTO Student ("StudentID", "Lname", "Fname", "HouseID")  
         VALUES ('HOG239', 'Finnigan', 'Seamus', 'H1')  
    INTO Student ("StudentID", "Lname", "Fname", "HouseID")  
         VALUES ('HOG720', 'Chang', 'Cho', 'H3')  
    INTO Student ("StudentID", "Lname", "Fname", "HouseID")  
         VALUES ('HOG902', 'Diggory', 'Cedric', 'H2')  
SELECT * FROM dual ;



-- Create StudentClass table
-- Each row represents a student-class.
-- This is sort of a record of each student's registration for each class.
-- A student registering for 2 classes will therefore have 2 records in this table.
CREATE TABLE StudentClass  
    ("StudentClass" varchar2(12) PRIMARY KEY, "StudentID" varchar2(6), "ClassID" varchar2(5)) ;

-- Insert values to StudentClass table
-- Each row represents a student-class.
-- This is sort of a record of each student's registration for each class.
-- A student registering for 2 classes will therefore have 2 records in this table.
INSERT ALL    
    INTO StudentClass ("StudentClass", "StudentID", "ClassID")   
         VALUES ('HOG119-C5555', 'HOG119', 'C5555')   
    INTO StudentClass ("StudentClass", "StudentID", "ClassID")   
         VALUES ('HOG136-C1111', 'HOG136', 'C1111')   
    INTO StudentClass ("StudentClass", "StudentID", "ClassID")   
         VALUES ('HOG136-C2222', 'HOG136', 'C2222')   
    INTO StudentClass ("StudentClass", "StudentID", "ClassID")   
         VALUES ('HOG139-C2222', 'HOG139', 'C2222')   
    INTO StudentClass ("StudentClass", "StudentID", "ClassID")   
         VALUES ('HOG239-C2222', 'HOG239', 'C2222')   
    INTO StudentClass ("StudentClass", "StudentID", "ClassID")   
         VALUES ('HOG437-C1111', 'HOG437', 'C1111')   
    INTO StudentClass ("StudentClass", "StudentID", "ClassID")   
         VALUES ('HOG437-C2222', 'HOG437', 'C2222')   
    INTO StudentClass ("StudentClass", "StudentID", "ClassID")   
         VALUES ('HOG437-C3333', 'HOG437', 'C3333')   
    INTO StudentClass ("StudentClass", "StudentID", "ClassID")   
         VALUES ('HOG563-C2222', 'HOG563', 'C2222')   
    INTO StudentClass ("StudentClass", "StudentID", "ClassID")   
         VALUES ('HOG697-C1111', 'HOG697', 'C1111')   
    INTO StudentClass ("StudentClass", "StudentID", "ClassID")   
         VALUES ('HOG697-C3333', 'HOG697', 'C3333')   
    INTO StudentClass ("StudentClass", "StudentID", "ClassID")   
         VALUES ('HOG697-C5555', 'HOG697', 'C5555')   
    INTO StudentClass ("StudentClass", "StudentID", "ClassID")   
         VALUES ('HOG720-C2222', 'HOG720', 'C2222')   
    INTO StudentClass ("StudentClass", "StudentID", "ClassID")   
         VALUES ('HOG902-C5555', 'HOG902', 'C5555')
    INTO StudentClass ("StudentClass", "StudentID", "ClassID")   
         VALUES ('HOG136-C0000', 'HOG136', 'C0000')   
SELECT * FROM dual ;





-- Create Wand table
-- Their wand cores are probably accurate.
-- Please excuse any inaccuracies.
CREATE TABLE Wand  
    ("StudentID" varchar2(6), "WandCore" varchar2(30)) ;

-- Insert values to Wand table
-- Their wand cores are probably accurate.
-- Please excuse any inaccuracies.
INSERT ALL    
    INTO Wand ("StudentID", "WandCore")   
         VALUES ('HOG119', 'Unicorn Hair')   
    INTO Wand ("StudentID", "WandCore")   
         VALUES ('HOG136', 'Veela Hair')   
    INTO Wand ("StudentID", "WandCore")   
         VALUES ('HOG139', 'Dragon Heartstring')   
    INTO Wand ("StudentID", "WandCore")   
         VALUES ('HOG387', 'Unicorn Hair')   
    INTO Wand ("StudentID", "WandCore")   
         VALUES ('HOG437', 'Phoenix Feather')   
    INTO Wand ("StudentID", "WandCore")   
         VALUES ('HOG697', 'Broken Stuff')   
    INTO Wand ("StudentID", "WandCore")   
         VALUES ('HOG988', 'Veela Hair')   
    INTO Wand ("StudentID", "WandCore")   
         VALUES ('HOG563', 'Unknown')   
    INTO Wand ("StudentID", "WandCore")   
         VALUES ('HOG239', 'Unknown')   
    INTO Wand ("StudentID", "WandCore")   
         VALUES ('HOG720', 'Unknown')   
    INTO Wand ("StudentID", "WandCore")   
         VALUES ('HOG902', 'Unicorn hair')   
SELECT * FROM dual; 

Show entire table

Show the Student table

SELECT * FROM Student;

Show the House table

SELECT * FROM HogwartsHouse;

Show the Wand table

SELECT * FROM Wand;

Show some fields from a single table

Retrieve some fields from a table

Retrieve a table that shows each student's Lname, Fname, and HouseID

SELECT "Lname", "Fname", "HouseID" FROM Student;

Retrieve a table that shows each Instructor's first name, last name, and HouseID

Notice how the order of the fields can be specified

SELECT "InstructorFName", "InstructorLName", "HouseID" FROM Instructor;

Retrieve a table that shows the class titles and their corresponding terms

The force is strong with this :-)

SELECT "ClassTitle", "Term" FROM Class;

Using column aliases

SELECT "House", "Founder" AS PRESIDENT, "Relic" AS TREASURE
FROM HogwartsHouse;

Using computed columns

How much should we pay each Instructor (i.e. Hogwarts Professor) in total?

SELECT "InstructorID", "Pay" + "Bonus" AS TotalPay
FROM Salary;

How much should each instructor (i.e. Hogwarts Professor) get if they each get a 10% raise? Apply the 10% raise on the pay only, not the bonus

SELECT "InstructorID", ("Pay" * 1.1 + "Bonus") AS NewTotalPay
FROM Salary;

How much should each instructor (i.e. Hogwarts Professor) get if they each get a 10% raise? Apply the 10% raise to the total pay

SELECT "InstructorID", ("Pay" + "Bonus")*1.1 AS NewTotalPay
FROM Salary;

Dates

What if there was a mistake somewhere and each instructor's (i.e. Hogwarts Professor) birth date needs 100 days subtracted?

SELECT "InstructorID", "DoB" - 100 AS "CorrectDoB"
FROM DateOfBirth;

Calculate how old is each instructor as of today

SELECT "InstructorID", ROUND((SYSDATE - "DoB")/365, 1) AS "Age"
FROM DateOfBirth;

More accurately, age calculations can be rounded down

SELECT "InstructorID", FLOOR((SYSDATE - "DoB")/365) AS "Age"
FROM DateOfBirth;

List unique values

Get unique values of student IDs

SELECT DISTINCT "StudentID"
FROM MealPlan;

Get unique values of Class IDs

SELECT DISTINCT "ClassID"
FROM StudentClass;

FROM clause options

Get student IDs, combine the number, city and postal codes to show the full address, so as to know where students live using aliases variations

SELECT "StudentID", "Number" || ' ' || "Street" || ' ' || "City" AS "Address", "PostalCode" AS "Zip code"
FROM Address;

Get class titles, terms and seats from Class table to help plan classroom assignments. Combine class title and term to a single field

SELECT "ClassTitle" || '  in ' || "Term" AS "Class and Term", "Seats" AS "Vacancies"
FROM Class;

ORDER BY

Sort students by last name in ascending order

SELECT "Lname", "Fname"
FROM Student
ORDER BY "Lname";

Sort students by last name in descending order

SELECT "Lname", "Fname"
FROM Student
ORDER BY "Lname" DESC;

Hierarchical sorting by last name and then first name ascending

SELECT "Lname", "Fname"
FROM Student
ORDER BY "Lname", "Fname" DESC;

Combining with other operations

SELECT "Lname", "Fname" AS "GIVEN NAME"
FROM Student
ORDER BY "Lname", "Fname" DESC;

Deciding how to sort NAs

SELECT "InstructorFName", "InstructorLName" AS "SURNAME", "HouseID"
FROM Instructor
ORDER BY "HouseID" DESC NULLS LAST;

WHERE using operators

What are the classes offered in winter?

SELECT "ClassTitle", "Term"
FROM Class
WHERE "Term" = 'Winter';

What classes are not offered in Spring?

SELECT "ClassTitle", "Term"
FROM Class
WHERE "Term" != 'Spring';

Which instructor(s) by ID make(s) more than 2000 (include pay only)?

SELECT "InstructorID", "Pay"
FROM Salary
WHERE "Pay" > 2000;

Which instructor(s) by ID make(s) more than or equal to 2000 (include pay and bonus)?

SELECT "InstructorID", "Pay" + "Bonus" AS TotalPay
FROM Salary
WHERE "Pay" + "Bonus" >= 2000;

Using operators on character fields

SELECT "InstructorFName", "InstructorLName" AS "Instructor Surname"
FROM Instructor
WHERE "InstructorLName" > 'Snape'
ORDER BY "InstructorLName" DESC;

Using operators on dates,/p>

SELECT "InstructorID", "DoB"
FROM DateOfBirth
WHERE "DoB" > '06-JUN-1966';

WHERE using logical operators

Retrieve student records from Manchester and London

SELECT "StudentID", "City"
FROM Address
WHERE "City" = 'London' OR "City" = 'Manchester';

What about using AND?

SELECT "StudentID", "City"
FROM Address
WHERE "City" = 'London' AND "City" = 'Manchester';

Retrieve instructor records whose pay is between 1000 and 3000 inclusive

SELECT "InstructorID", "Pay"
FROM Salary
WHERE "Pay" >=1000 AND "Pay" <=3000;

An alternate way to write the preceding statement

SELECT "InstructorID", "Pay"
FROM Salary
WHERE "Pay" 
BETWEEN 1000 AND 3000;

What if we use OR?

SELECT "InstructorID", "Pay"
FROM Salary
WHERE "Pay" >=1000 OR "Pay" <=3000;

What are the class(es) offered in Fall and Winter that have less than or equal to 25 seats?

SELECT "ClassTitle", "Term", "Seats"
FROM Class
WHERE ("Term" = 'Fall' OR "Term" = 'Winter') AND "Seats" <=25;

The preceding statement can also be written as

SELECT "ClassTitle", "Term", "Seats"
FROM Class
WHERE ("Term" IN ('Fall', 'Winter'));

What are the class(es) offered in Summer that has/have more than or equal to 15 seats?

SELECT "ClassTitle", "Term", "Seats"
FROM Class
WHERE "Term" = 'Summer' AND "Seats" >=15;

What are the class(es) offered in Winter that has/have no seats left?

SELECT "ClassTitle", "Term", "Seats"
FROM Class
WHERE "Term" = 'Winter' AND "Seats" =0;

What are the class(es) offered that seats left?

SELECT "ClassTitle", "Term", "Seats"
FROM Class
WHERE NOT("Seats" =0);

What are the class(es) offered that are not in Spring and Summer?

SELECT "ClassTitle", "Term", "Seats"
FROM Class
WHERE NOT("Term" = 'Spring' OR "Term" = 'Summer');

What if the preceding statement uses AND?

SELECT "ClassTitle", "Term", "Seats"
FROM Class
WHERE NOT("Term" = 'Spring' AND "Term" = 'Summer');

WHERE using LIKE and NULL

Retrieve all students whose last names (surnames) start with W

SELECT "Fname", "Lname", "HouseID"
FROM Student
WHERE "Lname" LIKE 'W%';

Retrieve all students whose first names start with L

SELECT "Fname", "Lname", "HouseID"
FROM Student
WHERE "Fname" LIKE 'L%';

Retrieve all students whose first names end with e

How do we get those ending with y? How do we get Ginny Weasley only?

SELECT "Fname", "Lname", "HouseID"
FROM Student
WHERE "Fname" LIKE '%e';

What if we're not sure if Cho Chang is spelt "Chang" or "Cheng"?

SELECT "Fname", "Lname", "HouseID"
FROM Student
WHERE "Lname" LIKE 'Ch_ng';

Retrieve all instructors whose first names include e

SELECT "InstructorFName", "InstructorLName", "HouseID"
FROM Instructor
WHERE "InstructorFName" LIKE '%e%';

Retrieve all instructors whose first names end with "us"

What about LIKE '%us%'

SELECT "InstructorFName", "InstructorLName", "HouseID"
FROM Instructor
WHERE "InstructorFName" LIKE '%us';

Retrieve all instructors whose first names begin with "Se"

SELECT "InstructorFName", "InstructorLName", "HouseID"
FROM Instructor
WHERE "InstructorFName" LIKE 'Se%';

What if we're not sure if Yeoda is spelt "Yoda" or "Yeoda"?

SELECT "InstructorFName", "InstructorLName", "HouseID"
FROM Instructor
WHERE "InstructorLName" LIKE 'Y%da';

Is any instructor missing a bonus?

SELECT "InstructorID", "Bonus"
FROM Salary
WHERE "Bonus" IS NULL;

Which instructors have a bonus?

SELECT "InstructorID", "Bonus"
FROM Salary
WHERE "Bonus" IS NOT NULL;

Which instructors do not belong to a House?

SELECT "InstructorFName", "InstructorLName", "HouseID"
FROM Instructor
WHERE "HouseID" IS NULL;

Which instructors belong to a House?

SELECT "InstructorFName", "InstructorLName", "HouseID"
FROM Instructor
WHERE "HouseID" IS NOT NULL;

JOIN

Which houses do the instructors belong to?

SELECT "InstructorFName", "InstructorLName", "House"
FROM HogwartsHouse
JOIN Instructor
ON HogwartsHouse."HouseID" = Instructor."HouseID";

What are the Dates of Birth of the instructors?

SELECT "InstructorFName", "InstructorLName", "DoB"
FROM Instructor
JOIN DateOfBirth
ON Instructor."InstructorID" = DateOfBirth."InstructorID";

Retrieve a table of student names with their addresses to send Hogwarts letters.

SELECT "Fname", "Lname", "Number", "Street", "City", "PostalCode" 
FROM Student
JOIN Address
ON Student."StudentID" = Address."StudentID";

What are the email addresses of the students?

SELECT "Fname", "Lname", "EmailID"
FROM Student
JOIN Email
ON Student."StudentID" = Email."StudentID";

What are the wand cores of the students?

SELECT "Fname", "Lname", "WandCore"
FROM Student
JOIN Wand
ON Student."StudentID" = Wand."StudentID";

Criteria in JOIN

Which houses do the instructors whose first names begin with S belong to?

SELECT "InstructorFName", "InstructorLName", "House"
FROM HogwartsHouse
JOIN Instructor
ON HogwartsHouse."HouseID" = Instructor."HouseID"
WHERE Instructor."InstructorFName" LIKE 'S%';

What are the Dates of Birth of the instructors whose last names contain o?

SELECT "InstructorFName", "InstructorLName", "DoB"
FROM Instructor
JOIN DateOfBirth
ON Instructor."InstructorID" = DateOfBirth."InstructorID"
WHERE Instructor."InstructorLName" LIKE '%o%'
ORDER BY Instructor."InstructorLName" DESC;

Retrieve a table of student names who live in Portsmouth to send Hogwarts letters.

SELECT "Fname", "Lname", "Number", "Street", "City", "PostalCode" 
FROM Student
JOIN Address
ON Student."StudentID" = Address."StudentID"
WHERE Address."City" = 'Portsmouth';

What are the email addresses of the students whose first names end with y or those whose last names end with y?

SELECT "Fname", "Lname", "EmailID"
FROM Student
JOIN Email
ON Student."StudentID" = Email."StudentID"
WHERE Student."Fname" LIKE '%y' OR Student."Lname" LIKE '%y';

What are the names of the instructors whose salaries are below 1500 or above 3000, inclusive, whose last names have 5 characters? Please note that there are 5 "_" in the query

SELECT "InstructorFName", "InstructorLName", "Pay"
FROM Instructor
JOIN Salary
ON Instructor."InstructorID" = Salary."InstructorID"
WHERE (Salary."Pay" <=1500 OR Salary."Pay" >=3000) 
AND Instructor."InstructorLName" LIKE '_____';

SELECT "InstructorFName", "InstructorLName" AS "Surname", "Pay"
FROM Instructor
JOIN Salary
ON Instructor."InstructorID" = Salary."InstructorID"
WHERE (Salary."Pay" <=1500 OR Salary."Pay" >=3000) 
AND Instructor."InstructorLName" LIKE '_____'
ORDER BY "InstructorLName" DESC;

JOIN LEFT RIGHT

Which student(s) did not purchase a meal plan? Retrieve all student names, and the meal plans they purchased, including those who did not purchase one.

SELECT "Fname", "Lname", "MealPlanIDNo"
FROM Student
LEFT JOIN MealPlan
ON Student."StudentID" = MealPlan."StudentID";

Are there meal plans not associated with a student?

SELECT "Fname", "Lname", "MealPlanIDNo"
FROM Student
RIGHT JOIN MealPlan
ON Student."StudentID" = MealPlan."StudentID"
WHERE Student."StudentID" IS NULL;

COUNT

How many classes are there?

SELECT COUNT("ClassID")
FROM Class;

How many classes are there in Spring?

SELECT COUNT("ClassID")
FROM Class
WHERE "Term" = 'Spring';

How many classes are there in Spring with at least 20 seats?

SELECT COUNT("ClassID")
FROM Class
    WHERE "Term" = 'Spring' 
    AND "Seats" >=20;

How many instructors are paid less than 1500?

SELECT COUNT("InstructorID")
FROM Salary
    WHERE "Pay" <1500; 

How many instructors are paid less than 1500 in total?

SELECT COUNT("InstructorID")
FROM Salary
    WHERE "Pay" + "Bonus" <1500; 

How many instructors do not teach?

SELECT COUNT(Instructor."InstructorID")
    FROM Instructor
    LEFT JOIN InstructorClass
	ON Instructor."InstructorID" = InstructorClass."InstructorID"
	LEFT JOIN Class ON InstructorClass."ClassID" = Class."ClassID"
    WHERE Class."ClassTitle" IS NULL;

How many classes have no instructors assigned?

SELECT COUNT(Class."ClassID")
    FROM Class
    FULL JOIN InstructorClass
	ON Class."ClassID" = InstructorClass."ClassID"
	FULL JOIN Instructor 
    ON InstructorClass."InstructorID" = Instructor."InstructorID"
    WHERE Instructor."InstructorLName" IS NULL;

MIN MAX

What is the highest pay?

SELECT MAX("Pay")
FROM Salary;

What is the highest pay (easier to read)?

SELECT MAX("Pay") AS "HIGHEST PAY"
FROM Salary;

What are the highest pay and highest bonus?

SELECT MAX("Pay") AS "HIGHEST PAY", MAX("Bonus") AS "HIGHEST BONUS"
FROM Salary;

What are the lowest pay and lowest bonus?

SELECT MIN("Pay") AS "LOWEST PAY", MIN("Bonus") AS "LOWEST BONUS"
FROM Salary;

Who has the highest pay?

SELECT "InstructorFName", "InstructorLName", "Pay"
    FROM Instructor
    JOIN Salary
    ON Instructor."InstructorID" = Salary."InstructorID"
    WHERE "Pay" = (SELECT MAX("Pay") FROM Salary);

Who has the lowest bonus?

SELECT "InstructorFName", "InstructorLName", "Bonus"
    FROM Instructor
    JOIN Salary
    ON Instructor."InstructorID" = Salary."InstructorID"
    WHERE "Bonus" = (SELECT MIN("Bonus") FROM Salary);

Which class has the most seats available?

SELECT "ClassTitle", "Seats"
    FROM Class
    WHERE "Seats" = (SELECT MAX("Seats") FROM Class);

Which class has the most seats available?

SELECT "ClassTitle", "Seats"
    FROM Class
    WHERE "Seats" = (SELECT MAX("Seats") FROM Class);

Which class has the least seats available and who's teaching it?

SELECT "ClassTitle", "Seats", "InstructorFName", "InstructorLName"
    FROM Class
    JOIN InstructorClass
    ON Class."ClassID" = InstructorClass."ClassID"
    JOIN Instructor
    ON InstructorClass."InstructorID" = Instructor."InstructorID"
    WHERE "Seats" = (SELECT MIN("Seats") FROM Class);

GROUP BY

What are the total seats available by Term?

SELECT "Term", SUM("Seats") AS "TOTAL SEATS AVAILABLE"
FROM Class
GROUP BY "Term";

What is the average number of seats available by term?

SELECT "Term", AVG("Seats") AS "AVERAGE SEATS AVAILABLE"
FROM Class
GROUP BY "Term";

What is the average pay by house?

SELECT "House", AVG("Pay") AS "AVERAGE PAY"
    FROM Salary
    JOIN Instructor
    ON Salary."InstructorID" = Instructor."InstructorID"
    JOIN HogwartsHouse
    ON Instructor."HouseID" = HogwartsHouse."HouseID"
    GROUP BY HogwartsHouse."House"
    ORDER BY AVG("Pay") DESC;

HAVING

Which terms have more than 60 seats available in total?

SELECT "Term", SUM("Seats") AS "TOTAL SEATS AVAILABLE"
FROM Class
GROUP BY "Term"
    HAVING SUM("Seats") >60;
	

Which terms have more than 20 seats available on average?

SELECT "Term", AVG("Seats") AS "AVERAGE SEATS AVAILABLE"
FROM Class
GROUP BY "Term"
HAVING AVG("Seats") >20
ORDER BY AVG("Seats");

Multiple JOIN FULL

This involves >2 tables

Retrieve a table that shows the names of each student and the class(es) they registered for

SELECT "Fname", "Lname", "ClassTitle"
    FROM Student
    JOIN StudentClass
	ON Student."StudentID" = StudentClass."StudentID"
	JOIN Class ON StudentClass."ClassID" = Class."ClassID";

What classes did the students register for, who are their instructors (i.e. professors) and which term?

SELECT "Fname", "Lname", "ClassTitle", "InstructorLName", "Term"
    FROM Instructor
    JOIN InstructorClass
	ON Instructor."InstructorID" = InstructorClass."InstructorID"
	JOIN Class 
    ON InstructorClass."ClassID" = Class."ClassID"
    JOIN StudentClass
    ON InstructorClass."ClassID" = StudentClass."ClassID"
    JOIN Student
    ON StudentClass."StudentID" = Student."StudentID" 
    ORDER BY Student."Fname";

Retrieve a table that shows the names of each student, their houses, and wands

SELECT "Fname", "Lname", "House", "WandCore"
    FROM Student
    JOIN Wand
	ON Student."StudentID" = Wand."StudentID"
	JOIN HogwartsHouse 
ON Student."HouseID" = HogwartsHouse."HouseID";

Retrieve a table that shows the names of each instructor (i.e. Hogwarts professor) and the class(es) they teach, when, and the number of seats are available.

SELECT "InstructorFName", "InstructorLName", "ClassTitle" AS Training, "Term", "Seats"
    FROM Instructor
    JOIN InstructorClass
	ON Instructor."InstructorID" = InstructorClass."InstructorID"
	JOIN Class ON InstructorClass."ClassID" = Class."ClassID";

Which instructor(s) is/are not teaching?

SELECT "InstructorFName", "InstructorLName", "ClassTitle"
    FROM Instructor
    LEFT JOIN InstructorClass
	ON Instructor."InstructorID" = InstructorClass."InstructorID"
	LEFT JOIN Class ON InstructorClass."ClassID" = Class."ClassID"
    WHERE Class."ClassTitle" IS NULL;

Retrieve a table of all instructors and the classes they teach whether or not they are teaching

SELECT "InstructorFName", "InstructorLName", "ClassTitle"
    FROM Instructor
    FULL JOIN InstructorClass
	ON Instructor."InstructorID" = InstructorClass."InstructorID"
	FULL JOIN Class ON InstructorClass."ClassID" = Class."ClassID"
    ORDER BY Instructor."InstructorLName" DESC NULLS LAST;

Which classes have no instructor assigned?

SELECT "ClassTitle", "Term", "InstructorLName"
    FROM Class
    FULL JOIN InstructorClass
	ON Class."ClassID" = InstructorClass."ClassID"
	FULL JOIN Instructor 
    ON InstructorClass."InstructorID" = Instructor."InstructorID"
    WHERE Instructor."InstructorLName" IS NULL;

Which classes have which students? Who are teaching those classes?

SELECT "ClassTitle", "Term", "InstructorLName", Student."Fname"
    FROM Class
    FULL JOIN InstructorClass
	ON Class."ClassID" = InstructorClass."ClassID"
	FULL JOIN Instructor 
    ON InstructorClass."InstructorID" = Instructor."InstructorID"
    JOIN StudentClass
    ON Class."ClassID" = StudentClass."ClassID"
    JOIN Student
    ON StudentClass."StudentID" = Student."StudentID";

Which classes have students registered but no instructor (i.e. professor) assigned?

SELECT "ClassTitle", "Term", "InstructorLName", Student."Fname"
    FROM Class
    FULL JOIN InstructorClass
	ON Class."ClassID" = InstructorClass."ClassID"
	FULL JOIN Instructor 
    ON InstructorClass."InstructorID" = Instructor."InstructorID"
    JOIN StudentClass
    ON Class."ClassID" = StudentClass."ClassID"
    JOIN Student
    ON StudentClass."StudentID" = Student."StudentID"
    WHERE Instructor."InstructorLName" IS NULL;

Retrieve a table that shows the names of each instructor, their houses, and pay and bonuses

SELECT "InstructorFName", "InstructorLName", "House", "Pay", "Bonus"
    FROM Instructor
    JOIN Salary
	ON Instructor."InstructorID" = Salary."InstructorID"
	JOIN HogwartsHouse 
ON Instructor."HouseID" = HogwartsHouse."HouseID";

Who are the instructors whose last names start with S or after and have a pay that is less than 2000? What are their houses?

SELECT "InstructorFName", "InstructorLName", "House", "Pay", "Bonus"
    FROM Instructor
    JOIN Salary
	ON Instructor."InstructorID" = Salary."InstructorID"
	JOIN HogwartsHouse 
ON Instructor."HouseID" = HogwartsHouse."HouseID"
    WHERE Instructor."InstructorLName" >= 'S' 
    AND Salary."Pay" <2000;

Who are the instructors whose last names start with S or before and have a total salary (i.e. pay + bonus) pay that is more than 3000? What are their houses?

SELECT "InstructorFName", "InstructorLName", "House", "Pay" + "Bonus" AS TotalSalary
    FROM Instructor
    JOIN Salary
	ON Instructor."InstructorID" = Salary."InstructorID"
	JOIN HogwartsHouse 
ON Instructor."HouseID" = HogwartsHouse."HouseID"
    WHERE Instructor."InstructorLName" <= 'S' 
    AND Salary."Pay" + Salary."Bonus" >2500
ORDER BY TotalSalary DESC;