SQL Server: Solution Practical 1

--2.
create database DBLab1;
use DBLab1;

--3.
create table Subjects(
SubjectID int not null identity,
SubjectName nvarchar(50)
);
alter table Subjects add constraint pk_SubjectId
primary key(SubjectId);

create table Classes(
ClassID int not null identity,
ClassName nvarchar(50)
);
alter table Classes add constraint pk_ClassID
primary key(ClassID);

create table Students(
StudentId int not null identity,
StudentName nvarchar(50),
Age int,
Email varchar(100)
);
alter table Students add constraint pk_StudentID
primary key(StudentID);

create table Marks(
Mark int,
SubjectID int,
StudentID int
);
alter table Marks add constraint fk_SubjectID
foreign key(SubjectID) references Subjects(SubjectID);
alter table Marks add constraint fk_StudentID
foreign key(StudentID) references Students(StudentID);

create table ClassStudent(
StudentID int,
ClassID int
);
--alter table ClassStudent add constraint fk_ClassID
-- foreign key(ClassID) references Classes(ClassID);
--alter table ClassStudent add constraint fk_StudentID1
-- foreign key(StudentID) references Students(StudentID);
--Hoặc:
alter table ClassStudent add
constraint fk_ClassID
foreign key(ClassID) references Classes(ClassID),
constraint fk_StudentID1
foreign key(StudentID) references Students(StudentID);
--alter table ClassStudent drop constraint fk_ClassID,fk_StudentID1;

--4. Lưu ý: Phải chèn dữ liệu vào bảng chứa PK trước, FK sau.
insert Students(StudentName,Age,Email) values
('Nguyen Quang An',18,'an@yahoo.com'),
('Nguyen Cong Vinh',20,'vinh@gmail.com'),
('Nguyen Van Quyen',19,'quyen'),
('Pham Thanh Binh',25,'binh@com'),
('Nguyen Van Tai Em',30,'taiem@sport.vn');
select Students.* from Students;
insert Classes(ClassName) values
('C0706L'),
('C0708G');
insert ClassStudent values
(1,1),
(2,1),
(3,2),
(4,2),
(5,2);
insert Subjects(SubjectName) values
('SQL'),
('Java'),
('C'),
('Visual Basic');
insert Marks values
(8,1,1),
(4,2,1),
(9,1,1),
(7,1,3),
(3,1,4),
(5,2,5),
(8,3,3),
(1,3,5),
(3,2,4);

--5.
select*from Students;

--6.
select*from Subjects;

--7.
alter table Students add constraint ck_Age
check(Age between 15 and 50);
insert Students(Age) values(50);
select*from Students;

--8.
alter table Students add Status bit;
alter table Students add constraint df_Status
default(1) for Status;

--9.
alter table Marks drop constraint fk_StudentID,fk_SubjectID;
alter table ClassStudent drop constraint fk_ClassID,fk_StudentID1;
alter table Students drop constraint pk_StudentID;
alter table Classes drop constraint pk_ClassID;
alter table Subjects drop constraint pk_SubjectID;
--1.
select * from students order by studentname;

--2.
select * from subjects;

--3.
select * from students where email like '%@%' and email like '%.%';

--4.
select * from students where studentname like 'Nguyen%';

--5.
select studentname,classname from students,classes,classstudent where students.studentid=classstudent.studentid and classstudent.classid=classes.classid and classname='C0706L';

--6.
select studentname,subjectname,mark from students,subjects,marks where students.studentid=marks.studentid and marks.subjectid=subjects.subjectid;

--7.
select studentname from students where studentid not in(select studentid from marks);

--8.
select subjectname from subjects where subjectid not in(select subjectid from marks);

--9.
select studentname, avg(mark) as N'Điểm TB' from students,marks where students.studentid=marks.studentid group by studentname;

--10.
select top 1 subjectname,count(marks.subjectid) from subjects,marks where subjects.subjectid=marks.subjectid group by subjectname order by count(marks.subjectid) desc;

--11.
select subjectname,mark from subjects,marks where subjects.subjectid=marks.subjectid and mark=(select max(mark) from marks);

--12.
select top 1 subjectname,count(mark) from subjects,marks where subjects.subjectid=marks.subjectid and mark<5 group by subjectname order by count(mark) desc;

--13.
alter table students add constraint ck1 check(age>15 and age<50);

--14.
alter table marks drop constraint fk1,fk2;
alter table classstudent drop constraint fk3,fk4;

--15.
delete from students where studentid=1;

--16.
alter table students add status bit default 1;

--17.
update  students set status=0;