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 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

[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

by GokiSoft.com - 19:48 06/04/2021 1,750 Lượt Xem



- 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', '[email protected]', 'Nam Dinh', '1321321'),
('R004', 'TRAN VAN D', '[email protected]', 'Nam Dinh', '78978978'),
('R005', 'TRAN VAN E', '[email protected]', '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




Bình luận



Chia sẻ từ lớp học

Đã sao chép!!!