Scribd
Upload
4 views9 pages

SQL Queries for NorthWind Database

The document contains a series of SQL queries and commands for managing databases related to customers, orders, products, and flight management systems. It includes commands for creating tables, inserting data, and performing various queries to retrieve and manipulate data. Additionally, it demonstrates the use of joins, filters, and aggregate functions in SQL.

Uploaded by

thanhlbinh1804
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
4 views9 pages

SQL Queries for NorthWind Database

The document contains a series of SQL queries and commands for managing databases related to customers, orders, products, and flight management systems. It includes commands for creating tables, inserting data, and performing various queries to retrieve and manipulate data. Additionally, it demonstrates the use of joins, filters, and aggregate functions in SQL.

Uploaded by

thanhlbinh1804
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

use NorthWind

go
select *
From Customers, Orders
--Câu2
select *
From Customers
inner join Orders
on [Link] = [Link]
--Câu3
select *
from Customers
where [Link] = N'London' or [Link]= N'France'
order by [Link] desc
--Câu 4
Select *
from Customers
where
([Link] = N'Sales Manager' and [Link] =
N'USA')

or (
[Link] = N'Mexico' and [Link] = N'Owner')
order by [Link] desc
--Câu 5
select [Link], [Link],
[Link], [Link]
from Orders
inner join Customers
on [Link] = [Link]
inner join Employees
on [Link] = [Link]
where [Link] between '1997-01-01' and {d'1997-06-30'}
--Câu 6
select [Link], [Link],
[Link], [Link]
from Orders
inner join Customers
on [Link] = [Link]
inner join Employees
on [Link] = [Link]
where (MONTH([Link]) = 2 and YEAR([Link]) = 1997)
or ([Link] between '1997-03-01' and {d'1997-03-30'});
--Câu 7
select [Link], [Link],
[Link], [Link]
from Orders
inner join Customers
on [Link] = [Link]
inner join Employees
on [Link] = [Link]
where --DAY([Link]) %2 = 0 and YEAR([Link]) %2 != 0
DAY([Link]) in ( 15, 25, 16, 05, 01) and YEAR([Link]) in
(199, 1996)
--Câu12
select *
from Products
inner join Suppliers
on [Link] = [Link]
inner join Categories
on [Link] = [Link]
where [Link] Like N'%ch%' -- Lấy kí tự
--Cau 17
select
[Link], [Link],
[Link], [Link],
[Link],[Link], [Link],
[Link]
from Customers
inner join Orders
on [Link] = [Link]
inner join Employees
on [Link] = [Link]
where
--cast([Link] as date) = cast(getdate() as date) OR
--cast([Link] as date) = cast(getdate() as date);
cast([Link] as date) = {d'1996-10-01'} or
cast([Link] as date) = {d'1996-10-30'};
--câu 19 Cách 2
select

[Order Details].UnitPrice, [Order Details].Quantity,


[Link], [Link],
[Link],
[Order Details].Discount,
[Link],
([Link])
as TienPhaiTra
from Products
inner join (
select
[Order Details].OrderID,
[Order Details].ProductID,
([Order Details].UnitPrice *[Order Details].Quantity)
as ThanhTien,
([Order Details].UnitPrice *[Order
Details].Quantity)*[Order Details].Discount as TienGiamGia
from [Order Details]
inner join Orders
on [Order Details].OrderID= [Link]
where YEAR([Link])=1997 and [Order
Details].Discount > 0 and
(([Order Details].UnitPrice *[Order Details].Quantity)-
([Order Details].UnitPrice *[Order Details].Quantity)*[Order
Details].Discount) <50
) AnotherOrderDetailToTal
on [Link] =
[Link]
inner join [Order Details]
on [Link] = [Order
Details].OrderID and
[Link] = [Order
Details].ProductID

order by [Order Details].OrderID;


--Cau21
Select [Link], [Link],
[Link],
[Link], [Link],
[Link]*[Link] as TriGia
From Products
inner join Suppliers
on [Link] = [Link]
where (
[Link] = 1 and
[Link] >0)
--Cau22
select [Link],
[Link] + N' ' + [Link] + N' '
+[Link] as HoTen,
[Link], [Link], [Link]
From Employees

where Year([Link]) < 1993


