2.9 LAB

Question 1

STEP 1

The Movie table has the following columns:

ID - integer, primary key
Title - variable-length string
Genre - variable-length string
RatingCode - variable-length string
Year - integer
Write a SELECT statement to select the year and the total number of movies for that year.

Hint: Use the COUNT() function and GROUP BY clause.

Answer 1

STEP 1: Select CLAUSE

SELECT Year, COUNT(*) AS TotalMovies

• Select is used to specify the columns that you want to retrieve from the database
• ‘Year’ is the coulmn we want to inclide in the result set
• COUNT(asterisks) is an aggregate function that counts all the rows in each group. ‘‘asterisks*’’ means that it counts all rows
• AS ‘Total Movies’ gives a name to the result of COUNT(*), making it easier to read the output.

Question 2

STEP 2

The Movie table has the following columns:

ID - integer, primary key
Title - variable-length string
Genre - variable-length string
RatingCode - variable-length string
Year - integer
Write a SELECT statement to select the year and the total number of movies for that year.

Hint: Use the COUNT() function and GROUP BY clause.

Answer 2

Step 2: FROM Clause
FROM Movie

• ‘FROM’ specifies the table from which to retrieve the data. In the case, it is the ‘Movie’ table.

Question 3

STEP3

The Movie table has the following columns:

ID - integer, primary key
Title - variable-length string
Genre - variable-length string
RatingCode - variable-length string
Year - integer
Write a SELECT statement to select the year and the total number of movies for that year.

Hint: Use the COUNT() function and GROUP BY clause.

Answer 3

STEP 3: GROUP BY clause

• ‘GROUP BY’ is used to group rows that have the same values in specified columns into summary rows.
• ‘Year’ is the column by which we want to group the data. Each unique value in the ‘Year’ column will form a group

Question 4

Whats the full Query and how does it work?

The Movie table has the following columns:

ID - integer, primary key
Title - variable-length string
Genre - variable-length string
RatingCode - variable-length string
Year - integer
Write a SELECT statement to select the year and the total number of movies for that year.

Hint: Use the COUNT() function and GROUP BY clause.

Answer 4

Full Query:
SELECT Year, COUNT(*) AS TotalMovies
FROM Movie
GROUP BY Year;

How it works:
1. Selecting Columns: The SELECT statement specifies that we want to retrieve the Year and a count of movies (COUNT(*)).

2. Source Table: The FROM clause tells SQL which table to use, which is Movie in this case.
3. Grouping: The GROUP BY clause groups the rows in the Movie table by each unique ‘Year’.
4. Counting Rows: For each group (each unique Year), COUNT(*) counts the number of rows (movies) in that group.
5. Result: The result will be a list of years and the corresponding number of movies released in each year.