2021-04-10 13:39:47

create database banHang



use banHang







create table item (



id int primary key identity (1,1),

name nvarchar(30),

make_by nvarchar(30),

make_in nvarchar(20),

price_in money,

price_out money,

day_create date

)



create table sell (

id int primary key identity (1,1),

id_item int,

note nvarchar(300),

day_sell date,

soLuong int,

FOREIGN KEY (id_item) REFERENCES item (id)

)





insert into item (name,make_by,make_in,price_in,price_out,day_create)

values

('tông lào','tofudo','vn',1,10,'2020-02-19'),

('cá cơm','hawal','vn',2,100,'2020-02-19'),

('litchi','hawal','japan',1,10,'2020-02-19'),

('cona','doli','japan',1,1000,'2020-02-19'),

('gà','trại','china',0,10000,'2020-02-19'),

('grape','hawal','japan',1,10,'2020-02-19'),

('mangoes','hawal','japan',1,10,'2020-02-19'),

('bamboo','forest','japan',1,10,'2020-02-19')







 select * from item

 
 

insert into sell (id_item,note,day_sell,soLuong)

values
(9,'ttttttttttttttt','2020-02-19',58),
(10,'ádasdada','2020-02-19',1),
(2,'adasdasdasd','2020-02-19',5),
(8,'sdasdasdasdasdas','2020-02-19',9),
(8,'dsdasdasd','2020-02-19',78),
(1,'ádasdasdasda','2020-02-19',44),
(1,'đâsdadasdasdasd','2020-02-19',4),
(5,'dâsdadadasaa','2020-02-19',6),
(7,'âddsdadadadasdadadadadas','2020-02-19',54),
(5,'ádasdada','2020-02-19',44)

 select * from item

 --cau 3
create view view_sell
as
 select sell.id 'mã đơn hàng',item.name 'tên sản phẩm',sell.day_sell,sell.soLuong
  from sell,item
  where sell.id_item=item.id

 select * from  view_sell



 -- cau 4

 

create proc proc_xuatxu

  @xuatxu nvarchar(10)

as
begin


 select sell.id 'mã đơn hàng',item.name 'tên sản phẩm',item.make_in, sell.day_sell,sell.soLuong
  from sell,item
  where sell.id_item=item.id
		and item.make_in=@xuatxu

end


exec proc_xuatxu vn

  --cau 5



select item.name, sum(item.price_out * sell.soLuong) 'Tong'

from sell left join item on sell.id_item = item.id_item

group by item.name




create proc proc_total

  @item_ nvarchar(10),
  @total int output
as
begin


	select @total = sum(item.price_out * sell.soLuong)
	from sell,item
	where sell.id_item = item.id
		and item.name = @item_
	group by item.name,item.price_out

end
drop proc   proc_total



declare @count int

exec proc_total 'tofu', @total = @count output


print N'So ban ghi tim duoc: ' + CONVERT(nvarchar(10), @count)

2021-04-10 12:29:32

-- Active database
use C2010L

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

-- Query
---- Hien thi thong tin diem thi sinh vien: roll_number, student_name, subject_name, marks
select Student.roll_number, Student.student_name, Subject.subject_name, Marks.marks
from Student, Subject, Marks
where Student.roll_number = Marks.roll_number
	and Subject.subject_id = Marks.subject_id

---- Bai toan: Luu lai truy van tren thi lam cach nao -> Cach thong thuong -> save file sql -> sau mo lai va test
---- Bai toan 2: Neu phat trien du an theo team (nhieu thanh vien) -> cung su dung chung 1 database
--------- Lam sao de chia se cac query -> thanh vien trong du an co the su dung lai query ban da viet thi lam cach nao???
---- Bai toan 3: Phat trien chuong trinh phan mem:
--------- Cach 1: Nhung sql trong code ngon ngu lap trinh
--------- Cach 2: Luu toan sql trong database -> su dung thong qua 1 view & proc -> su dung dc query
---- View & Proc => giup chung ta => quan ly dc cau query trong chinh database
-- I) View: Luu 1 truy van select -> view se dc hieu nhu 1 table tam thoi.
create view view_student_marks
as
select Student.roll_number, Student.student_name, Subject.subject_name, Marks.marks
from Student, Subject, Marks
where Student.roll_number = Marks.roll_number
	and Subject.subject_id = Marks.subject_id