--Cau23
select
Count([Link]) as SoLuong,
Max([Link]) as DonGiaCaoNhat,
Min([Link]) as DonGiaThapNhat,
AVG([Link]) as DonGiaTrungBinh
From Products
select *
From Products
--Cau24
select
[Link], [Link],
Count([Link]) as SoLuong,
Max([Link]) as DonGiaCaoNhat,
Min([Link]) as DonGiaThapNhat,
AVG([Link]) as DonGiaTrungBinh
From Products
inner join Categories
on [Link] = [Link]
Group By
[Link],
[Link]
--Cau25
select COUNT([Order Details].OrderID) as TongDon
From Orders
inner join [Order Details]
on [Link] = [Order Details].OrderID
where [Link] in ('Belgium', 'Canada', 'UK')
--Cau26
select [Link], COUNT([Order Details].OrderID) as TongDon
From Orders
inner join [Order Details]
on [Link] = [Order Details].OrderID
where [Link] in ('Belgium', 'Canada', 'UK')
Group By [Link]
--Cau27
select [Link], [Link],
AVG([Link]) as DonGiaTrungBinh

From Products
inner join Categories
on [Link]= [Link]
Group By
[Link], [Link]
HAVING
AVG([Link]) >30
--Cau28
select [Link], [Link],
AVG([Link]) as DonGiaTrungBinh

From Products
inner join Categories
on [Link]= [Link]
where ([Link]) >40
Group By
[Link], [Link]
--Cau31
select top 15
[Link],
[Link],
[Link]
From Products
Order by [Link] Desc
--Cau32
select top 5 Percent
[Link],
[Link],
[Link]
From Products
Order by [Link] Desc;
--câu33
select top 10
[Link], [Link],
COUNT([Link]) as SoLuongDonHang
from Customers
inner join Orders
on [Link] = [Link]
group by
[Link], [Link]
order by COUNT([Link]) desc
--cach2
Select [Link], [Link],
[Link]
From Customers
inner join (

select
top 10
[Link],
COUNT([Link]) as SoLuongDonHang
from Orders
Group by [Link]
order by COUNT([Link]) desc

)KhachHangMuaDonHang
on [Link] = [Link]
--Cau34
select Customers.*
From Customers
where [Link] in
(
select distinct [Link]
from Orders
)
--cách 2
select distinct Customers.*
From Customers
inner join Orders
on [Link] = [Link]

use master
go
create database QuanLiChuyenBay
go
begin
use QuanLiChuyenbay;
end
go

create table MayBay(


MaMB nvarchar(10) not null primary key,
HangSX nvarchar(50) null,
NamSX nvarchar(50) null,
SoHieu nvarchar(20) null,
SoChoNgoi nvarchar(10) null,
);
create table HanhKhach(
MaHK nvarchar(10) not null primary key,
HoTen nvarchar(20) null,
NamSinh date null,
);
create table TuyenBay(
MaTB nvarchar(10) not null primary key,
DiemDi nvarchar(20) null,
DiemDen nvarchar(20) null,
);
create table Chuyenbay (
MaCB nvarchar(10) not null primary key,
MaTB nvarchar(10) null,
Constraint fk_CB_TB foreign key (MaTB) references TuyenBay(MaTB)
);
create table TiepVien(
MaTV nvarchar(10) not null primary key,
HoTen nvarchar(50) null,
);
create table PhiCong(
MaPC nvarchar(10) not null primary key,
HoTen nvarchar(50) null,
);
create table ThucHienChuyenBay(
MaTHCB nvarchar(10) not null primary key,
MaMB nvarchar(10) null,
MaCB nvarchar(10) null,
NgayGiohacanh date null,
Constraint fk_THCB_CB foreign key (MaCB) references ChuyenBay(MaCB),
Constraint fk_THCB_MayBay foreign key (MaMB) references MayBay(MaMB),
);
create table HanhKhach_ChuyenBay(
MaTHCB nvarchar(10) not null,
MaHK nvarchar(10) not null,
Constraint pk_HK_CB primary key (MaTHCB, MaHK),
Constraint fk_HK_CB foreign key (MaHK) references HanhKhach(MaHK),
Constraint fk_HK_CB_THCB foreign key (MaTHCB) references
ThucHienChuyenBay(MaTHCB)
);
create table PhiCong_CB(
MaTHCB nvarchar(10) not null,
MaPC nvarchar(10) not null,
Constraint pk_PC_CB primary key (MaTHCB, MaPC),
Constraint fk_PC_CB_PC foreign key (MaPC) references PhiCong(MaPC),
Constraint fk_PC_CB_THCB foreign key (MaTHCB) references
ThucHienChuyenBay(MaTHCB),
);

