Retrieving Data with SELECT and FROM

Communicating with Databases

The Foundation of SQL

To get data out of a database, you use a language called SQL (Structured Query Language). Every query starts with two fundamental building blocks: SELECT and FROM.

• SELECT: Specifies which columns you want.
• FROM: Specifies which table contains the data.

Welcome to your first step in mastering data analysis with SQL. To talk to a database, you need to know where to look and what to grab. The SELECT keyword acts like a shopping list, telling the database exactly which columns of data you want to see. The FROM keyword tells the database which specific table that list belongs to.

• SELECT identifies columns.
• FROM identifies the table.
• SQL keywords are capitalized by convention.

The Movie Table Schema

The Movie Table

Throughout this course, we will query the Movie table. Understanding its schema (structure) is crucial for writing accurate queries.

Columns:

• ID (integer)
• Title (varchar/text)
• Genre (varchar/text)
• RatingCode (varchar/text)
• Year (integer)

Before we write code, let's look at our workspace. We will be using the Movie table. Notice the five columns: ID, Title, Genre, RatingCode, and Year. Here are a few records, like 'Jurassic Park' and 'The Godfather', so you can see how the data is stored inside.

• A schema defines the table's structure.
• The Movie table contains 5 columns.
• Data types include integers and varchars (text).

Syntax and the Wildcard (*)

The Basic Syntax

The standard order for a query is:

SELECT column_name
FROM table_name;

Using the Wildcard

If you want to see all columns in a table, use the asterisk *.

SQL follows a strict order: SELECT first, then FROM. If you are exploring a new table and want to see everything at once, use the asterisk wildcard. Writing 'SELECT star FROM Movie' will return every single column and row available.

• SELECT always comes before FROM.
• The asterisk (*) is a wildcard for all columns.
• Queries end with a semicolon (;).

Selecting Specific Columns

Be Specific

In data analysis, you usually only need a subset of data. This makes queries faster and cleaner.

List the columns you want after SELECT, separated by commas.

SELECT Title, Year
FROM Movie;

Often, you don't need the ID or the Rating. To be specific, simply list the column names. For example, to see just the names and release years, you would write: SELECT Title, comma, Year. Notice how the result set is now much narrower and easier to read.

• Comma-separated list for multiple columns.
• No comma after the final column name.
• Selecting only what you need improves performance.

Hands-on: Query the Movie Table

Practice Challenge

Write a query to retrieve the Title, Genre, and RatingCode for all films in the Movie table.

Remember to capitalize your keywords!

Now it's your turn. Write a query that retrieves the Title, Genre, and RatingCode. Type your code in the editor and click 'Submit' to see if you got it right.

• Apply SELECT and FROM.
• Correct comma placement.
• Proper table naming.