This chapter contains the notes for eleventh week. This is mainly devoted to some applications of SQL. There are various sample applications in this notes to explain and illustrate how SQL works. The samples given here can be tested on any system which has some version of SQL although the Linux systems including SQL are considered by default. You can also use interface at the URL http://www.be.itu.edu.tr/beders. by clicking on SQL anchor. It is possible to run many types of SQL applications and see the output through this interface.

Queries

     Consider table named STUDENTS where students' information is given. A table, as you know, is composed of rows and columns. The columns of the STUDENTS table contain the informations about the registration number, name, surname, faculty, and department of the students such that each column specifies one of these properties while each row corresponds to a single student if each student is assumed not to be registered to more than one programs at the same time. If it is possible to make more than one registrations to the distinct programs then each registration will define a distinct registration information because at least the program name will be different in the data given by the table.

     The rows of this table are called records. One can instruct the database management system to retrieve the records from this table by using SQL language. This action is called query. A sample SQL statement which makes this query can be given as follows.

SELECT * FROM STUDENTS

This produces the following table.

Table 1. The STUDENTS table.

     The appearance of the table in the display may be different depending on the interface between the postgreSQL and the display manager. However, as a database standard, numerical values are right justified while all the strings mostly composed of alphabetical characters are left justified. Here we assumed to work through a GUI (Graphical User Interface) which produces this format. On the other hand the names and numbers are completely fictitious unless some of them fit to the real correspondents by coincidence.

     As you know SQL is an acronym for the statement Structured Query Lnguage. However SQL does a lot of things which are not included in the meaning of the term Query. It creates, deletes, modifies, joins data and more. That is, the term Query does not reflect what SQL can do. But it is traditional name so we use it.

     The data in a database is case sensitive while the SQL commands are case insensitive. This means that you can write SQL commands either in lowercase or uppercase but you must obey the case of string characters in a term related to the data in a database.

     The spacing is not important. That is, you can put spaces between the words as much as you want and you can give a command in a single or more than one lines at the SQL prompt. However it is better to get a comfort of easily following what the action to be done is.

     Each SQL query must terminate with a semicolon because a semicolon tells to the command--line SQL program that the given query is complete.

     Now we can return to the SELECT command to proceed further. This command is one of the keywords which take important roles in SQL syntax and hence it is preferred to capitalize this command although nothing is wrong if it is given in lowercase letters.

     SELECT can not be used alone. It needs some parameters or other keywords. Therefore if you just enter SELECT; at the sql prompt it will fail to work and the SQL program will complain by announcing a missing expression.  Under the simplest conditions the column name of the table under consideration must be specified after SELECT.  However this is not sufficient because the table name has not been specified. This specification can be done by using the keyword FROMwhich must be followed by the name of the table under consideration.

     Now it is time to clarify the distinction between three important items under SQL: keyword, clause, and statement. The keyword refer to an individual SQL element like

     SELECT and FROM. A clause is a part of an SQL statement. For example, SELECT column1, column2,... is a clause. A statement is a combination of SQL clauses. For example, you can combine a SELECT and FORM clause to write an SQL statement.

     Now we can apply the following query command to the STUDENTS table as follows.

select * from students;


This will produce the following output through the interface we use here.

    In this output all columns except the first one are left justified because they are alphabetic character based data whereas the first column contains numerical data.

    The asterisk * in select * is a wild card. It tells database to display the contents of all columns of the table whose name is specified in the FROM clause. The database determines the order of the columns from left to right in the output.

    In some implementations of SQL, the semicolon at the end of the statement is necessary because it tells the interpreter that you are finished writing the query. On the other hand, some implementations of SQL do not use the semicolon as a terminator. For example, Microsoft Query and Borland's ISQL do not require a terminator. The query is typed in an edit box and executed when you push a button. So pushbutton event takes the role of the semicolon in these implementations.

    The preceding example of an SQL statement have used the wild character * to select all columns from a table. The order of the appearances of the columns in the output has been determined by the database. However it is possible to specify the order of the columns. For example we can give the following command

select faculty, program, register_no, name, surname from students;

to obtain the following display.

Similary, the entering the following command

select register_no, faculty, program, name, surname from students;

results in the following display.

    As you can notice easily, the order of the column names listed in the SQL query is the order in which they will appear in the output. The column names are separated by commas and there must be a space between the final column name and the subsequent clause which is from in this case.

You could write the above SQL statement as follows.

select register_no, faculty, program, name, surname from students;


    where the from clause has been carried over to the second line. This convention is a matter of personal favor and has no effect on the appearence of the output.

   It is also possible to select some columns in the display without showing the remaining ones. For this purpose we can give the following command as an example.

