Introduction to SQL

Course Content

Introduction to SQL

Introduction to SQL

1. Getting Started

What is SQL?Setting Up SQL and SSMS Getting Familiar with SSMS Structure of an SQL Database System The SELECT Statement Challenge: First Day as a Librarian Challenge: Find all Book Titles Challenge: Find Book Titles and Authors The DISTINCT Keyword Challenge: Find all Genres

2. Filtering Data

The WHERE Clause Challenge: Help a Reader Find Their Favorite Genre Challenge: Fetching Older Books Conditional Expressions: Comparison Operators Challenge: Avoid the Damaged Books Challenge: Highlight New Arrivals Challenge: Budget-Friendly Books Conditional Expressions: The AND Operator Challenge: Find Specific Books for a Reader Challenge: Defining Ranges Challenge: Multiple Conditions Conditional Expressions: The OR Operator Challenge: Matching Multiple Options Challenge: Excluding A Range Conditional Expressions: The NOT Operator Challenge: Negating a Range

3. Sorting Data

Basic Sorting Challenge: Sorting the Library Catalogue Sorting by Multiple Columns Challenge: Further Sorting the Catalogue Sorting after Filtering Data Challenge: Find the Cheapest Books Challenge: Help a Customer Find a Book The LIMIT Keyword Challenge: Select Books for the Collector's Section Challenge: Affordable Books

4. Populating a Database

Creating a Table Challenge: Creating a new Books Table Inserting Rows into a Table Challenge: Inserting Data Challenge: Inserting More Data Inserting Multiple Rows Challenge: Inserting Bulk Data Primary Keys The SERIAL Keyword Challenge: Changing the Books Structure Specifying Default Values Challenge: Further Improving the Books Table

5. Updating & Deleting Data

Updating Rows in a Table Challenge: Correct Book's Data Challenge: Updating Page Counts Challenge: Mark Archived Books As Unavailable Deleting Rows Challenge: Deleting Outdated Records Duplicating Tables Challenge: Create a Table Backup Challenge: Generate a Summarized Books Table Deleting Tables

Duplicating Tables

We can create a duplicate of a table using the following query:

CREATE TABLE table_name AS (SELECT STATEMENT);

The above query creates a new table named table_name and inserts the results of the SELECT STATEMENT into it.

Creating an Exact Copy

CREATE TABLE students_duplicate AS SELECT * FROM students;

This query creates a new table called students_duplicate from the result of the query SELECT * FROM students;.

Creating a Concise Table

We can also create a more concise table by selecting specific columns from the target table:

CREATE TABLE students_basic_info AS SELECT name, age FROM students;

This query creates a new table named students_basic_info containing only the first_name and age columns from the students table.

Key Points

The new table (table_name) is created with the structure and data of the SELECT query's result;
This method is useful for creating backups, filtered copies, or summary tables.

Demonstration:

Everything was clear?

Thanks for your feedback!

Section 5. Chapter 7

Ask AI

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Course Content

Introduction to SQL

Introduction to SQL

1. Getting Started

What is SQL?Setting Up SQL and SSMS Getting Familiar with SSMS Structure of an SQL Database System The SELECT Statement Challenge: First Day as a Librarian Challenge: Find all Book Titles Challenge: Find Book Titles and Authors The DISTINCT Keyword Challenge: Find all Genres

2. Filtering Data

The WHERE Clause Challenge: Help a Reader Find Their Favorite Genre Challenge: Fetching Older Books Conditional Expressions: Comparison Operators Challenge: Avoid the Damaged Books Challenge: Highlight New Arrivals Challenge: Budget-Friendly Books Conditional Expressions: The AND Operator Challenge: Find Specific Books for a Reader Challenge: Defining Ranges Challenge: Multiple Conditions Conditional Expressions: The OR Operator Challenge: Matching Multiple Options Challenge: Excluding A Range Conditional Expressions: The NOT Operator Challenge: Negating a Range

3. Sorting Data

Basic Sorting Challenge: Sorting the Library Catalogue Sorting by Multiple Columns Challenge: Further Sorting the Catalogue Sorting after Filtering Data Challenge: Find the Cheapest Books Challenge: Help a Customer Find a Book The LIMIT Keyword Challenge: Select Books for the Collector's Section Challenge: Affordable Books

4. Populating a Database

Creating a Table Challenge: Creating a new Books Table Inserting Rows into a Table Challenge: Inserting Data Challenge: Inserting More Data Inserting Multiple Rows Challenge: Inserting Bulk Data Primary Keys The SERIAL Keyword Challenge: Changing the Books Structure Specifying Default Values Challenge: Further Improving the Books Table

5. Updating & Deleting Data

Updating Rows in a Table Challenge: Correct Book's Data Challenge: Updating Page Counts Challenge: Mark Archived Books As Unavailable Deleting Rows Challenge: Deleting Outdated Records Duplicating Tables Challenge: Create a Table Backup Challenge: Generate a Summarized Books Table Deleting Tables

Duplicating Tables

We can create a duplicate of a table using the following query:

CREATE TABLE table_name AS (SELECT STATEMENT);

The above query creates a new table named table_name and inserts the results of the SELECT STATEMENT into it.

Creating an Exact Copy

CREATE TABLE students_duplicate AS SELECT * FROM students;

This query creates a new table called students_duplicate from the result of the query SELECT * FROM students;.

Creating a Concise Table

We can also create a more concise table by selecting specific columns from the target table:

CREATE TABLE students_basic_info AS SELECT name, age FROM students;

This query creates a new table named students_basic_info containing only the first_name and age columns from the students table.

Key Points

The new table (table_name) is created with the structure and data of the SELECT query's result;
This method is useful for creating backups, filtered copies, or summary tables.

Demonstration:

Everything was clear?

Thanks for your feedback!

Section 5. Chapter 7

some-alt