syntax to insert null rows:
insert into tablename values(val1,val2,...);
ex:
employees(empid,empname,salary,dept)
insert into employees values(1,'suresh',30000,'testing');
if don't know the employee id, then you can left it as null
for number column -> null
for varchar2/date -> ''
insert into employees values(null,'ram',45000,'developer');
insert into employees values(10,'',56000,'manager');
select statement:
* used to display rows inserted in a table.
syntax to display all rows under all columns in table:
select * from tablename;
ex:
select * from employees;
projection: display all rows under selected columns.
syntax to display selected columns from table:
select columnname,columnname,... from tablename;
ex:
select empname,salary from employees;
selection: display all columns and only selected rows
select * from tablename where condition;
condition -> expression -> used to select rows when condition is true
-> to write condition use relational and logical operators
value operator value
Relational operators ( < , <=, >, >=, = ,!=)
ex:
25 > 20 (true)
20 < 5 (false)
salary < 23000
Logical Operators (and, or, not, between, in, like)
ex:
Q) select rows from table employees only when salary is above 5000
select * from employees where salary > 5000;
Q) select rows from table employees only when salary is equals to 40000
select * from employees where salary=40000;
Q) select rows from table employees only of empid is 1 and salary is 24000;
select * from employees where empid=1 and salary=24000;
Q) display rows from table employees only when salary is in between 20000 and 25000
select * from employees where salary>=20000 and salary<=25000;
select * from employees where salary between 20000 and 25000;
Q) display rows from table employees only when dept column having tester or
developer
select * from employees where dept='tester' or dept='developer';
select * from employees where dept in('tester','developer');
Q) display rows from table employees only whne dept is not sales.
select * from employees where dept!='sales';
select * from employees where not dept='sales';
Q) display rows from table employees only when dept column not having tester or
developer
select * from employees where dept not in('tester','developer');
like operator:
used for pattern matching
pattern% ->pattern must present at beigin of the row
%pattern -> pattern must present in row at Ending of the row
%patter% -> search for pattern from begining to end.
ex:
select * from employees where dept like 'd%';
select * from employees where dept like '%s';
select * from employees where dept like '%na%';
update -> used to change the value after it is stored in table or fill null values
in table.
syntax:
update tablename set columnname=value where condition;
ex:
update employees set empname='rakesh' where empid=10;
delete -> used to delete rows from table.
syntax to delete all rows from table:
delete from tablename;
delete tablename;
syntax to delete selected rows from table:
delete from tablename where condition;
ex:
delete from employee;
delete from employees where dept='sales';
