The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

Basic SQL Queries

Next ►
►

In this section, we will cover the fundamentals of writing basic SQL queries in BigQuery. SQL (Structured Query Language) is the standard language for interacting with relational databases, and BigQuery uses a variant of SQL called Standard SQL. By the end of this section, you will be able to write simple SQL queries to retrieve and manipulate data in BigQuery.

Key Concepts

  1. SELECT Statement: Used to select data from a database.
  2. FROM Clause: Specifies the table from which to retrieve the data.
  3. WHERE Clause: Filters records based on specified conditions.
  4. ORDER BY Clause: Sorts the result set in ascending or descending order.
  5. LIMIT Clause: Limits the number of rows returned in the result set.

SELECT Statement

The SELECT statement is used to query the database and retrieve data. The basic syntax is:

SELECT column1, column2, ...
FROM table_name;

Example

SELECT name, age
FROM `my_dataset.my_table`;

This query retrieves the name and age columns from the table my_table in the dataset my_dataset.

WHERE Clause

The WHERE clause is used to filter records that meet certain conditions. The basic syntax is:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example

SELECT name, age
FROM `my_dataset.my_table`
WHERE age > 30;

This query retrieves the name and age columns for records where the age is greater than 30.

ORDER BY Clause

The ORDER BY clause is used to sort the result set. The basic syntax is:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC];

Example

SELECT name, age
FROM `my_dataset.my_table`
ORDER BY age DESC;

This query retrieves the name and age columns and sorts the results by age in descending order.

LIMIT Clause

The LIMIT clause is used to limit the number of rows returned. The basic syntax is:

SELECT column1, column2, ...
FROM table_name
LIMIT number;

Example

SELECT name, age
FROM `my_dataset.my_table`
LIMIT 5;

This query retrieves the name and age columns and limits the result set to 5 rows.

Practical Exercises

Exercise 1: Basic SELECT Query

Task: Write a query to retrieve the first_name and last_name columns from the employees table.

Solution:

SELECT first_name, last_name
FROM `my_dataset.employees`;

Exercise 2: Filtering Data with WHERE

Task: Write a query to retrieve the first_name and last_name columns from the employees table where the department is 'Sales'.

Solution:

SELECT first_name, last_name
FROM `my_dataset.employees`
WHERE department = 'Sales';

Exercise 3: Sorting Data with ORDER BY

Task: Write a query to retrieve the first_name and last_name columns from the employees table and sort the results by last_name in ascending order.

Solution:

SELECT first_name, last_name
FROM `my_dataset.employees`
ORDER BY last_name ASC;

Exercise 4: Limiting Results with LIMIT

Task: Write a query to retrieve the first_name and last_name columns from the employees table and limit the result set to 10 rows.

Solution:

SELECT first_name, last_name
FROM `my_dataset.employees`
LIMIT 10;

Common Mistakes and Tips

  • Missing Semicolon: Always end your SQL statements with a semicolon (;).
  • Case Sensitivity: SQL keywords are not case-sensitive, but table and column names can be, depending on the database.
  • Quoting Identifiers: Use backticks (`) to quote table and column names that contain special characters or are reserved words.

Conclusion

In this section, you learned how to write basic SQL queries using the SELECT, FROM, WHERE, ORDER BY, and LIMIT clauses. These fundamental skills are essential for querying and manipulating data in BigQuery. In the next section, we will dive deeper into filtering and sorting data to refine your queries further.

◄ Previous
◄
Next ►
►
The Project | About Us | Contribute | Donations | License
© Copyright 2024. All rights reserved