target-benchmark/spider-queries huggingface dataset:
Database: baseball_1 (tables: home_game, park)
Question: Which park did the most people attend in 2008?
SQL: SELECT T2.park_name FROM home_game AS T1 JOIN park AS T2 ON T1.park_id  =  T2.park_id WHERE T1.year = 2008 ORDER BY T1.attendance DESC LIMIT 1;
Database: mountain_photos (tables: camera_lens)
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: mountain_photos (tables: camera_lens)
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: mountain_photos (tables: photos)
Question: List the id, color scheme, and name for all the photos.
SQL: SELECT id ,  color ,  name FROM photos
Database: mountain_photos (tables: mountain)
Question: What are the maximum and average height of the mountains?
SQL: SELECT max(height) ,  avg(height) FROM mountain
Database: mountain_photos (tables: mountain)
Question: What are the average prominence of the mountains in country 'Morocco'?
SQL: SELECT avg(prominence) FROM mountain WHERE country  =  'Morocco'
Database: mountain_photos (tables: mountain)
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: mountain_photos (tables: mountain, photos)
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: mountain_photos (tables: mountain, photos)
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: mountain_photos (tables: photos, camera_lens)
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: mountain_photos (tables: photos, camera_lens)
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: mountain_photos (tables: camera_lens)
Question: How many different kinds of lens brands are there?
SQL: SELECT count(DISTINCT brand) FROM camera_lens
Database: mountain_photos (tables: photos, camera_lens)
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: mountain_photos (tables: mountain, photos)
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: mountain_photos (tables: mountain, camera_lens, photos)
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: mountain_photos (tables: mountain, camera_lens, photos)
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: mountain_photos (tables: camera_lens)
Question: List the camera lens names containing substring "Digital".
SQL: SELECT name FROM camera_lens WHERE name LIKE "%Digital%"
Database: mountain_photos (tables: photos, camera_lens)
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: program_share (tables: channel)
Question: Find the names of channels that are not owned by CCTV.
SQL: SELECT name FROM channel WHERE OWNER != 'CCTV'
Database: program_share (tables: channel)
Question: Which channels are not owned by CCTV? Give me the channel names.
SQL: SELECT name FROM channel WHERE OWNER != 'CCTV'
Database: program_share (tables: channel)
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: program_share (tables: channel)
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: program_share (tables: channel)
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: program_share (tables: channel)
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
Database: program_share (tables: program)
Question: how many programs are there?
SQL: SELECT count(*) FROM program
Database: program_share (tables: program)
Question: Count the number of programs.
SQL: SELECT count(*) FROM program
Database: program_share (tables: program)
Question: list all the names of programs, ordering by launch time.
SQL: SELECT name FROM program ORDER BY launch
Database: program_share (tables: program)
Question: What is the list of program names, sorted by the order of launch date?
SQL: SELECT name FROM program ORDER BY launch
Database: program_share (tables: program)
Question: List the name, origin and owner of each program.
SQL: SELECT name ,  origin ,  OWNER FROM program
Database: program_share (tables: program)
Question: What are the name, origin and owner of each program?
SQL: SELECT name ,  origin ,  OWNER FROM program
Database: program_share (tables: program)
Question: find the name of the program that was launched most recently.
SQL: SELECT name FROM program ORDER BY launch DESC LIMIT 1
Database: program_share (tables: program)
Question: Which program was launched most recently? Return the program name.
SQL: SELECT name FROM program ORDER BY launch DESC LIMIT 1
Database: program_share (tables: channel)
Question: find the total percentage share of all channels owned by CCTV.
SQL: SELECT sum(Share_in_percent) FROM channel WHERE OWNER  =  'CCTV'
Database: program_share (tables: channel)
Question: What is the total share (in percent) of all the channels owned by CCTV?
SQL: SELECT sum(Share_in_percent) FROM channel WHERE OWNER  =  'CCTV'
Database: program_share (tables: channel, broadcast)
Question: Find the names of the channels that are broadcast in the morning.
SQL: SELECT t1.name FROM channel AS t1 JOIN broadcast AS t2 ON t1.channel_id  =  t2.channel_id WHERE t2.time_of_day  =  'Morning'
Database: program_share (tables: channel, broadcast)
Question: Which channels are broadcast in the morning? Give me the channel names.
SQL: SELECT t1.name FROM channel AS t1 JOIN broadcast AS t2 ON t1.channel_id  =  t2.channel_id WHERE t2.time_of_day  =  'Morning'
Database: program_share (tables: channel, broadcast)
Question: what are the names of the channels that broadcast in both morning and night?
SQL: SELECT t1.name FROM channel AS t1 JOIN broadcast AS t2 ON t1.channel_id  =  t2.channel_id WHERE t2.time_of_day  =  'Morning' INTERSECT SELECT t1.name FROM channel AS t1 JOIN broadcast AS t2 ON t1.channel_id  =  t2.channel_id WHERE t2.time_of_day  =  'Night'
Database: program_share (tables: channel, broadcast)
Question: Which channels broadcast both in the morning and at night? Give me the channel names.
SQL: SELECT t1.name FROM channel AS t1 JOIN broadcast AS t2 ON t1.channel_id  =  t2.channel_id WHERE t2.time_of_day  =  'Morning' INTERSECT SELECT t1.name FROM channel AS t1 JOIN broadcast AS t2 ON t1.channel_id  =  t2.channel_id WHERE t2.time_of_day  =  'Night'
Database: program_share (tables: broadcast)
Question: how many programs are broadcast in each time section of the day?
SQL: SELECT count(*) ,  time_of_day FROM broadcast GROUP BY time_of_day
Database: program_share (tables: broadcast)
Question: Count the number of programs broadcast for each time section of a day.
SQL: SELECT count(*) ,  time_of_day FROM broadcast GROUP BY time_of_day
Database: program_share (tables: broadcast)
Question: find the number of different programs that are broadcast during night time.
SQL: SELECT count(DISTINCT program_id) FROM broadcast WHERE time_of_day  =  'Night'
Database: program_share (tables: broadcast)
Question: How many distinct programs are broadcast at "Night" time?
SQL: SELECT count(DISTINCT program_id) FROM broadcast WHERE time_of_day  =  'Night'
Database: program_share (tables: program, broadcast)
Question: Find the names of programs that are never broadcasted in the morning.
SQL: SELECT name FROM program EXCEPT SELECT t1.name FROM program AS t1 JOIN broadcast AS t2 ON t1.program_id  =  t2.program_id WHERE t2.Time_of_day  =  "Morning"
Database: program_share (tables: program, broadcast)
Question: Which programs are never broadcasted in the morning? Give me the names of the programs.
SQL: SELECT name FROM program EXCEPT SELECT t1.name FROM program AS t1 JOIN broadcast AS t2 ON t1.program_id  =  t2.program_id WHERE t2.Time_of_day  =  "Morning"
Database: program_share (tables: program, broadcast)
Question: find the program owners that have some programs in both morning and night time.
SQL: SELECT t1.owner FROM program AS t1 JOIN broadcast AS t2 ON t1.program_id  =  t2.program_id WHERE t2.Time_of_day  =  "Morning" INTERSECT SELECT t1.owner FROM program AS t1 JOIN broadcast AS t2 ON t1.program_id  =  t2.program_id WHERE t2.Time_of_day  =  "Night"
Database: program_share (tables: program, broadcast)
Question: Who are the owners of the programs that broadcast both in the morning and at night?
SQL: SELECT t1.owner FROM program AS t1 JOIN broadcast AS t2 ON t1.program_id  =  t2.program_id WHERE t2.Time_of_day  =  "Morning" INTERSECT SELECT t1.owner FROM program AS t1 JOIN broadcast AS t2 ON t1.program_id  =  t2.program_id WHERE t2.Time_of_day  =  "Night"
Database: program_share (tables: program)
Question: List all program origins in the alphabetical order.
SQL: SELECT origin FROM program ORDER BY origin
Database: program_share (tables: program)
Question: What is the list of program origins ordered alphabetically?
SQL: SELECT origin FROM program ORDER BY origin
Database: program_share (tables: channel)
Question: what is the number of different channel owners?
SQL: SELECT count(DISTINCT OWNER) FROM channel
Database: program_share (tables: channel)
Question: Count the number of distinct channel owners.
SQL: SELECT count(DISTINCT OWNER) FROM channel
Database: program_share (tables: program)
Question: find the names of programs whose origin is not in Beijing.
SQL: SELECT name FROM program WHERE origin != 'Beijing'
Database: program_share (tables: program)
Question: Which programs' origins are not "Beijing"? Give me the program names.
SQL: SELECT name FROM program WHERE origin != 'Beijing'
Database: program_share (tables: channel)
Question: What are the names of the channels owned by CCTV or HBS?
SQL: SELECT name FROM channel WHERE OWNER  =  'CCTV' OR OWNER  =  'HBS'
Database: program_share (tables: channel)
Question: List the names of all the channels owned by either CCTV or HBS
SQL: SELECT name FROM channel WHERE OWNER  =  'CCTV' OR OWNER  =  'HBS'
Database: program_share (tables: channel)
Question: Find the total rating ratio for each channel owner.
SQL: SELECT sum(Rating_in_percent) ,  OWNER FROM channel GROUP BY OWNER
Database: program_share (tables: channel)
Question: What is the total rating of channel for each channel owner?
SQL: SELECT sum(Rating_in_percent) ,  OWNER FROM channel GROUP BY OWNER
Database: program_share (tables: program, broadcast)
Question: Find the name of the program that is broadcast most frequently.
SQL: SELECT t1.name FROM program AS t1 JOIN broadcast AS t2 ON t1.program_id  =  t2.program_id GROUP BY t2.program_id ORDER BY count(*) DESC LIMIT 1
Database: program_share (tables: program, broadcast)
Question: Which program is broadcast most frequently? Give me the program name.
SQL: SELECT t1.name FROM program AS t1 JOIN broadcast AS t2 ON t1.program_id  =  t2.program_id GROUP BY t2.program_id ORDER BY count(*) DESC LIMIT 1
Database: e_learning (tables: courses)
Question: How many courses are there in total?
SQL: SELECT count(*) FROM COURSES
Database: e_learning (tables: courses)
Question: Find the total number of courses offered.
SQL: SELECT count(*) FROM COURSES
Database: e_learning (tables: courses)
Question: What are the descriptions of the courses with name "database"?
SQL: SELECT course_description FROM COURSES WHERE course_name  =  "database"
Database: e_learning (tables: courses)
Question: Return the description for the courses named "database".
SQL: SELECT course_description FROM COURSES WHERE course_name  =  "database"
Database: e_learning (tables: course_authors_and_tutors)
Question: What are the addresses of the course authors or tutors with personal name "Cathrine"
SQL: SELECT address_line_1 FROM Course_Authors_and_Tutors WHERE personal_name	 =  "Cathrine"
Database: e_learning (tables: course_authors_and_tutors)
Question: Return the addresses of the course authors or tutors whose personal name is "Cathrine".
SQL: SELECT address_line_1 FROM Course_Authors_and_Tutors WHERE personal_name	 =  "Cathrine"
Database: e_learning (tables: course_authors_and_tutors)
Question: List the addresses of all the course authors or tutors.
SQL: SELECT address_line_1 FROM Course_Authors_and_Tutors
Database: e_learning (tables: course_authors_and_tutors)
Question: What is the address of each course author or tutor?
SQL: SELECT address_line_1 FROM Course_Authors_and_Tutors
Database: e_learning (tables: course_authors_and_tutors)
Question: List all the login names and family names of course author and tutors.
SQL: SELECT login_name ,  family_name FROM Course_Authors_and_Tutors
Database: e_learning (tables: course_authors_and_tutors)
Question: What are the login names and family names of course author and tutors?
SQL: SELECT login_name ,  family_name FROM Course_Authors_and_Tutors
Database: e_learning (tables: student_course_enrolment)
Question: List all the dates of enrollment and completion of students.
SQL: SELECT date_of_enrolment ,  date_of_completion FROM Student_Course_Enrolment
Database: e_learning (tables: student_course_enrolment)
Question: What are all the dates of enrollment and completion in record?
SQL: SELECT date_of_enrolment ,  date_of_completion FROM Student_Course_Enrolment
Database: e_learning (tables: student_course_enrolment)
Question: How many distinct students are enrolled in courses?
SQL: SELECT count(DISTINCT student_id) FROM Student_Course_Enrolment
Database: e_learning (tables: student_course_enrolment)
Question: Find the number of distinct students enrolled in courses.
SQL: SELECT count(DISTINCT student_id) FROM Student_Course_Enrolment
Database: e_learning (tables: student_course_enrolment)
Question: How many distinct courses are enrolled in by students?
SQL: SELECT count(course_id) FROM Student_Course_Enrolment
Database: e_learning (tables: student_course_enrolment)
Question: Find the number of distinct courses that have enrolled students.
SQL: SELECT count(course_id) FROM Student_Course_Enrolment
Database: e_learning (tables: student_tests_taken)
Question: Find the dates of the tests taken with result "Pass".
SQL: SELECT date_test_taken FROM Student_Tests_Taken WHERE test_result  =  "Pass"
Database: e_learning (tables: student_tests_taken)
Question: Which tests have "Pass" results? Return the dates when the tests were taken.
SQL: SELECT date_test_taken FROM Student_Tests_Taken WHERE test_result  =  "Pass"
Database: e_learning (tables: student_tests_taken)
Question: How many tests have result "Fail"?
SQL: SELECT count(*) FROM Student_Tests_Taken WHERE test_result  =  "Fail"
Database: e_learning (tables: student_tests_taken)
Question: Count the number of tests with "Fail" result.
SQL: SELECT count(*) FROM Student_Tests_Taken WHERE test_result  =  "Fail"
Database: e_learning (tables: students)
Question: What are the login names of the students with family name "Ward"?
SQL: SELECT login_name FROM Students WHERE family_name  =  "Ward"
Database: e_learning (tables: students)
Question: Return the login names of the students whose family name is "Ward".
SQL: SELECT login_name FROM Students WHERE family_name  =  "Ward"
Database: e_learning (tables: students)
Question: What are the dates of the latest logon of the students with family name "Jaskolski" or "Langosh"?
SQL: SELECT date_of_latest_logon FROM Students WHERE family_name  =  "Jaskolski" OR family_name  =  "Langosh"
Database: e_learning (tables: students)
Question: Find the latest logon date of the students whose family name is "Jaskolski" or "Langosh".
SQL: SELECT date_of_latest_logon FROM Students WHERE family_name  =  "Jaskolski" OR family_name  =  "Langosh"
Database: e_learning (tables: students)
Question: How many students have personal names that contain the word "son"?
SQL: SELECT COUNT(*) FROM Students WHERE personal_name LIKE "%son%"
Database: e_learning (tables: students)
Question: Find the number of students who have the word "son" in their personal names.
SQL: SELECT COUNT(*) FROM Students WHERE personal_name LIKE "%son%"
Database: e_learning (tables: subjects)
Question: List all the subject names.
SQL: SELECT subject_name FROM SUBJECTS
Database: e_learning (tables: subjects)
Question: What are the names of all the subjects.
SQL: SELECT subject_name FROM SUBJECTS
Database: e_learning (tables: course_authors_and_tutors)
Question: List all the information about course authors and tutors in alphabetical order of the personal name.
SQL: SELECT * FROM Course_Authors_and_Tutors ORDER BY personal_name
Database: e_learning (tables: course_authors_and_tutors)
Question: Sort the information about course authors and tutors in alphabetical order of the personal name.
SQL: SELECT * FROM Course_Authors_and_Tutors ORDER BY personal_name
Database: e_learning (tables: students)
Question: List the personal names and family names of all the students in alphabetical order of family name.
SQL: SELECT personal_name ,  family_name FROM Students ORDER BY family_name
Database: e_learning (tables: students)
Question: What are the personal names and family names of the students? Sort the result in alphabetical order of the family name.
SQL: SELECT personal_name ,  family_name FROM Students ORDER BY family_name
Database: e_learning (tables: student_tests_taken)
Question: List each test result and its count in descending order of count.
SQL: SELECT test_result ,  COUNT(*) FROM Student_Tests_Taken GROUP BY test_result ORDER BY COUNT(*) DESC
Database: e_learning (tables: student_tests_taken)
Question: For each distinct test result, find the number of students who got the result.
SQL: SELECT test_result ,  COUNT(*) FROM Student_Tests_Taken GROUP BY test_result ORDER BY COUNT(*) DESC
Database: e_learning (tables: course_authors_and_tutors, courses)
Question: Find the login name of the course author that teaches the course with name "advanced database".
SQL: SELECT T1.login_name FROM Course_Authors_and_Tutors AS T1 JOIN Courses AS T2 ON T1.author_id  =  T2.author_id WHERE T2.course_name  =  "advanced database"
Database: e_learning (tables: course_authors_and_tutors, courses)
Question: Which course author teaches the "advanced database" course? Give me his or her login name.
SQL: SELECT T1.login_name FROM Course_Authors_and_Tutors AS T1 JOIN Courses AS T2 ON T1.author_id  =  T2.author_id WHERE T2.course_name  =  "advanced database"
Database: e_learning (tables: course_authors_and_tutors, courses)
Question: Find the addresses of the course authors who teach the course with name "operating system" or "data structure".
SQL: SELECT T1.address_line_1 FROM Course_Authors_and_Tutors AS T1 JOIN Courses AS T2 ON T1.author_id  =  T2.author_id WHERE T2.course_name  =  "operating system" OR T2.course_name  =  "data structure"
Database: e_learning (tables: course_authors_and_tutors, courses)
Question: What are the addresses of the course authors who teach either "operating system" or "data structure" course.
SQL: SELECT T1.address_line_1 FROM Course_Authors_and_Tutors AS T1 JOIN Courses AS T2 ON T1.author_id  =  T2.author_id WHERE T2.course_name  =  "operating system" OR T2.course_name  =  "data structure"
Database: e_learning (tables: course_authors_and_tutors, courses)
Question: Find the personal name, family name, and author ID of the course author that teaches the most courses.
SQL: SELECT T1.personal_name ,  T1.family_name ,  T2.author_id FROM Course_Authors_and_Tutors AS T1 JOIN Courses AS T2 ON T1.author_id  =  T2.author_id GROUP BY T2.author_id ORDER BY COUNT(*) DESC LIMIT 1
Database: e_learning (tables: course_authors_and_tutors, courses)
Question: What are the personal name, family name, and author ID of the course author who teaches the most courses?
SQL: SELECT T1.personal_name ,  T1.family_name ,  T2.author_id FROM Course_Authors_and_Tutors AS T1 JOIN Courses AS T2 ON T1.author_id  =  T2.author_id GROUP BY T2.author_id ORDER BY COUNT(*) DESC LIMIT 1
Database: e_learning (tables: course_authors_and_tutors, courses)
Question: Find the addresses and author IDs of the course authors that teach at least two courses.
SQL: SELECT T1.address_line_1 ,  T2.author_id FROM Course_Authors_and_Tutors AS T1 JOIN Courses AS T2 ON T1.author_id  =  T2.author_id GROUP BY T2.author_id HAVING Count(*)  >=  2
Database: e_learning (tables: course_authors_and_tutors, courses)
Question: Which course authors teach two or more courses? Give me their addresses and author IDs.
SQL: SELECT T1.address_line_1 ,  T2.author_id FROM Course_Authors_and_Tutors AS T1 JOIN Courses AS T2 ON T1.author_id  =  T2.author_id GROUP BY T2.author_id HAVING Count(*)  >=  2
Database: e_learning (tables: course_authors_and_tutors, courses)
Question: Find the names of courses taught by the tutor who has personal name "Julio".
SQL: SELECT T2.course_name FROM Course_Authors_and_Tutors AS T1 JOIN Courses AS T2 ON T1.author_id  =  T2.author_id WHERE T1.personal_name  =  "Julio"
Database: e_learning (tables: course_authors_and_tutors, courses)
Question: What are the names of the courses taught by the tutor whose personal name is "Julio"?
SQL: SELECT T2.course_name FROM Course_Authors_and_Tutors AS T1 JOIN Courses AS T2 ON T1.author_id  =  T2.author_id WHERE T1.personal_name  =  "Julio"
Database: e_learning (tables: subjects, courses)
Question: Find the names and descriptions of courses that belong to the subject named "Computer Science".
SQL: SELECT T1.course_name ,  T1.course_description FROM Courses AS T1 JOIN Subjects AS T2 ON T1.subject_id  =  T2.subject_id WHERE T2.subject_name  =  "Computer Science"
Database: e_learning (tables: subjects, courses)
Question: What are the names and descriptions of the all courses under the "Computer Science" subject?
SQL: SELECT T1.course_name ,  T1.course_description FROM Courses AS T1 JOIN Subjects AS T2 ON T1.subject_id  =  T2.subject_id WHERE T2.subject_name  =  "Computer Science"
Database: e_learning (tables: subjects, courses)
Question: Find the subject ID, subject name, and the corresponding number of available courses for each subject.
SQL: SELECT T1.subject_id ,  T2.subject_name ,  COUNT(*) FROM Courses AS T1 JOIN Subjects AS T2 ON T1.subject_id  =  T2.subject_id GROUP BY T1.subject_id