Scribd
Upload
14 views7 pages

SQL Commands

The document provides an overview of SQL commands, including Data Definition Language (DDL), Data Manipulation Language (DML), and Transaction Control Language (TCL). It demonstrates how to create, alter, insert, update, delete, truncate, and drop tables, specifically focusing on a 'student' table and a 'customers' table. Additionally, it covers the use of commit and rollback in transaction control.

Uploaded by

asmwcnaaccomputerscience
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
14 views7 pages

SQL Commands

The document provides an overview of SQL commands, including Data Definition Language (DDL), Data Manipulation Language (DML), and Transaction Control Language (TCL). It demonstrates how to create, alter, insert, update, delete, truncate, and drop tables, specifically focusing on a 'student' table and a 'customers' table. Additionally, it covers the use of commit and rollback in transaction control.

Uploaded by

asmwcnaaccomputerscience
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

1.

SQL COMMANDS

1. DDL COMMANDS
2. DML COMMANDS
3. TCL COMMANDS

CREATE:

SQL>create table student(sname varchar2(10),regno number(5),m1 number(5));

Table created.

SQL> desc student;


Name Null? Type
----------------------------------------- -------- -----------------
SNAME VARCHAR2(10)
REGNO NUMBER(5)
M1 NUMBER(5)

ALTER:

SQL> alter table student add(m2 number(5),m3 number(5),total number(5));

Table altered.

SQL> desc student;


Name Null? Type
----------------------------------------- -------- ----------------------------
SNAME VARCHAR2(10)
REGNO NUMBER(5)
M1 NUMBER(5)
M2 NUMBER(5)
M3 NUMBER(5)
TOTAL NUMBER(5)

SQL> insert into student values('prathika',101,70,80,90,300);

1 row created.

SQL> insert into student values('sanjana',102,80,90,60,400);


1 row created.

SQL> insert into student values('pavithra',103,60,70,80,500);

1 row created.

SQL> insert into student values('kavi',104,80,50,70,200);

1 row created.

SQL> select * from student;

SNAME REGNO M1 M2 M3 TOTAL


---------- ---------- ---------- ---------- ---------- ---------- ------------
prathika 101 70 80 90 300
sanjana 102 80 90 60 400
pavithra 103 60 70 80 500
kavi 104 80 50 70 200

TRUNCATE:

SQL> truncate table student;

Table truncated.

SQL> select * from student;

no rows selected.

DROP:

SQL> drop table student;

Table dropped.
2. DML COMMANDS

SQL>create table student(sname varchar2(10),regno number(5),m1 number(5),m2 number(5),m3


number(5), total number(5));

Table created.

INSERT:

SQL> insert into student values('prathika',101,70,80,90,300);

1 row created.

SQL> insert into student values('sanjana',102,80,90,60,400);

1 row created.

SQL> insert into student values('pavithra',103,60,70,80,500);

1 row created.

SQL> insert into student values('kavi',104,80,50,70,200);

1 row created.

SQL> select * from student;


SNAME REGNO M1 M2 M3 TOTAL
---------- ---------- ---------- ---------- ---------- ---------- -------------
prathika 101 70 80 90 300
sanjana 102 80 90 60 400
pavithra 103 60 70 80 500
kavi 104 80 50 70 200

UPDATE:

SQL> update student set m2=95,m3=65 where sname='prathika';

1 row updated.

SQL> select * from student;

SNAME REGNO M1 M2 M3 TOTAL


---------- ---------- ---------- ---------- ---------- ---------- -------------
prathika 101 70 95 65 300
sanjana 102 80 90 60 400
pavithra 103 60 70 80 500
kavi 104 80 50 70 200

DELETE:

SQL> delete from student where sname='kavi';

1 row deleted.

SQL> select * from student;

SNAME REGNO M1 M2 M3 TOTAL


---------- ---------- ---------- ---------- ---------- ---------- -----------
prathika 101 70 95 65 300
sanjana 102 80 90 60 400
pavithra 103 60 70 80 500
3. TCL COMMANDS:

SQL> connect scott/tiger;


Connected.

SQL> INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)


VALUES (4, ' Chaitali ', 25, ' Mumbai ', 6500.00 );

1 row created.

SQL> INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)


VALUES (5, ' Hardik ', 27, ' Bhopal ', 8500.00 );

1 row created.

SQL> INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)


VALUES (6, ' Komal ', 22, ' MP ', 4500.00 );

1 row created.

SQL> INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)


VALUES (7, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

1 row created.

SQL> INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)


VALUES (8, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

1 row created.

COMMIT;

Commit complete.

SQL> select * from customers;

ID NAME AGE ADDRESS SALARY


---------- --------------- ---------- ------------------------- --------------
8 Ramesh 32 Ahmedabad 2000
1 Ramesh 32 Ahmedabad 2000
2 Khilan 25 Delhi 1500
3 kaushik 23 Kota 2000
4 Chaitali 25 Mumbai 6500
5 Hardik 27 Bhopal 8500
6 Komal 22 MP 4500
7 Ramesh 32 Ahmedabad 2000

8 rows selected.

SQL> INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)


2 VALUES (9, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

1 row created.

SQL> select * from customers;

ID NAME AGE ADDRESS SALARY


---------- --------------- ---------- ------------------------- --------------
8 Ramesh 32 Ahmedabad 2000
9 Ramesh 32 Ahmedabad 2000
1 Ramesh 32 Ahmedabad 2000
2 Khilan 25 Delhi 1500
3 kaushik 23 Kota 2000
4 Chaitali 25 Mumbai 6500
5 Hardik 27 Bhopal 8500
6 Komal 22 MP 4500
7 Ramesh 32 Ahmedabad 2000

9 rows selected.

SQL> rollback;

Rollback complete.

SQL> select * from customers;

ID NAME AGE ADDRESS SALARY


---------- --------------- ---------- ------------------------- --------------
8 Ramesh 32 Ahmedabad 2000
1 Ramesh 32 Ahmedabad 2000
2 Khilan 25 Delhi 1500
3 kaushik 23 Kota 2000
4 Chaitali 25 Mumbai 6500
5 Hardik 27 Bhopal 8500
6 Komal 22 MP 4500
7 Ramesh 32 Ahmedabad 2000

8 rows selected.

You might also like