In this chapter, you'll create your very first database with a set of simple SQL commands. Next, you'll migrate data from existing flat tables into that database. You'll also learn how meta-information about a database can be queried.

Introduction to relational databases

Attributes of relational databases

Query information_schema with SELECT

Tables: At the core of every database

CREATE your first few TABLEs

ADD a COLUMN with ALTER TABLE

Update your database as the structure changes

RENAME and DROP COLUMNs in affiliations

Migrate data with INSERT INTO SELECT DISTINCT

Delete tables with DROP TABLE

Your first database

After building a simple database, it's now time to make use of the features. You'll specify data types in columns, enforce column uniqueness, and disallow NULL values in this chapter.

Better data quality with constraints

Types of database constraints

Conforming with data types

Type CASTs

Working with data types

Change types with ALTER COLUMN

Convert types USING a function

The not-null and unique constraints

Disallow NULL values with SET NOT NULL

What happens if you try to enter NULLs?

Make your columns UNIQUE with ADD CONSTRAINT

Enforce data consistency with attribute constraints

Now let’s get into the best practices of database engineering. It's time to add primary and foreign keys to the tables. These are two of the most important concepts in databases, and are the building blocks you’ll use to establish relationships between tables.

Keys and superkeys

Get to know SELECT COUNT DISTINCT

Identify keys with SELECT COUNT DISTINCT

Primary keys

Identify the primary key

ADD key CONSTRAINTs to the tables

Surrogate keys

Add a SERIAL surrogate key

CONCATenate columns to a surrogate key

Test your knowledge before advancing

Uniquely identify records with key constraints

In the final chapter, you'll leverage foreign keys to connect tables and establish relationships that will greatly benefit your data quality. And you'll run ad hoc analyses on your new database.

Model 1:N relationships with foreign keys

REFERENCE a table with a FOREIGN KEY

Explore foreign key constraints

JOIN tables linked by a foreign key

Model more complex relationships

Add foreign keys to the "affiliations" table

Populate the "professor_id" column

Drop "firstname" and "lastname"

Referential integrity

Referential integrity violations

Change the referential integrity behavior of a key

Roundup

Count affiliations per university

Join all the tables together

Glue together tables with foreign keys

Course Glossary

You’ve already used SQL to query data from databases. But did you know that there's a lot more you can do with databases? You can model different phenomena in your data, as well as the relationships between them. This gives your data structure and consistency, which results in better data quality. In this course, you'll experience this firsthand by working with a real-life dataset that was used to investigate questionable university affiliations. Column by column, table by table, you'll get to unlock and admire the full potential of databases. You'll learn how to create tables and specify their relationships, as well as how to enforce data integrity. You'll also discover other unique features of database systems, such as constraints.

The videos contain live transcripts you can reveal by clicking "Show transcript" at the bottom left of the videos.
The course glossary can be found on the right in the resources section.
To obtain CPE credits you need to complete the course and reach a score of 70% on the qualified assessment. You can navigate to the assessment by clicking on the CPE credits callout on the right.

<h2>Explore the Role of SQL in Relational Database Management</h2> 
There are a lot of reasons why SQL is the go-to query language for relational database management. The main one is that SQL is a powerful language that can handle large amounts of data in complex ways and solve tricky analytical questions. In this course, you will gain an introduction to relational databases in SQL.
<br><br> 
Learn how to create tables and specify their relationships, as well as how to enforce data integrity. Additionally, discover other unique features of database systems, such as constraints.
<br><br> 
<h2>Create Your First Database</h2> 
You begin the course by creating your first database with simple SQL commands. Next, you’ll learn how to update your database as the structure changes by migrating data and deleting tables.
<br><br> 
In the final chapter, you will glue tables in foreign keys together and establish relationships that greatly benefit your data quality. Finally, you will run ad hoc analyses on your new database.
<br><br> 
<h2>Understand the Basics of Relational Databases</h2> 
By the end of the course, you will gain a basic yet essential understanding of SQL relational databases. They are widely used in various data science fields (from healthcare to finance) and have consequently become one of the crucial languages for data scientists. If you're interested in deepening your knowledge further, you may be interested in our SQL for Database Administrators, SQL Server Developer, and SQL Server for Database Administrators Tracks.

Intermediate SQL

Gain an Introduction to Relational Databases in SQL. You will acquire essential skills from creating your first database to running ad hoc analyses on it.  

Introduction to Relational Databases in SQL

Learn how to create one of the most efficient ways of storing data - relational databases!

Yardımcı Veri Mühendisi SQL içinde

Veritabanı Yöneticileri için SQL

SQL Server Geliştiricisi

Veritabanı Yöneticileri için SQL Server

Data Journalist

The current database model

Only store DISTINCT data in the new tables

INSERT DISTINCT records INTO the new tables

The INSERT INTO statement

RENAME a COLUMN in affiliations

DROP a COLUMN in affiliations

A professor is uniquely identified by firstname, lastname only

Let's get to work!

https://s3.amazonaws.com/assets.datacamp.com/production/course_13690/subtitles/course_13690_5c9e25169d053bad1730b81f958946e8.vtt

Your first database - Update your database as the structure changes

Update your database as the structure changes

Create Your Free Account