All needs to be completed with PostgresSQL 9.5 syntax Create 7 tables for the following parameters:
A. users: userid (int, primary key), name (text)
B. movies: movieid (integer, primary key), title (text)
C. taginfo: tagid (int, primary key), content (text)
D. genres: genreid (integer, primary key), name (text)
E. ratings: userid (int, foreign key), movieid (int, foreign key), rating (numeric), timestamp (bigint, seconds since midnight Coordinated Universal Time (UTC) of January 1, 1970)
F. tags: userid (int, foreign key), movieid (int, foreign key), tagid (int, foreign key), timestamp (bigint, seconds since midnight Coordinated Universal Time (UTC) of January 1, 1970).
G. hasagenre: movieid (int, foreign key), genreid (int, foreign key)
Now write queries for the following
Note: Do not put "create/select/drop database", or "set system settings or encoding"
1. Write a SQL query to return the total number of movies for each genre. Your query result should be saved in a table called "query1" which has two attributes: "name" attribute is a list of genres, and "moviecount" list of movie counts for each genre.
2. Write a SQL query to return the average rating per genre. Your query result should be saved in a table called "query2" which has two attributes: "name" attribute is a list of all genres, and "rating" attribute is a list of average rating per genre.
3. Write a SQL query to return the movies which have at least 10 ratings. Your query result should be saved in a table called "query3" which has two attributes: "title" is a list of movie titles, and "CountOfRatings" is a list of ratings.
4. Write a SQL query to return all "Comedy" movies, including movieid and title. Your query result should be saved in a table called "query4" which has two attributes: "movieid" is a list of movie ids, and "title" is a list of movie titles.
5. Write a SQL query to return the average rating per movie. Your query result should be saved in a table called "query5" which has two attributes: "title" is a list of movie titles, and "average" is a list of the average rating per movie.
6. Write a SQL query to return the average rating for all "Comedy" movies. Your query result should be saved in a table called "query6" which has one attribute: "average".
7. Write a SQL query to return the average rating for all movies and each of these movies is both "Comedy" and "Romance". Your query result should be saved in a table called "query7" which has one attribute: "average".
8. Write a SQL query to return the average rating for all movies and each of these movies is "Romance" but not "Comedy". Your query result should be saved in a table called "query8" which has one attribute: "average".
9. Find all movies that are rated by a user such that the userId is equal to v1. The v1 will be an integer parameter passed to the SQL query. Your query result should be saved in a table called "query9" which has two attributes: "movieid" is a list of movieid’s rated by userId v1, and "rating" is a list of ratings given by userId v1 for corresponding movieid.
10. Write an SQL query to create a recommendation table* for a given user. Given a userID v1, you need to recommend the movies according to the movies he has rated before. In particular, you need to predict the rating P of a movie "i" that the user "Ua" didn’t rate. In the following recommendation model, P(Ua, i) is the predicted rating of movie i for User Ua. L contains all movies that have been rated by Ua. Sim(i, l) is the similarity between i and l. r is the rating that Ua gave to l.
11. Your SQL query should return predicted ratings from all movies that the given user hasn’t rated yet. You only return the movies whose predicted ratings are >3.9. Your query result should be saved in a table called "recommendation" which has one attribute: "title".
12. In order to produce the recommendation table, you first need to calculate the similarities between every pair of two movies. The similarity is equal to the similarity of the average ratings of two movies (average rating over all users, the average ratings in Query 5). That means, if the average ratings of two movies are more close, the two movies are more similar. The similarity score is a fractional value E [0, 1]. 0 means not similar at all, 1 means very similar.