1.
DDL COMMANDS
1. The Create Table Command: - it defines each column of the table uniquely.
Each column has minimum of three attributes, a name , data type and size.
Syntax:
Create table <table name> (<col1> <datatype>(<size>),<col2> <datatype><size>));
Ex:create table emp(empno number(4) primary key, ename char(10));
2. Modifying the structure of tables.
a) Add new columns
Syntax:
Alter table <tablename> add(<new col><datatype(size),<new col>datatype(size));
Ex:alter table emp add(sal number(7,2));
3. Dropping a column from a table.
Syntax:
Alter table <tablename> drop column <col>;
Ex:alter table emp drop column sal;
4. Modifying existing columns.
Syntax:
Alter table <tablename> modify(<col><newdatatype>(<newsize>));
Ex:alter table emp modify(ename varchar2(15));
5. Renaming the tables
Syntax:
Rename <oldtable> to <new table>;
Ex:rename emp to emp1;
6. truncating the tables.
Syntax:
Truncate table <tablename>;
Ex:trunc table emp1;
7. Destroying tables.
Syntax:
Drop table <tablename>;
Ex:drop table emp;
CREATION OF TABLE:
SYNTAX:
create table<tablename>(column1 datatype,column2 datatype...);
EXAMPLE:
SQL>create table std(sno number(5),sname varchar(20),age number(5),sdob date,sm1
number(4,2),sm2 number(4,2),sm3 number(4,4));
Table created.
SQL>insert into std values(101,’AAA’,16,’03-jul-88’,80,90,98);
1 row created.
SQL>insert into std values(102,’BBB’,18,’04-aug-89’,88,98,90);
1 row created.
OUTPUT:
Select * from std;
SNO SNAME AGE SDOB SM1 SM2 SM3
101 AAA 16 03-jul-88 80 90 98
102 BBB 18 04-aug-89 88 98 90
ALTER TABLE WITH ADD:
SQL>create table student(id number(5),name varchar(10),game varchar(20));
Table created.
SQL>insert into student
values(1,’mercy’,’cricket’); 1 row created.
SYNTAX:
alter table<tablename>add(col1 datatype,col2 datatype..);
EXAMPLE:
SQL>alter table student add(age number(4));
SQL>insert into student values(2,’sharmi’,’tennis’,19);
OUTPUT:
ALTER: select * from student;
ID NAME GAME
1 Mercy Cricket
ADD: select * from student;
ID NAME GAME AGE
1 Mercy cricket
2 Sharmi Tennis 19
ALTER TABLE WITH MODIFY:
SYNTAX:
Alter table<tablename>modify(col1 datatype,col2 datatype..);
EXAMPLE:
SQL>alter table student modify(id number(6),game varchar(25));
OUTPUT:
MODIFY
desc student;
NAME NULL? TYPE
Id Number(6)
Name
Varchar(20)
Game
Varchar(25) Age
Number(4)
DROP:
SYNTAX: drop table<tablename>;
EXAMPLE:
SQL>drop table student;
SQL>Table dropped.
TRUNCATE TABLE
SYNTAX: TRUNCATE TABLE <TABLE NAME>;
Example: Truncate table stud;
DESC
Example: desc emp;
Name Null? Type
EmpNo NOT NULL number(5)
EName VarChar(15)
Job NOT NULL Char(10)
DeptNo NOT NULL number(3)
PHONE_NO number (10)
DML COMMANDS
DML commands are the most frequently used SQL commands and is used to query and
manipulate the existing database objects. Some of the commands are Insert, Select, Update,
Delete.
Insert Command This is used to add one or more rows to a table. The values are separated
by commas and the data types char and date are enclosed in apostrophes. The values must be
entered in the same order as they are defined.
Select Commands It is used to retrieve information from the table. It is generally referred to
as querying the table. We can either display all columns in a table or only specify column
from the table.
Update Command It is used to alter the column values in a table. A single column may be
updated or more than one column could be updated.
Delete command After inserting row in a table we can also delete them if required. The delete
command consists of a from clause followed by an optional where clause.
Q1: Insert a single record into dept table.
Ans: SQL> insert into dept values
(1,'IT','Tholudur'); 1 row created.
Q2: Insert more than a record into emp table using a single insert command.
Ans: SQL> insert into emp
values(&empno,'&ename','&job',&deptno,&sal); Enter value for empno: 1
Enter value for ename: Mathi
Enter value for job: AP
Enter value for deptno: 1
Enter value for sal: 10000
old 1: insert into emp values(&empno,'&ename','&job',&deptno,&sal)
new 1: insert into emp
values(1,'Mathi','AP',1,10000) 1 row created.
SQL> / Enter value for empno: 2
Enter value for ename: Arjun
Enter value for job: ASP
Enter value for deptno: 2
Enter value for sal: 12000
old 1: insert into emp values(&empno,'&ename','&job',&deptno,&sal)
new 1: insert into emp values(2,'Arjun','ASP',2,12000)
1 row created.
SQL> Enter value for empno: 3
Enter value for ename: Gugan
Enter value for job: ASP
Enter value for deptno: 1
Enter value for sal: 12000
old 1: insert into emp values(&empno,'&ename','&job',&deptno,&sal)
new 1: insert into emp values(3,'Gugan','ASP',1,12000)
1 row created.
Q3: Update the emp table to set the salary of all employees to Rs15000/- who are working as
ASP
Ans: SQL> select * from emp;
EMPNO ENAME JOB DEPTNO SAL
1 Mathi AP 1 10000
2 Arjun ASP 2 12000
3 Gugan ASP 1 12000
SQL> update emp set sal=15000 where job='ASP'; 2 rows updated.
SQL> select * from emp;
EMPNO ENAME JOB DEPTNO SAL
1 Mathi AP 1 10000
2 Arjun ASP 2 15000
3 Gugan ASP 1 15000
Q4: Create a pseudo table employee with the same structure as the table emp and insert rows
into the table using select clauses.
Ans: SQL> create table employee as select * from emp;
Table created.
SQL> desc employee;
Name Null? Type
EMPNO NUMBER(6)
ENAME NOT NULL VARCHAR2(20)
JOB NOT NULL VARCHAR2(13)
DEPTNO NUMBER(3)
SAL NUMBER(7,2)
Q5: select employee name, job from the emp table
Ans: SQL> select ename, job from emp;
ENAME JOB
Mathi AP
Arjun ASP
Gugan ASP
Karthik Prof
Akalya AP
suresh lect
6 rows selected.
Q6: Delete only those who are working as lecturer
Ans: SQL> select * from emp;
EMPNO ENAME JOB DEPTNO SAL
1 Mathi AP 1 10000
2 Arjun ASP 2 15000
3 Gugan ASP 1 15000
4 Karthik Prof 2 30000
5 Akalya AP 1 10000
6 suresh lect 1 8000
7 rows selected.
SQL> delete from emp where job='lect';
1 row deleted.
SQL> select * from emp;
EMPNO ENAME JOB DEPTNO SAL
-
1 Mathi AP 1 10000
2 Arjun ASP 2 15000
3 Gugan ASP 1 15000
4 Karthik Prof 2 30000
5 Akalya AP 1 10000
2. TEST CASES
From the above constructed table write the queries for following
1 List the records in the emp table orderby salary in ascending order
2 List the records in the emp table orderby salary in descending order.
3 Display only those employees whose deptno is 30.
4 Display deptno from the table employee avoiding the duplicated values.
3. SAMPLE OUTPUT
1. List the records in the emp table orderby salary in descending order.
Ans: SQL> select * from emp order by sal desc;
EMPNO ENAME JOB DEPTNO SAL
4 Karthik Prof 2 30000
2 Arjun ASP 2 15000
3 Gugan ASP 1 15000
1 Mathi AP 1 10000
5 Akalya AP 1 10000
2. Display only those employees
whose deptno is 30. Solution: Use
SELECT FROM WHERE syntax.
Ans: SQL> select * from emp where
deptno=1; EMPNO ENAME JOB
DEPTNO SAL
1 Mathi AP 1 10000
3 Gugan ASP 1 15000
4 Akalya AP 1 10000
