SQL Quick Rules 

Keyword	Meaning	Example
SELECT	Choose which column(s) you want to see	SELECT title
FROM	Choose which table to get the data from	FROM Movies
WHERE	Add a condition to filter results	WHERE rental_rate < 4.0
AND	Combine two conditions (both must be true)	release_year = 2020 AND rental_rate < 4.0
OR	Combine two conditions (any one true is enough)	release_year = 2020 OR rental_rate < 4.0
BETWEEN	Filter values within a rrange (inclusive)	WHERE release_year BETWEEN 1980 AND 1989
IS NOT NULL	Ensures the column has a value (not empty)	WHERE budget IS NOT NULL
NOT IN	Exclude multiple values from a column	WHERE genre NOT IN ('Action','Comedy','Horror')

 

Operator	Meaning	Example	Result
=	Equal to	score = 8.5	True if score is exactly 8.5
<>	Not equal to (standard)	country <> 'United States'	True if country is anything except United States
!=	Not equal to (alternative, works in MySQL, etc.)	score != 7	True if score is not 7
>	Greater than	votes > 50000	True if votes are more than 50,000
<	Less than	year < 1990	True if year is before 1990
>=	Greater than or equal to	score >= 7.5	True if score is 7.5 or higher
<=	Less than or equal to	budget <= 20000000	True if budget is 20 million or less

 

Question: 

The manager wants to identify popular and moderately budgeted movies released between 2001 and 2015, specifically in the genres Comedy or Animation, while excluding Family and Musical movies. 
You are given the SQL code below: 

 
SELECT Name, Rating, Genre, Year, Score, Votes 

FROM ChildrenMovies 

WHERE  

    ((Genre = 'Comedy' AND Votes > 500000 )OR (Genre = 'Animation' AND Score >= 7.5)) 

    AND Year BETWEEN 2001 AND 2015 

    AND Budget <= 150000000 

    AND Genre NOT IN ('Family', 'Musical'); 

Children Movies 

S.No	Name	Rating	Genre	Year	Score	Votes	Director	Writer	Country	Budget ($)
1	Toy Story	G	Animation	1995	8.3	950000	John Lasseter	Joss Whedon	United States	30,000,000
2	Finding Nemo	G	Animation	2003	8.1	1050000	Andrew Stanton	Andrew Stanton	United States	94,000,000
3	The Lion King	G	Musical	1994	8.5	1100000	Roger Allers	Irene Mecchi	United States	45,000,000
4	Frozen	PG	Musical	2013	7.4	650000	Chris Buck	Jennifer Lee	United States	150,000,000
5	Shrek	PG	Comedy	2001	7.9	690000	Andrew Adamson	Ted Elliott	United States	60,000,000
6	Moana	PG	Adventure	2016	7.6	400000	Ron Clements	Jared Bush	United States	150,000,000
7	The Incredibles	PG	Action	2004	8.0	780000	Brad Bird	Brad Bird	United States	92,000,000
8	Inside Out	PG	Animation	2015	8.1	710000	Pete Docter	Meg LeFauve	United States	175,000,000
9	Kung Fu Panda	PG	Action	2008	7.6	490000	Mark Osborne	Jonathan Aibel	United States	130,000,000
10	Coco	PG	Musical	2017	8.4	610000	Lee Unkrich	Adrian Molina	United States	175,000,000
11	Cars	G	Comedy	2006	7.1	440000	John Lasseter	Dan Fogelman	United States	120,000,000
12	Despicable Me	PG	Comedy	2010	7.6	520000	Pierre Coffin	Cinco Paul	United States	69,000,000

 

SQL Quick Rules 

Keyword	Meaning	Example
SELECT	Choose which column(s) you want to see	SELECT title
FROM	Choose which table to get the data from	FROM Movies
WHERE	Add a condition to filter results	WHERE rental_rate < 4.0
AND	Combine two conditions (both must be true)	release_year = 2020 AND rental_rate < 4.0
OR	Combine two conditions (any one true is enough)	release_year = 2020 OR rental_rate < 4.0
BETWEEN	Filter values within a range (inclusive)	WHERE release_year BETWEEN 1980 AND 1989
IS NOT NULL	Ensures the column has a value (not empty)	WHERE budget IS NOT NULL

 

Operator	Meaning	Example	Result
=	Equal to	score = 8.5	True if score is exactly 8.5
<>	Not equal to (standard)	country <> 'United States'	True if country is anything except United States
!=	Not equal to (alternative, works in MySQL, etc.)	score != 7	True if score is not 7
>	Greater than	votes > 50000	True if votes are more than 50,000
<	Less than	year < 1990	True if year is before 1990
>=	Greater than or equal to	score >= 7.5	True if score is 7.5 or higher
<=	Less than or equal to	budget <= 20000000	True if budget is 20 million or less

 

Question: 

The manager wants to know which movies were popular between 2006 and 2012 in the genres Comedy, Family, or Musical. 
You are given the following SQL code: 

