#### Database Schema Creation The following SQL commands establish a university database with student information, majors, courses, and enrollment data: sql -- Database setup drop university_db; create university_db; use university_db; create department( dept_id int primary key, dept_name varchar(30) ); create student( student_id varchar(13) primary key, full_name varchar(30) not null, age int, gender bit, dept_id int, foreign key(dept_id) references department(dept_id) ); create course( course_code varchar(13) primary key, course_title varchar(30) not null, duration int, credits decimal(5,2) ); create enrollment( course_code varchar(13), student_id varchar(13), score decimal(5,2), primary key(course_code,student_id), foreign key(student_id) references student(student_id), ); alter enrollment add constraint fk_enrollment foreign key(course_code) references course(course_code); #### Sample Data Population sql -- Department data select * from department; insert into department(dept_id,dept_name) values(1,'Computer Science'); insert into department(dept_id,dept_name) values(2,'Software Engineering'); insert into department(dept_id,dept_name) values(3,'Network Engineering'); insert into department(dept_id,dept_name) values(4,'Data Science'); -- Student data select * from student; insert into student values('1001','John Smith',18,0,1); insert into student values('1002','Emily Johnson',18,1,1); insert into student values('1003','Michael Brown',18,1,1); insert into student values('1004','Sarah Davis',18,1,1); insert into student values('1005','David Wilson',18,0,2); insert into student values('1006','Lisa Anderson',18,1,2); insert into student values('1007','Robert Taylor',18,0,2); insert into student values('1008','Jennifer Thomas',18,1,2); insert into student values('1009','William Jackson',19,0,3); insert into student values('1010','Maria White',20,0,3); insert into student values('1011','Christopher Harris',19,0,3); insert into student values('1012','Patricia Martin',21,0,3); insert into student values('1013','Daniel Thompson',19,0,null); insert into student values('1014','Linda Garcia',19,0,4); insert into student values('1015','Matthew Rodriguez',21,0,4); insert into student values('1016','Karen Lewis',21,0,4); -- Course data select * from course; insert into course values('CS101','Programming Fundamentals',32,4); insert into course values('CS102','Advanced Programming',32,3); insert into course values('CS201','Data Structures',16,4); insert into course values('ENG101','English Composition I',32,3); insert into course values('ENG102','English Composition II',32,3); insert into course values('MATH101','Calculus I',32,4); insert into course values('MATH201','Calculus II',32,4); -- Enrollment data select * from enrollment; insert into enrollment(student_id,course_code,score) values('1006','CS101',null); insert into enrollment(student_id,course_code,score) values('1005','CS101',null); insert into enrollment(student_id,course_code,score) values('1001','CS101',85); insert into enrollment(student_id,course_code,score) values('1002','CS101',92); insert into enrollment(student_id,course_code,score) values('1003','CS101',78); insert into enrollment(student_id,course_code,score) values('1004','CS101',65); insert into enrollment(student_id,course_code,score) values('1004','CS102',88); insert into enrollment(student_id,course_code,score) values('1004','CS201',90); insert into enrollment(student_id,course_code,score) values('1005','CS201',82); #### Basic Table Modifications sql -- Add and drop columns alter student add email varchar(50); alter student drop column email; -- Create and drop tables create table temp( temp_id int ); drop table temp; -- Insert duplicate department insert into department(dept_id,dept_name) values(1,'Software Engineering'); #### Single Table Queries sql -- Select all columns select * from student; -- Select specific columns select student_id,full_name from student; -- Calculated columns select full_name,2020-age as birth_year from student; -- Distinct values select distinct student_id from enrollment; -- Simple conditions select * from student where full_name='Emily Johnson'; select * from student where full_name like 'Emily Johnson'; -- Compound conditions select * from enrollment where course_code='CS101' and score>80; -- Range conditions select * from student where age>=18 and age<=19; select * from student where age between 18 and 19; -- IN operator select * from student where dept_id in (1,2,4); -- LIKE patterns select * from student where full_name like 'J%'; select * from student where full_name like '_oh%'; select * from student where full_name like '%oh%'; -- NULL values select * from enrollment where score is null; #### Sorting and Aggregation sql -- Ordering results select * from enrollment order by score; select * from enrollment order by score desc; -- Count functions select count(*) from student; select distinct student_id from enrollment; select count(distinct student_id) from enrollment; -- Count non-null values select * from enrollment; select count(score) from enrollment; -- Average calculation select avg(score) from enrollment where course_code='CS101'; -- Grouping select course_code,count(student_id) from enrollment group by course_code; -- Grouping with HAVING select student_id,avg(score) from enrollment group by student_id having avg(score)>=85; -- Aggregate functions: count, sum, avg, max, min -- Cannot be used directly in WHERE, require GROUP BY and HAVING #### Multi-Table Queries sql -- Equijoins select student.*,enrollment.* from student,enrollment where student.student_id=enrollment.student_id; select * from student,enrollment where student.student_id=enrollment.student_id; -- Multi-table joins select * from student,enrollment,course where student.student_id=enrollment.student_id and enrollment.course_code=course.course_code; -- Left outer joins -- Preserve all student records, even those without enrollments select * from student left outer join enrollment on student.student_id=enrollment.student_id; -- Department student count with all departments insert into department values(5,'Test Department'); select department.dept_id,count(student_id) from department left outer join student on department.dept_id=student.dept_id group by department.dept_id; -- Nested queries -- Unrelated subqueries (subquery doesn't depend on parent query) -- Find students enrolled in 'CS101' select full_name from student,enrollment where course_code='CS101' and student.student_id=enrollment.student_id; select full_name from student where student_id in (select student_id from enrollment where course_code='CS101'); -- Related subqueries -- Find students enrolled in 'CS101' select full_name from student where 'CS101' in (select course_code from enrollment where student.student_id=enrollment.student_id); -- Find students who scored above their average select student_id,course_code from enrollment x where score > (select avg(score) from enrollment y group by student_id having x.student_id=y.student_id); -- Using derived tables select student_id,course_code from enrollment, (select student_id,avg(score) as avg_score from enrollment group by student_id) as avg_enrollment where enrollment.student_id=avg_enrollment.student_id and score>avg_enrollment.avg_score; -- EXISTS -- Find students enrolled in 'CS101' select full_name from student where exists( select * from enrollment where course_code='CS101' and student.student_id=enrollment.student_id ); -- Set operations: UNION, INTERSECT, EXCEPT -- Find students aged 18 in department 1 select student_id from student where age=18 and dept_id=1; select student_id from student where age=18 intersect select student_id from student where dept_id=1; select student_id from student where age=18 except select student_id from student where dept_id!=1; -- Find students enrolled in 'CS101' or 'CS201' select distinct student_id from enrollment where course_code in('CS101','CS201'); select student_id from enrollment where course_code='CS101' union select student_id from enrollment where course_code='CS201'; #### Views A view is a virtual table based on the result-set of an SQL statement. The database stores only the view definition, not the data itself. Data remains in the base tables, so changes to base tables are reflected in views. sql -- Create a view with student information create view student_basic_info as select student_id,full_name,age from student select * from student_basic_info -- Create a view with department information drop view student_dept_info create view student_dept_info as select student_id,full_name,student.dept_id from student left outer join department on student.dept_id=department.dept_id select * from student_dept_info -- Create a view with average scores create view student_avg_scores as select student_id,avg(score) as average_score from enrollment group by student_id -- Find courses where students scored above their average select enrollment.student_id,course_code from enrollment,student_avg_scores where enrollment.student_id=student_avg_scores.student_id and enrollment.score>student_avg_scores.average_score #### Stored Procedures A stored procedure is a precompiled collection of SQL statements stored in the database that can be executed by calling it. sql -- Create a procedure to get a student's grades create proc get_student_grades as begin select * from enrollment where student_id='1005' end exec get_student_grades -- Modify procedure to accept parameters alter proc get_student_grades @student_id varchar(13),@course_code varchar(13) as begin select enrollment.*,course.credits from enrollment,course where student_id=@student_id and enrollment.course_code=course.course_code end exec get_student_grades '1004','CS201' -- Drop procedure drop proc get_student_grades #### Triggers A trigger is a special procedure that automatically executes when an event (INSERT, UPDATE, DELETE) occurs on a table. sql -- Prevent adding more than 20 students create trigger student_count_check on student after insert as begin if (select count(*) from student)>20 begin print 'Error: Maximum student count exceeded' rollback transaction end else begin print 'Student added successfully' end end insert into student(student_id,full_name) values('1020','New Student') delete from student where student_id='1019' -- Instead of trigger alter trigger student_count_check on student instead of insert as begin select * from inserted select * from deleted if (select count(*) from student)>19 begin print 'Error: Maximum student count exceeded' rollback transaction end else begin print 'Student added successfully' declare @student_id varchar(13) declare @full_name varchar(30) declare @age int select @student_id=student_id from inserted select @full_name=full_name from inserted select @age=age from inserted insert into student(student_id,full_name,age) values(@student_id,@full_name,@age) end end -- Prevent removing too many students create trigger min_student_check on student after delete as begin if (select count(*) from student)<15 begin print 'Error: Minimum student count not met' rollback end else print 'Deletion completed successfully' end delete from student where student_id='1001' -- Adjust borderline scores (55-59 becomes 60) create borderline_score_adjustment on enrollment instead of insert as begin declare @student_id varchar(13) declare @course_code varchar(13) declare @score decimal(5,2) select @student_id=student_id from inserted select @course_code=course_code from inserted select @score=score from inserted if @score >=55 and @score <=59 begin set @score=60 end insert into enrollment values(@course_code,@student_id,@score) end insert enrollment values('CS102','1016',57) select * from enrollment #### Functions sql -- Function to calculate average score for a course create function course_average(@course_code varchar(13)) returns decimal(5,2) as begin declare @average decimal(5,2) select @average=avg(score) from enrollment where course_code=@course_code return @average end select dbo.course_average('CS101') -- Function to get students in a department create function department_students(@dept_id int) returns @student_list table( student_id varchar(13), full_name varchar(30) ) as begin insert into @student_list(student_id,full_name) select student_id,full_name from student where dept_id=@dept_id return end select * from dbo.department_students(1) -- Function to get all enrollments for a department create function department_enrollments(@dept_id int) returns @dept_enrollments table( student_id varchar(13), course_code varchar(13), score decimal(5,2) ) as begin insert into @dept_enrollments select student.student_id,course_code,score from department,student,enrollment where department.dept_id=student.dept_id and student.student_id=enrollment.student_id and student.dept_id=@dept_id return end select * from dbo.department_enrollments(1) #### Indexes An index is a data structure that improves the speed of data retrieval operations on a database table. However, indexes consume storage space and require maintenance during updates. Considerations for index creation: 1. Tables with frequent updates or heavy insert/delete operations 2. Tables with very few records 3. Columns with many duplicate values (e.g., gender fields) Types of indexes: - Clustered: Physical order of data matches the index order. Only one per table. - Non-clustered: Physical order doesn't match index order. Multiple allowed. sql -- Create unique index on enrollment table create unique index enrollment_idx on enrollment(student_id asc,course_code desc) -- Drop index drop index enrollment_idx on enrollment #### Cursors A cursor is a pointer to a result set that allows row-by-row processing. It's useful for complex operations that require sequential access to data. Advantages: - Saves query results for later use - Eliminates need for repeated queries Disadvantages: - Memory intensive for large result sets sql -- Basic cursor usage declare dept_cursor cursor for select dept_name from department declare @dept_name varchar(30) open dept_cursor fetch next from dept_cursor into @dept_name while @@FETCH_STATUS=0 begin select @dept_name as 'Department Name' fetch next from dept_cursor into @dept_name end deallocate dept_cursor -- Add grade categories to enrollment declare grade_cursor cursor for select course_code,student_id,score from enrollment declare @course_code varchar(13) declare @student_id varchar(13) declare @score decimal open grade_cursor fetch next from grade_cursor into @course_code,@student_id,@score while @@FETCH_STATUS=0 begin if @score >=80 update enrollment set grade_category='A' where course_code=@course_code and student_id=@student_id else if @score>=70 update enrollment set grade_category='B' where course_code=@course_code and student_id=@student_id else if @score>=0 update enrollment set grade_category='C' where course_code=@course_code and student_id=@student_id fetch next from grade_cursor into @course_code,@student_id,@score end deallocate grade_cursor select * from enrollment -- Student and department information declare student_dept_cursor cursor for select sname,mname from student left outer join department on student.dept_id=department.dept_id declare @sname varchar(30) declare @mname varchar(30) open student_dept_cursor fetch next from student_dept_cursor into @sname,@mname while @@FETCH_STATUS=0 begin select @sname as 'Student Name',@mname as 'Department Name' fetch next from student_dept_cursor into @sname,@mname end close student_dept_cursor #### Views vs Cursors Comparison 1. Nature: Views are database objects, while cursors are data buffers 2. Resource usage: Cursors use more memory than views 3. Processing method: Cursors process row-by-row, views process entire result sets 4. Database operations: Cursors allow data modification, views are typically read-only