SQLite Fundamentals - Comprehensive SQL course on SQLite

Explore data definition language and data manipulation language by creating a table with a primary key, not null constraint, and text and integer columns, insert rows, and select records.

Learn to use the SQLite delete command to remove specific rows with a condition and to delete all rows, then drop the table with the drop command.

Learn how to use the alter table command in SQLite to rename tables and add new columns, while noting that data types and primary keys cannot be changed.

Learn to use the SQL update command to modify existing records in a table. Update designation for selected employees and adjust names for a specific id.

Learn how union and union all combine results from multiple selects, with union removing duplicates, union all including duplicates, columns must match, and union all offering faster performance.

Learn to use the substring function in SQL to extract string portions by starting position and length, and query names starting with a letter without the like operator.

Discover how the instr function in sql finds the first position of a substring and returns zero when not found, with examples on month and name columns.

Master the concatenate function to merge first and last names with a space, creating full names. Learn how upper and lower convert strings to uppercase or lowercase with practical examples.

Explore the trim functions in SQLite, including LTRIM and RTRIM, to remove specific characters or spaces from string ends; learn with examples like Hello World to polish strings.

Master the replace function in SQLite by replacing a string in the home_phone column, updating the table, and verifying changes with a select query.

Explore automatic arithmetic operators for addition, subtraction, multiplication, and division; examine comparison operators as equal to, greater than, less than; learn logical operators like and, or, between, is nil.

Understand the and operator in SQLite SQL queries, where all conditions must be true, illustrated by designation equals to manager, department id equals 202, and salary greater than six thousand.

Learn how the or operator returns rows when one condition is met, using designation and salary checks for manager or director and salary thresholds.

Apply the in operator in SQLite fundamentals to select employees by multiple salaries and exclude managers, using salary in (13000, 14000, 25000) and designation not equal to 'manager'.

This lecture teaches the between operator in SQLite, using numeric, text, and date ranges with inclusive bounds, and solves a puzzle selecting employees by salary, departments, and not managers.

Explore how the like operator searches patterns in a column using percent and underscore wildcards, with examples for starts with, ends with, and contains.

Explore sqlite's glob operator, a case-sensitive pattern search using * for zero or more characters and ? for a single character, and compare it to like.

Learn how the limit clause constrains the number of rows in a select, with optional offset and after applying order by for precise results.

Explore aggregate functions in SQLite, and use sum, average, max, min, and count on a salary table to compute total, highest, lowest, and average salaries.

Explore the group by statement and its use with aggregate functions to group repeating values in the salary table by designation, and see how sum totals arise.

Learn how the having clause filters grouped results after a group by, using aggregates like sum of salary by designation to target specific groups.

Apply aggregate functions to count employees with salary less than 10000 by designation, compute total salary by department, and analyze department and designation counts using group by and having.

Learn how inner join returns only matching rows by combining records from two tables to show employee names with department names using a shared department id.

Perform inner joins across imply, department, and location to show first name, last name, and department name for salaries between 10000 and 20000. Filter departments by regional groups Texas, California, and Chicago.

Practice inner join queries to count employees by department, using group by and having, with sales showing three employees.

Explains left, right, and full outer joins and how unmatched rows yield null values. Uses practical examples with implied and department tables to illustrate join conditions and results.

Learn how left outer joins display all departments, including those without employees, with unmatched rows showing null values; right and full outer joins are not supported in the Escalade database.

Explore self joins in sql by joining a table to itself, covering inner self joins, left and right outer self joins, and cross joins with a boss relationships example.

Explore the Cartesian product, or cross join, where every row of first table pairs with every row of second, illustrating how 3x3 yields 9 and its cost on large tables.

Learn how subqueries with select statements in SQLite execute inner queries first, compare results with outer queries, and apply operators for filtering by department, location, or salary.

Explore subquery examples in SQLite, comparing salaries to department 203 and identifying departments without employees using not in, with practical query steps and results.

Discover how row_number assigns a unique value to each row using order by (mandatory) and optional partition by in SQLite. Learn to display top salaries by designation and position.

Explore rank and dense_rank functions in SQL, comparing how duplicates affect ranking, applying over with order by salary descending, and noting the optional partition by clause.

Explore how rank and dense rank functions operate with the partition by clause to apply rankings within each designation, including how duplicates affect ranking.

Explore analytical functions rank and dense rank to find the third highest salary, demonstrate how duplicates affect ranking, and apply the over clause with order by salary desc.

Learn how to compute cumulative sum, moving average, and moving count in SQLite using window functions over sales data, with unbounded preceding and following clauses.

Explore calculating cumulative sums, moving averages, and moving counts in SQLite, using partition by store number and month, order by date, and rows unbounded preceding on sales data.

Understand triggers in a school like database, fired on insert, delete, or update. Compare before, after, and instead of triggers and their use in logs and action replacement.

Learn how to create after insert and before insert triggers in SQLite, logging inserted row IDs and timestamps to an audit_log, and verify triggers via the master table.

Create a before insert trigger on the sales table to validate projected and actual sales, displaying user defined messages for thresholds above 20000 and 18000 respectively.

Demonstrate implementing an update trigger in SQLite that logs changes to an ordered_log table when old_department_name differs from new_department_name, recording department_id and operation.

Define an audit log and a delete trigger on the department table to automatically capture deletions using the old record, logging department id, name, operation, and time in GMT.

Learn how to use the sql case statement to categorize designations into high, medium, or low salary by evaluating designation values in sqlite.

Learn how the coalesce function returns the first non expression among its arguments to pick the best contact detail (business, cell, or home phone) in a contact table.

Create and use views in SQL to save the results of complex queries, demonstrated with a join example, and manage views by selecting from or dropping them.

Understand how the NullIf() function compares two parameters and returns null when equal. Observe how not equal cases return the first parameter in examples using hello and world.

Create indexes to speed data retrieval in a table, explain single and multi-column indexes, and show how an index avoids full table scans using a salary example.

Create single and multi-column indexes on a worker table in SQLite to speed queries and avoid full table scans, with examples on salary, first name, and last name.