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