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

Introduction to SQL

Next ►
►

Structured Query Language (SQL) is a standard programming language specifically designed for managing and manipulating relational databases. SQL is essential for querying, updating, and managing data stored in databases. In this section, we will cover the basics of SQL, which will serve as the foundation for working with BigQuery.

Key Concepts

  1. Database: A structured collection of data stored electronically.
  2. Table: A collection of related data entries consisting of rows and columns.
  3. Row: A single record in a table.
  4. Column: A field in a table; all rows in a table have the same columns.
  5. Query: A request for data or information from a database.

Basic SQL Commands

  1. SELECT Statement

The SELECT statement is used to retrieve data from a database.

SELECT column1, column2, ...
FROM table_name;

Example:

SELECT first_name, last_name
FROM employees;

This query retrieves the first_name and last_name columns from the employees table.

  1. WHERE Clause

The WHERE clause is used to filter records.

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

Example:

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

This query retrieves the first_name and last_name of employees who work in the Sales department.

  1. INSERT INTO Statement

The INSERT INTO statement is used to add new records to a table.

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Example:

INSERT INTO employees (first_name, last_name, department)
VALUES ('John', 'Doe', 'Marketing');

This query adds a new employee named John Doe to the Marketing department.

  1. UPDATE Statement

The UPDATE statement is used to modify existing records in a table.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example:

UPDATE employees
SET department = 'HR'
WHERE last_name = 'Doe';

This query updates the department of employees with the last name Doe to HR.

  1. DELETE Statement

The DELETE statement is used to remove records from a table.

DELETE FROM table_name
WHERE condition;

Example:

DELETE FROM employees
WHERE last_name = 'Doe';

This query deletes all employees with the last name Doe from the employees table.

Practical Exercises

Exercise 1: Basic SELECT Query

Task: Retrieve the first_name and last_name of all employees from the employees table.

Solution:

SELECT first_name, last_name
FROM employees;

Exercise 2: Filtering Data with WHERE

Task: Retrieve the first_name and last_name of employees who work in the 'Engineering' department.

Solution:

SELECT first_name, last_name
FROM employees
WHERE department = 'Engineering';

Exercise 3: Inserting Data

Task: Add a new employee named Jane Smith to the 'Finance' department.

Solution:

INSERT INTO employees (first_name, last_name, department)
VALUES ('Jane', 'Smith', 'Finance');

Exercise 4: Updating Data

Task: Change the department of the employee with the last name 'Smith' to 'Operations'.

Solution:

UPDATE employees
SET department = 'Operations'
WHERE last_name = 'Smith';

Exercise 5: Deleting Data

Task: Remove all employees from the 'Marketing' department.

Solution:

DELETE FROM employees
WHERE department = 'Marketing';

Common Mistakes and Tips

  • Syntax Errors: Ensure that SQL keywords are spelled correctly and that the correct syntax is used.
  • Case Sensitivity: SQL keywords are not case-sensitive, but table and column names might be, depending on the database system.
  • Semicolon: Always end SQL statements with a semicolon (;) to avoid errors.
  • Data Types: Ensure that the values being inserted or updated match the data types of the columns.

Conclusion

In this section, we introduced the basics of SQL, including the most commonly used commands: SELECT, INSERT, UPDATE, and DELETE. Understanding these commands is crucial for managing and manipulating data in BigQuery. In the next section, we will dive deeper into writing basic SQL queries in BigQuery, building on the foundation laid here.

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