Contents

Chapter 1
A Tutorial On SYBASE

This tutorial has been designed to familiarize you with the SYBASE relational database system. You should be able to follow the examples given here and observe the same results.

1.1 Getting Started

The query language we are using is SQL. This tutorial provides an introduction to SQL on SYBASE. Complete information on SQL and SYBASE appears in the SQL Server Quick Reference Guide, Transact-SQL User’s Guide, and SQL Server Reference Manual Volume I and II. These manuals are available on-line using ’sybooks’. They are in the SQL Server Collection.

Before you can use SYBASE, there are a few things that must be set up:

1.1.1 Setting up SYBASE environment

In order to access SYBASE, you must add “SYBASE” to your .subscriptions file. Do this by running

To have access to the SYBASE manuals you must also subscribe to SYBOOKS:

1.1.2 Entering SYBASE

Now you can start using SQL from the operating system. To use SQL, you must have an account for SYBASE’s SQL server. SYBASE maintains its own password file distinct from the Unix password file. Your SYBASE account will be set up with your Unix user name and the standard default password “nnnnaa” where “nnnn” is the last four digits of your student ID number and “aa” is your first and last initials in lower case. ²

Type the following command at your operating system prompt:

On your screen, you will then see the prompt:

Type in your password and press the return key. Then you will see:

1.1.3 Changing Password

Once you have logged in, you can change your password at anytime ³ . Your new password must be at least 6 bytes long. Assuming your password is ’1234xx’, here is how you’d change the password ’1234xx’ to ’topsecret’:

Notice that the word go appears on a line by itself and must not be preceded by blanks. It is the command terminator, and lets SQL server know that you are done typing, and you are ready for your command to be executed. Also notice that the old password ’1234xx’ is enclosed in quotes. A password starting with a numeral must be enclosed in quotes.

1.1.4 Copying Tutorial Tables

Before you go on, you must copy all of the tables in the tutorial database. Type the following command:⁴

The set of tutorial tables, including “parts”, “supply” and “employee”, will be used for every examples in this tutorial.

The OSUsp_CopyTutorial procedure will give you a fresh copy of the tutorial tables. However, you have to first make sure that no tables with the same names of the tutorial tables existing. (See the next section.)

1.1.5 Clearing Tutorial Tables

If you decide to delete all of the tutorial tables, type the following command:

1.1.6 Listing User Tables

To list all of the objects, including tables, indexes and views, created by you, type:

1.1.7 The Query Buffer

Once you have entered isql, each query that you type is placed in a query buffer, rather than executed immediately. The queries are not executed until you type the command terminator (go). Recall that go must not be preceded by any blanks. The results, by default, appear on your terminal. For example, we have a table called “parts” in our database. Type the following command:

The line “select * from parts” selects all from the table “parts”. The following then appears:

What is printed on your terminal is the current content of the “parts” relation. In this case, the relation name is “parts”. There are five columns (we call them attributes) named pno (part number), pname (part name), color, weight, quantity(quantity at hand). Each row of the relation (called a tuple) represents one entry, which in this case represents one part in a computer installation.

After a go command the query buffer is cleared if another query is typed in.

Editing a Query in the Query Buffer

It is possible to edit a command if you make an error. For example, enter the following query containing a typographical error.

You will get an error message. After a careful review, you will find that the relation name “paart” should be “parts”. Then type:

An emacs editor will be invoked.⁵ You can edit the query buffer and correct the error. After you have done, type Ctrl-x-Ctrl-s to save the correct query and Ctrl-x-Ctrl-c to return to SQL. After you have corrected the mistake and returned to SQL, rerun the query by typing:

Then the following appears:

Loading Previously Prepared Query File into Query Buffer

First use emacs to create a file of the following:

Note if you forget to type the right quote, when you execute the query later, nothing will happen. So please check if the quotes or parenthesis match. Notice also that the “%” used in the above query is one of the pattern matching constructs. In fact, there are three pattern matching constructs provided by SYBASE. All three can be used in any combination for character comparison. They are:

Save the above query in a file called “smallquery”. Then type :

Then the following appears:

Erasing Query Buffer

The following command will allow you to clear the entire query buffer. The former contents of the buffer are lost and cannot be retrieved.

1.1.8 Exiting SYBASE

To leave the SYBASE, type:

1.1.9 Summary of Commands Related to Query Buffer

The following are a list of the commands related to the query buffer:

1.2 The Tutorial Database

Thus far we have signed onto SYBASE, started SQL and viewed a table called “parts”. The following is a description of all the tables used in this tutorial and their formats:

This information may be obtained using the help facilities of SQL. For example “OSUsp_help” lists all tables, “OSUsp_help parts” list the description of the table “parts”. However, note that the column “meaning” won’t be provided by the help facility. It appeared above only as our remarks. Also note that an “1” in the null column means “null” is allowed in the corresponding attribute; a “0” means “null” is not allowed.

We have seen table ”parts”. Tables 1.1 and  1.2 in the following show the contents of tables “supply” and “employee” respectively.

1.2.1 Some Query Examples

