SQL for Business Users

SQL for Business Users

by O'Reilly Media, Inc.
Released December 2016
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781491979280

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Video description

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

Product information

  • Title: SQL for Business Users
  • Author(s): O'Reilly Media, Inc.
  • Release date: December 2016
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781491979280