select name, surname from students;

This returns the following display.

    All the above examples use the same database, that is, the table STUDENTS. You could, of course, get data from other databases. All you have to do for this purpose is to change the name given in the from clause.

select faculty from students;

    As we can notice the faculty, Electronics, is repeated. However it is possible to get rid of the repetitions in the display. For example the following example removes repetitions in the display.

select distinct faculty from students;

The resulting display is as below.

    Therefore the key distinct is used for the removal of the multiplicated data. There is also another key, all, which enables the SQL interpreter to show all repetitions. However, the command, select all, has never been encountered because select and select all do the same thing.
To grasp this fact you can try select all faculty from students;

and obtain the following display.

    SQL employs the expressions. An expression is somethingwhich returns value. The types of the expressions may vary covering the data types like String, Numeric, and Boolean. In SQL terminology, anything following a clause like SELECT or FROM is an expression. In the above samples, for example, faculty is an expression. It returns the value contained in the faculty column.

    The conditions of SQL are similar to ones in the other programming languages. For example NAME = 'BROWN' is a condition which is in fact a Boolean expression. NAME = 'BROWN' will be either TRUE or FALSE where NAME can be considered as a parameter or variable while 'BROWN' is the value of this parameter or the variable. The symbol = is a comparison operator which compares the left hand side variable with the right hand side value. If the comparison fails then the result of the Boolean expression NAME = 'BROWN' is FALSE otherwise TRUE. The comparison operators are not limited to =. It is also possible to make comparisons via >, < operators.

     SQL has some clauses which use conditions. Amongst them we can emphasize on the WHERE clause. WHERE keyword simply causes your queries to be more selective. You could display only all records in the selected tables without using the WHERE clause. To exemplify this, let us consider a table, COURSES, and display it by giving the following command.

select * from courses

which creates the following display.

    Now we can select some rows which satisfy a condition from this table by using where keyword. For example, if we want to select the rows which contain data for the course whose code is BIL103 then we can use the following command

select * from courses where CourseCode = 'BIL103';

which creates the following output.

    where the variable and its value are given by obeying the case sensitivity.

    If we would want to display the course sessions given by Metin Demiralp then we would give the command

select * from courses where Instructor = 'Metin Demiralp'; which creates the following output.

    Here we used always english characters. The utilization of the turkish characters may be or may not be possible depending on the implementation of the SQL you use. Hence, to get rid of the possibly non--standard applications and cases we attempt to use only the standard ascii characters. The SQL uses operators also. Operators are the elements which are used inside an expression to articulate how you want specified conditions to retrieve data. Operators can be categorized into six groups: arithmetic, comparison, character, logical, set, and miscellaneous.

    The arithmetic operators are plus (+), minus (-), divide (/), multiply (*), and modulo (\). The first four are traditional elementary arithmetic operators. The modulo operator returns the integer remainder of a division. We can explain this by giving the following examples.

7 / 2 = 1
8 / 2 = 0
8 / 3 = 2
9 / 3 = 0

    The modulo operator does work only with integer data type.

    If several of these arithmetic operators are placed in an expression without any parentheses, the operators are resolved with this order: multiplication, division, modulo, addition, and subtraction. For example, the expression

3 * 7 + 12/4

equals

21 + 3 = 24

However, the expression

3 * (7 + 12) / 4

equals

3 * 19 / 4 = 14


   Therefore you have to watch where you put parentheses in an expression. Sometimes the expression does exactly what you instruct it to do, rather than what you want it to do.

    Now we deal with the multiplication operator in the queries. Let us start by creating a prices list in the database table PRICES whose display through the command select * from prices is given below.

If we give the command

select item, price, price*1.2 from prices; then we obtain the following display.

    Here the term *1.2 multiplies each price by 1.2. The display contains an extra column which is somehow virtual because the original PRICES table has not been changed after this query. We can verify this by giving the command

select * from prices;

This returns the below display

   which means that the original table is protected from this arithmetic procedure.

    The column header Price*1.2 may not be so attractive even it may be resistive. However it is possible to change it by another header as follows.

select item, price (price*1.2) retail from prices; which gives the following output.

     This is one specific application of a more general feature of SQL. If any column name is followed by another name with a blank space separator the column name is changed by that another name. Therefore all column names can be changed in the display as we wish. For example,

