logo

Desklib Study Material Library: ERD Relationship, SQL Queries

7 Pages1319 Words264 Views
   

Added on  2020-09-22

About This Document

This article discusses ERD relationship and SQL queries on a movies database. It covers topics such as the number of users contributing tags, movies with 3 genres, users giving non-whole number ratings, movies with duplicate titles, movies released between 1985-1995, movies without ratings, and more. The output is strictly in JSON format.

Desklib Study Material Library: ERD Relationship, SQL Queries

   Added on 2020-09-22

ShareRelated Documents
Part 1:ERD Relationship Relational Schema i)There are two trains running at 2PM on a weekday. ii)There are Three Trains depart form the Flinders st ,Frankston and Pakenham The design has been made for be no partial dependences of any of the columns on the primary key. it’s in the first normal form and all the non-key attributes are fully functional dependent on the primary key. it is satisfying the conditions of the second normal form.
Desklib Study Material Library: ERD Relationship, SQL Queries_1
Part 2-SQL queries QuestionAnswerCode1How many users contributed tags?58selectcount(distinct userid) from bus5dwr.dbo.movielens_tags;2How many movies had exactly 3 genres associated with them? (Comedy|Drama|Romance would be one example.)2338selectcount(distinct movieid)from(select movieid,title,genres,count(value) as cntfrom(select *from bus5dwr.dbo.movielens_moviescross apply string_split(genres,'|') ) as Mgroupby movieid,title,genres) as Nwhere cnt= 3;3How many users gave ratings that are not whole numbers (e.g. 3.5, 4.5)?369SELECTCOUNT(DISTINCT userid)from bus5dwr.dbo.movielens_ratingsWHERE rating LIKE'%.%';4List the movies whose title (and year) appear more than once in the movielens_movies table.Title1-"Confessions of a Dangerous Mind (2002)"2-"Emma (1996)"3-"Eros (2004)"SELECT titlefrom bus5dwr.dbo.movielens_moviesgroupby title havingCOUNT(title)> 1;
Desklib Study Material Library: ERD Relationship, SQL Queries_2
4-"Saturn 3 (1980)"5-"War of the Worlds (2005)"5How many movies were released between 1985 and 1995 inclusive?1879SELECT titleFROM bus5dwr.dbo.movielens_movieswhereright(title, 6) >='(1985)'andright(title,6) <='(1995)';6Which movies in the movielens_movies table did not receive a rating? Write a query to list five of these movies.#title1-"Twentieth Century (1934)"2-"This Gun for Hire (1942)"3-"Parallax View, The (1974)"4-"For All Mankind (1989)"5-"I Know Where I'm Going! (1945)"SELECTTOP5 title FROM dbo.movielens_moviesLEFTJOIN movielens_ratings ONmovielens_ratings.movieId=movielens_movies.movieIdwhere rating isNULL7How many users tagged three or more different movies?26selectcount (DISTINCT userid)from(select userid,count(DISTINCT movieid) as cntfrom bus5dwr.dbo.movielens_tagsgroupby userid) as M where cnt >=3
Desklib Study Material Library: ERD Relationship, SQL Queries_3

End of preview

Want to access all the pages? Upload your documents or become a member.

Related Documents
Database Concepts: Answers to Questions 1-4
|8
|832
|240