SQL Queries for Mere Mortals

QL is the standard language for communicating with most database systems. Any time you import data into a spreadsheet or perform a merge into a word processing program, you’re most likely using SQL in some form or another. Learn SQL and gain a hands-on experience in SQL with the course SQL Queries for Mere Mortals 4e. This course is designed for a beginning database user and it is also for an expert user who is suddenly faced with solving complex problems or integrating multiple systems that support SQL. This course has well-descriptive interactive lessons containing knowledge checks, quizzes, flashcards, and glossary terms to get a detailed understanding of SQL Queries.

Lessons

26+ Lessons | 414+ Exercises | 140+ Quizzes | 121+ Flashcards | 121+ Glossary of terms

TestPrep

83+ Pre Assessment Questions | 2+ Full Length Tests | 83+ Post Assessment Questions | 165+ Practice Test Questions

Hand on lab

45+ LiveLab | 45+ Video tutorials | 55+ Minutes

Lessons 1: Introduction

• Are You a Mere Mortal?
• About This Course
• What This Course Is Not
• How to Use This Course
• Reading the Diagrams Used in This Course
• Sample Databases Used in This Course

Lessons 2: What Is Relational?

• Types of Databases
• A Brief History of the Relational Model
• Anatomy of a Relational Database
• What’s in It for You?
• Summary

Lessons 3: Ensuring Your Database Structure Is Sound

• Why Is this Lesson Here?
• Why Worry about Sound Structures?
• Fine-Tuning Columns
• Fine-Tuning Tables
• Establishing Solid Relationships
• Is That All?
• Summary

Lessons 4: A Concise History of SQL

• The Origins of SQL
• Early Vendor Implementations
• “… And Then There Was a Standard”
• Evolution of the ANSI/ISO Standard
• Commercial Implementations
• What the Future Holds
• Why Should You Learn SQL?
• Which Version of SQL Does this Course Cover?
• Summary

Lessons 5: Creating a Simple Query

• Introducing SELECT
• The SELECT Statement
• A Quick Aside: Data versus Information
• Translating Your Request into SQL
• Eliminating Duplicate Rows
• Sorting Information
• Saving Your Work
• Sample Statements
• Summary
• Problems for You to Solve

Lessons 6: Getting More Than Simple Columns

• What Is an Expression?
• What Type of Data Are You Trying to Express?
• Changing Data Types: The CAST Function
• Specifying Explicit Values
• Types of Expressions
• Using Expressions in a SELECT Clause
• That “Nothing” Value: Null
• Sample Statements
• Summary
• Problems for You to Solve

Lessons 7: Filtering Your Data

• Refining What You See Using WHERE
• Defining Search Conditions
• Using Multiple Conditions
• Nulls Revisited: A Cautionary Note
• Expressing Conditions in Different Ways
• Sample Statements
• Summary
• Problems for You to Solve

Lessons 8: Thinking in Sets

• What Is a Set, Anyway?
• Operations on Sets
• Intersection
• Difference
• Union
• SQL Set Operations
• Summary

Lessons 9: INNER JOINs

• What Is a JOIN?
• The INNER JOIN
• Uses for INNER JOINs
• Sample Statements
• Summary
• Problems for You to Solve

Lessons 10: OUTER JOINs

• What Is an OUTER JOIN?
• The LEFT/RIGHT OUTER JOIN
• The FULL OUTER JOIN
• Uses for OUTER JOINs
• Sample Statements
• Summary
• Problems for You to Solve

Lessons 11: UNIONs

• What Is a UNION?
• Writing Requests with UNION
• Uses for UNION
• Sample Statements
• Summary
• Problems for You to Solve

Lessons 12: Subqueries

• What Is a Subquery?
• Subqueries as Column Expressions
• Subqueries as Filters
• Uses for Subqueries
• Sample Statements
• Summary
• Problems for You to Solve

Lessons 13: Simple Totals

• Aggregate Functions
• Using Aggregate Functions in Filters
• Sample Statements
• Summary
• Problems for You to Solve

Lessons 14: Grouping Data

• Why Group Data?
• The GROUP BY Clause
• “Some Restrictions Apply”
• Uses for GROUP BY
• Sample Statements
• Summary
• Problems for You to Solve

Lessons 15: Filtering Grouped Data

• A New Meaning for “Focus Groups”
• Where You Filter Makes a Difference
• Uses for HAVING
• Sample Statements
• Summary
• Problems for You to Solve

