
CREATE TABLE city (
	"ID" INTEGER NOT NULL, 
	"Name" CHAR(35) DEFAULT '' NOT NULL, 
	"CountryCode" CHAR(3) DEFAULT '' NOT NULL, 
	"District" CHAR(20) DEFAULT '' NOT NULL, 
	"Population" INTEGER DEFAULT '0' NOT NULL, 
	PRIMARY KEY ("ID"), 
	FOREIGN KEY("CountryCode") REFERENCES country ("Code")
)


CREATE TABLE country (
	"Code" CHAR(3) DEFAULT '' NOT NULL, 
	"Name" CHAR(52) DEFAULT '' NOT NULL, 
	"Continent" TEXT DEFAULT 'Asia' NOT NULL, 
	"Region" CHAR(26) DEFAULT '' NOT NULL, 
	"SurfaceArea" FLOAT DEFAULT '0.00' NOT NULL, 
	"IndepYear" INTEGER DEFAULT NULL, 
	"Population" INTEGER DEFAULT '0' NOT NULL, 
	"LifeExpectancy" FLOAT DEFAULT NULL, 
	"GNP" FLOAT DEFAULT NULL, 
	"GNPOld" FLOAT DEFAULT NULL, 
	"LocalName" CHAR(45) DEFAULT '' NOT NULL, 
	"GovernmentForm" CHAR(45) DEFAULT '' NOT NULL, 
	"HeadOfState" CHAR(60) DEFAULT NULL, 
	"Capital" INTEGER DEFAULT NULL, 
	"Code2" CHAR(2) DEFAULT '' NOT NULL, 
	PRIMARY KEY ("Code")
)


CREATE TABLE countrylanguage (
	"CountryCode" CHAR(3) DEFAULT '' NOT NULL, 
	"Language" CHAR(30) DEFAULT '' NOT NULL, 
	"IsOfficial" TEXT DEFAULT 'F' NOT NULL, 
	"Percentage" FLOAT DEFAULT '0.0' NOT NULL, 
	PRIMARY KEY ("CountryCode", "Language"), 
	FOREIGN KEY("CountryCode") REFERENCES country ("Code")
)

CREATE TABLE city (
	"ID" INTEGER NOT NULL, 
	"Name" CHAR(35) DEFAULT '' NOT NULL, 
	"CountryCode" CHAR(3) DEFAULT '' NOT NULL, 
	"District" CHAR(20) DEFAULT '' NOT NULL, 
	"Population" INTEGER DEFAULT '0' NOT NULL, 
	PRIMARY KEY ("ID"), 
	FOREIGN KEY("CountryCode") REFERENCES country ("Code")
)

/*
2 rows from city table:
ID	Name	CountryCode	District	Population
1	Kabul	AFG	Kabol	1780000
2	Qandahar	AFG	Qandahar	237500
*/


CREATE TABLE country (
	"Code" CHAR(3) DEFAULT '' NOT NULL, 
	"Name" CHAR(52) DEFAULT '' NOT NULL, 
	"Continent" TEXT DEFAULT 'Asia' NOT NULL, 
	"Region" CHAR(26) DEFAULT '' NOT NULL, 
	"SurfaceArea" FLOAT DEFAULT '0.00' NOT NULL, 
	"IndepYear" INTEGER DEFAULT NULL, 
	"Population" INTEGER DEFAULT '0' NOT NULL, 
	"LifeExpectancy" FLOAT DEFAULT NULL, 
	"GNP" FLOAT DEFAULT NULL, 
	"GNPOld" FLOAT DEFAULT NULL, 
	"LocalName" CHAR(45) DEFAULT '' NOT NULL, 
	"GovernmentForm" CHAR(45) DEFAULT '' NOT NULL, 
	"HeadOfState" CHAR(60) DEFAULT NULL, 
	"Capital" INTEGER DEFAULT NULL, 
	"Code2" CHAR(2) DEFAULT '' NOT NULL, 
	PRIMARY KEY ("Code")
)

/*
2 rows from country table:
Code	Name	Continent	Region	SurfaceArea	IndepYear	Population	LifeExpectancy	GNP	GNPOld	LocalName	GovernmentForm	HeadOfState	Capital	Code2
ABW	Aruba	North America	Caribbean	193.0000000000	None	103000	78.4000000000	828.0000000000	793.0000000000	Aruba	Nonmetropolitan Territory of The Netherlands	Beatrix	129	AW
AFG	Afghanistan	Asia	Southern and Central Asia	652090.0000000000	1919	22720000	45.9000000000	5976.0000000000	None	Afganistan/Afqanestan	Islamic Emirate	Mohammad Omar	1	AF
*/


CREATE TABLE countrylanguage (
	"CountryCode" CHAR(3) DEFAULT '' NOT NULL, 
	"Language" CHAR(30) DEFAULT '' NOT NULL, 
	"IsOfficial" TEXT DEFAULT 'F' NOT NULL, 
	"Percentage" FLOAT DEFAULT '0.0' NOT NULL, 
	PRIMARY KEY ("CountryCode", "Language"), 
	FOREIGN KEY("CountryCode") REFERENCES country ("Code")
)

/*
2 rows from countrylanguage table:
CountryCode	Language	IsOfficial	Percentage
ABW	Dutch	T	5.3000000000
ABW	English	F	9.5000000000
*/
For the countries founded before 1930, what is the total number of distinct official languages?
SELECT count(DISTINCT T2.Language) FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE  IndepYear  <  1930 AND T2.IsOfficial  =  "T"
[{'count(DISTINCT T2.Language)': 40}]