Pages

Thursday 17 October 2013

SELECT command of SQL



      The select command of SQL is used to retrieve data from the tables of the database and display those data in a table format.

      The capabilities of SQL select statements are as mentioned below

1.      Projection
2.      Selection
3.      Join

      The general form of select statement is shown below. 

               
SELECT  *|{[DISTINCT] column|expression [alias],...}
  
FROM tableName;
        
       
      In above general form:

1.      The SELECT indentifies which column to select from the table specified.

2.      The FROM indentifies which table to chose from the database.

      Now lets’ analyze the statement SELECT  *|{[DISTINCT] column|expression [alias],...}, first we will start with the ‘ * ‘ operator. 

 The ‘ * ‘ operator in the SELECT statement:

     When the ‘ * ‘ operator is used all the columns and its respective values present in the table are retrieved. The below example shows you the use of ‘ * ‘ operator.


select * from departments;
       

     Here, all the columns and its respective data from the departments table is retrieve and displayed as shown in the below table image.




The ‘ DISTINCT ‘ keyword used in the SELECT statement:

     The DISTINCT keyword is used to eliminate the duplicate rows in the result of the select statement.
 
      Consider the below query which is written without DISTINCT keyword. 

        
select department_id

from employees;
  

      You can see the output where the ‘ department_id ‘ column displays many rows with duplicate values.
       
      


                   
      Now consider the same query where duplicate rows are eliminated using DISTINCT keyword.

select DISTINCT department_id

from employees;
  
     In the result you can see all the duplicate values displayed in previous result is eliminated, only the distinct values are displayed.
               
   

Tuesday 15 October 2013

Describe Command in SQL



           This is a SQL command used to display the structure of a table present in the database. 

General Form of DESCRIBE command:

              DESCRIBE tableName

           Consider the below example showing the use of DESCRIBE command to display the structure of  the employees table present in the database of my Oracle 10g express edition DB software.

               DESCRIBE employees

Output of above statement:


               
           You can see in above output only the internal structure of employees table is displayed. It shows different columns present in tables and the constraints, data-types of column, length of data-types etc. Here the data present in the table is not displayed but only the internal structure of the table is displayed.

          In some situation you will not be aware of the internal structure of a table. In such conditions the DESCRIBE command will be a great help to you. Consider that I have to display only a few columns present in the table say employees and its data. But am not aware how these columns are internally named for employees table present in a database. In such situation I can make use of DESCRIBE command. 

          Below you can see the use of SELECT statement used to display only two particular columns of  employees table and its data.

            select first_name, salary from employees

Note: SQL commands are not case sensitive.

Output of above statement:


Sunday 13 October 2013

RDBMS Concepts



Relational Database:

       A relational database is a collection of relations or two-dimensional tables. The database management system (DBMS) of a relational database is called relational database management system (RDBMS).  The RDBMS is software of relational database. The below table list some of the popular RDBMS software.

Database Name
Oracle
MySQL
Microsoft SQL Server
MS Access
DB2
SQL Anywhere (Sybase)
PostgreSQL
Teradata
Informix
Apache Derby
Derby or Java DB

Terminology: 

        The relational database theory uses several terminologies. These relational database terminologies are equivalent to the SQL database terminologies. The table below lists some of the most important relational database terms and their SQL database equivalents.  

SQL
Relational Database
Description
Table
Relation
Object of database. It is data structure
Row
Tuple
Horizontal data entries in a table
 Column name and Column value
Attribute name and Attribute value
Labeled members in a table and values under that label
Set of column names
Relation scheme or attributes set
A Schema

Objects of RDBMS: 

      The below table show you the objects of RDBMS.

Objects
Description
Tables
The basic unit of storage. It composed of rows and columns
View
The Logical representation of subsets of data from one or more tables
Sequence
used to generate primary key values
Index
It improves the performs of some queries
Synonym
Alternative name given to an object


Constraints Applied to Tables:

       The constraints are used to enforce rules at table level. They also prevent deletion of tables if there is any dependency. The below table list the valid constraint types.

Constraints
Description
NOT NULL
It does not allow null values in table
UNIQUE
It enforces uniqueness in the column in which they are specified. It allows null value and create non-cluster index
Primary Key
It is the a column or set of column that uniquely identifies the rest of the data in any given row. It does not allow null value and create cluster index.
Foreign Key
A foreign key is a column in a table and this column is a primary key of another table.
Check
Using this constraint user can restrict possible attribute values for a column admissible once.


How a database table looks like? 

       The below shown is the representation of RDBMS table when you use Oracle Database 10g express edition.