Lessons 16: Updating Sets of Data

• What Is an UPDATE?
• The UPDATE Statement
• Some Database Systems Allow a JOIN in the UPDATE Clause
• Uses for UPDATE
• Sample Statements
• Summary
• Problems for You to Solve

Lessons 17: Inserting Sets of Data

• What Is an INSERT?
• The INSERT Statement
• Uses for INSERT
• Sample Statements
• Summary
• Problems for You to Solve

Lessons 18: Deleting Sets of Data

• What Is a DELETE?
• The DELETE Statement
• Uses for DELETE
• Sample Statements
• Summary
• Problems for You to Solve

Lessons 19: “NOT” and “AND” Problems

• A Short Review of Sets
• Finding Out the “Not” Case
• Finding Multiple Matches in the Same Table
• Sample Statements
• Summary
• Problems for You to Solve

Lessons 20: Condition Testing

• Conditional Expressions (CASE)
• Solving Problems with CASE
• Sample Statements
• Summary
• Problems for You to Solve

Lessons 21: Using Unlinked Data and “Driver” Tables

• What Is Unlinked Data?
• Solving Problems with Unlinked Data
• Solving Problems Using “Driver” Tables
• Sample Statements
• Summary
• Problems for You to Solve

Lessons 22: Performing Complex Calculations on Groups

• Grouping in Sub-Groups
• Extending the GROUP BY Clause
• Getting Totals in a Hierarchy Using Rollup
• Calculating Totals on Combinations Using CUBE
• Creating a Union of Totals with GROUPING SETS
• Variations on Grouping Techniques
• Sample Statements
• Summary
• Problems for You to Solve

Lessons 23: Partitioning Data into Windows

• What You Can Do With a “Window” into Your Data
• Calculating a Row Number
• Ranking Data
• Splitting Data into Quintiles
• Using Windows with Aggregate Functions
• Sample Statements
• Summary
• Problems for You to Solve

Appendix A: SQL Standard Diagrams

Appendix B: Schema for the Sample Databases

• Sales Orders Example Database
• Sales Orders Modify Database
• Entertainment Agency Example Database
• Entertainment Agency Modify Database
• School Scheduling Example Database
• School Scheduling Modify Database
• Bowling League Example Database
• Bowling League Modify Database
• Recipes Database
• “Driver” Tables

Appendix C: Date and Time Types, Operations, and Functions

• IBM DB2
• Microsoft Access
• Microsoft SQL Server
• MySQL
• Oracle
• PostgreSQL

Hands-on LAB Activities

Creating a Simple Query

• Using the SELECT Statement
• Using the DISTINCT Keyword
• Using the ORDER BY Clause

Getting More Than Simple Columns

• Using the CAST Function
• Using a Literal
• Using the Concatenation Expression
• Using the NULL Values
• Naming an Expression
• Finding Null Values in a Column

Filtering Your Data

• Using the LIKE Predicate
• Using the IN Predicate
• Using the BETWEEN Predicate
• Using Comparison Predicates
• Using the WHERE Clause
• Using the NOT Operator
• Using the ESCAPE Option
• Using the Order of Precedence
• Using AND and OR Operators
• Using the NOT IN Operator

Thinking in Sets

• Using the UNION Operator
• Using the EXCEPT Operator
• Using the INTERSECT Operator

INNER JOINs

• Using an INNER JOIN
• Using a Subquery with the IN Predicate

OUTER JOINs

• Using the FULL OUTER JOIN
• Using the RIGHT OUTER JOIN
• Using the LEFT OUTER JOIN

UNIONs

• Sorting with UNION

Subqueries

• Using Subqueries
• Using the COUNT Function
• Using the SOME Predicate
• Using the ALL predicate
• Using the ANY Predicate

Simple Totals

• Using the MIN and MAX Functions
• Using the SUM and AVG Functions

Grouping Data

• Using the GROUP BY Clause

Filtering Grouped Data

• Using the HAVING Clause

Updating Sets of Data

• Using the UPDATE Statement

Inserting Sets of Data

• Using the INSERT Statement

Deleting Sets of Data

• Using the DELETE Statement

“NOT” and “AND” Problems

• Using the NOT EXISTS Command

Condition Testing

• Using the CASE Statement

Performing Complex Calculations on Groups

• Using ROLLUP
• Using the CUBE clause

Partitioning Data into Windows

• Using the RANK Function