Top Database Bundle: MySQL, PostgreSQL, SQLite3, SQL Server

Learn how to connect Python to MySQL, an open source, free database powering WordPress, and install the tools you need across Windows, Mac, and Linux.

Think of a database as a big spreadsheet: tables hold rows called records and columns enforce uniform data types, enabling add, update, remove, and retrieval across multiple tables.

Download and install python 3 or newer from python.org, ensure add python to path, so you can run python from anywhere; the video also introduces Sublime Text as an editor.

Install git bash on Windows to get a unix-like terminal for learning git. Download from git-scm.com, install with default vim, adjust path, and prepare for future MySQL work.

Install and configure MySQL and Workbench, including the server, Windows service, and default settings; create a root user with a password, verify connection, and prepare for Python integration.

Install the MySQL connector for Python with pip, try different variants, and set up a project directory in git bash to prepare for Python-MySQL connections.

Import the mysql.connector in Python, establish a connection to a local database using host, user, and password, and verify the connection by printing the connection object.

Explore MySQL Workbench to view, edit, and run queries on a local world database, using schemas and tables to inspect city and country data.

Create a MySQL database from a Python program by creating a cursor, executing create database, and looping through and printing available databases from the server.

Create a users table in the test database using sql create table, defining name, email, age, and an auto incrementing primary key user_id via a cursor.

Insert many records into the users table using Python by building a list (array) of tuples, executing the insert with placeholders, and committing to reveal auto-incremented IDs.

Learn to pull data from a table using a cursor to execute a select, fetch results, loop through rows, and print names in a readable report.

Format query results into readable text reports by building a string with placeholders, interpolating row data, and using tabs to align name, email, age, and id.

Explore the where clause to filter data using conditions like age greater than 30 or name equals 'John', combining with and or. Fetch and display matching users.

This lecture demonstrates using the like clause and wildcards to search for names, showing how the wildcard percent enables prefix and substring matching with examples like John, Tim, and Tina.

Update records in MySQL with an update statement, where clause, and cursor.execute, then commit changes. Use the user_id primary key to avoid unintended edits and ensure proper string quoting.

Identify how to limit query results with limit and offset, and sort with order by name in ascending or descending order.

Delete records using a simple sql delete statement with a where clause, targeting the user_id primary key and committing changes to make the removal permanent.

Ask questions anytime and get quick help from the instructor as you learn to set up Postgres locally with PG Admin on Windows, Linux, and Mac.

Launch pgAdmin from the Postgres installation, view the web browser dashboard, create a new database named my database, then explore schemas and tables.

Highlight basic select statements in PostgreSQL, retrieving all columns with * from public customers or selecting specific columns like name and email, and aliasing headers for reports.

Discover how the PostgreSQL where clause narrows query results using comparison operators such as =, !=, >, <, >=, <=, and like, with practical examples on a customers table.

Explore how PostgreSQL wildcards in the where clause use the percent sign with like. Learn starts with, ends with, and middle searches with examples like John Elder and Smith.

Explore how to combine conditions in PostgreSQL using and, or, and not within where clauses, including practical examples with email patterns and id checks.

Learn to insert data into PostgreSQL using insert into on public.customers, specifying name and email, with single-quoted values and verification via a select; primary keys are auto assigned.

Master updating data in PostgreSQL with the update command, using set and a where clause to target specific rows and avoid changing all records, guided by a unique ID.

Learn to use the PostgreSQL delete from table with a precise where clause on the id to remove a specific row, and observe how the serial sequence advances after deletion.

demonstrates how postgresql uses the limit clause as a substitute for select top, shows selecting the top N records with limit and offset, and notes that percentage limits don't work.

Explore the pgadmin menu to review databases, schemas, and table spaces, see how data is stored and migrated between servers, and learn how serial keys use sequences.

Connect tables with PostgreSQL foreign keys by linking the customer ages table to the customers table through the ID, and enforce cascade delete to remove related records.

Perform an inner join between the public customers table and the customer ages table on matching ids to display names and ages.

Explore left, right, and full outer joins and learn a where-clause trick to join public.customers and public.customer_ages by matching ids to return name and age with less code.

learn to back up and restore a PostgreSQL table by exporting to a csv file, saving it locally, and re-importing after recreating the table, with a note on database backups.

Back up the entire PostgreSQL database to a .sql file. Then drop and recreate the database and restore from the backup to recover the customers table.

