(
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