SQL Queries for Mere Mortals
(SQL-MM.AB1)/ISBN:978-1-64459-369-1
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.
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
Need guidance and support? Click here to check our Instructor Led Course.
Here's what you will learn
Download Course OutlineLessons 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