Learn SQL databases in a weekend

Explore the fundamental concepts of sql databases, including architecture, the logical components for data access and modification, and how to access Eskil database from programming languages and web pages.

Learn how to set up a database connection in your application, including hostname, port, username, password, and database name, plus the connect pattern and default users.

Learn how to choose and install a database management system for SQL work, compare open source and proprietary options, assess popularity, infrastructure fit, platform support, and key features.

Discover why MySQL is chosen for this course, noting its popularity, free and open source status, and cross-platform availability, with installation covered in upcoming videos.

Install MySQL by following official instructions; set up the database server and client, install language drivers, and optionally add a database administration tool, with Windows steps and Linux/macOS links.

Install the MySQL community server on Windows and choose server only with port 3306. Start the Windows service, set a root password, and test the connection.

Learn to use database administration and exploration tools with a graphical interface to view databases, tables, structures, and data, including insert and delete operations, using DB Virtue.

Install DBeaver on Windows, choose the community edition with Java, and complete the setup to explore databases, write queries, and create tables for sensor data.

Open DBeaver from the desktop or search, and create a sample database with automatic driver installation. Inspect the sample database's tables and columns and view an ER diagram.

Open and configure the MySchool database by creating a new MySQL connection, verifying host, port, and credentials, installing the driver, and debugging the public key retrieval issue.

Copy the error message, search online with precise keywords, and consult Stack Overflow for solutions; then adjust the connection string and key retrieval parameter to fix the database connection.

Run sql queries in the DBeaver Beaver editor and see results as tables, reinforcing learning by writing queries without programming.

Create a database by sending a create database statement terminated with a semicolon, then refresh the view or run the script in a sql editor; defaults use utf8.

Define table columns by name and type, noting types vary by database system. Explore common types like integers, decimals, date/time, strings, and binaries, and use them for arithmetic.

Explore storing pictures in a SQL database with a long blob column, separate tables, or base64 text, and compare file system or cloud storage with filenames or URLs.

Learn how a primary key serves as a unique identifier for records, from single integer ids to composite keys like author and title, with auto increment handling the ids.

Imagine managing a retail chain and designing a database to store stores, products, and employees as the starting point for future changes.

Learn how to create a table using the create table syntax, define id, name, and address, apply a primary key constraint, and auto increment the id for unique records.

Learn how to escape reserved keywords with backticks to safely name tables or columns like order, avoiding syntax errors and enabling flexible database design.

Learn to create a database and a store table in sql, define id as auto increment primary key with name and address, and use database.table syntax.

Practice creating a SQL table named employee with columns for employee number, first name, last name, and height, using the described column types.

Create an employee table with an auto-incrementing integer employee number as the primary key, and fields for first name, last name, and height stored as decimal(3,2) in meters.

Alter table lets you modify a table after creation by listing actions (add, drop, rename, change) including primary keys, separated by commas and ending with a semicolon.

Practice a small alter table exercise by modifying the employee table to add a birth date column, and prepare to see the instructor demonstrate it in the next video.

Learn to alter the employee table by adding a birth date column of type date using SQL, and validate changes via refresh and persist in the database interface.

Learn how to drop a table by executing the drop table statement, remove the supplier table in practice, and refresh the view to confirm deletion in any SQL tool.

Export a database to a file and import it back into the same or a new database to back up, move between servers, or initialize data with SQL statements.

Learn to create a database dump (export) with graphical tools and the command line, selecting tables and export options. The dump file includes drop and create table statements and comments.

Drop the employee table, then restore the database by importing the provided Eskil dump from lecture resources to recover the original structure.

Learn to import a database dump to restore the employee table, including dropping and recreating it, executing inserts, and using restore database to clone the database.

Insert data into tables using the insert into statement to add rows to the employee table, with case-insensitive keywords, specifying columns and values in quotes, ending with a semicolon.

Master the insert statement syntax by using insert into, specify table and column names, handle defaults and auto generated ids, and insert multiple rows with values in parentheses.

Master inserting data into the store table in a DB2 database, including using insert statements for name and address, enabling auto-generated IDs, and refreshing the view to see results.

Insert two employees into the employees table, including Charlie Young at 1.85 meters and Other Lovelace at 1.77 meters, to practice SQL insert operations.

Insert two people into the employee table using first_name, last_name, and height, then verify that two new rows appear.

Learn how default values and auto incrementing primary keys simplify insert statements. Create a product table with id, name, and quantity, where quantity defaults to zero if not provided.

Create a product table with id as an auto incrementing primary key, and name as a string and quantity as an integer defaulting to zero.

Insert into the product table using default values and auto generated IDs, either by specifying name and quantity or omitting IDs.

Learn how default values can use constants, expressions, or depend on other columns, and explore built-in functions like current date and interval across different database vendors.

