lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE genre(
	g_name varchar2(20) not null,
	rating varchar2(10),
	most_popular_in varchar2(50),
	primary key(g_name)
)

CREATE TABLE artist(
	artist_name varchar2(50) not null,
	country varchar2(20),
	gender varchar2(20),
	preferred_genre varchar2(50),
	constraint a_name primary key(artist_name),
	foreign key(preferred_genre) references genre(g_name) ON DELETE CASCADE
)

CREATE TABLE files(
	f_id number(10) not null,
	artist_name varchar2(50),
	file_size varchar2(20),
	duration varchar2(20),
	formats varchar2(20),
	primary key(f_id),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE
)

CREATE TABLE song(
	song_name varchar2(50),
	artist_name varchar2(50),
	country varchar2(20),
	f_id number(10),
    	genre_is varchar2(20),
	rating number(10) check(rating>0 and rating<11),
	languages varchar2(20),
	releasedate Date, 
	resolution number(10) not null,
	constraint s_name primary key(song_name),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE,
	foreign key(f_id) references files(f_id) ON DELETE CASCADE,
	foreign key(genre_is) references genre(g_name) ON DELETE CASCADE
)

### Question
What is the name and country of origin for each artist who has released a song with a resolution higher than 900?

### SQL
SELECT T1.artist_name ,  T1.country FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name WHERE T2.resolution  >  900 GROUP BY T2.artist_name HAVING count(*)  >=  1
### Database Schema
CREATE TABLE genre(
	g_name varchar2(20) not null,
	rating varchar2(10),
	most_popular_in varchar2(50),
	primary key(g_name)
)

CREATE TABLE artist(
	artist_name varchar2(50) not null,
	country varchar2(20),
	gender varchar2(20),
	preferred_genre varchar2(50),
	constraint a_name primary key(artist_name),
	foreign key(preferred_genre) references genre(g_name) ON DELETE CASCADE
)

CREATE TABLE files(
	f_id number(10) not null,
	artist_name varchar2(50),
	file_size varchar2(20),
	duration varchar2(20),
	formats varchar2(20),
	primary key(f_id),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE
)

CREATE TABLE song(
	song_name varchar2(50),
	artist_name varchar2(50),
	country varchar2(20),
	f_id number(10),
    	genre_is varchar2(20),
	rating number(10) check(rating>0 and rating<11),
	languages varchar2(20),
	releasedate Date, 
	resolution number(10) not null,
	constraint s_name primary key(song_name),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE,
	foreign key(f_id) references files(f_id) ON DELETE CASCADE,
	foreign key(genre_is) references genre(g_name) ON DELETE CASCADE
)

### Question
Find the names and number of works of the three artists who have produced the most songs.

### SQL
SELECT T1.artist_name ,  count(*) FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name GROUP BY T2.artist_name ORDER BY count(*) DESC LIMIT 3
### Database Schema
CREATE TABLE genre(
	g_name varchar2(20) not null,
	rating varchar2(10),
	most_popular_in varchar2(50),
	primary key(g_name)
)

CREATE TABLE artist(
	artist_name varchar2(50) not null,
	country varchar2(20),
	gender varchar2(20),
	preferred_genre varchar2(50),
	constraint a_name primary key(artist_name),
	foreign key(preferred_genre) references genre(g_name) ON DELETE CASCADE
)

CREATE TABLE files(
	f_id number(10) not null,
	artist_name varchar2(50),
	file_size varchar2(20),
	duration varchar2(20),
	formats varchar2(20),
	primary key(f_id),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE
)

CREATE TABLE song(
	song_name varchar2(50),
	artist_name varchar2(50),
	country varchar2(20),
	f_id number(10),
    	genre_is varchar2(20),
	rating number(10) check(rating>0 and rating<11),
	languages varchar2(20),
	releasedate Date, 
	resolution number(10) not null,
	constraint s_name primary key(song_name),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE,
	foreign key(f_id) references files(f_id) ON DELETE CASCADE,
	foreign key(genre_is) references genre(g_name) ON DELETE CASCADE
)

### Question
What are the names of the three artists who have produced the most songs, and how many works did they produce?

### SQL
SELECT T1.artist_name ,  count(*) FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name GROUP BY T2.artist_name ORDER BY count(*) DESC LIMIT 3
### Database Schema
CREATE TABLE genre(
	g_name varchar2(20) not null,
	rating varchar2(10),
	most_popular_in varchar2(50),
	primary key(g_name)
)

CREATE TABLE artist(
	artist_name varchar2(50) not null,
	country varchar2(20),
	gender varchar2(20),
	preferred_genre varchar2(50),
	constraint a_name primary key(artist_name),
	foreign key(preferred_genre) references genre(g_name) ON DELETE CASCADE
)

CREATE TABLE files(
	f_id number(10) not null,
	artist_name varchar2(50),
	file_size varchar2(20),
	duration varchar2(20),
	formats varchar2(20),
	primary key(f_id),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE
)

CREATE TABLE song(
	song_name varchar2(50),
	artist_name varchar2(50),
	country varchar2(20),
	f_id number(10),
    	genre_is varchar2(20),
	rating number(10) check(rating>0 and rating<11),
	languages varchar2(20),
	releasedate Date, 
	resolution number(10) not null,
	constraint s_name primary key(song_name),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE,
	foreign key(f_id) references files(f_id) ON DELETE CASCADE,
	foreign key(genre_is) references genre(g_name) ON DELETE CASCADE
)

### Question
Find the country of origin for the artist who made the least number of songs?

### SQL
SELECT T1.country FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name GROUP BY T2.artist_name ORDER BY count(*) LIMIT 1
### Database Schema
CREATE TABLE genre(
	g_name varchar2(20) not null,
	rating varchar2(10),
	most_popular_in varchar2(50),
	primary key(g_name)
)

CREATE TABLE artist(
	artist_name varchar2(50) not null,
	country varchar2(20),
	gender varchar2(20),
	preferred_genre varchar2(50),
	constraint a_name primary key(artist_name),
	foreign key(preferred_genre) references genre(g_name) ON DELETE CASCADE
)

CREATE TABLE files(
	f_id number(10) not null,
	artist_name varchar2(50),
	file_size varchar2(20),
	duration varchar2(20),
	formats varchar2(20),
	primary key(f_id),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE
)

CREATE TABLE song(
	song_name varchar2(50),
	artist_name varchar2(50),
	country varchar2(20),
	f_id number(10),
    	genre_is varchar2(20),
	rating number(10) check(rating>0 and rating<11),
	languages varchar2(20),
	releasedate Date, 
	resolution number(10) not null,
	constraint s_name primary key(song_name),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE,
	foreign key(f_id) references files(f_id) ON DELETE CASCADE,
	foreign key(genre_is) references genre(g_name) ON DELETE CASCADE
)

