Cấp bậc tác giả:

DATABASE

Một số bài tập mẫu SQL(Phân I)

Được viết bởi QuangIT ngày 25/12/2012 lúc 01:56 PM
Khách hàng đặt hàng cho cty thông qua các đơn đặt hàng. Thông tin chung về các đơn đạt hàng được lưu trữ trong DONDATHANG (mỗi một đơn đặt hàng phải do một nhân viên của cty lập và do đó bảng này có quan hệ với bảng NHANVIEN)
  • 0
  • 20295

Một số bài tập mẫu SQL(Phân I)


*      Bảng NHACUNGCAP lưu trữ dữ liệu về các đối tác cung cấp hàng cho công ty

*      Bảng MATHANG lưu trữ dữ liệu về các mặt hàng hiện có trong công ty.

*      Bảng LOAIHANG phân loại các mặt hàng hiện có.

*      Bảng NHANVIEN có dữ liệu là thông tin về các nhân viên làm việc trong công ty.

*      Bảng KHACHHANG được sử dụng để lưu giữ thông tin về các khách hàng của công ty.

Khách hàng đặt hàng cho cty thông qua các đơn đặt hàng. Thông tin chung về các đơn đạt hàng được lưu trữ trong DONDATHANG (mỗi một đơn đặt hàng phải do một nhân viên của cty lập và do đó bảng này có quan hệ với bảng NHANVIEN)

Thông tin chi tiết của các đơn đặt hàng (đặt mua hàng gì, số lượng, giá cả, …) được lưu trữ chi tiết trong bảng CHITIETDATHANG. Bảng này có quan hệ với DONDATHANG và MATHANG.

 

Sử dụng câu lệnh sau để tạo cơ sở dữ liệu:

CREATE TABLE nhacungcap

(

macongty NVARCHAR(10) NOT NULL

CONSTRAINT pk_nhacungcap

PRIMARY KEY(macongty),

tencongty NVARCHAR(40) NOT NULL,

tengiaodich NVARCHAR(30) NULL,

diachi NVARCHAR(60) NULL,

dienthoai NVARCHAR(20) NULL,

fax NVARCHAR(20) NULL,

email NVARCHAR(50) NULL

)

CREATE TABLE loaihang

(

maloaihang INT NOT NULL

CONSTRAINT pk_loaihang

PRIMARY KEY(maloaihang),

tenloaihang NVARCHAR(15) NOT NULL

)

CREATE TABLE mathang

(

mahang NVARCHAR(10) NOT NULL

CONSTRAINT pk_mathang

PRIMARY KEY(mahang),

tenhang NVARCHAR(50) NOT NULL,

macongty NVARCHAR(10) NULL ,

maloaihang INT NULL ,

soluong INT NULL,

donvitinh NVARCHAR(20) NULL ,

giahang MONEY NULL

)

CREATE TABLE nhanvien

(

manhanvien NVARCHAR(10) NOT NULL

CONSTRAINT pk_nhanvien

PRIMARY KEY(manhanvien),

ho NVARCHAR(20) NOT NULL ,

ten NVARCHAR(10) NOT NULL ,

ngaysinh DATETIME NULL ,

ngaylamviec DATETIME NULL ,

diachi NVARCHAR(50) NULL ,

dienthoai NVARCHAR(15) NULL ,

luongcoban MONEY NULL ,

phucap MONEY NULL

)

CREATE TABLE khachhang

(

makhachhang NVARCHAR(10) NOT NULL

CONSTRAINT pk_khachhang

PRIMARY KEY(makhachhang),

tencongty NVARCHAR(50) NOT NULL ,

tengiaodich NVARCHAR(30) NOT NULL ,

diachi NVARCHAR(50) NULL ,

email NVARCHAR(30) NULL ,

dienthoai NVARCHAR(15) NULL ,

fax NVARCHAR(15) NULL

)

CREATE TABLE dondathang

(

sohoadon INT NOT NULL

CONSTRAINT pk_dondathang

PRIMARY KEY(sohoadon),

makhachhang NVARCHAR(10) NULL ,

manhanvien NVARCHAR(10) NULL ,

ngaydathang SMALLDATETIME NULL ,

ngaygiaohang SMALLDATETIME NULL ,

ngaychuyenhang SMALLDATETIME NULL ,

noigiaohang NVARCHAR(50) NULL

)

CREATE TABLE chitietdathang

(

sohoadon INT NOT NULL ,

mahang NVARCHAR(10) NOT NULL ,

giaban MONEY NOT NULL ,

soluong SMALLINT NOT NULL ,

mucgiamgia REAL NOT NULL,

CONSTRAINT pk_chitietdathang

PRIMARY KEY(sohoadon,mahang)

)

