DEV Community

Cover image for SQL Most Important Query
Ramani Hitesh (iOS Software Engineer)
Ramani Hitesh (iOS Software Engineer)

Posted on

SQL Most Important Query

MySql

To see the databases

1)show databases;

to create database

2)create database cognizantstud;

to goto the database
Enter fullscreen mode Exit fullscreen mode

3)use cognizantstud;

to drop database
Enter fullscreen mode Exit fullscreen mode

4)drop database cognizantstud;

to create a table
Enter fullscreen mode Exit fullscreen mode

5)create table student

( studid int not null auto_increment,
name varchar(30) not null,
address varchar(30) not null,
primary key(studid));


6)to insert record

insert into student values(101,'tarun','chennei');

7)to view the records

select * from student;

8)update the record

update student set address='Bangalore' where name='tarun';

9)to create a view (view will not contain any data.it will contain only the query)
create view stud as select studid,name from student;

we can do insert,update and delete of the view it will replect to the base table.

10)to see all the tables and views

show tables;

11)where condition :-

select * from student where name='tarun';

12)distinct :-(unique data will be displayed)

select distinct address from student;

13)order by:-
select * from student order by name;
select * from student order by name asc;

select * from student order by name desc;

14)group by

select address, count(*) from student group by address;

15)aggregate function

count(),sum(),avg(),min(),max(),first(),last()

i)select count(*) from student;
first record :- select name from student limit 1;

last record :-select name from student order by name desc limit 1;

16)alter table:-
alter table student add salary int not null;
to add data to salary column :-
update student set salary=45000 where studid=101;

update student set salary=35000 where studid in (102,103,104);

17)select sum(salary) from student;
18)select avg(salary) from student;
19)select min(salary) from student;

20)select max(salary) from student;

between and example:-

21)select * from student where salary between 30000 and 46000;

like % (wildcard)

22)select * from student where name like 'p%';(word starting with p will display)

select * from student where name like '%r';(word ending with r will display)

23)truncate table:-
it will delete all the data from the table.

truncate table student;

24)drop table;
it will delete the data as well as the table also.

drop table student;

25)join

create table marks
(studid int(4),
phy int(4),
chem int(4),
maths int(4),
tot int(4),avg int(4));

i)insert into marks(studid,phy,chem,maths) values(101,56,67,78);
ii)update marks set tot=(phy+chem+maths) where studid in (101,102,103);

iii)update marks set avg=tot/3 where studid in (101,102,103);

joining 2 tables:-

select a.studid,a.name,a.address,a.salary,b.phy,b.chem,b.maths,b.tot,b.avg from student a,marks b where a.studid=b.studid;

Top comments (0)