shell> sqlplus C##user_id/password@xe
SQL*Plus: Release 23.0.0.0.0 - Production on Fri Aug 9 15:22:18 2024
Version 23.4.0.24.05
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Last Successful login time: Fri Aug 09 2024 15:17:08 -05:00
Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> DROP TABLE take1;
Table dropped.
SQL> DROP TABLE course1;
Table dropped.
SQL> DROP TABLE student1;
Table dropped.
SQL> DROP SEQUENCE student1_seq;
Sequence dropped.
SQL> CREATE SEQUENCE student1_seq START WITH 1 INCREMENT BY 1 CACHE 100;
Sequence created.
SQL> CREATE TABLE student1 (
2 sid INTEGER PRIMARY KEY,
3 name VARCHAR(64) NOT NULL,
4 email VARCHAR(32) NOT NULL );
Table created.
SQL> INSERT INTO student1 VALUES( student1_seq.NEXTVAL,
2 'Super Mario', 'super.mario@und.edu' );
1 row created.
SQL> INSERT INTO student1 VALUES( student1_seq.NEXTVAL,
2 'Digi Mon', 'digi.mon@ndus.edu' );
1 row created.
SQL> INSERT INTO student1 VALUES( student1_seq.NEXTVAL,
2 'Poke Mon', 'poke.mon@und.edu' );
1 row created.
SQL> INSERT INTO student1 VALUES( student1_seq.NEXTVAL,
2 'Sponge Bob', 'sponge@gmail.com' );
1 row created.
SQL> CREATE TABLE course1 (
2 cid CHAR(12) PRIMARY KEY,
3 title VARCHAR(64) NOT NULL,
4 room VARCHAR(16) NOT NULL,
5 credit INTEGER NOT NULL );
Table created.
SQL> INSERT INTO course1 VALUES( 'CSCI260',
2 '.NET and World Wide Web Programming', 'CEC 205', 3 );
1 row created.
SQL> INSERT INTO course1 VALUES( 'DATA520',
2 'Databases', 'Upson II 265', 3 );
1 row created.
SQL> INSERT INTO course1 VALUES( 'DATA525',
2 'Data Engineering and Mining', 'Harrington 218', 3 );
1 row created.
SQL> INSERT INTO course1 VALUES( 'CSCI520',
2 'Advanced Databases', 'Harrington 364', 3 );
1 row created.
SQL> CREATE TABLE take1 (
2 sid INTEGER NOT NULL,
3 cid CHAR(12) NOT NULL,
4 semester VARCHAR(12) NOT NULL,
5 PRIMARY KEY ( sid, cid ),
6 FOREIGN KEY ( sid ) REFERENCES student1( sid ) ON DELETE CASCADE,
7 FOREIGN KEY ( cid ) REFERENCES course1( cid ) ON DELETE CASCADE );
Table created.
SQL> INSERT INTO take1 SELECT sid, 'DATA520', 'Fall 2022'
2 FROM student1 WHERE name='Poke Mon';
1 row created.
SQL> INSERT INTO take1 SELECT sid, 'DATA525', 'Fall 2022'
2 FROM student1 WHERE name='Poke Mon';
1 row created.
SQL> INSERT INTO take1 SELECT sid, 'CSCI260', 'Fall 2022'
2 FROM student1 WHERE name='Digi Mon';
1 row created.
SQL> INSERT INTO take1 SELECT sid, 'CSCI520', 'Spring 2023'
2 FROM student1 WHERE name='Sponge Bob';
1 row created.
SQL> SELECT s.name, c.title FROM student1 s, course1 c, take1 t
2 WHERE UPPER(s.name) LIKE '%MON%' AND s.sid=t.sid AND t.cid=c.cid;
NAME TITLE
----------------- ----------------------------------------
Digi Mon .NET and World Wide Web Programming
Poke Mon Databases
Poke Mon Data Engineering and Mining
SQL> COMMIT;
SQL> EXIT;
Disconnected from Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
shell>
|
|