By GokiSoft.com| 19:48 06/04/2021|
SQL Server/MySQL

[Share Code] Tìm hiểu where, inner join sql, left join sql, right join sql, full outer join sql- like sql- not like sql- in sql- not in sql, between sql - Lập trình SQL Server BT2240



- Where
	- between
	- like (not like)
	- in (not in)
- Select
	- left join
	- inner join
	- right join
	- full outer join
- select into
- Cau lenh khac
	- distinct
	- top
	- group by .. having
		- sum
		- count
		- min
		- max
		- avg
	- order by
		- desc
		- asc





-- Using database
use C2010L

-- TEST
select * from Student
select * from Subject
select * from Marks

-- Insert data
insert into Student(roll_number, student_name, email, address, phone)
values
('R003', 'TRAN VAN B', 'b@gmail.com', 'Nam Dinh', '1321321'),
('R004', 'TRAN VAN D', 'd@gmail.com', 'Nam Dinh', '78978978'),
('R005', 'TRAN VAN E', 'e@gmail.com', 'Nam Dinh', '3453453')

-- Where - Hien thi tat ca du lieu
select * from Marks

-- Hien thi diem thi theo tu tu tang dan
select * from Marks
order by marks asc

-- Hien thi diem thi theo tu tu giam dan
select * from Marks
order by marks desc

-- Hien thi diem thi marks >= 8 && sap xep marks theo thu tu giam dan
select * from Marks
where marks >= 8
order by marks desc

-- Hien thi danh sach sinh vien co student_name chua VAN
select * from Student
where student_name like '%VAN%'
order by student_name asc

select * from Student
where student_name like '%VAN'
order by student_name asc

select * from Student
where student_name like 'VAN%'
order by student_name asc

select * from Student
where student_name like '%V%A%N%'
order by student_name asc

-- Hien thi sinh vien ko chua chu VAN
select * from Student
where student_name not like '%VAN%'
order by student_name asc

-- Hien thi sinh vien co roll_number: R001, R002, R005, R010
select * from Student
where roll_number = 'R001'
	or roll_number = 'R002'
	or roll_number = 'R005'
	or roll_number = 'R010'
order by student_name asc

---- Tuong tu cau lenh sau day
select * from Student
where roll_number in ('R001', 'R002', 'R005', 'R010')
order by student_name asc

-- Hien thi sinh vien khong co roll_number: R001, R002, R005, R010
select * from Student
where roll_number not in ('R001', 'R002', 'R005', 'R010')
order by student_name asc

-- Hien thi diem thi nam trong khoang: 7-9
select * from Marks
where marks >= 7
	and marks <= 9
order by marks asc

select * from Marks
where marks between 7 and 9 --Su dung (numeric) int, float, money, date, datime
order by marks asc

-- Hien thi 2 sinh vien dau tien trong bang A-Z
select top(2) * from Student
order by student_name asc

-- TEST Marks => Hieu ve lenh distinct
select distinct roll_number from Marks

-- Hien thi danh sach sinh vien da co diem thi
select distinct Student.* from Student, Marks
where Student.roll_number = Marks.roll_number -- foreign key
order by Student.student_name asc

-- Hien thi danh sach vien(roll_number, student_name) + diem thi + ma mon hoc
select Student.roll_number, Student.student_name, Marks.subject_id, Marks.marks
from Student, Marks
where Student.roll_number = Marks.roll_number -- foreign key
	and Marks.marks > 7
order by Student.student_name asc

--> Tuong duong voi lenh inner join
select Student.roll_number, Student.student_name, Marks.subject_id, Marks.marks
from Student inner join Marks on Student.roll_number = Marks.roll_number
where Marks.marks > 7
order by Student.student_name asc

select Student.roll_number, Student.student_name, Marks.subject_id, Marks.marks
from Student join Marks on Student.roll_number = Marks.roll_number
order by Student.roll_number asc

select Student.roll_number, Student.student_name, Marks.subject_id, Marks.marks
from Student inner join Marks on Student.roll_number = Marks.roll_number
order by Student.roll_number asc

select Student.roll_number, Student.student_name, Marks.subject_id, Marks.marks
from Student left join Marks on Student.roll_number = Marks.roll_number
order by Student.roll_number asc

select Student.roll_number, Student.student_name, Marks.subject_id, Marks.marks
from Student right join Marks on Student.roll_number = Marks.roll_number
order by Student.roll_number asc

select Student.roll_number, Student.student_name, Marks.subject_id, Marks.marks
from Marks right join Student on Student.roll_number = Marks.roll_number
order by Student.roll_number asc

select Student.roll_number, Student.student_name, Marks.subject_id, Marks.marks
from Student full outer join Marks on Student.roll_number = Marks.roll_number
order by Student.roll_number asc

-- Hien thi thong tin sinh vien + so mon hoc da co diem
select Student.roll_number, Student.student_name, count (Marks.marks) 'So Mon Co Diem'
from Student, Marks
where Student.roll_number = Marks.roll_number -- foreign key
group by Student.roll_number, Student.student_name
order by Student.roll_number asc

-- Hien thi thong tin sinh vien + so mon hoc da co diem
select Student.roll_number, Student.student_name, count (Marks.marks) 'So Mon Co Diem'
from Student left join Marks on Student.roll_number = Marks.roll_number -- foreign key
group by Student.roll_number, Student.student_name
order by Student.roll_number asc

select Student.roll_number, Student.student_name, sum (Marks.marks) 'Tong So Diem'
from Student, Marks
where Student.roll_number = Marks.roll_number -- foreign key
group by Student.roll_number, Student.student_name
order by Student.roll_number asc

select Student.roll_number, Student.student_name, avg (Marks.marks) 'Diem Trung Binh'
from Student, Marks
where Student.roll_number = Marks.roll_number -- foreign key
group by Student.roll_number, Student.student_name
order by Student.roll_number asc

select Student.roll_number, Student.student_name, max (Marks.marks) 'Diem Max'
from Student, Marks
where Student.roll_number = Marks.roll_number -- foreign key
group by Student.roll_number, Student.student_name
order by Student.roll_number asc

select Student.roll_number, Student.student_name, min (Marks.marks) 'Diem Min'
from Student, Marks
where Student.roll_number = Marks.roll_number -- foreign key
group by Student.roll_number, Student.student_name
order by Student.roll_number asc

select Student.roll_number, Student.student_name, count (Marks.marks) 'So Mon Co Diem', 
	sum (Marks.marks) 'Tong So Diem',
	avg (Marks.marks) 'Diem Trung Binh',
	max (Marks.marks) 'Diem Max',
	min (Marks.marks) 'Diem Min'
from Student, Marks
where Student.roll_number = Marks.roll_number -- foreign key
group by Student.roll_number, Student.student_name
order by Student.roll_number asc

-- Xem sinh vien co diem trung binh >= 8
select Student.roll_number, Student.student_name, count (Marks.marks) 'So Mon Co Diem', 
	sum (Marks.marks) 'Tong So Diem',
	avg (Marks.marks) 'Diem Trung Binh',
	max (Marks.marks) 'Diem Max',
	min (Marks.marks) 'Diem Min'
from Student, Marks
where Student.roll_number = Marks.roll_number -- foreign key
group by Student.roll_number, Student.student_name
having avg (Marks.marks) >= 8
order by Student.roll_number asc

-- Foreign key & primary key
select Student.roll_number, Student.student_name, Student.email, Marks.marks
from Student, Marks
where Student.roll_number = Marks.roll_number
order by Student.roll_number asc

select * from Student
select * from Marks




Tags:

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

https://gokisoft.com/2240

Bình luận