IT248-DBMS 18IT072

PRACTICAL -1
Aim: Create a table and insert a value.

Theoretical Description:
Basic syntax to create table in sql is as follow:
CREATE TABLE table_name(
var_name DATATYPE,
var_name DATATYPE,
…
var_name DATATYPE
);
Here DATATYPE can be Number(a,b): with two arguments where a
indicates maximum length of number and b indicates decimal places.
Varchar,Varchar2 and Date,etc
To describe any table “desc table_name” used.
To entre data into table “INSERT INTO table_name VALUES(data,data)”
To view data of table “SELECT * FROM TABLE_NAME”

Query-1: Create Table Job (job_id, job_title, min_sal, max_sal)

COLUMN NAME DATA TYPE
job_id Varchar2(15)
job_title Varchar2(30)
min_sal Number(7,2)
max_sal Number(7,2)
SQL Statement: create table Job(job_id Varchar2(15),job_title
Varchar2(30),min_sal number(7,2),max_sal number(7,2));
Output: Table created.

Query-2: Create table Employee (emp_no, emp_name, emp_sal, emp_comm,

dept_no)

COLUMN NAME DATA TYPE

emp_no Number(3)
emp_name Varchar2(30)
emp_sal Number(8,2)
emp_comm Number(6,1)
dept_no Number(3)

CSPIT Department of Information Technology Page 1

IT248-DBMS 18IT072

SQL Statement: Create table Employee (emp_no number(3), emp_name

varchar2(30), emp_sal number(8,2), emp_comm number(6,1), dept_no
number(3));
Output: Table created.

Query-3: Create table deposit(a_no,cname,bname,amount,a_date).

COLUMN NAME DATA TYPE
a_no Varchar2(5)
cname Varchar2(15)
bname Varchar2(10)
amount Number(7,2)
a_date Date
SQL Statement: Create table deposit(a_no Varchar2(5),cname
Varchar2(15),bname Varchar2(10),amount number(7,2),a_date date);
Output: Table created.

Query-4: Create table borrow(loanno,cname,bname,amount).

COLUMN NAME DATA TYPE

loanno Varchar2(5)
cname Varchar2(15)
bname Varchar2(10)
amount Varchar2(7,2)
SQL Statement: Create table borrow(loanno Varchar2(5),cname
Varchar2(15),bname Varchar2(10),amount number(7));
Output: Table created.

Query-5: Describe table Job, employee, depo Describe table Job, employee,
deposit.
SQL Statement:
describe Job
describe employee
describe deposit
Output:

CSPIT Department of Information Technology Page 2

IT248-DBMS 18IT072

Query-6: Insert following values in the table Employee.

emp_no emp_name emp_sal emp_comm dept _no

101 Smith 800 20
102 Snehal 1600 300 25
103 Adama 1100 0 20
104 Aman 3000 15
105 Anita 5000 50,000 10
106 Sneha 2450 24,500 10
107 Anamika 2975 30

SQL Statement:
insert into Employee(emp_no,emp_name,emp_sal,emp_comm,dept_no)
values(101,'Smith',800,NULL,20);
insert into Employee(emp_no,emp_name,emp_sal,emp_comm,dept_no)
values(102,'Snehal',1600,300,25);
insert into Employee(emp_no,emp_name,emp_sal,emp_comm,dept_no)
values(103,'Adama',1100,0,20);
insert into Employee(emp_no,emp_name,emp_sal,emp_comm,dept_no)
values(104,'Aman',3000,NULL,15);
insert into Employee(emp_no,emp_name,emp_sal,emp_comm,dept_no)
values(105,'Anita',5000,50000,10);
insert into Employee(emp_no,emp_name,emp_sal,emp_comm,dept_no)
values(106,'Sneha',2450,24500,10);
insert into Employee(emp_no,emp_name,emp_sal,emp_comm,dept_no)
values(107,'Anamika',2975,NULL,30);

CSPIT Department of Information Technology Page 3

IT248-DBMS 18IT072

Output:

Query-7: Insert following values in the table job.

job_id job_title min_sal max_sal

IT_PROG Programmer 4000 10000
MK_MGR Marketing manager 9000 15000
FI_MGR Finance manager 8200 12000
FI_ACC Account 4200 9000
LEC Lecturer 6000 17000
COMP_OP Computer Operator 1500 3000

SQL Statement:
insert into job(job_id,job_title,min_sal,max_sal)
values('IT_PROG','Programmer',4000,10000)
insert into job values('&job_id','&job_title',&min_sal,&max_sal)
insert into job(job_id,job_title,min_sal,max_sal)
values(' FI_MGR',' Finance manager',8200,12000)
insert into job values('&job_id','&job_title',&min_sal,&max_sal);
insert into job values('&job_id','&job_title',&min_sal,&max_sal);
insert into job values('&job_id','&job_title',&min_sal,&max_sal);

CSPIT Department of Information Technology Page 4

IT248-DBMS 18IT072

Output:

Query-8: Insert following values in the table deposit.

A_no cname Bname Amount date

101 Anil andheri 7000 01-jan-06
102 sunil virar 5000 15-jul-06
103 jay villeparle 6500 12-mar-06
104 vijay andheri 8000 17-sep-06
105 keyur dadar 7500 19-nov-06
106 mayur borivali 5500 21-dec-06

SQL Statement:
insert into deposit(A_no,cname,bname,Amount,A_date)
values(101,'Smith','andheri',7000,'01-jan-06');
insert into deposit(A_no,cname,bname,Amount,A_date)
values(102,'Snehal','virar',5000,'15-jul-06');
insert into deposit(A_no,cname,bname,Amount,A_date)
values(103,'Adama','villeparle',6500,'12-mar-06');
insert into deposit(A_no,cname,bname,Amount,A_date)
values(104,'Aman','andheri',8000,'17-sep-06');
insert into deposit(A_no,cname,bname,Amount,A_date)
values(105,'Anita','dadar',7500,'19-nov-06');
insert into deposit(A_no,cname,bname,Amount,A_date)
values(106,'Sneha','borivali',5500,'21-dec-06');

CSPIT Department of Information Technology Page 5

IT248-DBMS 18IT072

Output:

Question-1: Which is different query for describe?

Answer: desc

Question-2: Which command is used for display table?

Answer: select * from table_name;

Question-2: Which query is used for entering blank value?

Answer: NULL

CSPIT Department of Information Technology Page 6