ex:
create table student(sno number(3),sname varchar2(10),sadd varchar2(12));
create table dept(deptno number(3),dname varchar2(10),dloc varchar2(12));
create table emp(eid number(3),ename varchar2(10),esal number(10,2),
deptno number(3),job varchar2(10),comm number(6));
describe command
==============
It is used to see the structure of the table.
syntax:
desc <table_name>;
ex:
desc student;
desc dept;
desc emp;
insert command
==================
It is used to insert a record into database table.
syntax:
insert into <table_name> values(val1,val2,....,valN);
ex:
insert into student values(101,'raja','hyd');
insert into student values(102,'ravi','delhi');
insert into student values('ramana',103,'vizag'); // invalid
insert into student values(103,'ramana'); //invalid
null
-----
null is a keyword which used for unavailable or undefined.
insert into student values(103,'ramana',null);
approch2
--------
insert into student(sno,sname,sadd) values(104,'ramulu','pune');
insert into student(sno) values(105);
approach3
---------
Using '&' symbol we can read inputs at the time query execution.
ex:
insert into student values(&sno,'&sname','&sadd');
commit command
==============
It is used to make the changes permanent to database.
syntax:
commit;
ex:
commit;
dept table
----------
create table dept(deptno number(3),dname varchar2(10),dloc varchar2(12));
insert into dept values(10,'ECE','HYD');
insert into dept values(20,'EEE','DELHI');
insert into dept values(30,'CSE','PUNE');
insert into dept values(40,'MEC','PUNE');
commit;
emp table
----------
create table emp(eid number(3),ename varchar2(10),esal number(10,2),
deptno number(3),job varchar2(10),comm number(6));
insert into emp values(201,'Alan',8000,10,'Clerk',null);
insert into emp values(202,'Lisa',18000,10,'Clerk',200);
insert into emp values(203,'Kelvin',28000,20,'HR',500);
insert into emp values(204,'Nelson',10000,20,'HR',900);
insert into emp values(205,'Jose',48000,30,'Manager',500);
insert into emp values(206,'James',15000,30,'Manager',800);
commit;
select command
===============
A select command is used to retrieve the data which is present in database table.
syntax:
select * from <table_name>;
ex:
select * from student;
select * from emp;
select * from dept;
projection
----------
Selecting specific columns from the database table is called projection.
ex:
select * from student;
select sno from student;
select sno,sname from student;
select sno,sname,sadd from student;
Note:
----
In select command we can perform arithmetic operations also.
ex:
select sno-100,sname,sadd from student;
select sno+100,sname,sadd from student;
column alias
-------------
A userdefined heading given to a column is called column alias.
Column alias can be applied to any column.
Column alias are temperory.
Once the query execution is completed we will loss the column alias.
ex:
select sno+100 as SNO,sname,sadd from student;
select sno,sname as First_Name, sadd as CITY from student;
Interview Queries
-----------------
Q)Write a query to display all the employees information from employee table?
select * from emp;
Q)Write a query to display employee id, employee name and employee salary from
employee table?
select eid,ename,esal from emp;
Q)Write a query to see all the list of tables present in database?
select * from tab;
Q)Write a query to see the logical database name / schema ?
select * from global_name;
Q)Write a query to display employee id , employee name , employee salary and annual
salary from emp table?
select eid,ename,esal,esal*12 from emp;
Q)Write a query to display employee id , employee name , employee salary and
annual salary as ANNUAL_SAL from emp table?
select eid,ename,esal,esal*12 as ANNUAL_SAL from emp;
where clause
============
It is used to select specific records from database table.
syntax:
select * from <table_name> where condition;
ex:
select * from student where sno=101;
select * from student where sname='ravi';
select * from student where sadd=null; //no rows selected
is null
--------
is null is a operator which is used to select the null records.
ex:
select * from student where sadd is null;
Intervie Queries
--------------------
Q)Write a query to display all the employees information whose who are working in
20 department?
select * from emp where deptno=20;
Q)Write a query to display all the employee information whose who are working as a
manager?
select * from emp where job='Manager';
Q)Write a query to display employee id,employee name and employee salary who not
earn commission?
select eid,ename,esal from emp where comm is null;
update command
============
Update command is used to update/modify the present in database table.
syntax:
update <table_name> set <column_name>=value where condition;
ex:
update student set sname='rani' where sno=101;
update student set sname='jojo',sadd='texas' where sno=105;
Note:
----
If we won't use where clause then all rows will be updated.
ex:
update student set sno=101;
update student set sname='raja';
update student set sadd='hyd';
