SQL Practise

create table std_table(stno number ,stname varchar2(20),stcity varchar2(20),pincode number ,address varchar2(30), fee varchar2(10),company varchar2(20) )

select * from student_table

desc student_table

--=========DDL================Dr.CAT============

alter table student_table modify pincode number(6)

alter table student_table add fee number;

alter table student_table drop column company

alter table student_table rename column stcity to city

alter table student_table rename to std_table

alter table std_table rename to student_table

--==========================================

insert into student_table values(100,'yug','pune',503183,'addr3',25000)

truncate table student_table

drop table student_table

flashback table test to before drop

create table emp_tab(eid number,ename varchar2(20),)

select * from emp_tab

drop table emp_tab purge

--================DML========SUDI======

insert into student_table values(100,'yug','pune',507183,'addr1')

insert into student_table values(&stno,'&stname','&city',&pincode,'&address')

insert into student_table(stno,pincode) values(&stno,&pincode)

update student_table set fee=22000 where stno='104'

--update student_table set stname='yugandhar' where address=null  can not update

select * from student_table

select stno A,stname B,city C,pincode D,address E from student_table

select stno student_number, stno+100 test_no from student_table where stno>102

select * from student_table order by stno [desc];

select rowid, rownum from student_table

select sum(fee) from student_table

select avg(fee) from student_table

select min(fee) from student_table

select max(fee) from student_table

select 2+3+5/2 from dual;

select trunc(sysdate) from dual;

select count(*) from student_table

select count(stname) from student_table

select count(distinct stname) from student_table

select * from user_tab_columns where table_name='student_table'

desc user_tab_columns

select student_table.* from STUDENT_TABLE;

select student_table.*,nvl(address,'myaddr') from student_table;

select student_table.*,nvl2(address,'myaddr','nullAddr') from student_table;

select student_table.*,nvl2(city,'pune','hyd') from student_table;

select student_table.*,nvl2(stname,'fist','second') from student_table;

select stno , length(stno), city, length(city), nullif(length(stno),length(city)) from student_table;

select stname , length(stname), city, length(city), nullif(length(stname),length(city)) from student_table;

select stno, count(*) from student_table group by stno;

select stno, count(*) from student_table group by stno having count(*)>1;

select stno, count(*),sum(fee) from student_table group by stno;

select stno, count(*),sum(fee),max(fee),min(fee),avg(fee) from student_table group by stno;

create sequence s2 minvalue 100 maxvalue 110 increment by 3;

create table seq_test1(id number , name varchar2(10));

insert into  seq_test values(s2.nextval, 'yog6')

select * from seq_test

alter sequence s2 maxvalue 115;

select * from user_sequences

select * from student_table

create table std as select * from student_table

select * from std

create table std1 as select * from student_table where 1=2

select * from std1

insert into std1 select * from student_table

update std1 set fee=17000 where stno='addr2'