### Question
What country is the artist who made the fewest songs from?

### SQL
SELECT T1.country FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name GROUP BY T2.artist_name ORDER BY count(*) LIMIT 1
### Database Schema
CREATE TABLE genre(
	g_name varchar2(20) not null,
	rating varchar2(10),
	most_popular_in varchar2(50),
	primary key(g_name)
)

CREATE TABLE artist(
	artist_name varchar2(50) not null,
	country varchar2(20),
	gender varchar2(20),
	preferred_genre varchar2(50),
	constraint a_name primary key(artist_name),
	foreign key(preferred_genre) references genre(g_name) ON DELETE CASCADE
)

CREATE TABLE files(
	f_id number(10) not null,
	artist_name varchar2(50),
	file_size varchar2(20),
	duration varchar2(20),
	formats varchar2(20),
	primary key(f_id),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE
)

CREATE TABLE song(
	song_name varchar2(50),
	artist_name varchar2(50),
	country varchar2(20),
	f_id number(10),
    	genre_is varchar2(20),
	rating number(10) check(rating>0 and rating<11),
	languages varchar2(20),
	releasedate Date, 
	resolution number(10) not null,
	constraint s_name primary key(song_name),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE,
	foreign key(f_id) references files(f_id) ON DELETE CASCADE,
	foreign key(genre_is) references genre(g_name) ON DELETE CASCADE
)

### Question
What are the names of the songs whose rating is below the rating of all songs in English?

### SQL
SELECT song_name FROM song WHERE rating  <   (SELECT min(rating) FROM song WHERE languages  =  'english')
### Database Schema
CREATE TABLE genre(
	g_name varchar2(20) not null,
	rating varchar2(10),
	most_popular_in varchar2(50),
	primary key(g_name)
)

CREATE TABLE artist(
	artist_name varchar2(50) not null,
	country varchar2(20),
	gender varchar2(20),
	preferred_genre varchar2(50),
	constraint a_name primary key(artist_name),
	foreign key(preferred_genre) references genre(g_name) ON DELETE CASCADE
)

CREATE TABLE files(
	f_id number(10) not null,
	artist_name varchar2(50),
	file_size varchar2(20),
	duration varchar2(20),
	formats varchar2(20),
	primary key(f_id),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE
)

CREATE TABLE song(
	song_name varchar2(50),
	artist_name varchar2(50),
	country varchar2(20),
	f_id number(10),
    	genre_is varchar2(20),
	rating number(10) check(rating>0 and rating<11),
	languages varchar2(20),
	releasedate Date, 
	resolution number(10) not null,
	constraint s_name primary key(song_name),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE,
	foreign key(f_id) references files(f_id) ON DELETE CASCADE,
	foreign key(genre_is) references genre(g_name) ON DELETE CASCADE
)

### Question
What are the song names for every song whose rating is less than the minimum rating for English songs?

### SQL
SELECT song_name FROM song WHERE rating  <   (SELECT min(rating) FROM song WHERE languages  =  'english')
### Database Schema
CREATE TABLE genre(
	g_name varchar2(20) not null,
	rating varchar2(10),
	most_popular_in varchar2(50),
	primary key(g_name)
)

CREATE TABLE artist(
	artist_name varchar2(50) not null,
	country varchar2(20),
	gender varchar2(20),
	preferred_genre varchar2(50),
	constraint a_name primary key(artist_name),
	foreign key(preferred_genre) references genre(g_name) ON DELETE CASCADE
)

CREATE TABLE files(
	f_id number(10) not null,
	artist_name varchar2(50),
	file_size varchar2(20),
	duration varchar2(20),
	formats varchar2(20),
	primary key(f_id),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE
)

CREATE TABLE song(
	song_name varchar2(50),
	artist_name varchar2(50),
	country varchar2(20),
	f_id number(10),
    	genre_is varchar2(20),
	rating number(10) check(rating>0 and rating<11),
	languages varchar2(20),
	releasedate Date, 
	resolution number(10) not null,
	constraint s_name primary key(song_name),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE,
	foreign key(f_id) references files(f_id) ON DELETE CASCADE,
	foreign key(genre_is) references genre(g_name) ON DELETE CASCADE
)

### Question
What is ids of the songs whose resolution is higher than the resolution of any songs with rating lower than 8?

### SQL
SELECT f_id FROM song WHERE resolution  >  (SELECT max(resolution) FROM song WHERE rating  <  8)
### Database Schema
CREATE TABLE genre(
	g_name varchar2(20) not null,
	rating varchar2(10),
	most_popular_in varchar2(50),
	primary key(g_name)
)

CREATE TABLE artist(
	artist_name varchar2(50) not null,
	country varchar2(20),
	gender varchar2(20),
	preferred_genre varchar2(50),
	constraint a_name primary key(artist_name),
	foreign key(preferred_genre) references genre(g_name) ON DELETE CASCADE
)

CREATE TABLE files(
	f_id number(10) not null,
	artist_name varchar2(50),
	file_size varchar2(20),
	duration varchar2(20),
	formats varchar2(20),
	primary key(f_id),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE
)

CREATE TABLE song(
	song_name varchar2(50),
	artist_name varchar2(50),
	country varchar2(20),
	f_id number(10),
    	genre_is varchar2(20),
	rating number(10) check(rating>0 and rating<11),
	languages varchar2(20),
	releasedate Date, 
	resolution number(10) not null,
	constraint s_name primary key(song_name),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE,
	foreign key(f_id) references files(f_id) ON DELETE CASCADE,
	foreign key(genre_is) references genre(g_name) ON DELETE CASCADE
)

### Question
What is the id of every song that has a resolution higher than that of a song with a rating below 8?

### SQL
SELECT f_id FROM song WHERE resolution  >  (SELECT max(resolution) FROM song WHERE rating  <  8)
### Database Schema
CREATE TABLE genre(
	g_name varchar2(20) not null,
	rating varchar2(10),
	most_popular_in varchar2(50),
	primary key(g_name)
)

CREATE TABLE artist(
	artist_name varchar2(50) not null,
	country varchar2(20),
	gender varchar2(20),
	preferred_genre varchar2(50),
	constraint a_name primary key(artist_name),
	foreign key(preferred_genre) references genre(g_name) ON DELETE CASCADE
)

CREATE TABLE files(
	f_id number(10) not null,
	artist_name varchar2(50),
	file_size varchar2(20),
	duration varchar2(20),
	formats varchar2(20),
	primary key(f_id),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE
)

