
CREATE TABLE "Album" (
	"AlbumId" INTEGER NOT NULL, 
	"Title" VARCHAR(160) NOT NULL, 
	"ArtistId" INTEGER NOT NULL, 
	PRIMARY KEY ("AlbumId"), 
	FOREIGN KEY("ArtistId") REFERENCES "Artist" ("ArtistId")
)


CREATE TABLE "Artist" (
	"ArtistId" INTEGER NOT NULL, 
	"Name" VARCHAR(120) DEFAULT NULL, 
	PRIMARY KEY ("ArtistId")
)


CREATE TABLE "Customer" (
	"CustomerId" INTEGER NOT NULL, 
	"FirstName" VARCHAR(40) NOT NULL, 
	"LastName" VARCHAR(20) NOT NULL, 
	"Company" VARCHAR(80) DEFAULT NULL, 
	"Address" VARCHAR(70) DEFAULT NULL, 
	"City" VARCHAR(40) DEFAULT NULL, 
	"State" VARCHAR(40) DEFAULT NULL, 
	"Country" VARCHAR(40) DEFAULT NULL, 
	"PostalCode" VARCHAR(10) DEFAULT NULL, 
	"Phone" VARCHAR(24) DEFAULT NULL, 
	"Fax" VARCHAR(24) DEFAULT NULL, 
	"Email" VARCHAR(60) NOT NULL, 
	"SupportRepId" INTEGER DEFAULT NULL, 
	PRIMARY KEY ("CustomerId"), 
	FOREIGN KEY("SupportRepId") REFERENCES "Employee" ("EmployeeId")
)


CREATE TABLE "Employee" (
	"EmployeeId" INTEGER NOT NULL, 
	"LastName" VARCHAR(20) NOT NULL, 
	"FirstName" VARCHAR(20) NOT NULL, 
	"Title" VARCHAR(30) DEFAULT NULL, 
	"ReportsTo" INTEGER DEFAULT NULL, 
	"BirthDate" DATETIME DEFAULT NULL, 
	"HireDate" DATETIME DEFAULT NULL, 
	"Address" VARCHAR(70) DEFAULT NULL, 
	"City" VARCHAR(40) DEFAULT NULL, 
	"State" VARCHAR(40) DEFAULT NULL, 
	"Country" VARCHAR(40) DEFAULT NULL, 
	"PostalCode" VARCHAR(10) DEFAULT NULL, 
	"Phone" VARCHAR(24) DEFAULT NULL, 
	"Fax" VARCHAR(24) DEFAULT NULL, 
	"Email" VARCHAR(60) DEFAULT NULL, 
	PRIMARY KEY ("EmployeeId"), 
	FOREIGN KEY("ReportsTo") REFERENCES "Employee" ("EmployeeId")
)


CREATE TABLE "Genre" (
	"GenreId" INTEGER NOT NULL, 
	"Name" VARCHAR(120) DEFAULT NULL, 
	PRIMARY KEY ("GenreId")
)


CREATE TABLE "Invoice" (
	"InvoiceId" INTEGER NOT NULL, 
	"CustomerId" INTEGER NOT NULL, 
	"InvoiceDate" DATETIME NOT NULL, 
	"BillingAddress" VARCHAR(70) DEFAULT NULL, 
	"BillingCity" VARCHAR(40) DEFAULT NULL, 
	"BillingState" VARCHAR(40) DEFAULT NULL, 
	"BillingCountry" VARCHAR(40) DEFAULT NULL, 
	"BillingPostalCode" VARCHAR(10) DEFAULT NULL, 
	"Total" DECIMAL(10, 2) NOT NULL, 
	PRIMARY KEY ("InvoiceId"), 
	FOREIGN KEY("CustomerId") REFERENCES "Customer" ("CustomerId")
)


