Basics of MYSQL

1.)Table creation on sql
***********************

create table contact
(
id INT NOT NULL AUTO_INCREMENT
PRIMARY KEY,
first_name varchar (30),
Last_name varchar (30),
phone varchar (15)
)

#######################

desc contact

#######################

2.)Adding Values to TABLES
************************
insert into contact values
(
1,'Remya','Gopi','90*6***40'
)
#######################
3.)For updating table values
***************************
select * from contact
update contact
set phone = '04683333'
where id = 1

#######################

4.)For delete value from TABLE
*****************************
delete from contact
where id = 1

#######################

5.)For deleting a TABLE
***********************
drop table contact

#######################

6.)Adding a column on a table
***************************
alter table test add state varchar2 (2)

#######################

7.)Deleting a column on a table
*******************************
alter table noc DROP COLUMN salary

#######################

8.)Comparison
***************
select * from list
where age >= '25'
========================
select * from ardc
where id = '1' or id = '2'

select * from ardc
where id != '1' and id = '2'

#######################

9.)IN or NOT IN
**************

select * from ardc
where id not in (1,3)

select * from ardc
where id in (1,3)

select * from ardc
where name not in ('Sreenu Raghavan','Ratheesh')

select * from ardc
where name in ('Sreenu Raghavan','Ratheesh')

#######################

10.)BETWEEN or NOT BETWEEN
***********************
select * from ardc
where id between 1 and 3

select * from ardc
where id >= 1 and id <= 3


select * from ardc
where id not between 1 and 3


select * from ardc
where id < 1 or id > 3

#######################

11.)LIKE or NOT LIKE with % or _
********************
select * from ardc
where name like 'S%'

select * from ardc
where name like 'S_ee_u%'

select * from ardc
where name not like 'S_ee_u%'

#######################

12.)< or >
*******
select * from noc
where salary < prev_salary

select * from noc
where name > 'S'
select * from noc
where name > 'Na'

B > A
C > B
a > A

#######################

13.)NULL
******
1)insert into noc values
(7,'pratheek','madiwala','3000',null)

2)insert into noc (sl,name,house,salary) values
(7,'pratheek','madiwala','3000')

3)select * from noc
where prev_salary = null >>>> Wrong

select * from noc
where prev_salary != null >>>>>Wrong


select * from noc
where prev_salary is null >>>>>Correct

select * from noc
where prev_salary is not null >>>>>>Correct

#######################

14.)ORDER
******
select * from noc
order by name
=
select * from noc
order by salary,sl
=
select * from noc
order by 1 >>>Column Number
=
select sl,name from noc
order by 2 >>>>order should be as per select list

#######################

15.)ORDER_descending order
**********************
select * from noc
order by name desc
==
select * from noc
order by salary desc ,sl desc
==
select sl,name,salary from noc
order by 3 desc
===============================
select * from ardc
where (salary > '2000' or dept ='1') and year = '2008'

select * from ardc
where salary > '2000' or (dept ='1' and year = '2008')

select * from ardc
where dept ='1' and year = '2007'

3 Ratheesh 2600 1 2008
4 Robin 2600 2 2008
1 Sreenu Raghavan 6000 1 2007
2 Nithin Kumar 6000 2 2007
###########################################################
###########################################################