SQL Queries
Raj Kishore Agrawal
Data Analyst Converting Complex Data into Business Solutions | SQL | Power BI | Python
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.