Data Manipulation in SQL

Overview

Master the complex SQL queries necessary to answer a wide variety of data science questions and prepare robust data sets for analysis in PostgreSQL.

So you've learned how to aggregate and join data from tables in your database—now what? How do you manipulate, transform, and make the most sense of your data? This intermediate-level course will teach you several key functions necessary to wrangle, filter, and categorize information in a relational database, expand your SQL toolkit, and answer complex questions. You will learn the robust use of CASE statements, subqueries, and window functions—all while discovering some interesting facts about soccer using the European Soccer Database.

Syllabus

We'll take the CASE

In this chapter, you will learn how to use the CASE WHEN statement to create categorical variables, aggregate data into a single column with multiple filtering conditions, and calculate counts and percentages.

Short and Simple Subqueries

In this chapter, you will learn about subqueries in the SELECT, FROM, and WHERE clauses. You will gain an understanding of when subqueries are necessary to construct your dataset and where to best include them in your queries.

Correlated Queries, Nested Queries, and Common Table Expressions

In this chapter, you will learn how to use nested and correlated subqueries to extract more complex data from a relational database. You will also learn about common table expressions and how to best construct queries using multiple common table expressions.

Window Functions

You will learn about window functions and how to pass aggregate functions along a dataset. You will also learn how to calculate running totals and partitioned averages.

Taught by

Mona Khalil

Reviews

2.0 rating, based on 1 Class Central review

4.5 rating at DataCamp based on 189 ratings

Start your review of Data Manipulation in SQL

Anonymous

While some of the lessons are super well-explained, I've found two major sources of frustration in Intermediate SQL: 1. In the practices, you don't get to write your own code. It is just "fill in the blank" style. I find this makes it difficlut to…

While some of the lessons are super well-explained, I've found two major sources of frustration in Intermediate SQL:

1. In the practices, you don't get to write your own code. It is just "fill in the blank" style. I find this makes it difficlut to really grasp the concepts and actually learn how to write queries on my own. I've tried deleting the fill-in-the-blank code to write from scratch, but the prompts don't include all of the information you need to write the code. For example, the prompt won't mention that it wants you to rename a column, only the fill-in-the-blank code includes that aspect, so you have to utilize it, otherwise your asnwer is graded as incorrect. You don't really learn how to write queries, but how to just type in some words.

2. Some of the videos have typos or unclear explanations from time to time, but there is no question-asking mechanism (like you might find in an edX or coursera class). It would be really great if you could ask your fellow learners questions, but there is no way to do this. Datacamp does have a way to submit feedback, but this is entirely one-way, and doesn't allow you to get any answers or connect to a learning community.

Taught by

Combining and Filtering Data with PostgreSQL

SQL Window Functions for Analytics

Intuitive SQL for Data Analytics - Tutorial

Introduction to SQL Window Functions

Managing Relational Databases

SQL CASE Statements

10 Best Free SQL Courses for 2024

From Data to Insights: 10 Best Data Analysis Courses for 2024

Never Stop Learning.