IMG-LOGO
×

Giáo Trình Môn Học

Khoá học lập trình SQL

Kiến Thức Core SQL Server

[Video] Hướng dẫn cách xác định kiểu dữ liệu, primary key, foreign key trong SQL - My SQL - SQL Server [Video] Hướng dẫn thao tác nhiểu bảng - select - multi table - join, inner join trong SQL Server [Video] Hướng dẫn phân tích CSDL - phân tích bảng, column trong bản - Học lập trình SQL, SQL Server [Video] Hướng dẫn tìm hiểu create, alter , insert table và bản chất primary key, foreign key - SQL Server [Video] Hướng dẫn tìm hiểu select, and, in, like, not in,group by .. having, order by asc, desc - SQL Server [Video] Quản lý sinh viên (create, alter, update, insert, select) - Lập trình SQL Server

View SQL Server

[Video] Hướng dẫn tìm hiểu view trong SQL Server Bài Giảng: Hướng dẫn tìm hiểu view + proc trong SQL - Lập trình SQL Server

Store & Procedure

[Video] Hướng dẫn tìm hiểu proc procedure trong SQL Server - Lập trình SQL Server, MySQL Bài Giảng: Xây dự hệ thống đưa đón học sinh bằng xe bus của Trường Học - Lập trình SQL Server [Video] Xây dựng CSDL quản lý điểm danh aptech - Lập trình SQL Server/MySQL

Trigger

[Video] Học Trigger qua ví dụ - quản lý nhà trên phố - Lập trình SQL Server [Video] Hướng dẫn học trigger qua bài quản lý điểm danh Aptech - Lập trình SQL Server [Video] Bài tập - Quản lý nhân khẩu - Lập trình SQL Server - MySQL




Trang Chủ SQL Server/MySQL [Share Code] Tìm hiểu foreign key, constraint, where: join nhieu tables, inner join, left join Lập trình SQL Server

[Share Code] Tìm hiểu foreign key, constraint, where: join nhieu tables, inner join, left join Lập trình SQL Server

by GokiSoft.com - 09:59 22/03/2021 1,517 Lượt Xem



Nội dung:
	- Thiết kế table
		- foreign key
		- constraint
	- Select
		- where: join nhieu tables
		- inner join
		- left join
		- right join
		- full outer join
		- order by



-- Create database
create database C2010G

-- Active database
use C2010G

-- Create tables
create table Student (
	rollno nvarchar(20) primary key,
	fullname nvarchar(50) not null,
	email nvarchar(200),
	gender nvarchar(16)
)

create table Subject (
	id int primary key identity(1,1),
	subject_name nvarchar(50)
)

create table Marks (
	rollno nvarchar(20),
	subject_id int,
	marks float
)

--- Insert data
insert into Student (rollno, fullname, email, gender)
values
('R001', 'TRAN VAN A', '[email protected]', 'Nam'),
('R002', 'TRAN VAN B', '[email protected]', 'Nam')

insert into Subject(subject_name)
values
('EPC'),
('Lap Trinh HTML/CSS/JS')

insert into Marks(rollno, subject_id, marks)
values
('R001', 1, 7),
('R001', 5, 8),
('R002', 1, 7)

select * from Student
select * from Subject
select * from Marks

-- Bai toan: Thiet ke rollno trong Marks -> khong dc phep insert du lieu rollno khong chua trong bang Student vao Marks
insert into Marks(rollno, subject_id, marks)
values
('R003', 1, 7)

insert into Marks(rollno, subject_id, marks)
values
('R002', 10, 7)

insert into Marks(rollno, subject_id, marks)
values
('R020', 16, 7)

-- Cach thiet ke foreign key
-- Co 3 cach
---- Cach 1:
drop table Marks

create table Marks (
	rollno nvarchar(20) references Student (rollno),
	subject_id int,
	marks float
)

insert into Marks(rollno, subject_id, marks)
values
('R003', 2, 7)

insert into Marks(rollno, subject_id, marks)
values
('R001', 1, 7)

---- Cach 2:
drop table Marks

create table Marks (
	rollno nvarchar(20) references Student (rollno),
	subject_id int,
	marks float,
	constraint fk_subject_id foreign key (subject_id) references subject (id)
)

insert into Marks(rollno, subject_id, marks)
values
('R002', 1, 8)

insert into Marks(rollno, subject_id, marks)
values
('R002', 10, 8)

---- Cach 3:
drop table Marks

create table Marks (
	rollno nvarchar(20),
	subject_id int,
	marks float
)

alter table Marks
add constraint fk_rollno foreign key (rollno) references Student (rollno)

alter table Marks
add constraint fk_subject_id foreign key (subject_id) references Subject (id)

