O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Learning Path: SQL for Business Users

Learning Path: SQL for Business Users

by O'Reilly Media, Inc.
Publisher: O'Reilly Media, Inc.
Release Date: December 2016
ISBN: 9781491979297
View table of contents
Start watching

Video Description

If you’re looking for "just enough” SQL knowledge to wrangle data and do sophisticated analysis, this Learning Path is for you. You’ll get a solid understanding of basic database design principles and normalization. You’ll learn how to pipeline data to other analysis tools (R, Python, even Excel). You’ll be able to prototype a database that suits your business needs. and form accurate queries and database updates in several SQL environments--all without getting bogged down in the deeply technical aspects of SQL that you will never use.

Table of Contents

  1. Getting Started With Databases And SQL
    1. Relational Databases And Key Terms Explained 00:08:09
    2. About The Author 00:01:31
    3. What Are The SQL Commands 00:02:41
    4. Select Syntax And Examples 00:04:58
    5. Where Can I Use SQL? 00:05:35
    6. Installing Sample Databases And Working Files 00:04:13
  2. Using SQL To Select And Filter Data
    1. Using Select To View Data 00:04:08
    2. Sorting The Data 00:05:57
    3. Adding A Where Clause To Filter Data 00:05:45
    4. Filtering Date Columns 00:04:34
    5. Working With Null Or Empty Values 00:06:51
    6. Using Comments Within SQL 00:06:02
    7. Use Of Wildcards In Filtering 00:09:14
    8. Multiple Criteria Using And Or In 00:06:11
  3. Functions, Grouping And Summarizing
    1. Add A Calculating Column To A SQL Statement 00:03:14
    2. Text Manipulation Functions 00:09:04
    3. Differences In Date Functions Within SQL 00:07:10
    4. Calculating Age 00:06:25
    5. Concatenate Data Columns 00:05:54
  4. Joining Tables
    1. Which Tables Can I Join And How? 00:06:26
    2. Joining More Than Two Tables 00:04:23
    3. Using Different Join Types 00:05:51
    4. The Special Cross Or Cartesian Join 00:03:44
    5. Group And Summaries Within Joined Table SQL 00:07:04
  5. Data Modification
    1. Amend Data In Tables 00:08:29
    2. Adding Data To Tables With Insert 00:09:32
    3. Removing Data From Tables 00:08:40
  6. Creating And Altering Database Objects
    1. Create A New Table 00:07:10
    2. Changing A Tables Structure 00:04:59
    3. What Is A View And How To Create One 00:07:00
    4. Alter Or Delete A View 00:03:12
  7. SQL Summary
    1. The Main Variances In SQL Between Databases 00:05:38
    2. The Powerful Subquery 00:07:00
  8. Conclusions
    1. Wrap Up 00:05:14
  9. The Warm Up
    1. About This SQL Advanced Title 00:05:00
    2. About The Author 00:01:48
    3. Relational Databases And Key Terms Explained 00:07:46
    4. Recap Of SQL SELECT Command 00:07:39
    5. Overview Of The Other DML Statements 00:05:30
    6. Installing The Sample Data And Using The Working Files 00:03:05
  10. Useful Skills
    1. Converting Datatypes 00:07:59
    2. Creating A Temporary Table 00:06:03
    3. The DISTINCT And TOP Keywords In Action 00:05:21
    4. Indexing To Speed Up Your Life 00:08:00
    5. Declaring And Using Variables 00:06:50
  11. The Subquery
    1. What Is A Subquery? 00:06:01
    2. Subquery Vs Joins 00:04:59
    3. Using Correlated Subqueries 00:07:05
    4. Using The EXISTS Keyword 00:04:47
    5. Finding Duplicate Records 00:05:45
  12. The CASE Statement
    1. Getting Started With The CASE Statement 00:06:35
    2. Using The CASE Statement With Criteria On Multiple Columns 00:05:34
    3. Multiple Criteria Within The CASE Statement 00:06:11
    4. CASE In WHERE, ORDER BY, And GROUP BY Clauses 00:04:50
    5. CASE In UPDATE And INSERT Statements 00:06:33
    6. CASE Within Subqueries 00:06:05
  13. Numbering And Ranking
    1. Numbering Or Ranking Records In A SELECT Statement 00:06:21
    2. Filtering Results Based On Numbering Ranking 00:05:54
    3. Using Numbering Within Groups 00:04:45
    4. Filtering A Ranked Group To Determine Top N 00:05:18
  14. Functions
    1. Using Built In Functions 00:07:35
    2. Creating And Using Your Own Functions (UDF) 00:05:08
  15. Views
    1. Creating A View 00:05:20
    2. Example Views And Views In Views 00:05:06
    3. Using The UNION Clause 00:06:24
  16. Stored Procedures
    1. What Is A Stored Procedure? 00:08:04
    2. Creating A Stored Procedure 00:05:34
    3. Running A Stored Procedure 00:04:56
    4. Stored Procedures Within Stored Procedures 00:05:28
    5. The Global Variables @@Identity And @@Rowcount 00:08:06
    6. Looping With WHILE And IF 00:06:59
    7. Dynamically Build SQL Statements 00:07:44
    8. TRY And CATCH For Error Trapping 00:04:30
    9. Cursors In Use - Part 1 00:06:33
    10. Cursors In Use - Part 2 00:05:31
    11. Use Of Transactions 00:06:42
  17. Conclusions
    1. Wrap Up 00:06:37
  18. Getting Started
    1. About The Course 00:03:24
    2. About The Author 00:02:49
    3. What Is A Database? 00:02:23
    4. What Is A Data Model? 00:01:52
  19. Data Model Development Process
    1. Data Model Inputs And Outputs 00:02:06
    2. Data Model Notations 00:03:00
    3. UML Versus IE - Conceptual, Logical And Physical 00:01:19
  20. Basic Data Modeling
    1. Class And Attribute 00:06:12
    2. Operation 00:01:32
    3. Domain 00:03:55
    4. Association 00:05:00
    5. IE Entity Type And Relationship Type 00:03:15
    6. Association Name 00:04:46
    7. Association End 00:04:09
    8. Multiplicity - UML 00:03:40
    9. Multiplicity - IE 00:02:32
    10. Generalization - UML 00:03:32
    11. Generalization - IE 00:04:06
    12. Abstract Versus Concrete Superclass 00:02:11
    13. Practical Tips 00:01:59
    14. Self Assessment Test - Basic Modeling Data 00:04:44
  21. Advanced Data Modeling
    1. Identity 00:02:35
    2. Derived Data 00:02:34
    3. Current Versus Historical Data 00:01:07
    4. Association Class 00:05:21
    5. Ordered Association 00:05:26
    6. Qualified Association - UM 00:05:12
    7. Qualified Association - IE 00:02:36
    8. Large Taxonomies 00:03:05
    9. Package 00:02:46
    10. Abridged UML Metamodel 00:02:04
    11. Abridged IE Metamodel 00:01:14
    12. Modeling Pitfalls 00:03:27
    13. Practical Tips 00:01:47
    14. Self Assessment Test - Advanced Data Modeling 00:03:45
  22. Create A UML Data Model
    1. Problem Statement 00:01:52
    2. Finding Classes 00:04:32
    3. Finding Associations - Part 1 00:05:00
    4. Finding Associations - Part 2 00:05:53
    5. Finding Generalizations 00:01:37
    6. Iterating And Refining The Model - Part 1 00:02:39
    7. Iterating And Refining The Model - Part 2 00:04:42
    8. Adding Attributes 00:04:48
    9. Cleaning Up Layout 00:04:04
    10. Simplifying The Model 00:01:55
    11. Evolving A Model - Part 1 00:02:13
    12. Evolving A Model - Part 2 00:04:46
    13. Enterprise Architect Techniques - Part 1 00:03:48
    14. Enterprise Architect Techniques - Part 2 00:05:20
    15. Enterprise Architect Techniques - Part 3 00:04:25
  23. Translate A UML Data Model Into An IE Data Model
    1. Creating Subject Areas 00:02:39
    2. Creating Entity Types 00:02:44
    3. Creating Domains 00:06:13
    4. Adding Attributes - Part 1 00:06:14
    5. Adding Attributes - Part 2 00:03:25
    6. Creating Relationship Types - Part 1 00:05:04
    7. Creating Relationship Types - Part 2 00:03:34
    8. Creating Relationship Types - Part 3 00:05:07
    9. Subtyping 00:03:20
    10. Adding Alternate Keys 00:03:36
    11. Cleaning Up The Layout 00:01:38
    12. ERwin Techniques - Part 1 00:04:58
    13. ERwin Techniques - Part 2 00:04:00
  24. Model Quality
    1. Model Quality 00:01:02
    2. Normal Forms 00:04:02
    3. Constraints 00:03:27
    4. Hillard Graph Complexity 00:07:04
    5. Hoberman Data Model Scorecard 00:05:21
  25. Kinds Of Data Models
    1. Operational Data Models 00:03:30
    2. Enterprise Data Models 00:05:34
    3. Data Warehouses - Part 1 00:05:12
    4. Data Warehouses - Part 2 00:04:54
    5. Data Warehouses - Part 3 00:03:15
    6. Master Data Models 00:04:08
  26. Database Design
    1. Schema Adjustments 00:04:48
    2. Attribute Details - Part 1 00:04:27
    3. Attribute Details - Part 2 00:06:28
    4. Attribute Details - Part 3 00:07:44
    5. Primary And Alternate Keys 00:08:09
    6. Indexes 00:06:45
    7. Referential Integrity - Part 1 00:08:22
    8. Referential Integrity - Part 2 00:06:28
    9. Check Constraints - Part 1 00:06:45
    10. Check Constraints - Part 2 00:07:08
    11. Views 00:08:44
    12. Other Aspects Of Design 00:03:53
    13. Self Assessment Test - Database Design 00:03:15
  27. Create A SQL Server Database
    1. Creating A New Database 00:03:39
    2. Executing Schema 00:02:29
    3. Inspecting Metadata 00:08:33
    4. Loading Sample Data 00:04:03
    5. Querying Sample Data 00:06:34
  28. Create An MS-Access Database
    1. Generating An ERwin Schema 00:03:08
    2. Creating Tables 00:06:17
    3. Creating Indexes 00:03:22
    4. Creating Constraints And Default Values 00:02:52
    5. Defining Foreign Keys 00:03:44
    6. Creating Views 00:04:32
    7. Loading Sample Data 00:03:55
    8. Querying Sample Data 00:04:21
  29. Software Engineering
    1. Development Frameworks 00:03:25
    2. Agile Data Modelling 00:03:19
    3. Documenting A Model - Part 1 00:03:49
    4. Documenting A Model - Part 2 00:04:25
    5. Presenting A Model 00:02:45
  30. Data Modeling Patterns
    1. Overview 00:01:37
    2. Tree - Hardcoded 00:01:26
    3. Tree - Simple 00:01:37
    4. Tree - Structured 00:01:16
    5. Tree - Overlapping 00:04:06
    6. Tree - Changing Over Time 00:03:22
    7. Tree - Degenerate Node and Edge 00:01:29
  31. Database Reverse Engineering
    1. Motives 00:01:30
    2. Comparison With Forward Engineering 00:02:21
    3. Outputs 00:01:08
    4. Inputs 00:02:08
    5. Process 00:05:35
    6. Principles 00:01:41
    7. Example - Part 1 00:05:54
    8. Example - Part 2 00:08:08
  32. Conclusion
    1. Wrap-Up 00:06:19
  33. Introducing The Relational Database
    1. Course Introduction 00:00:43
    2. What Is A Relational Database 00:01:23
    3. The Database Hierarchy 00:02:32
    4. Table Relations 00:02:38
    5. The Structured Query Language 00:01:25
    6. Conclusion 00:01:08
  34. Installing And Configuring MySQL
    1. Introduction To MySQL 00:03:21
    2. MySQL Features 00:09:19
    3. Installing MySQL - Introduction 00:00:36
    4. Downloading MySQL 00:01:54
    5. Installing MySQL - Part 1 00:03:17
    6. Installing MySQL - Part 2 00:01:55
    7. Configuring MySQL 00:05:20
    8. Setting The System Path - Part 1 00:01:04
    9. Setting The System Path - Part 2 00:02:48
    10. Installing MySQL On The Mac 00:02:47
    11. The Sakila Database 00:01:38
    12. Installing The Sakila Database 00:02:33
  35. Connecting To MySQL And Creating Your First Database
    1. Introduction To MySQL Connections 00:00:48
    2. Role Of The Root User 00:01:42
    3. Logging In For The First Time 00:04:40
    4. Change The Root Password 00:01:26
    5. Creating Your First Database 00:06:43
    6. Creating Your First User Account 00:04:18
    7. Creating Your First Table 00:03:48
  36. Introducing MySQL's Data Types
    1. Intro To Data Types 00:02:53
    2. String Data Types - Part 1 00:04:31
    3. String Data Types - Part 2 00:02:07
    4. Data Attributes 00:02:15
    5. Customers Table Revised 00:03:37
    6. Numerical Data Types - Part 1 00:03:30
    7. Numerical Data Types - Part 2 00:03:30
    8. Numerical Data Attributes 00:02:38
    9. Date And Time Types 00:08:46
    10. Multiple Timestamp Columns In Table 00:04:20
  37. Primary And Foreign Keys
    1. Primary Keys - Part 1 00:02:42
    2. Primary Keys - Part 2 00:06:27
    3. Creating A Primary Key 00:02:42
    4. Foreign Key Introduction 00:08:45
    5. Create Foreign Key 00:04:24
  38. Introducing phpMyAdmin, MySQL Workbench, SQLyog
    1. Graphical MySQL Clients 00:04:54
    2. phpMyAdmin Intro 00:09:59
    3. MySQL Workbench Intro 00:02:01
    4. MySQL Workbench SQL Development Intro 00:08:39
    5. MySQL Workbench Data Modelling Intro 00:09:03
    6. MySQL Workbench Server Administration Intro 00:02:35
    7. SQLyog Introduction 00:01:26
    8. SQLyog Creating A Database 00:05:00
    9. SQLyog Navigating And Modifying Data 00:03:33
    10. SQLyog Scheduling Backups 00:05:03
  39. Creating, Retrieving, Updating And Deleting Data
    1. Performing CRUD Operations 00:01:58
    2. Creating Data 00:06:29
    3. Retrieving Data 00:05:05
    4. Ordering Results 00:02:28
    5. Filtering Results 00:04:52
    6. Grouping Results 00:07:07
    7. Removing Duplicate Rows 00:01:32
    8. The LIKE Clause 00:03:54
    9. The LIMIT Clause 00:03:35
    10. Updating Data 00:05:31
    11. Deleting Data 00:03:04
  40. Using String, Numeric, Temporal And Stored Functions
    1. Introduction To Functions 00:00:57
    2. String Functions 00:09:55
    3. Numeric Functions 00:05:21
    4. Temporal Functions - Introduction 00:02:26
    5. Temporal Functions - Example 00:11:13
    6. Stored Functions - Part 1 00:01:18
    7. Stored Functions - Part 2 00:06:33
    8. Stored Functions - Part 3 00:06:18
  41. Introducing Subqueries
    1. Subqueries Intro 00:02:40
    2. Subqueries 00:10:18
  42. Introducing SQL Joins
    1. SQL Joins 00:01:43
    2. The Inner Join 00:11:52
    3. The Left Join 00:07:37
  43. Optimizing MySQL
    1. Optimizing MySQL 00:00:49
    2. Column Indexing 00:01:39
    3. Column Indexing Explained 00:08:14
    4. Optimizing The MySQL Configuration 00:07:17
    5. Query Caching 00:01:17
    6. Query Caching Explained 00:03:49
    7. Optimizing MySQL Conclusions 00:00:35
  44. Importing, Exporting And Backing Up MySQL Databases
    1. Introduction To MySQL Data Tasks 00:00:54
    2. Exporting Data With MySQL Workbench 00:05:38
    3. Importing Data With phpMyAdmin 00:02:40
    4. Automatically Backing Up Your MySQL Database 00:01:22
    5. Automating Your Backups With mysqldump And cron 00:09:05
  45. Managing MySQL User Privileges
    1. Managing MySQL Privileges 00:00:59
    2. MySQL Privilege Management Walkthrough 00:08:58
  46. Using PHP And MySQL
    1. Introduction to PHP and MySQL 00:05:33
    2. Querying Your MySQL Database With PHP 00:12:50
  47. About The Author
    1. About The Author 00:01:48
  48. Install And Setup MSSQL
    1. Is This Course For Me 00:03:23
    2. About The Author 00:00:55
    3. MSSQL Versions And Editions 00:02:40
    4. Installing MSSQL - Part 1 00:02:42
    5. Installing MSSQL - Part 2 00:04:51
    6. The Adventureworks Sample Database 00:02:05
    7. SQL Server Management Studio (SSMS) 00:03:43
    8. Restoring The Adventureworks DB 00:05:26
  49. Exploring Adventureworks With SSMS
    1. Exploring A Foreign Database 00:04:23
    2. Browsing The Database With Object Explorer 00:04:15
    3. SSMS Query Editor 00:05:55
    4. Selecting Data About Pay Rates 00:03:55
    5. Limiting Number Of Rows Returned 00:01:21
    6. Is My Query Valid 00:00:59
    7. Query Result Formats 00:02:08
    8. Documentation In Extended Properties 00:04:24
    9. Creating A Project To Organize Scripts 00:04:13
    10. The Query Designer 00:03:36
  50. Answering Questions By Querying A Database
    1. Answering The Question Of Pay Inequality 00:01:49
    2. Inspecting Dependencies 00:02:26
    3. Inspecting Foreign Keys 00:02:50
    4. Executing Multiple Queries In Query Editor 00:03:25
    5. Counting Results From A Query 00:01:56
    6. Select Distinct 00:02:04
    7. Group By And Having 00:03:35
    8. Joining Tables 00:03:50
    9. Aliasing Tables In A Query 00:02:54
    10. Validating Joins And Join Types 00:05:29
    11. Computing The Average Pay Rate 00:02:58
    12. Order By 00:03:14
    13. Where 00:01:18
    14. Variables 00:01:47
    15. Querying Current Rate Change Date 00:05:11
    16. Join To A Sub Query 00:07:16
    17. Inequality In Current Wages 00:02:27
    18. Recap 00:01:55
  51. Advanced Querying To Answer Subtleties
    1. Organizational Level And Pay Equity 00:04:57
    2. Common Table Expressions 00:02:27
    3. REFactoring For Readability 00:03:28
    4. Case When And Calculated Columns 00:06:29
    5. Over Rank Functions 00:06:07
  52. Reporting From A Database
    1. Getting Information To Those That Need It 00:01:16
    2. Connecting Excel To MSSQL 00:04:43
    3. Creating Views 00:03:58
    4. Extracting CSVS With SQL CMD 00:06:05
    5. SSRS Report Builder 00:04:17
    6. SSRS Report Web Access 00:03:14
  53. Creating Databases
    1. National Stock Number Extract 00:00:36
    2. Create Database 00:03:52
    3. Drop Database 00:01:02
    4. SSMS Table Designer 00:05:18
    5. Create Table 00:04:47
    6. Drop Table 00:00:55
    7. Data Types 00:05:37
    8. Create Table National Stock Numbers 00:03:56
    9. Stored Procedures - SP_Rename 00:01:37
    10. Alter Table 00:02:11
    11. Importing Data - National Stock Number Extract 00:06:15
    12. Troubleshooting Importing Data 00:05:50
    13. Import Alternatives 00:02:09
    14. Backing Up The Database 00:02:16
    15. Backup With Select Into 00:00:51
  54. Developing Applications With MSSQL
    1. Visual Studio Community Edition 00:01:22
    2. New Console Application 00:01:31
    3. Connecting To MSSQL 00:03:45
    4. Querying Data Old School 00:05:29
    5. Create Table In Code 00:04:04
    6. Create A Foreign Key 00:04:58
    7. Inserting Data Old School 00:02:44
    8. Installing Entity Famework 00:02:36
    9. Querying With Entity Framework 00:05:53
    10. SQL Server Profiler - What Is EF Doing 00:02:20
    11. EF Querying Data 00:01:56
    12. EF Inserting Data 00:02:57
    13. EF Updating Data 00:02:36
    14. EF Deleting Data 00:02:04
    15. Other Programming Languages 00:02:16
    16. Course Wrap Up 00:00:40
  55. Introduction
    1. Welcome To The Course 00:02:36
    2. About The Author 00:02:07
    3. Course Overview 00:05:46
    4. Current Context 00:08:14
    5. Introduction To SQL 00:07:20
    6. Introduction To R 00:08:30
    7. Software Installation 00:06:42
    8. Rstudio Overview 00:05:39
    9. R Packages 00:07:42
    10. The Relationship Between R And SQL 00:10:43
    11. Demo Application And Database Schema Overview 00:04:16
  56. SQL With Single Table Results Sets
    1. Relational Theory Review 00:08:35
    2. Results Sets 00:07:17
    3. Processing Results Sets With R 00:08:33
    4. Filtering And Ordering With SQL 00:06:59
    5. Grouping And Summarizing SQL 00:06:03
    6. Modify Results Using SQL Functions 00:08:30
  57. SQL With Multiple Tables
    1. Common Database Joins 00:10:15
    2. Less-Common Joins 00:04:52
    3. Subqueries 00:08:13
    4. Set Operations 00:04:51
    5. DBA Considerations 00:09:01
    6. Table-Like Objects 00:06:25
    7. Indexes 00:06:08
  58. R Packages And SQL-Like Results Set Processing
    1. SQL Results Set And Tidy Data 00:06:53
    2. Processing Results Sets With R Vs SQL 00:08:55
    3. Filtering And Ordering With Dplyr Vs. SQL 00:08:20
    4. Grouping And Summarizing With Dplyr Vs. SQL 00:06:03
    5. Modify Results Using Dplyr And R Functions 00:07:10
    6. Joins Using Dplyr 00:06:22
    7. Set Operations Using Dplyr 00:04:01
    8. Reshape Package 00:05:49
    9. RTidy Package 00:04:31
  59. Data Artifacts Using SQL And R
    1. Plotting Database Results With R 00:09:14
    2. Plots Using Base R Plots 00:08:22
    3. Plots Using Lattice 00:06:53
    4. Plots Using ggplot2 00:09:36
    5. Plotting Time Series Data 00:08:24
    6. Creating Maps With R 00:07:33
    7. Creating Reports With R 00:09:00
    8. Web Applications With R 00:06:54
  60. Data Sources And Connections
    1. Sample Data Sets 00:06:31
    2. Database File Exports/Imports 00:06:47
    3. Local Relational Databases (RSQLite) 00:07:00
    4. Non-Relational Data Sources 00:08:06
    5. Remote Connections 00:04:33
    6. Troubleshooting Remote Connections 00:04:39
    7. JDBC Client Software 00:05:32
  61. Additional Topics
    1. Derived Tables 00:07:23
    2. Vendor Specific SQL 00:08:51
    3. Non-SQL Inspired/SQL-Like Languages 00:05:56
    4. R Inside The Database 00:04:52
    5. Schema Design Considerations 00:08:06
  62. Conclusion
    1. Wrap Up And Thank You 00:02:10
  63. Introduction
    1. What Is Apache Phoenix And Who Uses It 00:02:56
    2. What You Should Expect From This Course 00:02:22
    3. About The Author 00:00:56
  64. Introduction To NoSQL
    1. What Is NoSQL 00:04:31
    2. What Is SQL 00:05:00
    3. Performance Characteristics Of SQL 00:05:53
    4. Performance Characteristics Of NoSQL 00:12:02
    5. Optimizing NoSQL 00:09:45
  65. Introduction To Phoenix
    1. Hbase Fundamentals 00:08:56
    2. Phoenix SQL Engine For Hbase 00:05:39
    3. Obtaining And Configuring Apache Phoenix 00:03:01
  66. Using Phoenix
    1. Creating Tables 00:07:14
    2. Schema Operations 00:04:14
    3. Indexes And Secondary Indexes 00:06:50
    4. Executing Queries 00:05:45
  67. Advanced Phoenix
    1. Statistics 00:03:08
    2. Salted Tables 00:06:40
    3. Views 00:05:30
  68. Conclusion
    1. Wrap Up 00:02:03