Getting Started with SQL and Databases : Managing and Manipulating Data with SQL

Learn the basics of writing SQL scripts. Using Standard SQL as the starting point, this book teaches writing SQL in various popular dialects, including PostgreSQL, MySQL/MariaDB, Microsoft SQL Server, Oracle, and SQLite. The book starts with a general introduction to writing SQL and covers the basic concepts. Author Mark Simon then covers database principles, and how database tables are designed. He teaches you how to filter data using the WHERE clause, and you will work with NULL, numbers, dates, and strings. You will also understand sorting results using the ORDER BY clause, sorting by calculated columns, and limiting the number of results. By the end of the book, you will know how to insert and update data, and summarize data with aggregate functions and groups. Three appendices cover differences between SQL dialects, working with tables, and a crash course in PDO. What You Will Learn Filter, sort, and calculate data Summarize data with aggregate functions Modify data with insert, update, and delete statements Study design principles in developing a database Who This Book Is For Developers and analysts working with SQL, as well as web developers who want a stronger understanding of working with databases Mark Simon has been involved in training and education since the beginning of his career. He started as a teacher of mathematics and moved into IT consultancy and training. He has worked with and trained in several programming and coding languages, and currently focuses on web development and database languages Table of Contents About the Author About the Technical Reviewer Acknowledgments Introduction Chapter 1: Starting with SQL Basic SELECT Statement Case Sensitivity Spacing Clause Ordering The Semicolon (;) Selecting Specific Columns Column Order Layout Using SELECT * Calculated Columns Aliases Comments Block Comments Uses of Comments Filtering Rows Clause Ordering Placing the Semicolon Ordering the Results Clause Order Distinct Rows Summary Writing SQL Columns Comments Filtering Data Row Order Clause Order Coming Up Chapter 2: Database About the Sample Database Database Database Terminology Data vs. Value Tables Table Terminology Normalized Tables Data Is Atomic Columns Are Independent Columns Are of a Single Type Rows Are Unordered Rows Are Unique Rows Are Independent Column Names Are Unique Columns Are Unordered Multiple Values Using Related Tables Example: Paintings and Artists Examples of Alternative Terminology A More Complex Relationship Summary Terms Normalized Tables Multiple Values Coming Up Chapter 3: Filtering Data The WHERE Clause Unrelated Assertions All and Nothing Dealing with NULL Deliberately Ignoring NULLs Finding NULLs Numbers Discrete vs. Continuous Values Strings Quotes More on MySQL/MariaDB Modes More on Double and Single Quotes Case Sensitivity Trailing Spaces Filtering with String Functions Handling Quotes and Apostrophes Before and After Strings Dates Dates Are Not Strings Alternative Date Formats Date Comparisons Filtering with a Date Calculation Multiple Assertions AND and OR The IN Operator Derived Lists Wildcard Matches Case Sensitivity and Patterns Pattern Characters Wildcards with Non-strings Extensions to Wildcards Regular Expressions (PostgreSQL, MySQL/MariaDB, Oracle) Simpler Extensions (PostgreSQL, MSSQL) A Simple Pattern Match Example Summary NULL Numbers Strings Dates Multiple Assertions The IN Operator Wildcard Matches Coming Up Chapter 4: Ordering Results Using the ORDER BY Clause Sort Direction Missing Data (NULL) Data Types Case Sensitivity and Collation Multiple Columns Interdependence of Columns Sort Direction on Multiple Columns Sorting by Calculated Columns Limiting the Number of Results Paging Using LIMIT ... OFFSET ... (MySQL/MariaDB, SQLite, and PostgreSQL) Using TOP (MSSQL) Fetching a Random Row Nonalphabetical String Order Special Strings Summary Sorting with ORDER BY Limiting Results Sorting Strings Coming Up Chapter 5: Calculating Column Values Testing Calculations Emulating Variables Some Basic Calculations Basic Number Calculations Basic String Calculations Basic Date Calculations Working with NULL Using Aliases Aliases Without AS Awkward Aliases Calculating with Numbers Arithmetic Operators Integers Remainder Extra Decimals Mathematical Functions Approximation Functions Formatting Functions Calculating with Dates Simple Calculations Age Calculations Extracting Parts of a Date Date Extraction in PostgreSQL, MariaDB/MySQL, and Oracle Date Extraction in Microsoft SQL Extracting a Date from a Datetime Formatting a Date Date Formatting in PostgreSQL and Oracle Date Formatting in MariaDB/MySQL Date Formatting in Microsoft SQL Server Using a Formatted Date for Grouping by Month Using a Formatted Date for Grouping by Weekday Strings Character Functions String Length Searching for a Substring Replace Change Case Trim Spaces Substrings Subqueries The CASE Expression Casting to Different Data Types The cast() Function Casting to a String Casting Date Literals Creating a View Using Views in Microsoft SQL Summary Data Types NULLs Aliases Subqueries The CASE Expression Casting a Value Views Coming Up Chapter 6: Joining Tables How a Join Works Joining the Tables Alternative Syntax Selecting the Results Table Aliases Developing a Price List Join Types The INNER JOIN The LEFT OUTER JOIN and RIGHT OUTER JOIN The “Preferred” Outer Join Some Recommendations on JOINS (Almost) Always Alias Your Tables Which Table Comes First? Decide Whether You Use INNER and OUTER Finishing the Price List Joining Many Tables Building a Larger JOIN Simplifying the Result Revisiting Some Subqueries A More Complex Join Using a Self-Join Summary Syntax Table Aliases The ON Clause Join Types Coming Up Chapter 7: Aggregating Data Counting Data Counting Values How Aggregates Work Counting Selectively Distinct Values Summarizing Numbers Bad Examples Scales of Measurement Aggregating Calculated Data Other Aggregate Functions Using Aggregates As Filters Grouping Using the GROUP BY Clause GROUP BY vs. DISTINCT Grouping with Multiple Tables Redundant Groups Preparing Data for Aggregating Using CASE in a CTE Using a Join in the CTE Summarizing Strings Filtering Grouped Results with HAVING Using Results in a CTE Finding Duplicates Using Aggregates on Aggregates Summary Coming Up Chapter 8: Working with Tables How Tables Are Created Creating a Table Column Names Data Type Primary Keys Constraints NOT NULL UNIQUE DEFAULT CHECK Foreign Keys Indexes Adding Rows to a Table Deleting Rows from a Table Adding More Rows Updating Rows Altering the Table DML in Real Life Security Front-End Software Summary Data Types Constraints Foreign Keys Indexes Manipulating Data Chapter 9: Set Operations Unions Selective Unions SELECT Clauses Must Be Compatible Only Column Names from the First SELECT Statement Are Used Sorting Results Intersections Differences Some Tricks with Set Operations Comparing Results Virtual Tables Mixing Aggregates Summary Appendix 1: Differences Between SQL Dialects Writing SQL Semicolons Data Types Dates Case Sensitivity Quote Marks Sorting (ORDER BY) Limiting Results Filtering (WHERE) Case Sensitivity String Comparisons Dates Wildcard Matching Calculations SELECT Without FROM Arithmetic Formatting Functions Date Functions Concatenation String Functions Joining Tables Aggregate Functions Manipulating Data Manipulating Tables Appendix 2: A Crash Course in PDO PDO Objects The PDO Object The PDOStatement Object Working with PDO Establishing a Connection Other DBMSs Prepared Statements and SQL Injection Prepared Statements Repeated Execution Unprepared (Direct) SQL Statements SELECT Statements INSERT, UPDATE, and DELETE Statements Selecting Data Fetching Data The Result Set Fetching a Single Column A Simple Login Script Getting the Last Auto-Incremented Key Error Reporting Summary of PDO Connection Executing Simple Statements INSERT, UPDATE, and DELETE SELECT Statements Executing Prepared Statements Reading Data Reading a Single Row Reading Multiple Rows Reading a Single Column Appendix 3: Additional Notes Cultural Notes Addresses and Phone Numbers Towns States Postcodes Phone Numbers Email Addresses Measurements, Prices, and Currency Dates SQL Data Values Stored Values Variables Literals Calculated Values Some Notes on Dates (and Times) Oracle Date Format Microsoft Age Function Working with SQLite Dates ISO 8601 Dates and Times Date Functions Manipulating Dates and Times Formatting Dates and Times Index