CREATE TABLE "InvoiceLine" (
	"InvoiceLineId" INTEGER NOT NULL, 
	"InvoiceId" INTEGER NOT NULL, 
	"TrackId" INTEGER NOT NULL, 
	"UnitPrice" DECIMAL(10, 2) NOT NULL, 
	"Quantity" INTEGER NOT NULL, 
	PRIMARY KEY ("InvoiceLineId"), 
	FOREIGN KEY("TrackId") REFERENCES "Track" ("TrackId"), 
	FOREIGN KEY("InvoiceId") REFERENCES "Invoice" ("InvoiceId")
)


CREATE TABLE "MediaType" (
	"MediaTypeId" INTEGER NOT NULL, 
	"Name" VARCHAR(120) DEFAULT NULL, 
	PRIMARY KEY ("MediaTypeId")
)


CREATE TABLE "Playlist" (
	"PlaylistId" INTEGER NOT NULL, 
	"Name" VARCHAR(120) DEFAULT NULL, 
	PRIMARY KEY ("PlaylistId")
)


CREATE TABLE "PlaylistTrack" (
	"PlaylistId" INTEGER NOT NULL, 
	"TrackId" INTEGER NOT NULL, 
	PRIMARY KEY ("PlaylistId", "TrackId"), 
	FOREIGN KEY("TrackId") REFERENCES "Track" ("TrackId"), 
	FOREIGN KEY("PlaylistId") REFERENCES "Playlist" ("PlaylistId")
)


CREATE TABLE "Track" (
	"TrackId" INTEGER NOT NULL, 
	"Name" VARCHAR(200) NOT NULL, 
	"AlbumId" INTEGER DEFAULT NULL, 
	"MediaTypeId" INTEGER NOT NULL, 
	"GenreId" INTEGER DEFAULT NULL, 
	"Composer" VARCHAR(220) DEFAULT NULL, 
	"Milliseconds" INTEGER NOT NULL, 
	"Bytes" INTEGER DEFAULT NULL, 
	"UnitPrice" DECIMAL(10, 2) NOT NULL, 
	PRIMARY KEY ("TrackId"), 
	FOREIGN KEY("MediaTypeId") REFERENCES "MediaType" ("MediaTypeId"), 
	FOREIGN KEY("GenreId") REFERENCES "Genre" ("GenreId"), 
	FOREIGN KEY("AlbumId") REFERENCES "Album" ("AlbumId")
)

CREATE TABLE "Album" (
	"AlbumId" INTEGER NOT NULL, 
	"Title" VARCHAR(160) NOT NULL, 
	"ArtistId" INTEGER NOT NULL, 
	PRIMARY KEY ("AlbumId"), 
	FOREIGN KEY("ArtistId") REFERENCES "Artist" ("ArtistId")
)

/*
2 rows from Album table:
AlbumId	Title	ArtistId
1	For Those About To Rock We Salute You	1
2	Balls to the Wall	2
*/


CREATE TABLE "Artist" (
	"ArtistId" INTEGER NOT NULL, 
	"Name" VARCHAR(120) DEFAULT NULL, 
	PRIMARY KEY ("ArtistId")
)

/*
2 rows from Artist table:
ArtistId	Name
1	AC/DC
2	Accept
*/


CREATE TABLE "Customer" (
	"CustomerId" INTEGER NOT NULL, 
	"FirstName" VARCHAR(40) NOT NULL, 
	"LastName" VARCHAR(20) NOT NULL, 
	"Company" VARCHAR(80) DEFAULT NULL, 
	"Address" VARCHAR(70) DEFAULT NULL, 
	"City" VARCHAR(40) DEFAULT NULL, 
	"State" VARCHAR(40) DEFAULT NULL, 
	"Country" VARCHAR(40) DEFAULT NULL, 
	"PostalCode" VARCHAR(10) DEFAULT NULL, 
	"Phone" VARCHAR(24) DEFAULT NULL, 
	"Fax" VARCHAR(24) DEFAULT NULL, 
	"Email" VARCHAR(60) NOT NULL, 
	"SupportRepId" INTEGER DEFAULT NULL, 
	PRIMARY KEY ("CustomerId"), 
	FOREIGN KEY("SupportRepId") REFERENCES "Employee" ("EmployeeId")
)

