Data Analyst Full Course for Beginners | Sql Part 5

Restoring and managing databases is a crucial skill for any data professional. In this guide, we’ll walk you through the steps to restore a database and perform basic SQL operations such as SELECT, ALIAS, ORDER BY, DISTINCT, and WHERE clauses. Let’s dive right in!

Download Dataset

Step 1: Restoring a Database

Before working with a database, we need to restore it. Here’s how you can do it:

  1. Create a Database

    • Right-click on the database section in your SQL tool (e.g., pgAdmin).
    • Select “Create” → “Database” and name it, e.g., DVD Rental.
    • Click Save to create the database.

  2. Restore the Database

    • Right-click on the newly created database and choose “Restore.”
    • Browse and select the backup file you want to restore. (You can download the provided backup file.)
    • If prompted with an error about the binary path, follow these steps:
      • Go to pgAdmin Settings → Binary Path → Enter the path of the SQL tool’s bin folder (e.g., C:/Program Files/PostgreSQL/bin).
      • Save the changes and try restoring the database again.

Once the database is successfully restored, you can explore its tables and schemas.

Step 2: Exploring Tables in the Database

To view all tables in the database, use the following query:

 
SELECT * FROM information_schema.tables WHERE table_schema = 'public';

This query lists all tables in the public schema. If you only want base tables, add a condition:

 
SELECT * FROM information_schema.tables WHERE table_schema = 'public' AND table_type = 'BASE TABLE';

Step 3: Working with the SELECT Statement

Fetching All Data

To view all records from a specific table, such as the customer table:

 
SELECT * FROM customer;

Fetching Specific Columns

If you only need the first_name and last_name columns:

 
SELECT first_name, last_name FROM customer;

Combining Columns (CONCAT)

To merge first_name and last_name into a single column:

 
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customer;

Step 4: Sorting Data with ORDER BY

The ORDER BY clause is used to sort data systematically.

  • Ascending Order (Default)

     
    SELECT * FROM customer ORDER BY first_name;

  • Descending Order

     
    SELECT * FROM customer ORDER BY last_name DESC;

  • Multiple Columns
    Sort first_name in ascending order and address_id in descending order:

     
    SELECT first_name, address_id FROM customer ORDER BY first_name ASC, address_id DESC;

Step 5: Removing Duplicates with DISTINCT

To fetch only unique values, use the DISTINCT keyword. For example, to get unique first names:

 
SELECT DISTINCT first_name FROM customer;

Step 6: Filtering Data with WHERE

The WHERE clause applies conditions to filter records.

Using Conditions

  • Fetch customers with store_id = 2:

     
    SELECT first_name FROM customer WHERE store_id = 2;

  • Fetch customers with customer_id > 200:

     
    SELECT * FROM customer WHERE customer_id > 200;

Using Multiple Conditions

  • Combine conditions with AND:

     
     
    SELECT * FROM customer WHERE customer_id > 200 AND store_id = 2;

  • Combine conditions with OR:

     
     
    SELECT * FROM customer WHERE customer_id > 200 OR store_id = 2;

Using Operators

Here are some common SQL operators you can use in the WHERE clause:

  • =: Equal to
  • >: Greater than
  • <: Less than
  • >=: Greater than or equal to
  • <=: Less than or equal to
  • !=: Not equal to
  • IN: Matches a list of values
  • BETWEEN: Matches a range of values
  • LIKE: Matches a pattern

For example:

 
SELECT * FROM customer WHERE first_name LIKE 'A%'; -- Names starting with 'A'

Conclusion

In this session, we restored a database, explored its tables, and mastered some essential SQL clauses. These tools and techniques form the foundation of working efficiently with databases. Practice these steps to enhance your SQL skills and stay tuned for more!