Data Fundamentals: From Spreadsheets to SQL | Nesqual TechAll coursesData Fundamentals: From Spreadsheets to SQL
Read, clean, summarise and chart real data, then answer questions with your first SQL queries, all hands-on.
- Access
- Free
- Level
- Beginner
- Duration
- 42 h
- Sections
- 89
Start free Course outline
Meet your data
- Data, information and decisionsLesson25 min
- Tables: rows, columns and one thing per rowLesson30 min
- Data types: text, numbers, dates and missing valuesLesson30 min
- Identifiers, units and granularityLesson25 min
- First steps in the terminalLesson35 min
- The question-first habitLesson30 min
- Chapter quiz: meet your dataQuiz15 min
CSV files and spreadsheets
How a spreadsheet works
Lesson25 min
The CSV format: commas, headers and quotesLesson30 minEncodings and line endingsLesson30 minSpreadsheet import and export trapsLesson30 minInspecting a CSV in the terminalLesson30 minYour first Python script: reading CSV properlyLesson35 minQuiz: CSV files and spreadsheetsQuiz15 minLab: inspect and read the café's CSV filesLab40 minCleaning messy data
- Why real data is messyLesson25 min
- Finding problems with countsLesson30 min
- Missing and impossible valuesLesson30 min
- Finding duplicatesLesson25 min
- Writing the cleaning scriptLesson35 min
- Checking the resultLesson30 min
- Chapter quiz: cleaning messy dataQuiz15 min
- Lab: clean the café's till exportLab40 min
Sorting and filtering
- Why sorting and filtering come firstLesson20 min
- Sorting in a spreadsheet and in the terminalLesson30 min
- Sorting in Python with sortedLesson30 min
- Filtering rows with conditionsLesson30 min
- Combining conditions: AND, OR and NOTLesson30 min
- Top-N lists and the spreadsheet SORT and FILTER functionsLesson25 min
- Quiz: sorting and filteringQuiz15 min
- Lab: answer the owner's sorting and filtering questionsLab40 min
Counting, totals and averages
- Counting: rows, distinct values and how many of eachLesson30 min
- Totals and derived columns: what did the week earn?Lesson30 min
- Averages and spread: mean, median, mode and rangeLesson35 min
- Grouped summaries: totals per day, category and paymentLesson35 min
- Percentages, rates and honest roundingLesson30 min
- Chapter quiz: counting, totals and averagesQuiz15 min
- Lab: summarise the café's weekLab40 min
Charts and honest visuals
- Why draw a picture of your dataLesson25 min
- Choosing the right chartLesson30 min
- Drawing bar charts in the terminalLesson35 min
- Titles, labels, units and the order of barsLesson30 min
- Spotting misleading chartsLesson30 min
- One chart, one messageLesson30 min
- Charts and honest visuals quizQuiz15 min
Your first SQL queries
- From spreadsheet to databaseLesson25 min
- Loading the café data and opening the SQL shellLesson30 min
- SELECT and FROM: choosing columnsLesson30 min
- WHERE: picking the rows you wantLesson35 min
- ORDER BY, LIMIT and DISTINCTLesson30 min
- Reading errors and translating your filters into SQLLesson30 min
- Quiz: your first SQL queriesQuiz15 min
- Lab: answer the café's questions with SQLLab40 min
Summarising with SQL
- Aggregate functions: many rows in, one number outLesson30 min
- GROUP BY: a summary for every groupLesson30 min
- HAVING versus WHERE, and the order clauses run inLesson30 min
- NULL: when a value is missingLesson30 min
- Rounding, formatting and dates as textLesson30 min
- Recreating chapter 5 in SQL and checking the numbers agreeLesson30 min
- Chapter quiz: summarising with SQLQuiz15 min
Joining tables
- Why data lives in more than one tableLesson25 min
- INNER JOIN: matching rows across tablesLesson30 min
- LEFT JOIN: finding what is missingLesson30 min
- Joins with GROUP BYLesson30 min
- Join mistakes and how to catch themLesson30 min
- The same idea in spreadsheets with VLOOKUP and XLOOKUPLesson25 min
- Quiz: joining tablesQuiz15 min
- Lab: answer Rosa's questions with joinsLab40 min
Asking good questions of data
- From a vague question to a metricLesson30 min
- Comparing fairlyLesson30 min
- Samples, bias and small numbersLesson30 min
- Correlation, causation and Simpson's paradoxLesson35 min
- Checking your own workLesson30 min
- Communicating a findingLesson30 min
- Chapter quiz: asking good questions of dataQuiz15 min
Data ethics and privacy
- Personal data and the loyalty listLesson25 min
- The data protection principlesLesson30 min
- Anonymisation and pseudonymisationLesson35 min
- Bias and fairnessLesson30 min
- Handling personal data safelyLesson30 min
- An ethics checklist before any analysisLesson30 min
- Data ethics and privacy quizQuiz15 min
Capstone and where next
- The analysis workflow, end to endLesson25 min
- Capstone briefing: Juniper Café's second weekLesson25 min
- Walking through the approachLesson35 min
- Presenting your resultsLesson30 min
- Where nextLesson20 min
- Capstone and where next quizQuiz15 min
- Capstone lab: Juniper Café's second weekLab40 min