insert into Marks(rollno, subject_id, marks)
values
('R001', 1, 7),
('R002', 1, 7)

insert into Marks(rollno, subject_id, marks)
values
('R001', 2, 2)

select * from Student
select * from Subject
select * from Marks

delete from Subject where id = 1
delete from Subject where id = 2

-- Phan 2:
--- Hien thi du lieu theo yeu cau: rollno (Student), fullname (Student), marks (Marks)

select Student.rollno, Student.fullname, Marks.marks
from Student, Marks
where Student.rollno = Marks.rollno

--- Hien thi du lieu theo yeu cau: rollno (Student), fullname (Student), marks (Marks), subject_name (Subject)

select Student.rollno, Student.fullname, Marks.marks, Subject.subject_name
from Student, Marks, Subject
where Student.rollno = Marks.rollno
	and Subject.id = Marks.subject_id

-- Them dieu kien hien -> marks xep theo thu tu giam dan
select Student.rollno, Student.fullname, Marks.marks, Subject.subject_name
from Student, Marks, Subject
where Student.rollno = Marks.rollno
	and Subject.id = Marks.subject_id
order by Marks.marks desc

select Student.rollno, Student.fullname, Marks.marks, Subject.subject_name
from Student, Marks, Subject
where Student.rollno = Marks.rollno
	and Subject.id = Marks.subject_id
order by Marks.marks asc

select Student.rollno, Student.fullname, Marks.marks, Subject.subject_name
from Student, Marks, Subject
where Student.rollno = Marks.rollno
	and Subject.id = Marks.subject_id
order by Marks.marks

insert into Student (rollno, fullname, email, gender)
values
('R003', 'TRAN VAN C', '[email protected]', 'Nam')

select * from Student
select * from Subject
select * from Marks

-- inner join
select Student.rollno, Student.fullname, Marks.marks
from Student, Marks
where Student.rollno = Marks.rollno
order by Student.rollno asc

-- Viet bang cach khac
select Student.rollno, Student.fullname, Marks.marks
from Student inner join Marks on Student.rollno = Marks.rollno
-- Student: table left, Marks -> table right

-- left join
select Student.rollno, Student.fullname, Marks.marks
from Student left join Marks on Student.rollno = Marks.rollno

-- right join
select Student.rollno, Student.fullname, Marks.marks
from Marks right join Student on Student.rollno = Marks.rollno

-- full outer join
select Student.rollno, Student.fullname, Marks.marks
from Student full outer join Marks on Student.rollno = Marks.rollno

-- group by
select * from Student
select * from Subject
select * from Marks

-- Hien thi du lieu dau ra: dem so mon hoc da co diem cua tung sinh vien
select Student.rollno, Student.fullname, count(Marks.subject_id) as 'Count'
from Student, Marks
where Student.rollno = Marks.rollno
group by Student.rollno, Student.fullname

select Student.rollno, Student.fullname, avg(Marks.marks) as 'Marks_AVG'
from Student, Marks
where Student.rollno = Marks.rollno
group by Student.rollno, Student.fullname

select Student.rollno, Student.fullname, min(Marks.marks) as 'Marks MIN'
from Student, Marks
where Student.rollno = Marks.rollno
group by Student.rollno, Student.fullname

select Student.rollno, Student.fullname, max(Marks.marks) as 'Marks MAX'
from Student, Marks
where Student.rollno = Marks.rollno
group by Student.rollno, Student.fullname

select Student.rollno, Student.fullname, sum(Marks.marks) as 'Marks SUM'
from Student, Marks
where Student.rollno = Marks.rollno
group by Student.rollno, Student.fullname

select Student.rollno, Student.fullname, 
	count(Marks.subject_id) as 'Count',  
	avg(Marks.marks) as 'Marks_AVG', 
	min(Marks.marks) as 'Marks MIN',
	max(Marks.marks) as 'Marks MAX',
	sum(Marks.marks) as 'Marks SUM'
from Student, Marks
where Student.rollno = Marks.rollno
group by Student.rollno, Student.fullname

select Student.rollno, Student.fullname, 
	count(Marks.subject_id) as 'Count',  
	avg(Marks.marks) as 'Marks_AVG', 
	min(Marks.marks) as 'Marks MIN',
	max(Marks.marks) as 'Marks MAX',
	sum(Marks.marks) as 'Marks SUM'
from Student left join Marks on Student.rollno = Marks.rollno
group by Student.rollno, Student.fullname

select Student.rollno, Student.fullname, count(Marks.subject_id) as 'Count'
from Student left join Marks on Student.rollno = Marks.rollno
group by Student.rollno, Student.fullname
having count(Marks.subject_id) = 0





Bình luận



Chia sẻ từ lớp học

Đã sao chép!!!