SQL - Basic Queries

In the last article we started learning about some basic ways to write SQL queries.  We'll continue with that now and also start introducing more complex concepts.  We have already written simple queries with the where clause and the following are some other ways in which a condition can be specified:

select ID 
from teacher
where Paycheck >= 2000 and Paycheck <= 5000;
We can see that multiple conditions can be specified in a where clause using logical operators like and, or etc.  A simpler way of writing the previous query would be using the between operator:
select ID
from teacher
where Paycheck between 2000 and 5000;
Another useful feature which can be used when writing SQL queries is the existence of character array comparation operators, which are based on the standard lexicographic character order, just like in standard programming languages (for example, 'abcd' is less than 'b').  For comparing character arrays, the like operator is often used, and it enables comparing a part of the char array with certain parts of a pattern.  For specifying the pattern, symbols '%' and '_' are used.  The '%' symbol corresponds to any arrays of (zero or more) characters, and the '_' symbol corresponds to a single, arbitrary character.  So, let's use the like operator to obtain all names starting with 'Ma':
select Name
from person
where Name like 'Ma%';
The following query selects all subjects which have '1' as a third character in their subject code:
select *
from subject
where SubjectID like '_ _1%';


Sorting Query Results

For displaying the results of a query in a certain order, we use the order by clause.  For example:

select Name, City
from person
order by Name [desc];
The desc modifier is optional, and you use it if you want the data to be sorted in descending order.  The default order is ascending.


Set Operations

SQL supports set operations - union, intersect and except, which are executed in a similar way to the corresponding relational algebra operations.  All of the three operations are binary.  The operands are relations which have to be compatible, i.e. they have to have the same number of attribute, and the the corresponding attributes have to have the same domain. Unlike these operations in relational algebra, SQL set operation results can contain duplicates.  For example, to obtain a list of IDs of teachers who teach at the Computer Science department and/or the Electronics department, we can specify the following query:

(select TeacherID
from teaches
where Department = 'Computer Science')


(select TeacherID
from teaches
where Department = 'Electronics');
The other set operations work in the same way.