---- TEST
select * from view_student_marks
order by marks asc

select * from view_student_marks
where marks >= 8
order by marks asc

---- Sua view
alter view view_student_marks
as
select top(10) Student.roll_number, Student.student_name, Subject.subject_name, Marks.marks
from Student, Subject, Marks
where Student.roll_number = Marks.roll_number
	and Subject.subject_id = Marks.subject_id

---- Xoa view
drop view view_student_marks

-- II) proc: Duoc hieu nhu 1 function (ham & method & phuong thuc)
------ Điều này có nghĩa là gì: proc -> có thể lưu nhiều câu truy vấn khác nhau: Có thể là nhiều lệnh select, insert, update, delete
create proc proc_test
as
begin
	select * from Student
	select * from Subject
	select * from Marks
end

---- TEST
exec proc_test

create proc proc_view_student_marks_and_test
as
begin
	select Student.roll_number, Student.student_name, Subject.subject_name, Marks.marks
	from Student, Subject, Marks
	where Student.roll_number = Marks.roll_number
		and Subject.subject_id = Marks.subject_id

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

exec proc_view_student_marks_and_test

-- Proc: Hiểu nó như là 1 function trong ngôn ngữ lập trình
---- Do vậy: Tham số đầu vào, dữ liệu trả về (output)
---- Proc -> xem thông tin điểm thi của sinh viên -> điều kiện tham số đầu vào là minMarks
create proc view_student_marks_by_min
	@minMarks float
as
begin
	select Student.roll_number, Student.student_name, Subject.subject_name, Marks.marks
	from Student, Subject, Marks
	where Student.roll_number = Marks.roll_number
		and Subject.subject_id = Marks.subject_id
		and Marks.marks >= @minMarks
end

exec view_student_marks_by_min 8

---- Bài toán proc gồm nhiều tham số đầu vào thì làm thế nào.
------ Xem thông tin điểm thi sinh viên: @minMark & @roll_number
create proc view_student_marks_by_min_rollno
	@minMarks float,
	@rollno nvarchar(20)
as
begin
	select Student.roll_number, Student.student_name, Subject.subject_name, Marks.marks
	from Student, Subject, Marks
	where Student.roll_number = Marks.roll_number
		and Subject.subject_id = Marks.subject_id
		and Marks.marks >= @minMarks
		and Student.roll_number = @rollno
end

exec view_student_marks_by_min_rollno 7, 'R001'

---- Đếm xem có bao nhiêu sinh viên có có điểm thi >= minMarks - Hiển thị dữ liệu đầu ra.
create proc view_count_student_marks_by_min
	@minMarks float,
	@count int output -- float, int, nvarchar, ...
as
begin
	select Student.roll_number, Student.student_name, Subject.subject_name, Marks.marks
	from Student, Subject, Marks
	where Student.roll_number = Marks.roll_number
		and Subject.subject_id = Marks.subject_id
		and Marks.marks >= @minMarks

	-- count(*): Có nghĩa là đếm tổng số bản ghi trong câu truy vấn: Với cách viết này không cần dùng thêm group by.
	select @count = count(*)
	from Student, Subject, Marks
	where Student.roll_number = Marks.roll_number
		and Subject.subject_id = Marks.subject_id
		and Marks.marks >= @minMarks
end

---- Khi khai báo 1 biến trong lệnh sql -> cần đặt từ khóa declare trước tên biến.
declare @count2 int
exec view_count_student_marks_by_min 6, @count = @count2 output
---- Không thể cộng chuỗi với số nguyên
---- Do vậy: Để in được kết quả đầu ra -> chuyển số nguyên về chuỗi (int -> nvarchar)
------- Hàm chuyển int -> nvarhhar: CONVERT(nvarchar(20), @count2)
print N'So ban ghi tim duoc: ' + CONVERT(nvarchar(20), @count2)

-- Sua stored (proc) -> alter
-- Xoa stored (proc) -> drop
drop proc view_count_student_marks_by_min

---- Overview: group by
-- group by: Khi nhom dữ liêu -> thông qua các hàm: count, sum, min, max, avg.
select Student.roll_number, Student.student_name, count(Marks.marks) 'So Mon Co Diem Thi'
from Student, Subject, Marks
where Student.roll_number = Marks.roll_number
	and Subject.subject_id = Marks.subject_id
group by Student.roll_number, Student.student_name