USE master;
GO
ALTER DATABASE examdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE IF EXISTS examdb;
CREATE DATABASE examdb;
USE examdb;
create table students (
stdid INT,
FullName NVARCHAR(100),
Email NVARCHAR(100),
GPA DECIMAL (10,2),
age DECIMAL (10),
constraint pk_students_stdid primary key(stdid)
);
CREATE NONCLUSTERED INDEX IX_STUDENTS_EMAIL
ON students (Email);
CREATE NONCLUSTERED INDEX IX_STUDENTS_age
ON students (age);
/select name from sys.key_constraints where parent_object_id= object_id(‘students’); alter table students drop constraint PK_students_stdid;/
/create clustered index IX_students_FullName on students (FullName);/
EXEC sp_helpindex ‘students’;
insert into students (stdid, FullName, Email, GPA, age)
values (101, ‘James Brown’, ‘james@gmail.com’, 3.0, 18),
(110, ‘Michael Shaw’, ‘shaw@gmail.com’, 3.3, 25),
(111, ‘Oliver khan’, ‘oliver@gmail.com’, 4.3, 34);
Select * from students WHERE age < 30;
/delete from students where GPA = 3.0;/
create table courses (
courseid int,
coursename nvarchar(100),
constraint pk_courses_courseid primary key(courseid)
);
insert into courses (courseid, coursename)
values (1, ‘Maths’),
(2, ‘English’),
(3, ‘Physis’);
select * from courses;
/* Now create a relationship table (student takes a course) */
create table enrollments (
enrollid int,
stdid int,
courseid int,
constraint pk_enrollments_enrollid primary key(enrollid),
constraint fk_enroll_students foreign key (stdid) references students(stdid),
constraint fk_enroll_courses foreign key (courseid) references courses(courseid)
);
insert into enrollments (enrollid, stdid, courseid) values (10, 101, 2);
select * from enrollments;
update enrollments set courseid = 3 where stdid=101;
select * from enrollments;
/*delete from enrollments where enrollid = 10;
select * from enrollments;*/
EXEC sp_helpindex ‘students’;