SQL 101

In this post I will talk about very basics of Oracle SQL


1. Table is made of row and columns.
2. Each row contains exactly one record known as tuple.
3. A column is made up of a column name and a data type, and it describes an attribute of the tuples.
4. The structure of a table, also called relation schema, thus is defined by its attributes
5. The type of information to be stored in a table is defined by the data types of the attributes at table creation time.
6. SQL uses the terms table, row, and column for relation, tuple, and attribute, respectively

Few Properties of table:
1. A table doesn't have duplicate tuples.
2. Order in which tuples appear is not relevant, unless a query requires a explicit sorting.

A database schema is a set of relation schemas. The extension of a database schema at database
run-time is called a database instance or database, for short.

Simple Queries

a) Selection of tuples:

select [distinct] <coloumn(s)> from <table> [where <conditions>] [order by <coloumn(s) [asc|desc]>]

Select * from EmployeeTable;
This will select all tuples from the table.

We can also perform arithmetic operation

Select EmpId, Salary*1.5 from EmployeeTable;

We can also get sorted output using following query:

Select * from EomployeeTable orderby deptId, hiredate desc;

Here, output will be sorte (by default ascending) according to department Id, and if department id of two entries are same, then their order will be decided by hire date in descending order.

We can also write query to filter out tuples based on conditions. For example:

Select * from EmployeeTable where (EmpId>400 and EmpId <455) or Salary > 55000;

Some more examples are

Select * from EmployeeTable where EmpId in (200,300);

Select * from EmployeeTable where HRA is not null;

We can also filter out tuples based on pattern of string of particular strings.
For example:

Select * from Employee Employeename like %C_%C;

%C%C means any string that contains two C
%C_%C means one character between two C