/*
2 rows from Customer table:
CustomerId	FirstName	LastName	Company	Address	City	State	Country	PostalCode	Phone	Fax	Email	SupportRepId
1	Luís	Gonçalves	Embraer - Empresa Brasileira de Aeronáutica S.A.	Av. Brigadeiro Faria Lima, 2170	São José dos Campos	SP	Brazil	12227-000	+55 (12) 3923-5555	+55 (12) 3923-5566	luisg@embraer.com.br	3
2	Leonie	Köhler	None	Theodor-Heuss-Straße 34	Stuttgart	None	Germany	70174	+49 0711 2842222	None	leonekohler@surfeu.de	5
*/


CREATE TABLE "Employee" (
	"EmployeeId" INTEGER NOT NULL, 
	"LastName" VARCHAR(20) NOT NULL, 
	"FirstName" VARCHAR(20) NOT NULL, 
	"Title" VARCHAR(30) DEFAULT NULL, 
	"ReportsTo" INTEGER DEFAULT NULL, 
	"BirthDate" DATETIME DEFAULT NULL, 
	"HireDate" DATETIME DEFAULT NULL, 
	"Address" VARCHAR(70) DEFAULT NULL, 
	"City" VARCHAR(40) DEFAULT NULL, 
	"State" VARCHAR(40) DEFAULT NULL, 
	"Country" VARCHAR(40) DEFAULT NULL, 
	"PostalCode" VARCHAR(10) DEFAULT NULL, 
	"Phone" VARCHAR(24) DEFAULT NULL, 
	"Fax" VARCHAR(24) DEFAULT NULL, 
	"Email" VARCHAR(60) DEFAULT NULL, 
	PRIMARY KEY ("EmployeeId"), 
	FOREIGN KEY("ReportsTo") REFERENCES "Employee" ("EmployeeId")
)

/*
2 rows from Employee table:
EmployeeId	LastName	FirstName	Title	ReportsTo	BirthDate	HireDate	Address	City	State	Country	PostalCode	Phone	Fax	Email
1	Adams	Andrew	General Manager	None	1962-02-18 00:00:00	2002-08-14 00:00:00	11120 Jasper Ave NW	Edmonton	AB	Canada	T5K 2N1	+1 (780) 428-9482	+1 (780) 428-3457	andrew@chinookcorp.com
2	Edwards	Nancy	Sales Manager	1	1958-12-08 00:00:00	2002-05-01 00:00:00	825 8 Ave SW	Calgary	AB	Canada	T2P 2T3	+1 (403) 262-3443	+1 (403) 262-3322	nancy@chinookcorp.com
*/


CREATE TABLE "Genre" (
	"GenreId" INTEGER NOT NULL, 
	"Name" VARCHAR(120) DEFAULT NULL, 
	PRIMARY KEY ("GenreId")
)

/*
2 rows from Genre table:
GenreId	Name
1	Rock
2	Jazz
*/


CREATE TABLE "Invoice" (
	"InvoiceId" INTEGER NOT NULL, 
	"CustomerId" INTEGER NOT NULL, 
	"InvoiceDate" DATETIME NOT NULL, 
	"BillingAddress" VARCHAR(70) DEFAULT NULL, 
	"BillingCity" VARCHAR(40) DEFAULT NULL, 
	"BillingState" VARCHAR(40) DEFAULT NULL, 
	"BillingCountry" VARCHAR(40) DEFAULT NULL, 
	"BillingPostalCode" VARCHAR(10) DEFAULT NULL, 
	"Total" DECIMAL(10, 2) NOT NULL, 
	PRIMARY KEY ("InvoiceId"), 
	FOREIGN KEY("CustomerId") REFERENCES "Customer" ("CustomerId")
)

/*
2 rows from Invoice table:
InvoiceId	CustomerId	InvoiceDate	BillingAddress	BillingCity	BillingState	BillingCountry	BillingPostalCode	Total
1	2	2009-01-01 00:00:00	Theodor-Heuss-Straße 34	Stuttgart	None	Germany	70174	1.98
2	4	2009-01-02 00:00:00	Ullevålsveien 14	Oslo	None	Norway	0171	3.96
*/