CREATE TABLE song(
	song_name varchar2(50),
	artist_name varchar2(50),
	country varchar2(20),
	f_id number(10),
    	genre_is varchar2(20),
	rating number(10) check(rating>0 and rating<11),
	languages varchar2(20),
	releasedate Date, 
	resolution number(10) not null,
	constraint s_name primary key(song_name),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE,
	foreign key(f_id) references files(f_id) ON DELETE CASCADE,
	foreign key(genre_is) references genre(g_name) ON DELETE CASCADE
)

### Question
What is ids of the songs whose resolution is higher than the average resolution of songs in modern genre?

### SQL
SELECT f_id FROM song WHERE resolution  >  (SELECT avg(resolution) FROM song WHERE genre_is  =  "modern")
### Database Schema
CREATE TABLE genre(
	g_name varchar2(20) not null,
	rating varchar2(10),
	most_popular_in varchar2(50),
	primary key(g_name)
)

CREATE TABLE artist(
	artist_name varchar2(50) not null,
	country varchar2(20),
	gender varchar2(20),
	preferred_genre varchar2(50),
	constraint a_name primary key(artist_name),
	foreign key(preferred_genre) references genre(g_name) ON DELETE CASCADE
)

CREATE TABLE files(
	f_id number(10) not null,
	artist_name varchar2(50),
	file_size varchar2(20),
	duration varchar2(20),
	formats varchar2(20),
	primary key(f_id),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE
)

CREATE TABLE song(
	song_name varchar2(50),
	artist_name varchar2(50),
	country varchar2(20),
	f_id number(10),
    	genre_is varchar2(20),
	rating number(10) check(rating>0 and rating<11),
	languages varchar2(20),
	releasedate Date, 
	resolution number(10) not null,
	constraint s_name primary key(song_name),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE,
	foreign key(f_id) references files(f_id) ON DELETE CASCADE,
	foreign key(genre_is) references genre(g_name) ON DELETE CASCADE
)

### Question
What are the ids of all songs that have higher resolution of the average resolution in the modern genre?

### SQL
SELECT f_id FROM song WHERE resolution  >  (SELECT avg(resolution) FROM song WHERE genre_is  =  "modern")
### Database Schema
CREATE TABLE genre(
	g_name varchar2(20) not null,
	rating varchar2(10),
	most_popular_in varchar2(50),
	primary key(g_name)
)

CREATE TABLE artist(
	artist_name varchar2(50) not null,
	country varchar2(20),
	gender varchar2(20),
	preferred_genre varchar2(50),
	constraint a_name primary key(artist_name),
	foreign key(preferred_genre) references genre(g_name) ON DELETE CASCADE
)

CREATE TABLE files(
	f_id number(10) not null,
	artist_name varchar2(50),
	file_size varchar2(20),
	duration varchar2(20),
	formats varchar2(20),
	primary key(f_id),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE
)

CREATE TABLE song(
	song_name varchar2(50),
	artist_name varchar2(50),
	country varchar2(20),
	f_id number(10),
    	genre_is varchar2(20),
	rating number(10) check(rating>0 and rating<11),
	languages varchar2(20),
	releasedate Date, 
	resolution number(10) not null,
	constraint s_name primary key(song_name),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE,
	foreign key(f_id) references files(f_id) ON DELETE CASCADE,
	foreign key(genre_is) references genre(g_name) ON DELETE CASCADE
)

### Question
Find the top 3 artists who have the largest number of songs works whose language is Bangla.

### SQL
SELECT T1.artist_name FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name WHERE T2.languages  =  "bangla" GROUP BY T2.artist_name ORDER BY count(*) DESC LIMIT 3
### Database Schema
CREATE TABLE genre(
	g_name varchar2(20) not null,
	rating varchar2(10),
	most_popular_in varchar2(50),
	primary key(g_name)
)

CREATE TABLE artist(
	artist_name varchar2(50) not null,
	country varchar2(20),
	gender varchar2(20),
	preferred_genre varchar2(50),
	constraint a_name primary key(artist_name),
	foreign key(preferred_genre) references genre(g_name) ON DELETE CASCADE
)

CREATE TABLE files(
	f_id number(10) not null,
	artist_name varchar2(50),
	file_size varchar2(20),
	duration varchar2(20),
	formats varchar2(20),
	primary key(f_id),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE
)

CREATE TABLE song(
	song_name varchar2(50),
	artist_name varchar2(50),
	country varchar2(20),
	f_id number(10),
    	genre_is varchar2(20),
	rating number(10) check(rating>0 and rating<11),
	languages varchar2(20),
	releasedate Date, 
	resolution number(10) not null,
	constraint s_name primary key(song_name),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE,
	foreign key(f_id) references files(f_id) ON DELETE CASCADE,
	foreign key(genre_is) references genre(g_name) ON DELETE CASCADE
)

### Question
What are the top 3 artists with the largest number of songs in the language Bangla?

### SQL
SELECT T1.artist_name FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name WHERE T2.languages  =  "bangla" GROUP BY T2.artist_name ORDER BY count(*) DESC LIMIT 3
### Database Schema
CREATE TABLE genre(
	g_name varchar2(20) not null,
	rating varchar2(10),
	most_popular_in varchar2(50),
	primary key(g_name)
)

CREATE TABLE artist(
	artist_name varchar2(50) not null,
	country varchar2(20),
	gender varchar2(20),
	preferred_genre varchar2(50),
	constraint a_name primary key(artist_name),
	foreign key(preferred_genre) references genre(g_name) ON DELETE CASCADE
)

CREATE TABLE files(
	f_id number(10) not null,
	artist_name varchar2(50),
	file_size varchar2(20),
	duration varchar2(20),
	formats varchar2(20),
	primary key(f_id),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE
)

CREATE TABLE song(
	song_name varchar2(50),
	artist_name varchar2(50),
	country varchar2(20),
	f_id number(10),
    	genre_is varchar2(20),
	rating number(10) check(rating>0 and rating<11),
	languages varchar2(20),
	releasedate Date, 
	resolution number(10) not null,
	constraint s_name primary key(song_name),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE,
	foreign key(f_id) references files(f_id) ON DELETE CASCADE,
	foreign key(genre_is) references genre(g_name) ON DELETE CASCADE
)

### Question
List the id, genre and artist name of English songs ordered by rating.

### SQL
SELECT f_id ,  genre_is ,  artist_name FROM song WHERE languages  =   "english" ORDER BY rating
### Database Schema
CREATE TABLE genre(
	g_name varchar2(20) not null,
	rating varchar2(10),
	most_popular_in varchar2(50),
	primary key(g_name)
)

