undcemcs02> source ~/.profile
undcemcs02> sqlplus C##user.id/password@//65.52.222.73:1521/cdb1
SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 23 17:37:07 2023
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Mon Aug 15 2022 14:55:00 - 05:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
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( 'CSCI513',
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, 'CSCI513', '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 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
undcemcs02>
|
|
