Learn SQL Database Programming: Query and manipulate databases from popular relational database servers using SQL

✍ Scribed by Josephine Bush

• Publisher: Packt Publishing
• Year: 2020
• Tongue: English
• Leaves: 550
• Category: Library

✦ Synopsis

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. Introduction to Relational Database Management Systems
2. Installing and Using MySQL Workbench
3. Understanding Data Types
4. Designing and Creating a Database
5. Importing and Exporting Data
6. Querying a Single Table
7. Querying Multiple Tables
8. Modifying Data and Table Structures
9. Working with Expressions
10. Grouping and Summarizing Data
11. Advanced Querying Techniques
12. Programmable Objects
13. Exploring and Processing Your Data
14. Tell a Story with Your Data
15. Best Practices for Design and Querying
16. SQL Appendix

✦ Table of Contents

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