How to use the SAP HANA Web-based Development tool for SQLScript exercises
Editor to share with you how to use SAP HANA Web-based Development tools for SQLScript exercises, I hope you will learn something after reading this article, let's discuss it together!
The database table contents provided through the csv file:
The format of links.csv:
Movies.csv format. A movie can have multiple styles (genres), separated by |
Ratings.csv:
Users give movie a score:
The label of tags.csv:movie
Exercise 1:
List the total number of records for the four tables:
Select 'links' as "table name", count (1) as "row count" from "MOVIELENS". Public.aa.movielens.hdb::data.LINKS "
Union all
Select 'movies' as "table name", count (1) as "row count" from "MOVIELENS". Public.aa.movielens.hdb::data.MOVIES "
Union all
Select 'ratings' as "table name", count (1) as "row count" from "MOVIELENS". Public.aa.movielens.hdb::data.RATINGS "
Union all
Select 'tags' as "table name", count (1) as "row count" from "MOVIELENS". Public.aa.movielens.hdb::data.TAGS "
Execution result:
Exercise 2: calculate a total of 9125 movies, how many art categories are included?
DO
BEGIN
DECLARE genreArray NVARCHAR (255) ARRAY
DECLARE tmp NVARCHAR (255)
DECLARE idx INTEGER
DECLARE sep NVARCHAR (1): ='|'
DECLARE CURSOR cur FOR SELECT DISTINCT "GENRES" FROM "MOVIELENS". Public.aa.movielens.hdb::data.MOVIES "
DECLARE genres NVARCHAR: =''
Idx: = 1
FOR cur_row AS cur () DO
SELECT cur_row. "GENRES" INTO genres FROM DUMMY
Tmp: =: genres
WHILE LOCATE (: tmp,:sep) > 0 DO
GenreArray [: idx]: = SUBSTR_BEFORE (: tmp,:sep)
Tmp: = SUBSTR_AFTER (: tmp,:sep)
Idx: =: idx + 1
END WHILE
GenreArray [: idx]: =: tmp
END FOR
GenreList = UNNEST (: genreArray) AS ("GENRE")
SELECT "GENRE" FROM: genreList GROUP BY "GENRE"
END
Implementation results, including a total of 18:
Exercise 3: calculate the total number of movies in each art category:
DO
BEGIN
DECLARE genreArray NVARCHAR (255) ARRAY
DECLARE tmp NVARCHAR (255)
DECLARE idx INTEGER
DECLARE sep NVARCHAR (1): ='|'
DECLARE CURSOR cur FOR SELECT DISTINCT "GENRES" FROM "MOVIELENS". Public.aa.movielens.hdb::data.MOVIES "
DECLARE genres NVARCHAR: =''
Idx: = 1
FOR cur_row AS cur () DO
SELECT cur_row. "GENRES" INTO genres FROM DUMMY
Tmp: =: genres
WHILE LOCATE (: tmp,:sep) > 0 DO
GenreArray [: idx]: = SUBSTR_BEFORE (: tmp,:sep)
Tmp: = SUBSTR_AFTER (: tmp,:sep)
Idx: =: idx + 1
END WHILE
GenreArray [: idx]: =: tmp
END FOR
GenreList = UNNEST (: genreArray) AS ("GENRE")
SELECT "GENRE", count (1) FROM: genreList GROUP BY "GENRE"
END
Exercise 4: list the number of styles contained in each movie:
SELECT
"MOVIEID"
, "TITLE"
, OCCURRENCES_REGEXPR ('[|]'IN GENRES) + 1 "GENRE_COUNT"
, "GENRES"
FROM "MOVIELENS". "public.aa.movielens.hdb::data.MOVIES"
ORDER BY "GENRE_COUNT" ASC
Exercise 5: list the style distribution of each movie
SELECT
"GENRE_COUNT"
, COUNT (1)
FROM (
SELECT
OCCURRENCES_REGEXPR ('[|]'IN "GENRES") + 1 "GENRE_COUNT"
FROM "MOVIELENS". "public.aa.movielens.hdb::data.MOVIES"
)
GROUP BY "GENRE_COUNT" ORDER BY "GENRE_COUNT"
For example, there are at least one style of film, 2793 films, 3039 films of two styles, and so on.
Exercise 6: calculate the rating distribution of movie
SELECT DISTINCT
MIN ("RATING_COUNT") OVER () AS "MIN"
MAX ("RATING_COUNT") OVER () AS "MAX"
AVG ("RATING_COUNT") OVER () AS "AVG"
SUM ("RATING_COUNT") OVER () AS "SUM"
MEDIAN ("RATING_COUNT") OVER () AS "MEDIAN"
STDDEV ("RATING_COUNT") OVER () AS "STDDEV"
COUNT (*) OVER () AS "CATEGORY_COUNT"
FROM (
SELECT "MOVIEID", COUNT (1) as "RATING_COUNT"
FROM "MOVIELENS". "public.aa.movielens.hdb::data.RATINGS"
GROUP BY "MOVIEID"
)
GROUP BY "RATING_COUNT"
Details:
SELECT "RATING_COUNT", COUNT (1) as "MOVIE_COUNT"
FROM (
SELECT "MOVIEID", COUNT (1) as "RATING_COUNT"
FROM "MOVIELENS". "public.aa.movielens.hdb::data.RATINGS"
GROUP BY "MOVIEID"
)
GROUP BY "RATING_COUNT" ORDER BY "RATING_COUNT" asc
For example, there are 397 movies with 5 votes cast by users.
Exercise 7: count users' votes
SELECT "RATING_COUNT", COUNT (1) as "USER_COUNT"
FROM (
SELECT "USERID", COUNT (1) as "RATING_COUNT"
FROM "MOVIELENS". "public.aa.movielens.hdb::data.RATINGS"
GROUP BY "USERID"
)
GROUP BY "RATING_COUNT" ORDER BY 1 DESC
One user cast 2391 votes and one user cast 1868 votes:
Exercise 8: count users' voting scores
SELECT "RATING", COUNT (1) as "RATING_COUNT"
FROM "MOVIELENS". "public.aa.movielens.hdb::data.RATINGS"
GROUP BY "RATING" ORDER BY 1 DESC
15095 users voted, with a score of 5.
After reading this article, I believe you have some understanding of "how to use SAP HANA Web-based Development tools for SQLScript exercises". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!
