Protected by Copyscape Web Copyright Protection Software

Search This Blog

Tuesday, December 3, 2013

SQL Lesson 1

What is SQL?

            SQL means Structured Query Language. It is a special-purpose programming language designed for managing data held in a relational database management system (RDBMS).
Through SQL we can define the data as well as manipulate, share and control.

SQL Overview:

SQL was initially developed at IBM by Donald D. Chamberlin, Donald C. Messerly, and Raymond F. Boyce in the early 1970s. This version, initially called SEQUEL (Structured English Query Language), was designed to manipulate and retrieve data stored in IBM's original quasi-relational database management system, System R, which a group at IBM San Jose Research Laboratory had developed during the 1970s. The acronym SEQUEL was later changed to SQL because "SEQUEL" was a trademark of the UK-based Hawker Siddeley aircraft company.

In the late 1970s, Relational Software, Inc. (now Oracle Corporation) saw the potential of the concepts described by Codd, Chamberlin, and Boyce and developed their own SQL-based RDBMS with aspirations of selling it to the U.S. Navy, Central Intelligence Agency, and other U.S. government agencies. In June 1979, Relational Software, Inc. introduced the first commercially available implementation of SQL, Oracle V2 (Version2) for VAX computers.

After testing SQL at customer test sites to determine the usefulness and practicality of the system, IBM began developing commercial products based on their System R prototype including System/38, SQL/DS, and DB2, which were commercially available in 1979, 1981, and 1983, respectively.

For additional information see here. http://en.wikipedia.org/wiki/SQL.

Most of the relation data base management systems ex; My SQL, Sybase, Oracle, MS Access, Informix etc use SQL as Standard Data base Language.


We launched material for SQL with updated features. With this material, you would be able to learn SQL from Basic standard to an extent where you can develop SQL Queries by adding up Knowledge. We are also provides Interview Questions, Quizzes and many more.

After reading this post you will learn following things.

What is Data, database and DBMS?
What is RDBMS?
What are Database Objects?
What are DDL, DML, DQL, DCL and TCL?
What are Constraints, Clauses?
What are SQL Functions?
What are Operators in SQL?
What are Joins?
What are Sub-Queries?
What are Synonym, View, Sequence, Index and Set Operators?



What is Data and database and DBMS?
            Collection of information is called Data. To store the data we need Database.
A Data Base is an Organized Collection of Data which can be easily accessed, managed and updated.
To control the data in Database we need one system. i.e DBMS.

DBMS means Data Base Management System, is used to control and manage the data in Database.
Database management systems (DBMSs) are specially designed applications that interact with the user, other applications, and the database itself to capture and analyze data. A general-purpose database management system (DBMS) is a software system designed to allow the definition, creation, querying, update, and administration of databases.

What is RDBMS?

A relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd, of IBM's San Jose Research Laboratory. Many popular databases currently in use are based on the relational database model.

What are Database Objects?

A logical entity created and stored in a database. In Sql the database objects are Tables, views, synonyms, indexes, sequences, stored procedures, materialized views, and triggers …etc are all examples of database objects.

Let’s start the Sql lessons here….

Lesson 1: 28-11-2013:

Data Types

In Sql, Data will store in Tables. It has different data types. Data type represents the type of data that is being stored or used. The commonly used data types are Char, Varchar, Varchar2, Number and Date. Apart from those some other data types are also there in Sql, like LOB, NCLOB, BLOB, LONG, RAW, LONG RAW etc….

Normally while creating the table we will use these data types. We can discuss about what is table later. Let me explain about data types clearly.

Char data type is used to store the character information. It is a fixed length data type.
Syntax: column_name CHAR (Size)

Here Size represents the no of characters that column can hold.
Ex: invoice_num char (10)

In above example we can insert data in this column up to 10 characters, we can’t enter more than 10 characters. If we insert invoice_num as ‘Test1’ then it will occupy 10 characters even though it has 5 characters because it is fixed length data type. So most of the times we use it whenever we know the size of the column fixed like Gender (M or F). In 11g the max size of CHAR is 2000.

VARCHAR and VARCHAR2

These two data types are used to store the character information but these are varying length data types. Normally we use VARCHAR2 because VARCHAR is reserved for future.

Syntax: column_name varchar2 (size)

Here Size represents the no of characters that column can hold.
Ex: invoice_num varchar2 (10)
In Oracle 11g the maximum size is 4000.
In above example If we insert invoice_num as ‘Test1’ then it will occupy only 5 characters not like CHAR.

Note: While inserting data for CHAR, VARCHAR, VARCHAR2 and DATE Single Quotes (‘ ‘) required.

Use ‘Test’ instead of Test

NUMBER

This data type is used to store the numeric information. It will accept all positive and negative numbers as well as decimals also.

Syntax: column_name Number or column_name Number (P, S)

Here P means Precision and S means Scale. Precision means before decimal point and scale means after decimal point.

Ex: Amount_Paid number (5, 2)

In the above example we can insert like this….

Amount_Paid(4351.95)

In 11g the maximum size of Number is 38

DATE

This data type is used to store the date information. This will accept different date formats.
Syntax: column_name date

No need to specify size for this. By default it will take 9 bytes.

Ex: Invoice_Date date

While inserting it will accept like this..

’28-Nov-2013’ or ‘28/Nov/13’ or ’28-Nov-13’

RAW and LONG RAW

These data types are used to store the images.