MySql Task1

项目一 查找重复的电子邮箱

 

CREATE TABLE if not EXISTS Email ( 
ID INT NOT NULL PRIMARY KEY, 
Email VARCHAR ( 40 ) 
);

INSERT INTO Email VALUES('1','a@b.com');
INSERT INTO Email VALUES('2','c@d.com');
INSERT INTO Email VALUES('3','a@b.com');
INSERT INTO Email VALUES('4','a@b.com');

 
 

SELECT Email.Email FROM Email
		WHERE Email.Email IN (
				SELECT Email.Email FROM Email
					GROUP BY Email.Email HAVING COUNT(*) > 1 )

 
 
 

项目二：查找大国

CREATE TABLE World(
name VARCHAR(20) NOT NULL PRIMARY KEY,
continent VARCHAR(50) NOT NULL,
area INT NOT NULL,
population INT NOT NULL,
gdp INT NOT NULL);
		

INSERT INTO World VALUES('Afghanistan','Asia',652230,25500100,20343000);
INSERT INTO World VALUES('Albania','Europe',28748,2831741,12960000);
INSERT INTO World VALUES('Algeria','Africa',2381741,37100000,188681000);
INSERT INTO World VALUES('Andorra','Europe',468,78115,3712000);
INSERT INTO World VALUES('Angola','Africa',1246700,20609294,100990000);

 
 

SELECT name, population, area from World
	WHERE area > 300000
		OR (
			population > 25000000 
			AND gdp > 20000000);