CREATE TABLE artist(
	artist_name varchar2(50) not null,
	country varchar2(20),
	gender varchar2(20),
	preferred_genre varchar2(50),
	constraint a_name primary key(artist_name),
	foreign key(preferred_genre) references genre(g_name) ON DELETE CASCADE
)

CREATE TABLE files(
	f_id number(10) not null,
	artist_name varchar2(50),
	file_size varchar2(20),
	duration varchar2(20),
	formats varchar2(20),
	primary key(f_id),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE
)

CREATE TABLE song(
	song_name varchar2(50),
	artist_name varchar2(50),
	country varchar2(20),
	f_id number(10),
    	genre_is varchar2(20),
	rating number(10) check(rating>0 and rating<11),
	languages varchar2(20),
	releasedate Date, 
	resolution number(10) not null,
	constraint s_name primary key(song_name),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE,
	foreign key(f_id) references files(f_id) ON DELETE CASCADE,
	foreign key(genre_is) references genre(g_name) ON DELETE CASCADE
)

### Question
What is the id, genre, and name of the artist for every English song ordered by ascending rating?

### SQL
SELECT f_id ,  genre_is ,  artist_name FROM song WHERE languages  =   "english" ORDER BY rating
### Database Schema
CREATE TABLE genre(
	g_name varchar2(20) not null,
	rating varchar2(10),
	most_popular_in varchar2(50),
	primary key(g_name)
)

CREATE TABLE artist(
	artist_name varchar2(50) not null,
	country varchar2(20),
	gender varchar2(20),
	preferred_genre varchar2(50),
	constraint a_name primary key(artist_name),
	foreign key(preferred_genre) references genre(g_name) ON DELETE CASCADE
)

CREATE TABLE files(
	f_id number(10) not null,
	artist_name varchar2(50),
	file_size varchar2(20),
	duration varchar2(20),
	formats varchar2(20),
	primary key(f_id),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE
)

CREATE TABLE song(
	song_name varchar2(50),
	artist_name varchar2(50),
	country varchar2(20),
	f_id number(10),
    	genre_is varchar2(20),
	rating number(10) check(rating>0 and rating<11),
	languages varchar2(20),
	releasedate Date, 
	resolution number(10) not null,
	constraint s_name primary key(song_name),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE,
	foreign key(f_id) references files(f_id) ON DELETE CASCADE,
	foreign key(genre_is) references genre(g_name) ON DELETE CASCADE
)

### Question
List the duration, file size and format of songs whose genre is pop, ordered by title?

### SQL
SELECT T1.duration ,  T1.file_size ,  T1.formats FROM files AS T1 JOIN song AS T2 ON T1.f_id  =  T2.f_id WHERE T2.genre_is  =  "pop" ORDER BY T2.song_name
### Database Schema
CREATE TABLE genre(
	g_name varchar2(20) not null,
	rating varchar2(10),
	most_popular_in varchar2(50),
	primary key(g_name)
)

CREATE TABLE artist(
	artist_name varchar2(50) not null,
	country varchar2(20),
	gender varchar2(20),
	preferred_genre varchar2(50),
	constraint a_name primary key(artist_name),
	foreign key(preferred_genre) references genre(g_name) ON DELETE CASCADE
)

CREATE TABLE files(
	f_id number(10) not null,
	artist_name varchar2(50),
	file_size varchar2(20),
	duration varchar2(20),
	formats varchar2(20),
	primary key(f_id),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE
)

CREATE TABLE song(
	song_name varchar2(50),
	artist_name varchar2(50),
	country varchar2(20),
	f_id number(10),
    	genre_is varchar2(20),
	rating number(10) check(rating>0 and rating<11),
	languages varchar2(20),
	releasedate Date, 
	resolution number(10) not null,
	constraint s_name primary key(song_name),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE,
	foreign key(f_id) references files(f_id) ON DELETE CASCADE,
	foreign key(genre_is) references genre(g_name) ON DELETE CASCADE
)

### Question
What is the duration, file size, and song format for every pop song, ordered by title alphabetically?

### SQL
SELECT T1.duration ,  T1.file_size ,  T1.formats FROM files AS T1 JOIN song AS T2 ON T1.f_id  =  T2.f_id WHERE T2.genre_is  =  "pop" ORDER BY T2.song_name
### Database Schema
CREATE TABLE genre(
	g_name varchar2(20) not null,
	rating varchar2(10),
	most_popular_in varchar2(50),
	primary key(g_name)
)

CREATE TABLE artist(
	artist_name varchar2(50) not null,
	country varchar2(20),
	gender varchar2(20),
	preferred_genre varchar2(50),
	constraint a_name primary key(artist_name),
	foreign key(preferred_genre) references genre(g_name) ON DELETE CASCADE
)

CREATE TABLE files(
	f_id number(10) not null,
	artist_name varchar2(50),
	file_size varchar2(20),
	duration varchar2(20),
	formats varchar2(20),
	primary key(f_id),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE
)

CREATE TABLE song(
	song_name varchar2(50),
	artist_name varchar2(50),
	country varchar2(20),
	f_id number(10),
    	genre_is varchar2(20),
	rating number(10) check(rating>0 and rating<11),
	languages varchar2(20),
	releasedate Date, 
	resolution number(10) not null,
	constraint s_name primary key(song_name),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE,
	foreign key(f_id) references files(f_id) ON DELETE CASCADE,
	foreign key(genre_is) references genre(g_name) ON DELETE CASCADE
)

### Question
Find the names of the artists who have produced English songs but have never received rating higher than 8.

### SQL
SELECT DISTINCT artist_name FROM song WHERE languages  =  "english" EXCEPT SELECT DISTINCT artist_name FROM song WHERE rating  >  8
### Database Schema
CREATE TABLE genre(
	g_name varchar2(20) not null,
	rating varchar2(10),
	most_popular_in varchar2(50),
	primary key(g_name)
)

CREATE TABLE artist(
	artist_name varchar2(50) not null,
	country varchar2(20),
	gender varchar2(20),
	preferred_genre varchar2(50),
	constraint a_name primary key(artist_name),
	foreign key(preferred_genre) references genre(g_name) ON DELETE CASCADE
)

CREATE TABLE files(
	f_id number(10) not null,
	artist_name varchar2(50),
	file_size varchar2(20),
	duration varchar2(20),
	formats varchar2(20),
	primary key(f_id),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE
)

