Learn SQL Database Programming

Learn SQL Database Programming

by Josephine Bush
Released May 2020
Publisher(s): Packt Publishing
ISBN: 9781838984762

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

Book description

Learn everything you need to know to build efficient SQL queries using this easy-to-follow beginner's guide

Key Features

  • Explore all SQL statements in depth using a variety of examples
  • Get to grips with database querying, data aggregate, manipulation, and much more
  • Understand how to explore and process data of varying complexity to tell a story

Book Description

SQL is a powerful querying language that's used to store, manipulate, and retrieve data, and it is one of the most popular languages used by developers to query and analyze data efficiently.

If you're looking for a comprehensive introduction to SQL, Learn SQL Database Programming will help you to get up to speed with using SQL to streamline your work in no time. Starting with an overview of relational database management systems, this book will show you how to set up and use MySQL Workbench and design a database using practical examples. You'll also discover how to query and manipulate data with SQL programming using MySQL Workbench. As you advance, you'll create a database, query single and multiple tables, and modify data using SQL querying. This SQL book covers advanced SQL techniques, including aggregate functions, flow control statements, error handling, and subqueries, and helps you process your data to present your findings. Finally, you'll implement best practices for writing SQL and designing indexes and tables.

By the end of this SQL programming book, you'll have gained the confidence to use SQL queries to retrieve and manipulate data.

What you will learn

  • Install, configure, and use MySQL Workbench to restore a database
  • Explore different data types such as string, numeric, and date and time
  • Query a single table using the basic SQL SELECT statement and the FROM, WHERE, and ORDER BY clauses
  • Query multiple tables by understanding various types of table relationships
  • Modify data in tables using the INSERT, UPDATE, and DELETE statements
  • Use aggregate functions to group and summarize data
  • Detect bad data, duplicates, and irrelevant values while processing data

Who this book is for

This book is for business analysts, SQL developers, database administrators, and students learning SQL. If you want to learn how to query and manipulate SQL data for database administration tasks or simply extract and organize relevant data for analysis, you'll find this book useful. No prior SQL experience is required.

