index<--

SQL Overview

SQL is a 4'th generation programming language designed for rapid developement of data centric applications. It was designed to resolve complex problems by describing the problem and not by writing an algorithm. The will compile your query in memory and will create an algorithm to resolve the problem.

Data Concepts

Before diving into SQL syntax we must review several basic concepts abut data. Without these concepts is likely you will not understand SQL.Learning concepts is borring so I will use diagrams and examples to add a bit of color.I promice will be fun. Let's get started:

Database

SQL is used to maintain and use a database. Now a database represents a data storage system.Is actually a software that has specific features and utilities to store and use data efficiently.A database is useful but difficult to implement. We need SQL to create database structure and operate the data content.

Life-Cycle

One database has usually a long life. Once created it can function for many years without intreruptions.This require significant effort for developers to create a robust database. Like a living organism,a database is born, it lives, it ages and eventually dies. Let's look closer to its life phases:

DB LifeCycle

Phases

Design: require analysis and modeling;
Build: assume software codding and implementation;
Operate: consist of initialization and exploitation;
Maintain: require backups and structure updates;
Optimize: consist of performance monitoring and reporting;
Retire: assume dara archive and storage cleanups.

Data Source

SQL works with primary database source object called tables. A database has other objects like types, users and views, therefore is only logical SQL can manipulate data using these kind of objects as data source or data destination.

Database Tables: are organized in rows and columns. Each database object has a name and a definition. The object definition is described using SQL language.

Database Objects: are stored in system tables or metadata. These are database default tables accessible to the system. An administrator or an application can query these tables and improve the application.

Other objects: Most relational databases can use stored procedures, functions and custom types. To create stored procedures we use PL/SQL programming language specific to databases.

Using SQL

To create a SQL command we use plain text editor. A SQL command can be a single line or multiple lines of text terminated with semicolon. We can run SQL using a console application, desktop application or a dynamic website.

SQL can be used in several ways:

to type in a SQL console application;
to build an applications embedding SQL;
to make a database setup script;
to make a database evolution script;
to make several stored procedures;

Execution:

User can start a database specific console tool and is can write a query or load a query from a file. The console tool send the query to the server, it is executed and the console display data. The user will be able to see the data as the console displays it.

For Oracle console tool is sqlplus.exe. You can start this if Oracle Client is installed on your computer. After you connect to a database using the user and password you can use the console.

Example:

c:\workfloder>sqlplus user_name/password@database_service_name
sql:>select "hello world" from dual;
hello world

Embedded SQL

Programmers can create SQL using a programming language. For this we concatenate strings and we create a SQL text statement. This can be send to server for execution. Programmer use a library (database driver) to interact with the database.

The server is parsing the SQL string and is sending the results back to the driver. The result is divided into packages. The client request a number of packages and use them. This is called client-server application.

Data communication between the driver and database is usually in binary format and sometimes is encrypted. Modern databases can use compressed JSON for data communication.

Syntax Rules

1. SQL is case insensitive. That means keywords in SQL can use either capidal letters or lowercase letters.In the past people prefered to use capital letters for keywords and lowecase letters for identifiers and names.This concention has faded and new languages use lowercase for keywords. In this tutorial we will use the old concention.

2. SQL is free form language. That means spaces are not significant. One SQL statement can use one single line or multiple lines of code.It is necesary to use one single space as separator between keywords and identifiers.

3. SQL statement is ending with semicolumn ";". If you do not use semicolumns the statement will not execute and will be considered part of next statement.Even if you separate statements with comments or empty lines, these will be ignored and will not end the statement.

4. Database objects have names that can be case sensitive and can contain spaces. In this case you must enclose the object name in double quotes like: "Table Name". So these two objects are the same: PERSONAL = personal, but not the same with "Personal".

Caution: The SQL statements are similar but differend depending on database implementation. The principles are the same but keywords, syntax and data types can be different. Our introductory examples will use MySQL syntax.

Syntax Elements

SQL Syntax consist of several commands used to create specific scripts for each phase of database life-cycle. SQL have 4 groups of commands, each is considered a different language: {DDL, DML, TCL, DCL}. Every of these sub-languages target a different category of people as follows:

Database users can learn only DML.
Developers should learn all these languages, especially DDL, DML and TCL.
Database administrators should focus on learning DCL.

SQL Commands

DDL

DDL is short name of Data Definition Language, which deals with database schemas and descriptions, of how the data should reside in the database. Using these keywords we manipulate database objects: tables, indexs, views, procedures, functions, triggers;

CREATE - to create database and its objects;
ALTER - alters the structure of the existing objects;
DROP - delete objects from the database;
TRUNCATE - remove all records from a table.

Note: These keywords can be used with specific objects. For example you can't alter a view you need to recreate the view instead. And you can't truncate a view or a stored procedure but you can drop a view or procedure.

Example:

-- create our first table
CREATE TABLE personal (
    name   VARCHAR(50) NOT NULL, 
    age    INT NOT NULL, 
    salary INT NOT NULL
);   

CREATE index PERSONAL_NAME on personal(name);

DML

Data Manipulation Language cover several actions: data search, data creation, data update, data removal.

SELECT - execute a query
INSERT - create a new rows into a table
UPDATE - modify one or more values in a table row
DELETE - remove a row from a table

Example:

-- our first select statement
SELECT name, age, salary
    FROM personal
    WHERE salary >= 2000 and salary <= 4000;

In example we use SELECT, FROM, WHERE keywords. The name of the columns are lowercase and separated by coma: name, age, salary. We use table personal to search persons with salary between 2000 and 4000. A command must end with semicolon.

TCL

TCL is the short name of Transaction Control Language which deals with a transaction within a database.

START TRANSACTION - begin a transaction;
COMMIT - make changes done in transaction permanent;
ROLLBACK - cancel all modifications and restore database state;
SAVE-POINT - create a point transaction bookmark to which you can rollback.

Example:

-- our first insert statement
START TRANSACTION

INSERT INTO personal (
   ,name, 
   ,age, 
   ,salary)
VALUES (
    "Elucian"
    "55"
    "0"
);
    
COMMIT;

DCL

DCL is short name of Data Control Language. This is mostly concerned with rights, permissions and other controls of the database system. By default when you create a database nobody but the administrator have access to it. You must use DCL to enable specific users with specific roles to access your data.

GRANT - allow users access privileges to database
REVOKE - withdraw users access privileges given by using the GRANT command

Example:

-- our first dcl script
CREATE ROLE salary-app;
CREATE USER elucian;


-- role privilages
GRANT salary-app TO elucian
GRANT SELECT ON TABLE personal TO salary-app;

-- user privilages
GRANT UPDATE ON TABLE personal TO elucian;
GRANT INSERT ON TABLE personal TO elucian;
GRANT DELETE ON TABLE personal TO elucian;

Note: But of course these examples are not nearly enaugh to build, operate and maintain your database.We will cover each statement in turn while we build an example database with all required scriptsfor a real application. Let's start with the design then.

Read next: Build