Job Ready SQL

Description

Learn the most important SQL skills and apply them in your job—quickly and efficiently! SQL (Structured Query Language) is the modern language that almost every relational database system supports for adding data, retrieving data, and modifying data in a database. Although basic visual tools are available to help end-users input common commands, data scientists, business intelligence analysts, Cloud engineers, Machine Learning programmers, and other professionals routinely need to query a database using SQL. Job Ready SQL provides you with the foundational skills necessary to work with data of any kind. Offering a straightforward ‘learn-by-doing’ approach, this concise and highly practical guide teaches you all the basics of SQL so you can apply your knowledge in real-world environments immediately. Throughout the book, each lesson includes clear explanations of key concepts and hands-on exercises that mirror real-world SQL tasks. Teaches the basics of SQL database creation and management using easy-to-understand language Helps readers develop an understanding of fundamental concepts and more advanced applications such as data engineering and data science Discusses the key types of SQL commands, including Data Definition Language (DDL) commands and Data Manipulation Language (DML) commands Includes useful reference information on querying SQL-based databases Job Ready SQL is a must-have resource for students and working professionals looking to quickly get up to speed with SQL and take their relational database skills to the next level. Cover 2 Title Page 3 Introduction 1 WHAT DOES THIS BOOK COVER? 2 READER SUPPORT FOR THIS BOOK 3 Part I: Introduction to Database Concepts 1 Lesson 1: Exploring Relational Databases and SQL 1 SAVING DATA 2 WHAT IS A DATABASE? 3 RELATIONAL DATABASE CONCEPTS 4 ACID COMPLIANCE 5 ENTITY INTEGRITY 6 BACKUP STRATEGIES 7 SUMMARY 8 EXERCISES 9 Lesson 2: Applying Normalization 1 WHAT IS NORMALIZATION? 2 NORMALIZING DATA 3 FIRST NORMAL FORM 4 SECOND NORMAL FORM 5 THIRD NORMAL FORM 6 DENORMALIZATION 7 SUMMARY 8 EXERCISES 9 Lesson 3: Creating Entity‐Relationship Diagrams 1 USING ERDs 2 ERD COMPONENTS 3 ERD OF DATABASE 4 WHAT ABOUT MANY‐TO‐MANY RELATIONSHIPS? 5 SUMMARY 6 EXERCISES 7 Lesson 4: Pulling It All Together: Normalizing a Vinyl Record Shop Database 1 THE VINYL RECORD SHOP DATA OVERVIEW 2 STEP 1: IDENTIFY THE ENTITIES AND ATTRIBUTES 3 STEP 2: FIRST NORMAL FORM 4 STEP 3: SECOND NORMAL FORM 5 STEP 4: THIRD NORMAL FORM 6 STEP 5: FINALIZE THE STRUCTURE 7 FINAL STEPS SUMMARY 9 Part II: Applying SQL 1 Lesson 5: Working with MySQL Server 1 MySQL INSTALLATION 2 MySQL NOTIFIER 3 COMMAND‐LINE INTERFACE 4 GETTING STARTED WITH MySQL WORKBENCH 5 SUMMARY 6 EXERCISES 7 Lesson 6: Diving into SQL 1 INTRODUCTION TO SQL 2 SQL SYNTAX 3 WORKING WITH NULL VALUES 4 WORKING WITH INDEXES 5 SUMMARY 6 EXERCISES 7 Lesson 7: Database Management Using DDL 1 DATABASE MANAGEMENT 2 MySQL DATA TYPES 3 MANAGING TABLES IN MYSQL 4 MANAGING RELATIONSHIPS IN MySQL 5 SUMMARY 6 EXERCISES 7 Lesson 8: Pulling It All Together: Building the Vinyl Record Shop Database 1 STEP 1: EXAMINE THE STRUCTURE 2 STEP 2: CREATE THE DATABASE 3 STEP 3: CREATE THE PRIMARY TABLES 4 STEP 4: CREATE THE RELATED TABLES 5 STEP 5: FINALIZE THE SCRIPT 6 SUMMARY 7 Part III: Data Management and Manipulation 1 Lesson 9: Applying CRUD: Basic Data Management and Manipulation 1 DATA MANIPULATION LANGUAGE 2 CREATE A DATABASE 3 INSERT DATA 4 UPDATE DATA 5 DELETE DATA 6 SUMMARY 7 EXERCISES 8 Lesson 10: Working with SELECT Queries 1 SETTING UP A DATABASE 2 USING THE SELECT KEYWORD 3 USING THE WHERE CLAUSE PERFORMING CALCULATIONS 5 SUMMARY 6 EXERCISES 7 Lesson 11: Adding JOIN Queries 1 STARTING WITH A SCHEMA 2 GET DATA FROM MULTIPLE TABLES 3 USE THE JOIN CLAUSE 4 INNER JOIN 5 OUTER JOIN: LEFT, RIGHT, AND FULL 6 SELF‐JOIN AND ALIASES 7 CROSS JOIN 8 SUMMARY 9 EXERCISES 10 Lesson 12: Sorting and Limiting Query Results 1 USING ORDER BY 2 USING LIMIT 3 USING DISTINCT 4 SUMMARY 5 EXERCISES 6 Lesson 13: Grouping and Aggregates 1 AGGREGATE FUNCTIONS 2 USING GROUP BY 3 USING HAVING 4 SELECT EVALUATION ORDER 5 OTHER EXAMPLES 6 SUMMARY 7 EXERCISES 8 Lesson 14: Pulling It All Together: Adding Data to the Vinyl Record Shop Database 1 INSERTING DATA 2 IMPORT CSV DATA 3 ADD DATA TO THE SCRIPT 4 TEST THE SCRIPT 5 WRAP UP THE VINYL MUSIC SHOP SCRIPT 6 SUMMARY 7 Lesson 15: Diving into Advanced SQL Topics 1 ADDING SUBQUERIES 2 WORKING WITH VIEWS 3 UNDERSTANDING TRANSACTIONS 4 SCHEMA OPTIMIZATION 5 SUMMARY 6 EXERCISES 7 Appendix A: Bonus Lesson on Applying SQL with Python 1 DATABASE OPERATIONS 2 TABLE OPERATIONS 3 DATA OPERATIONS: CRUD 4 SUMMARY EXERCISES 6 Appendix B: SQL Quick Reference 1 WORKING WITH DATABASES 2 DEFINING TABLES, COLUMNS, AND ROWS 3 PERFORMING TABLE QUERIES 4 BASIC SQL DATA TYPES 5 Index 6 Copyright 7 Acknowledgments 8 About the Authors 9 About the Technical Writer 10 About the Technical Editor 11 End User License Agreement