SQL Queries

Query 1:

USE IMBD

Explanation:

Command Purpose: The USE command sets the active database to IMBD.

Key Points:

This is crucial because SQL operations are database-specific.

Any subsequent queries will target the IMBD database.

When to Use: At the start of a session or after switching to a new database.

---

Query 2:

SHOW TABLES

Explanation:

Command Purpose: This lists all the tables within the currently selected database (IMBD).

Logic:

SQL retrieves metadata about the database to show the table names.

When to Use: When you want to explore the structure or identify available tables for querying.

---

Query 3:

DESCRIBE movies

Explanation:

Command Purpose: This retrieves the schema of the movies table.

Details:

It shows column names, data types, whether the column allows NULL values, and any constraints like PRIMARY KEY.

When to Use: Before querying a table, especially if you need to confirm its structure.

---

Query 4:

SELECT * FROM movies

Explanation:

Command Purpose: The SELECT command retrieves data. Here, * means all columns are selected.

Logic:

Fetches every column and row from the movies table.

This can be inefficient for large datasets.

When to Use: Useful during initial exploration of data.

---

Query 5:

SELECT name, year FROM movies

Explanation:

Command Purpose: Fetches only the name and year columns from the movies table.

Logic:

Limits the result set to specific columns, reducing unnecessary data retrieval.

When to Use: When you need only a subset of columns.

---

Query 6:

SELECT rankscore, name FROM movies

Explanation:

Same as Query 5 but fetches the rankscore and name columns.

---

Query 7:

SELECT * FROM movies LIMIT 20

Explanation:

Clause Explanation:

LIMIT 20 restricts the result to the first 20 rows.

Purpose: View a small, manageable subset of data for analysis.

When to Use: Testing or sampling large datasets.

---

Query 8:

SELECT * FROM movies LIMIT 20 OFFSET 40

Explanation:

Clause Explanation:

OFFSET 40 skips the first 40 rows.

LIMIT 20 fetches the next 20 rows.

Purpose: Implements pagination.

When to Use: Divide results into pages for better readability.

---

Query 9:

SELECT name, year FROM movies ORDER BY year DESC LIMIT 10

Explanation:

Clause Explanation:

ORDER BY year DESC sorts rows by year in descending order.

LIMIT 10 retrieves only the first 10 rows from the sorted result.

Purpose: Retrieves the most recent 10 movies.

When to Use: To analyze recent data trends.

---

Query 10:

SELECT name, year FROM movies ORDER BY year LIMIT 10

Explanation:

Same as Query 9 but sorts year in ascending order.

---

Query 11:

SELECT DISTINCT genre FROM movies_genres

Explanation:

Command Purpose: Fetches unique genre values.

Logic:

DISTINCT ensures no duplicate values in the result.

When to Use: Summarizing or identifying categories in the data.

---

Query 12:

SELECT DISTINCT first, last FROM directors

Explanation:

Retrieves unique combinations of first and last names.

---

Query 13:

SELECT name, year, rankscore FROM movies WHERE rankscore > 9

Explanation:

Clause Explanation:

WHERE filters rows where the rankscore is greater than 9.

Logic:

Only high-ranking movies are included in the result.

When to Use: For performance analysis or identifying top-rated items.

---

Query 14:

SELECT name, year, rankscore FROM movies WHERE rankscore > 9 ORDER BY rankscore DESC LIMIT 20

Explanation:

Same as Query 13 but sorts by rankscore in descending order and limits results to the top 20.

---

Query 15:

SELECT * FROM movies_genres WHERE genre = 'Comedy'

Explanation:

Fetches all rows where genre is exactly 'Comedy'.

---

Query 16:

SELECT * FROM movies_genres WHERE genre = 'Comedy' LIMIT 20

Explanation:

Adds a limit of 20 rows for brevity.

---

Query 17:

SELECT * FROM movies_genres WHERE genre <> 'Horror'

Explanation:

Operator: <> means "not equal to."

Filters out rows where genre is 'Horror'.

---

Query 18:

SELECT name, year, rankscore FROM movies WHERE rankscore IS NULL LIMIT 20

Explanation:

Retrieves rows with missing rankscore values.

---

Query 19:

SELECT name, year, rankscore FROM movies WHERE rankscore IS NOT NULL LIMIT 20

Explanation:

Opposite of Query 18; retrieves rows with valid rankscore.

---

Query 20:

SELECT name, year, rankscore FROM movies WHERE rankscore > 9 AND year > 2020

Explanation:

Operator: AND combines two conditions.

Filters rows where both rankscore > 9 and year > 2020.

---

Query 21:

SELECT name, year, rankscore FROM movies WHERE NOT year <= 2000 LIMIT 20

Explanation:

Filters rows where year is greater than 2000.

---

Query 22:

SELECT name, year, rankscore FROM movies WHERE rankscore > 9 OR year > 2020

Explanation:

Operator: OR retrieves rows where either condition is true.