CREATE TABLE song(
	song_name varchar2(50),
	artist_name varchar2(50),
	country varchar2(20),
	f_id number(10),
    	genre_is varchar2(20),
	rating number(10) check(rating>0 and rating<11),
	languages varchar2(20),
	releasedate Date, 
	resolution number(10) not null,
	constraint s_name primary key(song_name),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE,
	foreign key(f_id) references files(f_id) ON DELETE CASCADE,
	foreign key(genre_is) references genre(g_name) ON DELETE CASCADE
)

### Question
What are the names of the different artists that have produced a song in English but have never receieved a rating higher than 8?

### SQL
SELECT DISTINCT artist_name FROM song WHERE languages  =  "english" EXCEPT SELECT DISTINCT artist_name FROM song WHERE rating  >  8
### Database Schema
CREATE TABLE genre(
	g_name varchar2(20) not null,
	rating varchar2(10),
	most_popular_in varchar2(50),
	primary key(g_name)
)

CREATE TABLE artist(
	artist_name varchar2(50) not null,
	country varchar2(20),
	gender varchar2(20),
	preferred_genre varchar2(50),
	constraint a_name primary key(artist_name),
	foreign key(preferred_genre) references genre(g_name) ON DELETE CASCADE
)

CREATE TABLE files(
	f_id number(10) not null,
	artist_name varchar2(50),
	file_size varchar2(20),
	duration varchar2(20),
	formats varchar2(20),
	primary key(f_id),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE
)

CREATE TABLE song(
	song_name varchar2(50),
	artist_name varchar2(50),
	country varchar2(20),
	f_id number(10),
    	genre_is varchar2(20),
	rating number(10) check(rating>0 and rating<11),
	languages varchar2(20),
	releasedate Date, 
	resolution number(10) not null,
	constraint s_name primary key(song_name),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE,
	foreign key(f_id) references files(f_id) ON DELETE CASCADE,
	foreign key(genre_is) references genre(g_name) ON DELETE CASCADE
)

### Question
Find the names of the artists who are from Bangladesh and have never received rating higher than 7.

### SQL
SELECT DISTINCT artist_name FROM artist WHERE country  =  "Bangladesh" EXCEPT SELECT DISTINCT artist_name FROM song WHERE rating  >  7
### Database Schema
CREATE TABLE genre(
	g_name varchar2(20) not null,
	rating varchar2(10),
	most_popular_in varchar2(50),
	primary key(g_name)
)

CREATE TABLE artist(
	artist_name varchar2(50) not null,
	country varchar2(20),
	gender varchar2(20),
	preferred_genre varchar2(50),
	constraint a_name primary key(artist_name),
	foreign key(preferred_genre) references genre(g_name) ON DELETE CASCADE
)

CREATE TABLE files(
	f_id number(10) not null,
	artist_name varchar2(50),
	file_size varchar2(20),
	duration varchar2(20),
	formats varchar2(20),
	primary key(f_id),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE
)

CREATE TABLE song(
	song_name varchar2(50),
	artist_name varchar2(50),
	country varchar2(20),
	f_id number(10),
    	genre_is varchar2(20),
	rating number(10) check(rating>0 and rating<11),
	languages varchar2(20),
	releasedate Date, 
	resolution number(10) not null,
	constraint s_name primary key(song_name),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE,
	foreign key(f_id) references files(f_id) ON DELETE CASCADE,
	foreign key(genre_is) references genre(g_name) ON DELETE CASCADE
)

### Question
What are the names of the different artists from Bangladesh who never received a rating higher than a 7?

### SQL
SELECT DISTINCT artist_name FROM artist WHERE country  =  "Bangladesh" EXCEPT SELECT DISTINCT artist_name FROM song WHERE rating  >  7
### Database Schema
CREATE TABLE "mountain" (
"id" int,
"name" text,
"Height" real,
"Prominence" real,
"Range" text,
"Country" text,
primary key("id")
)

CREATE TABLE "camera_lens" (
"id" int,
"brand" text,
"name" text,
"focal_length_mm" real,
"max_aperture" real,
primary key("id")
)

CREATE TABLE "photos" (
"id" int, 
"camera_lens_id" int,
"mountain_id" int,
"color" text, 
"name" text,
primary key("id"),
foreign key("camera_lens_id") references `camera_lens`("id"),
foreign key("mountain_id") references `mountain`("id")
)

### Question
How many camera lenses have a focal length longer than 15 mm?

### SQL
SELECT count(*) FROM camera_lens WHERE focal_length_mm  >  15
### Database Schema
CREATE TABLE "mountain" (
"id" int,
"name" text,
"Height" real,
"Prominence" real,
"Range" text,
"Country" text,
primary key("id")
)

CREATE TABLE "camera_lens" (
"id" int,
"brand" text,
"name" text,
"focal_length_mm" real,
"max_aperture" real,
primary key("id")
)

CREATE TABLE "photos" (
"id" int, 
"camera_lens_id" int,
"mountain_id" int,
"color" text, 
"name" text,
primary key("id"),
foreign key("camera_lens_id") references `camera_lens`("id"),
foreign key("mountain_id") references `mountain`("id")
)

### Question
Find the brand and name for each camera lens, and sort in descending order of maximum aperture.

### SQL
SELECT brand ,  name FROM camera_lens ORDER BY max_aperture DESC
### Database Schema
CREATE TABLE "mountain" (
"id" int,
"name" text,
"Height" real,
"Prominence" real,
"Range" text,
"Country" text,
primary key("id")
)

CREATE TABLE "camera_lens" (
"id" int,
"brand" text,
"name" text,
"focal_length_mm" real,
"max_aperture" real,
primary key("id")
)

CREATE TABLE "photos" (
"id" int, 
"camera_lens_id" int,
"mountain_id" int,
"color" text, 
"name" text,
primary key("id"),
foreign key("camera_lens_id") references `camera_lens`("id"),
foreign key("mountain_id") references `mountain`("id")
)

### Question
List the id, color scheme, and name for all the photos.

### SQL
SELECT id ,  color ,  name FROM photos
### Database Schema
CREATE TABLE "mountain" (
"id" int,
"name" text,
"Height" real,
"Prominence" real,
"Range" text,
"Country" text,
primary key("id")
)

CREATE TABLE "camera_lens" (
"id" int,
"brand" text,
"name" text,
"focal_length_mm" real,
"max_aperture" real,
primary key("id")
)

CREATE TABLE "photos" (
"id" int, 
"camera_lens_id" int,
"mountain_id" int,
"color" text, 
"name" text,
primary key("id"),
foreign key("camera_lens_id") references `camera_lens`("id"),
foreign key("mountain_id") references `mountain`("id")
)

### Question
What are the maximum and average height of the mountains?

