Thursday 27 June 2013

Sql queries for IT students

assign no 5
//check
1.create table tebranch (branch_name varchar2(10) primary key , branch_city varchar2(10), assests number(12,2), check (assests >1000));
insert into tebranch values ('sbi','kop',20000);

insert into tebranch values ('icici','kop',30000);

insert into tebranch values ('axis','pune',40000);

insert into tebranch values ('axis','pune',500);//ORA-02290: check constraint (SYSTEM.SYS_C004082) violated

select *from tebranch

BRANCH_NAME BRANCH_CITY ASSESTS
sbi kop 20000
icici kop 30000
axis pune 40000


//unique
2.create table tebranch1 (branch_name varchar2(10)  constraint tecons primary key , branch_city varchar2(10), assests number(12,2), check (assests >1000));

insert into tebranch1 values ('sbi','kop',20000);

insert into tebranch1 values ('icici','kop',30000);

insert into tebranch1 values ('axis','pune',40000);

insert into tebranch1 values ('rbi','pune',40);////ORA-02290:check constraint (SYSTEM.SYS_C004084) violated

insert into tebranch1 values ('sbi','pune',4000);//ORA-00001: unique constraint (SYSTEM.TECONS) violated


//

4.delete from tebranch where branch_name='icici';//ORA-02292: integrity constraint (SYSTEM.SYS_C004046) violated - child record found

//foreign key
5.create table teaccount1 (account_number number(10) not null primary key, branch_name varchar2(10), balance number (12,2), check(balance>100),
foreign key (branch_name) references tebranch1 on delete cascade);

insert into teaccount1 values(1,'sbi',5000);

insert into teaccount1 values(2,'icici',4000);

insert into teaccount1 values(3,'axis',3000);

select *from teaccount1;

ACCOUNT_NUMBER BRANCH_NAME BALANCE
1 sbi 5000
2 icici 4000
3 axis 3000

delete from tebranch1 where branch_name='icici';
1 row(s) deleted.
select * from tebranch;
BRANCH_NAME BRANCH_CITY ASSESTS
sbi kop 20000
axis pune 40000
select *from teaccount1;

ACCOUNT_NUMBER BRANCH_NAME BALANCE
1 sbi 5000
3 axis 3000


//not null
6.create table stebranch (branch_name varchar2(10) primary key , branch_city varchar2(10) not null, assests number(12,2), check (assests >1000));

insert into stebranch values('sbi','kop',2000);

insert into stebranch values('icici','kop',3000);

select *from stebranch;

BRANCH_NAME BRANCH_CITY ASSESTS
sbi kop 2000
icici kop 3000

insert into stebranch values('sbi','kop',4000);//unique constraint (SYSTEM.SYS_C004096) violated

insert into stebranch values('sbi',null,2000);//cannot insert NULL into ("SYSTEM"."STEBRANCH"."BRANCH_CITY")

insert into stebranch values('sbi','kop',300);// check constraint (SYSTEM.SYS_C004095) violated


7.create table stebranch1 (branch_name varchar2(10) constraint xxxx primary key , branch_city varchar2(10) not null, assests number(12,2),
check (assests >1000));  

insert into stebranch1 values('sbi','kop',10000);

insert into stebranch1 values('icici','kop',10000);  

select *from stebranch1

BRANCH_NAME BRANCH_CITY ASSESTS
sbi kop 10000
icici kop 10000

insert into stebranch1 values('sbi','kop',10000);// ORA-00001: unique constraint (SYSTEM.XXXX) violated

insert into stebranch1 values('icici',null,10000); //ORA-01400: cannot insert NULL into ("SYSTEM"."STEBRANCH1"."BRANCH_CITY")


8.//primary key
create table stebranch (branch_name varchar2(10) primary key , branch_city varchar2(10) not null, assests number(12,2), check (assests >1000));

insert into stebranch values('sbi','kop',2000);

insert into stebranch values('icici','kop',3000);

insert into stebranch values('sbi','kop',2000);//ORA-00001: unique constraint (SYSTEM.SYS_C004096) violated

select *from stebranch;

BRANCH_NAME BRANCH_CITY ASSESTS
sbi kop 2000
icici kop 3000

//foreign key

9.
create table steaccount (account_number number(10) not null primary key, branch_name varchar2(10), balance number (12,2), check(balance>100),
foreign key (branch_name) references stebranch);  

insert into steaccount values(1,'sbi',3400);

insert into steaccount values(2,'icici',3400);

select *from steaccount;

ACCOUNT_NUMBER BRANCH_NAME BALANCE
1 sbi 3400
2 icici 3400

insert into steaccount values(1,'sbi',3400); //ORA-00001: unique constraint (SYSTEM.SYS_C004102) violated

insert into steaccount values(2,'rbi',3400);//ORA-02291: integrity constraint (SYSTEM.SYS_C004102) violated - parent key not found

delete from stebranch where branch_name='sbi';//ORA-02292: integrity constraint (SYSTEM.SYS_C004103) violated - child record found

