SQL: A Practical Introduction for Querying Databases

Instructor: Rav Ahuja

MODULE-1- Getting started with SQL

Course Introduction

In this SQL course, I learned the basics of the SQL language and relational databases. It helped me
develop the skills to write different SQL queries and use them to access and manage data in databases.
We started with simple queries like SELECT, INSERT, UPDATE, and DELETE. I also learned how to create
tables, load them with data, filter, sort, and group results, use built-in functions, write nested queries,
and join data from multiple tables. The course included hands-on labs and a project with real-world data.
By the end, I gained a solid understanding of SQL, which will help me in my professional journey as a
data professional.

Introduction to Databases

In this part of the course, I learned about the basics of SQL and the relational database model. It
included lab exercises, review questions, and a final exam. By the end, I was able to explain SQL basics
and understand different aspects of relational databases. SQL is a language used to interact with
relational databases, which store and manage data. A relational database organizes data into tables, and
these tables can be related to each other. The five main SQL commands I learned were create table,
insert data, select data, update data, and delete data. SQL is commonly used in industries like banking,
transportation, and healthcare.

SELECT Statement

In this section, I learned how to retrieve data from a database using the SELECT statement. SELECT is
used to read and change data in a table. I can retrieve all columns from a table with "SELECT FROM
table_name" or select specific columns by naming them. I also learned how to filter the results using the
WHERE clause, which allows me to use comparison operators like equal to, greater than, less than, and
not equal to. These tools help me narrow down the data I need from the database. Overall, I found the
SELECT statement to be a very powerful tool for fetching data from tables.

COUNT, DISTINCT, LIMIT

I also learned about three important SQL expressions that are used with the SELECT statement:
1. COUNT: This function helps me get the number of rows that match a query. I can use it to count all
rows in a table or just specific rows that meet certain conditions.

2. DISTINCT: This is useful when I want to remove duplicates from the results and retrieve only unique
values. For instance, I could use DISTINCT to get a list of unique countries that have won gold medals.

3. LIMIT: This expression allows me to limit the number of rows returned from the query. It’s really
helpful when I only want to look at a few rows instead of a large dataset.

These expressions have been very helpful in improving my SQL querying skills.

INSERT Statement

In this part, I learned how to add new data to a table using the INSERT statement. The INSERT statement
is used to add new rows to a database table. Its syntax includes the table name, column names, and the
values I want to add. I can either insert one row at a time or multiple rows at once. It’s important to
make sure that the number of values I provide matches the number of columns in the table. An example
we worked on was adding data to the author table by inserting rows for Raul Chong and Rav Ahuja. By
the end of this section, I was able to understand the syntax and different methods for adding data to
tables.

UPDATE and DELETE Statements

In this part of the course, I learned about how to update and delete data in tables using the UPDATE and
DELETE statements.

1. UPDATE: The UPDATE statement allows me to modify data in a table. The syntax is:

`UPDATE [TableName] SET [ColumnName] = [Value] WHERE [Condition]`

The table and column names tell the database where the change needs to be made, and the WHERE
clause specifies which rows should be updated. If I don’t use the WHERE clause, all rows in the table
would be updated.

Example: To update the author's name with AUTHOR_ID A2, I would use:

`UPDATE AUTHOR SET LAST NAME = KATTA, FIRST NAME = LAKSHMI WHERE AUTHOR_ID = A2`

2. DELETE: The DELETE statement allows me to remove rows from a table. The syntax is:

`DELETE FROM [TableName] WHERE [Condition]`

The table name specifies which table to delete from, and the WHERE clause tells the database which
rows to remove. Without the WHERE clause, all rows in the table would be deleted.
 Example: To delete the rows with AUTHOR_ID A2 and A3, I would write:

`DELETE FROM AUTHOR WHERE AUTHOR_ID IN ('A2', 'A3')`

