CREATE TABLE Address
("StudentID" varchar2(6) PRIMARY KEY, "Number" varchar2(4), "Street" varchar2(13), "City" varchar2(15), "PostalCode" int) Table created.
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 11 row(s) inserted.
CREATE TABLE Class
("ClassID" varchar2(5) PRIMARY KEY, "ClassTitle" varchar2(29), "Term" varchar2(6), "Seats" int)Table created.
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 DUAL11 row(s) inserted.
CREATE TABLE DateOfBirth
("InstructorID" varchar2(6) PRIMARY KEY, "DoB" date)Table created.
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 dual14 row(s) inserted.
CREATE TABLE Email
("StudentID" varchar2(6) PRIMARY KEY, "EmailID" varchar2(50))Table created.
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 dual11 row(s) inserted.
CREATE TABLE HogwartsHouse
("HouseID" varchar2(2) PRIMARY KEY, "House" varchar2(10), "Founder" varchar2(17), "Relic" varchar2(20)) Table created.
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 5 row(s) inserted.
CREATE TABLE Instructor
("InstructorID" varchar2(6) PRIMARY KEY, "InstructorFName" varchar2(8), "InstructorLName" varchar2(10), "HouseID" varchar2(2)) Table created.
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 14 row(s) inserted.
CREATE TABLE InstructorClass
("InstructorClass" varchar2(12) PRIMARY KEY, "InstructorID" varchar2(6), "ClassID" varchar2(5)) Table created.
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 dual8 row(s) inserted.
CREATE TABLE MealPlan
("StudentID" varchar2(6), "MealPlanIDNo" varchar2(6) PRIMARY KEY) Table created.
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 11 row(s) inserted.
CREATE TABLE Salary
("InstructorID" varchar2(6) PRIMARY KEY, "Pay" int, "Bonus" int) Table created.
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 14 row(s) inserted.
CREATE TABLE Student
("StudentID" varchar2(6), "Lname" varchar2(10), "Fname" varchar2(8), "HouseID" varchar2(2)) Table created.
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 11 row(s) inserted.
CREATE TABLE StudentClass
("StudentClass" varchar2(12) PRIMARY KEY, "StudentID" varchar2(6), "ClassID" varchar2(5)) Table created.
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 15 row(s) inserted.
CREATE TABLE Wand
("StudentID" varchar2(6), "WandCore" varchar2(30)) Table created.
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 dual11 row(s) inserted.
SELECT * FROM Student| StudentID | Lname | Fname | HouseID | HOG119 | Malfoy | Draco | H4 | HOG136 | Lovegood | Luna | H3 | HOG139 | Granger | Hermione | H1 | HOG387 | Longbottom | Neville | H1 | HOG437 | Potter | Harry | H1 | HOG697 | Weasley | Ron | H1 | HOG988 | Weasley | Ginny | H1 | HOG563 | Brown | Lavender | H1 | HOG239 | Finnigan | Seamus | H1 | HOG720 | Chang | Cho | H3 | HOG902 | Diggory | Cedric | H2 |
|---|
SELECT * FROM HogwartsHouse| HouseID | House | Founder | Relic | H1 | Gryffindor | Godric Gryffindor | Sword | H2 | Hufflepuff | Helga Hufflepuff | Cup | H3 | Ravenclaw | Rowena Ravenclaw | Diadem | H4 | Slytherin | Salazar Slytherin | Locket | H5 | Jedi | Master Yeoda | Light Sabre |
|---|
SELECT * FROM Wand| StudentID | WandCore | HOG119 | Unicorn Hair | HOG136 | Veela Hair | HOG139 | Dragon Heartstring | HOG387 | Unicorn Hair | HOG437 | Phoenix Feather | HOG697 | Broken Stuff | HOG988 | Veela Hair | HOG563 | Unknown | HOG239 | Unknown | HOG720 | Unknown | HOG902 | Unicorn hair |
|---|
SELECT "Lname", "Fname", "HouseID" FROM Student| Lname | Fname | HouseID | Malfoy | Draco | H4 | Lovegood | Luna | H3 | Granger | Hermione | H1 | Longbottom | Neville | H1 | Potter | Harry | H1 | Weasley | Ron | H1 | Weasley | Ginny | H1 | Brown | Lavender | H1 | Finnigan | Seamus | H1 | Chang | Cho | H3 | Diggory | Cedric | H2 |
|---|
SELECT "InstructorFName", "InstructorFName", "HouseID" FROM Instructor| InstructorFName | InstructorFName | HouseID | Albus | Albus | H1 | Dolores | Dolores | H3 | Serius | Serius | H1 | Firenze | Firenze | - | Filius | Filius | H3 | Rubeus | Rubeus | - | Gilderoy | Gilderoy | H3 | Sybill | Sybill | H3 | Alecto | Alecto | H4 | Remus | Remus | H1 | Horace | Horace | H4 | Severus | Severus | H4 | Poppy | Poppy | H2 | Master | Master | H5 |
|---|
SELECT "ClassTitle", "Term" FROM Class| ClassTitle | Term | Potions | Fall | Flying | Summer | Defence against the dark arts | Fall | Divination | Fall | Quiddich | Spring | Care for Magical Creatures | Spring | Herbology | Spring | Charms | Summer | Apparition | Summer | Muggle Studies | Summer | The Force | Winter |
|---|
SELECT "House", "Founder" AS PRESIDENT, "Relic" AS TREASURE
FROM HogwartsHouse| House | PRESIDENT | TREASURE | Gryffindor | Godric Gryffindor | Sword | Hufflepuff | Helga Hufflepuff | Cup | Ravenclaw | Rowena Ravenclaw | Diadem | Slytherin | Salazar Slytherin | Locket | Jedi | Master Yeoda | Light Sabre |
|---|
SELECT "InstructorID", "Pay" + "Bonus" AS TotalPay
FROM Salary| InstructorID | TOTALPAY | GA2345 | 1380 | SA3456 | 1500 | GA4567 | 1800 | XA5678 | 2300 | RB1234 | 2250 | GB2345 | 2500 | RB3456 | 2720 | RB4567 | 3500 | SB5678 | 3180 | GC1234 | 4200 | SC2345 | 3960 | SA1234 | 1150 | HA6789 | 1400 | MY6666 | 976 |
|---|
SELECT "InstructorID", ("Pay" * 1.1 + "Bonus") AS NewTotalPay
FROM Salary| InstructorID | NEWTOTALPAY | GA2345 | 1500 | SA3456 | 1640 | GA4567 | 1960 | XA5678 | 2480 | RB1234 | 2450 | GB2345 | 2720 | RB3456 | 2960 | RB4567 | 3760 | SB5678 | 3460 | GC1234 | 4550 | SC2345 | 4320 | SA1234 | 1250 | HA6789 | 1520 | MY6666 | 1064.8 |
|---|
SELECT "InstructorID", ("Pay" + "Bonus")*1.1 AS NewTotalPay
FROM Salary| InstructorID | NEWTOTALPAY | GA2345 | 1518 | SA3456 | 1650 | GA4567 | 1980 | XA5678 | 2530 | RB1234 | 2475 | GB2345 | 2750 | RB3456 | 2992 | RB4567 | 3850 | SB5678 | 3498 | GC1234 | 4620 | SC2345 | 4356 | SA1234 | 1265 | HA6789 | 1540 | MY6666 | 1073.6 |
|---|
SELECT "InstructorID", "DoB" - 100 AS "CorrectDoB"
FROM DateOfBirth| InstructorID | CorrectDoB | GA2345 | 19-JAN-31 | SA3456 | 20-DEC-34 | GA4567 | 11-AUG-53 | XA5678 | 16-OCT-64 | RB1234 | 02-MAY-32 | GB2345 | 15-MAR-60 | RB3456 | 02-NOV-39 | RB4567 | 31-MAR-69 | SB5678 | 24-AUG-69 | GC1234 | 29-JUN-62 | SC2345 | 09-JUN-62 | SA1234 | 03-FEB-58 | HA6789 | 10-APR-52 | MY6666 | 27-FEB-96 |
|---|
SELECT "InstructorID", ROUND((SYSDATE - "DoB")/365, 1) AS "Age"
FROM DateOfBirth| InstructorID | Age | GA2345 | 92.4 | SA3456 | 88.5 | GA4567 | 69.8 | XA5678 | 58.6 | RB1234 | 91.1 | GB2345 | 63.2 | RB3456 | 83.6 | RB4567 | 54.2 | SB5678 | 53.8 | GC1234 | 61 | SC2345 | 61 | SA1234 | 65.4 | HA6789 | 71.2 | MY6666 | 1128 |
|---|
SELECT DISTINCT "StudentID"
FROM MealPlan| StudentID | HOG387 | HOG136 | HOG437 | HOG697 | HOG139 | HOG988 | HOG119 |
|---|
SELECT DISTINCT "ClassID"
FROM StudentClass| ClassID | C0000 | C5555 | C3333 | C1111 | C2222 |
|---|
SELECT "StudentID", "City", "PostalCode"
FROM Address| StudentID | City | PostalCode | HOG119 | Manchester | 123456789 | HOG136 | Sunderland | 234557329 | HOG139 | London | 234252342 | HOG387 | Norwich | 234252525 | HOG437 | Anfield | 235254534 | HOG697 | London | 255423645 | HOG988 | London | 346347564 | HOG563 | Portsmouth | 736495730 | HOG239 | London | 726354859 | HOG720 | Burnley | 825485029 | HOG902 | Brighton | 245826940 |
|---|
SELECT "ClassTitle", "Term", "Seats"
FROM Class| ClassTitle | Term | Seats | Potions | Fall | 30 | Flying | Summer | 30 | Defence against the dark arts | Fall | 25 | Divination | Fall | 30 | Quiddich | Spring | 25 | Care for Magical Creatures | Spring | 35 | Herbology | Spring | 15 | Charms | Summer | 10 | Apparition | Summer | 10 | Muggle Studies | Summer | 15 | The Force | Winter | 0 |
|---|
SELECT "Lname", "Fname"
FROM Student
ORDER BY "Lname"| Lname | Fname | Brown | Lavender | Chang | Cho | Diggory | Cedric | Finnigan | Seamus | Granger | Hermione | Longbottom | Neville | Lovegood | Luna | Malfoy | Draco | Potter | Harry | Weasley | Ginny | Weasley | Ron |
|---|
SELECT "Lname", "Fname"
FROM Student
ORDER BY "Lname" DESC| Lname | Fname | Weasley | Ron | Weasley | Ginny | Potter | Harry | Malfoy | Draco | Lovegood | Luna | Longbottom | Neville | Granger | Hermione | Finnigan | Seamus | Diggory | Cedric | Chang | Cho | Brown | Lavender |
|---|
SELECT "Lname", "Fname"
FROM Student
ORDER BY "Lname", "Fname" DESC| Lname | Fname | Brown | Lavender | Chang | Cho | Diggory | Cedric | Finnigan | Seamus | Granger | Hermione | Longbottom | Neville | Lovegood | Luna | Malfoy | Draco | Potter | Harry | Weasley | Ron | Weasley | Ginny |
|---|
SELECT "Lname", "Fname" AS "GIVEN NAME"
FROM Student
ORDER BY "Lname", "Fname" DESC| Lname | GIVEN NAME | Brown | Lavender | Chang | Cho | Diggory | Cedric | Finnigan | Seamus | Granger | Hermione | Longbottom | Neville | Lovegood | Luna | Malfoy | Draco | Potter | Harry | Weasley | Ron | Weasley | Ginny |
|---|
SELECT "InstructorFName", "InstructorLName" AS "SURNAME", "HouseID"
FROM Instructor
ORDER BY "HouseID" DESC NULLS LAST| InstructorFName | SURNAME | HouseID | Master | Yeoda | H5 | Severus | Snape | H4 | Horace | Slughorn | H4 | Alecto | Carrow | H4 | Filius | Flitwick | H3 | Dolores | Umbridge | H3 | Gilderoy | Lockhart | H3 | Sybill | Trelawney | H3 | Poppy | Pomfrey | H2 | Remus | Lupin | H1 | Serius | Black | H1 | Albus | Dumbledore | H1 | Firenze | Centaur | - | Rubeus | Hagrid | - |
|---|
SELECT "ClassTitle", "Term"
FROM Class
WHERE "Term" = 'Winter'| ClassTitle | Term | The Force | Winter |
|---|
SELECT "ClassTitle", "Term"
FROM Class
WHERE "Term" != 'Spring'| ClassTitle | Term | Potions | Fall | Flying | Summer | Defence against the dark arts | Fall | Divination | Fall | Charms | Summer | Apparition | Summer | Muggle Studies | Summer | The Force | Winter |
|---|
SELECT "InstructorID", "Pay"
FROM Salary
WHERE "Pay" > 2000| InstructorID | Pay | GB2345 | 2200 | RB3456 | 2400 | RB4567 | 2600 | SB5678 | 2800 | GC1234 | 3500 | SC2345 | 3600 |
|---|
SELECT "InstructorID", "Pay" + "Bonus" AS TotalPay
FROM Salary
WHERE "Pay" + "Bonus" >= 2000| InstructorID | TOTALPAY | XA5678 | 2300 | RB1234 | 2250 | GB2345 | 2500 | RB3456 | 2720 | RB4567 | 3500 | SB5678 | 3180 | GC1234 | 4200 | SC2345 | 3960 |
|---|
SELECT "InstructorFName", "InstructorLName"
FROM Instructor
WHERE "InstructorLName" > 'Snape'| InstructorFName | InstructorLName | Dolores | Umbridge | Sybill | Trelawney | Master | Yeoda |
|---|
SELECT "InstructorID", "DoB"
FROM DateOfBirth
WHERE "DoB" > '06-JUN-1966'| InstructorID | DoB | RB4567 | 09-JUL-69 | SB5678 | 02-DEC-69 |
|---|
SELECT "StudentID", "City"
FROM Address
WHERE "City" = 'London' OR "City" = 'Manchester'| StudentID | City | HOG119 | Manchester | HOG139 | London | HOG697 | London | HOG988 | London | HOG239 | London |
|---|
SELECT "StudentID", "City"
FROM Address
WHERE "City" = 'London' AND "City" = 'Manchester'no data found
SELECT "InstructorID", "Pay"
FROM Salary
WHERE "Pay" >=1000 AND "Pay" <=3000| InstructorID | Pay | GA2345 | 1200 | SA3456 | 1400 | GA4567 | 1600 | XA5678 | 1800 | RB1234 | 2000 | GB2345 | 2200 | RB3456 | 2400 | RB4567 | 2600 | SB5678 | 2800 | SA1234 | 1000 | HA6789 | 1200 |
|---|
SELECT "InstructorID", "Pay"
FROM Salary
WHERE "Pay"
BETWEEN 1000 AND 3000| InstructorID | Pay | GA2345 | 1200 | SA3456 | 1400 | GA4567 | 1600 | XA5678 | 1800 | RB1234 | 2000 | GB2345 | 2200 | RB3456 | 2400 | RB4567 | 2600 | SB5678 | 2800 | SA1234 | 1000 | HA6789 | 1200 |
|---|
SELECT "InstructorID", "Pay"
FROM Salary
WHERE "Pay" >=1000 OR "Pay" <=3000| InstructorID | Pay | GA2345 | 1200 | SA3456 | 1400 | GA4567 | 1600 | XA5678 | 1800 | RB1234 | 2000 | GB2345 | 2200 | RB3456 | 2400 | RB4567 | 2600 | SB5678 | 2800 | GC1234 | 3500 | SC2345 | 3600 | SA1234 | 1000 | HA6789 | 1200 | MY6666 | 888 |
|---|
SELECT "ClassTitle", "Term", "Seats"
FROM Class
WHERE ("Term" = 'Fall' OR "Term" = 'Winter') AND "Seats" <=25| ClassTitle | Term | Seats | Defence against the dark arts | Fall | 25 | The Force | Winter | 0 |
|---|
SELECT "ClassTitle", "Term", "Seats"
FROM Class
WHERE ("Term" IN ('Fall', 'Winter'))| ClassTitle | Term | Seats | Potions | Fall | 30 | Defence against the dark arts | Fall | 25 | Divination | Fall | 30 | The Force | Winter | 0 |
|---|
SELECT "ClassTitle", "Term", "Seats"
FROM Class
WHERE "Term" = 'Summer' AND "Seats" >=15| ClassTitle | Term | Seats | Flying | Summer | 30 | Muggle Studies | Summer | 15 |
|---|
SELECT "ClassTitle", "Term", "Seats"
FROM Class
WHERE "Term" = 'Winter' AND "Seats" =0| ClassTitle | Term | Seats | The Force | Winter | 0 |
|---|
SELECT "ClassTitle", "Term", "Seats"
FROM Class
WHERE NOT("Seats" =0)| ClassTitle | Term | Seats | Potions | Fall | 30 | Flying | Summer | 30 | Defence against the dark arts | Fall | 25 | Divination | Fall | 30 | Quiddich | Spring | 25 | Care for Magical Creatures | Spring | 35 | Herbology | Spring | 15 | Charms | Summer | 10 | Apparition | Summer | 10 | Muggle Studies | Summer | 15 |
|---|
SELECT "Fname", "Lname", "HouseID"
FROM Student
WHERE "Lname" LIKE 'W%'| Fname | Lname | HouseID | Ron | Weasley | H1 | Ginny | Weasley | H1 |
|---|
SELECT "Fname", "Lname", "HouseID"
FROM Student
WHERE "Fname" LIKE 'L%'| Fname | Lname | HouseID | Luna | Lovegood | H3 | Lavender | Brown | H1 |
|---|
SELECT "Fname", "Lname", "HouseID"
FROM Student
WHERE "Fname" LIKE '%e'| Fname | Lname | HouseID | Hermione | Granger | H1 | Neville | Longbottom | H1 |
|---|
SELECT "Fname", "Lname", "HouseID"
FROM Student
WHERE "Lname" LIKE 'Ch_ng'| Fname | Lname | HouseID | Cho | Chang | H3 |
|---|
SELECT "InstructorFName", "InstructorLName", "HouseID"
FROM Instructor
WHERE "InstructorFName" LIKE '%e%'| InstructorFName | InstructorLName | HouseID | Dolores | Umbridge | H3 | Serius | Black | H1 | Firenze | Centaur | - | Rubeus | Hagrid | - | Gilderoy | Lockhart | H3 | Alecto | Carrow | H4 | Remus | Lupin | H1 | Horace | Slughorn | H4 | Severus | Snape | H4 | Master | Yeoda | H5 |
|---|
SELECT "InstructorFName", "InstructorLName", "HouseID"
FROM Instructor
WHERE "InstructorFName" LIKE '%us%'| InstructorFName | InstructorLName | HouseID | Albus | Dumbledore | H1 | Serius | Black | H1 | Filius | Flitwick | H3 | Rubeus | Hagrid | - | Remus | Lupin | H1 | Severus | Snape | H4 |
|---|
SELECT "InstructorFName", "InstructorLName", "HouseID"
FROM Instructor
WHERE "InstructorFName" LIKE 'Se%'| InstructorFName | InstructorLName | HouseID | Serius | Black | H1 | Severus | Snape | H4 |
|---|
SELECT "InstructorFName", "InstructorLName", "HouseID"
FROM Instructor
WHERE "InstructorLName" LIKE 'Y%da'| InstructorFName | InstructorLName | HouseID | Master | Yeoda | H5 |
|---|
SELECT "InstructorID", "Bonus"
FROM Salary
WHERE "Bonus" IS NULLno data found
SELECT "InstructorID", "Bonus"
FROM Salary
WHERE "Bonus" IS NOT NULL| InstructorID | Bonus | GA2345 | 180 | SA3456 | 100 | GA4567 | 200 | XA5678 | 500 | RB1234 | 250 | GB2345 | 300 | RB3456 | 320 | RB4567 | 900 | SB5678 | 380 | GC1234 | 700 | SC2345 | 360 | SA1234 | 150 | HA6789 | 200 | MY6666 | 88 |
|---|
SELECT "InstructorFName", "InstructorLName", "House"
FROM HogwartsHouse
JOIN Instructor
ON HogwartsHouse."HouseID" = Instructor."HouseID"| InstructorFName | InstructorLName | House | Albus | Dumbledore | Gryffindor | Dolores | Umbridge | Ravenclaw | Serius | Black | Gryffindor | Filius | Flitwick | Ravenclaw | Gilderoy | Lockhart | Ravenclaw | Sybill | Trelawney | Ravenclaw | Alecto | Carrow | Slytherin | Remus | Lupin | Gryffindor | Horace | Slughorn | Slytherin | Severus | Snape | Slytherin | Poppy | Pomfrey | Hufflepuff | Master | Yeoda | Jedi |
|---|
SELECT "InstructorFName", "InstructorLName", "DoB"
FROM Instructor
JOIN DateOfBirth
ON Instructor."InstructorID" = DateOfBirth."InstructorID"| InstructorFName | InstructorLName | DoB | Albus | Dumbledore | 29-APR-31 | Dolores | Umbridge | 30-MAR-35 | Serius | Black | 19-NOV-53 | Firenze | Centaur | 24-JAN-65 | Filius | Flitwick | 10-AUG-32 | Rubeus | Hagrid | 23-JUN-60 | Gilderoy | Lockhart | 10-FEB-40 | Sybill | Trelawney | 09-JUL-69 | Alecto | Carrow | 02-DEC-69 | Remus | Lupin | 07-OCT-62 | Horace | Slughorn | 17-SEP-62 | Severus | Snape | 14-MAY-58 | Poppy | Pomfrey | 19-JUL-52 | Master | Yeoda | 06-JUN-96 |
|---|
SELECT "Fname", "Lname", "Number", "Street", "City", "PostalCode"
FROM Student
JOIN Address
ON Student."StudentID" = Address."StudentID"| Fname | Lname | Number | Street | City | PostalCode | Draco | Malfoy | 23 | Apple St | Manchester | 123456789 | Luna | Lovegood | 456 | Orange St | Sunderland | 234557329 | Hermione | Granger | 78 | Grape Rd | London | 234252342 | Neville | Longbottom | 234 | Mango Blvd | Norwich | 234252525 | Harry | Potter | 45 | Cherry Rd | Anfield | 235254534 | Ron | Weasley | 985A | Banana Ave | London | 255423645 | Ginny | Weasley | 35 | Pear Ln | London | 346347564 | Lavender | Brown | 235 | Pineapple Rd | Portsmouth | 736495730 | Seamus | Finnigan | 642 | Strawberry Ln | London | 726354859 | Cho | Chang | 24 | Blueberry Ave | Burnley | 825485029 | Cedric | Diggory | 894 | Blackberry Rd | Brighton | 245826940 |
|---|
SELECT "Fname", "Lname", "EmailID"
FROM Student
JOIN Email
ON Student."StudentID" = Email."StudentID"| Fname | Lname | EmailID | Draco | Malfoy | Malfoy-Drago@hogwarts.edu | Luna | Lovegood | Lovegood-Luna@hogwarts.edu | Hermione | Granger | Granger-Hermione@hogwarts.edu | Neville | Longbottom | Longbottom-Neville@hogwarts.edu | Harry | Potter | Potter-Harry@hogwarts.edu | Ron | Weasley | Weasley-Ron@hogwarts.edu | Ginny | Weasley | Weasley-Ginny@hogwarts.edu | Lavender | Brown | Lavender-Brown@hogwarts.edu | Seamus | Finnigan | Seamus-Finnigan@hogwarts.edu | Cho | Chang | Cho-Chang@hogwarts.edu | Cedric | Diggory | Cedric-Diggory@hogwarts.edu |
|---|
SELECT "Fname", "Lname", "WandCore"
FROM Student
JOIN Wand
ON Student."StudentID" = Wand."StudentID"| Fname | Lname | WandCore | Draco | Malfoy | Unicorn Hair | Luna | Lovegood | Veela Hair | Hermione | Granger | Dragon Heartstring | Neville | Longbottom | Unicorn Hair | Harry | Potter | Phoenix Feather | Ron | Weasley | Broken Stuff | Ginny | Weasley | Veela Hair | Lavender | Brown | Unknown | Seamus | Finnigan | Unknown | Cho | Chang | Unknown | Cedric | Diggory | Unicorn hair |
|---|
SELECT "InstructorFName", "InstructorLName", "House"
FROM HogwartsHouse
JOIN Instructor
ON HogwartsHouse."HouseID" = Instructor."HouseID"
WHERE Instructor."InstructorFName" LIKE 'S%'| InstructorFName | InstructorLName | House | Serius | Black | Gryffindor | Sybill | Trelawney | Ravenclaw | Severus | Snape | Slytherin |
|---|
SELECT "InstructorFName", "InstructorLName", "DoB"
FROM Instructor
JOIN DateOfBirth
ON Instructor."InstructorID" = DateOfBirth."InstructorID"
WHERE Instructor."InstructorLName" LIKE '%o%'
ORDER BY Instructor."InstructorLName" DESC| InstructorFName | InstructorLName | DoB | Master | Yeoda | 06-JUN-96 | Horace | Slughorn | 17-SEP-62 | Poppy | Pomfrey | 19-JUL-52 | Gilderoy | Lockhart | 10-FEB-40 | Albus | Dumbledore | 29-APR-31 | Alecto | Carrow | 02-DEC-69 |
|---|
SELECT "Fname", "Lname", "Number", "Street", "City", "PostalCode"
FROM Student
JOIN Address
ON Student."StudentID" = Address."StudentID"
WHERE Address."City" = 'Portsmouth'| Fname | Lname | Number | Street | City | PostalCode | Lavender | Brown | 235 | Pineapple Rd | Portsmouth | 736495730 |
|---|
SELECT "Fname", "Lname", "EmailID"
FROM Student
JOIN Email
ON Student."StudentID" = Email."StudentID"
WHERE Student."Fname" LIKE '%y' OR Student."Lname" LIKE '%y'| Fname | Lname | EmailID | Draco | Malfoy | Malfoy-Drago@hogwarts.edu | Harry | Potter | Potter-Harry@hogwarts.edu | Ron | Weasley | Weasley-Ron@hogwarts.edu | Ginny | Weasley | Weasley-Ginny@hogwarts.edu | Cedric | Diggory | Cedric-Diggory@hogwarts.edu |
|---|
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 '_____'| InstructorFName | InstructorLName | Pay | Remus | Lupin | 3500 | Severus | Snape | 1000 | Master | Yeoda | 888 |
|---|
SELECT "Fname", "Lname", "MealPlanIDNo"
FROM Student
LEFT JOIN MealPlan
ON Student."StudentID" = MealPlan."StudentID"| Fname | Lname | MealPlanIDNo | Draco | Malfoy | ABC123 | Luna | Lovegood | DEF456 | Hermione | Granger | GHI789 | Neville | Longbottom | JKL123 | Harry | Potter | MNO456 | Ron | Weasley | PQR789 | Ginny | Weasley | STU123 | Draco | Malfoy | VWX456 | Luna | Lovegood | YYZ789 | Neville | Longbottom | ABC789 | Ginny | Weasley | DEF000 | Cedric | Diggory | - | Cho | Chang | - | Lavender | Brown | - | Seamus | Finnigan | - |
|---|
SELECT "Fname", "Lname", "MealPlanIDNo"
FROM Student
RIGHT JOIN MealPlan
ON Student."StudentID" = MealPlan."StudentID"
WHERE Student."StudentID" IS NULLno data found
SELECT COUNT("ClassID")
FROM Class| COUNT("CLASSID") | 11 |
|---|
SELECT COUNT("ClassID")
FROM Class
WHERE "Term" = 'Spring'| COUNT("CLASSID") | 3 |
|---|
SELECT COUNT("ClassID")
FROM Class
WHERE "Term" = 'Spring'
AND "Seats" >=20| COUNT("CLASSID") | 2 |
|---|
SELECT COUNT("InstructorID")
FROM Salary
WHERE "Pay" <1500| COUNT("INSTRUCTORID") | 5 |
|---|
SELECT COUNT("InstructorID")
FROM Salary
WHERE "Pay" + "Bonus" <1500| COUNT("INSTRUCTORID") | 4 |
|---|
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| COUNT(INSTRUCTOR."INSTRUCTORID") | 7 |
|---|
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| COUNT(CLASS."CLASSID") | 3 |
|---|
SELECT MAX("Pay")
FROM Salary| MAX("PAY") | 3600 |
|---|
SELECT MAX("Pay") AS "HIGHEST PAY"
FROM Salary| HIGHEST PAY | 3600 |
|---|
SELECT MAX("Pay") AS "HIGHEST PAY", MAX("Bonus") AS "HIGHEST BONUS"
FROM Salary| HIGHEST PAY | HIGHEST BONUS | 3600 | 900 |
|---|
SELECT MIN("Pay") AS "LOWEST PAY", MIN("Bonus") AS "LOWEST BONUS"
FROM Salary| LOWEST PAY | LOWEST BONUS | 888 | 88 |
|---|
SELECT "InstructorFName", "InstructorLName", "Pay"
FROM Instructor
JOIN Salary
ON Instructor."InstructorID" = Salary."InstructorID"
WHERE "Pay" = (SELECT MAX("Pay") FROM Salary)| InstructorFName | InstructorLName | Pay | Horace | Slughorn | 3600 |
|---|
SELECT "InstructorFName", "InstructorLName", "Bonus"
FROM Instructor
JOIN Salary
ON Instructor."InstructorID" = Salary."InstructorID"
WHERE "Bonus" = (SELECT MIN("Bonus") FROM Salary)| InstructorFName | InstructorLName | Bonus | Master | Yeoda | 88 |
|---|
SELECT "ClassTitle", "Seats"
FROM Class
WHERE "Seats" = (SELECT MAX("Seats") FROM Class)| ClassTitle | Seats | Care for Magical Creatures | 35 |
|---|
SELECT "ClassTitle", "Seats"
FROM Class
WHERE "Seats" = (SELECT MAX("Seats") FROM Class)| ClassTitle | Seats | Care for Magical Creatures | 35 |
|---|
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)| ClassTitle | Seats | InstructorFName | InstructorLName | The Force | 0 | Master | Yeoda |
|---|
SELECT "Term", SUM("Seats") AS "TOTAL SEATS AVAILABLE"
FROM Class
GROUP BY "Term"| Term | TOTAL SEATS AVAILABLE | Fall | 85 | Winter | 0 | Summer | 65 | Spring | 75 |
|---|
SELECT "Term", AVG("Seats") AS "AVERAGE SEATS AVAILABLE"
FROM Class
GROUP BY "Term"| Term | AVERAGE SEATS AVAILABLE | Fall | 28.33333333333333333333333333333333333333 | Winter | 0 | Summer | 16.25 | Spring | 25 |
|---|
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| House | AVERAGE PAY | Slytherin | 2466.666666666666666666666666666666666667 | Ravenclaw | 2100 | Gryffindor | 2100 | Hufflepuff | 1200 | Jedi | 888 |
|---|
SELECT "Term", SUM("Seats") AS "TOTAL SEATS AVAILABLE"
FROM Class
GROUP BY "Term"
HAVING SUM("Seats") >60| Term | TOTAL SEATS AVAILABLE | Fall | 85 | Summer | 65 | Spring | 75 |
|---|
SELECT "Term", AVG("Seats") AS "AVERAGE SEATS AVAILABLE"
FROM Class
GROUP BY "Term"
HAVING AVG("Seats") >20
ORDER BY AVG("Seats")| Term | AVERAGE SEATS AVAILABLE | Spring | 25 | Fall | 28.33333333333333333333333333333333333333 |
|---|
SELECT "Fname", "Lname", "ClassTitle"
FROM Student
JOIN StudentClass
ON Student."StudentID" = StudentClass."StudentID"
JOIN Class ON StudentClass."ClassID" = Class."ClassID"| Fname | Lname | ClassTitle | Luna | Lovegood | Potions | Harry | Potter | Potions | Ron | Weasley | Potions | Luna | Lovegood | Defence against the dark arts | Hermione | Granger | Defence against the dark arts | Seamus | Finnigan | Defence against the dark arts | Harry | Potter | Defence against the dark arts | Lavender | Brown | Defence against the dark arts | Cho | Chang | Defence against the dark arts | Harry | Potter | Divination | Ron | Weasley | Divination | Draco | Malfoy | Care for Magical Creatures | Ron | Weasley | Care for Magical Creatures | Cedric | Diggory | Care for Magical Creatures | Luna | Lovegood | The Force |
|---|
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"| Fname | Lname | ClassTitle | InstructorLName | Term | Cedric | Diggory | Care for Magical Creatures | Centaur | Spring | Cho | Chang | Defence against the dark arts | Dumbledore | Fall | Draco | Malfoy | Care for Magical Creatures | Centaur | Spring | Harry | Potter | Defence against the dark arts | Dumbledore | Fall | Harry | Potter | Potions | Snape | Fall | Hermione | Granger | Defence against the dark arts | Dumbledore | Fall | Lavender | Brown | Defence against the dark arts | Dumbledore | Fall | Luna | Lovegood | Potions | Snape | Fall | Luna | Lovegood | Defence against the dark arts | Dumbledore | Fall | Luna | Lovegood | The Force | Yeoda | Winter | Ron | Weasley | Potions | Snape | Fall | Ron | Weasley | Care for Magical Creatures | Centaur | Spring | Seamus | Finnigan | Defence against the dark arts | Dumbledore | Fall |
|---|
SELECT "Fname", "Lname", "House", "WandCore"
FROM Student
JOIN Wand
ON Student."StudentID" = Wand."StudentID"
JOIN HogwartsHouse
ON Student."HouseID" = HogwartsHouse."HouseID"| Fname | Lname | House | WandCore | Draco | Malfoy | Slytherin | Unicorn Hair | Luna | Lovegood | Ravenclaw | Veela Hair | Hermione | Granger | Gryffindor | Dragon Heartstring | Neville | Longbottom | Gryffindor | Unicorn Hair | Harry | Potter | Gryffindor | Phoenix Feather | Ron | Weasley | Gryffindor | Broken Stuff | Ginny | Weasley | Gryffindor | Veela Hair | Lavender | Brown | Gryffindor | Unknown | Seamus | Finnigan | Gryffindor | Unknown | Cho | Chang | Ravenclaw | Unknown | Cedric | Diggory | Hufflepuff | Unicorn hair |
|---|
SELECT "InstructorFName", "InstructorLName", "ClassTitle" AS Training, "Term", "Seats"
FROM Instructor
JOIN InstructorClass
ON Instructor."InstructorID" = InstructorClass."InstructorID"
JOIN Class ON InstructorClass."ClassID" = Class."ClassID"| InstructorFName | InstructorLName | TRAINING | Term | Seats | Albus | Dumbledore | Defence against the dark arts | Fall | 25 | Firenze | Centaur | Care for Magical Creatures | Spring | 35 | Filius | Flitwick | Herbology | Spring | 15 | Gilderoy | Lockhart | Apparition | Summer | 10 | Alecto | Carrow | Flying | Summer | 30 | Severus | Snape | Potions | Fall | 30 | Severus | Snape | Quiddich | Spring | 25 | Master | Yeoda | The Force | Winter | 0 |
|---|
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| InstructorFName | InstructorLName | ClassTitle | Horace | Slughorn | - | Serius | Black | - | Poppy | Pomfrey | - | Sybill | Trelawney | - | Rubeus | Hagrid | - | Remus | Lupin | - | Dolores | Umbridge | - |
|---|
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| InstructorFName | InstructorLName | ClassTitle | Master | Yeoda | The Force | Dolores | Umbridge | - | Sybill | Trelawney | - | Severus | Snape | Potions | Severus | Snape | Quiddich | Horace | Slughorn | - | Poppy | Pomfrey | - | Remus | Lupin | - | Gilderoy | Lockhart | Apparition | Rubeus | Hagrid | - | Filius | Flitwick | Herbology | Albus | Dumbledore | Defence against the dark arts | Firenze | Centaur | Care for Magical Creatures | Alecto | Carrow | Flying | Serius | Black | - | - | - | Charms | - | - | Divination | - | - | Muggle Studies |
|---|
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| ClassTitle | Term | InstructorLName | Divination | Fall | - | Charms | Summer | - | Muggle Studies | Summer | - |
|---|
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"| ClassTitle | Term | InstructorLName | Fname | Care for Magical Creatures | Spring | Centaur | Draco | Potions | Fall | Snape | Luna | Defence against the dark arts | Fall | Dumbledore | Luna | The Force | Winter | Yeoda | Luna | Defence against the dark arts | Fall | Dumbledore | Hermione | Potions | Fall | Snape | Harry | Defence against the dark arts | Fall | Dumbledore | Harry | Divination | Fall | - | Harry | Potions | Fall | Snape | Ron | Divination | Fall | - | Ron | Care for Magical Creatures | Spring | Centaur | Ron | Defence against the dark arts | Fall | Dumbledore | Lavender | Defence against the dark arts | Fall | Dumbledore | Seamus | Defence against the dark arts | Fall | Dumbledore | Cho | Care for Magical Creatures | Spring | Centaur | Cedric |
|---|
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| ClassTitle | Term | InstructorLName | Fname | Divination | Fall | - | Harry | Divination | Fall | - | Ron |
|---|
SELECT "InstructorFName", "InstructorLName", "House", "Pay", "Bonus"
FROM Instructor
JOIN Salary
ON Instructor."InstructorID" = Salary."InstructorID"
JOIN HogwartsHouse
ON Instructor."HouseID" = HogwartsHouse."HouseID"| InstructorFName | InstructorLName | House | Pay | Bonus | Albus | Dumbledore | Gryffindor | 1200 | 180 | Dolores | Umbridge | Ravenclaw | 1400 | 100 | Serius | Black | Gryffindor | 1600 | 200 | Filius | Flitwick | Ravenclaw | 2000 | 250 | Gilderoy | Lockhart | Ravenclaw | 2400 | 320 | Sybill | Trelawney | Ravenclaw | 2600 | 900 | Alecto | Carrow | Slytherin | 2800 | 380 | Remus | Lupin | Gryffindor | 3500 | 700 | Horace | Slughorn | Slytherin | 3600 | 360 | Severus | Snape | Slytherin | 1000 | 150 | Poppy | Pomfrey | Hufflepuff | 1200 | 200 | Master | Yeoda | Jedi | 888 | 88 |
|---|