alter table PhiCong_CB drop constraint fk_PC_CB_PC


alter table PhiCong_CB add constraint fk_PC_CB_PC foreign key (MaPC)
references PhiCong(MaPC)
drop table PhiCong

use master
go
--create database QuanliBanhang
--go

begin
use QuanLiBanHang;
end
go
create table KhachHang(
MaKH nvarchar(10) not null primary key,
HoTenKH nvarchar(50) null,
GioiTinh nvarchar(10) null,
SoDienThoaiKH bigint null,

);
create table NhanVien(
MaNV nvarchar(10) not null primary key,
HoTenNV nvarchar(50) null,
GioiTinh nvarchar(10) null,
SoDienThoaiNV bigint null,

);
create table DonHang(
MaDH nvarchar(10) not null primary key,
NgayMua date null,
TienVC money null,
MaNV nvarchar(10) null,
MaKH nvarchar(10) null,
Constraint fk_DonHang_KhachHang foreign key (MaKH) references
KhachHang(MaKH),
Constraint fk_DonHang_NhanVien foreign key (MaNV) references
NhanVien(MaNV),

);

create table HangHoa(


MaHH nvarchar(10) not null primary key,
TenHH nvarchar(50) null,
DonViTinh nvarchar(20) null,
DonGiaNiemYet money null,

);
create table ChiTietDonHang(
MaDH nvarchar(10) not null,
MaHH nvarchar(10) not null,
Constraint pk_ChiTietDonHang primary key (MaDH, MaHH),
DonGia nvarchar(50) null,
SoLuongBan nvarchar(50) null,
Constraint fk_ChiTiet_DonHang foreign key (MaDH) references
DonHang(MaDH),
Constraint fk_CHiTiet_HangHoa foreign key (MaHH) references
HangHoa(MaHH),
);
ALTER TABLE DonHang DROP CONSTRAINT fk_DonHang_KhachHang;
ALTER TABLE DonHang DROP CONSTRAINT fk_DonHang_NhanVien;
alter table ChiTietDonHang drop constraint fk_ChiTiet_DonHang;
drop table DonHang

create table SinhVien


(
MaSV nvarchar(10) not null,
HoLotSV nvarchar(50) null,
TenSV nvarchar(50) null,
GioiTinhSV nvarchar(20) null constraint df_gioi_tinh_sv_m
default 'M',-- default 'F',-- check(GioiTinhSV in ('M', 'F')),
GhiChuSV nvarchar_max,
EmailSinhVien nvarchar(100) null,
MaLop nvarchar(10) null,
constraint ck_gioi_tinh_sv check(GioiTinhSV in ('M', 'F')),
constraint uq_email_sinh_vien unique(EmailSinhVien)
);
create table GiangVien
(
MaGiangVien nvarchar(10) not null primary key,
HoLotGiangVien nvarchar(100) null,
TenGiangVien nvarchar(50) null,
GioiTinhGiangVien nvarchar(20) null check (GioiTinhGiangVien
in ('F', 'M')),
EmailGiangVien nvarchar(100) null unique
);
create table Diem
(
MaSV nvarchar(10) not null,
MaMonHoc nvarchar(10) not null,
MaGiangVien nvarchar(10) not null,
Diem decimal(14,7) null,
constraint pk_diem primary key (MaSV, MaMonHoc, MaGiangVien),
constraint fk_diem_sinh_vien foreign key (MaSV) references
SinhVien (MaSV),
constraint fk_diem_mon_hoc foreign key (MaMonHoc) references
MonHoc (MaMonHoc),
constraint fk_diem_giang_vien foreign key (MaGiangVien)
references GiangVien (MaGiangVien)
);
begin
if OBJECT_ID(N'uq_ten_khoa', N'UQ') is null
begin
alter table Khoa
add constraint uq_ten_khoa unique(TenKhoa);
end

if COL_LENGTH(N'[Link]', N'SoDienThoaiKhoa') is null


begin
alter table Khoa
add SoDienThoaiKhoa nvarchar(20) null;
end
end
begin

if OBJECT_ID(N'df_gioi_tinh_sv_m', N'D') is not null


begin
alter table SinhVien
drop constraint df_gioi_tinh_sv_m;
end

if OBJECT_ID(N'df_gioi_tinh_sv_f', N'D') is null


begin
alter table SinhVien
add constraint df_gioi_tinh_sv_f default 'F' for
GioiTinhSV;
end
end

You might also like