Install python 3.7 from python.org, add python to path, so you can run it anywhere on Windows; prefer the 32-bit version for compatibility.

Install sublime text and the git bash terminal to set up your coding environment. Download, install, and launch these free tools on Windows or Mac.

Import sqlite3 in Python, connect to a database file, and let sqlite3 create it if needed. Use an in-memory database for temporary data that vanishes when the program ends.

Insert into the customers table a record with first name, last name, and email. Execute the cursor, commit, and close the connection, as shown with John.

Learn to insert many records at once by building a Python list of customers, using tuples with three question mark placeholders, and batch inserting with execute many.

Execute queries with a cursor to select all from the customers table, then fetchone, fetchmany, or fetchall, and print the results as a Python list for display.

Learn to fetch one or all records, access fields by index, and format results with loops, concatenation, and tabs in Python when working with SQL data.

Master filtering with the where clause to select specific customers, using exact matches, comparison operators, and like with wildcards to search by last name.

Learn to update records safely by targeting a single row with its primary key (row id), using set, where, and quotes, committing changes, and verifying with a select.

Use and and or to extend the where clause, searching for multiple conditions such as last_name like Bru% and row_id=3. Or see how or yields Mary and Wes.

Learn how to limit results in SQL queries using limit, including ordering by row id descending and placing limit at the end to fetch a subset of customers.

Execute the drop table command to remove the customers table from the database, commit the change, and verify by noting the no such table customers error.

Create a simple Python app by importing the database module, defining a show function, and querying the database to display all records, preparing for additional functions.

Create an add record function to insert a new customer into the database using a connection and cursor, placeholders for first name, last name, and email, then commit and close.

Learn to build an add many records function that uses execute many to insert into customers values. Create a two-record list and verify the bulk insert works.

Create a where clause function to look up emails by querying the customers table where email equals a provided value, then fetch and display the results.

Visualize a database as a collection of tables with rows and columns. Treat records as rows and fields as columns, using Excel as a familiar analogy.

Download and install Microsoft SQL Server 2016 on Windows 8 or newer using the 180-day evaluation; older versions like 2012, 2010, 2008 are available, plus install SQL Server Management Studio.

Launch Microsoft SQL Server Management Studio and connect with Windows authentication to explore the interface. Familiarize yourself with system databases and logins.

Learn how to add data to a table by entering records and fields, notice the red exclamation dot signaling unsaved changes, and save by moving to the next row.

Master writing select statements in sequel server management studio, selecting all or specific fields from the current customers table, and executing queries to view results.

Explore the where clause in SQL to filter data with comparison operators, like and between, applying conditions to select statements and other statements.

Master using and, or, and not in where clauses to filter by age and first name, with examples of equals and like conditions.

Explore how to sort query results with order by, choosing ascending or descending order by last name or age, and understand default ordering.

Learn how to use group by to aggregate data, count states, rename output with as, and order results by count in MySQL, PostgreSQL, SQLite, and SQL Server.

Explore the top clause in SQL by selecting top N or top percent of records. Order by age or other fields to rank results, with examples like top five customers.

Learn how to use select distinct to retrieve unique records, such as states, and see Illinois duplicates collapse into a single result; the union operator comes next.

Use union to combine multiple select statements from current and past customers, ensuring the same number of columns and data types, with a unified order by for both selects.

Explore common SQL functions such as data length, lower, upper, count, average, max, and sum, and learn how to apply mathematical operations and parameters in queries.

Master the update statement in sql by using set and where clauses to modify specific records, such as updating a customer's city and state.

Learn to delete records with delete from a table where a condition, such as in current customers, using a where clause and star to delete everything.

Master changing nullability with alter table: switch city to allow nulls or not null, noting existing data can block not null, requiring drop and re-add or column alteration.

Create a table by naming it, defining columns with data types, and applying not null or default null, then execute the command to add it.

Learn the difference between delete and drop, how delete from with a where clause removes data, and how drop table removes the entire table.

Link two tables with an inner join to pull matching records from current customers and customer orders using the ID key, returning first names, last names, and widget orders.

Create and drop indexes to speed up searches on large tables, such as indexing the email column in current_customers.

Unlock lifetime access to 60+ coding courses, books, and future updates for $49 with the Udemy coupon code during the next three days, and enjoy a 30-day money-back guarantee.