insert into steaccount values(null,'sbi',3400);//ORA-01400: cannot insert NULL into ("SYSTEM"."STEACCOUNT"."ACCOUNT_NUMBER")

//foreign & delete cascade
10.create table steaccount1 (account_number number(10) not null primary key, branch_name varchar2(10), balance number (12,2), check(balance>100),
foreign key (branch_name) references stebranch on delete cascade);

insert into steaccount1 values(1,'sbi',10000);

insert into steaccount1 values(2,'icici',10000);

select *from steaccount1;

ACCOUNT_NUMBER BRANCH_NAME BALANCE
1 sbi 10000
2 icici 10000

delete from steaccount1;
2 row(s) deleted.
//delete from stebranch where branch_name='sbi'
select *from steaccount1;
no data found




//character function

1.select upper(branch_name) as branch_name from tebranch;

BRANCH_NAME
SBI
ICICI
AXIS

2.select lower(branch_name) from tebranch;

LOWER(BRANCH_NAME)
sbi
icici
axis

3.select initcap(branch_name) from tebranch;

INITCAP(BRANCH_NAME)
Sbi
Icici
Axis

4.select concat(branch_name,branch_city) from tebranch;

CONCAT(BRANCH_NAME,BRANCH_CITY)
sbikop
icicikop
axispune
   select concat(branch_name,assests) from tebranch;

CONCAT(BRANCH_NAME,ASSESTS)
sbi20000
icici30000
axis40000

5.select substr(branch_name,2,3)from tebranch;

SUBSTR(BRANCH_NAME,2,3)
bi
cic
xis

6.select instr(branch_name,'i')from tebranch;

INSTR(BRANCH_NAME,'I')
3
1
3

7.select trim('i' from branch_name)from tebranch;

TRIM('I'FROMBRANCH_NAME)
sb
cic
axis

8.select length(branch_name)from tebranch;

LENGTH(BRANCH_NAME)
3
5
4

9.select lpad(branch_name,10,'xx')from tebranch;

LPAD(BRANCH_NAME,10,'XX')
xxxxxxxsbi
xxxxxicici
xxxxxxaxis
   select rpad(branch_name,10,'xx')from tebranch;

RPAD(BRANCH_NAME,10,'XX')
sbixxxxxxx
icicixxxxx
axisxxxxxx



//number fuction

1.select round(123.467,2) from dual;

ROUND(123.467,2)
123.47
   select round(assests,3)from tebranch

ROUND(ASSESTS,3)
20000
30000
40000
   select *from tebranch

2.select trunc(123.467,2) from dual;

TRUNC(123.467,2)
123.46
   select trunc(assests,2) from tebranch;

TRUNC(ASSESTS,2)
20000
30000
40000

3.select power(2,3) from dual;

POWER(2,3)
8
   select power(assests,2) from tebranch;

POWER(ASSESTS,2)
400000000
900000000
1600000000
 
4.select abs(-2) from dual;

ABS(-2)
2
   select abs(-3) from tebranch;

ABS(-3)
3
3
3
 
5.select mod(5,3) from dual;

MOD(5,3)
2
   select mod(5,3) from tebranch;

MOD(5,3)
2
2
2


//date function
1.select sysdate from dual;

SYSDATE
20-MAR-13

2.select months_between('10-mar-13', '10-july-13') from dual;

MONTHS_BETWEEN('10-MAR-13','10-JULY-13')
-4

3.select add_months('20-mar-13', 5) from dual;
or
 select add_months(sysdate, 5) from dual;

ADD_MONTHS('20-MAR-13',5)
20-AUG-13

4.select next_day('20-mar-13', 'sunday') from dual;
or
   select next_day(sysdate, 'sunday') from dual;

NEXT_DAY('20-MAR-13','SUNDAY')
24-MAR-13

5.select last_day('20-mar-13') from dual;
or
  select last_day(sysdate) from dual;

LAST_DAY('20-MAR-13')
31-MAR-13

6.select round(sysdate,'year') from dual;

ROUND(SYSDATE,'YEAR')
01-JAN-13
  select round(sysdate,'mm') from dual;
ROUND(SYSDATE,'MM')
01-APR-13

7.select trunc(sysdate,'mm') from dual;
or
  select trunc(sysdate,'year') from dual;

TRUNC(SYSDATE,'MM')
01-MAR-13

8.select to_char(sysdate,'yyyy') from dual;

TO_CHAR(SYSDATE,'YYYY')
2013

  select to_char(sysdate,'mm') from dual;

TO_CHAR(SYSDATE,'MM')
03


9.select to_date('20032012','dd-mm-yyyy') from dual;

TO_DATE('20032012','DD-MM-YYYY')
20-MAR-12

10.select to_number(marks,'99.9') from testud;

























No comments:

Post a Comment

Check if column exists or not in the SQL server

--method 1 IF EXISTS(SELECT 1 FROM sys.columns with (nolock)           WHERE Name = N'LoginName'           AND Object_ID = Objec...