4. Let's Learn About SQL!

Seite 1

4. Let's Learn About SQL!

Searching for data using a SELECT statement

If specifies which column from which table (FROM) and matching which conditions (WHERE)

Creating conditions

Comparison operators:

Local operators:

Partterns: when you dont know exactly what to search for you can also use pattern matching in conditions by using wildcard characters.

Searches:

BETWEEN ------- AND---------; specifies a search range.

IS NULL; earches for a null.

Seite 2

Aggregate functions

Your can use these functions t aggregate information such as maximum and minimum values number of items and sum.

Aggregating data by grouping: to group data combine the aggregate function and the GROUP BY phrase. Do not specify a condition in the WHERE phrase, but use a HAVING phrase instead.

Seaching for data

Using a subquery: you can embed one query in another query.

Using a correlated subquery: such a subquery may refer to data from the outer query.

Correlation: because the subqudery refers to data from the outer query the subquery is not independent of the outer query. In the correlated subquery the query outside is implemented first.

Joining tables

Joining tables by combining columns with the same names is called an equi join. For an equi join, rows with the same value are designated as join conditions for joining tables. Joining columns with the same name into one is called a natural join.

The join method only rows having a common value like equi join are selected is called inner join. The join method that keeps all rows of one table and specifies a null for rows not included in another table is called an outer.

Creating a table

The statement syntax inside a CREATE TABLE statement often depends on the particular kind of database you use.

Inseting, updating or deleting rows: you can use INSERT INTO, UPDATE AND DELETE, to insert update or delete data from a table created by the CREATE TABLE.

Creating a view: you can also create a virtual table that exists only when it is viewed by a user.

Seite 3