This course introduces the world of database systems. It provides the foundation that will enable learners to master skills in data modeling and information, as well as extract information using existing database management systems. The following main topics are covered: database design/modeling, data storage and indexing, query processing/optimization, transaction management, database security, and data analytics.
Assignment Directions: SQL Query for Movie Recomendation
Loading...
來自 Arizona State University 的課程
Core Database Concepts
63 個評分
從本節課中
Basic Database Concepts
與講師見面
Mohamed SarwatAssistant Professor
Computer Science and Engineering
Hi. Welcome back. In this video,
I'm going to explain the assignment for creating,
or generating SQL queries for the movie recommendation database.
This assignment will build upon
the first assignment in which you created the movie database.
So, in this specific assignment,
what you need to do is that,
having the movie database you'll have to generate SQL queries.
And we mean by SQL queries is we learned in the course are like the SELECT,
FROM, WHERE queries we've learned and the different variations of it.
You need to use that to practically
generate SQL queries that will generate data for the end user.
So, for example, when you go on Netflix and you want to watch movies,
sometimes you want to filter these movies.
Instead of just asking Netflix to return everything you asked it,
I'm looking only for comedy movies.
So, this is one query actually that you can post to the database and you want to
select from the movie table, only comedy movies.
And then you look at the filtered comedy movies,
and you can later select one of them.
But the query will be,
SELECT only comedy movies from the movie database.
That's something we will want you to implement.
Another thing also that what we want you to implement is that,
sometimes when Netflix shows movies to you,
it shows like a rating next to it.
This rating is an aggregate of all the user ratings that it came to it,
and the aggregate can be an average for example.
So, instead of just returning all the reviews that users has given to,
or the ratings that every user has assigned to a movie,
for each movie you just want the average rating.
And this will give you an idea of how good this movie is, or something.
So, that's one other query we will be asking you to implement.
Again, in this assignment,
we just want you to write the SQL statements that map to every of the single queries.
And again, the details of
every single queries again in the instruction document for the assignment.
And then we will ask you also to write analytics queries on top of this database.
Like for example, if you are on
the Netflix site and you are a data scientist working at Netflix,
you want to do some kind of analytics for everything.
So, you want to group all the movies,
and you see how much the average rating for every comedy movie for example is.
That's kind of again people on Netflix side doing some kind of statistics on the data.
Collecting these kind of facts about the data to know
how like the viewership of comedy movies is going,
and how people rate the comedy movies that will show on Netflix for example.
So, we will also ask like for different variations of that.
So, the analyst maybe interest is like,
OK I want to see how people rated comedy movies that are comedy,
but are not romance.
There are movies that are both romance and comedy genre together.
So, I want to exclude this and I want to see how people rated.
So, we're asking in this assignment basically,
we're asking you to just figure out how to come up with these.
And the main task in the assignment,
which is the final query we're asking to do is to,
based on the database you have,
we want you to build a set of SQL statements
that will help generate recommendations for the end user.
So, we will assume that you are now the Netflix company,
and you are the main person who's developing the Netflix software,
and we want you to generate recommendations.
Recommendations is based again on,
past ratings of the movies that the people seen in the past.
So, if the user logs in,
user already rated some movies.
So, the recommendation you're going to create based on,
you want to recommend movies that
are similar to movies that the user has liked in the past,
or given the five star rating for example in the past.
So, we share the equation that you need to use to
find interesting movies for the user that the user didn't see before.
And the SQL statements you're going to write is going to return this movies.
So again, everything you do needs to use SQL,
and we're going to run this assignment.
You have to submit a script,
and this script has all the SQL statements in it.
And this SQL script will be automatically graded,
and every single query has several test cases to test.
And if you pass all the test cases of course,
you get the full grade for this assignment.
But you don't have to write any program,
or anything beside SQL.
Is just a bunch of SQL statements,
and we'll automatically figure out the results from that.
That's all I want to say about this assignment.
Thank you so much and good luck.
