book

Python and SQL Bible

Name: Python and SQL Bible
Author: Cuantum Technologies LLC
ISBN: 9781836206279

by Cuantum Technologies LLC

June 2024

Beginner to intermediate

519 pages

10h 30m

English

Packt Publishing

Read now

Unlock full access

Who we are
Our Philosophy:Our Expertise:
Introduction
Chapter 1: Python: An Introduction
1.1 Brief History of Python1.2 Benefits of Python1.2.1 Readability and Simplicity1.2.2 High-Level Language1.2.3 Extensive Libraries1.2.4 Cross-Platform Compatibility1.2.5 Dynamically Typed1.2.6 Support for Multiple Programming Paradigms1.2.7 Strong Community and Widespread Adoption1.2.8 Integration with Other Languages1.2.9 Versatility1.3 Python Applications1.3.1 Web Development1.3.2 Data Analysis and Data Visualization1.3.3 Machine Learning and Artificial Intelligence1.3.4 Game Development1.3.5 Automation and Scripting1.3.6 Cybersecurity1.3.7 Internet of Things (IoT)1.3.8 Robotics1.3.9 Bioinformatics and Computational Biology1.3.10 Education1.4 Setting up the Python Environment and Writing Your First Python Program1.4.1 Setting up Python Environment1.4.2 Your First Python Program
Chapter 1 Conclusion
Chapter 2: Python Building Blocks
2.1 Python Syntax and Semantics2.1.1 Python Syntax2.1.2 Python Semantics2.2 Variables and Data Types2.2.1 Integers2.2.2 Floating-Point Numbers2.2.3 Strings2.2.4 Booleans2.2.5 Lists2.2.6 Tuples2.2.7 Dictionaries2.2.8 Type Conversion2.2.9 Dynamic Typing2.2.10 Variable Scope2.3 Basic Operators2.3.1 Arithmetic Operators2.3.1 Comparison Operators2.3.2 Logical Operators2.3.3 Assignment Operators2.3.4 Bitwise Operators2.3.5 Membership Operators2.3.6 Identity Operators2.3.6 Operator Precedence2.4 Practice Exercises
Chapter 2 Conclusion
Chapter 3: Controlling the Flow
3.1 Control Structures in Python3.1.1 Conditional Statements (if, elif, else)3.1.2 Loop Structures (for, while)3.2 Error and Exception Handling3.2.1 Handling Exceptions with try and except3.2.2 The else and finally Clauses3.2.3 Raising Exceptions3.2.4 The assert Statement3.3 Understanding Iterables and Iterators3.3.1 Iterators in Python3.3.2 The for loop and Iterators3.3.3 Iterators and Built-in Types3.3.4 Python's itertools Module3.3.5 Python Generators3.4 Practice ExercisesExercise 1: Conditional StatementsExercise 2: LoopsExercise 3: Error and Exception HandlingExercise 4: Iterables and Iterators
Chapter 3 Conclusion
Chapter 4: Functions, Modules, and Packages
4.1 Function Definition and Call4.1.1 Function Definition4.1.2 Function Call4.1.3 Function Parameters4.1.4 Docstrings4.1.5 Local and Global Variables4.2 Scope of Variables4.2.1 Global Scope4.2.2 Local Scope4.2.3 Nonlocal Scope4.2.4 Built-In Scope4.2.5 Best Practices for Variable Scope4.3 Modules and Packages4.3.1 Modules in Python4.3.2 Packages in Python4.3.3 Python's import system4.4 Recursive Functions in Python4.4.1 Understanding Recursion4.4.2 Recursive Functions Must Have a Base Case4.4.3 The Call Stack and Recursion4.5 Practical ExercisesExercise 1: Writing and Calling a FunctionExercise 2: Understanding Variable ScopeExercise 3: Importing and Using a ModuleExercise 4: Recursive FunctionExercise 5: Error Handling
Chapter 4 Conclusion

