Course Description Get an Introduction to SQL in Two Hours Much of the world's raw data庸rom electronic medical records to customer transaction histories様ives in organized collections of tables called relational databases. Being able to wrangle and extract data from these databases using SQL is an essential skill within the data industry and in increasing demand. In just two hours, you'll get to know the theory and the practice through bite-sized videos and interactive exercises where you can put your new-found skills to the test. Learn how Relational Databases are Organized SQL is an essential language for building and maintaining relational databases, which opens the door to a range of careers in the data industry and beyond. You値l start this course by covering data organization, tables, and best practices for database construction. Write Your First SQL Queries The second half of this course looks at creating SQL queries for selecting data that you need from your database. You値l have the chance to practice your querying skills before moving on to customizing and saving your results. Understand the Difference Between PostgreSQL and SQL Server PostgreSQL and SQL Server are two of the most popular SQL flavors. You値l finish off this course by looking at the differences, benefits, and applications of each. By the end of the course, you値l have some hands-on experience in learning SQL and the grounding to start applying it to projects or continue your learning in a more specialized direction. 1 Relational Databases FREE Before writing any SQL queries, it痴 important to understand the underlying data. In this chapter, we値l discover the role of SQL in creating and querying relational databases. Using a database for a local library, we will explore database and table organization, data types and storage, and best practices for database construction. View chapter details 2 Querying Learn your first SQL keywords for selecting relevant data from database tables! After practicing querying skills in a database of books, you値l customize query results using aliasing and save them as views so they can be shared. Finally, you値l explore the differences between SQL flavors and databases such as PostgreSQL and SQL Server. View chapter details IN THE FOLLOWING TRACKS Data Analyst in SQL Data Engineer Data Scientist Professional with Python Data Scientist Professional with R SQL FundamentalsData Coach at iO-Sphere Izzy is a Data Coach at iO-Sphere. She discovered a love for data during her seven years as an accounting professor at the University of Washington. She holds a masters degree in Taxation and is a Certified Public Accountant. Her passion is making learning technical topics fun. Course Description SQL is the most popular language for turning raw data stored in a database into actionable insights. Using a database of films made around the world, this course covers: ? How to filter and compare data ? How to use aggregate functions to summarize data ? How to sort and group your data ? How to present your data cleanly using tools such as rounding and aliasing Accompanied at every step with hands-on practice queries, this course teaches you everything you need to know to analyze data using your own SQL code today! 1 Selecting Data FREE In this first chapter, you値l learn how to query a films database and select the data needed to answer questions about the movies and actors. You'll also understand how SQL code is executed and formatted. View chapter details 3 Aggregate Functions SQL allows you to zoom in and out to better understand an entire dataset, its subsets, and its individual records. You'll learn to summarize data using aggregate functions and perform basic arithmetic calculations inside queries to gain insights into what makes a successful film. View chapter details 2 Filtering Records Learn about how you can filter numerical and textual data with SQL. Filtering is an important use for this language. You値l learn how to use new keywords and operators to help you narrow down your query to get results that meet your desired criteria and gain a better understanding of NULL values and how to handle them. View chapter details 4 Sorting and Grouping This final chapter teaches you how to sort and group data. These skills will take your analyses to a new level by helping you uncover critical business insights and identify trends and performance. You'll get hands-on experience to determine which films performed the best and how movie durations and budgets changed over time. Course Description Joining data is an essential skill which enables us to draw information from separate tables together into a single, meaningful set of results. Learn to supercharge your queries using table joins and relational set theory in this course on joining data. In this course, you値l learn how to: ? Work with more than one table in SQL ? Use inner joins, outer joins and cross joins ? Leverage set theory, including unions, intersect, and except clauses ? Create nested queries Every step is accompanied by exercises and opportunities to apply the theory and grow your confidence in SQL. 1 Introducing Inner Joins FREE In this chapter, you値l be introduced to the concept of joining tables and will explore all the ways you can enrich your queries using joins傭eginning with inner joins. View chapter details 3 Set Theory for SQL Joins In this chapter, you will learn about using set theory operations in SQL, with an introduction to UNION, UNION ALL, INTERSECT, and EXCEPT clauses. You値l explore the predominant ways in which set theory operations differ from join operations. View chapter details 2 Outer Joins, Cross Joins and Self Joins After familiarizing yourself with inner joins, you will come to grips with different kinds of outer joins. Next, you will learn about cross joins. Finally, you will learn about situations in which you might join a table with itself. View chapter details 4 Subqueries In this closing chapter, you値l begin by investigating semi-joins and anti-joins. Next, you'll learn how to use nested queries. Last but not least, you値l wrap up the course with some challenges! Course Description So you've learned how to aggregate and join data from tables in your database溶ow 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預ll while discovering some interesting facts about soccer using the European Soccer Database. 1 We'll take the CASE FREE 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. View chapter details 3 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. View chapter details 2 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. View chapter details 4 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. Course Description Have you ever wondered how data professionals use SQL to solve real-world business problems, like generating rankings, calculating moving averages and running totals, deduplicating data, or performing time intelligence? If you already know how to select, filter, order, join and group data with SQL, this course is your next step. By the end, you will be writing queries like a pro! You will learn how to create queries for analytics and data engineering with window functions, the SQL secret weapon! Using flights data, you will discover how simple it is to use window functions, and how flexible and efficient they are. 1 Introduction to window functions FREE In this chapter, you'll learn what window functions are, and the two basic window function subclauses, ORDER BY and PARTITION BY. View chapter details 3 Aggregate window functions and frames In this chapter, you'll learn how to use aggregate functions you're familiar with, like `AVG()` and `SUM()`, as window functions, as well as how to define frames to change a window function's output. View chapter details 2 Fetching, ranking, and paging In this chapter, you'll learn three practical applications of window functions: fetching values from different parts of the table, ranking rows according to their values, and binning rows into different tables. View chapter details 4 Beyond window functions In this last chapter, you'll learn some techniques and functions that are useful when used together with window functions. Course Description This course will provide you an understanding of how to use built-in PostgreSQL functions in your SQL queries to manipulate different types of data including strings, character, numeric and date/time. We'll travel back to a time where Blockbuster video stores were on every corner and if you wanted to watch a movie, you actually had to leave your house to rent a DVD! You'll also get an introduction into the robust full-text search capabilities which provides a powerful tool for indexing and matching keywords in a PostgreSQL document. And finally, you'll learn how to extend these features by using PostgreSQL extensions. 1 Overview of Common Data Types FREE Learn about the properties and characteristics of common data types including strings, numerics and arrays and how to retrieve information about your database. View chapter details 3 Parsing and Manipulating Text Learn how to manipulate string and text data by transforming case, parsing and truncating text and extracting substrings from larger strings. View chapter details 2 Working with DATE/TIME Functions and Operators Explore how to manipulate and query date and time objects including how to use the current timestamp in your queries, extract subfields from existing date and time fields and what to expect when you perform date and time arithmetic. View chapter details 4 Full-text Search and PostgresSQL Extensions An introduction into some more advanced capabilities of PostgreSQL like full-text search and extensions. Course Description Statistics are all around us, from marketing to sales to healthcare. The ability to collect, analyze, and draw conclusions from data is not only extremely valuable, but it is also becoming commonplace to expect roles that are not traditionally analytical to understand the fundamental concepts of statistics. This course will equip you with the necessary skills to feel confident in working with analyzing data to draw insights. You'll be introduced to common methods used for summarizing and describing data, learn how probability can be applied to commercial scenarios, and discover how experiments are conducted to understand relationships and patterns. You'll work with real-world datasets including crime data in London, England, and sales data from an online retail company! 1 Summary Statistics FREE Summary statistics gives you the tools you need to describe your data. In this chapter, you'll explore summary statistics including mean, median, and standard deviation, and learn how to accurately interpret them. You'll also develop your critical thinking skills, allowing you to choose the best summary statistics for your data. View chapter details 3 More Distributions and the Central Limit Theorem It's time to explore more probability distributions. You'll learn about the binomial distribution for visualizing the probability of binary outcomes, and one of the most important distributions in statistics, the normal distribution. You'll see how distributions can be described by their shape, along with discovering the Poisson distribution and its role in calculating the probabilities of events occuring over time. You'll also gain an understanding of the central limit theorem! View chapter details 2 Probability and distributions Probability underpins a large part of statistics, where it is used to calculate the chance of events occurring. You'll work with real-world sales data and learn how data with different values can be interpreted as a probability distribution. You'll find out about discrete and continuous probability distributions, including the discovery of the normal distribution and how it occurs frequently in natural events! View chapter details 4 Correlation and Hypothesis Testing In the final chapter, you'll be introduced to hypothesis testing and how it can be used to accurately draw conclusions about a population. You'll discover correlation and how it can be used to quantify a linear relationship between two variables. You'll find out about experimental design techniques such as randomization and blinding. You'll also learn about concepts used to minimize the risk of drawing the wrong conclusion about the results of hypothesis tests! Course Description You have access to a database. Now what do you do? Building on your existing skills joining tables, using basic functions, grouping data, and using subqueries, the next step in your SQL journey is learning how to explore a database and the data in it. Using data from Stack Overflow, Fortune 500 companies, and 311 help requests from Evanston, IL, you'll get familiar with numeric, character, and date/time data types. You'll use functions to aggregate, summarize, and analyze data without leaving the database. Errors and inconsistencies in the data won't stop you! You'll learn common problems to look for and strategies to clean up messy data. By the end of this course, you'll be ready to start exploring your own PostgreSQL databases and analyzing the data in them. 1 What's in the database? FREE Start exploring a database by identifying the tables and the foreign keys that link them. Look for missing values, count the number of observations, and join tables to understand how they're related. Learn about coalescing and casting data along the way. View chapter details 3 Exploring categorical data and unstructured text Text, or character, data can get messy, but you'll learn how to deal with inconsistencies in case, spacing, and delimiters. Learn how to use a temporary table to recode messy categorical data to standardized values you can count and aggregate. Extract new variables from unstructured text as you explore help requests submitted to the city of Evanston, IL. View chapter details 2 Summarizing and aggregating numeric data You'll build on functions like min and max to summarize numeric data in new ways. Add average, variance, correlation, and percentile functions to your toolkit, and learn how to truncate and round numeric values too. Build complex queries and save your results by creating temporary tables. View chapter details 4 Working with dates and timestamps What time is it? In this chapter, you'll learn how to find out. You'll aggregate date/time data by hour, day, month, or year and practice both constructing time series and finding gaps in them. View chapter details IN THE FOLLOWING TRACKS Data Analyst in SQL SQL for Business Analysts DATASETS Stack Overflow Question Counts Fortune 500 Companies Evanston 311 Help Requests Course Database Creation Code Course Database Entity Relationship Diagram COLLABORATORS Collaborator's avatar Chester Ismay Collaborator's avatar Mona Khalil Collaborator's avatar Adrian Soto PREREQUISITES Data Manipulation in SQL Christina Maimone Headshot Christina Maimone Data Scientist, Northwestern University Christina Maimone leads Research Data Services at Northwestern University with the IT Research Computing Services group. She enables innovative research by providing data science, programming, and software development support for researchers. Through consultations, project collaborations, user groups, and workshops, the Research Data Services team ensures researchers have the resources, services, and skills they need to overcome challenges in their work. Christina regularly uses R, Python, and SQL but enjoys the challenge of using a wide range of programs and languages in her work. She has a PhD in political science and an MS in statistics from Stanford. Course Description In this course, you will learn how to use SQL to support decision making. It is based on a case study about an online movie rental company with a database about customer information, movie ratings, background information on actors and more. You will learn to apply SQL queries to study for example customer preferences, customer engagement, and sales development. This course also covers SQL extensions for online analytical processing (OLAP), which makes it easier to obtain key insights from multidimensional aggregated data. 1 Introduction to business intelligence for a online movie rental database FREE The first chapter is an introduction to the use case of an online movie rental company, called MovieNow and focuses on using simple SQL queries to extract and aggregated data from its database. View chapter details 3 Data Driven Decision Making with advanced SQL queries The concept of nested queries and correlated nested queries is introduced and the functions EXISTS and UNION are used to categorize customers, movies, actors, and more. View chapter details 2 Decision Making with simple SQL queries More complex queries with GROUP BY, LEFT JOIN and sub-queries are used to gain insight into customer preferences. View chapter details 4 Data Driven Decision Making with OLAP SQL queries The OLAP extensions in SQL are introduced and applied to aggregated data on multiple levels. These extensions are the CUBE, ROLLUP and GROUPING SETS operators.