Share Description
Perform data analysis on relational and non-relational database systems to support strategic decision-making. Learn how to determine, create, and execute SQL and NoSQL queries for manipulating and dissecting large datasets. Begin by leveraging the power of SQL commands, functions, and data cleaning methodologies to join, aggregate, and clean tables, as well as perform a full performance tune analysis to provide strategic business recommendations. Finally, use relational database management techniques to normalise data schemas before constructing the supporting data structures for a social news aggregator.
Syllabus:
Course 1: Introduction to SQL
Basic SQL
- Write common SQL commands including SELECT, FROM, and WHERE
- Use logical operators like LIKE, AND, and OR
SQL JOINs
- Write JOINs in SQL to combine data from multiple sources to answer more complex business questions
- Understand different types of JOINs and when to use each type
SQL Aggregations
- Write common aggregations in SQL including COUNT, SUM, MIN, and MAX
- Write CASE and DATE functions, as well as work with NULLs
SQL Subqueries & Temporary Tables
- Write subqueries to run multiple queries together
- Learn the types of subquery placement and formatting
- Use temp tables to access a table with more than one query
SQL Data Cleaning
- Learn and apply the basics of data cleaning strategies in SQL to normalize or create a column from existing data
- Perform the appropriate data cleaning methodology based on goals for further analysis
SQL Window Functions
- Apply core window functions to tackle analysis tasks that require further targeting or segmentation
- Use other window functions including RANK, NTILE, LAG, LEAD new functions along with partitions to complete complex tasks
SQL Advanced JOINs AND Performance Tuning
- Learn how and when to use advanced joins (e.g., self joins) to write queries that run quickly across giant datasets
- Learn the high-level tradeoffs with queries, including performance and what you can do to optimize them
Deforestation Exploration
- Apply basic and advanced query techniques to compile strategic recommendations from a large dataset
Project: Deforestation Exploration
SQL is most commonly used to manipulate and analyse data in order to make better decisions. In this project, you will work as a data analyst for an organisation dedicated to reducing deforestation around the world and raising awareness about this critical environmental issue. First, you will clean any erroneous values in a table, then join that table to another lookup table to add a new categorical and quantitative variable, and finally, you will return a new view of all categories greater than a reference value. Then, you'll write and run SQL queries to perform calculations on variables from those disparate data sets in order to answer questions for stakeholders. Your research will help you better understand which countries and regions around the world appear to have shrinking forests, as well as which countries and regions have the most significant forest area. Finally, you will compile your responses and summarise your analysis into a report that can be shared with the leadership team.
Course 2: Management of Relational & NonRelational Databases
Normalizing Data
- Organize data in a format suitable for relational databases
- Get a grasp on database normal forms
Data Definition Language (DDL)
- Write common SQL commands with CREATE TABLE and ALTER TABLE
- Use different data types to model real-world situations
Data Manipulation Language (DML)
- Write common SQL commands with INSERT, UPDATE, and DELETE
- Use SQL functions to manipulate numbers, strings, and dates
Consistency with Constraints
- Implement business rules at the database level using SQL commands with CONSTRAINT, UNIQUE, PRIMARY KEY, and CHECK
- Formalize the relations between tables using SQL FOREIGN KEY and its variations
Performance with Indexes
- Fix some slow SQL queries by introducing database indexes with the SQL command CREATE INDEX
- Introspect SQL queries through the query planner with EXPLAIN and EXPLAIN ANALYZE
- Assess whether a use-case is a good candidate for indexing
Intro to NonRelational Databases
- Articulate why non-relational databases were created, and what are their tradeoffs compared to relational databases
- Add, modify, and query data in a MongoDB database
- Use the right MongoDB design patterns for various real-life situations
- Add, modify, and query data in a Redis database
- Use Redis as a standalone database to build the data part of a small application
Project: Uddidit, A Social News Aggregator
Many of today's most popular web applications include database structures that enable them to customise and aggregate data in seconds. You will create the supporting data structures for Uddidit, a social media news aggregator site, in this project. To begin, you will examine the provided data model for potential errors such as a lack of normalisation, consistency rules, and proper indexing. Then, using DDL, you will create a new, normalised database based on the provided denormalized one. Finally, DML queries will be written to migrate data from the denormalized schema to the normalised schema.