select item product, price, price*1.2
retail from prices; creates the following display. 

     Some implementation of SQL use a different syntax. There must be an = symbol instead of blank space between the column name and the alias in these implementations. So you have to be sure about the syntax of your implementation.

    The plus, minus, division, and modulo operators can be used  in the same way. To give some examples about them we can use the table SOMENUMBERS which can be displayed by the command select * from somenumbers This command gives the following output display

    The command select x1, x2, x1+x2 from somenumbers; produces the following display

    The command elect x1, x2, x1-x2 from somenumbers; produces the following display

    The command select x1, x2, x1/x2 from somenumbers; produces the following display

      The comparison operators of SQL compare expressions and return one of three values: TRUE, FALSE, or Unknown.To explain the Unknown value we need to know a little about the concept of NULL. NULL is the absence of data in a field in terms of database. It does not mean a column has a blank space or a zero in it. A zero or a blank is a value. NULL means nothing is in that field. If you make a comparison like Field = 5 and the only value for Field is NULL, the comparison will come back Unknown Unknown is an uncomfortable condition, hence SQL can change Unknown to FALSE and provide a special operator, IS NULL, to test for a NULL condition. Let us consider a new version of the PRICES table. Assume that we displayed it by using the command select * from prices which gives the following output.

     we could display the row where price is null (not given) by giving the command select * from prices where price is null;. This would produce the following display.

    If we would use price = null then no rows would be selected. This is because the logical expression price = null returns the value FALSE even for the rows where a null data exists. Hence, you must use is null instead of = null. And the keyword where uses the logical statements to decide which rows will be displayed.

If we now give the command select * from prices
where product = 'Quince'; then we get

    The greater than (>), the less than (<), the greater than or equal to (=>), and the less than or equal to (<=) operators can also be used in the where clauses. It is almost same with the previous case. But there is one thing important. Numerical data does not require quoting but the character based data does.

    When you need to find everything except for certain data, you can use the inequality symbol, which can be either < > or !=, depending on your SQL implementation.

     SQL has also character operators to manipulate the way character strings are represented, both in the output of data and in the process of placing conditions on data to be retrieved. One of these operators is LIKE. It can be given through an SQL command like the following one select * from prices where product like '\ora\';. This displays all rows of the table prices such that the product column contains the substring ora. You have to be careful about the wildcard character . Of course, \ora, ora\, and ora\ have different meanings. \ora matches all strings which end with ora while ora\ matches all strings which begin with ora whereas \ora\ matches all strings which contain ora.

    If you want to find data that matches all but one character in a certain pattern then you can use a different type of wildcard: the underscore (\_). You can combine the underscore and percentage symbols in pattern recognition. The \vert\vert (double pipe) symbol concatenates two strings. For example we can give the command select name || surname entirename from students; to get the following display.

    Some implementations of SQL use the plus sign to concatenatestrings. You have to check your implementation before attempting to concatenate strings.

    SQL uses logical operators to join comparative expressions or primitive logical expressions. Amongst these we can mention about the AND and OR operators. A compound logical statement can be obtained by combining the primitive logical statements like logstatement1 AND logstatement2. The compound statement can take the value TRUE if and only if both of the logical statements logstatement1 and logstatement2 are TRUE. Otherwise its value is FALSE. The OR statement works however more flexibly. It is used by locating it in place of ND above. The compound statement can take the value FALSE if and only if both of the logical statements logstatement1 and logstatement2 are FALSE.

     There is one more logical operator used in SQL. It is NOT. It negates the value of a logical statement. That is, it converts TRUE to FALSE and FALSE to TRUE.

    NOT can also be used with the operator IS when applied to NULL.

    The UNION keyword returns the results of two queries minus the duplicate rows. To exemplify we can consider the tables BE and SANEG whose content can be displayed by the commands select * from be and select * from saneg as follows.

    Now we can find how many people works in BE or SANEG by giving the command select name from be union select name from saneg;. This gives

     If we use UNION ALL instead of UNION then all the duplicate rows are displayed in the output. The keyword INTERSECT returns only the rows found by both queries. For example, the command select * from be intersect select * from saneg; displays

    In this example intersect finds the common names for both tables BE and SANEG by combining the results of the two elect statements.

    The MINUS (Difference) keyword returns the rows from the first query that were not present in the second. For example, we can use the command select * from be minus select * from saneg.