ALTER TABLE mathang

ADD

CONSTRAINT fk_mathang_loaihang

FOREIGN KEY (maloaihang)

REFERENCES loaihang(maloaihang)

ON DELETE CASCADE ON UPDATE CASCADE ,

CONSTRAINT fk_mathang_nhacungcap

FOREIGN KEY (macongty)

REFERENCES nhacungcap(macongty)

ON DELETE CASCADE ON UPDATE CASCADE

ALTER TABLE dondathang

ADD

CONSTRAINT fk_dondathang_khachhang

FOREIGN KEY (makhachhang)

REFERENCES khachhang(makhachhang)

ON DELETE CASCADE ON UPDATE CASCADE ,

CONSTRAINT fk_dondathang_nhanvien

FOREIGN KEY (manhanvien)

REFERENCES nhanvien(manhanvien)

ON DELETE CASCADE ON UPDATE CASCADE

ALTER TABLE chitietdathang

ADD

CONSTRAINT fk_chitiet_dondathang

FOREIGN KEY (sohoadon)

REFERENCES dondathang(sohoadon)

ON DELETE CASCADE ON UPDATE CASCADE ,

CONSTRAINT fk_chitiet_mathang

FOREIGN KEY (mahang)

REFERENCES mathang(mahang)

ON DELETE CASCADE ON UPDATE CASCADE


1.    Hãy cho biết có những khách hàng nào lại chính là đối tác cung cấp hàng của công ty (tức là có cùng tên giao dịch).

 

SELECT khachhang.tencongty, khachhang.tengiaodich, khachhang.diachi

FROM   khachhang INNER JOIN nhacungcap ON khachhang.tengiaodich = nhacungcap.tengiaodich

/*2.  Những đơn đặt hàng nào yêu cầu giao hàng ngay tại cty đặt hàng và những đơn đó là của công ty nào? */

SELECT dondathang.sohoadon,noigiaohang,khachhang.tencongty,tengiaodich

FROM dondathang INNER JOIN khachhang

ON khachhang.makhachhang = dondathang.makhachhang

WHERE khachhang.diachi = dondathang.noigiaohang

/*3.  Những mặt hàng nào chưa từng được khách hàng đặt mua?*/

SELECT mahang, tenhang FROM mathang

WHERE NOT EXISTS (select mahang from chitietdathang where chitietdathang.mahang=mathang.mahang)

/*4.  Những nhân viên nào của công ty chưa từng lập bất kỳ một hoá đơn đặt hàng nào? */

SELECT ho, ten FROM nhanvien

WHERE NOT EXISTS (select manhanvien from dondathang where dondathang.manhanvien = nhanvien.manhanvien)

/*5.  Trong năm 2003, những mặt hàng nào chỉ được đặt mua đúng một lần*/

SELECT mathang.mahang, tenhang

FROM (mathang INNER JOIN chitietdathang

            ON mathang.mahang=chitietdathang.mahang)

            INNER JOIN dondathang

            ON chitietdathang.sohoadon=dondathang.sohoadon

WHERE year(ngaydathang)=2003

GROUP BY mathang.mahang, tenhang

HAVING COUNT (chitietdathang.mahang)=1

/*6.  Hãy cho biết mỗi một khách hàng đã phải bỏ ra bao nhiêu tiền để đặt mua hàng của công ty? */

SELECT khachhang.makhachhang,tencongty,tengiaodich,

            SUM (soluong*giaban-soluong*giaban*mucgiamgia/100)AS giatien

FROM (khachhang INNER JOIN dondathang

            ON khachhang.makhachhang=dondathang.makhachhang)

            INNER JOIN chitietdathang

            ON dondathang.sohoadon=chitietdathang.sohoadon

GROUP BY khachhang.makhachhang,tencongty,tengiaodich

/*7.  Mỗi một nhân viên của công ty đã lập bao nhiêu đơn đặt hàng (nếu nhân viên chưa hề lập một hoá đơn nào thì cho kết quả là 0) */

SELECT nhanvien.manhanvien,ho,ten,COUNT(sohoadon) AS tongsohoadon

FROM nhanvien LEFT OUTER JOIN dondathang

       ON nhanvien.manhanvien=dondathang.manhanvien

GROUP BY nhanvien.manhanvien,ho,ten

/*8.  Cho biết tổng số tiền hàng mà cửa hàng thu được trong mỗi tháng của năm 2003 (thời được gian tính theo ngày đặt hàng). */

