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.

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.

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.

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.

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.

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.

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.

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.

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 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.

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.

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.

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 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.

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.

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.

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.