
CREATE TABLE artist (
	"Artist_ID" INTEGER, 
	"Artist" TEXT, 
	"Age" INTEGER, 
	"Famous_Title" TEXT, 
	"Famous_Release_date" TEXT, 
	PRIMARY KEY ("Artist_ID")
)


CREATE TABLE music_festival (
	"ID" INTEGER, 
	"Music_Festival" TEXT, 
	"Date_of_ceremony" TEXT, 
	"Category" TEXT, 
	"Volume" INTEGER, 
	"Result" TEXT, 
	PRIMARY KEY ("ID"), 
	FOREIGN KEY("Volume") REFERENCES volume ("Volume_ID")
)


CREATE TABLE volume (
	"Volume_ID" INTEGER, 
	"Volume_Issue" TEXT, 
	"Issue_Date" TEXT, 
	"Weeks_on_Top" REAL, 
	"Song" TEXT, 
	"Artist_ID" INTEGER, 
	PRIMARY KEY ("Volume_ID"), 
	FOREIGN KEY("Artist_ID") REFERENCES artist ("Artist_ID")
)

CREATE TABLE artist (
	"Artist_ID" INTEGER, 
	"Artist" TEXT, 
	"Age" INTEGER, 
	"Famous_Title" TEXT, 
	"Famous_Release_date" TEXT, 
	PRIMARY KEY ("Artist_ID")
)

/*
2 rows from artist table:
Artist_ID	Artist	Age	Famous_Title	Famous_Release_date
1	Gorgoroth	34	Bergen 1996	November 2007
2	Ophiolatry	35	Transmutation	January 21, 2008
*/


CREATE TABLE music_festival (
	"ID" INTEGER, 
	"Music_Festival" TEXT, 
	"Date_of_ceremony" TEXT, 
	"Category" TEXT, 
	"Volume" INTEGER, 
	"Result" TEXT, 
	PRIMARY KEY ("ID"), 
	FOREIGN KEY("Volume") REFERENCES volume ("Volume_ID")
)

/*
2 rows from music_festival table:
ID	Music_Festival	Date_of_ceremony	Category	Volume	Result
1	34th England Academy Prize	18 February 2011	Best Song	1	Nominated
2	34th Japan Academy Prize	18 February 2011	Best Lyrics	2	Nominated
*/


CREATE TABLE volume (
	"Volume_ID" INTEGER, 
	"Volume_Issue" TEXT, 
	"Issue_Date" TEXT, 
	"Weeks_on_Top" REAL, 
	"Song" TEXT, 
	"Artist_ID" INTEGER, 
	PRIMARY KEY ("Volume_ID"), 
	FOREIGN KEY("Artist_ID") REFERENCES artist ("Artist_ID")
)

/*
2 rows from volume table:
Volume_ID	Volume_Issue	Issue_Date	Weeks_on_Top	Song	Artist_ID
1	45:14 §	27 December 1986 - 10 January	3.0	 The Way	1
2	45:15	17 January	1.0	 Everybody Have Fun Tonight 	2
*/
Show the famous titles of the artists with both volumes that lasted more than 2 weeks on top and volumes that lasted less than 2 weeks on top.
SELECT T1.Famous_Title FROM artist AS T1 JOIN volume AS T2 ON T1.Artist_ID  =  T2.Artist_ID WHERE T2.Weeks_on_Top  >  2 INTERSECT SELECT T1.Famous_Title FROM artist AS T1 JOIN volume AS T2 ON T1.Artist_ID  =  T2.Artist_ID WHERE T2.Weeks_on_Top  <  2
[{'Famous_Title': 'Bergen 1996'}]