### SQL
SELECT max(height) ,  avg(height) FROM mountain
### Database Schema
CREATE TABLE "mountain" (
"id" int,
"name" text,
"Height" real,
"Prominence" real,
"Range" text,
"Country" text,
primary key("id")
)

CREATE TABLE "camera_lens" (
"id" int,
"brand" text,
"name" text,
"focal_length_mm" real,
"max_aperture" real,
primary key("id")
)

CREATE TABLE "photos" (
"id" int, 
"camera_lens_id" int,
"mountain_id" int,
"color" text, 
"name" text,
primary key("id"),
foreign key("camera_lens_id") references `camera_lens`("id"),
foreign key("mountain_id") references `mountain`("id")
)

### Question
What are the average prominence of the mountains in country 'Morocco'?

### SQL
SELECT avg(prominence) FROM mountain WHERE country  =  'Morocco'
### Database Schema
CREATE TABLE "mountain" (
"id" int,
"name" text,
"Height" real,
"Prominence" real,
"Range" text,
"Country" text,
primary key("id")
)

CREATE TABLE "camera_lens" (
"id" int,
"brand" text,
"name" text,
"focal_length_mm" real,
"max_aperture" real,
primary key("id")
)

CREATE TABLE "photos" (
"id" int, 
"camera_lens_id" int,
"mountain_id" int,
"color" text, 
"name" text,
primary key("id"),
foreign key("camera_lens_id") references `camera_lens`("id"),
foreign key("mountain_id") references `mountain`("id")
)

### Question
What are the name, height and prominence of mountains which do not belong to the range 'Aberdare Range'?

### SQL
SELECT name ,  height ,  prominence FROM mountain WHERE range != 'Aberdare Range'
### Database Schema
CREATE TABLE "mountain" (
"id" int,
"name" text,
"Height" real,
"Prominence" real,
"Range" text,
"Country" text,
primary key("id")
)

CREATE TABLE "camera_lens" (
"id" int,
"brand" text,
"name" text,
"focal_length_mm" real,
"max_aperture" real,
primary key("id")
)

CREATE TABLE "photos" (
"id" int, 
"camera_lens_id" int,
"mountain_id" int,
"color" text, 
"name" text,
primary key("id"),
foreign key("camera_lens_id") references `camera_lens`("id"),
foreign key("mountain_id") references `mountain`("id")
)

### Question
What are the id and name of the photos for mountains?

### SQL
SELECT T1.id ,  T1.name FROM mountain AS T1 JOIN photos AS T2 ON T1.id  =  T2.mountain_id WHERE T1.height  >  4000
### Database Schema
CREATE TABLE "mountain" (
"id" int,
"name" text,
"Height" real,
"Prominence" real,
"Range" text,
"Country" text,
primary key("id")
)

CREATE TABLE "camera_lens" (
"id" int,
"brand" text,
"name" text,
"focal_length_mm" real,
"max_aperture" real,
primary key("id")
)

CREATE TABLE "photos" (
"id" int, 
"camera_lens_id" int,
"mountain_id" int,
"color" text, 
"name" text,
primary key("id"),
foreign key("camera_lens_id") references `camera_lens`("id"),
foreign key("mountain_id") references `mountain`("id")
)

### Question
What are the id and name of the mountains that have at least 2 photos?

### SQL
SELECT T1.id ,  T1.name FROM mountain AS T1 JOIN photos AS T2 ON T1.id  =  T2.mountain_id GROUP BY T1.id HAVING count(*)  >=  2
### Database Schema
CREATE TABLE "mountain" (
"id" int,
"name" text,
"Height" real,
"Prominence" real,
"Range" text,
"Country" text,
primary key("id")
)

CREATE TABLE "camera_lens" (
"id" int,
"brand" text,
"name" text,
"focal_length_mm" real,
"max_aperture" real,
primary key("id")
)

CREATE TABLE "photos" (
"id" int, 
"camera_lens_id" int,
"mountain_id" int,
"color" text, 
"name" text,
primary key("id"),
foreign key("camera_lens_id") references `camera_lens`("id"),
foreign key("mountain_id") references `mountain`("id")
)

### Question
What are the names of the cameras that have taken picture of the most mountains?

### SQL
SELECT T2.name FROM photos AS T1 JOIN camera_lens AS T2 ON T1.camera_lens_id  =  T2.id GROUP BY T2.id ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "mountain" (
"id" int,
"name" text,
"Height" real,
"Prominence" real,
"Range" text,
"Country" text,
primary key("id")
)

CREATE TABLE "camera_lens" (
"id" int,
"brand" text,
"name" text,
"focal_length_mm" real,
"max_aperture" real,
primary key("id")
)

CREATE TABLE "photos" (
"id" int, 
"camera_lens_id" int,
"mountain_id" int,
"color" text, 
"name" text,
primary key("id"),
foreign key("camera_lens_id") references `camera_lens`("id"),
foreign key("mountain_id") references `mountain`("id")
)

### Question
What are the names of photos taken with the lens brand 'Sigma' or 'Olympus'?

### SQL
SELECT T1.name FROM camera_lens AS T1 JOIN photos AS T2 ON T2.camera_lens_id  =  T1.id WHERE T1.brand  =  'Sigma' OR T1.brand  =  'Olympus'
### Database Schema
CREATE TABLE "mountain" (
"id" int,
"name" text,
"Height" real,
"Prominence" real,
"Range" text,
"Country" text,
primary key("id")
)

CREATE TABLE "camera_lens" (
"id" int,
"brand" text,
"name" text,
"focal_length_mm" real,
"max_aperture" real,
primary key("id")
)

CREATE TABLE "photos" (
"id" int, 
"camera_lens_id" int,
"mountain_id" int,
"color" text, 
"name" text,
primary key("id"),
foreign key("camera_lens_id") references `camera_lens`("id"),
foreign key("mountain_id") references `mountain`("id")
)

### Question
How many different kinds of lens brands are there?

### SQL
SELECT count(DISTINCT brand) FROM camera_lens
### Database Schema
CREATE TABLE "mountain" (
"id" int,
"name" text,
"Height" real,
"Prominence" real,
"Range" text,
"Country" text,
primary key("id")
)

CREATE TABLE "camera_lens" (
"id" int,
"brand" text,
"name" text,
"focal_length_mm" real,
"max_aperture" real,
primary key("id")
)

CREATE TABLE "photos" (
"id" int, 
"camera_lens_id" int,
"mountain_id" int,
"color" text, 
"name" text,
primary key("id"),
foreign key("camera_lens_id") references `camera_lens`("id"),
foreign key("mountain_id") references `mountain`("id")
)

### Question
How many camera lenses are not used in taking any photos?

