Consider the following MOVIE database and answer t – Class 11 Informatics Practices

Exercises · Q4

Consider the following MOVIE database and answer the SQL queries based on it.

MovieID	MovieName	Category	ReleaseDate	ProductionCost	BusinessCost
001	Hindi_Movie	Musical	2018-04-23	124500	130000
002	Tamil_Movie	Action	2016-05-17	112000	118000
003	English_Movie	Horror	2017-08-06	245000	360000
004	Bengali_Movie	Adventure	2017-01-04	72000	100000
005	Telugu_Movie	Action	-	100000	-
006	Punjabi_Movie	Comedy	-	30500	-

a) Retrieve movies information without mentioning their column names.

b) List business done by the movies showing only MovieID, MovieName and BusinessCost.

c) List the different categories of movies.

d) Find the net profit of each movie showing its ID, Name and Net Profit.

(Hint: Net Profit = BusinessCost – ProductionCost)

Make sure that the new column name is labelled as NetProfit. Is this column now a part of the MOVIE relation. If no, then what name is coined for such columns? What can you say about the profit of a movie which has not yet released? Does your query result show profit as zero?

e) List all movies with ProductionCost greater than 80,000 and less than 1,25,000 showing ID, Name and ProductionCost.

f) List all movies which fall in the category of Comedy or Action.

g) List the movies which have not been released yet.

Puducherry TnboardTextbookSubjective· 4mImportance★★★★★

67% · 30/45 Questions

🔒 Locked · start free trial →

You're viewing a preview — the full solution, concept, methods & PYQ mapping are locked.

Start your 14-day free trial to unlock the full solution →

Concept understanding — Database Schema

Database Schema: The Blueprint of a Database

Imagine you are organising a large library. You don't just pile books randomly. First, you decide: What information will we keep about each book? Title, author, ISBN, shelf number. Then you decide: How are these pieces of information related? A book has one shelf, but a shelf holds many books. You also decide rules: Every book must have an ISBN; no two books can share the same ISBN.

That plan — the structure you design before you put a single book on the shelf — is the schema.

The Precise Meaning

A database schema is the formal, logical description of how data is organised in a database. It defines:

What tables exist (like "Books", "Members", "Loans")
What columns each table has (e.g., BookID, Title, Author, YearPublished)
What type of data each column holds (text, number, date, etc.)
What rules or constraints apply (e.g., "Every loan must refer to an existing member")
How tables relate to one another (e.g., "A member can borrow many books, but each loan record belongs to one member")

Think of the schema as the skeleton of the database. The actual data — the rows you fill in later — is the flesh and blood. The schema stays mostly unchanged; the data changes constantly.

Note

A common confusion: people say "the database" when they mean the data inside it. But the schema is the structure, not the data. Two databases can have the same schema but completely different data — like two schools using the same student record form but with different students' names.

Why the Schema Matters

Without a schema, data becomes a mess. You might store a customer's phone number in the "Name" column, or lose track of which orders belong to which customer. The schema prevents this by enforcing consistency and integrity.

Consider a bank. The schema says: "Every transaction must have an amount, a date, and a valid account number." If someone tries to enter a transaction without an account number, the database refuses — because the schema says so. This is not a software bug; it is a deliberate design choice built into the structure.

Key Components of a Schema

Tables – The main containers. Each table represents one kind of thing (Students, Products, Invoices).
Columns (Fields) – The attributes or properties of that thing (StudentName, DateOfBirth, Email).
Data Types – What kind of value each column holds (text, whole number, decimal, date).
Constraints – Rules that data must obey:
- Primary Key – A unique identifier for each row (like a roll number). No two rows can share it.
- Foreign Key – A column that links to another table's primary key, creating a relationship.
- Not Null – A column that must always have a value.
- Unique – A column where every value must be different.
Relationships – How tables connect: one-to-one, one-to-many, many-to-many. …

Start your 14-day free trial to unlock the full solution →

The seven queries in one sweep (the dashes in the table are NULLs):

