Week 2: Advanced SQL

SQL Joins

This clause is used to combine records from two or more tables in a database, based on a common field between them other keywords are combined with the SELECT statement

INNER JOIN

Returns rows when there is at least one match in both tables.

OUTER JOIN

An extension of INNER JOIN. But it does not require each record in the two joined tables to have a matching record.

LEFT JOIN

Returns all rows from the left table, even if no matches exist in the right table.

RIGHT JOIN

Returns all rows from the right table, even if no matches exist in the left table.

FULL JOIN

Both tables are secondary (or optional), such that if rows are being matched in table A and table B, then all rows from table A are displayed even if there is no matching row in table B, and vice versa.

CROSS JOIN

It returns all possible combinations of rows from the two tables.

Subquery

A SELECT statement embedded within another SELECT statement.

Scalar Subquery

This subquery returns a single value.

Row Subquery

This subquery returns multiple columns, single row.

Table Subquery

This subquery returns multiple rows and columns.

Index

A structure used to improve search efficiency and avoid duplicate column values.

Creating Index Syntax

CREATE INDEX index_name ON table_name(column);

DROP INDEX

It is used to delete index.

View

A virtual table based on a SELECT query.

CREATE VIEW

Is a virtual table based on a SELECT query.

Create View Syntax

CREATE VIEW view_name AS SELECT query;

ALTER VIEW

It is the keyword used to modify a created view.

DROP VIEW

It is used to delete a view that was previously created.