my first database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

my first database

Author

Topic

disciple
Starting Member

27 Posts

Posted - 2006-03-26 : 02:03:11

This is my very first experience with sql, and its for a class. Im not sure what im doing wrong as I think it looks right, but here is the code:

DROP TABLE ENROLLMENT;
DROP TABLE COURSE_SECTION;
DROP TABLE COURSE;
DROP TABLE TERM;
DROP TABLE STUDENT;
DROP TABLE FACULTY;
DROP TABLE LOCATION;

CREATE TABLE LOCATION (
LOCID NUMBER(5),
BLDG_CODE VARCHAR(10) NOT NULL,
CAPACITY NUMBER(5),
ROOM NUMBER(5) NOT NULL,
PRIMARY KEY(LOCID));

CREATE TABLE FACULTY (
FID NUMBER(4),
FFNAME VARCHAR(25) NOT NULL,
FLNAME VARCHAR(25) NOT NULL,
FMI CHAR(1),
SPHONE NUMBER(10),
F_RANK VARCHAR(4) CHECK(F_RANK IN ('ASSO','FULL','ASST','ADJ')),
LOCID NUMBER(5),
F_PIN NUMBER(4),
PRIMARY KEY(FID),
FOREIGN KEY(LOCID) REFERENCES LOCATION);

CREATE TABLE STUDENT (
SID NUMBER(5),
SLNAME VARCHAR(25) NOT NULL,
SFNAME VARCHAR(25) NOT NULL,
SMI CHAR(1),
SADDR VARCHAR(30),
SCITY VARCHAR(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);

CREATE TABLE TERM (
TERMID NUMBER(5),
TDESC VARCHAR(20) NOT NULL,
STATUS VARCHAR(20) NOT NULL CHECK(STATUS IN ('OPEN','CLOSED')),

PRIMARY KEY(TERMID),
FOREIGN KEY(FID) REFERENCES FACULTY);

CREATE TABLE COURSE (
CID NUMBER(6),
CNAME VARCHAR(10) NOT NULL,
CALLID VARCHAR(30) NOT NULL,
CCREDIT NUMBER(2) DEAFAULT '3',

PRIMARY KEY(CID));

CREATE TABLE COURSE_SELECTION (
CSECID NUMBER(8),
CID NUMBER(6) NOT NULL,
DAY VARCHAR(10),
TERMID NUMBER(5) NOT NULL,
LOCID NUMBER(5),
CURRENRL NUMBER(4) NOT NULL,
MAXENRL NUMBER(4) NOT NULL,
FID NUMBER(4),
SECNUM NUMBER(2) NOT NULL,

PRIMARY KEY(CSECID),
FOREIGN KEY(CID) REFERENCES COURSE,
FOREIGN KEY(TERMID) REFERENCES TERM,
FOREIGN KEY(FID) REFERENCES FACULTY,
FOREIGN KEY(LOCID) REFERENCES LOCATION);

CREATE TABLE ENROLLMENT (
CSECID NUMBER(8),
SID NUMBER(5),
GRADE CHAR(1) CHECK(GRADE IN ('A','B','C','D','F',''I','W')),

PRIMARY KEY(SID),
PRIMARY KEY(CSECID),
FOREIGN KEY(SID) REFERENCES ENROLLMENT,
FOREIGN KEY(CSECID) REFERENCES COURSE_SELECTION);

INSERT INTO LOCATION
VALUES (53,'BUS','424',45);
INSERT INTO LOCATION
VALUES (54,'BUS','402',35);
INSERT INTO LOCATION
VALUES (55,'BUS','433',100);

SELECT * FROM LOCATION;

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);
SELECT * FROM FACULTY;

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);

SELECT * FROM STUDENT;

INSERT INTO TERM
VALUES (1,'Spring 2004','CLOSED');
INSERT INTO TERM
VALUES (2,'Summer 2004','OPEN');

SELECT * FROM TERM;

INSERT INTO COURSE
VALUES (1,'MIS101','Intro. to Info. Systems', 3);
INSERT INTO COURSE
VALUES (2,'Systems Analysis and Design',3);
INSERT INTO COURSE
VALUES (3,'Into to Database Management',3);

SELECT * FROM COURSE;

INSERT INTO COURSE_SELECTION
VALUES (1000,1,2,1,12,'MWF',55,100,35);
INSERT INTO COURSE_SELECTION
VALUES (1001,1,2,2,10,'TTH',54,45,35);
INSERT INTO COURSE_SELECTION
VALUES (1002,2,2,3,10,'MWF',53,35,32);
INSERT INTO COURSE_SELECTION
VALUES (1003,3,2,1,11,'TTH',54,45,35);

SELECT * FROM COURSE_SELECTION;

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');

SELECT * FROM ENROLLMENT;

We're using iSQL plus to learn on, and im getting alot of errors. ive already fixed a few, but here are the others.

DROP TABLE ENROLLMENT
*

ERROR at line 1:
ORA-00942: table or view does not exist

DROP TABLE COURSE_SECTION
*

ERROR at line 1:
ORA-00942: table or view does not exist

DROP TABLE COURSE
*

ERROR at line 1:
ORA-00942: table or view does not exist

DROP TABLE TERM
*

ERROR at line 1:
ORA-00942: table or view does not exist
Table dropped.

Table dropped.

Table dropped.

Table created.

Table created.

Table created.

FOREIGN KEY(FID) REFERENCES FACULTY)
*

