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

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