can someone help me in sql I\'m using orcale sql plus 1. Create the following ta

Question

can someone help me in sql I'm using orcale sql plus

1. Create the following tables.

Student

Field Name Data Type Comments

stdNo CHAR(5) This is the Primary Key

lastname VARCHAR(25) Must be Not Null

givennames VARCHAR(50) Must be Not Null

Dept CHAR(4)

Course

Field Name Data Type Comments

courseID CHAR(8) This is the Primary Key

courseTitle VARCHAR(50) Must be Unique and Not Null

Cost DECIMAL(6,2) Ensure Cost is greater or equal to zero

Credits INT Ensure that Credits are between 0 and 200. Also the

Default value is 2

Semester

Field Name Data Type Comments

semesterID CHAR(5) This is the Primary Key

semesterCode INT Ensure that semesterCode is Between 1 and 4

Year INT Ensure that year is Between 2000 and 9999

Register

Field Name Data Type Comments

stdNo CHAR(5) Foreign Key referenced to stdNo in Student table.

On update cascade On delete cascade

courseID CHAR(8) Foreign Key referenced to courseID in Course table.

On update cascade On delete cascade

semesterID CHAR(5) Foreign Key referenced to semesterID in Semester table.

On update cascade On delete cascade

Grade CHAR(2)

Mark DECIMAL(4,2) Mark should be between 0.00 and 100.00

Primary Key (stdNo, courseID, semesterID)

................................

here in sql (I'm using orcale sql plus) .. table STD , SEMESTER works with me

, but table COURSE I did not know how to put default value is 2 and table REGISTER dose not work with me at all :(

CREATE TABLE STD (
STDNO CHAR(5) PRIMARY KEY,
LASTNAME VARCHAR(25) NOT NULL,
GIVENNAME VARCHAR(50) NOT NULL,
DEPT CHAR(4)
);

CREATE TABLE SEMESTER (
SEMESTERID CHAR(5) PRIMARY KEY,
SEMESTERCODE INT CHECK(SEMESTERCODE BETWEEN 1 AND 4),
YEARS INT CHECK(YEARS BETWEEN 2000 AND 9999)
);

CREATE TABLE COURSE (
COURSEID CHAR(8) PRIMARY KEY,
COURSETITLE VARCHAR(50) NOT NULL UNIQUE,
COST DECIMAL(6,2) CHECK(COST >= 0),
CREDITS INT CHECK(CREDITS BETWEEN 0 AND 200)
);

CREATE TABLE REGISTER (
STDNO CHAR(5)FOREIGN KEY REFERENCES STD(STDNO) ON UPDATE CASCADE ON DELETE CASCADE,
COURSEID CHAR(5) FOREIGN KEY REFERENCES COURSE(COURSEID) ON UPDATE CASCADE ON DELETE CASCADE,
SEMESTERID CHAR(5)FOREIGN KEY REFERENCES SEMESTER(SEMESTERID) ON UPDATE CASCADE ON DELETE CASCADE,
GRADE CHAR(2),
MARK DECIMAL(4,2) CHECK(MARK BETWEEN 0.00 AND 100.0),

PRIMARYKEY(STDNO,COURSEID,SEMESTERID)
);

Explanation / Answer

CREATE TABLE STD (
STDNO CHAR(5) PRIMARY KEY,
LASTNAME VARCHAR(25) NOT NULL,
GIVENNAME VARCHAR(50) NOT NULL,
DEPT CHAR(4)
);
CREATE TABLE SEMESTER (
SEMESTERID CHAR(5) PRIMARY KEY,
SEMESTERCODE INT CHECK(SEMESTERCODE BETWEEN 1 AND 4),
YEARS INT CHECK(YEARS BETWEEN 2000 AND 9999)
);

CREATE TABLE COURSE (
COURSEID CHAR(8) PRIMARY KEY,
COURSETITLE VARCHAR(50) NOT NULL UNIQUE,
COST DECIMAL(6,2) CHECK(COST >= 0),
CREDITS INT CHECK(CREDITS BETWEEN 0 AND 200) DEFAULT 2
);
CREATE TABLE REGISTER (
STDNO CHAR(5)FOREIGN KEY REFERENCES STD(STDNO) ON UPDATE CASCADE ON DELETE CASCADE,
COURSEID CHAR(8) FOREIGN KEY REFERENCES COURSE(COURSEID) ON UPDATE CASCADE ON DELETE CASCADE,
SEMESTERID CHAR(5)FOREIGN KEY REFERENCES SEMESTER(SEMESTERID) ON UPDATE CASCADE ON DELETE CASCADE,
GRADE CHAR(2),
MARK DECIMAL(4,2) CHECK(MARK BETWEEN 0.00 AND 100.00),
PRIMARYKEY(STDNO,COURSEID,SEMESTERID)
);

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.