Learn SQL Database Programming by Josephine Bush -- Read -- Imperial Library of Trantor

Index

Title Page Copyright and Credits

Learn SQL Database Programming

About Packt

Why subscribe?

Contributors

About the author About the reviewers Packt is searching for authors like you

Preface

Who this book is for What this book covers To get the most out of this book

Download the example code files Download the color images Conventions used

Get in touch

Reviews

Section 1: Database Fundamentals 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

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

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

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

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 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

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

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 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

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

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

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 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

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 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

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

Leave a review - let other readers know what you think

← Prev
Back
Next →

← Prev
Back
Next →