Database Systems with SQL - Full Course - Database Systems - Relational Algebra and Query Optimization

Relational algebra is the theoretical foundation of SQL. Relational algebra has 9 operators, which take one or two relations as inputs and produce a new relation as a result. The select operation selects table rows based on some logical expression The project operation selects table columns from a table. The product operation combines two tables into one result The join operation is a product followed by a select operation The union operation combines all rows of 2 tables into a single table (assuming the tables are compatible) The Intersect operations operates on two tables and returns only rows that appear in both tables The difference operation removes all rows from a table that appear in a second table. The rename operation specifies new table and column names for a table The aggregate operation applies aggregate functions like SUM(), MIN(), MAX(), or AVG() Relational algebra expressions are considered equivalent if the expressions give the same results when operating on the same tables. Query optimization is a feature of many relational database management systems and other databases such as graph databases. The query optimizer converts a SQL query into a query execution plan, which is a sequence of low-level database actions. This works similarly to a programming language compiler, which converts high-level code into machine language or bytecode. The query optimizer converts queries into relational algebra expressions, then generates alternate or equivalent expressions. Then it figures out the cost, or processing time, of each operation for each expression. After it figures out which expression is the most optimal, it converts the optimal expression into a query execution plan. Subscribe to Appficial for more programming videos coming soon. Also, don't forget to click LIKE and comment on the video if it helped you out!