Chapter 5: Deep Dive into Data Structures
5.1 Advanced Concepts on Lists, Tuples, Sets, and Dictionaries5.1.1 Advanced Concepts on Lists5.1.2 Advanced Concepts on Tuples5.1.3 Advanced Concepts on Sets5.1.4 Advanced Concepts on Dictionaries5.1.5 Combining Different Data Structures5.1.6 Immutable vs Mutable Data Structures5.1.7 Iterating over Data Structures5.1.8 Other Built-in Functions for Data Structures5.2 Implementing Data Structures (Stack, Queue, Linked List, etc.)5.2.1 Stack5.2.2 Queue5.2.3 Linked Lists5.2.4 Trees5.3 Built-in Data Structure Functions and Methods5.4 Python's Collections Module5.5 Mutability and Immutability5.6 Practical ExercisesExercise 1: Implementing a Stack
Exercise 2: Implementing a Queue
Exercise 3: Using List ComprehensionsExercise 4: Implementing a Linked List
Chapter 5 Conclusion
Chapter 6: Object-Oriented Programming in Python
6.1 Classes, Objects, and Inheritance6.2 Polymorphism and Encapsulation6.2.1 Polymorphism6.2.2 Encapsulation6.3 Python Special Functions6.4 Abstract Base Classes (ABCs) in Python6.4.1 ABCs with Built-in Types6.5 Operator Overloading6.6 Metaclasses in Python6.7 Practical ExercisesExercise 6.7.1: Class Definition and Object CreationExercise 6.7.2: Inheritance and PolymorphismExercise 6.7.3: Encapsulation
Chapter 6 Conclusion
Chapter 7: File I/O and Resource Management
7.1 File Operations7.1.1 Opening a file7.1.2 Exception handling during file operations7.1.3 The with statement for better resource management7.1.4 Working with Binary Files7.1.5 Serialization with pickle7.1.6 Working with Binary Files7.1.7 Serialization with pickle7.1.8 Handling File Paths7.1.9 The pathlib Module7.2 Context Managers7.3 Directories and Filesystems7.4 Working with Binary Data: The pickle and json modules7.5 Working with Network Connections: The socket Module7.6 Memory Management in Python7.6.1 Reference Counting7.6.2 Garbage Collection7.7 Practical ExercisesExercise 1Exercise 2Exercise 3
Chapter 7 Conclusion
Chapter 8: Exceptional Python
8.1 Error and Exception Handling8.1.1 Else Clause8.1.2 Finally Clause8.1.3 Custom Exceptions8.2 Defining and Raising Custom Exceptions8.2.1 Defining Custom Exceptions8.2.2 Adding More Functionality to Custom Exceptions8.2.3 Raising Custom Exceptions8.3 Good practices related to raising and handling exceptions8.4 Logging in Python8.5 Practical ExercisesExercise 1: Creating a custom exceptionExercise 2: Adding exception handlingExercise 3: LoggingExercise 4: Advanced logging
Chapter 8 Conclusion
Chapter 9: Python Standard Library
9.1 Overview of Python Standard Library9.1.1 Text Processing Services9.1.2 Binary Data Services9.1.3 Data Types9.1.4 Mathematical Modules9.1.5 File and Directory Access9.1.6 Functional Programming Modules9.1.7 Data Persistence9.1.8 Data Compression and Archiving9.1.9 File Formats9.2 Exploring Some Key Libraries9.2.1 numpy9.2.2 pandas9.2.3 matplotlib9.2.4 requests9.2.5 flask9.2.6 scipy9.2.7 scikit-learn9.2.8 beautifulsoup49.2.9 sqlalchemy9.2.10 pytorch and tensorflow9.3 Choosing the Right Libraries9.3.1 Suitability for Task9.3.2 Maturity and Stability9.3.3 Community and Support9.3.4 Documentation and Ease of Use9.3.5 Performance9.3.6 Community Support9.4 Practical ExercisesExercise 1: Exploring the Math LibraryExercise 2: Data Manipulation with PandasExercise 3: File Operations with os and shutil Libraries
Chapter 9 Conclusion
Chapter 10: Python for Scientific Computing and Data Analysis
10.1 Introduction to NumPy, SciPy, and Matplotlib10.1.1 Understanding NumPy Arrays10.1.2 Efficient Mathematical Operations with NumPy10.1.3 Linear Algebra with SciPy10.1.4 Data Visualization with Matplotlib10.2 Digging Deeper into NumPy10.2.1 Array slicing and indexing10.2.2 Array reshaping and resizing10.3 Working with SciPy10.3.1 Optimization with SciPy10.3.2 Statistics with SciPy10.4 Visualizing Data with Matplotlib10.4.1 Basic Plotting with Matplotlib10.4.2 Creating Subplots10.4.3 Plotting with Pandas10.5 Exploring Pandas for Data Analysis10.5.1 Creating a DataFrame10.5.2 Data Selection10.5.3 Data Manipulation10.5.4 Reading Data from Files10.6 Introduction to Scikit-Learn10.7 Introduction to Statsmodels10.8 Introduction to TensorFlow and PyTorch10.9 Practical ExercisesExercise 10.1Exercise 10.2Exercise 10.3Exercise 10.4
Chapter 10: Conclusion
Chapter 11: Testing in Python
11.1 Unit Testing with unittest11.1.1 setUp and tearDown11.1.2 Test Discovery11.1.3 Testing for Exceptions11.2 Mocking and Patching11.2.1 Mock and Side Effects11.2.2 PyTest11.3 Test-Driven Development11.4 Doctest11.5 Practical ExercisesExercise 1: Unit TestingExercise 2: Mocking and PatchingExercise 3: Test-Driven Development
Chapter 11 Conclusion
Chapter 12: Introduction to SQL
12.1 Brief History of SQL12.2 SQL Syntax12.2.1 Basic Query Structure12.2.2 SQL Keywords12.2.3 SQL Statements12.2.4 SQL Expressions12.3 SQL Data Types12.3.1 Numeric Types12.3.2 Date and Time Types12.3.3 String Types12.3.4 SQL Constraints12.4 SQL Operations12.4.1 Data Definition Language (DDL)12.4.2 Data Manipulation Language (DML)12.5 SQL Queries12.5.1 Filtering with the WHERE clause12.5.2 Sorting with the ORDER BY clause12.5.3 Grouping with the GROUP BY clause12.5.4 Joining Tables12.6 Practical ExercisesExercise 1Exercise 2Exercise 3Exercise 4Exercise 5Exercise 6Exercise 7
Chapter 12 Conclusion
Chapter 13: SQL Basics
13.1 Creating Databases and Tables13.2 Inserting Data into Tables13.3 Selecting Data from Tables13.4 Updating Data in Tables13.5 Deleting Data from Tables13.6 Filtering and Sorting Query Results13.7 NULL Values13.8 Practical ExercisesExercise 1: Creating Databases and TablesExercise 2: Inserting DataExercise 3: Updating and Deleting DataExercise 4: Querying DataExercise 5: Working with NULL
Chapter 13 Conclusion
Chapter 14: Deep Dive into SQL Queries
14.1 Advanced Select Queries14.1.1 The DISTINCT Keyword14.1.2 The ORDER BY Keyword14.1.3 The WHERE Clause14.1.4 The LIKE Operator14.1.5 The IN Operator14.1.6 The BETWEEN Operator14.2 Joining Multiple Tables14.2.1 LEFT JOIN and RIGHT JOIN14.2.2 FULL OUTER JOIN14.2.3 UNION and UNION ALL14.2.4 Subqueries14.3 Aggregate Functions14.4 Practical ExercisesExercise 1 - Advanced Select QueriesExercise 2 - Joining Multiple TablesExercise 3 - Aggregate Functions
Chapter 14 Conclusion
Chapter 15: Advanced SQL
15.1 Subqueries15.1.1 Scalar Subquery15.1.2 Correlated Subquery15.1.3 Common Table Expressions (CTEs)15.2 Stored Procedures15.2.1 Different Types of Stored Procedures15.3 Triggers15.3.1 Additional Details15.4 Practical ExercisesExercise 1: Working with SubqueriesExercise 2: Creating and Using Stored ProceduresExercise 3: Triggers
Chapter 15 Conclusion
Chapter 16: SQL for Database Administration
16.1 Creating, Altering, and Dropping Tables16.1.1 Creating Tables16.1.2 Altering Tables16.1.3 Dropping Tables16.2 Database Backups and Recovery16.2.1 Database Backups16.2.2 Database Recovery16.2.3 Point-In-Time Recovery (PITR)16.3 Security and Permission Management16.3.1 User Management16.3.2 Granting Permissions16.3.3 Revoking Permissions16.3.4 Deleting Users16.4 Practical ExercisesExercise 1: Creating, Altering, and Dropping TablesExercise 2: Database Backups and RecoveryExercise 3: Security and Permission Management
Chapter 16 Conclusion
Chapter 17: Python Meets SQL
17.1 Python's sqlite3 Module17.1.1 Inserting Data17.1.2 Fetching Data17.2 Python with MySQL17.3 Python with PostgreSQL17.4 Performing CRUD Operations17.4.1 Create Operation17.4.2 Read Operation17.4.3 Update Operation17.4.4 Delete Operation17.4.5 MySQL17.4.6 PostgreSQL17.5 Handling Transactions in Python17.6 Handling SQL Errors and Exceptions in Python17.7 Practical ExercisesExercise 17.7.1Exercise 17.7.2Exercise 17.7.3Exercise 17.7.4Exercise 17.7.5Exercise 17.7.6
Chapter 17 Conclusion
Chapter 18: Data Analysis with Python and SQL
18.1 Data Cleaning in Python and SQL18.2 Data Transformation in Python and SQL18.2.1 Data Transformation in SQL18.2.2 Data Transformation in Python18.3 Data Visualization in Python and SQL18.3.1 Data Visualization in SQL18.3.2 Data Visualization in Python18.4 Statistical Analysis in Python and SQL18.4.1 Statistical Analysis in SQL18.4.2 Statistical Analysis in Python18.5 Integrating Python and SQL for Data Analysis18.5.1 Querying SQL Database from Python18.5.2 Using pandas with SQL18.5.3 Using SQLAlchemy for Database Abstraction18.6 Practical ExercisesExercise 1: Data CleaningExercise 2: Data TransformationExercise 3: Querying SQL Database from Python
Chapter 18 Conclusion
Chapter 19: Advanced Database Operations with SQLAlchemy
19.1 SQLAlchemy: SQL Toolkit and ORM19.2 Connecting to Databases19.3 Understanding SQLAlchemy ORM19.4 CRUD Operations with SQLAlchemy ORM19.4.1 Creating Records19.4.2 Reading Records19.4.3 Updating Records19.4.4 Deleting Records19.5 Managing Relationships with SQLAlchemy ORM19.6 Querying with Joins in SQLAlchemy19.7 Transactions in SQLAlchemy19.8 Managing Relationships in SQLAlchemy19.9 SQLAlchemy SQL Expression Language19.10 Practical ExerciseExercise 19.1
Chapter 19 Conclusion
Appendix A: Python Interview Questions
Appendix B: SQL Interview Questions
Appendix C: Python Cheat Sheet
Basic Python SyntaxData StructuresList Comprehensions
Appendix D: SQL Cheat Sheet
SQL SyntaxCRUD Operations
References
Conclusion
Where to continue?
Know more about us

Content preview from Python and SQL Bible

Chapter 14: Deep Dive into SQL Queries

In the previous chapters, we learned about the fundamentals of SQL. We covered topics such as creating databases, tables, and inserting, updating, and deleting data. Additionally, we covered basic querying of data. However, while these concepts are essential, they only scratch the surface of what SQL can do.

In this chapter, we will delve deeper into SQL queries and learn how to perform more advanced data retrieval operations using complex SQL SELECT statements. By the end of this chapter, you will have a comprehensive understanding of how to construct and utilize these statements effectively.

We will cover several topics, including joining tables, grouping records, and filtering data. By learning these ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781836206279

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Python and SQL Bible

by Cuantum Technologies LLC

Chapter 14: Deep Dive into SQL Queries

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.