Skip to main content

Basic SQL Query Structure

Now that you've set up your SQL environment and loaded the University schema, it's time to write your first queries! In this lesson, we'll explore the fundamental structure of SQL SELECT statements using the department, student, instructor, course, and enrollment tables you just created.

Learning Goals:

  • Understand the basic components of a SQL query
  • Learn how to use the SELECT and FROM clauses
  • Practice writing queries to retrieve specific columns
  • Execute your first SQL queries in your environment

The Anatomy of a SQL Query​

At its core, a SQL query tells the database what data you want to retrieve. The most basic query structure looks like this:

Basic Query Structure
SELECT column1, column2, ...
FROM table_name;

Let's break this down:

  • SELECT: Specifies which columns you want to retrieve
  • FROM: Specifies which table contains the data
  • Semicolon: Terminates the statement (optional in some databases, but good practice)
tip

Think of SQL as asking a question: "From this table, please select these columns." The database responds with the requested data.

The SELECT Clause​

The SELECT clause determines which columns appear in your results. You can select specific columns, all columns, or even create calculated columns.

Selecting Specific Columns​

Selecting Specific Columns
SELECT first_name, last_name, email
FROM student;

This query returns only the first_name, last_name, and email columns from the student table.

Selecting All Columns​

Selecting All Columns
SELECT *
FROM student;

The asterisk (*) is a wildcard that means "all columns." While convenient for exploration, it's better practice to specify only the columns you need in production code.

Creating Calculated Columns​

Calculated Column Example
SELECT  student_id,
        first_name,
        last_name,
        first_name || ' ' || last_name AS full_name
FROM student;

Here we calculate student's full name.

The FROM Clause​

The FROM clause specifies which table to query. Tables are the fundamental storage structures in databases where your data lives.

Querying Different Tables
-- From the instructor table
SELECT first_name, last_name, email, dept_id
FROM instructor;

-- From the course table
SELECT course_code, course_title, credits
FROM course;

-- From the department table
SELECT dept_id, dept_name
FROM department;
warning

Table and column names are case-sensitive in some database systems (like PostgreSQL) and case-insensitive in others (like MySQL). It's best practice to be consistent with your naming conventions.

Putting It All Together​

Let's work through complete examples using the University schema:

Complete Query Examples
-- Review the course catalog
SELECT course_code, course_title, credits
FROM course
ORDER BY course_code;

-- List instructors and the departments they belong to
SELECT i.instructor_id,
       i.first_name || ' ' || i.last_name AS instructor_name,
       d.dept_name
FROM instructor AS i
JOIN department AS d ON d.dept_id = i.dept_id
ORDER BY d.dept_name, instructor_name;

-- See which students are enrolled in each course this semester
SELECT e.enroll_id,
       s.first_name || ' ' || s.last_name AS student_name,
       c.course_code,
       c.course_title,
       e.semester,
       e.grade
FROM enrollment AS e
JOIN student AS s ON s.student_id = e.student_id
JOIN course  AS c ON c.course_id = e.course_id
ORDER BY e.semester, c.course_code, student_name;

Working with Multiple Tables​

While we'll cover JOINs in detail later, it's helpful to see how you might query different tables in the same session:

-- Switch between different tables in the schema
SELECT dept_id, dept_name FROM department;
SELECT course_code, credits FROM course;
SELECT first_name, last_name, enrollment_year FROM student;

Common Pitfalls​

  • Missing commas: Forgetting commas between column names in SELECT lists
  • Table name typos: Mixing up singular table names like student or course
  • Case sensitivity: Assuming case doesn't matter when it does in your database
  • Missing semicolons: Forgetting to terminate statements (can cause issues with multiple queries)
  • Selecting too much data: Using SELECT * when you only need specific columns

Summary​

You've now learned the fundamental structure of SQL queries! Remember:

  • Use SELECT to specify which columns to retrieve
  • Use FROM to specify which table to query
  • Be specific about the columns you need rather than using SELECT *
  • Always test your queries with small datasets first
  • Pay attention to your database's case sensitivity rules

Practice writing simple queries against department, student, and course in your environment to become comfortable with the basic syntax before moving on to filtering and sorting.

Quiz​

Show quiz
  1. Which clause specifies what columns to return in a SQL query?
  2. What does the * symbol represent in a SELECT statement?
  3. In the query SELECT course_code, credits FROM course; which part indicates the table being queried?
  4. Why is it generally better to specify column names instead of using SELECT *?
  5. What is the purpose of the semicolon at the end of a SQL statement?

Answers:

  1. The SELECT clause
  2. It's a wildcard that means "all columns"
  3. FROM course specifies the table
  4. Better performance, clearer intent, and safety against schema changes
  5. It terminates the SQL statement (required in some databases, good practice in all)