Giải bài tập CSDL Bài toán quản lý điện thoại (Đề CH ĐHQGHCM)

Giải bài tập CSDL Bài toán quản lý điện thoại

USE MASTER

GO

DROP DATABASE CH_HT_CUOC_DIEN_THOAI

GO

CREATE DATABASE CH_HT_CUOC_DIEN_THOAI

GO

USE CH_HT_CUOC_DIEN_THOAI

GO

CREATE TABLE THEDIENTHOAI

(

       SOTHE NVARCHAR(100) PRIMARY KEY,

       MENHGIA NVARCHAR(500),

       HANSUDUNG DATETIME

)

GO

CREATE TABLE SODIENTHOAI

(

       SODT NVARCHAR(20) PRIMARY KEY,

       NGAYDK DATETIME,

       SOTIEN MONEY,

       NGAYHH DATETIME

)

GO

CREATE TABLE NAPTIEN

(

       SODT NVARCHAR(20),

       SOTHE NVARCHAR(100),

       NGAYNAP DATETIME,

        CONSTRAINT  FP_NAPTIEN PRIMARY KEY (SODT,SOTHE)

)

GO

ALTER TABLE NAPTIEN

ADD CONSTRAINT FK_THEDIENTHOAI_NAPTIEN FOREIGN KEY (SOTHE) REFERENCES THEDIENTHOAI(SOTHE)

GO

ALTER TABLE NAPTIEN

ADD CONSTRAINT FK_SODIENTHOAI_NAPTIEN FOREIGN KEY (SODT) REFERENCES SODIENTHOAI(SODT)

GO

CREATE TABLE NHANTIN

(

       SODT NVARCHAR(20),

       NGAYGIO DATETIME,

       SODTDEN NVARCHAR(20),

       SOLUONG INT,

        CONSTRAINT  FP_NHANTIN PRIMARY KEY (SODT,NGAYGIO)

)

GO

ALTER TABLE NHANTIN

ADD CONSTRAINT FK_SODIENTHOAI_NHANTIN FOREIGN KEY (SODT) REFERENCES SODIENTHOAI(SODT)

GO

CREATE TABLE CUOCGOI

(

       SODT NVARCHAR(20),

       NGAYGIO DATETIME,

       SODTDEN NVARCHAR(20),

       THOIGIAN INT,

       CONSTRAINT  FP_CUOCGOI PRIMARY KEY (SODT,NGAYGIO)

)

GO

ALTER TABLE CUOCGOI

ADD CONSTRAINT FK_SODIENTHOAI_CUOCGOI FOREIGN KEY (SODT) REFERENCES SODIENTHOAI(SODT)

GO

------------------------------------INSERT INTO TABLE---------------------------------------------

INSERT INTO THEDIENTHOAI VALUES('1900571085','50000','6/30/2014')

GO

INSERT INTO THEDIENTHOAI VALUES('1900571086','100000','6/28/2014')

GO

INSERT INTO THEDIENTHOAI VALUES('1900571087','20000','6/29/2014')

GO

INSERT INTO THEDIENTHOAI VALUES('1900571088','10000','6/29/2014')

GO

INSERT INTO THEDIENTHOAI VALUES('1900571089','200000','6/29/2014')

GO

-----

INSERT INTO SODIENTHOAI VALUES('0907190179','6/1/2014',50000,'9/2/2014')

GO

INSERT INTO SODIENTHOAI VALUES('0907179179','6/1/2014',100000,'8/2/2014')

GO

INSERT INTO SODIENTHOAI VALUES('0907999979','6/1/2014',200,'6/30/2014')

GO

INSERT INTO SODIENTHOAI VALUES('0907439479','6/1/2014',10000,'7/2/2014')

GO

INSERT INTO SODIENTHOAI VALUES('0907439279','6/1/2014',100,'7/2/2014')

GO

INSERT INTO SODIENTHOAI VALUES('0907439379','6/1/2014',100,'6/2/2014')

GO

-----

INSERT INTO NAPTIEN VALUES('0907190179','1900571089','6/14/2014')

GO

INSERT INTO NAPTIEN VALUES('0907179179','1900571086','6/15/2014')

GO

INSERT INTO NAPTIEN VALUES('0907999979','1900571087','6/16/2014')

GO

INSERT INTO NAPTIEN VALUES('0907439479','1900571089','6/11/2014')

GO

-----

INSERT INTO NHANTIN VALUES('0907439479','6/10/2014','0907179179','15')

GO

INSERT INTO NHANTIN VALUES('0907999979','6/11/2014','0907179179','5')

GO

INSERT INTO NHANTIN VALUES('0907190179','6/12/2014','0907179179','20')

GO

INSERT INTO NHANTIN VALUES('0907439479','6/14/2014','0907179179','31')

GO

------

INSERT INTO CUOCGOI VALUES('0907439479','5/14/2014','0907179179','10')

GO

INSERT INTO CUOCGOI VALUES('0907439479','6/12/2014','0907179179','18000')

GO

INSERT INTO CUOCGOI VALUES('0907999979','6/11/2013','0907179179','5')

GO

INSERT INTO CUOCGOI VALUES('0907999979','6/12/2013','0907179179','510')

GO

INSERT INTO CUOCGOI VALUES('0907439479','6/10/2014','0907179179','15000')

GO

INSERT INTO CUOCGOI VALUES('0907190179','6/11/2014','0907179179','30999')

GO

--A) Liệt kê danh sách các số điện thoại chưa hết hạn nghe có tài khoản nhỏ hơn 1000 vnd?

--B) Liệt kê danh sách các số điện thoại mà trong tháng 6 năm 2014 có tổng thời gian gọi đi lớn hơn 500'?

------------------GIẢI---------------------

--A/

SELECT SODT FROM SODIENTHOAI

WHERE SOTIEN<1000

AND NGAYHH>GETDATE()

--B/

SELECT SODT,SUM(THOIGIAN/60) AS N'THỜI GIAN' FROM CUOCGOI WHERE MONTH(NGAYGIO)=6

AND YEAR(NGAYGIO)=2014  GROUP BY SODT

HAVING SUM(THOIGIAN/60)>=ALL(SELECT 500)