SQL PROBLEM I just need to write a single SQL query for each

SQL PROBLEM

I just need to write a single SQL query for each of the question. ERD diagram is up there.

1.Which movies were created by a company with \"films\" in its name. List the movie title and ID.

2.Which actresses have been in a movie with Daniel Radcliffe. List their name and nickname.

3.Which movies have been directed by directors who h@ve directed more than one movie starring Angelina Jolie. Return the movie n@me and director n@me.

4.Find directors who have directed movies containing at least one actor or actress who has starred in more than twenty movies. Sort by number of movies made by the director that fits this criteria.

5.Find directors who have directed movies containing only actors and actresses who have starred in more than two movies. Sort by the number of movies made by the directors fitting this criteria.

eyword company type kind type movie_keyword keyword company_name id episode_nr episode ofid imdb_index company_type keyword keyword id movie_id country code imdb_id md5sunm movie_companies Ophonetic_codeT id kind O company_id name Oname_pcode_nfT Omd5sum company type_id movie_id note name_pcode_sf T movie id note phonetic_code T production year season nr title title link type kind type kind id episode nr movie link episode_of id info type imdb id imdb index kind id char_name link type_id linked movie id cast info | md5sum phonetic-code 1 production yearW season nr series yearS title id movie id note nr order person_id imdb_id imdb_index md5sunm movie_id role_type movie into name comp_cast type info type role T name_pcode_nf T Operson_role_id surname pcode T info_type_id movie_id note role id kind name aka name id imdb i imdb index md5sunm imdb_index md5sum name name_pcodecf T name_pcode_nf T movie_info_idx person_info id info_type_id complete cast name name pcode cf T movie_id status_id subject_id info_type_id movie-id- note note Oname_pcode_nf T Operson_id Operson_id surname_pcode T surname_pcode T Generated using DbSchema

Solution

1.Which movies were created by a company with \"films\" in its name. List the movie title and ID.

Sql query: SELECT title.title, id FROM movie_companies,title WHERE movies_companies.id= company_name.id AND company_name.name LIKE %films%;

2.Which actresses have been in a movie with Daniel Radcliffe. List their name and nickname.

Sql query: SELECT name.name, aka_name.name FROM name,aka_name,cast_info ON cast_info.person_id= name.id WHERE cast_info.movie_id IN(SELECT movie_id FROM cast_info,name ON cast_info.person_id=name.person_id WHERE name.name=\"Daniel Radcliffe\") AND cast_info.role_id=(SELECT id from role_type where role=\"actresses\");