Book Image

Learn SQL Database Programming

By : Josephine Bush
5 (1)
Book Image

Learn SQL Database Programming

5 (1)
By: Josephine Bush

Overview of this book

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.

Related Content you might be interested in

Current Title:

Small book image
Learn SQL Database Programming
Josephine Bush
May, 2020 | 564 pages
Small book image
The SQL Workshop
Frank Solomon | Awni Al Saqqa | Prashanth Jayaram
Dec, 2019 | 288 pages
Small book image
The MySQL Workshop
Thomas Pettit | Scott Cosentino
Apr, 2022 | 726 pages
Small book image
Data Wrangling with SQL
Raghav Kandarpa | Shivangi Saxena
Jul, 2023 | 350 pages
Table of Contents (24 chapters)
Preface
Preface
Who this book is for
What this book covers
To get the most out of this book
Get in touch
1
Section 1: Database Fundamentals
Section 1: Database Fundamentals
Free Chapter
2
Introduction to Relational Database Management Systems
Introduction to Relational Database Management Systems
Understanding SQL
Understanding databases
Understanding data integrity
Database normalization
Types of RDMS
Summary
Questions
3
Installing and Using MySQL Workbench
Installing and Using MySQL Workbench
Technical requirements
Installing MySQL Workbench
Using MySQL Workbench
Restoring a database
Summary
Questions
4
Understanding Data Types
Understanding Data Types
Understanding string data types
Understanding numeric data types
Understanding date and time data types
Understanding other data types
Choosing the right data type
Summary
Questions
5
Designing and Creating a Database
Designing and Creating a Database
Technical requirements
Creating a database
Understanding table relationships
Creating a table in the database
Understanding indexes
Summary
Further reading
Questions
6
Importing and Exporting Data
Importing and Exporting Data
Technical requirements
Understanding table data import and export
Understanding SQL data import and export
Understanding result data export
Understanding SQL syntax for importing and exporting data
Summary
Further reading
Questions
7
Section 2: Basic SQL Querying
Section 2: Basic SQL Querying
8
Querying a Single Table
Querying a Single Table
Technical requirements
Using the SELECT statement and FROM clause
Using the WHERE clause
Using the ORDER BY clause
Using indexes with your queries
Summary
Questions
Further reading
9
Querying Multiple Tables
Querying Multiple Tables
Technical requirements
Understanding joins
Using INNER JOIN
Using OUTER JOIN
Using advanced joins
Understanding set theory
Using indexes with your queries
Summary
Questions
Further reading
10
Modifying Data and Table Structures
Modifying Data and Table Structures
Technical requirements
Inserting data into tables
Deleting data from tables
Updating data in tables
Using transactions to save or revert changes
Modifying the table structure
Summary
Questions
Further reading
11
Section 3: Advanced SQL Querying
Section 3: Advanced SQL Querying
12
Working with Expressions
Working with Expressions
Technical requirements
Using expressions
Using statistical functions
Using generated columns
Summary
Questions
Further reading
13
Grouping and Summarizing Data
Grouping and Summarizing Data
Technical requirements
Understanding aggregate functions
Using the GROUP BY clause
Using the HAVING clause
Understanding SQL query order of execution
Summary
Questions
14
Advanced Querying Techniques
Advanced Querying Techniques
Technical requirements
Using subqueries
Using common table expressions
Using query hints and transaction isolation levels
Summary
Questions
Further reading
15
Programmable Objects
Programmable Objects
Technical requirements
Creating and using views
Creating and using variables
Creating and using stored procedures
Creating and using functions
Creating and using triggers
Creating and using temporary tables
Summary
Questions
Further reading
16
Section 4: Presenting Your Findings
Section 4: Presenting Your Findings
17
Exploring and Processing Your Data
Exploring and Processing Your Data
Technical requirements
Exploring your dataset
Processing your dataset
Summary
Questions
18
Telling a Story with Your Data
Telling a Story with Your Data
Technical requirements
Finding a narrative
Knowing your audience
Determining a presentation framework
Using visualizations
Summary
Questions
19
Section 5: SQL Best Practices
Section 5: SQL Best Practices
20
Best Practices for Designing and Querying
Best Practices for Designing and Querying
Technical requirements
Best practices for database design
Best practices for indexing
Best practices for querying and modifying data
Summary
Questions
21
SQL Appendix
SQL Appendix
SQL for designing databases
SQL for selecting data
SQL for modifying data
SQL expressions
Advanced query techniques
Programmable objects
Summary
22
Assessments
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
23
Other Books You May Enjoy
Other Books You May Enjoy
Leave a review - let other readers know what you think

Using transactions to save or revert changes

A SQL transaction is a grouping of one or more changes to the database. Transactions help ensure a consistent state in your database. The common terms in SQL transactions are COMMIT and ROLLLBACK. Commit makes the changes permanent and rollback cancels the changes.

There are four properties of transactions to keep in mind:

  • Atomicity: This ensures all changes in a transaction are completed successfully. If they are successful, the changes are committed. If any change isn't successful, all the changes are rolled back.
  • Consistency: This ensures any changes can't violate the database's integrity, including constraints. Changes interrupted by errors due to violations of database integrity are rolled back. This includes any changes that don't violate database integrity.
  • Isolation: All transactions are isolated from...
Previous Section
End of Section 6
Next Section