ERROR at line 7:
ORA-00904: "FID": invalid identifier

CREATE TABLE COURSE (
*

ERROR at line 1:
ORA-00922: missing or invalid option

FOREIGN KEY(CID) REFERENCES COURSE,
*

ERROR at line 13:
ORA-00942: table or view does not exist

GRADE CHAR(1) CHECK(GRADE IN ('A','B','C','D','F',''I','W')),
*

ERROR at line 4:
ORA-00907: missing right parenthesis
1 row created.

1 row created.

1 row created.

LOCID BLDG_CODE CAPACITY ROOM
53 BUS 424 45
54 BUS 402 35
55 BUS 433 100

VALUES (10,'Cox','Kim','J', 53, 7155551234, 'ASSO',1181)
*

ERROR at line 2:
ORA-01401: inserted value too large for column

VALUES (11, 'Blanchard', 'Frank', 'R',54, 7155559087, 'FULL', 1075)
*

ERROR at line 2:
ORA-01401: inserted value too large for column

VALUES (12, 'McClure','William', 'L', 55, 7155556409, 'ADJ', 1690)
*

ERROR at line 2:
ORA-01401: inserted value too large for column

no rows selected

INSERT INTO STUDENT
*

ERROR at line 1:
ORA-02291: integrity constraint (D00741655.SYS_C00368419) violated - parent key not found

INSERT INTO STUDENT
*

ERROR at line 1:
ORA-02291: integrity constraint (D00741655.SYS_C00368419) violated - parent key not found
ERROR:
ORA-01756: quoted string not properly terminated

no rows selected

INSERT INTO TERM
*

ERROR at line 1:
ORA-00942: table or view does not exist

INSERT INTO TERM
*

ERROR at line 1:
ORA-00942: table or view does not exist

SELECT * FROM TERM
*

ERROR at line 1:
ORA-00942: table or view does not exist

INSERT INTO COURSE
*

ERROR at line 1:
ORA-00942: table or view does not exist

INSERT INTO COURSE
*

ERROR at line 1:
ORA-00942: table or view does not exist

INSERT INTO COURSE
*

ERROR at line 1:
ORA-00942: table or view does not exist

SELECT * FROM COURSE
*

ERROR at line 1:
ORA-00942: table or view does not exist

INSERT INTO COURSE_SELECTION
*

ERROR at line 1:
ORA-00942: table or view does not exist

INSERT INTO COURSE_SELECTION
*

ERROR at line 1:
ORA-00942: table or view does not exist

INSERT INTO COURSE_SELECTION
*

ERROR at line 1:
ORA-00942: table or view does not exist

INSERT INTO COURSE_SELECTION
*

ERROR at line 1:
ORA-00942: table or view does not exist

SELECT * FROM COURSE_SELECTION
*

ERROR at line 1:
ORA-00942: table or view does not exist

INSERT INTO ENROLLMENT
*

ERROR at line 1:
ORA-00942: table or view does not exist

INSERT INTO ENROLLMENT
*

ERROR at line 1:
ORA-00942: table or view does not exist

INSERT INTO ENROLLMENT
*

ERROR at line 1:
ORA-00942: table or view does not exist

INSERT INTO ENROLLMENT
*

ERROR at line 1:
ORA-00942: table or view does not exist

INSERT INTO ENROLLMENT
*

ERROR at line 1:
ORA-00942: table or view does not exist

INSERT INTO ENROLLMENT
*

ERROR at line 1:
ORA-00942: table or view does not exist

SELECT * FROM ENROLLMENT
*

ERROR at line 1:
ORA-00942: table or view does not exist

As you can see it only sucessfully creates one of the tables. Im not looking for someone to fix all of my mistakes, but maybe explain what im doing wrong. Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-26 : 02:12:33

This is a Microsoft SQL Server forum. You question is best posted in an Oracle Database forum.
Try
http://www.dbforums.com/
http://forums.oracle.com/

Anyway just few quick on one of the error

quote:
DROP TABLE ENROLLMENT
*
ERROR at line 1:
ORA-00942: table or view does not exist

You cannot drop a table if it is not exist.

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-03-26 : 02:26:57

I guess what you are trying to do is first to see if the table exists before dropping the table. In SQL server its more like this


if exists (--arguments go here)
-- if it does, then...
drop table
GO

nr
SQLTeam MVY

12543 Posts

Posted - 2006-03-26 : 09:15:35

in FACULTY
FOREIGN KEY(LOCID) REFERENCES LOCATION);

need to specify the column to reference
FOREIGN KEY (LOCID) REFERENCES LOCATION (LOCID));

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

disciple
Starting Member

27 Posts

Posted - 2006-03-26 : 11:07:30

Ah thankyou

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources