[SQL SERVER] Bài thực hành tổng hợp 7

Thảo luận trong 'Khoa Khoa Học Máy Tính' bắt đầu bởi interpol, 14 Tháng năm 2012.

  1. Offline

    interpol

    • Friends

    • Change
    Số bài viết:
    1.414
    Đã được thích:
    1.462
    Điểm thành tích:
    1.550
    Tạo bảng
    CREATE TABLE VATTU
    ( MaVTu char(4) NOT NULL primary key,
    TenVTu varchar(100) NOT NULL,
    DvTinh varchar(10),
    PhanTram Real
    )
    CREATE TABLE NHACC
    (
    MaNhaCC Char(3) NOT NULL primary key,
    TenNhaCC varchar(100) NOT NULL,
    DiaChi varchar(200) NULL,
    DienThoai varchar(20) NULL
    )

    CREATE TABLE DONDH
    (
    SoDh char(4) NOT NULL primary key,
    NgayDh datetime NOT NULL,
    MaNhaCC char(3) NOT NULL
    )

    CREATE TABLE CTDONDH
    (
    SoDh char(4) NOT NULL,
    MaVTu char(4) NOT NUll,
    SlDat int,
    primary key (SoDh, MaVTu)
    )

    create table PNHAP
    (
    SoPn char(4) NOT NULL primary key,
    NgayNhap datetime NOT NULL,
    SoDh char(4)
    )

    create table CTPNHAP
    (
    SoPn char(4) NOT NUll,
    MaVTu char(4) NOT NULL,
    SlNhap Int,
    DgNhap Money,
    Primary key(SoPn, MaVTu)
    )

    create table PXUAT
    (
    SoPx char(4) NOT NULL primary key,
    NgayXuat datetime,
    TenKh varchar(100)
    )

    create table CTPXUAT
    (
    SoPx char(4) NOT NULL,
    MaVTu char(4) NOT NULL,
    SlXuat int,
    DgXuat money,
    primary key(SoPx, MaVTu)
    )

    create table TONKHO
    (
    NamThang char(6) NOT NULL,
    MaVTu char(4) NOT NULL,
    SLDau int,
    TongSLN int,
    TongSLX int,
    SLCuoi int,
    primary key(NamThang,MaVTu)
    )


    Nhập dữ liệu

    insert into NHACC values('C01','Le Minh Tri','54 Hau Giang Q6 HCM','9781024')
    insert into NHACC values('C02','Tran Minh Thach','145 Hung Vuong My Tho','7698154')
    insert into NHACC values('C03','Hong Phuong','154/85 Le Lai Q1 HCM','9600125')
    insert into NHACC values('C04','Nhat Thang','198/40 Huong Lo 14 QTB HCM','8757757')
    insert into NHACC values('C05','Luu Nguyet Que','178 Nguyen Van Luong Da Lat','7964251')
    insert into NHACC values('C06','Cao Minh Trung','125 Le Quang Sung Nha Trang','Chua co')

    insert into VATTU values('DD01','Dau DVD Hitachi 1 dia','Bo',40)
    insert into VATTU values('DD02','Dau DVD Hitachi 3 dia','Bo',40)
    insert into VATTU values('TL15','Tu lanh Sanyo 150 lit','Cai',25)
    insert into VATTU values('TL90','Tu lanh Sanyo 90 lit','Cai',20)
    insert into VATTU values('TV14','Tivi Sony 14 inches','Cai',15)
    insert into VATTU values('TV21','Tivi Sony 21 inches','Cai',10)
    insert into VATTU values('TV29','Tivi Sony 29 inches','Cai',10)
    insert into VATTU values('VD01','Dau VCD Sony 1 dia','Bo',30)
    insert into VATTU values('VD02','Dau VCD Sony 3 dia','Bo',30)

    insert into DONDH values('D001','01/15/2006','C03')
    insert into DONDH values('D002','01/30/2006','C01')
    insert into DONDH values('D003','01/10/2006','C02')
    insert into DONDH values('D004','02/17/2006','C05')
    insert into DONDH values('D005','03/01/2006','C02')
    insert into DONDH values('D006','03/12/2006','C05')

    insert into PNHAP values('N001','01/17/2006','D001')
    insert into PNHAP values('N002','01/20/2006','D001')
    insert into PNHAP values('N003','01/21/2006','D002')
    insert into PNHAP values('N004','02/15/2006','D003')

    insert into CTDONDH values('D001','DD01',10)
    insert into CTDONDH values('D001','DD02',15)
    insert into CTDONDH values('D002','VD02',30)
    insert into CTDONDH values('D003','TV14',10)
    insert into CTDONDH values('D003','TV29',20)
    insert into CTDONDH values('D004','TL90',10)
    insert into CTDONDH values('D005','TV14',10)
    insert into CTDONDH values('D005','TV29',20)
    insert into CTDONDH values('D006','TV14',10)
    insert into CTDONDH values('D006','TV29',20)
    insert into CTDONDH values('D006','VD01',20)

    insert into CTPNHAP values('N001','DD01',8,2500000)
    insert into CTPNHAP values('N001','DD02',10,3500000)
    insert into CTPNHAP values('N002','DD01',2,2500000)
    insert into CTPNHAP values('N002','DD02',5,3500000)
    insert into CTPNHAP values('N003','VD02',30,2500000)
    insert into CTPNHAP values('N004','TV14',5,2500000)
    insert into CTPNHAP values('N004','TV29',12,3500000)

    insert into PXUAT values('X001','01/17/2006','Nguyen Ngoc Phuong Nhi')
    insert into PXUAT values('X002','01/25/2006','Nguyen Hong Phuong')
    insert into PXUAT values('X003','01/21/2006','Nguyen Tuan Tu')

    insert into CTPXUAT values('X001','DD01',2,3500000)
    insert into CTPXUAT values('X002','DD01',1,3500000)
    insert into CTPXUAT values('X002','DD02',5,4900000)
    insert into CTPXUAT values('X003','DD01',3,3500000)
    insert into CTPXUAT values('X003','DD02',2,4900000)
    insert into CTPXUAT values('X003','VD02',10,3250000)

    insert into TONKHO values('200601','DD01',0,10,6,4)
    insert into TONKHO values('200601','DD02',0,15,7,8)
    insert into TONKHO values('200601','VD02',0,30,10,20)
    insert into TONKHO values('200602','DD01',4,0,0,4)
    insert into TONKHO values('200602','DD02',8,0,0,8)
    insert into TONKHO values('200602','TV14',5,0,0,5)
    insert into TONKHO values('200602','TV29',12,0,0,12)
    interpol, 14 Tháng năm 2012
    #1
    noiaybinhyen, killyou117, hongoctrien và 2 người khác thích bài này.
  2. Offline

    interpol

    • Friends

    • Change
    Số bài viết:
    1.414
    Đã được thích:
    1.462
    Điểm thành tích:
    1.550
    Đang update....
    interpol, 14 Tháng năm 2012
    #2
  3. Offline

    killyou117

    • Lão Tạ

    • Kẻ cô đơn vẫn ngắm tường vi
    Số bài viết:
    1.270
    Đã được thích:
    627
    Điểm thành tích:
    560
    lúc thi mình có thể làm trên giao diện mà phải không? cái này mà insert rất mất thời gian đó
    killyou117, 14 Tháng năm 2012
    #3
  4. Offline

    hongoctrien

    • Friends

    Số bài viết:
    2.449
    Đã được thích:
    2.464
    Điểm thành tích:
    2.431
    uhm, có thể làm giao diện cho nhanh hơn, nếu có thể làm giao diện thì không ai mà đi gõ code cả, trừ trường hợp muốn gõ code cho thuần thục :)

    Gửi mọi người dữ liệu đà hoàn thành từ nội dung 1 đến nội dung 4.
    http://www.mediafire.com/?mx1u7i3dvpba1xy
    hongoctrien, 14 Tháng năm 2012
    #4
    killyou117, TheDeath, duyen.only.it và 1 người khác thích bài này.
  5. Offline

    interpol

    • Friends

    • Change
    Số bài viết:
    1.414
    Đã được thích:
    1.462
    Điểm thành tích:
    1.550
    Haha... Tạo database sau đó copy và execute toàn bộ câu lệnh trên thì đã có cơ sở dữ liệu database QLBANHANG cho các bạn dùng rồi... Đỡ tốn thời gian...
    Còn lúc thi thì tất nhiên là nhập vào qua giao diện rồi :D
    interpol, 15 Tháng năm 2012
    #5
  6. Offline

    hongoctrien

    • Friends

    Số bài viết:
    2.449
    Đã được thích:
    2.464
    Điểm thành tích:
    2.431
    CSDL này chưa tạo quan hệ và ràng buộc mà, sao nhập dữ liệu rồi :D
    hongoctrien, 15 Tháng năm 2012
    #6
  7. Offline

    interpol

    • Friends

    • Change
    Số bài viết:
    1.414
    Đã được thích:
    1.462
    Điểm thành tích:
    1.550
    Tạo Diagram sau :D
    Update phần view
    --a
    create view vw_DMVT
    as
    select mavtu, tenvtu
    from vattu
    select * from vw_dmvt

    --b
    create view vw_DonDH_TongSLDat
    as
    select dondh.sodh, sum(sldat)as 'TongSLDat'
    from dondh inner join ctdondh on dondh.sodh=ctdondh.sodh
    group by dondh.sodh --thong ke phai co group by (sum, count, avg, max, min..)
    select * from vw_dondh_tongsldat

    --c
    create view vw_DonDH_TongSLNhap
    as
    select dondh.sodh, sum(slnhap)as 'TongSLNhap'
    from (dondh inner join pnhap on dondh.sodh=pnhap.sodh) inner join ctpnhap on pnhap.sopn=ctpnhap.sopn
    group by dondh.sodh
    select * from vw_dondh_tongslnhap

    --d
    create view vw_DonDH_TongSLDatNhap
    as
    select dondh.sodh, sum(sldat)as 'TongSLDat',sum(slnhap)as 'TongSLNhap'
    from((ctdondh inner join dondh on ctdondh.sodh=dondh.sodh)inner join pnhap on dondh.sodh=pnhap.sodh)inner join ctpnhap on pnhap.sopn=ctpnhap.sopn
    group by dondh.sodh
    select * from vw_dondh_tongsldatnhap

    --e
    create view vw_DonDh_TongSLDatNhap_DayDu
    as
    select dondh.sodh
    from ((ctdondh inner join dondh on ctdondh.sodh=dondh.sodh)inner join pnhap on dondh.sodh=pnhap.sodh)inner join ctpnhap on pnhap.sopn=ctpnhap.sopn
    group by dondh.sodh
    having sum(sldat)=sum(slnhap)
    select * from vw_DonDh_TongSLDatNhap_DayDu

    --g
    create view vw_TongNhap
    as
    select convert(char(7),ngaynhap,121)as'NamThang',mavtu,sum(slnhap)as 'TongSLNhap'
    from pnhap, ctpnhap
    where pnhap.sopn=ctpnhap.sopn
    group by convert(char(7),ngaynhap,121),mavtu
    select * from vw_tongnhap

    --h
    create view vw_TongXuat
    as
    select convert(char(7),ngayxuat,121)as 'NamThang',mavtu,sum(slxuat)as'TongSLXuat'
    from pxuat, ctpxuat
    where pxuat.sopx=ctpxuat.sopx
    group by convert(char(7),ngayxuat,121),mavtu
    select * from vw_tongxuat

    --i
    create view vw_DonDH_MaVTu_TongSLNhap
    as
    select dondh.sodh, ngaydh, vattu.mavtu,tenvtu,sldat,sum(slnhap)as 'TongSLNhap'
    from dondh, ctdondh, vattu, pnhap, ctpnhap
    where dondh.sodh=ctdondh.sodh and ctdondh.mavtu=vattu.mavtu and dondh.sodh=pnhap.sodh and pnhap.sopn=ctpnhap.sopn
    group by dondh.sodh, ngaydh, vattu.mavtu,tenvtu,sldat
    select * from vw_DonDH_MaVTu_TongSLNhap

    --cau 3.5
    --a
    create view vw_PhieuDatHangChuaTungNhapHang
    as
    select *
    from vw_dondh_tongsldat
    where sodh not in (Select sodh from vw_dondh_tongslnhap)
    select * from vw_PhieuDatHangChuaTungNhapHang

    --b
    create view vw_MatHangChuaTungDatHang
    as
    select *
    from vattu
    where mavtu not in(select mavtu from ctdondh)
    select * from vw_MatHangChuaTungDatHang

    --c
    create view vw_NhaCCNhieuDonDHNhat
    as
    select *
    from nhacc
    where manhacc in(
    select top 1 with ties manhacc
    from dondh
    group by manhacc
    order by count(sodh) desc)
    select * from vw_NhaCCNhieuDonDHNhat

    --d
    create view vw_Vattu_TongSLXuat_NhieuNhat
    as
    select *
    from vw_dmvt
    where mavtu in(
    select top 1 with ties mavtu
    from vw_tongxuat
    order by tongslxuat desc)
    select * from vw_Vattu_TongSLXuat_NhieuNhat

    --e
    create view vw_DonDH_NhieuMatHangNhat
    as
    select *
    from vw_dondh_tongsldat
    where sodh in(
    select top 1 with ties sodh
    from vw_dondh_mavtu_tongslnhap
    group by sodh
    order by count(mavtu) desc)
    select * from vw_DonDH_NhieuMatHangNhat

    --f
    create view vw_TinhHinhNhapXuat_VatTu
    as
    select vw_tongnhap.NamThang,vw_dmvt.mavtu,tenvtu,tongslnhap,tongslxuat
    from vw_dmvt,vw_tongnhap,vw_tongxuat
    where vw_dmvt.mavtu=vw_tongnhap.mavtu and vw_dmvt.mavtu=vw_tongxuat.mavtu
    select * from vw_TinhHinhNhapXuat_VatTu

    --g
    create view vw_DatHangTheoNgay
    as
    select day(ngaydh)as ngaydh,vattu.mavtu,tenvtu,sum(sldat) as tongsldat
    from dondh,ctdondh,vattu
    where dondh.sodh=ctdondh.sodh and ctdondh.mavtu=vattu.mavtu
    group by day(ngaydh),vattu.mavtu,tenvtu
    select *
    from vw_DatHangTheoNgay
    order by ngaydh

    --h
    create view vw_DatHangTheoThang
    as
    select convert(char(7),ngaydh,121)as NamThang,vattu.mavtu,tenvtu,sum(sldat)as Tongsldat
    from dondh,ctdondh,vattu
    where dondh.sodh=ctdondh.sodh and ctdondh.mavtu=vattu.mavtu
    group by convert(char(7),ngaydh,121),vattu.mavtu,tenvtu
    select *
    from vw_DatHangTheoThang
    order by NamThang
    interpol, 15 Tháng năm 2012
    #7
    killyou117 và TheDeath thích bài này.
  8. Offline

    hongoctrien

    • Friends

    Số bài viết:
    2.449
    Đã được thích:
    2.464
    Điểm thành tích:
    2.431
    Gửi mọi người dữ liệu TH7, Nội dung 7: View
    http://www.mediafire.com/?l1p7teqe2117ipg
    hongoctrien, 19 Tháng năm 2012
    #8
  9. Offline

    hongoctrien

    • Friends

    Số bài viết:
    2.449
    Đã được thích:
    2.464
    Điểm thành tích:
    2.431
    Gửi mọi người Nội dung 8

    Mã:
    //1.Hien thi thong tin trong bang CTPNHAP co them cot thanh tien (Thanh tien=SLNhap*DgNhap)
select sopn, mavtu, slnhap, dgnhap, slnhap*dgnhap N'Thành tiền'
from ctpnhap

//2. Hien thi danh sach cac nha cung cap trong ban Nhacc co cot dia chi o Quan 1 TP HCM, sxep thu tu theo ho ten tang dan và sdt giam dan
select *
from nhacc
where diachi like N'Quận 1 HCM%'
order by tennhacc desc,dienthoai

//3. Hien thi danh sach cac phien xuat có ngay xuat nam trong khoang [1/1/2009, 31/1/2009]
select *
from pxuat
where month(NgayXuat) = 1 and year(NgayXuat)=2009

//4. Hien thi danh sach cac chi tiet phieu xuat co them cac cot vat tu, ngay xuat. Loc theo so luong xuat lon hon 5 va ngay xuat trong thang 1/2009
SELECT     ctpxuat.SoPx, ctpxuat.MaVTu, TenVTu, NgayXuat, SlXuat, DgXuat
FROM       VATTU INNER JOIN
                      CTPXUAT ON vattu.MaVTu = ctpxuat.MaVTu INNER JOIN
                      PXUAT ON ctpxuat.SoPx = pxuat.SoPx
WHERE     (MONTH(NgayXuat) = 1 AND YEAR(NgayXuat) = 2009) AND (SlXuat > 5)

//5. Hien thi ds cac nha cc gom cac thong tin sau: ma nha cung cap, ten nha cung cap da co dat hang. Khong dc trung lap du lieu.
SELECT distinct MaNhaCC, TenNhaCC
FROM dbo.NHACC
WHERE (MaNhaCC IN
                          (SELECT     MaNhaCC
                            FROM          dbo.DONDH))

//6. Hien thi cac don dat hang gan day nhat trong bang don dat hang
select * 
from dondh
where ngaydh = (select max(ngaydh) from dondh)

//7. Hien thi ds nha cung cap chua co don dat hang
SELECT MaNhaCC, TenNhaCC
FROM dbo.NHACC
WHERE (MaNhaCC NOT IN (SELECT MaNhaCC FROM dbo.DONDH))

//8. Hien thi danh sach cac nha cung cap gom cac cot: ma nha cc, ten nhacc, tong so don dat hang. Loc ra nha cc co ong so don dat hang > 1
SELECT nhacc.MaNhaCC, TenNhaCC, SUM(SlDat) AS TongSLDat
FROM NHACC INNER JOIN DONDH ON dondh.MaNhaCC = nhacc.MaNhaCC INNER JOIN
           CTDONDH ON ctdondh.SoDh = dondh.SoDh
GROUP BY nhacc.MaNhaCC, TenNhaCC
HAVING(SUM(SlDat) > 1)

//9. Hien thi thong tin trong ban pnhap co them cot tong thanh tien. Tong thanh tien = slnhap x dgia nhap cua cac mau tin chi tiet trong bang ctpnhap
select *, slnhap * dgnhap thanhtien
from pnhap inner join ctpnhap on pnhap.sopn=ctpnhap.sopn

//10. Hien thi danh sach cac phieu xuat hang gom cac cot: so phieu xuat va tong thanh tien. Trong do sxep theo thu tu tong thanh tien giam dan
select sopx, sum(slxuat * dgxuat) ThanhTien
from ctpxuat
group by sopx
order by 2 desc

//11. Hien thi cac phieu xuat hang co tong thanh tien lon nhat
select top 1 sopx, sum(slxuat * dgxuat) ThanhTien
from ctpxuat
group by sopx
order by 2 desc

//12. Hien thi danh sach nha cung cap chua co don dat hang
select *
from nhacc
where manhacc not in (select manhacc from dondh)

//13. Hien thi cac don dat hang chua co phieu nhap
select *
from dondh
where sodh not in (select sodh from pnhap)

//14. Hien thi danh sach cac don dat hang so tong so lan nhap la lon nhat
SELECT TOP (1) SoDh AS SoDH, COUNT(SoPn) AS SoPn
FROM PNHAP
GROUP BY SoDh
ORDER BY SoPn DESC

//15. Hien thi danh sach cac nha cung cap chua co don dat hang
select *
from nhacc
where manhacc not in (select manhacc from dondh)

//16. Hien thi danh sach cach nha cung cap gom cac cot: ma nha cung cap, ten nha cung cap va tong so don dat hang
select nhacc.manhacc, tennhacc, count(sodh) 'So luong dat hang'
from nhacc inner join dondh on nhacc.manhacc=dondh.manhacc
group by nhacc.manhacc, tennhacc

//17. Hien thi danh sach cac nha cung cap gom cac cot: ma nha cung cap, ten nha cung cap, tong so don dat hang va tong so phieu nhap
select nhacc.manhacc, nhacc.tennhacc, count(dondh.sodh), count(sopn)
from nhacc inner join dondh on nhacc.manhacc=dondh.manhacc
			inner join pnhap on dondh.sodh=pnhap.sodh
group by nhacc.manhacc, nhacc.tennhacc

//18. 
select pnhap.sopn, NgayNhap, TenVtu, slnhap 'So luong nhap', '^-^' as 'So luong xuat'
from pnhap inner join ctpnhap on pnhap.sopn=ctpnhap.sopn
			inner join vattu on ctpnhap.mavtu=vattu.mavtu
union
select pxuat.sopx, NgayXuat, TenVtu, '^-^', slxuat
from pxuat inner join ctpxuat on pxuat.sopx=ctpxuat.sopx
			inner join vattu on ctpxuat.mavtu=vattu.mavtu

//19. Hien thi thong tin trong bang ctpxuat va bo sung them cot thanh tien, sao cho co dong thong ke thanh tien o tung nhom phieu xuat
select *, slxuat * dgxuat 'TongThanhTien'
from ctpxuat
group by sopx, mavtu, slxuat, dgxuat
order by sopx
compute sum(slxuat*dgxuat) by sopx

//20. Hien thi trong bang ctpnhap cac thong tin: ma vat tu, so nhap hang, so luong nhap, don gia nhap, va co dong tong thong ke so luong, gia thap nhat, gia cao nhat o tung vat tu.
select mavtu, sopn, slnhap, dgnhap
from ctpnhap
group by mavtu, sopn, slnhap, dgnhap
order by slnhap
compute sum(slnhap), max(dgnhap), min(dgnhap) by slnhap

//21. Tao bang VATTU_TAM co cau truc va du lieu duoc sao chep tu bang VATTU
select * into vattu_tam
from vattu

//22. Tang bang vattu_bo gom cac cot: ma vat tu, don vi tinh, phan tram va du lieu dc sao chep tu bang vattu co loc theo don vi la Bo
select mavtu, dvtinh, phantram into VATTU_BO
from vattu
where dvtinh like 'Bo'
    hongoctrien, 27 Tháng năm 2012
    #9
    killyou117, thisicodon và duyen.only.it thích bài này.
  10. Offline

    BigZero

    • Friends

    • ôi diễn đàn, ngày càng thấy buồn ra...
    Số bài viết:
    703
    Đã được thích:
    987
    Điểm thành tích:
    1.000
    Hôm sau thi không bít đề sao đâu, yếu mấy cái thủ tục với trigger quá :(
    BigZero, 27 Tháng năm 2012
    #10
  11. Offline

    traitaodo_st

    • Thành Viên Mới

    Số bài viết:
    5
    Đã được thích:
    0
    Điểm thành tích:
    0
    thanks for sharing
    traitaodo_st, 11 Tháng sáu 2012
    #11

Chia sẻ trang này

Advertising: Linux system admin | nukeviet | nukeviet 4 | Upload ảnh miễn phí