Create table department(dept_id int primary key,dept_name varchar(30), hod_name varchar(30));
Create table course(course_id int primary key,course_name varchar(30),dept_id int,program
varchar(30),semester int,credit int,foreign key(dept_id) references department(dept_id));
Create table student(student_id int primary key,student_name varchar(30), program
varchar(30),semester int,dept_id int, foreign key(dept_id) references department(dept_id));
Create table enrollment(enroll_id int primary key,course_id int,grade varchar(30),foreign
key(course_id) references course(course_id));
create table customer(cust_id int primary key,cust_name varchar(30),cust_street
varchar(50),cust_city varchar(30),cust_phone int,cust_email varchar(50));
create table account(ac_no int primary key,cust_id int,branch_id int,account_type
varchar(30),balance int,opened_date date,foreign key(cust_id) references customer(cust_id),foreign
key(branch_id) references branch(branch_id));
create table branch(branch_id int primary key,branch_name varchar(30),location varchar(30));
create table transaction(tran_id int primary key,ac_no int,tran_type varchar(30),amount
int,tran_date date,cust_email varchar(30),foreign key(ac_no) references account(ac_no));
select constraint_name,constraint_type from user_constraints where table_name=’ACCOUNT’;
alter table account drop constraint SYS_C008229;
alter table account add constraint p_key primary key(ac_no);
alter table enrollment add student_id int;
alter table enrollment drop PRIMARY KEY;
alter table enrollment add constraint p_key_enroll primary key (enroll_id,student_id);
alter table enrollment add constraint f_key_enroll foreign key (student_id) references
student(student_id);
insert values
INSERT INTO Department VALUES (1, 'Computer Science', 'Dr. Alice Green');
INSERT INTO Department VALUES (2, 'Information Tech', 'Dr. Brian Lee');
INSERT INTO Department VALUES (3, 'Mathematics', 'Dr. Carol White');
INSERT INTO Department VALUES (4, 'Physics', 'Dr. David Brown');
INSERT INTO Department VALUES (5, 'Chemistry', 'Dr. Emma Clark');
INSERT INTO Department VALUES (6, 'English', 'Dr. Frank Hall');
INSERT INTO Department VALUES (7, 'Economics', 'Dr. Grace King');
INSERT INTO Department VALUES (8, 'History', 'Dr. Henry Adams');
INSERT INTO Department VALUES (9, 'Psychology', 'Dr. Irene Scott');
INSERT INTO Department VALUES (10, 'Philosophy', 'Dr. Jack Turner');
INSERT INTO Course VALUES (101, 'DBMS', 1, 'BCA', 4, 3);
INSERT INTO Course VALUES (102, 'Algorithms', 1, 'MCA', 3, 4);
INSERT INTO Course VALUES (103, 'Linear Algebra', 3, 'MTECH', 2, 3);
INSERT INTO Course VALUES (104, 'Quantum Physics', 4, 'MSC', 3, 4);
INSERT INTO Course VALUES (105, 'Organic Chemistry', 5, 'MSC', 2, 3);
INSERT INTO Course VALUES (106, 'Literature', 6, 'MA', 1, 3);
INSERT INTO Course VALUES (107, 'Microeconomics', 7, 'MA', 3, 3);
INSERT INTO Course VALUES (108, 'World History', 8, 'MA', 1, 2);
INSERT INTO Course VALUES (109, 'Cognitive Psychology', 9, 'MA', 4, 3);
INSERT INTO Course VALUES (110, 'Ethics', 10, 'MA', 2, 2);
INSERT INTO Student VALUES (1, 'Rahul Sharma', 'BCA', 1, 1);
INSERT INTO Student VALUES (2, 'Anjali Das', 'MCA', 2, 2);
INSERT INTO Student VALUES (3, 'Rohit Kumar', 'MTECH', 1, 3);
INSERT INTO Student VALUES (4, 'Sneha Roy', 'MSC', 3, 4);
INSERT INTO Student VALUES (5, 'Amit Singh', 'BCA', 2, 1);
INSERT INTO Student VALUES (6, 'Priya Verma', 'MA', 1, 6);
INSERT INTO Student VALUES (7, 'Karan Mehta', 'BCA', 4, 1);
INSERT INTO Student VALUES (8, 'Neha Gupta', 'MSC', 2, 5);
INSERT INTO Student VALUES (9, 'Arjun Nair', 'MCA', 3, 2);
INSERT INTO Student VALUES (10, 'Pooja Sen', 'MA', 2, 8);
INSERT INTO Enrollment VALUES (1, 101, 'A', 1);
INSERT INTO Enrollment VALUES (2, 102, 'B', 2);
INSERT INTO Enrollment VALUES (3, 103, 'A', 3);
INSERT INTO Enrollment VALUES (4, 104, 'C', 4);
INSERT INTO Enrollment VALUES (5, 101, 'B', 5);
INSERT INTO Enrollment VALUES (6, 106, 'A', 6);
INSERT INTO Enrollment VALUES (7, 101, 'A', 7);
INSERT INTO Enrollment VALUES (8, 105, 'B', 8);
INSERT INTO Enrollment VALUES (9, 102, 'C', 9);
INSERT INTO Enrollment VALUES (10, 110, 'A', 10);
COMMIT;
COMMIT;
COMMIT;
COMMIT;
alter table course add constraint chk_course_program CHECK (program IN
('BCA','MCA','MTECH','MSC','MA'));
UPDATE course SET program=’BCA’ WHERE program NOT IN (‘BCA’,’MCA’,’MTECH’,’MSC’,’MA’);
Logic
select c.course_name from course c join department d on c.dept_id=d.dept_id where
d.dept_name='Computer Science';
select student_name from student where semester=4;
select s.student_name,c.course_name,[Link] from student s join enrollment e on
s.student_id=e.student_id join course c on c.course_id=e.course_id;
SET LINESIZE 200;
SET PAGESIZE 50;
SET WRAP OFF;
SET TRIMSPOOL ON;
Select s.student_name,d.dept_name,[Link] from student s join department d on
s.dept_id=d.dept_id join enrollment e on s.student_id=e.student_id where [Link]=’A’;
Select s.student_name,c.course_name,[Link] from student s join enrollment e on
s.student_id=e.student_id join course c on c.course_id=e.course_id where [Link]='MSC';
Select program,count(*) as student_count from student as student_count from student group by
program;
Select d.dept_name ,avg([Link]) from department d join course c on d.dept_id=c.dept_id group by
d.dept_name;
Select c.course_name,count(*) as Student_number from student s join enrollment e on
s.student_id=e.student_id join course c on c.course_id=e.course_id group by c.course_name;
Select s.student_name ,count(e.course_id) as courses_taken from student s join enrollment e on
s.student_id=e.student_id group by s.student_name having count(e.course_id)>1;