database.txt

/*2.1*/
CREATE LOGIN DBA WITH PASSWORD='ABCD1234@', DEFAULT_DATABASE=TEST;
CREATE USER USER_A FOR LOGIN DBA WITH DEFAULT_SCHEMA=DBO;

GRANT SELECT,UPDATE,DELETE,INSERT ON ACTIN TO USER_A;
GRANT SELECT,UPDATE,DELETE,INSERT ON ACTOR TO USER_A;

GRANT SELECT,UPDATE,DELETE,INSERT ON FILM TO USER_A;

GRANT SELECT,UPDATE,DELETE,INSERT ON SHOW TO USER_A;
GRANT SELECT,UPDATE,DELETE,INSERT ON THEATER TO USER_A;

CREATE TABLE FILM
(
	FID INT PRIMARY KEY, 
	FNAME CHAR(30), 
	FTYPE CHAR(10), 
	DNAME CHAR(30), 
	LENGTH INT, 
	IS3D CHAR(1) CHECK (IS3D = 'Y' OR IS3D = 'N'),
	GRADE INT CHECK (GRADE <= 100 AND GRADE >=0 )
)
CREATE TABLE ACTOR
(
	ACTID INT PRIMARY KEY, 
	ANAME CHAR(30), 
	SEX CHAR(2), 
	BYEAR INT
)

CREATE TABLE ACTIN
(
	ACTID INT, 
	FID INT, 
	ISLEADING CHAR(1) CHECK (ISLEADING = 'Y' OR ISLEADING = 'N'),
	GRADE INT CHECK (GRADE <= 100 AND GRADE >=0),
	FOREIGN KEY (ACTID) REFERENCES ACTOR(ACTID),
	FOREIGN KEY (FID) REFERENCES FILM(FID),
	PRIMARY KEY (ACTID,FID)
)

CREATE TABLE THEATER
(
	TID INT PRIMARY KEY, 
	TNAME CHAR(20), 
	TAREA CHAR(20), 
	ADDRESS CHAR(30)
)

CREATE TABLE SHOW
(
	FID INT, 
	TID INT , 
	PRICE INT, 
	YEAR INT , 
	MONTH INT,
	FOREIGN KEY (FID) REFERENCES FILM(FID),
	FOREIGN KEY (TID) REFERENCES THEATER(TID),
	PRIMARY KEY (FID,TID) 
)
/*2.1.2*/


/*2.2.1*/
Bulk insert ACTIN from 'D:\study\DATABASE\参演表.txt' with (FieldterMinator =',', RowterMinator= '\n')

Bulk insert ACTOR from 'D:\study\DATABASE\演员表.txt' with (FieldterMinator =',', RowterMinator= '\n')

Bulk insert FILM from 'D:\study\DATABASE\电影表.txt' with (FieldterMinator =',', RowterMinator= '\n')

Bulk insert SHOW from 'D:\study\DATABASE\放映表.txt' with (FieldterMinator =',', RowterMinator= '\n')

Bulk insert THEATER from 'D:\study\DATABASE\影院表.txt' with (FieldterMinator =',', RowterMinator= '\n')

/*2.2.2*/
INSERT INTO FILM VALUES(20197396, '新喜剧之王', '爱情', '周星驰', 93, 'N', 73);

/*2.2.3*/
UPDATE FILM
SET FTYPE = '喜剧'
WHERE DNAME = '周星驰';

/*2.2.4*/
DElETE FROM FILM
WHERE FNAME = '新喜剧之王';

/*2.2.5*/
SELECT * INTO YOUNG_ACTOR
FROM ACTOR
WHERE BYEAR >= 1990;
/*2.2.6*/
CREATE VIEW ACTIN80
AS 
SELECT ACTOR.ACTID,ACTOR.ANAME,ACTOR.BYEAR,COUNT(*) AS LEADINGNUM,MAX(ACTIN.GRADE) AS MAXGRADE
FROM ACTOR, ACTIN
WHERE ACTOR.ACTID = ACTIN.ACTID AND
BYEAR BETWEEN 1980 AND 1989 AND ACTIN.ISLEADING='Y'
GROUP BY ACTOR.ACTID,ACTOR.ANAME,ACTOR.BYEAR;
/*2.2.7*/
CREATE TABLE TEST_2_7
(
	FID INT, 
	TID INT 
)
INSERT INTO TEST_2_7 VALUES(1,1);
INSERT INTO TEST_2_7 VALUES(1,1);
INSERT INTO TEST_2_7 VALUES(1,1);
INSERT INTO TEST_2_7 VALUES(1,1);
DELETE FROM TEST_2_7 WHERE FID = 1

UPDATE TEST_2_7
SET FID = 2
WHERE FID = 1;

/*2.3.1*/
SELECT *
FROM SHOW
WHERE SHOW.YEAR=2017 AND
	SHOW.FID IN ( 
		SELECT FID
		FROM FILM
		WHERE FNAME LIKE '%后来的我们%')
	AND
	SHOW.TID IN (
	SELECT TID
	FROM THEATER
	WHERE TAREA='洪山区')
	ORDER BY MONTH DESC;
/*2.3.2*/
SELECT *
FROM FILM
WHERE FID NOT IN(
	SELECT DISTINCT FID
	FROM ACTIN
	WHERE ACTID IS NOT NULL
);
/*2.3.3*/
SELECT FID,FNAME,DNAME,GRADE
FROM FILM
WHERE GRADE NOT BETWEEN 80 AND 89;
/*2.3.4*/
SELECT DNAME
FROM FILM
WHERE FTYPE IN ('动作','惊悚');
/*2.3.5*/
SELECT  FILM.FID,FNAME,TNAME,MONTH,YEAR
FROM FILM,SHOW,THEATER
WHERE FILM.FNAME LIKE '%巴霍巴利王%'
	AND FILM.FID = SHOW.FID
	AND SHOW.TID = THEATER.TID;