Miscellaneous Operators: IN and BETWEEN

    The two operators IN and BETWEEN provide a shorthand for functions you already know how to do. If you wanted to find friends in Colorado,California, and Louisiana, you could type the following:

    The IN keyword can be used instead of an or combined compound logical statement. For example, the commands select * from friends where province = 'istanbul' or province = 'izmir' \ or \ province = 'adana'; and \ \ select * from \ friends where province in('istanbul','izmir','adana'); are equivalent. Here we assumed that there is a table friends where the provinces each of the friends live are given in a column.

    The between keyword can be used for and combined comparative expression to get a shorthand. For example, the command select * from prices where price > 0.25 and price < 0.75; is equivalent to select * from \ prices \ where \ price between 0.25 and 0.75;. The parameters used in between are inclusive. That is, the endpoints are included.

SQL Functions

    SQL can use some functions to realize certain predefined actions. A function is composed of three things: Name, Action, and Argument. Argument is mostly a columnname. Therefore we can symbolically denote a function as functionname(columnname). Functions can be nested as long as it is permitted. For example, func1(func2(colname)) can be used to perform two different actions defined by the functions func1 and func2.In this case, first the function func1 is applied to the elements of the specified column individually. The created set of the values can be considered as a new column. The function func2 is applied on this new constructed set to get the composite action. A new set of elements is created after this action. This set can also be considered as a new column.

    SQL functions can be categorized into classes which can be called aggregate functions, date and time functions, arithmetic functions, character functions, conversion functions, and miscellaneous functions. Some of these functions may not be supported depending on the implementation and the version of SQL. The names of the functions may also differ from implementation to implementation. Here we are going to give a list of some important SQL functions without too much details.

* COUNT function returns the number of rows which satisfy the condition in the WHERE clause. It may be given with a wildcard argument like count(*). If COUNT is used without a WHERE clause, it returns the number of records in the table.
* SUM returns the sum of all values in a column. It works only with numbers. Otherwise an error message is broadcasted.
* AVG function evaluates the average of a column. It works only with the numbers.
* MAX function evaluates the largest value of a column. It can also work with character strings. In that cases the ascii values of the characters in the string is used to find the maximum value.
* MIN function evaluates the smallest value of a column. It can also work with character strings. In that cases the ascii values of the characters in the string is used to find the minimum value.

     The SQL has date type of data. Its structure may be varying depending on the implementation. You have to check your implementation before using it. Some of the functions which act on date type data are listed below.
* ADD_MONTHS function adds a number of months to a specified data. The number of the months to be added is specified in the second argument of the function. This function does not work with the other data types without using any data convertor.
* LAST_DAY function returns the last day of a specified month. It works with date type data.
* MONTHS_BETWEEN function returns the number of the months between two months. It needs two arguments: beginning and end dates. It works with date type data. It is sensitive to the order of the months.
* NEXT_DAY function returns the name of the first day of the week which is equal to or later than another specified date. The next day must be specified as a name string in the second argument of the function.
* SYSDATE function returns the system time and date.

    SQL uses some Arithmetic Functions to perform mathematical tasks. These functions work on numerical type data. Some of them are listed below.
* ABS function returns the absolute value of the data.
* CEIL function returns the smallest integer value which is greater than or equal to the argument of the function.
* FLOOR function returns the largest integer value which is smaller than or equal to the argument of the function.
* COS function evaluates the cosine of the argument of the function. The argument is assumed to be given in radians.
* SIN function evaluates the sine of the argument of the function. The argument is assumed to be given in radians.
* TAN function evaluates the tangent of the argument of the function. The argument is assumed to be given in radians.
* COSH function evaluates the hyperbolic cosine of the argument of the function.
* SINH function evaluates the hyperbolic sine of the argument of the function.
* TANH function evaluates the hyperbolic tangent of the argument of the function.
* EXP function evaluates the power of the number e. The power to which e will be raised is given as the argument of the function.
* LN function evaluates the natural logarithm of the argument of the function. The argument must be positive. It is better to use it together with ABS.
* LOG function needs two arguments. The first argument denotes the value whose logarithm will be evaluated while the second argument specifies the base of the logarithm.
* MOD function evaluates the modulo of its first argument with respect to the divisor which is its second argument. In contrast to modulo this function can deal with the real numbers.
* POWER function evaluates the power of its first argument. The value of the power is given in the second argument. If the first argument is negative then the second argument must be an integer. Otherwise SQL complains.
* SIGN function returns -1 if its argument
is less than 0, 0 if its argument is equal to 0, and 1 if its
argument is greater than 0.
* SQRT function evaluates the square root of its argument. The argument must be nonnegative.

    SQL supports some functions which perform certain actions on characters or character strings. Some of these functions are listed below.
