SQL Database Interaction Project with Azure Data Studio

Introduction

This project serves as an introduction to creating and interacting with SQL databases using Microsoft Azure and Azure Data Studio. We'll cover the creation of a SQL database on Azure, setting up Azure Data Studio, and performing basic database operations.

Prerequisites

• Microsoft Azure
• Azure Data Studio
• Basic SQL Scripts, (SQL scripts used will be provided)

Step 1: Create an Azure SQL Database

1. Log in to your Azure portal.
2. Create a new Azure SQL Database.
3. Configure the database settings, including server, resource group, and pricing tier.
4. Make sure to allow public access and include your local machines IP in the firewall settings.

Step 2: Set Up Azure Data Studio

1. Download and install Azure Data Studio.
2. Launch Azure Data Studio on your local machine.

Step 3: Connect to Azure SQL Database

1. In Azure Data Studio, click "Connections" on the sidebar.
2. Click "New Connection" and select "Azure" as the data source.
3. Enter your Azure SQL Database connection details, including server name, authentication type, and credentials.
4. Click "Connect" to establish a connection.

Using the connection string found in the Azure Portal we replace the portion (your_password) with our own credentials and hit connect.

Step 4: Create Tables

1. In Azure Data Studio, open a new query window.

2. Use SQL scripts to create tables for your database (e.g., "Authors," "Books," "Categories"). Here I show the script to add the authors table. I went ahead and added Books and Categories as well.

Step 5: Insert Data

In this step, we'll populate our database with sample data by inserting authors, books, and categories.

Insert Authors:

1. In a query window in Azure Data Studio, use SQL scripts to insert author records into the "Authors" table. For example, insert authors "John Jacob," "Kimmy Karen," and "Nate Pena."

Insert Categories:

1. Next, insert category records into the "Categories" table. In our example, we'll add "Fiction" and "Non-Fiction" categories.

Insert Books:

1. Finally, insert book records into the "Books" table. Assign each book to an author and a category.

Heres the Books we added:

These SQL scripts will insert sample data into your tables. Customize the data as needed for your own projects. After executing these scripts, your database should be populated. Lets make sure with a new query.

Step 6: Retrieve All Books

1. Write and execute a SQL query to retrieve all books from the "Books" table.
2. Observe the results to ensure all books are displayed.

Step 7: Retrieve Books by Author

1. Create and run a SQL query to retrieve books by a specific author (e.g., author with ID 3, which is Nate Pena).
2. Review the results to verify that only books by the selected author are shown.

Step 8: Calculate Average Book Price

1. Write an SQL query to calculate the average price of all books in the "Books" table.
2. Record the calculated average price and note the prices of other books in the database.

Step 9: Adjust Book Price

1. Modify the price of one of the books (e.g., "The Great Grumblebums of Zoggleton") by executing an SQL UPDATE query.
2. Increase the price to your desired value.

Step 10: Recheck Average Price

1. Recalculate the average price of all books in the "Books" table using an SQL query.
2. Compare the new average price to the previous calculation to confirm the price adjustment.

What We Learned

In this project, we learned how to:

• Create an Azure SQL Database.
• Set up Azure Data Studio.
• Connect to an Azure SQL Database using Azure Data Studio.
• Create tables in a SQL database.
• Insert data into tables.
• Retrieve data using SQL queries.
• Calculate and adjust values in the database.

This project serves as an introduction to working with SQL databases, and it showcases the basics of creating, populating, and interacting with a database using Microsoft Azure and Azure Data Studio.