CUBRID2008 R2.1 Tutorial for Windows

Introduction:This document is designed to help a new user of CUBRID get familiar with the product by providing a series of examples for database operations such as creating, starting, terminating a database as well as database manipulation such as schema creation, changed data manipulation and retrieval.

This document contains the basic information for using CUBRID, so it is recommended to see CUBRID manual for more information.

Acceptable Version : CUBRID2008 R2.0

Table of Contents

• Terminology
• Starting CUBRID
  • Creating and Starting a Database
• Manipulating CUBRID
  • demodb Database Table Information
  • Using the Query Editor
  • Creating Schema
  • Manipulating Data
  • Retrieving Data
  • Creating Partitions and Views
• Terminating CUBRID

Terminology

Basically, CUBRID uses object-relational DBMS terminology, but relational database terminology is used in this document for the users of conventional relational DBMS. The following table shows the relationship between relational database terms and object-relational database terms.

^ Top of page

Starting CUBRID

This document is written with the assumption that CUBRID installation is completed. If CUBRID has not been installed, install CUBRID before reading this document.

To create and run a database, use CUBRID Manager Client.

^ Top of page

Creating and Starting a Database

Starting CUBRID Manager Client

If CUBRID is properly installed, you can see whether CUBRID program is running by selecting [Start] -> [All Programs]. Select CUBRID to run CUBRID Manager Client, The CUBRID Manager is a tool that can manage databases and execute queries.

If you right-click the localhost on the left after starting CUBRID Manager Client, the Connect Host menu appears. When you click it, the Connect Host dialog appears as shown below.

By default, the localhost's site name is already registered. To add a new CUBRID server, click button on the upper left menu. Then, the following dialog appears, and you can enter a new name and address to register a new site.

As shown above, select a server you want to connect to in the Site name filed and click the Connect button. Select localhost for connection. The initial default user ID and password are admin/admin, and then the following password change dialog appears

When you click OK button, the following dialog appears.

When CUBRID is installed, you can see that one database is automatically installed as shown in the figure above. However, you need to log in to control the database. If you right-click or double-click the database, the following dialog appears. Only with dba, you can control the database, and no initial password is required. When you click the OK button, you are connected to demodb.

Creating a Database

To create a database, you can click button on the toolbar of CUBRID Manager or you can use the shortcut menu that appears when right-clicking a server in the server list.

If you click the button, the first page of the Create Database wizard appears as shown below. Enter the name of the database you want to create and click the Next button. In the page 2 and page 3, just click the Next button, then a database is created with the default settings.. For more information about the database creation, see to the user manual.

When the creation is complete, you can see the icon of the testdb database, which is ready to run.

Running a Database

To run a database, you need to log in by using dba.

Select a database you want to run and click [Action] from the toolbar. You will see the login menu is activated. If you select [Login Database], the [Login Database] dialog appears. Or right-click a database you want and select [Login Database] from the shortcut menu.

After login, click the icon or click [Start Database] under the [Action] menu, then the database starts.           

You can also right-click a database you want and then click [Start Database] to start the database.

^ Top of page

Manipulating CUBRID

There are several ways to manipulate a database, but this document introduces manipulation with CUBRID Manager. If a database is ready to be manipulated after following the above steps, you can execute queries by using the Query Editor. This is possible only online, and the demodb database created automatically by installing CUBRID will be used in the Manipulating Data section. demodb is the database created by default, which can be used as a sample.

^ Top of page

demodb Database Table Information

This database consists of Olympic related data and is designed to be flexible for manipulation and execution.

The relationship and table structure in demodb are shown below.

^ Top of page

Using the Query Editor

In CUBRID Manager, right-click demodb and select [Start Database]. To open the Query Editor, click icon at the upper left corner and select demodb. Or right-click demodb and select [New Query] from the following shortcut menu.

As shown below, the Query Editor opens. If you click [No database selected], you can select demodb.

Query results are displayed at the bottom of the Query Editor.

^ Top of page

Creating Schema

Creating and Checking a Table

To create a table, right-click [Table] under [Databases] and select Create Table from the shortcut menu.

When the Create Table dialog appears as shown below, enter a table name and select the owner. Then, click the OK button.

Click the [Add] button at the bottom to add a column. After creating a table, you can set the primary key in the Create Table dialog by using the PK button at the bottom.

In the FK/Indexes tab of the Create Table dialog, you can add a foreign key or an index. In addition, you can check a created SQL statement in the SQL Script tab.

When a table is created, you can see the table under the Tables tree on the left.

^ Top of page

Manipulating Data

Inserting Data

Add a new row by using the INSERT statement.

Query) Add the information about 2008 Beijing Olympics (2008, ‘China’, Beijing, 2008-08-08, 2008-08-24, FUWA,’ One world, One dream’) to the olympic table.

