We would now start with setups here.
There are various databases available some of which are designed for enterprises and some are for personal uses. There are various SQL versions(revisions) which your can read here. Each database supports one of the SQL standard. We would be making sure SQL which we provide here are compatible with LibreOffice Base, SQLite and Oracle.
Let us first understand different databases an their uses.
If you do not have much of technical background, you can start with something simple like LibreOffice Base or OpenOffice Base. You can also work with SQLite. SQLiteStudio is a GUI tool which allows us to create and manage SQLite database.
If you do have good understanding of database, you can start with Oracle. You can also you LiveSQL by Oracle if you do not want to install.
For the purpose of the this tutorial we would be primarily working with SQLiteStudio.
Now we need some data we can play around. We are going to use Employee and Department table to start with.
Step 1: Kindly install latest version of SQLiteStudio before we proceed further. FYI I am using SQLiteStudio version 3.3.3 and SQLite version 3.35.4. (Click on Help > About to check version of SQLiteStudio and Environment tab to check version of SQLite). You can also choose any of the above mentioned tools or tools of your choice with your chosen database.
Note: There would be some syntax differences between various databases. I’ve tried to provide scripts on Github here
Step 2: Now we that we have install tools, let us create our first database. To create database in SQLiteStudio click on File > Add Database or press Ctrl + O to open dialog box which should look similar to this
Please select “SQLite 3” for Database type. In case you already have SQLite database, please click on folder Icon in File section. In case you want to create new SQLite database, please click on green plus icon in File section. Select name and location of where you already have database or you want to create new one.
I’ve saved database “test.db” in my Documents folder and given alias “test”. The alias provided here is helpful in finding right database to open within SQLiteStudio.
Click on OK to create database.
Step 3: Now we should start creating tables and providing data in tables. We would use emp and dept table for our tutorial. These tables are simple and have been used for years to learn SQL.
Please click on File > Connect to the database to proceed further.
You should see something as follows on your screen.
Query tab, next to History tab, allows you to type commands and section at the bottom will show you results of you query in Grid view. You will now need to copy entire script in to Query tab.
CREATE TABLE dept
(
deptno NUMBER(2, 0),
dname VARCHAR2(14),
loc VARCHAR2(13),
CONSTRAINT pk_dept PRIMARY KEY (deptno)
);
CREATE TABLE emp
(
empno NUMBER(4, 0),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4, 0),
hiredate DATE,
sal NUMBER(7, 2),
comm NUMBER(7, 2),
deptno NUMBER(2, 0),
CONSTRAINT pk_emp PRIMARY KEY (empno),
CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept (deptno)
);
INSERT INTO dept (deptno, dname, loc)
VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept (deptno, dname, loc)
VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO dept (deptno, dname, loc)
VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO dept (deptno, dname, loc)
VALUES (40, 'OPERATIONS', 'BOSTON');
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-5-1', 2850, NULL, 30);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-6-9', 2450, NULL, 10);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-4-2', 2975, NULL, 20);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-07-13', 3000, NULL, 20);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-3', 3000, NULL, 20);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-2-20', 1600, 300, 30);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-2-22', 1250, 500, 30);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-9-28', 1250, 1400, 30);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-9-8', 1500, 0, 30);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-7-13', 1100, NULL, 20);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-3', 950, NULL, 30);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-1-23', 1300, NULL, 10);Select script contents by pressing Ctrl + A and click on “Execute query” or press Press F9 to run.
You should now see status bar updated with following
Now you can clear the script by pressing delete key. This will only clear script text from Query text.
Now you can run following (one by one) to see whether all records are inserted.
select * from emp;This should return 14 records.
select * from dept;This should return 4 records.
This is good now we have a database setup, we also have tables created and data inserted into them.
We can start playing with SQL in next part.