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.