SELECT MONTH(ngaydathang)AS thang,

         SUM (soluong*giaban-soluong*giaban*mucgiamgia/100)AS sotien

FROM dondathang INNER JOIN chitietdathang

       ON dondathang.sohoadon=chitietdathang.sohoadon

WHERE YEAR(ngaydathang)=2003

GROUP BY month (ngaydathang)

/*9.  Hãy cho biết tổng số lượng hàng của mỗi mặt hàng mà cty đã có (tổng số lượng hàng hiện có và đã bán). */

SELECT mathang.mahang,tenhang,mathang.soluong+

         CASE

                  WHEN SUM(chitietdathang.soluong) IS NULL THEN 0

                  ELSE SUM(chitietdathang.soluong)

            END AS tongsoluong

FROM mathang LEFT OUTER JOIN chitietdathang

ON mathang.mahang=chitietdathang.mahang

GROUP BY mathang.mahang, tenhang, mathang.soluong

/*10. Nhân viên nào của cty bán được số lượng hàng nhiều nhất và số lượng hàng bán được của nhân viên này là bao nhiêu?*/

SELECT nhanvien.manhanvien, ho,ten,SUM(soluong)as tongsoluong

FROM (nhanvien INNER JOIN dondathang

        ON nhanvien.manhanvien=dondathang.manhanvien)

        INNER JOIN chitietdathang

        ON dondathang.sohoadon=chitietdathang.sohoadon

GROUP BY nhanvien.manhanvien,ho,ten

HAVING SUM(soluong)>=ALL

         (SELECT sum(soluong)

            FROM (nhanvien INNER JOIN dondathang

                    ON nhanvien.manhanvien=dondathang.manhanvien)

                    INNER JOIN chitietdathang ON

                    dondathang.sohoadon=chitietdathang.sohoadon

            GROUP BY nhanvien.manhanvien,ho,ten)

/*11. Mỗi một đơn đặt hàng đặt mua những mặt hàng nào và tổng số tiền mà mỗi đơn đặt hàng phải trả là bao nhiêu? */

SELECT A.sohoadon,B.mahang,tenhang,

         B.soluong*giaban-B.soluong*giaban*mucgiamgia/100

FROM (dondathang AS A INNER JOIN chitietdathang AS B

        ON A.sohoadon=B.sohoadon)

        INNER JOIN mathang AS C ON B.mathang=C.mathang

ORDER BY A.sohoadon

COMPUTE SUM(B.soluong*giaban-B.soluong*giaban*mucgiamgia/100)

            BY A.sohoadon

/*12. Hãy cho biết mỗi một loại hàng bao gồm những mặt hàng nào, tổng số lượng hàng của mỗi loại và tổng số lượng của tất cả các mặt hàng hiện có trong công ty là bao nhiêu? */

SELECT loaihang.maloaihang,tenloaihang,mahang,tenhang,soluong

FROM loaihang INNER JOIN mathang

       ON loaihang.maloaihang=mathang.maloaihang

ORDER BY loaihang.maloaihang

COMPUTE SUM(soluong)BY loaihang.maloaihang

COMPUTE SUM(soluong)

/*13. Thống kê xem trong năm 2003, mỗi một mặt hàng trong mỗi tháng và trong cả năm bán được với số lượng bao nhiêu.*/

SELECT B.mahang,tenhang,

         SUM(CASE MONTH(ngaydathang)WHEN 1 THEN B.soluong

               ELSE 0 END) AS thang1,

       SUM(CASE MONTH(ngaydathang)WHEN 2 THEN B.soluong

               ELSE 0 END) AS thang2,

         SUM(CASE MONTH(ngaydathang)WHEN 3 THEN B.soluong

               ELSE 0 END) AS thang3,

         SUM(CASE MONTH(ngaydathang)WHEN 4 THEN B.soluong

               ELSE 0 END) AS thang4,

       SUM(CASE MONTH(ngaydathang)WHEN 5 THEN B.soluong

               ELSE 0 END) AS thang5,

       SUM(CASE MONTH(ngaydathang)WHEN 6 THEN B.soluong

               ELSE 0 END) AS thang6,

       SUM(CASE MONTH(ngaydathang)WHEN 7 THEN B.soluong

               ELSE 0 END) AS thang7,

       SUM(CASE MONTH(ngaydathang)WHEN 8 THEN B.soluong

               ELSE 0 END) AS thang8,

       SUM(CASE MONTH(ngaydathang)WHEN 9 THEN B.soluong

               ELSE 0 END) AS thang9,

       SUM(CASE MONTH(ngaydathang)WHEN 10 THEN B.soluong

               ELSE 0 END) AS thang10,

       SUM(CASE MONTH(ngaydathang)WHEN 11 THEN B.soluong

               ELSE 0 END) AS thang11,

       SUM(CASE MONTH(ngaydathang)WHEN 12 THEN B.soluong

               ELSE 0 END) AS thang12,

       SUM (B.soluong) AS canam

