USE my_movie_database;DROP TABLE IF EXISTS studio;
CREATE TABLE studio (name char(30) NOT NULL DEFAULT '',address varchar(100) DEFAULT NULL,presC int(11) DEFAULT NULL,PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO studio VALUES ('MGM','MGM Boulevard',123),('Fox','Fox Boulevard',199),('Disney','Disney Boulevard',222),('USA Entertainm','USA Entertainm Boulevard',333),('Paramount','Paramount Boulevard',555);DROP TABLE IF EXISTS movieexec;
CREATE TABLE movieexec (name char(30) DEFAULT NULL,address varchar(100) DEFAULT NULL,cert int(11) NOT NULL DEFAULT '0',netWorth int(11) DEFAULT NULL,PRIMARY KEY (`cert`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO movieexec VALUES ('Calvin Coolidge','Fast Lane',123,20000000),('Merv Griffin','Riot Rd.',199,112000000),('Stephen Spielberg','123 ET road',222,100000000),('Ted Turner','Turner Av.',333,125000000),('George Lucas','Oak Rd.',555,200000000),('Jane Fonda','Turner Av.',567,200000000);DROP TABLE IF EXISTS movies;
CREATE TABLE movies (title char(100) NOT NULL DEFAULT '',year int(11) NOT NULL DEFAULT '0',length int(11) DEFAULT NULL,movieType char(10) DEFAULT NULL,studioName char(30) DEFAULT NULL,producerC int(11) DEFAULT NULL,PRIMARY KEY (title,year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO movies VALUES ('Empire Strikes Back',1980,111,'drama','Fox',555),('Gone With the Wind',1938,238,'drama','MGM',123),('Logan\'s run',1977,120,'drama','MGM',888),('Pretty Woman',1990,119,'drama','Disney',999),('Star Trek',1979,132,'sciFic','Paramount',444),('Star Trek: Nemesis',2002,116,'sciFic','Paramount',321),('Star Wars',1977,124,'sciFic','Fox',555),('Terms of Endearment',1983,132,'drama','MGM',123),('The Man Who Wasn\'t There',2001,116,'comedy','USA Entertainm.',777),('The Usual Suspects',1995,106,'drama','MGM',999);
INSERT INTO movies(title,year) VALUES ('Star Wars',2017);
insert into movies(title,year,movieType,studioName)
values('Star Wars',2015,'sciFic','FOX');DROP TABLE IF EXISTS moviestar;
CREATE TABLE moviestar (name char(30) NOT NULL DEFAULT '',address varchar(255) DEFAULT NULL,gender char(1) DEFAULT NULL,birthdate date DEFAULT NULL,PRIMARY KEY (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO moviestar VALUES ('Alec Baldwin','Baldwin Av.','M','1977-06-07'),('Debra Winger','Baldwin Av.','F','1978-05-06'),('Harrison Ford','Prefect Rd.','M','1955-05-05'),('Jack Nicholson','X path','M','1949-05-05'),('Jane Fonda','Turner Av.','F','1977-07-07');DROP TABLE IF EXISTS starsin;
CREATE TABLE starsin (movieTitle char(100) NOT NULL DEFAULT '',movieYear int(11) NOT NULL DEFAULT '0',starName char(30) NOT NULL DEFAULT '',PRIMARY KEY (movieTitle,movieYear,starName)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO starsin VALUES ('Empire Strikes Back',1980,'Harrison Ford'),('Star Wars',1977,'Carrie Fisher'),('Star Wars',1977,'Harrison Ford'),('Star Wars',1977,'Mark Hamill'),('Star Wars',2017,'Carrie Fisher'),('Terms of Endearment',1983,'Debra Winger'),('Terms of Endearment',1983,'Jack Nicholson'),('The Usual Suspects',1995,'Kevin Spacey');
3）
4） 輸入代碼如下：
CREATE TABLE `銷售表` (`產(chǎn)品名稱` varchar(20) NULL,`數(shù)量` int(11) NOT NULL,`是否贈(zèng)品` bit(1) NOT NULL
);
5） 輸入代碼如下：
INSERT INTO `銷售表`(`產(chǎn)品名稱`, `數(shù)量`, `是否贈(zèng)品`) VALUES ('可口可樂', 100, b'1');
INSERT INTO `銷售表`(`產(chǎn)品名稱`, `數(shù)量`, `是否贈(zèng)品`) VALUES ('石榴', 20, b'0');
INSERT INTO `銷售表`(`產(chǎn)品名稱`, `數(shù)量`, `是否贈(zèng)品`) VALUES ('可口可樂', 50, b'0');
INSERT INTO `銷售表`(`產(chǎn)品名稱`, `數(shù)量`, `是否贈(zèng)品`) VALUES ('百事可樂', 120, b'1');
INSERT INTO `銷售表`(`產(chǎn)品名稱`, `數(shù)量`, `是否贈(zèng)品`) VALUES ('百事可樂', 30, b'0');
6） 輸入代碼如下：
SELECTt.產(chǎn)品名稱,COALESCE ( a.銷售數(shù)量, 0 ) AS 銷售數(shù)量,COALESCE ( b.贈(zèng)送數(shù)量, 0 ) AS 贈(zèng)送數(shù)量
FROM( SELECT DISTINCT 產(chǎn)品名稱 FROM 銷售表 ) tLEFT JOIN ( SELECT 產(chǎn)品名稱, COALESCE ( SUM( 數(shù)量 ), 0 ) AS 銷售數(shù)量 FROM 銷售表 WHERE 是否贈(zèng)品 = 0 GROUP BY 產(chǎn)品名稱 ) a ON t.產(chǎn)品名稱 = a.產(chǎn)品名稱LEFT JOIN ( SELECT 產(chǎn)品名稱, COALESCE ( SUM( 數(shù)量 ), 0 ) AS 贈(zèng)送數(shù)量 FROM 銷售表 WHERE 是否贈(zèng)品 = 1 GROUP BY 產(chǎn)品名稱 ) b ON t.產(chǎn)品名稱 = b.產(chǎn)品名稱;
