SQL

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:

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.

 

Lesson 2:

SQL Architecture

 

In SQL TABLE has more importance. TABLE is a database object which is used to store the data in rows and columns wise. A table should have at least one column and maximum of 30 columns.

Table name consist names, numbers and some symbols also. The maximum length of table name is 30 characters.

We can classify SQL into 5 groups based on their nature. Those are called SQL Statements. They are:

DDL, DML, DQL/DRL, DCL and TCL. So total SQL is working based on these 5 statements.

I)  DDL

DDL means Data Definition Language. It is used to define the Data base Objects. There are 5 commands in DDL. These statements define the structure of database.

The DDL commands are 1) Create 2) Alter 3) Rename 4) Drop and 5) Truncate.

Let me explain these commands thoroughly.

1)   Create

This CREATE command is used to create a new Table or View or any other database object. We already discussed about Table. We will discuss about View later. So to create a new Table we will use CREATE command.

Syntax: CREATE TABLE <Table_Name>(<column1> <data type(size)>,

  <column2><data type(size)>);

Here we are creating table. <Table_Name> represents name of table. <column1> represents name of first colimn and <data type(size)> represents data type and its size.

Ex: CREATE TABLE EMP (EMPNO NUMBER(4,0),

                  ENAME VARCHAR2(10),

                  JOB VARCHAR2(9),

                  MGR NUMBER(4,0),

                  HIREDATE DATE,

                  SAL NUMBER(7,2),

                  COMM NUMBER(7,2),

                  DEPTNO NUMBER(2,0)

                 );

We can run this query in Toad or SQL Developer or SQL Plus. Toad means Tool for Oracle Application Development. We do all SQL operations in any one of above. The Sql Developer and SQL Plus icons are shown below.

Double click on any icon and give username/password@databasename then it will connect to database. Then we can do SQL Operations in that. Write the above Ex: table script in SQL Developer and say control+enter then table will create in our database.

 

After creating table  we can run SELECT * FROM EMP;

Here it shows only structure without data because there is no data in that table. We can also see the structure with below command.

DESC EMP;

In table Description it shows all column names and data types and their sizes.

Note: While creating table we should keep in mind the following points.

ü  The name should begin with a letter A-Z

ü  Don’t use same name of existing object

ü  It can’t be more than 30 characters

ü  It can contain numbers and some symbols also

ü  It is not case sensitive.

ü  Don’t use oracle keywords like update, delete, as, rowed, rownum etc

Case sensitive means “Text sometimes exhibits case sensitivity; that is, words can differ in meaning based on differing use of uppercase and lowercase letters. Words with capital letters do not always have the same meaning when written with lowercase letters”.

Now we have EMP table. If we try to create another table with same name the following error will occur.

Create a table from another table:

By using below syntax we can create table based on existing table.

Create table emp1 as select * from emp;

Here emp is already existing table. Here ‘*’ represents all columns. If we want some columns only, then we use like below.

Create table emp2 as select empno,ename,sal from emp;

In the above two cases if we data in emp table those data also copied to new table. If we need to create table with out data based on existing table, then use below syntax.

Create table emp3 as select 8 from emp where 1=3;

In above example “where 1=3” is a false condition. So it will copy only Structure. We will discuss about WHERE condition in coming classes.

Lesson 3:

2)   Alter

This is the second command in DDL. It is used to alter/change the structure of data base object. For example if we want to change the name of table or data type then we will use this command.

Again this ALTER has 4 commands. Those are i) Add ii) Rename iii) Modify iv)Drop.

i) Add: By using this command we can add new columns to existing table.

Syntax: Alter table table_name add column_name datatype;

For two or more columns: Alter table table_name add (col1 datatype,col2 datatype);

Ex: Alter table EMP add(gender char,place varchar2(100));

After add check like below.

Even though we have data in table we can add new columns to that table.

ii)     Rename: By using this command we can change the name of a column.

Syntax: Alter table table_name RENAME column old_column_name To new_column_name

Here column is a keyword should required.

Ex: Alter table EMP rename column gender to sex;

After rename then check like below.

DESC EMP;

 
Lesson 2
Lesson 3
Lesson 4
Lesson 5
 Lesson 6
Lesson 7