AntDB-Oracle Compatibility Developer's Manual P1–2
Published: 2023-08-28 07:37:53

# Introduction to SQL Language Tutorial

This chapter provides an introduction to the SQL language for beginners to relational database systems. The following examples discuss basic SQL database operations such as creating data tables, inserting rows into tables, querying, and updating rows.

More advanced concepts such as views, foreign keys, and transactions are also discussed in this chapter.

## **Basic Description**

AntDB is a relational database management system (RDBMS) for managing data stored in a relationship. For a table, a relationship is actually a mathematical term. The concept of storing data in a table is now so well known that we may think of it as the only form of storing data. But there are other forms of database system organization here. The file and directory system on UNIX-like operating systems is an example of a hierarchical database system.

Each table is a set of records with a name. Each record in the table is an identical set of columns, and each column belongs to a specific data type. The order of the columns in each record is fixed. It is important to remember that the SQL language does not ensure that the rows appear in a fixed order in the table (although it is possible to sort the output for display purposes).

### **Example database**

Throughout this document, we will use a sample database to help us explain some of the basic to advanced database concepts.

#### Install the sample database

When AntDB is installed, a database with the name postgres is automatically created Create all the tables and procedures you will use in this guide in this sample database.

The tables and procedures will be created in the first schema in the search path, and the user must have permission to create tables and stored procedures on this schema. The search path can be displayed by using the following commands.

```
SHOW SEARCH_PATH;
```
We can use commands in PSQL to modify the search path.

The use of sql statements in this example differs from oracle syntax in the following ways.

- You need to set the stored procedure before executing it

```
\set PLSQL_MODE ON
```

- When the stored procedure has no parameters, you can just write the procedure name without brackets in Oracle; but in AntDB, you need to add empty brackets after the procedure name.

```
Oracle：create procedure name is ……
AntDB：create procedure name() is ……
```

- Use return for procedure return, don't use returns

#### Introduction to the sample database

The content of the sample database is some information about the employees in an organization.

It contains three types of records: employee personal information records, departmental information records, and employee history records.

Each employee has an identification number, name, start date, salary, and supervisor. Some employees have commissions in addition to their salary. All the information related to the employee is stored in table emp.

Since the companies in the example are located in different regions, the database keeps track of the geographical location of each department of the company. Each company employee is assigned to a department. Each department is identified by a unique department number and name. Each department is associated with a geographic location. All information related to the department is stored in the dept table.

Because some employees have been with the company for a long time, have worked in different positions, have received raises, or have changed departments, the company also keeps track of the employees' job information. When an employee's status changes, the company records the end date in the previous position. At the same time, a record is added for the employee of the new job, including the start date of the new job, job title, department, salary, and the reason for the change in status. The history of all employee records is in the jobhist table.

Below is the entity relationship diagram for the entire sample database table.

![image-20201029095156823](../oracle_compatible1.png)

Execute the following sql statement to create the relevant table.

```
set grammar to oracle;
BEGIN;
--
--  Create and load tables used in the documentation examples.
--
--  Create the 'dept' table
--
CREATE TABLE dept (
deptno  NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
dname   VARCHAR2(14) CONSTRAINT dept_dname_uq UNIQUE,
loc VARCHAR2(13)
);
--
--  Create the 'emp' table
--
CREATE TABLE emp (
empno   NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename   VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate    DATE,
sal NUMBER(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0),
comm    NUMBER(7,2),
deptno  NUMBER(2) CONSTRAINT emp_ref_dept_fk
REFERENCES dept(deptno)
);
--
--  Create the 'jobhist' table
--
CREATE TABLE jobhist (
empno   NUMBER(4) NOT NULL,
startdate   DATE NOT NULL,
enddate DATE,
job VARCHAR2(9),
sal NUMBER(7,2),
comm    NUMBER(7,2),
deptno  NUMBER(2),
chgdesc VARCHAR2(80),
CONSTRAINT jobhist_pk PRIMARY KEY (empno, startdate),
CONSTRAINT jobhist_ref_emp_fk FOREIGN KEY (empno)
REFERENCES emp(empno) ON DELETE CASCADE,
CONSTRAINT jobhist_ref_dept_fk FOREIGN KEY (deptno)
REFERENCES dept (deptno) ON DELETE SET NULL,
CONSTRAINT jobhist_date_chk CHECK (startdate <= enddate)
);
--
--  Create the 'salesemp' view
--
CREATE OR REPLACE VIEW salesemp AS
SELECT empno, ename, hiredate, sal, comm FROM emp WHERE job = 'SALESMAN';
--
--  Sequence to generate values for function 'new_empno'.
--
CREATE SEQUENCE next_empno START WITH 8000 INCREMENT BY 1;

--
--  Load the 'dept' table
--
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');
--
--  Load the 'emp' table
--
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,'19-APR-87',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,'23-MAY-87',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);
--
--  Load the 'jobhist' table
--
INSERT INTO jobhist VALUES (7369,'17-DEC-80',NULL,'CLERK',800,NULL,20,'New Hire');
INSERT INTO jobhist VALUES (7499,'20-FEB-81',NULL,'SALESMAN',1600,300,30,'New Hire');
INSERT INTO jobhist VALUES (7521,'22-FEB-81',NULL,'SALESMAN',1250,500,30,'New Hire');
INSERT INTO jobhist VALUES (7566,'02-APR-81',NULL,'MANAGER',2975,NULL,20,'New Hire');
INSERT INTO jobhist VALUES (7654,'28-SEP-81',NULL,'SALESMAN',1250,1400,30,'New Hire');
INSERT INTO jobhist VALUES (7698,'01-MAY-81',NULL,'MANAGER',2850,NULL,30,'New Hire');
INSERT INTO jobhist VALUES (7782,'09-JUN-81',NULL,'MANAGER',2450,NULL,10,'New Hire');
INSERT INTO jobhist VALUES (7788,'19-APR-87','12-APR-88','CLERK',1000,NULL,20,'New Hire');
INSERT INTO jobhist VALUES (7788,'13-APR-88','04-MAY-89','CLERK',1040,NULL,20,'Raise');
INSERT INTO jobhist VALUES (7788,'05-MAY-90',NULL,'ANALYST',3000,NULL,20,'Promoted to Analyst');
INSERT INTO jobhist VALUES (7839,'17-NOV-81',NULL,'PRESIDENT',5000,NULL,10,'New Hire');
INSERT INTO jobhist VALUES (7844,'08-SEP-81',NULL,'SALESMAN',1500,0,30,'New Hire');
INSERT INTO jobhist VALUES (7876,'23-MAY-87',NULL,'CLERK',1100,NULL,20,'New Hire');
INSERT INTO jobhist VALUES (7900,'03-DEC-81','14-JAN-83','CLERK',950,NULL,10,'New Hire');
INSERT INTO jobhist VALUES (7900,'15-JAN-83',NULL,'CLERK',950,NULL,30,'Changed to Dept 30');
INSERT INTO jobhist VALUES (7902,'03-DEC-81',NULL,'ANALYST',3000,NULL,20,'New Hire');
INSERT INTO jobhist VALUES (7934,'23-JAN-82',NULL,'CLERK',1300,NULL,10,'New Hire');

COMMIT;

```

The discussion of basic SQL commands will begin in the next section.