By GokiSoft.com| 14:32 23/01/2021|
SQL Server/MySQL

[Share Code] Tìm hiểu primary key, foreign key, join (left, right, inner, full outer join) - Lập trình SQL BT2175



- Dieu kien trong SQL
	- Check
- Primary Key
- Foreign Key
- Select
	- Join nhieu tables -> where
	- Join
		- inner join
		- left join
		- right join
		- full outer join
===========================================================
Quan ly diem thi
	- Student: Thiet ke roi
	- Subject:
		- subject name
		- subject code
	- Mark
		- rollno
		- subject_code
		- mark





-- Select Student
select * from Student

-- create table subject
create table Subject (
	subject_code nvarchar(20) primary key,
	subject_name nvarchar(50)
)

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

select * from Subject

create table Marks(
	rollno nvarchar(20),
	subject_code nvarchar(20),
	mark float,
	primary key (rollno, subject_code)
)

alter table Marks
alter column rollno nvarchar(20) not null

alter table Marks
alter column subject_code nvarchar(20) not null

alter table Marks
add constraint pk_rollno_subject_code primary key (rollno, subject_code)

insert into Marks(rollno, subject_code, mark)
values
('R001', 'EPC', 8),
('R001', 'HTML5', 6)

insert into Marks(rollno, subject_code, mark)
values
('R002', 'EPC', 7),
('R003', 'HTML5', 9)

select * from Marks

delete from Marks

--- Show Data
select * from Student
select * from Marks
select * from Subject

insert into Marks(rollno, subject_code, mark)
values
('R005', 'EPC', 7)

-- Foreign key
-- Add foreign key rollno cua bang Marks toi rollno trong bang Student
alter table Marks
add constraint fk_rollno foreign key (rollno) references Student (rollno)

alter table Marks
add constraint fk_subject_code foreign key (subject_code) references Subject (subject_code)

insert into Marks(rollno, subject_code, mark)
values
('R001', 'ABC', 7)

insert into Marks(rollno, subject_code, mark)
values
('R003', 'EPC', -10)

-- Lam sao de dat dieu kien marks >= 0 and <= 10
alter table Marks
add constraint check_marks check (mark >=0 and mark <= 10)

alter table Marks
drop constraint check_marks

--- Show Data
select * from Student
select * from Marks
select * from Subject

-- Hien thi du lieu theo dinh dang sau: rollno, fullname, subject_name, mark
select Student.rollno, Student.fullname, Subject.subject_name, Marks.mark
from Student, Subject, Marks
where Student.rollno = Marks.rollno
	and Subject.subject_code = Marks.subject_code

-- Cach viet tren viet bang cach khac nua -> inner join
select Student.rollno, Student.fullname, Subject.subject_name, Marks.mark
from Student inner join Marks on Student.rollno = Marks.rollno
	inner join Subject on Subject.subject_code = Marks.subject_code

-- Hien thi toan bo thong tin sinh vien => SV nao ko co diem thi de NULL
select Student.rollno, Student.fullname, Subject.subject_name, Marks.mark
from Student left join Marks on Student.rollno = Marks.rollno
	left join Subject on Subject.subject_code = Marks.subject_code

-- Tinh diem trung binh cua sinh vien
select Student.rollno, Student.fullname, AVG(Marks.mark) 'Diem TB', MAX(Marks.mark) 'Diem MAX', MIN(Marks.mark) 'Diem MIN', SUM(Marks.mark) 'TONG DIEM', COUNT(Marks.mark) 'So DIEM'
from Student left join Marks on Student.rollno = Marks.rollno
group by Student.rollno, Student.fullname

-- Tinh diem trung binh cua sinh vien dieu kien AVG >= 8
select Student.rollno, Student.fullname, AVG(Marks.mark) 'Diem TB', MAX(Marks.mark) 'Diem MAX', MIN(Marks.mark) 'Diem MIN', SUM(Marks.mark) 'TONG DIEM', COUNT(Marks.mark) 'So DIEM'
from Student left join Marks on Student.rollno = Marks.rollno
group by Student.rollno, Student.fullname
having AVG(Marks.mark) >= 8




Tags:

Liên kết rút gọn:

https://gokisoft.com/2175

Bình luận