▶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
▶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
▶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.
▶What this book covers
- Chapter 1, Introduction to Relational Database Management Systems, introduces the concepts required to understand the basics of relational database management systems. It introduces foundational topics such as understanding SQL, the relational model, data integrity, database normalization, and the various types of relational database management systems. It gives you fundamental knowledge about SQL and databases that will be required throughout the book.
- Chapter 2, Installing and Using MySQL Workbench, covers how to install MySQL Workbench on Windows and Mac, including step-by-step instructions to help you walk through each part of the installation process. The instructions also include the configuration of MySQL Workbench on both Windows and Mac. We will walk through some examples of connecting to your local MySQL and also setting up connections to other MySQL servers. We conclude with a step-by-step explanation of how to restore a database to MySQL.
- Chapter 3, Understanding Data Types, covers what data types are and how they are used. You will learn about specific data types and what data can be stored in each of them. The data types include string, numeric, and date and time. String data types include char and varchar, binary and varbinary, blob, enum, and text. Numeric data types include bit, int, float, double, and decimal. Date and time data types include date, time, datetime, timestamp, and year. You will learn from the perspective of MySQL data types, but where there are differences versus SQL Server, Oracle, and PostgreSQL, those differences will be noted. We will also go through some examples of types and values of data to see how to assign them correctly to data types, including an explanation of why you need to be careful when selecting a data type and how it can impact database performance.
- Chapter 4, Designing and Creating a Database, introduces you to designing and creating a database. We'll walk through the guidelines for naming conventions and understand SQL code errors. You will learn how to format SQL code for readability and apply data types and data integrity to our tables. You will also learn about the different types of table relationships and how to build entity-relationship diagrams. Going further, we will discuss the concept and usage of indexing. You will gain an understanding of how indexing helps database performance. Finally, you will learn how to create a table in a database.
- Chapter 5, Importing and Exporting Data, introduces you to importing and exporting data. There are many ways to import and export data in MySQL. You will learn how to import and export data using MySQL Workbench via table data from/to CSV files. We will also cover importing and exporting via SQL data with SQL scripts. An additional way to export data via result data and query results will also be covered. The final topic discussed is using SQL syntax to import and export data.
- Chapter 6, Querying a Single Table, covers how to use the basic SQL SELECT statement and the FROM, WHERE, and ORDER BY clauses. This chapter also covers how to tell which index your query is using and whether you may need additional indexes. By the end of this chapter, you will understand how to query data using the SELECT statement and the FROM clause. You will also learn how to limit results with a WHERE clause, how to use ORDER BY to return results in a specified order, and how to see information about what indexes are being used or may be needed.
- Chapter 7, Querying Multiple Tables, covers how to use SQL joins to join two or more tables together, including INNER, OUTER (LEFT, RIGHT, and FULL), and advanced joins (the cross and self joins). You will learn about set theory and how to combine queries using UNION and UNION ALL, and how to get the differences and intersections of different queries. Lastly, you will learn how to optimize queries when they contain multiple tables.
- Chapter 8, Modifying Data and Table Structures, goes through how to modify data in tables. This includes learning how to use INSERT, UPDATE, and DELETE statements. You will also learn about SQL transactions, which help to control the modification of data. Finally, you will learn how to modify a table structure.
- Chapter 9, Working with Expressions, covers how to use literals, operators, columns, and built-in functions to create expressions. You will learn about the different types of built-in functions, including string, numeric, date and time, and advanced functions, which include casting and converting to other data types. You will learn how to use statistical functions, including how to get and use variance and standard deviation. Finally, you will learn how to create a generated column based on an expression.
- Chapter 10, Grouping and Summarizing Data, covers how to use aggregate functions to group and summarize data. Aggregate functions include math functions such as AVG, SUM, COUNT, MIN, and MAX. You will also learn how to use the GROUP BY and HAVING clauses in conjunction with the aggregate functions. Finally, you will learn how MySQL executes your query clauses.
- Chapter 11, Advanced Querying Techniques, covers how to use two different kinds of subqueries, correlated and non-correlated. Then, you will learn about two different types of common table expressions, recursive and non-recursive. You will learn about query hints and how to choose which index your query will use. Finally, you will learn about isolation levels and concepts relating to how data is read from and written to tables.
- Chapter 12, Programmable Objects, covers how to create and use views, which includes selecting data from views, and inserting, updating, and deleting data using views. You will learn how to create and use variables, which includes how to declare and assign values to variables. You will also learn how to create and use stored procedures, including how to use variables and parameters in stored procedures, as well as how to control flow and error handling. In addition to all that, you will learn how to create and use functions, triggers, and temporary tables.
- Chapter 13, Exploring and Processing Your Data, covers how to explore and process data. By the end of this chapter, you will understand how to get to know data by creating a statistical identity, you will have learned how to detect and fix anomalous and missing values, and will know how to use regular expressions to match data value patterns.
- Chapter 14, Telling a Story with Your Data, teaches you how to find a narrative, including what types of stories you can tell with data and how to use the statistical identity of your data to determine a story. You will also learn about knowing your audience, including deciding who they are and what would be a compelling presentation for them. Then, you will learn how to identify a presentation framework, including explaining the question, answer, and methodology. Finally, you will learn how to use visualizations in your presentations.
- Chapter 15, Best Practices for Designing and Querying, covers database best practices for database design, indexing, and querying and modifying data. You learned about these topics in the previous chapters, and this chapter will summarize and give additional tips for best practices. This chapter will also provide a way for the more experienced among you to quickly reference best practices instead of having to go through each chapter.
- Chapter 16, SQL Appendix, covers the SQL commands discussed, which are outlined for quick reference. It includes the syntax for querying data, modifying data, and designing databases and tables. This chapter will help you by providing a quick reference guide, so you won't have to go back through all the chapters to check the syntax, but if you require more details about how the syntax works, you can refer to the specific chapter for that information.
