uCertify

SQL Queries for Mere Mortals

(SQL-MM.AB1) / ISBN : 978-1-64459-369-1
This course includes
Lessons
TestPrep
Hands-On Labs
AI Tutor (Add-on)
105 Review
Get A Free Trial

About This Course

SQL 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.

Get the support you need. Enroll in our Instructor-Led Course.

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

Hands-On Labs

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

Download Course Outline

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
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
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
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
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
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
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
8

Thinking in Sets

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

INNER JOINs

  • What Is a JOIN?
  • The INNER JOIN
  • Uses for INNER JOINs
  • Sample Statements
  • Summary
  • Problems for You to Solve
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
11

UNIONs

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

Subqueries

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

Simple Totals

  • Aggregate Functions
  • Using Aggregate Functions in Filters
  • Sample Statements
  • Summary
  • Problems for You to Solve
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
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
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
17

Inserting Sets of Data

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

Deleting Sets of Data

  • What Is a DELETE?
  • The DELETE Statement
  • Uses for DELETE
  • Sample Statements
  • Summary
  • Problems for You to Solve
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
20

Condition Testing

  • Conditional Expressions (CASE)
  • Solving Problems with CASE
  • Sample Statements
  • Summary
  • Problems for You to Solve
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
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
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

4

Creating a Simple Query

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

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
6

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
7

Thinking in Sets

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

INNER JOINs

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

OUTER JOINs

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

UNIONs

  • Sorting with UNION
11

Subqueries

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

Simple Totals

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

Grouping Data

  • Using the GROUP BY Clause
14

Filtering Grouped Data

  • Using the HAVING Clause
15

Updating Sets of Data

  • Using the UPDATE Statement
16

Inserting Sets of Data

  • Using the INSERT Statement
17

Deleting Sets of Data

  • Using the DELETE Statement
18

“NOT” and “AND” Problems

  • Using the NOT EXISTS Command
19

Condition Testing

  • Using the CASE Statement
21

Performing Complex Calculations on Groups

  • Using ROLLUP
  • Using the CUBE clause
22

Partitioning Data into Windows

  • Using the RANK Function

SQL Queries for Mere Mortals

$279.99

Buy Now

Related Courses

All Course
scroll to top