Dude! Where's My Car?!

View All Public Scripts Login to Run Script

Script Name Dude! Where's My Car?!
Visibility Unlisted - anyone with the share link can access
Description Dude! Where's My Car?! Demo Runthrough
Contributor jedi
Created Sunday September 03, 2023

Statement 1

CREATE TABLE Permit 
("PermitNo" varchar2(10), "CarType" varchar2(20), "StudentID" varchar2(10), "ExpiryDate" date)

Table created.

Statement 2

INSERT INTO Permit 
VALUES ('P111', 'Toyota', 'ABC123456', TO_DATE('06-Jun-1966', 'DD-MM-YYYY'))

1 row(s) inserted.

Statement 3

INSERT INTO Permit 
VALUES ('P222', 'Honda', 'DEF456123', TO_DATE('08-Aug-1988','DD-MM-YYYY'))

1 row(s) inserted.

Statement 4

SELECT * FROM Permit


PermitNo CarType StudentID ExpiryDate
P111 Toyota ABC123456 06-JUN-66
P222 Honda DEF456123 08-AUG-88

2 rows selected.

PermitNo	CarType	StudentID	ExpiryDate
P111	Toyota	ABC123456	06-JUN-66
P222	Honda	DEF456123	08-AUG-88

Statement 5

CREATE TABLE Student 
("StudentID" varchar2(10) PRIMARY KEY, "StudentName" varchar2(50), "StudentEmail" varchar2(60))

Table created.

Statement 6

INSERT ALL 
INTO Student ("StudentID", "StudentName", "StudentEmail") 
VALUES ('ABC123456', 'Jesse', 'jesse@car.edu') 
INTO Student ("StudentID", "StudentName", "StudentEmail") 
VALUES ('DEF456123', 'Chester', 'chester@car.edu') 
INTO Student ("StudentID", "StudentName", "StudentEmail") 
VALUES ('GHJ456789', 'Wanda', 'wanda@car.edu') 
INTO Student ("StudentID", "StudentName", "StudentEmail") 
VALUES ('KLM789456', 'Wilma', 'wilma@car.edu') 
SELECT * FROM dual

4 row(s) inserted.

Statement 7

SELECT * FROM Student


StudentID StudentName StudentEmail
ABC123456 Jesse jesse@car.edu
DEF456123 Chester chester@car.edu
GHJ456789 Wanda wanda@car.edu
KLM789456 Wilma wilma@car.edu

4 rows selected.

StudentID	StudentName	StudentEmail
ABC123456	Jesse	jesse@car.edu
DEF456123	Chester	chester@car.edu
GHJ456789	Wanda	wanda@car.edu
KLM789456	Wilma	wilma@car.edu

Statement 8

CREATE TABLE Student_Class 
("StudentID_ClassNo" varchar2(50) PRIMARY KEY, "StudentID" varchar2(10), "ClassNo" varchar2(10))

Table created.

Statement 9

INSERT ALL  
INTO Student_Class ("StudentID_ClassNo", "StudentID", "ClassNo")  
VALUES ('ABC123456-OMBA5305', 'ABC123456', 'OMBA5305')  
INTO Student_Class ("StudentID_ClassNo", "StudentID", "ClassNo")  
VALUES ('ABC123456-IS5310', 'ABC123456', 'IS5310')  
INTO Student_Class ("StudentID_ClassNo", "StudentID", "ClassNo")  
VALUES ('GHJ456789-OMBA5305', 'GHJ456789', 'OMBA5305')  
INTO Student_Class ("StudentID_ClassNo", "StudentID", "ClassNo") 
VALUES ('GHJ456789-BRMB5240', 'GHJ456789', 'BRMB5240') 
SELECT * FROM dual

4 row(s) inserted.

Statement 10

SELECT * FROM Student_Class


StudentID_ClassNo StudentID ClassNo
ABC123456-OMBA5305 ABC123456 OMBA5305
ABC123456-IS5310 ABC123456 IS5310
GHJ456789-OMBA5305 GHJ456789 OMBA5305
GHJ456789-BRMB5240 GHJ456789 BRMB5240

4 rows selected.

StudentID_ClassNo	StudentID	ClassNo
ABC123456-OMBA5305	ABC123456	OMBA5305
ABC123456-IS5310	ABC123456	IS5310
GHJ456789-OMBA5305	GHJ456789	OMBA5305
GHJ456789-BRMB5240	GHJ456789	BRMB5240

Statement 11

CREATE TABLE Class 
("ClassNo" varchar2(8), "ClassName" varchar2(15), "InstructorID" varchar2(10), "ClassSize" int)

Table created.

Statement 12

INSERT ALL 
INTO Class ("ClassNo", "ClassName", "InstructorID", "ClassSize") 
VALUES ('OMBA5305', 'Best Class', 'Y666', 300) 
INTO Class ("ClassNo", "ClassName", "InstructorID", "ClassSize") 
VALUES ('IS5310', 'Fantastic Class', 'Y666', 250) 
INTO Class ("ClassNo", "ClassName", "InstructorID", "ClassSize") 
VALUES ('BRMB5240', 'Awesome Class', 'Y666', 250) 
SELECT * FROM dual

3 row(s) inserted.

Statement 13

SELECT * FROM Class


