BIL101, Introduction to Computers and Information Systems Chapter 11

1
BIL101, Introduction to Computers  and 
Information Systems Chapter 11

• Sample SQL Applications
• Prepared by
• Metin Demiralp
• Istanbul Technical University, Informatics
  Institute,
• Maslak 80626, Istanbul, Türkiye)
• Version 0. 60
• Reformatted for presentation by Hüseyin Toros
• http//www.be.itu.edu.tr

2
Sample SQL Applications

• This chapter is mainly devoted to some
  applications of SQL. There are various sample
  applications in this notes to explain and
  illustrate how SQL works. You can use interface
  at the URL
• http//www.be.itu.edu.tr/cgi-bin/sql/exec_sql.pl
  
• or
• http//www.sqlcourse2.com
• It is possible to run many types of SQL
  applications and see the output through these
  interface.

3

• Queries
  
• As you know SQL is an acronym for the statement
  Structured Query Language. 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.
• 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.

4

• 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.
• SELECT 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.

5

• 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.
• select "column1","column2",etc from "tablename
  where "condition"
• optional

6

• This specification can be done by using the
  keyword FROM which must be followed by the name
  of the table under consideration.
• Three important items under SQL are 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 FROM clause to write an SQL statement.
• The column names that follow the select keyword
  determine which columns will be returned in the
  results. You can select as many column names that
  you'd like, or you can use an asterix, ( ) to
  select all columns.

7

• We can apply the following query command to the
  STUDENTS table.
  
• select from students This will produce
  the following output through the interface we use
  here.

Students
8
select register_no, faculty, program, name,
surname from students results in the following
display.  
9
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 studentsThe resulting
display is as below.
Therefore the key distinct is used for the
removal of the multiplicated data
10
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 \ 3 2 8 \ 2 0
9 \ 3 0The modulo operator does work only
with integer data type. 
11
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 expression3 7
12/4 equals 21 3 24However, the
expression3 (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.
12
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.

13
select item, price, price1.2 from prices then
we obtain the following display.
   
14

• 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).

15

• SQL functions can be categorized into classes
  which can be called aggregated functions, data
  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 where clause (optional) specifies which data
  values or rows will be returned or displayed,
  based on the criteria described after the keyword
  where.
• Conditional selections used in where clause

• lt Less than or equal to
• gt Greater than or equal to
• ltgt Not equal to
• LIKE

Equal gt Greater than lt Less than
16

• Select name, surname, faculty from students where
  program LIKE Com'
• 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
  satisfying 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.
• SELECT Count() FROM students

17

• SUM returns the sum of all values in a column.
  It works only with numbers. Otherwise, an error
  message is broadcasted.
• SELECT sum(exam) FROM students WHERE Faculty
  Electronics'
• AVG function evaluates the average of a column.
  It works only with the numbers.
• SELECT avg(exam) FROM students
• 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.
• SELECT max(exam) FROM students

18

• MIN function evaluates the smallest value of a
  column. It can also work with character strings.
  In that cases tha ascii values of the characters
  in the string is used to find the minimum value.
• SELECT min(exam) FROM students
• 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.

19

• LAST_DAY function returns the last day a
  spicified month. I works with data type data.
• MONTHS_BETWEEN function returns the number of
  months between two months. It needs two
  arguments beginning and end data. It works with
  date tape 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.

20

• SYSDATE function returns the system time and
  date.
• 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 fuction.

21

• 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.

22

• 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.
• LN function evaluates the natural logarithm of
  the argument of the function.

23

• 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 module this
  function can deal with the real numbers.

24

• POWER function evaluates the power of its first
  argument. The value of the power is given in the
  second argument.
• 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.

25

• SQL supports some functions which perform certain
  actions on characters or character strings.
• 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.

26

• LOWER changes all the characters in its argument
  to lowercase.
• UPPER changes all the characters in its argument
  to uppercase.
• SUBSTR has three arguments. The firs 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 characters in the substring to be extracted.

27

• LENGTH returns the length of its argument. The
  length is the number of the characters in the
  string.
• 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.