### SQL
SELECT count(*) FROM camera_lens WHERE id NOT IN ( SELECT camera_lens_id FROM photos )
### Database Schema
CREATE TABLE "mountain" (
"id" int,
"name" text,
"Height" real,
"Prominence" real,
"Range" text,
"Country" text,
primary key("id")
)

CREATE TABLE "camera_lens" (
"id" int,
"brand" text,
"name" text,
"focal_length_mm" real,
"max_aperture" real,
primary key("id")
)

CREATE TABLE "photos" (
"id" int, 
"camera_lens_id" int,
"mountain_id" int,
"color" text, 
"name" text,
primary key("id"),
foreign key("camera_lens_id") references `camera_lens`("id"),
foreign key("mountain_id") references `mountain`("id")
)

### Question
How many distinct kinds of camera lenses are used to take photos of mountains in the country 'Ethiopia'?

### SQL
SELECT count(DISTINCT T2.camera_lens_id) FROM mountain AS T1 JOIN photos AS T2 ON T1.id  =  T2.mountain_id WHERE T1.country  =  'Ethiopia'
### Database Schema
CREATE TABLE "mountain" (
"id" int,
"name" text,
"Height" real,
"Prominence" real,
"Range" text,
"Country" text,
primary key("id")
)

CREATE TABLE "camera_lens" (
"id" int,
"brand" text,
"name" text,
"focal_length_mm" real,
"max_aperture" real,
primary key("id")
)

CREATE TABLE "photos" (
"id" int, 
"camera_lens_id" int,
"mountain_id" int,
"color" text, 
"name" text,
primary key("id"),
foreign key("camera_lens_id") references `camera_lens`("id"),
foreign key("mountain_id") references `mountain`("id")
)

### Question
List the brands of lenses that took both a picture of mountains with range 'Toubkal Atlas' and a picture of mountains with range 'Lasta Massif'

### SQL
SELECT T3.brand FROM mountain AS T1 JOIN photos AS T2 ON T1.id  =  T2.mountain_id JOIN camera_lens AS T3 ON T2.camera_lens_id  =  T3.id WHERE T1.range  =  'Toubkal Atlas' INTERSECT SELECT T3.brand FROM mountain AS T1 JOIN photos AS T2 ON T1.id  =  T2.mountain_id JOIN camera_lens AS T3 ON T2.camera_lens_id  =  T3.id WHERE T1.range  =  'Lasta Massif'
### Database Schema
CREATE TABLE "mountain" (
"id" int,
"name" text,
"Height" real,
"Prominence" real,
"Range" text,
"Country" text,
primary key("id")
)

CREATE TABLE "camera_lens" (
"id" int,
"brand" text,
"name" text,
"focal_length_mm" real,
"max_aperture" real,
primary key("id")
)

CREATE TABLE "photos" (
"id" int, 
"camera_lens_id" int,
"mountain_id" int,
"color" text, 
"name" text,
primary key("id"),
foreign key("camera_lens_id") references `camera_lens`("id"),
foreign key("mountain_id") references `mountain`("id")
)

### Question
Show the name and prominence of the mountains whose picture is not taken by a lens of brand 'Sigma'.

### SQL
SELECT name ,  prominence FROM mountain EXCEPT SELECT T1.name ,  T1.prominence FROM mountain AS T1 JOIN photos AS T2 ON T1.id  =  T2.mountain_id JOIN camera_lens AS T3 ON T2.camera_lens_id  =  T3.id WHERE T3.brand  =  'Sigma'
### Database Schema
CREATE TABLE "mountain" (
"id" int,
"name" text,
"Height" real,
"Prominence" real,
"Range" text,
"Country" text,
primary key("id")
)

CREATE TABLE "camera_lens" (
"id" int,
"brand" text,
"name" text,
"focal_length_mm" real,
"max_aperture" real,
primary key("id")
)

CREATE TABLE "photos" (
"id" int, 
"camera_lens_id" int,
"mountain_id" int,
"color" text, 
"name" text,
primary key("id"),
foreign key("camera_lens_id") references `camera_lens`("id"),
foreign key("mountain_id") references `mountain`("id")
)

### Question
List the camera lens names containing substring "Digital".

### SQL
SELECT name FROM camera_lens WHERE name LIKE "%Digital%"
### Database Schema
CREATE TABLE "mountain" (
"id" int,
"name" text,
"Height" real,
"Prominence" real,
"Range" text,
"Country" text,
primary key("id")
)

CREATE TABLE "camera_lens" (
"id" int,
"brand" text,
"name" text,
"focal_length_mm" real,
"max_aperture" real,
primary key("id")
)

CREATE TABLE "photos" (
"id" int, 
"camera_lens_id" int,
"mountain_id" int,
"color" text, 
"name" text,
primary key("id"),
foreign key("camera_lens_id") references `camera_lens`("id"),
foreign key("mountain_id") references `mountain`("id")
)

### Question
What is the name of each camera lens and the number of photos taken by it? Order the result by the count of photos.

### SQL
SELECT T1.name ,  count(*) FROM camera_lens AS T1 JOIN photos AS T2 ON T1.id  =  T2.camera_lens_id GROUP BY T1.id ORDER BY count(*)
### Database Schema
CREATE TABLE "program" (
"Program_ID" int,
"Name" text,
"Origin" text,
"Launch" real,
"Owner" text,
PRIMARY KEY ("Program_ID")
)

CREATE TABLE "channel" (
"Channel_ID" int,
"Name" text,
"Owner" text,
"Share_in_percent" real,
"Rating_in_percent" real,
PRIMARY KEY ("Channel_ID")
)

CREATE TABLE "broadcast" (
"Channel_ID" int,
"Program_ID" int,
"Time_of_day" text,
PRIMARY KEY ("Channel_ID","Program_ID"),
FOREIGN KEY (`Channel_ID`) REFERENCES `channel`(`Channel_ID`),
FOREIGN KEY (`Program_ID`) REFERENCES `program`(`Program_ID`)
)

CREATE TABLE "broadcast_share" (
"Channel_ID" int,
"Program_ID" int,
"Date" text,
"Share_in_percent" real,
PRIMARY KEY ("Channel_ID","Program_ID"),
FOREIGN KEY (`Channel_ID`) REFERENCES `channel`(`Channel_ID`),
FOREIGN KEY (`Program_ID`) REFERENCES `program`(`Program_ID`)
)

### Question
Find the names of channels that are not owned by CCTV.

### SQL
SELECT name FROM channel WHERE OWNER != 'CCTV'
### Database Schema
CREATE TABLE "program" (
"Program_ID" int,
"Name" text,
"Origin" text,
"Launch" real,
"Owner" text,
PRIMARY KEY ("Program_ID")
)