CREATE TABLE "InvoiceLine" (
	"InvoiceLineId" INTEGER NOT NULL, 
	"InvoiceId" INTEGER NOT NULL, 
	"TrackId" INTEGER NOT NULL, 
	"UnitPrice" DECIMAL(10, 2) NOT NULL, 
	"Quantity" INTEGER NOT NULL, 
	PRIMARY KEY ("InvoiceLineId"), 
	FOREIGN KEY("TrackId") REFERENCES "Track" ("TrackId"), 
	FOREIGN KEY("InvoiceId") REFERENCES "Invoice" ("InvoiceId")
)

/*
2 rows from InvoiceLine table:
InvoiceLineId	InvoiceId	TrackId	UnitPrice	Quantity
1	1	2	0.99	1
2	1	4	0.99	1
*/


CREATE TABLE "MediaType" (
	"MediaTypeId" INTEGER NOT NULL, 
	"Name" VARCHAR(120) DEFAULT NULL, 
	PRIMARY KEY ("MediaTypeId")
)

/*
2 rows from MediaType table:
MediaTypeId	Name
1	MPEG audio file
2	Protected AAC audio file
*/


CREATE TABLE "Playlist" (
	"PlaylistId" INTEGER NOT NULL, 
	"Name" VARCHAR(120) DEFAULT NULL, 
	PRIMARY KEY ("PlaylistId")
)

/*
2 rows from Playlist table:
PlaylistId	Name
1	Music
2	Movies
*/


CREATE TABLE "PlaylistTrack" (
	"PlaylistId" INTEGER NOT NULL, 
	"TrackId" INTEGER NOT NULL, 
	PRIMARY KEY ("PlaylistId", "TrackId"), 
	FOREIGN KEY("TrackId") REFERENCES "Track" ("TrackId"), 
	FOREIGN KEY("PlaylistId") REFERENCES "Playlist" ("PlaylistId")
)

/*
2 rows from PlaylistTrack table:
PlaylistId	TrackId
1	1
8	1
*/


CREATE TABLE "Track" (
	"TrackId" INTEGER NOT NULL, 
	"Name" VARCHAR(200) NOT NULL, 
	"AlbumId" INTEGER DEFAULT NULL, 
	"MediaTypeId" INTEGER NOT NULL, 
	"GenreId" INTEGER DEFAULT NULL, 
	"Composer" VARCHAR(220) DEFAULT NULL, 
	"Milliseconds" INTEGER NOT NULL, 
	"Bytes" INTEGER DEFAULT NULL, 
	"UnitPrice" DECIMAL(10, 2) NOT NULL, 
	PRIMARY KEY ("TrackId"), 
	FOREIGN KEY("MediaTypeId") REFERENCES "MediaType" ("MediaTypeId"), 
	FOREIGN KEY("GenreId") REFERENCES "Genre" ("GenreId"), 
	FOREIGN KEY("AlbumId") REFERENCES "Album" ("AlbumId")
)

/*
2 rows from Track table:
TrackId	Name	AlbumId	MediaTypeId	GenreId	Composer	Milliseconds	Bytes	UnitPrice
1	For Those About To Rock (We Salute You)	1	1	1	Angus Young, Malcolm Young, Brian Johnson	343719	11170334	0.99
2	Balls to the Wall	2	2	1	None	342562	5510424	0.99
*/
What are the first names and support rep ids for employees serving 10 or more customers?
SELECT T1.FirstName ,  T1.SupportRepId FROM CUSTOMER AS T1 JOIN EMPLOYEE AS T2 ON T1.SupportRepId  =  T2.EmployeeId GROUP BY T1.SupportRepId HAVING COUNT(*)  >=  10
[{'FirstName': 'Luís', 'SupportRepId': 3}, {'FirstName': 'Bjørn', 'SupportRepId': 4}, {'FirstName': 'Leonie', 'SupportRepId': 5}]