unit 2 codes

 drop table person

1.

create table person

(

id int primary key,

L_name varchar(50),

F_name varchar(50),

Age int check(age between 18 and 45),

city varchar(20)

);

2.

create table person

(

id int,

L_name varchar(50),

F_name varchar(50),

Age int ,

city varchar(20),

primary key(id),

check(age between 18 and 45)

);

3.

create table person

(

id int,

L_name varchar(50),

F_name varchar(50),

Age int ,

city varchar(20),

constraint key1 primary key(id),

constraint chk_age check(age between 18 and 45)

);

create table person

(

id int primary key,

L_name varchar(50),

F_name varchar(50),

Age int check(age >18 or age=18 ),

city varchar(20)

);

reate table person

(

id int primary key,

L_name varchar(50),

F_name varchar(50),

Age int check( (age >18 or age=18) and age<=45 ),

city varchar(20)

);

create table person

(

id int primary key,

L_name varchar(50),

F_name varchar(50),

Age int ,

city varchar(20),

constraint ck check( (age >18 or age=18) and age<=45 )

);

drop table uni

create table uni(

S_id varchar(8) ,

F_name varchar(50),

L_name varchar(50),

city varchar(20),

state varchar(30),

annual_income number(6),

category varchar(90),

Primary key(S_id),

constraint chk1 check(state='punjab' AND annual_income<=200000 AND category='SC' OR category='BC')

);

desc uni

/* add null */

alter table uni modify state varchar(30) not null;

alter table uni modify annual_income number not null;

alter table uni modify category varchar(5) not null;

/* drop null */

alter table uni modify state varchar(30) null;

/* drop check constraint */

alter table uni drop constraint chk1;

/* add check */

alter table uni add constraint chk1 check(state='punjab' AND annual_income<=200000 AND category='SC' OR category='BC')

create table rock

(

u_id int primary key,

fname varchar(50) not null,

lname varchar(50),

state varchar(50) default 'punjab',

category varchar(10)

);

desc rock

alter table rock modify category varchar(10) ;

// truncate delete data but drop delete total table

select * from person

insert into person (U_id, Fname, address) values (1133,'gagan','kapurthala');

select fname from person where state= 'punjab'

select distinct fname from person where state= 'punjab'

// UPDATE

update person set state='Punjab' where state ='punjab'

update person set state='punjab' where u_id = 1131

update person set lname='mahajan' where u_id = 1131

select * from person

update person set country='INDIA'

update person set lname='sharma' , address='jalandhar' where u_id= 1132;

//DELETE

delete from person where address is NULL

delete from person where lname is null

delete from person where u_id = 1133

delete from person

select * from person where fname='ram' AND lname ='kumar'

delete from person where u_id = 1130

delete from person where fname='ram' AND lname ='kumar'

desc person

select * from person

select lname, fname from person // pi- project operation of RA

select * from person where u_id >1120 // sigma or select operation of RA

select fname, lname from person where u_id>1125;

// >, <, >=, <=, =,!=

select * from person where u_id=1120;

select * from person where u_id!=1120;

// multiple conditions in where clause

select * from person where state ='Punjab' and category='SC'

select * from person where state ='Punjab' and category!='SC'

select * from person where state !='Punjab' and category='SC'

select * from person where state !='Punjab' and category!='SC'

select * from person where state >'Punjab'

select * from person where state ='Punjab' or category='SC'

select * from person where category='Gen' or state ='Punjab'

select * from person where age<22 and state ='Punjab' or category='SC'

select * from person where age<22 and (state ='Punjab' or category='SC')

// between, in

select * from person where age between 21 and 23

select * from person where u_id between 1120 and 1125

//IN

select * from person where age IN (21, 23)

select * from person where lname IN ('kumar', 'sharma')

//LIKE

select * from person where fname like'a%'

select * from person where fname like'_a%'

select * from person where fname like'__r%'

select * from person where fname like'a%a%'

select * from person where fname like'_a%a%'

select * from person where fname like'%a_'

select * from person where fname like'%r__'

select * from person where fname like'_____'

// Distinct

select DISTINCT fname from person ;

select ALL fname from person ;

// null values

select * from person where lname is null

select * from person where lname is not null

// order by

select * from person where lname is not null order by fname

select * from person order by fname DESC

select * from person order by U_id ASC

// GROUP BY

select category,avg(age) from person group by category

select state,avg(age) from person group by state

select category,avg(age) from person where category ='SC' group by category

select state,avg(age) from person where category ='SC' group by state

select state,avg(age) from person where category !='SC' group by state

//Having

select state, count(category) "Number of Candidates" from person

group by state

having state = 'Punjab'

select state "state of candidate", count(category) "Number of Candidates" from person

group by state

having state != 'Punjab'

//DCL

create user john identified by john;

create role QA;

grant QA to john;

grant create table to john

or

grant create any table to john

grant update on person to john

revoke create table from john

select * from customer

select * from order1

//JOin using select statement

select id,name,age,amount from customer,order1 where customer.id=order1.customer_id;

//inner join

select id,name,amount,odate from customer inner join order1 on customer.id=order1.customer_id;

//outer join as left and right

//left join

select id,name,amount,odate from customer left join order1 on customer.id=order1.customer_id;

//right join

select id,name,amount,odate from customer right join order1 on customer.id=order1.customer_id;

//full join

select id,name,amount,odate from customer full join order1 on customer.id=order1.customer_id;

//self join

SELECT a.ID, b.NAME, a.SALARY FROM CUSTOMER a, CUSTOMER b WHERE a.SALARY < b.SALARY;

//cross join

SELECT ID, NAME, AMOUNT, ODATE FROM CUSTOMER, ORDER1;