Table of contents

  1. Title Page
  2. Copyright and Credits
    1. Learn SQL Database Programming
  3. About Packt
    1. Why subscribe?
  4. Contributors
    1. About the author
    2. About the reviewers
    3. Packt is searching for authors like you
  5. Preface
    1. Who this book is for
    2. What this book covers
    3. To get the most out of this book
      1. Download the example code files
      2. Download the color images
      3. Conventions used
    4. Get in touch
      1. Reviews
  6. Section 1: Database Fundamentals
  7. Introduction to Relational Database Management Systems
    1. Understanding SQL
      1. Elements of SQL
    2. Understanding databases
      1. Tables
      2. Fields
      3. Records or rows
      4. Columns
    3. Understanding data integrity
      1. Types of integrity
        1. Entity integrity
          1. Unique constraints
          2. Not null constraints
          3. The primary key
        2. Referential integrity
        3. Domain integrity
    4. Database normalization
      1. The first normal form
      2. The second normal form
      3. The third normal form
    5. Types of RDMS
      1. Oracle
      2. MySQL
      3. SQL Server
      4. PostgreSQL
      5. RDMS SQL differences
    6. Summary
    7. Questions
  8. Installing and Using MySQL Workbench
    1. Technical requirements
    2. Installing MySQL Workbench
      1. Installing on Windows
        1. Installing MySQL on Windows
        2. Configuring MySQL on Windows
      2. Installing on Mac
        1. Installing MySQL Server on Mac
          1. Checking the status of MySQL Server on Mac
        2. Installing MySQL Workbench on Mac
    3. Using MySQL Workbench
      1. Connecting to your local instance
      2. Connecting to another instance or setting up your local instance
    4. Restoring a database
    5. Summary
    6. Questions
  9. Understanding Data Types
    1. Understanding string data types
      1. String data types in MySQL
      2. MySQL string data type table summary
      3. String data types in other RDMS
        1. Oracle
        2. PostgreSQL
        3. SQL Server
      4. String data types RDMS table comparison
    2. Understanding numeric data types
      1. Numeric data types in MySQL
      2. MySQL numeric data type table summary
      3. Numeric data types in other RDMSes
        1. SQL Server
        2. Oracle
        3. PostgreSQL
      4. Numeric data types table comparison
    3. Understanding date and time data types
      1. Date and time data types in MySQL
      2. MySQL date and time data type table summary
      3. Date and time data types in other RDMSes
        1. Oracle
        2. PostgreSQL
        3. SQL Server
      4. Date and time data types table comparison
    4. Understanding other data types
      1. Other data types in MySQL
      2. Other data types in other RDMSes
        1. Oracle
        2. PostgreSQL
        3. SQL Server
    5. Choosing the right data type
      1. Examples of choosing a data type
    6. Summary
    7. Questions
  10. Designing and Creating a Database
    1. Technical requirements
    2. Creating a database
      1. Guidelines for naming conventions
        1. Avoiding keywords
        2. Avoiding spaces
        3. Descriptive and accurate naming
        4. Case and separating words
        5. Allowed characters when naming database objects
      2. Learning how to create a database
        1. Creating a database via the MySQL Workbench interface
        2. Creating a database via MySQL Workbench with a SQL script
      3. Understanding SQL code errors
    3. Understanding table relationships
      1. Understanding entity-relationship diagrams
      2. Understanding one-to-one table relationships
      3. Understanding one-to-many table relationships
      4. Understanding many-to-many table relationships
    4. Creating a table in the database
      1. Understanding how to apply data types and data integrity to your table
      2. Learning to create a database table
        1. Natural and surrogate primary keys
        2. Creating a database table via MySQL Workbench
        3. Creating a database table via MySQL Workbench with SQL scripts
      3. Learning how to format SQL code for readability
        1. Commenting SQL code
    5. Understanding indexes
      1. Understanding how indexing relates to data integrity
        1. Types of indexes
      2. Understanding how indexing impacts performance
      3. Understanding naming conventions for indexes
    6. Summary
    7. Further reading
    8. Questions
  11. Importing and Exporting Data
    1. Technical requirements
    2. Understanding table data import and export
      1. Importing CSV files with table data import
      2. Exporting to CSV files with table data export
    3. Understanding SQL data import and export
      1. Importing via data import in MySQL Workbench
      2. Exporting via data export in MySQL Workbench
    4. Understanding result data export
      1. Exporting data directly from a result set
    5. Understanding SQL syntax for importing and exporting data
      1. Importing with a SQL script
      2. Exporting with a SQL script
    6. Summary
    7. Further reading
    8. Questions
  12. Section 2: Basic SQL Querying
  13. Querying a Single Table
    1. Technical requirements
    2. Using the SELECT statement and FROM clause
      1. Understanding the SELECT statement and the FROM clause
      2. Learning the correct order of other clauses you can use with SELECT
      3. Understanding the different ways to query with a SELECT statement
      4. Learning how to use column aliases
      5. Using the USE statement
      6. Learning how to use the DISTINCT clause
      7. Learning how to use the LIMIT clause
        1. Limiting results on other Relational Database Management Systems (RDMSes)
      8. Learning how to save a SQL query to a file
      9. Learning how to open a SQL file
      10. Learning how to add comments to your SQL code
        1. Commenting code on other RDMSes
    3. Using the WHERE clause
      1. Understanding how and when to use the WHERE clause to limit query results
      2. Learning how to use the AND and OR operators
      3. Learning how to use the NOT, IN, and BETWEEN operators
      4. Learning how to use the LIKE operator and wildcards
        1. Using the percent (%) wildcard
        2. Using the underscore (_) wildcard
        3. Escaping wildcard values
        4. Differences between LIKE in other RDMSes
      5. Learning how to filter on NULL values
    4. Using the ORDER BY clause
      1. Learning how to use the ORDER BY clause to order query results
      2. Learning how to use the ORDER BY clause to sort by one or more columns
    5. Using indexes with your queries
      1. Learning how to see what indexes your query is using
    6. Summary
    7. Questions
    8. Further reading
  14. Querying Multiple Tables
    1. Technical requirements
    2. Understanding joins
      1. Understanding results returned with an inner join
      2. Understanding results returned with a left outer join
      3. Understanding results returned with a right outer join
      4. Understanding results returned with a full outer join
    3. Using INNER JOIN
      1. Learning INNER JOIN syntax
      2. Learning how to use table aliases
    4. Using OUTER JOIN
      1. Learning LEFT OUTER JOIN syntax
      2. Learning RIGHT OUTER JOIN syntax
      3. Exploring differences in other relational data models
        1. Using FULL OUTER JOIN
    5. Using advanced joins
      1. Understanding what a CROSS JOIN is and how to use it
      2. Understanding what a NATURAL JOIN is and how to use it
      3. Understanding what a SELF JOIN is and how to use it
    6. Understanding set theory
      1. Understanding what a UNION join is and learning how to use it in a SQL query
        1. UNION
        2. UNION ALL
      2. Understanding what an intersect is and learning how to use it in a SQL query
        1. Looking at intersection in other RDMS
      3. Understanding what difference is and learning how to use it in a SQL query
        1. Exploring differences in other RDMS
        2. EXCEPT
        3. MINUS
    7. Using indexes with your queries
    8. Summary
    9. Questions
    10. Further reading
  15. Modifying Data and Table Structures
    1. Technical requirements
    2. Inserting data into tables
      1. Gathering information to insert, update, or delete data
      2. Using the INSERT statement
        1. Single-row inserts
        2. Multiple row inserts
        3. Differences in other Relational Database Management Systems
      3. Inserting data from one table into another table
        1. Differences to other RDMSes
    3. Deleting data from tables
      1. Using the DELETE statement with a WHERE clause
      2. Deleting all the data from a table
      3. Learning an alternative way to delete data with the TRUNCATE statement
    4. Updating data in tables
      1. Using the UPDATE statement with a WHERE clause
      2. Updating all the data in a table
      3. Updating table data from another existing table
    5. Using transactions to save or revert changes
      1. Understanding a SQL transaction
      2. Learning the SQL syntax for SQL transactions
        1. Differences in RDMS transaction syntax
    6. Modifying the table structure
      1. Adding a column
      2. Dropping a column
      3. Renaming a column
      4. Changing the data type of a column
      5. Adding or changing a column constraint
      6. Dropping a constraint, key, or index
        1. Differences to other RDMS
      7. Dropping a table
    7. Summary
    8. Questions
    9. Further reading
  16. Section 3: Advanced SQL Querying
  17. Working with Expressions
    1. Technical requirements
    2. Using expressions
      1. Literal values
      2. Operators
        1. Comparison operators
        2. Logical operators
        3. Mathematical operators
          1. Operator precedence
      3. Column values
      4. Built-in functions
        1. String built-in functions
          1. Differences in RDMS (Relational Database Management Systems)
        2. Numeric built-in functions
          1. Differences in RDMS
        3. Datetime built-in functions
          1. Working with time zones
          2. Differences in RDMS
        4. Advanced built-in functions
          1. Working with NULL values
          2. Differences in advanced built-in functions in RDMS
        5. Built-in functions and indexing
    3. Using statistical functions
      1. Learning how to use built-in statistical functions
        1. Exploring differences in RDMS
    4. Using generated columns
      1. Types of generated columns
      2. Creating a generated column
        1. Differences in RDMSes
    5. Summary
    6. Questions
    7. Further reading
  18. Grouping and Summarizing Data
    1. Technical requirements
    2. Understanding aggregate functions
      1. Numeric aggregate functions
      2. Statistical aggregate functions
    3. Using the GROUP BY clause
      1. Understanding how GROUP BY works without aggregate functions
        1. Using WHERE with GROUP BY
        2. Using ORDER BY with GROUP BY
      2. Learning how to use the GROUP BY clause to group query results using aggregate functions
      3. Learning how to use the ROLLUP modifier
        1. Differences in RDBMSes
    4. Using the HAVING clause
      1. Learning how to use the HAVING clause to limit query results
      2. Understanding the difference between the HAVING and WHERE clauses
    5. Understanding SQL query order of execution
    6. Summary
    7. Questions
  19. Advanced Querying Techniques
    1. Technical requirements
    2. Using subqueries
      1. Understanding the different types of subqueries and their usage
        1. Using non-correlated subqueries
          1. Using a non-correlated subquery in the WHERE clause
          2. Using a non-correlated subquery in the SELECT clause
          3. Using a non-correlated subquery in the FROM clause
          4. Using INSERT, UPDATE, and DELETE with non-correlated subqueries
          5. Differences between non-correlated subqueries in other relational database management systems (RDMSes)
        2. Using correlated subqueries
          1. Using a correlated subquery in the WHERE clause
          2. Using a correlated subquery in the SELECT clause
    3. Using common table expressions
      1. Using non-recursive CTEs
        1. Non-recursive CTE with the SELECT statement
      2. Using recursive CTEs
        1. Differences between CTEs in other RDMSes
    4. Using query hints and transaction isolation levels
      1. Understand the concepts of locking, blocking, and deadlocking
      2. Learning how to use index hints to improve queries
      3. Learning how to use transaction isolation levels
    5. Summary
    6. Questions
    7. Further reading
  20. Programmable Objects
    1. Technical requirements
    2. Creating and using views
      1. Learning how to create and query a view
      2. Learning how to modify data returned in a view
        1. Updating data using a view
          1. Updating data using a view that has multiple tables
        2. Inserting data using a view
          1. Inserting data using a view that has multiple tables
        3. Deleting data using a view
          1. Deleting data using a view that has multiple tables
      3. Learning how to update or delete a view
      4. Differences between views in other relational database management systems (RDBMSes)
    3. Creating and using variables
      1. Learning how to create and assign values to variables
      2. Learning how to use variables in SQL statements
      3. Differences between variables in other RDBMSes
    4. Creating and using stored procedures
      1. Creating a stored procedure
      2. Learning how to alter and drop stored procedures
      3. Using variables and parameters in stored procedures
        1. IN parameter
        2. OUT parameter
      4. Using flow control statements
        1. Understanding the different types of flow control statements
        2. Understanding the difference between the IF and CASE statements and how to use them
        3. Understanding how to loop through statements
      5. Using error handling
        1. Understanding error handling syntax and how to implement error handling
      6. Differences between stored procedures in other RDBMSes
        1. Oracle
          1. Creating and calling a stored procedure in Oracle
          2. Flow control in Oracle
          3. Error handling in Oracle
        2. PostgreSQL
          1. Creating a stored procedure in PostgreSQL
          2. Flow control in PostgreSQL
          3. Error handling in PostgreSQL
        3. SQL Server
          1. Creating and calling a stored procedure in SQL Server
          2. Flow control in SQL Server
          3. Error handling in SQL Server
    5. Creating and using functions
      1. Understanding the difference between a function and a stored procedure
      2. Learning how to create and use functions
      3. Learning how to alter or delete functions
      4. Differences between functions in other RDBMSes
        1. Oracle
        2. PostgreSQL
        3. SQL Server
    6. Creating and using triggers
      1. Learning how to create and use a trigger
        1. Creating and using a trigger with one statement
        2. Creating and using a trigger with multiple statements
        3. Creating and using multiple triggers on the same table
        4. Deleting a trigger
      2. Differences between triggers in other RDBMSes
    7. Creating and using temporary tables
      1. Learning how to create and use a temporary table
      2. Learning how to delete a temporary table
      3. Differences between temporary tables in other RDBMSes
    8. Summary
    9. Questions
    10. Further reading
  21. Section 4: Presenting Your Findings
  22. Exploring and Processing Your Data
    1. Technical requirements
    2. Exploring your dataset
      1. Getting to know your data using statistical identities
      2. Detecting rare and outlier values
      3. Detecting missing values
      4. Detecting duplicate and erroneous values
      5. Consulting with experts or becoming the expert
        1. Creating a data dictionary
      6. Using regular expressions
        1. Combining regular expression characters
    3. Processing your dataset
      1. Fixing rare and outlier values
      2. Fixing missing values
      3. Removing or fixing duplicates
        1. Removing duplicates
        2. Fixing duplicates
      4. Fixing erroneous data
    4. Summary
    5. Questions
  23. Telling a Story with Your Data
    1. Technical requirements
    2. Finding a narrative
      1. Types of data stories
      2. Asking questions to find your narrative
      3. Using the statistical identity of your data to determine a narrative
    3. Knowing your audience
      1. Determining who your audience is
      2. Creating a compelling presentation for your audience
    4. Determining a presentation framework
      1. Explaining the question
      2. Explaining the answer
      3. Explaining your methodology
    5. Using visualizations
      1. Common mistakes to avoid in visualizations
      2. Using data visualization tools
    6. Summary
    7. Questions
  24. Section 5: SQL Best Practices
  25. Best Practices for Designing and Querying
    1. Technical requirements
    2. Best practices for database design
      1. Understanding data integrity
      2. Naming conventions of database objects
      3. Understanding what data types to use
    3. Best practices for indexing
      1. Understanding when to create indexes
    4. Best practices for querying and modifying data
      1. Understanding how to write clean code
      2. Understanding query optimization
      3. Understanding best practices when querying data
      4. Understanding best practices when modifying data
    5. Summary
    6. Questions
  26. SQL Appendix
    1. SQL for designing databases
      1. Syntax for creating a database
      2. Syntax for creating and altering tables
      3. Syntax for creating and altering indexes
    2. SQL for selecting data
      1. Syntax for selecting data
      2. Syntax for filtering data
      3. Syntax for ordering results
      4. Syntax for joining tables
      5. Syntax for grouping results
      6. Syntax for filtering grouped results
      7. Syntax for using aggregate functions
    3. SQL for modifying data
      1. Syntax for inserting data
      2. Syntax for updating data
      3. Syntax for deleting data
      4. Syntax for SQL transactions
    4. SQL expressions
      1. Types of expressions
      2. Syntax for using generated columns
    5. Advanced query techniques
      1. Syntax for subqueries
      2. Syntax for common table expressions
      3. Syntax for query hints
      4. Syntax for transaction isolation level
    6. Programmable objects
      1. Syntax for views
      2. Syntax for variables
      3. Syntax for stored procedures
        1. Syntax for flow control statements
        2. Syntax for error handling
      4. Syntax for functions
      5. Syntax for triggers
      6. Syntax for temporary tables
    7. Summary
  27. Assessments
    1. Chapter 1
    2. Chapter 2
    3. Chapter 3
    4. Chapter 4
    5. Chapter 5
    6. Chapter 6
    7. Chapter 7
    8. Chapter 8
    9. Chapter 9
    10. Chapter 10
    11. Chapter 11
    12. Chapter 12
    13. Chapter 13
    14. Chapter 14
    15. Chapter 15
  28. Other Books You May Enjoy
    1. Leave a review - let other readers know what you think

Product information

  • Title: Learn SQL Database Programming
  • Author(s): Josephine Bush
  • Release date: May 2020
  • Publisher(s): Packt Publishing
  • ISBN: 9781838984762