1/28/16

SQL INTRODUCTION

SQL is divided into the following


Ø  Data Definition Language (DDL)

Ø  Data Manipulation Language (DML)

Ø  Data Retrieval Language (DRL)

Ø  Transaction Control Language (TCL)

Ø  Data Control Language (DCL)


DDL -- create, alter, drop, truncate, rename

DML -- insert, update, delete

DRL -- select

TCL -- commit, rollback, savepoint

DCL -- grant, revoke


CREATE TABLE SYNTAX


Create table <table_name> (col1 datatype1, col2 datatype2 …coln datatypen); Ex:

SQL> create table student (no number (2), name varchar (10), marks number (3));


INSERT


This will be used to insert the records into table.

We have two methods to insert.

Ø  By value method

Ø  By address method


a)  USING VALUE METHOD


Syntax:

insert into <table_name) values (value1, value2, value3 …. Valuen);





Ex:

SQL> insert into student values (1, ’sudha’, 100);

SQL> insert into student values (2, ’saketh’, 200);


To insert a new record again you have to type entire insert command, if there are lot of records this will be difficult.

This will be avoided by using address method.


b) USING ADDRESS METHOD


Syntax:

insert into <table_name) values (&col1, &col2, &col3 …. &coln);

This will prompt you for the values but for every insert you have to use forward slash.


Ex:

SQL> insert into student values (&no, '&name', &marks);


Enter value for no: 1

Enter value for name: Jagan

Enter value for marks: 300

old  1: insert into student values(&no, '&name', &marks)

new  1: insert into student values(1, 'Jagan', 300)


SQL> /

Enter value for no: 2

Enter value for name: Naren

Enter value for marks: 400

old  1: insert into student values(&no, '&name', &marks)

new  1: insert into student values(2, 'Naren', 400)


c) INSERTING DATA INTO SPECIFIED COLUMNS USING VALUE METHOD


Syntax:

insert into <table_name)(col1, col2, col3 … Coln) values (value1, value2, value3 ….

Valuen);





Ex:

SQL> insert into student (no, name) values (3, ’Ramesh’);

SQL> insert into student (no, name) values (4, ’Madhu’);


d) INSERTING DATA INTO SPECIFIED COLUMNS USING ADDRESS METHOD


Syntax:

insert into <table_name)(col1, col2, col3 … coln) values (&col1, &col2, &col3 …. &coln); This will prompt you for the values but for every insert you have to use forward slash.

Ex:

SQL> insert into student (no, name) values (&no, '&name');

Enter value for no: 5

Enter value for name: Visu

old
1: insert into student (no, name) values(&no, '&name')
new
1: insert into student (no, name) values(5, 'Visu')
SQL> /

Enter value for no: 6

Enter value for name: Rattu

old
1: insert into student (no, name) values(&no, '&name')
new
1: insert into student (no, name) values(6, 'Rattu')
SELECTING DATA

Syntax:


Select * from <table_name>;
-- here * indicates all columns

or

Select col1, col2, … coln from <table_name>;


Ex:

SQL> select * from student;


NO NAME
MARKS
--- ------
--------





1
Sudha
100
2
Saketh
200
1
Jagan
300
2
Naren
400

3     Ramesh

4     Madhu

5     Visu

6     Rattu


SQL> select no, name, marks from student;


NO NAME
MARKS
---
------
--------
1
Sudha
100
2
Saketh
200
1
Jagan
300
2
Naren
400

3     Ramesh

4     Madhu

5     Visu

6     Rattu


SQL> select no, name from student;


NO NAME

--- -------

1     Sudha

2     Saketh

1     Jagan

2     Naren

3     Ramesh

4     Madhu

5     Visu

6     Rattu

0 comments:

Post a Comment