/*2.3.6*/
SELECT SHOW.FID,COUNT(*) AS NUM
FROM SHOW,FILM
WHERE SHOW.FID = FILM.FID 
GROUP BY SHOW.FID
/*2.3.7*/
SELECT ACTOR.ACTID,AVG(ACTIN.GRADE)AS AVARAGE
FROM ACTIN, ACTOR
WHERE ACTIN.ACTID = ACTOR.ACTID AND
      ISLEADING = 'Y'
GROUP BY ACTOR.ACTID
/*2.3.8*/
SELECT DNAME,MIN(GRADE) AS MING,MAX(GRADE) AS MAXG
FROM FILM
GROUP BY DNAME;
/*2.3.9*/
SELECT DNAME, COUNT(*) AS NUM
FROM FILM
GROUP BY DNAME HAVING COUNT(*) >=2;
/*2.3.10*/
SELECT DNAME,COUNT(*) AS NUM,AVG(GRADE) AS AVERAGE
FROM FILM
WHERE DNAME in (SELECT DNAME
			  FROM FILM
			  WHERE GRADE>80 
			  GROUP BY DNAME
			  HAVING COUNT(*)>=2)
GROUP BY DNAME;
/*2.3.11*/
SELECT DISTINCT FILM.DNAME,ACTOR.ACTID,ACTOR.ANAME
FROM FILM,ACTIN,ACTOR
WHERE FILM.FID=ACTIN.FID AND ACTOR.ACTID=ACTIN.ACTID AND 
	  FILM.DNAME IN
	   (SELECT DNAME
		FROM FILM
		GROUP BY DNAME
		 HAVING COUNT(*)>=2);
/*2.3.12*/
SELECT FILM.FID,FILM.FNAME,FILM.DNAME
FROM FILM
WHERE FILM.FID NOT IN (
	SELECT FILM.FID
	FROM FILM,SHOW
	WHERE FILM.FID=SHOW.FID AND SHOW.YEAR<=2017
	GROUP BY FILM.FID,FILM.FNAME,FILM.DNAME
)
/*2.3.13*/
SELECT FID
FROM SHOW
GROUP BY FID
HAVING COUNT(*)=(SELECT COUNT(*) FROM THEATER);
/*2.3.14*/
SELECT FNAME,YEAR,MONTH,TID
FROM FILM AS A ,SHOW AS B 
WHERE A.FID=B.FID AND GRADE > 80
	AND EXISTS
   (SELECT *
    FROM FILM,SHOW
    WHERE FILM.FID=SHOW.FID AND GRADE > 80 AND A.FNAME = FNAME
    GROUP BY FNAME
    HAVING B.YEAR*12+B.MONTH = MIN(YEAR*12+MONTH));
/*2.3.15*/
SELECT FIRST.TID
FROM SHOW FIRST,SHOW SECOND
WHERE FIRST.FID=30187395 AND SECOND.FID=26942631 AND FIRST.YEAR=SECOND.YEAR AND FIRST.MONTH=SECOND.MONTH
	AND FIRST.TID = SECOND.TID
/*2.3.16*/
SELECT ACTOR.ACTID,ACTOR.ANAME
FROM ACTOR,ACTIN,FILM
WHERE ACTOR.ACTID NOT IN (
SELECT DISTINCT ACTOR.ACTID
FROM ACTOR,ACTIN,FILM
WHERE FILM.FID=ACTIN.FID AND ACTOR.ACTID=ACTIN.ACTID AND FILM.GRADE<85)
GROUP BY ACTOR.ACTID,ACTOR.ANAME;
/*2.3.17*/
SELECT DISTINCT ACTOR.ACTID,ACTOR.ANAME,FILM.FNAME
FROM ACTOR
LEFT JOIN ACTIN
ON ACTOR.ACTID=ACTIN.ACTID
LEFT JOIN FILM
ON ACTIN.FID=FILM.FID;
/*2.3.18*/
SELECT FILM.FID,FILM.FNAME
FROM FILM
WHERE FILM.GRADE IS NULL
GROUP BY FILM.FID,FILM.FNAME
HAVING COUNT(*)>3;
/*2.3.19*/
ALTER TABLE FILM ADD FIRST_TIME VARCHAR(30);
UPDATE FILM
SET FILM.FIRST_TIME = CONVERT(VARCHAR(10) ,FILM_FIRST.YEAR)+'-'+CONVERT(VARCHAR(10) ,FILM_FIRST.MONTH)
FROM FILM,
	(SELECT DISTINCT A.FID,YEAR,MONTH
	FROM FILM AS A ,SHOW AS B 
	WHERE A.FID=B.FID 
	AND EXISTS
   (SELECT *
    FROM FILM,SHOW
    WHERE FILM.FID=SHOW.FID AND A.FNAME = FNAME
    GROUP BY FNAME
    HAVING B.YEAR*12+B.MONTH = MIN(YEAR*12+MONTH))) AS FILM_FIRST(FID,YEAR,MONTH)
WHERE FILM.FID=FILM_FIRST.FID;

SELECT *
FROM FILM
WHERE FILM.FIRST_TIME = CONVERT(VARCHAR(10),YEAR(DATEADD(MONTH,1,GETDATE())))+'-'+CONVERT(VARCHAR(10),MONTH(DATEADD(MONTH,1,GETDATE())))
/*2.3.20*/
SELECT TID,TNAME, 
	CASE TAREA
        WHEN '洪山区' THEN '附近'  
        ELSE '遥远'  
    END AS FAR 
FROM THEATER
/*2.3.21*/