Back

SQL - Altering Relations

Last time we learned how to create a relational database schema and relations.  Now we will expand our knowledge about SQL.  Deleting objects of the SQL schema can be performed using the drop command.  For example, the following statement deletes the table test_table in its entirety (both the data - table entities, and the definition - relational schema):

drop table test_table;
Deleting the entire SQL schema is performed using the following statement:
drop schema UniSchema;
There are two options of the drop statement which can be specified: cascade and restrict.  The restrict option prevents the table from being deleted if the table is referenced, for example, by a foreign key of another table.  Unlike that, the cascade option deletes not only the specified table, but also the tables which reference the given table.  Options are specified at the end of the drop statement.

 

Existing definitions of SQL schema objects can be altered using the alter command.  For example, if we want to add the dateOfBirth attribute to the person table, we can do that by writing:

alter table person add dateOfBirth date;
As you can see, it is necessary to also state the type of the attribute we are adding.  An attribute can also be removed by using the alter and drop commands:
alter table person drop dateOfBirth [restrict/cascade];
Deletion of attributes can also be performed while specifying restrict or cascade option.

 

SQL - Basic Queries

As previously stated, a relational database can (in an informal and simplified manner) be viewed as a collection of tables.  One of the most important things a user (or administrator) expects from a database is a simple way of acquiring and manipulating table data, and representing it in a readable format.  This is achieved by writing and executing queries.
Queries are non-case sensitive (except in an array of characters), and multiple statements are separated with the ; sign.  The basic form of an SQL query is:

-- this is a comment

select A1, A2,...       -- A1, A2 etc. are attributes 
from r                  -- r is a relation (table)
where P;                -- P is a predicate
For example, if we want to display the names of all the classes in a department (from our UniDB example), we will execute the following query:
select name
from class;

-- we can also specify the distinct keyword to retrieve unique names:
select distinct name
from class;
If we want to retrieve only those attributes which satisfy a certain condition, we can also specify a predicate (condition):
select name
from class
where credits>5;
This was the basic and most simple form of an SQL query, and in the following articles we will discuss more complex queries.