Logo 
Search:

Oracle Articles

Submit Article
Home » Articles » Oracle » DatabaseRSS Feeds

Student Enrollment System Database

Posted By: Joel Evans     Category: Oracle     Views: 11341

Article that create Student Enrollment System database with alter, insert, select, update, delete etc...

Consider the following Student Enrollment System database.

students(student_id, student_name, address) 
enrollment(student_id, subject_id, mark) 
subject(subject_id, subject_name, department) 

Note : 
Marks can only be between 0-100.
Department can only be ‘Maths’ or “Computer Science”.
Subject_id starts with MCA letters, ex. MCA302, MCA505 etc.

Create student table 

CREATE TABLE STUDENTS
(
STUDENT_ID VARCHAR2(5) PRIMARY KEY,
STUDENT_NAME VARCHAR2(15),
        ADDRESS VARCHAR2(25) 
);

Create enrollment table 

CREATE TABLE ENROLMENT
(
STUDENT_ID VARCHAR2(5) REFERENCES STUDENTS(STUDENT_ID),
SUBJECT_ID VARCHAR2(6) REFERENCES SUBJECTS(SUBJECT_ID),
MARK NUMBER(3),
CHECK (MARK BETWEEN 0 AND 100),
        CHECK (SUBJECT_ID LIKE 'MCA___'),
        PRIMARY KEY(STUDENT_ID,SUBJECT_ID)
);

Create subject table

CREATE TABLE SUBJECTS
(
SUBJECT_ID VARCHAR2(6) PRIMARY KEY,
SUBJECT_NAME VARCHAR2(20),
DEPARTMENT VARCHAR2(20),
CHECK (DEPARTMENT IN('MATHS','COMPUTER SCIENCE')),
        CHECK (SUBJECT_ID LIKE 'MCA___')
);

Description / definition of students table

DESC STUDENTS;

 Name                                                  Null?    Type
 STUDENT_ID                                            NOT NULL VARCHAR2(5)
 STUDENT_NAME                                                   VARCHAR2(15)
 ADDRESS                                                        VARCHAR2(25)

Description / definition of enrollment table

DESC ENROLMENT;

 Name                                                  Null?    Type
 STUDENT_ID                                            NOT NULL VARCHAR2(5)
 SUBJECT_ID                                            NOT NULL VARCHAR2(6)
 MARK                                                           NUMBER(3)

Description / definition of subject table

DESC SUBJECTS;

 Name                                                  Null?    Type
 SUBJECT_ID                                            NOT NULL VARCHAR2(6)
 SUBJECT_NAME                                                   VARCHAR2(20)
 DEPARTMENT                                                     VARCHAR2(20)

Insert statements

INSERT STATEMENT FOR STUDENT TABLE

INSERT INTO STUDENTS (STUDENT_ID,STUDENT_NAME,ADDRESS) VALUES
          ('303','KRUNAL','MANINAGAR,AMD');    

INSERT INTO STUDENTS (STUDENT_ID,STUDENT_NAME,ADDRESS) VALUES
          ('301','BHAVESH','VADAJ,AMD');    

INSERT INTO STUDENTS (STUDENT_ID,STUDENT_NAME,ADDRESS) VALUES
          ('309','KINA','GANDHINAGAR');    

INSERT INTO STUDENTS (STUDENT_ID,STUDENT_NAME,ADDRESS) VALUES
          ('310','KRISHNA','GANDHINAGAR');    

INSERT INTO STUDENTS (STUDENT_ID,STUDENT_NAME,ADDRESS) VALUES
          ('311','RITA','GANDHINAGAR');    

INSERT STATEMENT FOR ENROLLMENT TABLE

INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
             ('303','MCA302',87);

INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
             ('303','MCA303',75);

INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
               ('303','MCA304',70);

INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
               ('303','MCA305',72);

INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
             ('301','MCA302',82);

INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
             ('301','MCA303',93);

INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
               ('301','MCA305',70);

INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
             ('309','MCA302',60);

INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
             ('309','MCA303',55);

INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
               ('309','MCA304',61);

INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
               ('309','MCA305',67);

INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
             ('310','MCA302',78);

INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
             ('310','MCA303',55);

INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
               ('310','MCA304',72);

INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
               ('310','MCA305',90);

INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
             ('311','MCA302',40);

INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
             ('311','MCA303',52);

INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
               ('311','MCA304',53);

INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
               ('311','MCA305',60);

INSERT STATEMENT FOR SUBJECTS TABLE

INSERT INTO SUBJECTS (SUBJECT_ID,SUBJECT_NAME,DEPARTMENT) VALUES
    ('MCA302','DISCREATE MATHS','MATHS'); 

INSERT INTO SUBJECTS (SUBJECT_ID,SUBJECT_NAME,DEPARTMENT) VALUES
    ('MCA303','Fundamental Of C','COMPUTER SCIENCE'); 

INSERT INTO SUBJECTS (SUBJECT_ID,SUBJECT_NAME,DEPARTMENT) VALUES
    ('MCA304','C++','COMPUTER SCIENCE'); 

INSERT INTO SUBJECTS (SUBJECT_ID,SUBJECT_NAME,DEPARTMENT) VALUES
    ('MCA305','VB','COMPUTER SCIENCE'); 

Display records of student table

SELECT * FROM STUDENTS;

Output : 

STUDE STUDENT_NAME    ADDRESS
303   KRUNAL          MANINAGAR,AMD
301   BHAVESH         VADAJ,AMD
309   KINA            GANDHINAGAR
310   KRISHNA         GANDHINAGAR
311   RITA            GANDHINAGAR

Display enrollments which starts with MCA letters, ex. MCA302, MCA505 etc and marks can only be between 0-100.

SELECT * FROM ENROLMENT;

Output :

STUDE SUBJEC      MARK
303   MCA302        87
303   MCA303        75
303   MCA304        70
303   MCA305        72
301   MCA302        82
301   MCA303        93
301   MCA305        70
309   MCA302        60
309   MCA303        55
309   MCA304        61
309   MCA305        67
310   MCA302        78
310   MCA303        55
310   MCA304        72
310   MCA305        90
311   MCA302        40
311   MCA303        52
311   MCA304        53
311   MCA305        60

Display subjects having department only ‘Maths’ or “Computer Science”.

SELECT * FROM SUBJECTS;

Output :

SUBJEC SUBJECT_NAME         DEPARTMENT
MCA302 DISCREATE MATHS      MATHS
MCA303 Fundamental Of C     COMPUTER SCIENCE
MCA304 C++                  COMPUTER SCIENCE
MCA305 VB                   COMPUTER SCIENCE

Find the names of students doing MCA302 

SELECT STUDENT_NAME FROM STUDENTS WHERE STUDENT_ID IN (SELECT STUDENT_ID FROM ENROLMENT WHERE SUBJECT_ID = 'MCA302');

Output : 

STUDENT_NAME
BHAVESH
KRUNAL
KINA
KRISHNA
RITA

Find student id's of students enrolled in MCA302 but not in MCA304. 

SELECT COUNT(STUDENT_ID),SUBJECT_ID FROM ENROLMENT GROUP BY SUBJECT_ID
HAVING COUNT(STUDENT_ID) < (SELECT COUNT(STUDENT_ID)  FROM STUDENTS);
  
Share: 


Didn't find what you were looking for? Find more on Student Enrollment System Database Or get search suggestion and latest updates.

Joel Evans
Joel Evans author of Student Enrollment System Database is from London, United Kingdom.
 
View All Articles

 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!