Logo 
Search:

Oracle Articles

Submit Article
Home » Articles » Oracle » DatabaseRSS Feeds

Student-Exam database

Posted By: Callum Evans     Category: Oracle     Views: 8918

Article that create Student-Exam database with insert, select, update, delete etc...

Database tables 

Student (rollNo, name, degreeName)
Exam (ExamId, courseNo, courseName, examDate)
Appeared (ExamId, rollNo)
 

Create appropriate primary key and foreign key referential integrity constraints degreeName can be only bca,mca, courseNo < 100, examDate > 1-jan-2000.

Student table creation

create table stud
(
  roll number(2) PRIMARY KEY,
  name varchar2(20),
  degree varchar2(10) CHECK(degree in('MCA','BCA'))
)

Exam table creation

CREATE TABLE  EXAM
(
    EXAMID  NUMBER(2) PRIMARY KEY,
    COURSENO  NUMBER(3) CHECK(COURSENO<100),
    COURSENAME  VARCHAR2(20),
    EDATE  DATE  CHECK('EDATE' >'1-JAN-00')
)

Appeared table

create table app
 (
    examid number(2) REFERENCES exam,
    rollno number(2) REFERENCES stud
 )

Insert statements

INSERT STATEMENT FOR STUDENT TABLE USING VARIABLE 

INSERT INTO STUD VALUES(&ROLL,'&NAME','&DEGREE')

INSERT STATEMENT FOR EXAM

INSERT INTO EXAM VALUES(&EXAMID,&COURSENO,'&COURSENAME','&EDATE')

INSERT STATEMENT FOR APP

INSERT INTO APP VALUES(&EXAMID,&ROLLNO)


List the students studying computer Engineering Degree using select statement

SELECT * FROM STUD WHERE DEGREE='MCA';

Output :
ROLL   NAME DEGREE 1        DAPS  MCA 2        SHALIN MCA 4        SWETA MCA 6        KK MCA

List the Number of students studying in each Degree using select statement

SELECT COUNT(DEGREE)"COUNT OF DEGREE",DEGREE
FROM STUD GROUP BY DEGREE

Output :

COUNT OF DEGREE   DEGREE
                                 2   BCA
                                 4   MCA

List courses of which the examination conducted on 1-1-2000

SELECT  COURSENAME FROM EXAM WHERE EDATE='12-FEB-01'

Output :

COURSENAME
CPROG

List the courses in which no student has appeared.(sub-query).

SELECT DISTINCT EXAM.COURSENAME FROM EXAM,APP WHERE EXAM.EXAMID NOT IN(SELECT APP.EXAMID FROM APP) COURSENAME JAVA

Change the name of the course "C programming" to "Programming in C" using update statement

UPDATE EXAM SET COURSENAME='PROGC'WHERE COURSENAME='CPROG'; SELECT * FROM EXAM;
Output : 
EXAMID COURSENO COURSENAME EDATE    1 1                     DCA 10-JAN-00    2 2                     PROGC 12-FEB-01    3 3                     JAVA 20-SEP-02    4 3                     C++  12-DEC-03

Display date wise information of examination using select statement and order by clause

SELECT * FROM EXAM ORDER BY EDATE;
Output :
EXAMID COURSENO    COURSENAME EDATE 1 1                 DCA     10-JAN-00 2 2                 PROGC  12-FEB-01 3 3                 JAVA     20-SEP-02 4 3                 C++      12-DEC-03
  
Share: 


Didn't find what you were looking for? Find more on Student-Exam database Or get search suggestion and latest updates.

Callum Evans
Callum Evans author of Student-Exam 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!