* CHR function accepts numerical data as its argument and returns the character equivalent of this number.
* CONCAT function adds its second argument to the right hand side of its first argument. The result is a single string composed of these arguments.
* INITCAP function capitalizes the first letter of its argument which is assumed to be a string and makes all other characters lowercase.
* LOWER changes all the characters in its argument to lowercase.
* UPPER changes all the characters in its argument to uppercase.
* UBSTR has three arguments. The first argument which is to be operated on. The second argument defines the beginning of the substring which is extracted. And the last argument is the number of the characters in the substring to be extracted. Therefore this function extracts a substring from a given string.
* LENGTH returns the length of its argument. The length is the number of the characters in the string.

    SQL has also some conversion functions which operate between different data types. Some of these functions are listed below.
* TO_CHAR converts its argument which is assumed to be a number into a character.
* TO_NUMBER converts its argument which is assumed to be a string into a number.

     There are some other functions which are not mentioned here.
     The reader can refer to the references on SQL for them.

Joining Tables

Assume that we have created two tables A and  whose contents are displayed by giving the commands select * from a; and select * from b;. The displays are given below.

    We can join these two tables by giving the command select * from a,b;. The output display is as below.

     As we can see the output is constructed somehow unexpectedly. This is because the joining of the tables is a cartesian product operation. Therefore when we join two tables, the number of whose rows are respectively m and n the result is composed of m \times n rows. In other words, every possible cross combinations exist in the output. This type of join is called cross join. The output may contain unnecessary joinments. These can be removed by using where keyword and some conditions.

Data Manipulations

    You can enter data into a table by using the INSERT...VALUES statement. This enters data into a table one record at a time. This is useful for small operations dealing with just a few records. The values to be added must be same type of data as the fields where they are going to be added. Data size must be less than or equal to the size of the column where data will be added. The first value must be entered into the first column, the second value must be entered into the second column, ..., and so on. To exemplify how to use this command we can reconsider the table PRICES. If we want to add a new data about pear to this table we can give the command insert into prices (item, price) values('Pear',800000);. This changes the content of the table as we can see by issuing the command select * from prices;. The display is given below.

     The column names can be omitted without any problem. SQL manages data positioning automatically.

     The INSERT...VALUES statement can be useful when adding single records to a data-base table. For dealing with high number data to be entered a table INSERT...SELECT statement is much more beneficial. For example, the command nsert into temporary select * from table1; selects all existing rows of the table able1 and adds them into the table temporary. The creation of a new table can be accomplished by using CREATE keyword. For example we can create a new table whose name is MYTABLE by giving the command create table mytable (Name1 char(25), Number number, Name2 char(15), Number2 number);. This creates a four column table. The columns are defined by their names and the datatype they are going to keep. The first string in these definitions is for naming while the second one is for data type declaration. If the data type will be a character string then the length of the string, that is, the number of the characters in the string, must be specified between the parentheses which follow the type declarator char. That is, char(20) means that the corresponding column will be composed of at most 20 characters. If the column will be composed of sole numbers then the number declarator number should be given after the name of the column. Therefore, the above sample command creates a four column table whose name is mytable such that its first column has the Name1 and accepts at most 25 character long strings while its third column is named Name2 and accepts at most 15 character long strings whereas its second and fourth columns are called umber1 and Number2 and take number type data respectively.

     Let us now assume that we already have a table MYOLDTABLE which contains columns Name1, Name2, Number1, and umber2. And furthermore we assume that the data types of these columns fit the corresponding ones of the table YTABLE. Then we can give the command insert into mytable (Name1, Number1, Name2, Number2) select Name1, Number1, Name2, Number2 from myoldtable; to fill the columns of the newly created table MYTABLE. The string type data may not be in same length. However if the source table column accepts string type data whose length is equal to or less than the length of the coressponding column of the target table then there is no problem. After giving the command for filling new table the data appears to be in the table. However, in fact, the data transaction is not finalized until a COMMIT is issued. You can commit the transaction either by giving the COMMIT command or by simply exiting.

     In the utilization of the INSERT...SELECT statement the SELECT statement can not select rows from the table that is being inserted into and the number of columns in the INSERT INTO statement must be same as the number of columns returned from the SELECT statement.

     The DELETE Statement of SQL can be used to discard some data from a table. It uses also WHERE clause. By using DELETE command SQL can delete single or multiple rows. However DELETE can not delete an individual field's values. This can be accomplished by using UPDATE. DELETE does not remove a table. You need to use DROP command.

     To exemplify the action of DELETE command we can use it on the table PRICES by giving the command delete from prices where price > 1000000;. The new form of the PRICES table can be displayed as follows.

     To delete or remove an entire table you can use the DROP command. For example the command drop table prices; will remove the entire table of PRICES.