Primary database objects are: Tables, Columns, Indexes. Tables are organized in rows and collumns. 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 accesible to the system. An administrator or an application can quary these tables and improve the application.
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 in applications
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:
- into a console application;
- database setup script;
- database evolution script;
- stored procedures;
- embedded into applications;
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. Youu 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.
sql:>select “hello world” from dual;
Using SQL in applications
Programmers can create SQL using a programming language. For this we concatenate strings and we create a SQL text statment. 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.
Basic Syntax Elements
SQL Syntax has several basic commands. With this we execute actions into a database. We have 4 basic groups of commands. For every group we have one SQL dialect:
- DML: Data Manipulation Language
- DDL: Data Definition Language
- TCL: Transaction Control Language
- DCL: Data Control Language
The SQL syntax is not case-sensitive. We can write SELECT or select. Most of the databases will understand both keywords. The table names and column names is usually case-sensitive.
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
Here is the syntax example for SELECT:
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.
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.
TCL is the short name of Transaction Control Language which deals with a transaction within a database.
- COMMIT – make changes done in transaction permanent;
- ROLLBACK – cancel all modifications and restore database state;
- SAVEPOINT – specify a point in transaction to which you can rollback.
DCL is short name of Data Control Language. This is mostly concerned with rights, permissions and other controls of the database system.
- GRANT – allow users access privileges to database
- REVOKE – withdraw users access privileges given by using the GRANT command
See also: Oracle SQL reference