Queries
What movies was Nicolas Cage in? (please name the columns film and actor) ordered alphabetically by film title?
I would like to know the highest rated (score) movie with Sandra Bullock. (show columns titled film and rating)?
What actors were in the movie Blade Runner (order alphabetically and name the column actor)?
I would like a list of all movies where the director also acted in the film. The output should include a column named 'director' containing the director's name, and 'film' containing the name of the film. Order alphabetically by director. If there are multiple movies by the same director order those by film title (so first ordered by director's name, and then film)
I would like a list of actors and the number of movies they were in ordered by the number of movies (and actors with the same number of films should be ordered alphabetically by actor). Please show me the actors that have been in ten or more films. (name the columns actor and films)
What actors were in movies with Michelle Pfeiffer? (column named actors and order by actors)
I would like to see the artist name and the number of albums that artist produced. I want to see the top five in order. Name the columns artist and albums (Chinook)
A list of the albums by Santana (in a column called album ordered alphabetically).
The first and last names of all employees and the first and lastnames of who they report to. Label the columns employee_firstname, employee_lastname, manager_firstname, manager_lastname. Order by Employee lastname then firstname. don't display employees who don't report to anyone (Chinook )
I would like to know the artist that sold the most number of tracks (columns named artist and tracks_sold)
Solution:
{
"query1": "SELECT movie.title AS film, actor.name AS actor FROM actor,movie,casting WHERE actor.name = 'Nicolas Cage' AND actor.id = casting.actorid AND movie.id = casting.movieid ORDER BY movie.title ASC",
"query2": "SELECT movie.title AS film, movie.score AS rating FROM actor,movie,casting WHERE actor.name = 'Sandra Bullock' AND actor.id = casting.actorid AND movie.id = casting.movieid ORDER BY movie.score DESC LIMIT 1",
"query3": "SELECT actor.name AS actor FROM actor, casting, movie WHERE movie.title = 'Blade Runner' AND casting.movieid = movie.id AND casting.actorid = actor.id ORDER BY actor.name ASC",
"query4": "SELECT actor.name AS director, movie.title AS film FROM movie,casting,actor WHERE casting.movieid = movie.id AND casting.actorid = movie.director AND casting.actorid = actor.id ORDER BY actor.name ASC, movie.title ASC",
"query5": "SELECT actor.name AS actors, COUNT(movie.id) AS films FROM casting,movie,actor WHERE casting.movieid = movie.id AND casting.actorid = actor.id GROUP BY actor.name HAVING COUNT(movie.id) >= 10 ORDER BY COUNT(movie.id) DESC, actor.name DESC",
"query6": "SELECT DISTINCT(actor.name) AS actors FROM casting, actor WHERE casting.actorid = actor.id AND actor.name != 'Michelle Pfeiffer' AND casting.movieid IN (SELECT movie.id AS actors FROM casting,movie,actor WHERE casting.movieid = movie.id AND casting.actorid = actor.id AND actor.name = 'Michelle Pfeiffer') ORDER BY actor.name ASC",
"query7": "SELECT artist.name AS artist, COUNT(album.title) AS albums FROM album,artist WHERE album.artistid = artist.artistid GROUP BY artist.name ORDER BY COUNT(album.title) DESC, artist.name ASC limit 5 ",
"query8": "SELECT album.title AS album FROM album,artist WHERE artist.name = 'Santana' AND album.artistid = artist.artistid ORDER BY album.title ASC ",
"query9": "SELECT e1.firstname AS employee_firstname, e1.lastname AS employee_lastname, e3.rfname AS manager_firstname, e3.rlname AS manager_lastname FROM employee e1,(SELECT employee.employeeid AS eid, employee.reportsto AS rid from employee WHERE employee.reportsto is not null) as e2,(SELECT employee.employeeid AS e3id, employee.firstname AS rfname, employee.lastname AS rlname FROM employee ) as e3 WHERE e1.employeeid = e2.eid AND e2.rid = e3.e3id ORDER BY e1.lastname ASC, e1.firstname ASC",
"query10": "SELECT * FROM movie"
}
Database file you can download from:
We are group of more than 20+ experienced expert and professionals that can handle any types of database related problems easily.
If you face issue in any Database Related task or not have time to complete homework then don't worry, realcode4you.com expert team help you to complete your homework with an affordable price.
Comments