FROM (dondathang AS A INNER JOIN chitietdathang AS B

        ON A.sohoadon=B.sohoadon)

        INNER JOIN mathang AS C ON B.mahang=C.mahang

WHERE YEAR(ngaydathang)=2003

GROUP BY B.mathang,tenhang

/*14. Cập nhật lại giá trị NGAYCHUYENHANG của những bản ghi có giá trị NGAYCHUYENHANG chưa xác định (NULL) trong bảng DONDATHANG bằng với giá trị của trường NGAYDATHANG.*/

UPDATE dondathang

SET ngaychuyenhang=ngaydathang

WHERE ngaychuyenhang IS NULL

/*15. Cập nhật giá trị của trường NOIGIAOHANG trong bảng DONDATHANG bằng địa chỉ của khách hàng đối với những đơn đặt hàng chưa xác định được nơi giao hàng (có giá trị trường NOIGIAOHANG bằng NULL)*/

UPDATE dondathang

SET noigiaohang=diachi

FROM khachhang

WHERE dondathang.makhachhang=khachhang.makhachhang

      AND noigiaohang IS NULL

/*16. Cập nhật lại dữ liệu trong bảng KHACHHANG sao cho nếu tên công ty và tên giao dịch của khách hàng trùng với tên công ty và tên giao dịch của một nhà cung cấp nào đó thì địa chỉ, điện thoại, fax và email phải giống nhau.*/

UPDATE khachhang

SET khachhang.diachi=nhacungcap.diachi,

      khachhang.dienthoai=nhacungcap.dienthoai,

      khachhang.fax=nhacungcap.fax,

      khachhang.email=nhacungcap.email

FROM nhacungcap

WHERE khachhang.tencongty=nhacungcap.tencongty

        AND khachhang.tengiaodich=nhacungcap.tengiaodich

/*17. Tăng lương lên gấp rưỡi cho những nhân viên bán được số lượng hàng nhiều hơn 100 trong năm 2003 */

UPDATE nhanvien

SET luongcoban=luongcoban*1.5

WHERE manhanvien=

        (SELECT manhanvien

         FROM dondathang INNER JOIN chitietdathang

       ON dondathang.sohoadon=chitietdathang.sohoadon

       WHERE manhanvien=nhanvien.manhanvien

       GROUP BY manhanvien

       HAVING  SUM(soluong)>100 AND YEAR (ngaygiaohang)=2003)

 

*18.  Tăng phụ cấp lên bằng 50% lương cho những nhân viên bán được hàng nhiều nhất.*/

UPDATE nhanvien

SET phucap=luongcoban/2

WHERE manhanvien IN

                  (select manhanvien

                  from dondathang, chitietdathang

                  where dondathang.sohoadon=chitietdathang.sohoadon

                  group by manhanvien

                  having sum (soluong)>= all

                                         

(select sum (soluong)from dondathang,chitietdathang

                  where dondathang.sohoadon=chitietdathang.sohoadon

                                          group by manhanvien))

/*19. Giảm 25% lương của những nhân viên trong năm 2003 ko lập được bất kỳ đơn đặt hàng nào */

UPDATE nhanvien

SET luongcoban= luongcoban-luongcoban*0.25

WHERE NOT EXISTS (select manhanvien from dondathang where dondathang.manhanvien=nhanvien.manhanvien)

/*20. Giả sử trong bảng DONDATHANG có them trường SOTIEN cho biết số tiền mà khách hàng phải trả trong mỗi dơn đặt hàng. Hãy tính giá trị cho trường này.*/

UPDATE dondathang

SET sotien = (select SUM(soluong*giaban- soluong*giaban*mucgiamgia)

                        from chitietdathang where dondathang.sohoadon=chitietdathang.sohoadon)

/*21. Xoá khỏi bảng MATHANG những mặt hàng có số lượng bằng 0 và không được đặt mua trong bất kỳ đơn đặt hàng nào.*/

DELETE FROM mathang

            WHERE NOT EXISTS (select mahang from chitietdathang where chitietdathang.mahang=mathang.mahang) AND mathang.soluong =0

 

Nguồn bài viết: Dngaz.com

BÌNH LUẬN BÀI VIẾT

Bài viết mới nhất

LIKE BOX

Bài viết được xem nhiều nhất

HỌC HTML