November 12, 2010 at 12:20 pm
I posted in a newbie thread on here but I guess I musted have posted in the wrong forum. I was having problems with compiling errors in Oracle Sql, but since then I think I have figured out what I did wrong. The only problems I am having is
1. When displaying the student table using SELECT * FROM STUDENT; the displayed table has data all over the place instead of in colums and rows.
2. How do I save my code to an .sql file. Everytime I save it it saves to a text file but the extention when I save is . sql.
Here is my code
DROP TABLE ENROLLMENT CASCADE CONSTRAINTS PURGE;
DROP TABLE COURSE_SECTION CASCADE CONSTRAINTS PURGE;
DROP TABLE COURSE CASCADE CONSTRAINTS PURGE;
DROP TABLE TERM CASCADE CONSTRAINTS PURGE;
DROP TABLE STUDENT CASCADE CONSTRAINTS PURGE;
DROP TABLE FACULTY CASCADE CONSTRAINTS PURGE;
DROP TABLE LOCATION CASCADE CONSTRAINTS PURGE;
CREATE TABLE LOCATION(
LOCID NUMBER(5) NOT NULL,
BLDG_CODE VARCHAR2(10) NOT NULL,
ROOM VARCHAR2(6) NOT NULL,
CAPACITY NUMBER(5),
PRIMARY KEY (LOCID));
CREATE TABLE FACULTY(
FID NUMBER(4) NOT NULL,
FLNAME VARCHAR2(25) NOT NULL,
FFNAME VARCHAR2(25) NOT NULL,
FMI CHAR(1),
LOCID NUMBER(5) NOT NULL,
FPHONE NUMBER(10),
F_RANK VARCHAR2(4) CHECK(F_RANK IN ('ASSO','FULL','ASST','ADJ')),
F_PIN NUMBER(4),
PRIMARY KEY(FID),
FOREIGN KEY(LOCID) REFERENCES LOCATION(LOCID));
CREATE TABLE STUDENT(
SID NUMBER(4) NOT NULL,
SLNAME VARCHAR2(25) NOT NULL,
SFNAME VARCHAR(25) NOT NULL,
SMI CHAR(1),
SADDR VARCHAR2(30),
SCITY VARCHAR2(30),
SSTATE CHAR(2) DEFAULT'WI',
SZIP NUMBER(9),
SPHONE NUMBER(10) NOT NULL,
SCLASS CHAR(2) DEFAULT'FR' CHECK(SCLASS IN('FR','SO','JR','SR','GR')),
SDOB DATE NOT NULL,
S_PIN NUMBER(4) NOT NULL,
FID NUMBER(4),
PRIMARY KEY (SID),
FOREIGN KEY (FID) REFERENCES FACULTY(FID));
CREATE TABLE TERM(
TERMID NUMBER(5) NOT NULL,
TDESC VARCHAR2(20) NOT NULL,
STATUS VARCHAR2(20) NOT NULL CHECK(STATUS IN ('OPEN','CLOSED')),
PRIMARY KEY (TERMID));
CREATE TABLE COURSE(
CID NUMBER(6) NOT NULL,
CALLID VARCHAR2(10) NOT NULL,
CNAME VARCHAR2(30) NOT NULL,
CCREDIT NUMBER(2) DEFAULT'3',
PRIMARY KEY (CID));
CREATE TABLE COURSE_SECTION(
CSECID NUMBER(8) NOT NULL,
CID NUMBER(6) NOT NULL,
TERMID NUMBER(5) NOT NULL,
SECNUM NUMBER(2) NOT NULL,
FID NUMBER(4),
DAY VARCHAR2(10),
LOCID NUMBER(5),
MAXENRL NUMBER(4) NOT NULL,
CURRENRL NUMBER(4) NOT NULL,
PRIMARY KEY (CSECID),
FOREIGN KEY (CID) REFERENCES COURSE(CID),
FOREIGN KEY (TERMID) REFERENCES TERM(TERMID),
FOREIGN KEY (FID) REFERENCES FACULTY(FID),
FOREIGN KEY (LOCID) REFERENCES LOCATION(LOCID));
CREATE TABLE ENROLLMENT(
SID NUMBER(5) NOT NULL,
CSECID NUMBER(8) NOT NULL,
GRADE CHAR(1) CHECK(GRADE IN ('A','B','C','D','F','I','W')),
FOREIGN KEY (SID) REFERENCES STUDENT(SID),
FOREIGN KEY (CSECID) REFERENCES COURSE_SECTION(CSECID));
INSERT INTO LOCATION VALUES (53, 'BUS', 424, 45);
INSERT INTO LOCATION VALUES (54, 'BUS', 402, 35);
INSERT INTO LOCATION VALUES (55, 'BUS', 433, 100);
INSERT INTO FACULTY VALUES (10, 'Cox', 'Kim', 'J', 53, 7155551234, 'ASSO', 1181);
INSERT INTO FACULTY VALUES (11, 'Blanchard', 'Frank', 'R', 54, 7155559087, 'FULL', 1075);
INSERT INTO FACULTY VALUES (12, 'McClure', 'William', 'L', 55, 7155556409, 'ADJ', 1690);
INSERT INTO STUDENT VALUES (100, 'McClure', 'Sarah', 'M', '144 Windridge Blvd.', 'Eau Claire', 'WI', 54703, 7155559876, 'SR', '14-JUL-1979', 8891, 10);
INSERT INTO STUDENT VALUES (101, 'Bowie', 'Jim', 'D', '454 St. John Street', 'Eau Claire', 'WI', 54702, 7155552345, 'SR', '19-AUG-1979', 1230, 11);
INSERT INTO STUDENT VALUES (102, 'Boone', 'Daniel', NULL, '8921 Circle Drive', 'Bloomer', 'WI', 54715, 7155553907, 'JR',
'10-OCT-1977', 1613, 11);
INSERT INTO TERM VALUES (1, 'Spring 2004', 'CLOSED');
INSERT INTO TERM VALUES (2, 'Summer 2004', 'OPEN');
INSERT INTO COURSE VALUES (1, 'MIS101', 'Intro. to Info. Systems', 3);
INSERT INTO COURSE VALUES (2, 'MIS321', 'Systems Analysis and Design', 3);
INSERT INTO COURSE VALUES (3, 'MIS349', 'Intro to Database Management', 3);
INSERT INTO COURSE_SECTION VALUES (1000, 1, 2, 1, 12, 'MWF', 55, 100, 35);
INSERT INTO COURSE_SECTION VALUES (1001, 1, 2, 2, 10, 'TTH', 54, 45, 35);
INSERT INTO COURSE_SECTION VALUES (1002, 2, 2, 3, 10, 'MWF', 53, 35, 32);
INSERT INTO COURSE_SECTION VALUES (1003, 3, 2, 1, 11, 'TTH', 54, 45, 35);
INSERT INTO ENROLLMENT VALUES (100, 1000, 'A');
INSERT INTO ENROLLMENT VALUES (100, 1003, 'A');
INSERT INTO ENROLLMENT VALUES (101, 1000, 'C');
INSERT INTO ENROLLMENT VALUES (102, 1000, 'C');
INSERT INTO ENROLLMENT VALUES (102, 1001, NULL);
INSERT INTO ENROLLMENT VALUES (102, 1003, 'I');
Can't figure out where I went wrong. Oh almost forgot can someone explain the SPOOL command to me as I am not understanding how to use it and when to turn it off. Thanks in advance
November 13, 2010 at 6:08 am
bickling77 (11/12/2010)
bickling77 (11/12/2010)
1. When displaying the student table using SELECT * FROM STUDENT; the displayed table has data all over the place instead of in colums and rows.
Prove it 😀
SQLplus will always return rows and columns, may be the problem is that the size of STUDENTS row exceeds the size of your SQLplus window.
Try to add -at the beggining of your SQLplus code...
set linesize 180 pagesize 50
then do a select of just three columns from STUDENT just to check how it looks.
bickling77 (11/12/2010)
2. How do I save my code to an .sql file. Everytime I save it it saves to a text file but the extention when I save is . sql.
Use your editor 😀
Is this any Unix flavor?
Using VI you name your file like... vi mysqlcode.sql
then you save it like... :wq
Any other editor will allow to do the same in one form or another.
bickling77 (11/12/2010)
Can't figure out where I went wrong. Oh almost forgot can someone explain the SPOOL command to me as I am not understanding how to use it and when to turn it off.
spool will spool out whatever is returned by the query, like...
set linesize 180 pagesize 50
spool mysqlcode.out
select SID, SLNAME from students;
spool off
Assuming you have saved this piece of code as mysqlcode.sql you can execute like...
set your ORACLE_HOME and ORACLE_SID properly
sqlplus /
@mysqlcode.sql
exit
Then just look for mysqlcode.out file in same directory where you executed the query.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 2, 2010 at 2:05 am
What is your sql file name?
Is it mycode.sql or mycode.sql.txt?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply