database development assignment create and populate a database using sql

Saved searches

Use saved searches to filter your results more quickly.

To see all available qualifiers, see our documentation .

Notifications You must be signed in to change notification settings

This assignment requires you to create, populate, and manipulate a database using SQL.

KirillSBarsukov/Assignment-1-Relational-Database

Folders and files.

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Get started with SQL database projects

2 contributors

A SQL database project is a local representation of SQL objects that comprise the schema for a single database, such as tables, stored procedures, or functions. The development cycle of a SQL database project enables database development to be integrated into a continuous integration and continuous deployment (CI/CD) workflows familiar as a development best practice.

This article steps through creating a new SQL project, adding objects to the project, and building and deploying the project. Except for the Visual Studio (SQL Server Data Tools) instructions, the guide focuses on SDK-style SQL projects.

Create a new project
Add objects to the project
Build the project
Deploy the project

Prerequisites

Visual Studio 2022 Community, Professional, or Enterprise
SQL Server Data Tools (SSDT) installed in Visual Studio 2022
SQL Server Data Tools, SDK-style (preview) installed in Visual Studio 2022
SQL Database Projects extension for Azure Data Studio or SQL Database Projects extension for VS Code
SqlPackage CLI
Microsoft.Build.Sql.Templates .NET templates

To complete the deployment of a SQL database project, you need access to an Azure SQL or SQL Server instance. You can develop locally for free with SQL Server developer edition on Windows or in containers .

Step 1: Create a new project

We start our project by creating a new SQL database project before manually adding objects to it. There are other ways to create a project that enable immediately populating the project with objects from an existing database, such as using the schema comparison tools .

Select File , New , then Project .

In the New Project dialog box, use the term SQL Server in the search box. The top result should be SQL Server Database Project .

Select Next to proceed to the next step. Provide a project name, which doesn't need to match a database name. Verify and modify the project location as needed.

Select Create to create the project. The empty project is opened and visible in the Solution Explorer for editing.

In the New Project dialog box, use the term SQL Server in the search box. The top result should be SQL Server Database Project, SDK-style (preview) .

In the Database Projects view of VS Code or Azure Data Studio, select the New Project button.

The first prompt determines which project template to use, primarily based on whether the target platform is SQL Server or Azure SQL. If prompted to select a specific version of SQL, choose the version that matches the target database but if the target database version is unknown, choose the latest version as the value can be modified later.

Enter a project name in the text input that appears, which doesn't need to match a database name.

In the "Select a Folder" dialog that appears, select a directory for the project's folder, .sqlproj file, and other contents to reside in.

When prompted whether to create an SDK-style project (preview), select Yes .

Once completed, the empty project is opened and visible in the Database Projects view for editing.

With the .NET templates for Microsoft.Build.Sql projects installed, you can create a new SQL database project from the command line. The -n option specifies the name of the project, and the -tp option specifies the project target platform.

Use the -h option to see all available options.

Step 2: Add objects to the project

In Solution Explorer , right-click the project node and select Add , then Table . The Add New Item dialog appears, where you can specify the table name. Select Add to create the table in the SQL project.

The table is opened in the Visual Studio table designer with the template table definition, where you can add columns, indexes, and other table properties. Save the file when you're done making the initial edits.

More database objects can be added through the Add New Item dialog, such as views, stored procedures, and functions. Access the dialog by right-clicking the project node in Solution Explorer and selecting Add , then the desired object type. Files in the project can be organized into folders through the New Folder option under Add .

In Solution Explorer , right-click the project node and select Add , then New Item . The Add New Item dialog appears, select Show All Templates and then Table . Specify the table name as the file name and select Add to create the table in the SQL project.

The table is opened in the Visual Studio query editor with the template table definition, where you can add columns, indexes, and other table properties. Save the file when you're done making the initial edits.

More database objects can be added through the Add New Item dialog, such as views, stored procedures, and functions. Access the dialog by right-clicking the project node in Solution Explorer and selecting Add , then the desired object type after Show All Templates . Files in the project can be organized into folders through the New Folder option under Add .

In the Database Projects view of VS Code or Azure Data Studio, right-click the project node and select Add Table . In the dialog that appears, specify the table name.

The table is opened in the text editor with the template table definition, where you can add columns, indexes, and other table properties. Save the file when you're done making the initial edits.

More database objects can be added through the context menu on the project node, such as views, stored procedures, and functions. Access the dialog by right-clicking the project node in Database Projects view of VS Code or Azure Data Studio, then the desired object type. Files in the project can be organized into folders through the New Folder option under Add .

Files can be added to the project by creating them in the project directory or nested folders. The file extension should be .sql and organization by object type or schema and object type is recommended.

The base template for a table can be used as a starting point for creating a new table object in the project:

Step 3: Build the project

The build process validates the relationships between objects and the syntax against the target platform specified in the project file. The artifact output from the build process is a .dacpac file, which can be used to deploy the project to a target database and contains the compiled model of the database schema.

In Solution Explorer , right-click the project node and select Build .

The output window automatically opens to display the build process. If there are errors or warnings, they're displayed in the output window. On a successful build, the build artifact ( .dacpac file) is created its location is included in the build output (default is bin\Debug\projectname.dacpac ).

In the Database Projects view of VS Code or Azure Data Studio, right-click the project node and select Build .

SQL database projects can be built from the command line using the dotnet build command.

The build output includes any errors or warnings and the specific files and line numbers where they occur. On a successful build, the build artifact ( .dacpac file) is created its location is included in the build output (default is bin/Debug/projectname.dacpac ).

Step 4: Deploy the project

The compiled model of a database schema in a .dacpac file can be deployed to a target database using the SqlPackage command-line tool or other deployment tools. The deployment process determines the necessary steps to update the target database to match the schema defined in the .dacpac , creating or altering objects as needed based on the objects already existing in the database. As a result, the deployment process is idempotent, meaning it can be run multiple times without causing issues and you can deploy the same .dacpac to multiple databases without needing to predetermine their status.

In Solution Explorer , right-click the project node and select Publish... .

The publish dialog opens, where you establish the target database connection . If you don't have an existing SQL instance for deployment, LocalDB ( (localdb)\MSSQLLocalDB ) is installed with Visual Studio and can be used for testing and development.

Specify a database name and select Publish to deploy the project to the target database or Generate Script to generate a script to review before executing.

In the Database Projects view of VS Code or Azure Data Studio, right-click the project node and select Publish .

If you don't have an available SQL instance for deployment, the SQL Database Projects extension can create a local SQL Server instance for you in a new container. With a container runtime such as Docker Desktop running, select Publish to a new SQL server local development container from the dropdown list.

If you have an existing SQL instance for deployment, select Publish to an existing SQL server and then Don't use profile if prompted for a publish profile.

If you haven't configured a connection to a target database, you're prompted to create a new connection. The new connection inputs ask for the server name, authentication method, and database name.

After the connection is configured, the deployment process will begin. You can choose to automatically execute the deployment (publish) or generate a script to review before executing (generate script).

The SqlPackage CLI is used to deploy a .dacpac file to a target database with the publish action .

For example, to deploy a .dacpac file to a target database based on a connection string:

DacFx GitHub repository

Additional resources

How to Create Your Own Database to Practice SQL

sql practice
online practice

Table of Contents

Why You Need Your Own Database for Practice

Step 1: install required software, step 2: create a sql database, step 3: create custom tables, step 4: import data from csvs, example 1: what is the distinct count of customers, example 2: what is the average number of invoices per customer, the long-term benefits of regular practice, practice sql on your own database.

Welcome to the step-by-step guide to creating your own SQL database from scratch, designed with beginners in mind. This guide not only helps you set up your database but also introduces you to essential SQL practice exercises to get you started on your learning journey. Follow along to gain SQL hands-on practice and foster a deeper understanding of SQL functionalities. Set a solid foundation for more advanced SQL training!

Everyone has a different style of learning, but one thing we all need is SQL hands-on practice. It is one thing to read about SELECT statements, JOINS , and GROUP BY clauses, but it is an entirely different experience to use them in queries and see the results.

This is a lot like when I took 3 years of Spanish in school. One thing I found both fascinating and frustrating was that I could understand the teacher but struggled to form sentences to respond. This is not unusual; it often happens because we practice learning new words but neglect using them.

Learning by doing is one of the most effective ways to acquire a new skill. So, what is the most effective way to practice “speaking” SQL?

That is our focus in this article. If you’re interested in more on the best ways to learn SQL, be sure to check out this article here .

LearnSQL.com offers several comprehensive courses that include interactive exercises for SQL practice. You go through hundreds of exercises, covering dozens of hours to help you master SQL. For example, the SQL Basics course includes 129 practice exercises and 10 hours of hands-on learning. In addition, the course Data Types in SQL includes 89 exercises and another 10 hours of instruction.

These courses are a great way to learn the “new words” of SQL, and the practice exercises help you use them in writing your queries. However, while structured learning is a great resource, you can take your learning to a whole new level by setting up your own SQL database for practice. In this article, I will walk you through the tools and steps you need to accomplish this task.

When you have your own database, you get the freedom to customize it however you want, from the table structure to the actual data it houses. You can create real-world scenarios tailored to your learning objectives. Your data stays with you, and you may explore and even make mistakes, without any judgment or fear of breaking something.

Plus, the ability to stimulate real-world scenarios allows you to practice SQL for job-specific conditions, making you better prepared for the professional world. Looking for a job in e-commerce ? Set up your database with an order table, a customer table, and a product table. Interested in a career in finance ? Great! Create a table for accounts receivable and another for accounts payable. The flexibility in creating your own database is unmatched by other ways to practice SQL.

Create Your Own Database to Practice SQL

If setting up a database sounds intimidating to you, don’t worry! It is very simple in practice. Different databases have different nuances. We won’t spend time here explaining them, but you can learn more about some of the most popular databases in 2023 in this article .

A Step-by-Step Guide to Setting up Your SQL Database

OK, let’s get started setting up your database for SQL training. The first thing you need to do is to download a database tool. One that I use that works on Windows, Linux, and Mac is DBeaver, which may be downloaded here . Best of all, it’s free!

Want some other tool? Check the article on the best SQL IDEs .

Once you’ve downloaded and installed the software, launch the application. You should see a screen that looks like this:

In the top toolbar, click on “Help” and then “Create Sample Database.” You then see a prompt asking if you would like to create a sample database. Click “Yes.”

You should now see a sample database listed under your connections under the Database Navigator panel.

This sample database comes with already created tables and views you can query immediately. Later in this article, there are a few examples of SQL queries for practice with your new database. For now, let’s look at how we may further customize this database.

Let’s go back to our example of practicing with e-commerce data. I’m going to rename my database to ecommerce_data by right-clicking on the database name and selecting “rename.”

To create a new table for orders , we execute a CREATE TABLE statement. In this statement, we define the table name of orders and then define the column names and the data types.

You can now run SELECT * FROM orders , but you will see an empty table. The next step is to insert rows into this table using an INSERT statement.

Now, you will see your new data when you run a SELECT statement.

Inserting individual rows of data is tedious depending on how much data you want to set up in your database. Another option is to import whole CSV files rather than writing INSERT statements. To do this, right-click on the table name under your sample database and select “Import Data.”

From here, select a CSV from your computer to upload. If you do not see your table listed under your sample database, right-click on the database name and click “Refresh.” You should then see the newly created tables.

There you have it! Your own database to practice SQL.

If you’re unsure about creating your own data, you can also find several resources online with public datasets for download. You can then import these files into your database. A good, clean data set often used is the superstore data from Tableau. This Excel sheet includes 3 separate tabs to create 3 tables of store purchase data. Another great resource for finding data sets is our article, “ Where Can I Find Free Online Data Sets to Practice SQL? .”

Practice Queries

Let’s walk through a few examples to get you started in using your new database. This is only the beginning; you’ll be on to exploring your data in no time. For an added challenge, try answering the question on your own before reading the SQL query.

The questions below use the customer and invoice tables in the DBeaver sample database.

For this example, we only need to query the customer table. Use COUNT() and DISTINCT to answer this question.

This query counts the number of unique CustomerIds in the customer table. We now see there are 59 customers in our database.

Next, let’s see how many invoices each customer has on average.

To answer this question, use the invoice table and create a list of all customer IDs with how many invoices each has.

At a glance, it looks like most customers have 6 or 7 invoices. Let’s calculate the average across all customer IDs by placing the above in a subquery and calculating the average:

The query returns 6.98 invoices, on average, for each customer.

These examples are just to get you started. Get familiar with what data lives in each table and how the tables relate to one another. Then, begin asking yourself questions and creating hypotheses. Finally, begin exploring the data by writing queries to answer those questions and test your hypotheses.

A great resource to reference is our SQL for Data Analysis Cheat Sheet . This is a curated list of SQL commands to help you get started. Once you get started, it’ll be hard to stop!

There are numerous advantages to practicing SQL regularly. You improve your problem-solving skills, make yourself more marketable for higher-paying jobs, and get better equipped to handle complex databases. Trust me, practice does make perfect, and the more real-world problems you solve, the better you become.

Regular SQL practice significantly enhances your analytical thinking . As you delve deeper into your SQL database setup and start working with more complex queries, you learn to analyze data from various angles. This not only helps in extracting precise information but also in understanding the intricate relationships between different data sets.

Moreover, the ability to dissect and interpret complex data is a highly valued skill in many professions today. This puts you a step ahead in your career journey.

As you practice SQL through hands-on exercises, you find yourself becoming more autonomous and confident in your abilities. You learn to trust your judgment and develop a knack for finding solutions to complex problems. This is an indispensable trait in the ever-evolving tech landscape.

Furthermore, by setting up your own SQL database for practice, you foster a deep understanding of the database structure and nuances. This is a substantial asset in both learning and professional environments.

Dedicating time to SQL practice exercises is an investment in your future. The world is becoming increasingly data-driven . Proficiency in SQL is often a prerequisite in many fields, not just in IT but also in marketing , finance, healthcare , and more. By committing to regular practice, you are not just learning a skill but are paving the path to opportunities in a wide array of industries, opening doors to potential roles that are both fulfilling and financially rewarding.

So, there you have it! Your own SQL database setup for practice is not only possible but incredibly beneficial. You get customization, privacy, and the ability to simulate real-world scenarios.

If you’re looking to upskill further, remember to check out the extensive courses offered by LearnSQL.com . The All Forever package provides lifetime access to all the courses and tracks plus any new courses released in the future! This option, by far, gives you the most bang for your buck with a huge discount.

Start your SQL journey now by setting up your personal database. Dive into it, practice daily, and you’ll be amazed at how far you go. Happy querying!

Navigation Menu