Notes on SQL

Notes on SQL

Structured Query Language allows us to work with relational databases. SQL can be used to do a wide variety of tasks:

Create a table.
Delete the contents of a table.
Delete the table itself.
Add data to the table.
Delete a specific row from a table.
Delete a specific column from a table.
Search one or more tables for information. (This is called a "query".)

The most common uses of SQL are queries. The information returned by a qurey may be a single number or a string, or it may be a table which is created for us (a temporary variable).

Other kinds of SQL statements may not return any data at all.

Different database products may have slightly different formulations of SQL.

Many database systems provide an interactive interface. A user types a query and the result is delivered on-screen (standard input and output). It is also quite possible to interact with a database system in a program.

Here are some kinds of SQL statements:

Get all the data from a table:
```
     Select * from TableName
```
Delete an entire table:
```
     Drop Table TableName
```
As this is not a query, it will not return data.
Clear a table so it is empty but still exists:
```
     Delete from TableName
```
The table will still exist (column structure) but it will have no rows. This may return the number of rows affected.
Remove a column from a table:
```
     Alter Table TableName Drop Column ColumnName
```
Some RDBSes do not allow us to delete a column. In any case, we cannot delete the key field.
Create a new table:
```
     Create Table TableName
     (column_name1 data_type(size) constraint_name,
      column_name2 data_type(size) constraint_name,
      column_name3 data_type(size) constraint_name,
      ...,
      Primary Key(one or more column names)
      ....)
```
Here the constraint is optional and may have values such as (partial list):
- Not Null (cannot have the value Null)
- Unique (no two rows may have the same value in this column)
- Primary Key (this is primary key for this table, implying Not Null and Unique)
- Foreign Key (this is a primary key in some other table)
This also will not return data.
Add data to a table:
```
Insert Into TableName (ColumnName1, ...) Values(Value1, ...)
```
This will add a new row to the table and will not return data.
Get just some columns from a table:
```
Select ColumnName1, ... from TableName
```

There are various other SQL statements as well, and the "Select" statement has many options.

Example:

Suppose we have a relational database and we want to obtain some information. We need to know the names of the tables and the fields in the tables.

Suppose our table are storing data about movies and their makers.

A (very simple) typical query might be something like this:

     Select Film_Title
      From Director_Film_Table
      Where Director_Last_Name equals 'Hitchcock'

This should give us a list of film titles as a table with multiple rows and one column.

For this to make sense, we need to have a table called "Director_Film_Table" containing fields called "Film_Title" and "Director_Last_Name". That might be all that is in that table, or it might contain much more.

If we used "Select *" instead of "Select Title", we would be extracting whole rows of the table, not just one field.

There could be more than one table involved:

     Select Director_Film_Table.Film_Title, Film_Year
        From Director_Film_Table,
              Film_Table
        Where Director_Last_Name equals 'Hitchcock'
              and Director_Film_Table.Film_Title = Film_Table.Film_Title;

Here we have two tables, "Director_Film_Table" and "Film_Table", and Film_Table contains (at least) fields called "Film_Title" and "Film_Year".

The result of this query is itself a table (a temporary variable). Each row contains two items, a film title and a year.