SELECT * FROM MOVIE;                                                        -- a
SELECT MovieID, MovieName, BusinessCost FROM MOVIE;                         -- b
SELECT DISTINCT Category FROM MOVIE;                                        -- c
SELECT MovieID, MovieName, BusinessCost - ProductionCost AS NetProfit
FROM MOVIE;                                                                 -- d
SELECT MovieID, MovieName, ProductionCost FROM MOVIE
WHERE ProductionCost > 80000 AND ProductionCost < 125000;                   -- e: 001,002,005
SELECT * FROM MOVIE WHERE Category IN ('Comedy','Action');                  -- f: 002,005,006 …

Start your 14-day free trial to unlock the full solution →

Seven classic single-table queries on MOVIE: SELECT * (all columns), a projection list, DISTINCT for categories, a derived column BusinessCost - ProductionCost AS NetProfit (not stored in the relation; NULL — not zero — for unreleased movies), a range with AND, a set filter with IN, and IS NULL for the not-yet-released rows.

A reading note first: the dashes in the table mean the value is NULL (unknown) — ReleaseDate and BusinessCost are NULL for movies 005 and 006. That drives parts (d) and (g).

a) All movie information, without naming columns

SELECT * FROM MOVIE;

MovieID	MovieName	Category	ReleaseDate	ProductionCost	BusinessCost
001	Hindi_Movie	Musical	2018-04-23	124500	130000
002	Tamil_Movie	Action	2016-05-17	112000	118000
003	English_Movie	Horror	2017-08-06	245000	360000
004	Bengali_Movie	Adventure	2017-01-04	72000	100000
005	Telugu_Movie	Action	NULL	100000	NULL
006	Punjabi_Movie	Comedy	NULL	30500	NULL

* is exactly the "don't mention the column names" device — it expands to all columns.

b) Business done — only MovieID, MovieName, BusinessCost

SELECT MovieID, MovieName, BusinessCost FROM MOVIE;

MovieID	MovieName	BusinessCost
001	Hindi_Movie	130000
002	Tamil_Movie	118000
003	English_Movie	360000
004	Bengali_Movie	100000
005	Telugu_Movie	NULL
006	Punjabi_Movie	NULL

Listing column names after SELECT projects just those columns.

c) The different categories

SELECT DISTINCT Category FROM MOVIE;

Category
Musical
Action
Horror
Adventure
Comedy

DISTINCT collapses duplicates — Action appears in two rows but is listed once.

d) Net profit of each movie

SELECT MovieID, MovieName,
       BusinessCost - ProductionCost AS NetProfit
FROM MOVIE;

MovieID	MovieName	NetProfit
001	Hindi_Movie	5500
002	Tamil_Movie	6000
003	English_Movie	115000
004	Bengali_Movie	28000
005	Telugu_Movie	NULL
006	Punjabi_Movie	NULL

Three sub-questions hide here:

Is NetProfit now part of the MOVIE relation? No. It is computed on the fly for this result only; AS NetProfit merely labels the output column. Such columns are called derived (computed/virtual) columns — the table itself is unchanged.
Profit of an unreleased movie? Its BusinessCost is NULL, and any arithmetic with NULL yields NULL — so NULL - 100000 is NULL.
Does the result show zero? No — it shows NULL, which is the honest answer: the profit is unknown, not zero.

e) ProductionCost between 80,000 and 1,25,000 (exclusive)

SELECT MovieID, MovieName, ProductionCost
FROM MOVIE
WHERE ProductionCost > 80000 AND ProductionCost < 125000;

MovieID	MovieName	ProductionCost
001	Hindi_Movie	124500
002	Tamil_Movie	112000
005	Telugu_Movie	100000

AND requires both bounds to hold; 004 (72000) falls below and 003 (245000) above. …

Start your 14-day free trial to unlock the full solution →

←Previous questionQ3 — Fill in the blanks: a) ______ declares that an index in one table is r…→Next questionQ5 — Suppose your school management has decided to conduct cricket matches …

🎓Unlock everything free for 14 days

Unlock everything free for 14 days