08IntroductionToSQL

Introduction to Structured Query Laguage (SQL)

Outline

Structured Query Language
Characteristics
Functionality
Formatting

Structured Query Language (SQL) was developed and implemented by IBM corporation in mid 1970s by Donald D. Chamberlin and Raymond F. Boyce. It was originally called SEQUEL (Structured English Query Language).This is why, it is sometimes suggested that Q L supposed to be pronounced as sequel. Later on, due to the commercial reasons and a trademark conflict, a word: English, has been removed.

A word: query, in the name does not express well, what the functionality of the language really is. Initially, the language is supposed to be used for the purpose of database querying only. More functionality, that included data definition and data manipulation component have been added later on during the language standarisation process.

First implementations included IBM’s System R (later on renamed as DB2 and UDB) and Oracle's implementation in late 1970s.

The first ANSI and ISO standard was published in 1986 (SQL86) and the revisions followed in 1989, 1992, 1999, 2003, 2006, 2008, 2011 and the last one in 2016.

SQL is a command oriented, declarative, common for all relational database management system database programming language. "Command oriented" means that data processing operations are expressed as sequences of parameterized command. "Declarative" means that the programmers "declare" what data processing problem supposed to be solve without providing the algorithms how to solve the problem. It is the major difference between declarative and procedures programming languages like Java, Python, C, C++ etc where programming needs "plumbing" with variables, control structures, procedures, etc.

SQL due to its standarization supposed to be common fro all relational database systems. In fact every relational database system implements a large component of SQL standard and also some vendor dependent "extensions".

Structured Query Language

Defined and implemented by IBM in early 1970s
Originally called SEQUEL (Structured English QUEry Language)
First implementation: IBM’s SYSTEM R (DB/2, UDB)
The first ANSI and ISO standard in 1986 (SQL-86)
The revisions in 1989, 1992, 1999, 2003, 2006, 2008, 2011 and 2016
SQL is a command oriented, declarative, common for all relational database management system database programming language

Introduction to Structured Query Laguage (SQL)

SQL is commonly used to:

- create the databases and the objects within them,

- store data in the databases,

- change and analyse data,

- get data back in reports, web pages, etc.

MySQL, SQL is MySQL implementation of ANSI SQL standard. MySQL, SQL is close to, but it is not identical to ANSI SQL standard.

mysql command line interface is an enhancement of MySQL, SQL. It includes the commands not in ANSI SQL standard.

MySQL Workbench is a Graphical User Interface (GUI) to MySQLSQL.

S Q L Developer is another Graphical User Interface (GUI) to My SQLSQL.

All relational database systems implement different sorts of interfaces that can be used to process individual SQL statements and scripts with the sequences of SQL statements. None, of these interfaces is powerful enough to be used for implementation of real-world database applications. The real-world database applications are still implemented in the procedural programming languages with embedded SQL statements or with application generators, now almost only HTML based with small fragments of procedural code and SQL attached to HML code.

Characteristics

SQL is commonly used to:
- (1) Create databases and the objects within them
- (2) Store data in databases
- (3) Change and analyze data
- (4) Get data back in reports, web pages, etc
MySQL SQL is MySQL implementation of ANSI SQL standard
MySQL SQL is close to but it is not identical to ANSI SQL standard
mysql command line interface is an enhancement of MySQL SQL
SQL Developer is a Graphical User Interface (GUI) to MySQL SQL
MySQL Workbench is another Graphical User Interface (GUI) to MySQL SQL

Introduction to Structured Query Laguage (SQL)

Outline

Structured Query Language
Characteristics
Functionality
Formatting

SQL consists of the following groups of statements: data definition statements, data retrieval statements, data manipulation statements, access control statements and system administration statements.

For example, tata definition statements include:

CREATE TABLE statement, that can be used to create the relational tables together with the declarations of logical consistency constraints imposed on the contents of relational tables,

CREATE INDEX statement, that can be used to create the additional persistent storage structures to improve performance of database applications,

CREATE VIEW statement that can be sued to create virtual relational tables,

ALTER TABLE statement, that can be used to change the structures of relational tables and logical consistency constraints imposed on data stored in the relational tables,

DROP TABLE statement, that can be used drop the relational tables and to release all persistent storage occupied by the tables,

and the others.

All data definition statements form data definition language (DDL) of SQL.

Data retrieval statements include:

SELECT statement, that can be used to search the contents of a relational database,

WITH statement, that can be sued to implement complex searches through decomposition of SELECT statements,

MODEL statement, that cabe sued to formulate non standard queries,

and the others

All data retrieval statements form a query language of SQL.

Functionality

SQL consists of:
Data definition statements:
- CREATE TABLE,
- CREATE INDEX,
- CREATE VIEW,
- ALTER TABLE,
- ...
Data retrieval statements:
- SELECT
- WITH
- MODEL
- ...

Data manipulation statements include:

UPDATE statement, that can be used to modify the contents of relational tables,

INSERT statement, that can be used for data entry into relational tables,

DELETE statement that can be sued to delete the rows from reational tables,

and the other data manipulation statements.

All data manipulation statements form a data manipulation language, DML, of SQL.

The access control statements include:

GRANT statement and REVOKE statement that can be used to grant/revoke the data and functional privileges to/from database users.

The access control statements form the access control language of SQL.

