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 mysql community server and mysql workbench on Windows using the full installer; use python for tasks in this course.

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.

Learn how to insert a single record into a table using an insert into statement with placeholders, a commit, and an auto-incremented id, illustrated on a users table.

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.

Explore how databases define data types—from text and numbers to date types—with MySQL examples like text, blob, int, float, and time stamp.

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.

Combine and and or in a select statement to filter users by eye in the name and age, noting that and requires all conditions true while or returns any condition.

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.

Back up a table by exporting to CSV or JSON, then drop the table in SQL (with drop table if exists) via Python.

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.

Install Postgres and pgAdmin by downloading the latest Postgres installer for Windows, selecting the pgAdmin options, and configuring the default data directory, port 5432, and the Postgres superuser during setup.

Explore what a database is by comparing it to a spreadsheet, then learn how tables, rows, columns, and data types organize information using sql.

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

Create a customers table with a primary key id using big serial, add name and email as character varying fields, enforce not null, then save, view, and insert data.

Learn how to add data to a table using manual entry and sql insert statements, including import/export workflows, using the query tool, committing with save, and handling insert errors.

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.

Sort query results in PostgreSQL using order by on a chosen column, choosing ASC or DESC; ascending is default, and applying it to id or name reorders the customers table.

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 how to delete a whole table in PostgreSQL by using drop, distinguish it from deleting rows, and see how a dropped table vanishes from the database view.

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.

John Elder introduces the top database bundle course, outlines topics and required tools—Python, a text editor, a command prompt—and Windows, Mac, and Linux compatibility; explains how to contact the instructor.

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.

Learn to create a database table with a cursor, define columns such as first_name, last_name, and email using text data types, and commit and close the connection.

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.

discover how a primary key provides a unique id for each record, see how row id exposes it in queries, and learn how auto-increment maintains unique ids across inserts.

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.

Delete records from a table using a where clause, removing the row id six from the customers table. Understand that numeric ids are not quoted and deletions are permanent.

Explore how to sort query results using order by, switch between ascending and descending, and order by fields like row id and last name to customize data output.

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.

Implement a delete record function that removes a customer by id using a placeholder, and resolve a type issue by passing the id as a string so the delete works.

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.

Learn the fundamentals of databases and the sequel programming language using Microsoft sequel server 2016, with free tools and a free evaluation trial for beginners.

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.

Define data types in databases, ensuring each column holds the same type for all records, and note that types vary by database with varchar often used.

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.

download and install ssms to manage sql server; run the installer, accept defaults, and let required dot net and visual studio components install in the background.

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

Explore the system databases in a SQL server, including master, model, msdb, and tempdb, and learn their basic roles from login info to templates and temporary query storage.

Create a database named Acme, then design a customer table with columns for first name, last name, email, city, state, zip code, and age, saving changes.

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.

Learn SQL, the structured query language for databases, and practice querying, inserting, updating, and deleting data using Microsoft SQL Server 2016 and T-SQL.

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.

Explains how the having clause works with group by to filter grouped results using aggregates, when where cannot, and how to order by state in ascending or descending.

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.

Learn how to insert data into a database using the insert into statement, matching columns to values, and handling quoted strings, numbers, and nulls.

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.

alter a table to add, modify, or drop columns, change data types, and manage nulls, using SQL or design view.

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.

learn how to create a customer orders table with a primary key id, enforce not null and uniqueness, and use this key to cross-reference and enable joins across tables.

Add an id primary key to the current customers table. Link it to the orders table and split data into separate customers and orders tables.

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.

Explore left, right, and full outer joins and see how their behavior differs from inner joins by showing or hiding records based on the left or right table.

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.