Extend the product table by adding a new column named added with a default today's date, then insert a couple of products and verify the added column shows the date.

Add the added date column to the product table with a current date default using alter table, then insert two rows to see auto generated ids and the default date.

Learn to query data from a single table using the select statement, understand its parts and the importance of order, and know that queries return a table.

Learn to fetch all data from a single table using a select statement by writing select star from table, as shown with the product table example.

Learn how to select specific columns in SQL by using projections instead of select asterisk, returning only name and quantity from the product table.

Practice using expressions in select statements to convert heights from meters to feet and apply arithmetic on single or multiple columns, including plus, minus, multiplication, and division.

Learn to rename query result columns using aliases with as. Rename expressions like height divided by 0.3 to height in feet, affecting only the result set, not the table.

Explore using built-in functions in select statements, including round, upper, and concat, with aliases and safe naming, and learn how to locate function documentation in SQL.

Learn how the where clause filters rows by evaluating a condition for each row and returns only those that satisfy it, illustrated with employee and product table examples.

Learn how to combine conditions with logical operators using and, or, and not in SQL. Explore practical examples—range filters, or-conditions, and complex first-name and height queries—highlighting potential surprises.

Explore how operator precedence shapes SQL queries, with arithmetic, comparison, and logical operators, and learn to enforce the intended order using parentheses.

Learn substring search in sql using the like operator with the % wildcard to pattern-match names. Discover case-insensitive matching that finds starts-with and contains patterns, returning Chuck, Charlie, and Jack.

Learn how to order query results with ORDER BY, use multiple columns for tie-breakers, and choose ascending or descending directions, illustrated with first and last name examples.

Learn how to limit the number of rows in a SQL query using the limit clause, with examples selecting John and ordering by last name in descending order.

Learn to skip rows using an offset with the limit clause to return a subset of results, such as skipping the first row and returning the next two.

Explore aggregation in SQL databases by describing an entire table or grouped rows with a single number, using functions like count, min, max, average, and sum on query results.

Explore common aggregate functions in SQL, including count, min, max, avg, and sum, and learn how to apply them to whole columns and grouped data with practical queries.

Group data by chosen columns and apply aggregates to each group to produce counts and sums. Group by single or multiple fields, and order results by category or first name.

Apply sql to calculate product statistics by selecting the count and total weight of products with quantity below 100, then group results by category in this exercise.

Learn to write a query that selects count and total weight for products with quantity less than 100, grouped by category.

Learn how the having clause filters grouped results after aggregation, using group by and aggregate functions to keep only non-empty categories, as in the example.

Learn how to filter SQL query results with where for row-level conditions and having for group-level filters after grouping and aggregation.

Query data from your tables using the select statement, specifying tables and columns, and filter with where; group by and having, then order and limit the final result.

Explore how null values represent unknown data in SQL databases, and learn to store, insert, and test these values in tables, expressions, and aggregations.

Explore how null values are allowed by default and how to enforce not null with create table and alter table, including defaults and handling empty strings.

Learn to insert null values in sql by using insert into statements for two products with unknown quantity and category, where weight is 0.01.

Learn to query null values in SQL using is null, understand why equality to null fails, and use is not null to filter non-null records.

Learn how null values influence expressions and comparisons in SQL, where unknowns yield null in arithmetic and cannot be compared, leaving only null or not null checks.

Explore how null values affect aggregation in sql, learning how averages and sums treat unknowns, the difference between count(*) and count(column), and grouping by nulls.

Define null as an unknown value, explain it cannot be compared or used in arithmetic, and apply is null or is not null checks, noting aggregation ignores nulls.

Learn how to use update and delete statements to modify or remove data, while noting that Eskil does not save old data by default, so changes are permanent.

Use the delete statement to remove data by specifying the table and a where condition. Deleting uses the same syntax as select, affecting all rows that match the condition.

Practice delete statements by removing all products with no weight, using a proper delete statement. Consider exporting data, backing up the database, and restoring from backup in the next video.

Practice solving a SQL exercise by writing a select statement to identify products with no quantity, then delete those matched rows and refresh the products table.

Execute update statements to modify specific rows in a table using update, set, and where, updating one or more columns such as quantity and category with order by and limit.

Learn how to refer to old values in update statements to adjust per-row data, such as increasing each product quantity by one using set quantity = quantity + 1.

Practice updating data by locating employees named John and increasing their height by two centimeters, after first selecting the necessary data before applying the update.

Demonstrate querying and updating an employees table. Select John, increase height by 0.02, verify with a refresh, and restore original data from the attached file.

Learn to update and delete data by mastering where conditions and optional order and limit clauses, making update or delete statements like delete from table or update table set values.