In the following, we use some examples to illustrate the “select” command of SQL.

Simple Projection

Type the following commands:

The result of the above query is the following:

To eliminate the duplicates, use the keyword “distinct”:

The result is the following:

From now on, for most examples we will show only the queries but not the results. You should run the queries to see the results.

Arithmetic Operation

COMMENT: This query shows the result of giving a bonus to employees whose salary is not greater than $10,000 .

Logical operation

COMMENT: This query shows the names and salaries of the employee whose manager=199, if the employee was hired before 1961 or has a salary not greater than $10,000.

Simple Join Operation

Up until now, we haven’t used another important relational operation: join. The next example illustrates a simple join with projections. Suppose we want to find out all part names and the corresponding supplier numbers for a given shipdate: 12-31-73. To perform this operation, two relations: parts and supply, are referenced.

COMMENT: If you forgot to give the last qualification (that is, supply.pno = parts.pno), you will get a Cartesian product on selected “sno” and all “pname”. From this, can you see what is the relation between Cartesian product and join?

The syntax for a simple “select” is:

Use of “order by” clause

.

COMMENT: The use of “between” and “and” keywords in the where clause is equivalent to combining the two inequalities <= and >=. The “order by” clause specifies the sequence of the rows that result from a query. The default sort sequence is in ascending order. “desc” represents “descending”.

Use of “group by” and “having clause”

COMMENT: This query finds out all manager numbers of those having more than two employees. Note that we have an empty header for the second column of the output rather than ”count” or ”count(*)”.
SYBASE provides the following aggregation functions:

The built-in functions are only valid when used in “select” or “having” clauses.

Nested query

We have seen a simple nested query in the previous example. Nesting allows you to use the results of one query as input to another, so you can use the results of one question to answer another one. Let’s try another nested query in the next example.

COMMENT: This query modifies the previous example by printing out the names of those managers having more than two employees.

Use of “Union”

The complete select statement syntax is:

where the syntax for “subselect” is:

Note that duplicate rows are always eliminated if union is specified. But if you say union all, duplicates are not removed. If you say union all once, you must say it for all unions within one statement. Also all subselects in a select statement with union must have the same number of columns in their result tables. Additionally, columns of numeric type cannot be matched with columns of character type.

COMMENT: This query selects all part numbers (pno) that are either black or are shipped on December 31, 1973. Duplicates are eliminated.

1.2.2 How to modify the database

In addition to its query facilities, SQL also provides the capability to modify the database. This means that you can interactively insert, modify and delete tuples in your database. Access privileges for insert, modify and delete operations will be discussed in 1.7. Here you are allowed to experiment with these operations on the tutorial database. The command formats are as follows:

Insert from the screen

COMMENT: The above two tuples were appended to the table.

Update operation

COMMENT: You can also update a field by using an expression, and you can change more than one field in a single update query.

Delete operation

COMMENT: This command deletes all the tuples added.

1.3 Database Creation

This part of the tutorial is intended to provide you with a quick overview of database creation. There are two components in creating a new database. They are:

• specifying the relations and associated attributes
• entering data into the database.

1.3.1 How to create a table

The following create table statement creates a new table with the specified column formats:

Note that no null means the column specified doesn’t allow null to be stored as a value; null means the column specified allow null value.

Create a table

The following are some of the most frequently used data types in SYBASE/SQL:

1.3.2 Insert data into table

After creating a table, you can use the insert into statement to input data. SYBASE does not limit the number of tables in a database; you are limited only by the available disk space. If you are dealing with very large tables or data configurations, beware that:

• The maximum number of columns is 250.

To enter the following data into table “student”,

You have to use the insert command we described in 1.2.2.

Note that we can type in several insert commands and execute them all together.

1.3.3 Insert with Selection

To pull values into a table from one or more other tables, use a select in the insert statement. As an exercise, let’s create a temporary table “student_tmp” which consists of only the “id” and “name” and then insert the corresponding data from the “student” table.

We first create the table:

Then, we insert into the table by selecting data from “student” table.

To insert only the students with id < 3, type:

1.3.4 Destroy a table

COMMENT: removes table “student_tmp” from the database.

1.4 Adding, Changing, and Deleting columns

1.4.1 Adding columns

You can add new columns to a table at any time with the ALTER TABLE command. Here is its syntax:

For example, to add a column “birthday” to the student table, type:

Columns added with the ALTER TABLE statement must allow null values. This is because when the new column is added to the existing rows, there must be some value for it.

1.4.2 Changing columns

You can use sp_rename to rename columns of a table. The syntax is:

For example, you want to change “birthday” of student table to “birthdate”, type:

1.4.3 Deleting columns

To delete one or more columns from a table, proceed as in adding columns. The example deletes all columns from table student except id and name.
1. Create a temporary table containing all the columns to be included in the revised table.

2. Populate new table by selecting columns from original table.

3. Destroy the original table.

4. Rename the temporary table with the name of the original table.

1.5 How to create views

Views can be thought of as virtual tables. They do not store copies of the data, but refer to the base tables involved in the view. Primary uses for views include:

• Providing security by limiting access to specific columns in selected tables, without compromising database design
• Simplifying a commonly used query
• Defining reports

A view is created with the create view statement:

A view is destroyed if the base table is destroyed.

1.5.1 An example of creating view

1.5.2 Destroy view

1.6 How to create an index

We can use the following statement to create an index:

The create index statement creates an index on an existing base table. The index contains the columns specified and is keyed on those columns, in the order they are specified.

1.6.1 An example of creating index

1.6.2 Destroy index

To drop index, you must give both the table and the index name, in the form tablename.indexname.

1.7 How to give out privilege of access to your tables

The following grant statement grants privileges on a table, view, or procedure:

The priv can be any of the following:

1.8 Help Commands

The help command can be used to obtain information about the database. The legal forms are as follows:

1.9 Date and Time in Sybase (Section may be skipped on first reading.)

Sybase provides two special data types for storing dates and times.

1.9.1 Date/Time Data Types

The date/time data types are called datetime and smalldatetime. These data types are different from those specified in the SQL 92 standards and described in the Elmasri and Navathe text. In the Tutorial Database, the attribute “shipdate” of the relation “supply” is of type datetime. (Note that the attribute “bdate” of the relation “employee” is of type char(9)), not of type datetime.

The datetime data type can hold a date between January 1, 1753 and December 31, 9999. Values are accurate to 1/300th of a second. 8 bytes of storage are required, 4 bytes for the number of days since January 1,1753, and 4 bytes for the time of day.

The smalldatetime data type can hold a date between January 1, 1900 and June 6, 2079. 4 bytes of storage are required, 2 bytes for the number of days since January 1, 1900, and 2 for the number of midnights after midnight.

1.9.2 Display Format

The default display format for a date is “Mon dd yyyy hh:miAM” (or PM), e. g. “Mar 20, 1995 1:58PM”. For example using the Tutorial database

yields the partial result⁶ :

1.9.3 Other formats using the convert function

It is possible to display a date in many other formats (each is identified by a number) using the convert function. The year may be shown as 2 or 4 digits. The order of the day, month and year may be permuted. ’/’ or ’-’ may be used as separators. Most formats show the date alone. Some show the date and time.

An example using format “1” is shown below:

All the possible formats are described in Table 2-4 of the SQL Server Reference Manual.

1.9.4 Parts of a date or time using the datename and datepart functions

Individual parts of a date may be displayed using the datename and datepart functions. The function datename returns the name as a character string, e. g. “Monday”. The function datepart returns the integer value, e. g. “2”. An example showing the weekday, “dw” is shown below:

The possible date and time parts are given in Table 2-6 of the SQL Server Reference Manual.

1.9.5 Date/Time Data Entry Format

Sybase provides many formats for entering dates and time. One format will be described in this section. This format allows the entry of a date, e. g. March 20, 1995, or a date and time with hours (0 - 24) and minutes, e. g. March 20, 1995 13:30. For other formats and to enter seconds and milliseconds or to use AM and PM refer to the SQL Server Reference Manual.

The easiest format for entering a date is to use a numeric format “[m]m/dd/[yy]yy” for a date alone or “[m]m/dd/[yy]yy [h]h:[m]m” for a date and time. Note that if only two digits are used for the year, then values less than 50 are interpreted as 20yy and values of 50 or greater are interpreted as 19yy. Since the data being entered is treated as a character string, it must be enclosed in single or double quotes.

The following examples show some rows being entered into the “supply” relation:

The output for those dates is shown below:

Note that the first insert translates year “95” to the year “1995” and generates the default time of “12:00AM”, i. e. midnight. The second insert with a time of “14:30” is printed as “2:30PM”, since that is the default output format. The third insert translates the year “15” to “2015”. The fourth insert shows the use of a four digit year, “2095”. The last insert shows leading zeros are not needed in the day, hours or minutes.

1.10 User-Defined Datatypes (Section may be skipped on first reading.)

As a Transact-SQL enhancement to SQL, you may name and design your own datatypes to supplement the system datatypes. The user-defined datatypes can serve as domains for attributes of relations. A user-defined datatype is defined in terms of system datatypes. You may give one name to a frequently used type definition. This makes it easy for you to custom fit datatypes and give semantics to columns.

For example, consider the employee table

You can define a user-defined datatype ’id_no’ for eno and manager in table employee instead of using ’int’. The advantage of user-defined datatypes is that you can bind rules and defaults to them, for use in several tables. (Refer to Transact-SQL User’s Guide for rules and defaults).

The stored procedure “sp_addtype” is used to create user-defined datatypes. It takes as parameter the name of the user datatype being created, the SQL system datatype from which it is being built, a length (if one is required), and an optional NULL or NOT NULL specification.

The syntax for “sp_addtype” is:

You need to specify a length after the datatype only for char and varchar. Double or single quotes are required around a stored procedure parameter when:

• the string includes a blank or some form of punctuation or
• the parameter is a keyword.

To create a new datatype ’id_no’ for eno and manager of the employee table:

When you create the table ’employee’, you may specify ’id_no’ as the datatype of eno and manager instead of using ’int’.