
CREATE TABLE machine (
	"Machine_ID" INTEGER, 
	"Making_Year" INTEGER, 
	"Class" TEXT, 
	"Team" TEXT, 
	"Machine_series" TEXT, 
	value_points REAL, 
	quality_rank INTEGER, 
	PRIMARY KEY ("Machine_ID")
)


CREATE TABLE repair (
	"repair_ID" INTEGER, 
	name TEXT, 
	"Launch_Date" TEXT, 
	"Notes" TEXT, 
	PRIMARY KEY ("repair_ID")
)


CREATE TABLE repair_assignment (
	technician_id INTEGER, 
	"repair_ID" INTEGER, 
	"Machine_ID" INTEGER, 
	PRIMARY KEY (technician_id, "repair_ID", "Machine_ID"), 
	FOREIGN KEY("Machine_ID") REFERENCES machine ("Machine_ID"), 
	FOREIGN KEY("repair_ID") REFERENCES repair ("repair_ID"), 
	FOREIGN KEY(technician_id) REFERENCES technician (technician_id)
)


CREATE TABLE technician (
	technician_id REAL, 
	"Name" TEXT, 
	"Team" TEXT, 
	"Starting_Year" REAL, 
	"Age" INTEGER, 
	PRIMARY KEY (technician_id)
)

CREATE TABLE machine (
	"Machine_ID" INTEGER, 
	"Making_Year" INTEGER, 
	"Class" TEXT, 
	"Team" TEXT, 
	"Machine_series" TEXT, 
	value_points REAL, 
	quality_rank INTEGER, 
	PRIMARY KEY ("Machine_ID")
)

/*
2 rows from machine table:
Machine_ID	Making_Year	Class	Team	Machine_series	value_points	quality_rank
1	1991	125cc	Hero Sports TS- Honda	RS125	105.0	2
2	1992	125cc	Marlboro Pileri - Honda	RS125	57.0	1
*/


CREATE TABLE repair (
	"repair_ID" INTEGER, 
	name TEXT, 
	"Launch_Date" TEXT, 
	"Notes" TEXT, 
	PRIMARY KEY ("repair_ID")
)

/*
2 rows from repair table:
repair_ID	name	Launch_Date	Notes
1	Discoverer	21 Jan 2009	repair Failed. Failed to achieve orbit
2	Discoverer 1	28 Feb 2009	First object in polar orbit
*/


CREATE TABLE repair_assignment (
	technician_id INTEGER, 
	"repair_ID" INTEGER, 
	"Machine_ID" INTEGER, 
	PRIMARY KEY (technician_id, "repair_ID", "Machine_ID"), 
	FOREIGN KEY("Machine_ID") REFERENCES machine ("Machine_ID"), 
	FOREIGN KEY("repair_ID") REFERENCES repair ("repair_ID"), 
	FOREIGN KEY(technician_id) REFERENCES technician (technician_id)
)

/*
2 rows from repair_assignment table:
technician_id	repair_ID	Machine_ID
1	1	1
2	2	2
*/


CREATE TABLE technician (
	technician_id REAL, 
	"Name" TEXT, 
	"Team" TEXT, 
	"Starting_Year" REAL, 
	"Age" INTEGER, 
	PRIMARY KEY (technician_id)
)

/*
2 rows from technician table:
technician_id	Name	Team	Starting_Year	Age
1.0	Joe Sewell	NYY	2012.0	37
2.0	John Brown	NYY	2013.0	36
*/
Show different teams of technicians and the number of technicians in each team.
SELECT Team ,  COUNT(*) FROM technician GROUP BY Team
[{'Team': 'CHC', 'COUNT(*)': 1}, {'Team': 'CLE', 'COUNT(*)': 4}, {'Team': 'CWS', 'COUNT(*)': 2}, {'Team': 'NYY', 'COUNT(*)': 2}, {'Team': 'PHA', 'COUNT(*)': 1}]