SQL Server: C2010G-SQL-Buổi 3

create database C2010G_DBLab2;

use C2010G_DBLab2;

--go : Đặt ở giữa các lệnh => Thực hiện cùng lúc nhiều lệnh (lô lệnh).

create table Student(
   RN int not null identity,
   Name varchar(20),
   Age tinyint
);
go
alter table Student add constraint pk_RN primary key(RN);
go
insert Student(Name,Age) values
   ('Nguyen Hong Ha',20),
   ('Truong Ngoc Anh',30),
   ('Tuan Minh',25),
   ('Dan Truong',22);
go
create table Test(
   TestID int not null identity,
   Name varchar(20)
);
go
alter table Test add constraint pk_TestID primary key(TestID);
go
insert Test(Name) values('EPC'),('DWMX'),('SQL1'),('SQL2');
go
create table StudentTest(
   RN int not null,
   TestID int not null,
   Date date,
   Mark float
);

alter table StudentTest add
   constraint pk_RN_TestID primary key(RN,TestID),
   constraint fk_RN foreign key(RN) references Student(RN),
   constraint fk_TestID foreign key(TestID) references Test(TestID);

insert StudentTest values
   (1,1,'7/17/2006',8),
   (1,2,'7/18/2006',5),
   (1,3,'7/19/2006',7),
   (2,1,'7/17/2006',7),
   (2,2,'7/18/2006',4),
   (2,3,'7/19/2006',2),
   (3,1,'7/17/2006',10),
   (3,3,'7/18/2006',1);

--a.
select convert(numeric(4,2),Mark) from StudentTest;

--b.
select*from Student where Age>25;

--c.
select*from Student where Age in(20,30);

--d.
select*from Test where Name like'%s%'; --JS: .* => Một chuỗi bất kỳ. --SQL: %

--e.
select*from StudentTest where Mark>5;

--f.
select*from Student where Name like '% ____';
go
--g.
select*from Student where Name like '______ %';

--h.
select*from Student where Name like '[^r][^r][^r][^r][^r][^r] %';

--i.
alter table Student add Status varchar(10) default('Young');
select*from Student;

--k.
alter table StudentTest drop constraint fk_RN,fk_TestID;

--l.
alter table Student drop constraint pk_RN;
go
alter table Test drop constraint pk_TestID;
go
alter table StudentTest drop constraint pk_RN_TestID;

--m.
drop table Student, Test, StudentTest;

--n.
use master;
drop database C2010G_DBLab2;

--Extra:
--1.
--Tính tổng:
select SUM(Age) [Tổng tuổi] from Student;
--Tính trung bình cộng của tuổi:
select AVG(convert(float,Age)) [Tuổi trung bình] from Student;
--Đếm bản ghi:
select COUNT(*) [Số lượng bản ghi] from Student;
--Lấy giá trị lớn nhất:
select MAX(Age) [Tuổi lớn nhất] from Student;
--Lây giá trị nhỏ nhất:
select MIN(Age) [Tuổi nhỏ nhất] from Student;

--2.
select Name,Age from Student where Age=(select MAX(Age) from Student); --subquery

--3.
select Name,Age from Student where Age=(select MIN(Age) from Student); --subquery

--4.
select*from Student;
select*from StudentTest;
select*from Test a join StudentTest b on a.TestID=b.TestID
   where Mark=(select Max(Mark) from StudentTest);

--5.
select*from Test a join StudentTest b on a.TestID=b.TestID
   where Mark=(select MIN(Mark) from StudentTest);

--6.