insert intoolympic(host_year, host_nation, host_city, opening_date, closing_date, mascot, slogan) 	values(2008, 'China', 'Beijing', '2008-08-08', '2008-08-24', ' FUWA', 'One world, One dream'); 	select * from olympic order by host_year desc

Modifying Data

Modify the inserted value to a new one by using the UPDATE statement.

Query) Change the 2008 host_nation in the olympic table to People's Republic of China.

update olympic set host_nation='People''s Republic of China' where host_year=2008;  	
select * from olympic order by host_year desc; (Use ‘’ when you want to enter ‘.)

Deleting Data

Delete a row in the table by using the DELETE statement.

Query) Delete the data of year 2008 in the olympic table.

delete from olympic where host_year = 2008;  	select * from olympic order by host_year desc;

^ Top of page

Retrieving Data

The SELECT statement used for retrieving data consists of the SELECT clause, FROM clause, and optional WHERE clause.

ØSELECT clause

The SELECT clause can have a column name, character, operator, DISTINCT, *, alias.*, Table name.*, and sub-query, etc.

ØFROM clause

Specifies a table for retrieving data

ØWHERE clause

Operator

The following shows query results by using various operators.

Query) Retrieve all the countries which held Olympic Games (list countries just once if there are multiple results)

select distinct host_nation from olympic;

Query) Retrieve the total number of gold, silver, and bronze medals that South Korea won in the 1988 Seoul Olympic Games.

select gold+silver+bronze as "Total number" from participant where nation_code='KOR' and host_year=1988;

Query) Retrieve how many days 1988 Seoul Olympic Games last.

select closing_date-opening_date as "Number of days" from olympic where host_year=1988;

Function

The following shows the results by using functions.

Query) Output all the participant countries in the 1988 Seoul Olympic Games in capital letters.

select upper(A.name) from nation A, participant B where A.code=B.nation_code and B.host_year=1988;

Query) Output the opening date of 1988 Seoul Olympic Games in March 1 Mon format.

select host_year, host_city , TO_CHAR(opening_date, 'MONTH dd DY') from olympic where host_year=1988;

Query) Output all the Olympic Games mascots, or 'None' if mascot is not specified.

select host_year, nvl( mascot, 'NONE') from olympic;

Query) Retrieve the total number of gold medals in the 1988 Seoul Olympic Games

select host_year, sum(gold) from participant group by host_year  HAVING host_year =1988;

Query) Retrieve both the countries that won gold medals from swimming in the 1988 Seoul Olympic Games and the countries that won gold medals at 'Seoul Olympic Stadium.'

select * from nation where code in(
select nation_code from game 
where event_code in(select code from event where sports ='Swimming') and medal='G' and host_year=1988
UNION
select nation_code from game where stadium_code in(
select code from stadium where name ='Seoul Olympic Stadium') and medal='G' and host_year=1988
); 

Join

The following shows retrieving data through the relationship between tables.

Query) Retrieve the best record in 100 M and the athlete of the record in Olympic Games.

select B_2 as Record, A.name from athlete A INNER JOIN 
(select athlete_code, cast(score as numeric(4,2)) from record
where event_code in(select code from event where id='100m') order by 2 asc) B
on A.code=B.athlete_code and rownum=1; 

Query) Retrieve all the participant countries in the 1988 Seoul Olympic Games and their ranking.

select B.name, A.gold, A.silver, A.bronze from participant A inner join nation B
on B.code=A.nation_code and A.host_year=1988 order by 2 desc,3 desc,4 desc, name asc; 

Query) Retrieve the countries from rank 1 to rank 10 in the 81988 Seoul Olympic Games.

select B.name, A.gold, A.silver, A.bronze from participant A inner join nation B
on B.code=A.nation_code and A.host_year=1988
order by 2 desc,3 desc,4 desc, name asc for orderby_num() between 1 and 10; 

^ Top of page

Creating Partitions and Views

The following shows creating a partition by using a query and creating a view.

Partition

alter table game partition by list (medal)
(partition medal1 values in ('G'),
partition medal2 values in ('S'),
partition medal3 values in ('B')
); 

View

Condition)Define a view containing the name, country, and event of the gold medalists in the 1988 Seoul Olympic Games.

create view game_1988 as
select B.name as "Athlete's name", A.name as Nation, B.event as Event 
from nation A INNER JOIN (select name, nation_code, event from athlete 
where code in(select athlete_code from game where medal='G' and host_year=1988)) B
on A.code=B.nation_code; 

* For other operators and functions, it is recommended to see CUBRID2008 Manual.

^ Top of page

Terminating CUBRID

Select the database you want to stop from the server list on the left of CUBRID Manager Client and click button. Or right-click the database and select [Stop Database] from the shortcut menu. Starting and stopping a database are possible only with the DBA account.

^ Top of page