ASSIGNMENT 2
DDL
VVP
ENGINEERING
COLLEGE
CE DEPARTMENT
SUBMITTED BY: AAJUGIYA RAVI
G3
200470107072
Assignment 2
Assignment-2
1) Create table Student_master having following column
Enrollmentno,rollno, mobileno,current_sem,branch,
name,sem_result,batch
- define enrollment no as primary key
- current_sem must be between 1 to 10
- branch must not null
- name must not null
ANS:
CREATE TABLE STUDENT_MASTER
(
ENROLLMENTNO NUMBER(12) PRIMARY KEY,
ROLLNO VARCHAR2(10) NOT NULL,
NAME VARCHAR2(20) NOT NULL,
MOBILENO NUMBER(10) NOT NULL,
CURRENT_SEM NUMBER(2) DEFAULT 1 CHECK(CURRENT_SEM>0
OR CURRENT_SEM<10),
BRANCH CHAR(2) NOT NULL,
SEM_RESULT CHAR(5) CHECK(SEM_RESULT='PASS' OR
SEM_RESULT='FAIL'),
BATCH CHAR(2) NOT NULL
)
200470107072 DDL 1
Assignment 2
2) create subject_master having following column
subject_code,subject_name,branch,semester,theory,tutorial,practical,c
redit
- subject_code no as primary key
- semester must be between 1 to 10
- theory,tutorial must be between 0 to 5
- practical must be between 0 to 20
ANS:
CREATE TABLE SUBJECT_MASTER
(
SUBJECT_CODE NUMBER(7) PRIMARY KEY,
SUBJECT_NAME VARCHAR2(15) NOT NULL,
BRANCH CHAR(2) NOT NULL,
200470107072 DDL 2
Assignment 2
SEMESTER NUMBER(2) DEFAULT 1 CHECK(SEMESTER>=1 AND
SEMESTER<=10),
THEORY NUMBER(1) CHECK(THEORY>=0 AND THEORY<=5),
TUTORIAL NUMBER(1) CHECK(TUTORIAL>=0 AND TUTORIAL<=5),
PRACTICAL NUMBER(2) CHECK(PRACTICAL>=0 AND PRACTICAL<=20),
CREDIT NUMBER(2) NOT NULL
)
3) subject_distribution
subject_distribution_id, enrollmentno,subject_code
- subject_distribution_id must be defined as primary key
- enrollmentno defined as foreign key
- subject_code defined as foreign key
200470107072 DDL 3
Assignment 2
ANS:
CREATE TABLE SUBJECT_DISTRIBUTION
(
SUBJECT_DISTRIBUTION_ID NUMBER(2) PRIMARY KEY,
ENROLLMENTNO NUMBER(12) REFERENCES
STUDENT_MASTER(ENROLLMENTNO),
SUBJECT_CODE NUMBER(7) REFERENCES
SUBJECT_MASTER(SUBJECT_CODE)
)
200470107072 DDL 4
Assignment 2
4) Perform data insertation as per screenshot
Student_master - [Link]
ANS:
Subject_master - [Link]
200470107072 DDL 5
Assignment 2
subject_distribution - [Link]
5) total subject learned by Chris
ANS: SELECT SUBJECT_NAME, CURRENT_SEM
FROM STUDENT_MASTER
INNER JOIN SUBJECT_MASTER
ON STUDENT_MASTER.CURRENT_SEM =
SUBJECT_MASTER.SEMESTER
WHERE NAME='CHRIS'
200470107072 DDL 6
Assignment 2
6) total credit earned by Meet after clearing second sem exam
ANS: SELECT SUM(CREDIT) FROM STUDENT_MASTER
INNER JOIN SUBJECT_MASTER
ON STUDENT_MASTER.CURRENT_SEM =
SUBJECT_MASTER.SEMESTER
WHERE NAME='MEET'
200470107072 DDL 7
Assignment 2
7) update current_sem to 3 for all students who clears the exam
ANS: UPDATE STUDENT_MASTER
SET CURRENT_SEM=3
WHERE SEM_RESULT='PASS'
200470107072 DDL 8
Assignment 2
8) total theory needs to be attended by 3rd semester students
result should be display like
Name, total_theory_count
ANS: SELECT NAME, SUM(THEORY) TOTAL_THEORY_COUNT FROM
STUDENT_MASTER
INNER JOIN SUBJECT_MASTER
ON SUBJECT_MASTER.SEMESTER =
STUDENT_MASTER.CURRENT_SEM
WHERE CURRENT_SEM = 3 GROUP BY NAME
200470107072 DDL 9
Assignment 2
9) total practical needs to be attend by 3rd semester students
result should be display like
Name, total_practical_count
ANS: SELECT NAME, SUM(PRACTICAL) TOTAL_PRACTICAL_COUNT
FROM STUDENT_MASTER
INNER JOIN SUBJECT_MASTER
ON SUBJECT_MASTER.SEMESTER =
STUDENT_MASTER.CURRENT_SEM
WHERE CURRENT_SEM = 3 GROUP BY NAME
200470107072 DDL 10
Assignment 2
10) list subjects order by subject_code
ANS: SELECT SUBJECT_CODE, SUBJECT_NAME
FROM SUBJECT_MASTER
ORDER BY SUBJECT_CODE ASC
200470107072 DDL 11
