2.3 Intro to SQL/1

History of SQL

IBM developed Structured English Query Language as a part of R project. Later, it was named as SQL in the 1970s and 1980s. In 1989 , integrity constraints was introduced. In 1992, it was standardised using ISO 9075 standard, this is the major revision and we are following this using some revisions.

naturally SQL 1999 you can understand that this was getting close to year 2000. So, everybody was concerned with y2k. So, the naming of the standard also changed in the y2k four-digit year format.

This added several of very useful features like matching of regular expressions, recursive queries, that is which we will see is not possible in pure relational algebra that is transitive closure was provided as a part of SQL then triggers, support for procedural and control statements, which are SQL as I mentioned, or query languages, as I mentioned are declarative, but some procedural support is also provided. Arrays, structure types and most importantly, first a formal embedding mechanism was given in SQL, so that you can embed SQL queries in Java, which is called SQL will be and you can embed Java in SQL, which is called SQL JRT. Both of these embeddings are possible so that you can choose which part of the task you would like to do as relational operations or database operations and which part you would like to do in a standard programming language like Java. 2003 standard also added a very significant feature of XML. We will talk about XML being a standard format for Information Interchange. So, that was added in 2003 and in 2006, a full functionality of XML including, importing, storing, manipulating, exporting, publishing, all these were added. Subsequently several small but important updates continued to happen in SQL and the latest version of SQL is 2019.

Alternatives to SQL

Primarily SQL is used for most cases. There are some frontend softwares made which make SQL easy to write in different languages like Lisp, Haskell, Ruby , Scala.

  • Derivates using SQL : There are many derived query languages from SQL. But the most effective which stands out is SPARQL.


SPARQL has a recursive acronymn SPARQL Protocol and RDF Query Language. This has been used by the query languages for several NOSQL systems - particularly the Graph Databases that uses RDF (Resource Descriptive Framework) to store.

DDL - Data Definition Language

Domain Types in SQL

There are various types in SQL :

  • varchar(n) : In varchar(n), it can have any length upto ‘n’. But in char(n) it should have exactly ‘n’ number of characters.
  • char(n). Fixed length character string, with user-specified length n
  • smallint : Small integer (a machine-dependent subset of the integer domain type)
  • numeric(p, d) : Fixed point number, with user-specified precision of p digits, with d digits to the right of decimal point. (ex., numeric(3, 1), allows 44.5 to be stores exactly, but not 444.5 or 0.32)real, double precision. Floating point and double-precision floating point numbers, with machine-dependent precision
  • float(n): Floating point number, with user-specified precision of at least n digits

Create Table

Creating Schema is done using the “Create Table” command in SQL. We have to create the table with attributes and their types respectively.

CREATE TABLE INSTRUCTOR 
(ID CHAR(5),
NAME VARCHAR(20),
DEPT_NAME VARCHAR(20),
SALARY NUMERIC(8,2))

Here for the salary attribute numeric(8,2) means that it take up to 8 digits, and two decimals, after the period.

Integrity Constraints

These are used to specify that a field should not contain NULL values. - We should mention the Primary Key, primary key by default is not null. - We should maintain the Foriegn Key and its reference.

CREATE TABLE INSTRUCTOR 
(ID CHAR(5),
NAME VARCHAR(20) NOT NULL,
DEPT_NAME VARCHAR(20),
SALARY NUMERIC(8,2)
PRIMARY KEY (ID),
FOREIGN KEY (DEPT_NAME) references department 
)

Update Table

We can update table using the following operations :

  • Insert : Insert instance into a table. Ex : insert into instructor values(‘101’,‘Smith’,‘Maths’,50000)
  • Delete : We can remove all the tuples from a table.
  • Drop Table : We can delete the whole table and it will be removed from data dictionary itself.
  • Alter Table : Where we can add an attribute in a table.
alter table r add a

where add a is used to edit the schema. While adding the attributes , initially these should be nullable.

alter table r drop A

DML : Data Manipulation Language

Basic SQL Query Structure is in the form : 

select A1,A2,...An
from r1,r2,...rn
where P

where Ai represents an attribute from ri’ s. ri represent a relation P is a predicate , which is nothing but a boolean condition.

SELECT CLAUSE

Select Clause mentions the list of attributes required in the query. It corresponds to the projection operation in Relational Algebra.

SQL names are case insensitive.

SQL will show duplicate entries in the result. To display unique values without duplicates, we can use the “distinct” keyword which will remove the duplicates.

select distinct dept_name from instructor


To show the duplicates explicitly we can use the all command.

select all dept_name from instructor
  • An asterisk in the select clause uses all the attributes.
select * from instructor
  • We can select a literal using the select clause.
select '437'
  • We can use the arithmetic expressions in the Select clause and even rename it using the as clause.
select ID,name,salary/12 as monthly_salary

WHERE Clause

WHERE Clause is used to specify the conditions that should be resulted or displayed. The result of the predicate is always True or False. - WHERE Clause corresponds to selection predicate of the relational algebra.

select name 
from instructor 
where dept_name = 'Comp_Sci'

FROM Clause

FROM Clause tells us the scope of the relations we are dealing with from one or multiple tables. This corresponds to the Cartesian Product operation in the Relational Algebra.

select * 
from instructor , teaches

Note : We have to put a WHERE Clause to make it effective and simpler.