Finally, system administration statements include: CREATE DATABASE statement, that can be used to create a new database,

CREATE TABLESPACE statement that cab used to provide the implementation details fro the relational tables and to determine the persistent stiorage devices used for storing teh contents of relational tables, indexes, etc,

ALTER TABLESPACE statement, that can be used to modifiy the implementation details,

CREATE SNAPSHOT statement, that can be sued and to store a snapshots of the contents of a database taken in the different moments in time,

and many other statements.

The system administration statements form database administration language of SQL.

Functionality

Data manipulation statements:
- UPDATE,
- INSERT,
- DELETE,
- ...
Access control statements:
- GRANT,
- REVOKE,
System administration statements:
- CREATE DATABASE,
- CREATE TABLESPACE,
- ALTER TABLESPACE,
- CREATE SNAPSHOT,
- ...

Introduction to Structured Query Laguage (SQL)

Outline

Structured Query Language
Characteristics
Functionality
Formatting

SQL is NOT case sensitive as long as case sensitivity is set up in a different way in a particular system. For example, in MySQL it is possible to make SQL statements case sensitive, by changing the system initialisation variables.

In MySQL installation on your virtual machine, the system initialisation variables are set such, that the relational tables and the names of columns are case sensitive.The keywords like: select, from, where and so on, are not case sensitive.

All select statements listed on the present slide are equivalent.

It is strongly recommended to use two dimensions when typing SQL statements. For example, it means that each clause of a select statement like: select, from and where should be typed in a separate line. Typing an entire select statement in one line is possible, but for complex statements you may get one long line like from Sydney to Melbourne, and that is very difficult to comprehend.

SQL statement are semantically very dense. It means that a lot of actions may be hidden behind a single clause of an SQL statement. This is why, a nice formatting of an SQL statement makes SQL programming easier.

Formatting

SQL is NOT case sensitive as long as case sensitivity is set up in a different way in a particular system, e.g. MySQL

SELECT EMPLOYEE.*, DEPARTMENT.*
FROM EMPLOYEE, DEPARTMENT
WHERE EMPLOYEE.dname = DEPARTMENT.dname;

SELECT EMPLOYEE.*, DEPARTMENT.*
from EMPLOYEE, DEPARTMENT
WHERE EMPLOYEE.DNAME = DEPARTMENT.dname;

select EMPLOYEE.*, DEPARTMENT.*
FROM EMPLOYEE, DEPARTMENT
WHERE EMPLOYEE.dname = DEPARTMENT.DNAME;

select EMPLOYEE.*, DEPARTMENT.*
from EMPLOYEE, DEPARTMENT
WHERE EMPLOYEE.DNAME = DEPARTMENT.DNAME;

Formatting

The literal values in MySQL SQL statements are case sensitive


SELECT CONCAT('Number: ', enum ), CONCAT('Full name :', ENAME)
FROM EMPLOYEE;


SELECT CONCAT('NUMBER: ', enum ), CONCAT('FULL NAME :', ENAME)
FROM EMPLOYEE;


SELECT CONCAT('Number: ', enum ), CONCAT('Full name :', ENAME)
FROM EMPLOYEE
WHERE DNAME = 'Sales';


SELECT CONCAT('Number: ', enum ), CONCAT('Full name :', ENAME)
FROM EMPLOYEE
WHERE DNAME = 'SALES';

Formatting

SQL statements are terminated with a semicolon
When a statement is terminated with a semicolon, then it is immediately processed by a database server

When a statement is not terminated with a semicolon then the command line interface opens a new line for continuation of the statement.


SELECT  ENUM "Employee number", ENAME "Full name" FROM EMPLOYEE;


SELECT ENUM "Employee number",
-> ENAME "Full name" 
-> FROM EMPLOYEE;

SQL statements can be formatted in any way as long as the keywords, operations and literals can be properly recognized by a compiler.

For example, formatting of the first three select statements in the current slide, is correct.The first three select statements retrieve the same results from a database.

On the other hand, formatting of the last select statement is incorrect. Formatting in the last select statement is incorrect, because of a missing blank between a keyword: FROM, and a name of relational table: EMPLOYEE. In all other cases, a compiler is able to identify the keywords and the other syntactical components of select statements. It means, that apart from a blank, a double quotation, and a comma can be used as a separator between the keywords and column names and aliases of column names (the strings enclosed within the double quotations).

Formatting

SQL statements can be formatted in any way as long as keywords operations and literals can be properly recognized by a compiler


SELECT ENUM "Employee number", ENAME "Full name" 
FROM EMPLOYEE;


SELECT ENUM "Number", 
       ENAME "Full name" 
FROM EMPLOYEE;


SELECT ENUM "Employee number", ENAME "Full name" FROM 
EMPLOYEE;

Formatting below is incorrect


SELECT ENUM"Employee number",ENAME"Full name"FROMEMPLOYEE;

References

C. Coronel, S. Morris, A. Basta, M. Zgola, Data Management and Security, Chapter 5 Cengage Compose eBook, 2018, eBook: Data Management and Security, 1st Edition
T. Connoly, C. Begg, Database Systems, A Practical Approach to Design, Implementation, and Management, Chapter 6.1 Introduction to SQL, Pearson Education Ltd, 2015
D. Darmawikarta, SQL for MySQL A Beginner’s Tutorial, Introduction, Brainy Software Inc. First Edition: June 2014