Both the UPDATE and DELETE statements need the WHERE clause to ensure only specific rows are
changed or removed. Without the WHERE clause, all rows would be affected.

MODULE-2- Introduction to relational databases and

tables
Relational Database Concepts

In this video lecture, I learned about the different types of models used in databases, such as the
Relational Model and the Entity Relationship Model. The Relational Model is the most commonly
used because it allows for data independence and provides logical and physical storage
independence. The building blocks of the Entity Relationship Model are entities and attributes:

- Entities represent objects that exist independently.

- Attributes provide more information about those entities.

When these entities are mapped to a relational database, the entities become tables, and the attributes
become columns. Common data types in relational databases include characters, numbers, and
timestamps. Each table is assigned a primary key, which uniquely identifies each row in the table and
prevents duplication.

Types of SQL Statements (DDL vs. DML)

This section focused on the two main types of SQL statements:

1. DDL (Data Definition Language) Statements: Used to define, change, or drop database objects like
tables.

- CREATE: Used for creating tables and defining their columns.

- ALTER: Used for altering tables (adding/dropping columns, modifying datatypes).

- TRUNCATE: Deletes all data in a table but leaves the table itself.

- DROP: Deletes a table entirely.

2. DML (Data Manipulation Language) Statements: Used to manipulate data within tables.
 - INSERT: Adds new rows to a table.

- SELECT: Reads or retrieves data from a table.

- UPDATE: Modifies existing rows in a table.

- DELETE: Removes rows of data from a table.

The key takeaway here is that DDL is used to define or change the structure of the database, while
DML is used to manipulate the data within the tables.

CREATE TABLE Statement

In this part, I learned how to create a table using the CREATE TABLE statement. The syntax is:

```sql

CREATE TABLE table_name (

column1 datatype constraints,

column2 datatype constraints

);

```

In the video, they showed an example of creating a table for Canadian provinces using CHAR and
VARCHAR datatypes. Another example focused on creating an AUTHOR table in a library database,
where Author_ID was assigned as the Primary Key to ensure uniqueness. The video emphasized the
importance of the Primary Key constraint to prevent duplicate entries and the NOT NULL constraint,
which ensures that certain fields cannot contain null values.

ALTER, DROP, and TRUNCATE Tables

This section focused on how to modify or delete tables using the ALTER, DROP, and TRUNCATE SQL
statements.

1. ALTER TABLE Statement:

- Used to modify an existing table (e.g., adding/removing columns, changing data types).

- Example:

```sql

ALTER TABLE author ADD COLUMN telephone_number BIGINT;

ALTER TABLE author ALTER COLUMN telephone_number SET DATA TYPE CHAR(20);

ALTER TABLE author DROP COLUMN telephone_number;

 ```

2. DROP TABLE Statement:

- Used to delete a table and all its data from the database.

- Example:

```sql

DROP TABLE author;

```

3. TRUNCATE TABLE Statement:

- Deletes all rows of data in a table but leaves the table structure intact, and is more efficient than
using DELETE without a WHERE clause.

- Example:

```sql

TRUNCATE TABLE author IMMEDIATE;

```

These statements give me the ability to modify table structures, remove columns, or even delete entire
tables and data when needed.

How to Create a Database Instance on Cloud

In this video, I learned about Cloud databases, which are built and accessed through a Cloud platform.
They offer the same functionality as traditional databases, but with added flexibility, scalability, and ease
of use. Some advantages include disaster recovery and easier management.

Examples of relational Cloud databases include:

- IBM Db2 on Cloud

- Oracle Database Cloud Service

- Microsoft Azure SQL Database

The video demonstrated how to create a database instance on IBM Db2 on Cloud, where I learned to:

1. Select the Db2 service.

2. Choose the Lite plan.

3. Manage the database instance.

4. Understand the importance of service credentials to access the database from applications.

Overall, this section provided a good introduction to the benefits of Cloud databases and a step-by-step
guide on creating and managing a Cloud database instance.

MODULE-3- Intermediate SQL

Using String Patterns and Ranges

In this video, I learned advanced techniques to retrieve data from a relational database table. The
primary focus is on simplifying SQL queries, specifically SELECT statements, using string patterns, ranges,
and sets of values. Here's a breakdown of the key concepts:

- Basic structure of a SELECT statement: A SELECT statement allows us to retrieve specific columns from a
table or limit the results using the WHERE clause. The WHERE clause helps to filter the data based on
specified conditions.

- String patterns: The LIKE predicate is used to search for a specific pattern within a column’s data. I
learned that wildcards, such as `%` (for any sequence of characters) and `_` (for a single character), can
be used to perform pattern matching on strings.

- Ranges: The BETWEEN operator helps retrieve data within a specified range of values. This operator is
useful for filtering results based on a range of numeric or date values.

- IN operator: This operator allows us to specify a set of values within the WHERE clause. It simplifies
queries when looking for rows matching one of several possible values, without the need for multiple OR
conditions.

In summary, this video helped me understand how to use string patterns and ranges to make SELECT
statements more efficient and easier to read.

Sorting Result Sets

In this video, I learned how to sort the results of a SQL SELECT statement. Sorting is an essential part of
data retrieval, as it allows me to present the data in a specific order, making it more useful and easier to
understand.
- Default behavior: When performing a basic SELECT statement, the result set is retrieved without any
specific order. This means the rows might be returned in any sequence, which is not always useful.

- ORDER BY clause: To sort the result set, I can use the ORDER BY clause. This clause allows me to specify
the column or columns by which the result set should be sorted. By default, the ORDER BY clause sorts
the data in ascending order (from smallest to largest).

- Ascending and descending order: If I want to sort in descending order (from largest to smallest), I can
use the keyword DESC after the column name. This reverses the default sorting order.

- Sorting by column sequence number: Instead of specifying the column name, I can also use the
column’s sequence number (its position in the SELECT statement) to sort the data. This is especially
useful when working with queries that involve multiple columns.

Now, I am equipped with the knowledge to sort result sets in either ascending or descending order and
how to specify which column or sequence number to use for sorting.

Grouping Result Sets

In this video, I learned how to group and manipulate result sets using advanced SQL techniques. This
helps me analyze and summarize data effectively.

- Eliminating duplicates: Sometimes, SELECT statements return duplicate values in the result set. To
handle this, I can use the DISTINCT keyword to remove duplicates and only show unique values.

- Grouping data: The GROUP BY clause allows me to group data based on one or more columns. For
example, if I have data on authors and their countries, I can group the authors by their country to see
how many authors are from each country.

- Assigning column names: When using aggregate functions (like COUNT), the result set might show a
numeric value instead of a descriptive column name. To make the result clearer, I can use the "AS"
keyword to give the derived column a meaningful name.

- HAVING clause: The HAVING clause is used in combination with GROUP BY to filter grouped data based
on specific conditions. For example, I can use the HAVING clause to find countries with more than four
authors.

Overall, this video provided valuable insights into how to eliminate duplicates, group data, assign
meaningful column names, and restrict grouped results for better analysis.

Built-in Database Functions

This video introduced me to built-in SQL functions that help manipulate and analyze data directly within
the database. These functions eliminate the need to retrieve data into external applications for
processing.
- Aggregate functions: These functions work on a collection of values and return a single value or null.
Some of the key aggregate functions include SUM, MIN, MAX, and AVG. These are useful for performing
calculations on a set of rows, such as finding the total, minimum, maximum, or average value in a
column.

- Scalar functions: These functions operate on individual values rather than collections. Scalar functions
can be applied in the WHERE clause to filter results or used in calculations.

- String functions: These functions are specifically designed to work with string values. They allow
operations like trimming, concatenation, finding the length of a string, and more.

- Combining functions: I can also chain functions together, where the output of one function can be used
as input to another. This provides flexibility in building more complex queries.

SQL functions are powerful tools that make it easier to manipulate and analyze data directly within the
database without needing to retrieve and process it externally.

Date and Time Built-in Functions

In this video, I learned about built-in SQL functions for working with date and time values. Most
databases provide special data types for dates and times, and there are various functions available for
extracting specific details from these values.

- Date and time types: Databases typically use specialized data types like DATE, TIME, and TIMESTAMP to
store date and time information.

- Extracting date components: SQL provides functions that allow me to extract specific parts of a date or
time. For example, I can extract the day, month, day of the week, and even the hour from a timestamp.

- Date and time arithmetic: SQL also allows for date arithmetic. For instance, I can calculate the date
three days after a given date or find the number of days between two dates.

- Current date and time: Special registers like CURRENT_DATE and CURRENT_TIME can be used in
calculations. For example, I can calculate how many days have passed since a particular event by
subtracting a specific date from the current date.

These date and time functions are extremely useful when working with time-sensitive data or
performing calculations based on dates.

Sub-Queries and Nested Selects

In this video, I learned how to use sub-queries, also known as nested SELECT statements, to perform
more complex queries in SQL. A sub-query is essentially a query placed inside another query, allowing
me to solve problems that would otherwise be difficult.
- Sub-query basics: A sub-query is a query within parentheses that is nested inside another query. The
sub-query can be placed in the WHERE clause, the SELECT clause, or the FROM clause of the outer query.

- WHERE clause: I can use sub-queries to filter results in the WHERE clause. For example, I can retrieve
records from a table that meet certain conditions based on the result of a nested query.

- Derived tables: A derived table is essentially a sub-query that acts as a temporary table, providing a
data source for the outer query. This is helpful when I need to perform operations on an intermediate
result set.

- Complex queries: By using sub-queries, I can overcome limitations of aggregate functions and create
more powerful queries.

Sub-queries and nested SELECT statements are essential tools that help me write more complex and
efficient SQL queries.

Working with Multiple Tables

In this video, I learned how to work with multiple tables in SQL queries. Working with more than one
table is common when dealing with relational databases, and this video shows several ways to combine
data from different tables.

- Sub-queries: I can use sub-queries to retrieve data from multiple tables. For example, I can use a sub-
query to filter employee records based on department ID or location ID, then join it with the department
table for more information.

- Joins: I can also use joins to access data from multiple tables. A JOIN allows me to retrieve related data
from two or more tables based on a shared column.

- Table aliases: When working with multiple tables, I can use table aliases to simplify the query and avoid
confusion. Aliases are especially helpful when the same table is used multiple times in a query.

By mastering these techniques, I can write more complex queries that pull relevant data from multiple
tables and combine them in meaningful ways.

MODULE-4- Working with real world data sets

Working with Real-World Datasets

In this video, Working with Real-World Datasets, I learned how to handle real-world data in CSV files.
One of the key things covered was how to load CSV files into a database, particularly DB2. I discovered
how to map the attribute names from the CSV file headers to column names in the database, making
sure that everything lines up properly.

I also learned how to query databases where column names are in lower case or mixed case, or even
have spaces and special characters, which can sometimes be tricky. This session showed me practical
ways to deal with these issues. Another valuable tip was how to limit the number of rows retrieved in a
query, which is especially helpful when working with large datasets.

By the end of this video, I felt much more confident about working with real-world data in CSV files and
how to manage databases more efficiently with that data.

Getting Table and Column Details

In this session, I explored how to retrieve metadata for database tables and columns. I learned where
this information is stored in different databases, such as Db2, SQL Server, Oracle, and MySQL, and how to
access it.

One of the key takeaways was learning how to query for the list of tables in a database and view their
properties. I also figured out how to retrieve detailed information about each column in a table,
including its data type, size, and other characteristics.

By the end of the video, I understood how to retrieve and work with database metadata, making it easier
to manage tables and columns and filter the results to find exactly what I need.

MODULE-5- Advanced SQL

Views :In this section, I learned about views in SQL, which are a flexible way to represent data from one
or more tables. Here are the key points:

- Introduction to Views:

- A view is an alternate way of showing data from one or more tables or existing views.

- A view can include all or just some of the columns from the original tables.

- When you create a view, you create a named result of a query that can be accessed like a table.

- Benefits and Uses of Views:

- Views let you show selected data, which can help to hide sensitive information.

- You can combine multiple tables into one view to simplify complex queries.

- They also simplify access by allowing users to interact with the view without needing permission to
the base tables.

- Views can show only the data relevant to a specific task.

- Creating a View:

- To create a view, I use the `CREATE VIEW` statement.

- I need to give the view a name and list the columns I want to include.
 - The `AS SELECT` clause is used to specify the columns and `FROM` defines the base table.

- I can add a `WHERE` clause to filter the data that gets shown in the view.

- Working with Views:

- Views are dynamic, meaning they reflect the most current data from the SELECT query used to create
them.

- When I use a view in another SQL statement, it behaves as if I’m running the original SELECT query.

- The SELECT query used to create the view can include views, tables, and clauses like `WHERE`, `GROUP
BY`, and `HAVING`.

- Removing a View:

- To completely remove a view from the database, I use the `DROP VIEW` statement.

In conclusion, views are a useful tool to secure, simplify, and customize how data is presented and
accessed.

Stored Procedures

This part of the course focused on stored procedures in SQL. Here’s what I learned:

- What are Stored Procedures:

- Stored procedures are a set of SQL statements that are stored on the database server and can be run
as a single unit.

- They offer benefits like reducing network traffic, improving performance, reusing code, and enhancing
security.

- Benefits:

- Stored procedures are useful because they can be reused across multiple applications and users.

- They help in reducing the amount of SQL sent over the network, which can improve performance.

- Since the code is stored on the server, it keeps the application logic more secure.

- Creating a Stored Procedure:

- To create a stored procedure, I use the `CREATE PROCEDURE` statement, give it a name, and list any
parameters.
 - The main logic is written inside `BEGIN` and `END` blocks.

- Calling a Stored Procedure:

- Stored procedures can be called from other SQL queries or external applications using the `CALL`
statement.

I also learned that SQL is not a full-fledged programming language, so it’s not recommended to put all
business logic inside stored procedures. They are mainly used for tasks like data modification, reporting,
and automated workflows.

ACID Transactions

In this section, I learned about ACID transactions in SQL. ACID stands for:

1. Atomicity: A transaction is treated as a single unit. If any part of the transaction fails, the entire
transaction fails, and the database is left unchanged.

2. Consistency: Every transaction takes the database from one valid state to another, maintaining data
integrity.

3. Isolation: Transactions occur independently without interference from other operations. The result is
the same whether transactions are run concurrently or sequentially.

4. Durability: Once a transaction is committed, it is permanent, even in the case of a system failure.

These principles ensure that databases are reliable and stable, especially when handling multiple
transactions at once.

Join Overview - October 17, 2024

This part introduced joins in SQL. A join is used to combine rows from two or more tables based on
related columns.

- Types of Joins:

- INNER JOIN: Only returns rows where there is a match in both tables.
 - LEFT (OUTER) JOIN: Returns all rows from the left table and matched rows from the right table. If
there’s no match, it still returns rows from the left.

- RIGHT (OUTER) JOIN: Returns all rows from the right table and matched rows from the left.

- FULL (OUTER) JOIN: Returns rows when there’s a match in either table.

- CROSS JOIN: Combines all rows from both tables (Cartesian product).

Each type of join serves different purposes, depending on how I need to relate the data in multiple
tables.

This section gave me a clearer understanding of how to efficiently retrieve related data across multiple
tables.