Basics in Bioinformatics and Databases

Part 1: Basics in Databases

Practical: SQL

Aim

• To give experience in using a relational database management system.

Objectives

After this practical you will:

• know how to create tables in a relational database;
• know how to insert data into a relational database interactively;
• know how to access data in a relational database using the SQL query language.

Introduction

In this practical you will use the Oracle relational database management system. You will access the database using the program SQL*Plus, which is an interpreter for the SQL language.

When you run the SQL*plus program you need to give your Oracle user-name and password. The Oracle user-name created for you is the same as your UNIX user-name, but with "@medic1" appended (e.g. if your UNIX user-name is "bid-21" then your Oracle user-name will be "bid-21@medic1"). You can leave SQL*plus by typing the command "exit". This is what you will see in a terminal window when you run SQL*plus:

% sqlplus

SQL*Plus: Release 9.2.0.1.0 - Production on Mon Sep 29 10:30:41 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter user-name: bid-21@medic1
Enter password: 

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

medic1> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
%

You can create the "dept" and "emp" relational tables by copying and pasting the SQL commands that are given below. Try out some of the SQL queries from the lecture, and some other queries of your own.

Create some tables of your own, add data to these, then write some SQL queries that access these tables.

-- SQL statements to create the 'dept' and 'emp' tables

CREATE TABLE dept (
        deptno   int,
        dname    varchar(12),
        loc      varchar(10));

CREATE TABLE emp (
        empno    int,
        ename    varchar(7),
        job      varchar(10),
        mgr      int null,
        hiredate date,
        sal      int,
        comm     int null,
        deptno   int);

-- SQL statements to insert data into the 'dept' and 'emp' tables

INSERT INTO dept VALUES (10, 'accounting', 'new york');
INSERT INTO dept VALUES (20, 'research', 'dallas');
INSERT INTO dept VALUES (30, 'sales', 'chicago');
INSERT INTO dept VALUES (40, 'operations', 'boston');

INSERT INTO emp VALUES (7369, 'smith', 'clerk', 7902, '17-DEC-80', 800, NULL, 20);
INSERT INTO emp VALUES (7499, 'allen', 'salesman', 7698, '20-FEB-81', 1600, 300, 30);
INSERT INTO emp VALUES (7521, 'ward', 'salesman', 7698, '22-FEB-81', 1250, 500, 30);
INSERT INTO emp VALUES (7566, 'jones', 'manager', 7839, '02-APR-81', 2975, NULL, 20);
INSERT INTO emp VALUES (7654, 'martin', 'salesman', 7698, '28-SEP-81', 1250, 1400, 30);
INSERT INTO emp VALUES (7698, 'blake', 'manager', 7839, '01-MAY-81', 2850, NULL, 30);
INSERT INTO emp VALUES (7782, 'clark', 'manager', 7839, '09-JUN-81', 2450, NULL, 10);
INSERT INTO emp VALUES (7788, 'scott', 'analyst', 7566, '09-DEC-82', 3000, NULL, 20);
INSERT INTO emp VALUES (7839, 'king', 'president', NULL, '17-NOV-81', 5000, NULL, 10);
INSERT INTO emp VALUES (7844, 'turner', 'salesman', 7698, '08-SEP-81', 1500, 0, 30);
INSERT INTO emp VALUES (7876, 'adams', 'clerk', 7788, '12-JAN-83', 1100, NULL, 20);
INSERT INTO emp VALUES (7900, 'james', 'clerk', 7698, '03-DEC-81', 950, NULL, 30);
INSERT INTO emp VALUES (7902, 'ford', 'analyst', 7566, '03-DEC-81', 3000, NULL, 20);
INSERT INTO emp VALUES (7934, 'miller', 'clerk', 7782, '23-JAN-82', 1300, NULL, 10);

-- SQL statements to remove the 'dept' and 'emp' tables

DROP TABLE dept;
DROP TABLE emp;

Supplementary information

• SQL*Plus User's Guide and Reference