CREATE TABLE "channel" (
"Channel_ID" int,
"Name" text,
"Owner" text,
"Share_in_percent" real,
"Rating_in_percent" real,
PRIMARY KEY ("Channel_ID")
)

CREATE TABLE "broadcast" (
"Channel_ID" int,
"Program_ID" int,
"Time_of_day" text,
PRIMARY KEY ("Channel_ID","Program_ID"),
FOREIGN KEY (`Channel_ID`) REFERENCES `channel`(`Channel_ID`),
FOREIGN KEY (`Program_ID`) REFERENCES `program`(`Program_ID`)
)

CREATE TABLE "broadcast_share" (
"Channel_ID" int,
"Program_ID" int,
"Date" text,
"Share_in_percent" real,
PRIMARY KEY ("Channel_ID","Program_ID"),
FOREIGN KEY (`Channel_ID`) REFERENCES `channel`(`Channel_ID`),
FOREIGN KEY (`Program_ID`) REFERENCES `program`(`Program_ID`)
)

### Question
Which channels are not owned by CCTV? Give me the channel names.

### SQL
SELECT name FROM channel WHERE OWNER != 'CCTV'
### Database Schema
CREATE TABLE "program" (
"Program_ID" int,
"Name" text,
"Origin" text,
"Launch" real,
"Owner" text,
PRIMARY KEY ("Program_ID")
)

CREATE TABLE "channel" (
"Channel_ID" int,
"Name" text,
"Owner" text,
"Share_in_percent" real,
"Rating_in_percent" real,
PRIMARY KEY ("Channel_ID")
)

CREATE TABLE "broadcast" (
"Channel_ID" int,
"Program_ID" int,
"Time_of_day" text,
PRIMARY KEY ("Channel_ID","Program_ID"),
FOREIGN KEY (`Channel_ID`) REFERENCES `channel`(`Channel_ID`),
FOREIGN KEY (`Program_ID`) REFERENCES `program`(`Program_ID`)
)

CREATE TABLE "broadcast_share" (
"Channel_ID" int,
"Program_ID" int,
"Date" text,
"Share_in_percent" real,
PRIMARY KEY ("Channel_ID","Program_ID"),
FOREIGN KEY (`Channel_ID`) REFERENCES `channel`(`Channel_ID`),
FOREIGN KEY (`Program_ID`) REFERENCES `program`(`Program_ID`)
)

### Question
List all channel names ordered by their rating in percent from big to small.

### SQL
SELECT name FROM channel ORDER BY rating_in_percent DESC
### Database Schema
CREATE TABLE "program" (
"Program_ID" int,
"Name" text,
"Origin" text,
"Launch" real,
"Owner" text,
PRIMARY KEY ("Program_ID")
)

CREATE TABLE "channel" (
"Channel_ID" int,
"Name" text,
"Owner" text,
"Share_in_percent" real,
"Rating_in_percent" real,
PRIMARY KEY ("Channel_ID")
)

CREATE TABLE "broadcast" (
"Channel_ID" int,
"Program_ID" int,
"Time_of_day" text,
PRIMARY KEY ("Channel_ID","Program_ID"),
FOREIGN KEY (`Channel_ID`) REFERENCES `channel`(`Channel_ID`),
FOREIGN KEY (`Program_ID`) REFERENCES `program`(`Program_ID`)
)

CREATE TABLE "broadcast_share" (
"Channel_ID" int,
"Program_ID" int,
"Date" text,
"Share_in_percent" real,
PRIMARY KEY ("Channel_ID","Program_ID"),
FOREIGN KEY (`Channel_ID`) REFERENCES `channel`(`Channel_ID`),
FOREIGN KEY (`Program_ID`) REFERENCES `program`(`Program_ID`)
)

### Question
Give me a list of all the channel names sorted by the channel rating in descending order.

### SQL
SELECT name FROM channel ORDER BY rating_in_percent DESC
### Database Schema
CREATE TABLE "program" (
"Program_ID" int,
"Name" text,
"Origin" text,
"Launch" real,
"Owner" text,
PRIMARY KEY ("Program_ID")
)

CREATE TABLE "channel" (
"Channel_ID" int,
"Name" text,
"Owner" text,
"Share_in_percent" real,
"Rating_in_percent" real,
PRIMARY KEY ("Channel_ID")
)

CREATE TABLE "broadcast" (
"Channel_ID" int,
"Program_ID" int,
"Time_of_day" text,
PRIMARY KEY ("Channel_ID","Program_ID"),
FOREIGN KEY (`Channel_ID`) REFERENCES `channel`(`Channel_ID`),
FOREIGN KEY (`Program_ID`) REFERENCES `program`(`Program_ID`)
)

CREATE TABLE "broadcast_share" (
"Channel_ID" int,
"Program_ID" int,
"Date" text,
"Share_in_percent" real,
PRIMARY KEY ("Channel_ID","Program_ID"),
FOREIGN KEY (`Channel_ID`) REFERENCES `channel`(`Channel_ID`),
FOREIGN KEY (`Program_ID`) REFERENCES `program`(`Program_ID`)
)

### Question
What is the owner of the channel that has the highest rating ratio?

### SQL
SELECT OWNER FROM channel ORDER BY rating_in_percent DESC LIMIT 1
### Database Schema
CREATE TABLE "program" (
"Program_ID" int,
"Name" text,
"Origin" text,
"Launch" real,
"Owner" text,
PRIMARY KEY ("Program_ID")
)

CREATE TABLE "channel" (
"Channel_ID" int,
"Name" text,
"Owner" text,
"Share_in_percent" real,
"Rating_in_percent" real,
PRIMARY KEY ("Channel_ID")
)

CREATE TABLE "broadcast" (
"Channel_ID" int,
"Program_ID" int,
"Time_of_day" text,
PRIMARY KEY ("Channel_ID","Program_ID"),
FOREIGN KEY (`Channel_ID`) REFERENCES `channel`(`Channel_ID`),
FOREIGN KEY (`Program_ID`) REFERENCES `program`(`Program_ID`)
)

CREATE TABLE "broadcast_share" (
"Channel_ID" int,
"Program_ID" int,
"Date" text,
"Share_in_percent" real,
PRIMARY KEY ("Channel_ID","Program_ID"),
FOREIGN KEY (`Channel_ID`) REFERENCES `channel`(`Channel_ID`),
FOREIGN KEY (`Program_ID`) REFERENCES `program`(`Program_ID`)
)

### Question
Show me the owner of the channel with the highest rating.

### SQL
SELECT OWNER FROM channel ORDER BY rating_in_percent DESC LIMIT 1