ClassNo ClassName InstructorID ClassSize
OMBA5305 Best Class Y666 300
IS5310 Fantastic Class Y666 250
BRMB5240 Awesome Class Y666 250

3 rows selected.

ClassNo	ClassName	InstructorID	ClassSize
OMBA5305	Best Class	Y666	300
IS5310	Fantastic Class	Y666	250
BRMB5240	Awesome Class	Y666	250

Statement 14

CREATE TABLE Instructor 
("InstructorID" varchar2(10) PRIMARY KEY, "InstructorName" varchar2(60), "Department" varchar2(20))

Table created.

Statement 15

INSERT INTO Instructor VALUES('Y666', 'yeoda', 'Management')

1 row(s) inserted.

Statement 16

INSERT INTO Instructor VALUES('J888', 'jedi', 'Management')

1 row(s) inserted.

Statement 17

SELECT * FROM Instructor


InstructorID InstructorName Department
Y666 yeoda Management
J888 jedi Management

2 rows selected.

InstructorID	InstructorName	Department
Y666	yeoda	Management
J888	jedi	Management

Statement 18

UPDATE Instructor 
SET "Department" = 'Force' 
WHERE "InstructorID" = 'J888'

1 row(s) updated.

Statement 19

SELECT * 
FROM Instructor


InstructorID InstructorName Department
Y666 yeoda Management
J888 jedi Force

2 rows selected.

InstructorID	InstructorName	Department
Y666	yeoda	Management
J888	jedi	Force

Statement 20

ALTER TABLE Instructor 
MODIFY "Department" varchar(100)

Table altered.

Statement 21

ALTER TABLE Student 
ADD ("Telephone" varchar(20))

Table altered.

Statement 22

SELECT * 
FROM Student


StudentID StudentName StudentEmail Telephone
ABC123456 Jesse jesse@car.edu  - 
DEF456123 Chester chester@car.edu  - 
GHJ456789 Wanda wanda@car.edu  - 
KLM789456 Wilma wilma@car.edu  - 

4 rows selected.

StudentID	StudentName	StudentEmail	Telephone
ABC123456	Jesse	jesse@car.edu	-
DEF456123	Chester	chester@car.edu	-
GHJ456789	Wanda	wanda@car.edu	-
KLM789456	Wilma	wilma@car.edu	-

Statement 23

ALTER TABLE Student 
DROP COLUMN "Telephone"

Table altered.

Statement 24

SELECT * 
FROM Student


StudentID StudentName StudentEmail
ABC123456 Jesse jesse@car.edu
DEF456123 Chester chester@car.edu
GHJ456789 Wanda wanda@car.edu
KLM789456 Wilma wilma@car.edu

4 rows selected.

StudentID	StudentName	StudentEmail
ABC123456	Jesse	jesse@car.edu
DEF456123	Chester	chester@car.edu
GHJ456789	Wanda	wanda@car.edu
KLM789456	Wilma	wilma@car.edu

Statement 25

ALTER TABLE Student 
ADD ("Telephone" varchar(20))

Table altered.

Statement 26

SELECT * FROM Student


StudentID StudentName StudentEmail Telephone
ABC123456 Jesse jesse@car.edu  - 
DEF456123 Chester chester@car.edu  - 
GHJ456789 Wanda wanda@car.edu  - 
KLM789456 Wilma wilma@car.edu  - 

4 rows selected.

StudentID	StudentName	StudentEmail	Telephone
ABC123456	Jesse	jesse@car.edu	-
DEF456123	Chester	chester@car.edu	-
GHJ456789	Wanda	wanda@car.edu	-
KLM789456	Wilma	wilma@car.edu	-

Statement 27

UPDATE Student 
SET "Telephone" = '1234567890' 
WHERE "StudentName" = 'Jesse'

1 row(s) updated.

Statement 28

SELECT * FROM Student


StudentID StudentName StudentEmail Telephone
ABC123456 Jesse jesse@car.edu 1234567890
DEF456123 Chester chester@car.edu  - 
GHJ456789 Wanda wanda@car.edu  - 
KLM789456 Wilma wilma@car.edu  - 

4 rows selected.

StudentID	StudentName	StudentEmail	Telephone
ABC123456	Jesse	jesse@car.edu	1234567890
DEF456123	Chester	chester@car.edu	-
GHJ456789	Wanda	wanda@car.edu	-
KLM789456	Wilma	wilma@car.edu	-

Statement 29

INSERT INTO Instructor VALUES('Z999', 'voldermort', 'Necromancy')

1 row(s) inserted.

Statement 30

SELECT * 
FROM Instructor


InstructorID InstructorName Department
Y666 yeoda Management
J888 jedi Force
Z999 voldermort Necromancy

3 rows selected.

InstructorID	InstructorName	Department
Y666	yeoda	Management
J888	jedi	Force
Z999	voldermort	Necromancy

Statement 31

DELETE 
FROM 
Instructor 
WHERE 
"InstructorID" = 'Z999' 
AND "Department" = 'Necromancy'

1 row(s) deleted.

Statement 32

SELECT * 
FROM Instructor


InstructorID InstructorName Department
Y666 yeoda Management
J888 jedi Force

2 rows selected.

InstructorID	InstructorName	Department
Y666	yeoda	Management
J888	jedi	Force

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database