SELECT Name, Rating, Genre, Year, Score, Votes   

FROM ChildrenMovies   

WHERE (Genre = 'Comedy' OR Genre = 'Family' OR Genre = 'Musical')   

AND Year BETWEEN 2006 AND 2012               

AND Votes > 450000;             

Your task:  Examine the table to determine which movies this query will return.. 

ChildrenMovies 
 

S.No	Name	Rating	Genre	Year	Score	Votes	Director	Writer	Country	Budget ($)
1	Toy Story	G	Animation	1995	8.3	950000	John Lasseter	Joss Whedon	United States	30,000,000
2	Finding Nemo	G	Animation	2003	8.1	1050000	Andrew Stanton	Andrew Stanton	United States	94,000,000
3	The Lion King	G	Musical	1994	8.5	1100000	Roger Allers	Irene Mecchi	United States	45,000,000
4	Frozen	PG	Musical	2013	7.4	650000	Chris Buck	Jennifer Lee	United States	150,000,000
5	Shrek	PG	Comedy	2001	7.9	690000	Andrew Adamson	Ted Elliott	United States	60,000,000
6	Coco	PG	Musical	2017	8.4	610000	Lee Unkrich	Adrian Molina	United States	175,000,000
7	The Incredibles	PG	Action	2004	8.0	780000	Brad Bird	Brad Bird	United States	92,000,000
8	Inside Out	PG	Animation	2015	8.1	710000	Pete Docter	Meg LeFauve	United States	175,000,000
9	Kung Fu Panda	PG	Action	2008	7.6	490000	Mark Osborne	Jonathan Aibel	United States	130,000,000
10	Cars	G	Comedy	2006	7.1	440000	John Lasseter	Dan Fogelman	United States	120,000,000
11	Despicable Me	PG	Comedy	2010	7.6	520000	Pierre Coffin	Cinco Paul	United States	69,000,000
12	Ratatouille	G	Family	2007	8.0	750000	Brad Bird	Jan Pinkava	United States	150,000,000

 

Two drones are assigned to share the mission
Drone X : Lithium-ion battery powered
- Baseline (sea-level, test conditions): can carry 2 kg payload with 55 minutes of flight time at a cruise speed of 30 km/h.
- High-altitude adjustment (3,000 m): loses 25% of flight time.
- Payload adjustment: for every +1 kg beyond baseline, flight time reduces by 15%.
- Swap/charge option: battery swap 5 minutes (instant swap) or full recharge (not used here).

Drone Y: Hydrogen Fuel-cell Powered
- Baseline (sea-level, test conditions): can carry 2 kg payload with 65 minutes of flight time at a cruise speed of 35 km/h.
- High-altitude adjustment (3,000 m): loses 10% of flight time.
- Payload adjustment: for every +1 kg beyond baseline, flight time reduces by 10%.
- Refuel options: 10 minutes (tank swap) or 25 minutes (tank refill)
- Extra system mass: carries +0.5 kg system weight for fuel-cell cooling (counts in payload penalty).

Mission Requirement:

· Deliver a total of 12 kg medicines.

· The drones must split the load and complete two trips each.

· Outbound leg (with payload): apply payload penalty

· Return leg (empty): no payload penalty

· A drone can only depart if its effective flight time ≥ trip time.

· A low-battery alert triggers if a drone cannot complete the round trip with remaining endurance; if so, a swap/refuel must occur at base before the next trip.

· Loading/unloading is instantaneous.

Hint for Calculation:

· Effective flight time (minutes) = Base flight time − (altitude penalty + payload penalty)

· Trip time (minutes) = (Round trip distance(km) ÷ cruise speed(km/h))× 60

Once the mission is complete: Which drone delivers its medicines first, and what is the total time required for that drone to finish both trips?

Two drones are assigned to share the mission. Baseline (Sea Level, Test Condition):

Drone X: Lithium-ion Battery Powered

· Can carry 3 kg payload with 40 minutes of flight time at a cruise speed of 30 km/h

· Battery replacement: 2 minutes

Drone Y: Hydrogen Fuel-cell Powered

· Can carry 4 kg payload with 60 minutes of flight time at a cruise speed of 35 km/h

· Refuel (tank swap): 10 minutes

Payload Adjustment Rule:

· Drone X: Flight time reduces by 8 minutes for every additional 1 kg payload

· Drone Y: Flight time reduces by 5 minutes for every additional 1 kg payload

Mission Rules:

· Total medicines to deliver: 28 kg

· Each drone carries 4 kg per trip

· A drone can only depart if its effective flight time ≥ trip time.

· If effective flight time is less than the round-trip time → replace/refuel before flying again.

· Loading/unloading is instant.

Hint for Calculation:

· Effective flight time (minutes) = Base flight time − Payload penalty

· Trip time (minutes) = (Round trip distance(km) ÷ Cruise speed(km/h))× 60

Once the mission is complete:
Which drone can deliver more medicines safely with less effective flight time (including replace/refuel times), and how much total weight (kg) of medicines will that drone deliver?