EXPERIMENT -08, 09
#CODE
select abs(22);
select abs(-22);
select mod(10,4);
select mod(10,4) as reminder;
select mod(15,3) as reminder;
select mod(15.6,2) as reminder;
select power(4,2);
select pow(12,2) as power;
select sqrt(4);
select sqrt(169) as square_root;
select greatest(2,5,18,6,12,100,65);
select least(2,5,18,6,12,100,65);
select least(2,5,18,6,12,100,65) as min;
select truncate(22.897,1);
select truncate(22.897,2);
select round(22.897,0);
#table
create database db47;
use db20;
create table student (stu_id int primary key,
stu_name varchar(25),
age int,gender char(1),
doa date,city varchar(20));
insert into student values(101,"Joseph",22,"M","2016-11-23","USA"),
(102,"David",21,"M","2016-11-23","France"),
(103,"Kiara",19,"F","2016-11-23","Spain"),
(104,"Sana",22,"F","2016-11-23","USA"),
(105,"Eliana",24,"F","2016-11-23","USA"),
(106,"Mick",20,"M","2016-11-23","UK"),
(107,"Johnny",21,"M","2016-11-23","UK");
select * from student;
select stu_name,age,city from student;
#where clause
select * from student where city = 'USA';
select * from student where city = 'USA' and gender = 'F';
select * from student where city = 'UK' or city = 'Spain';
select * from student where not city = 'Spain';
#group by
select city,count(stu_id) as total_students from student group by city;
#having
select city,count(stu_id) as total_stds from student group by city having count (stu_id)>2;
#order by
select * from student order by city;
select * from student order by city desc;
#string functions
select upper('India') as upper_case;
select lower('INDIA') as lower_case;
select lcase('INDIA') as lower_case;
select character_length('India') as total_length;
select character_length(stu_name) as total_length from student;
select concat("India ","is ","My Country") as merged;
select stu_id,stu_name,concat(stu_name," ",age) as name_age from student;
select reverse("India");
select reverse(stu_name) from student;
select replace("Orange is a vegetable","vegetable","fruit");
select ltrim(" India ");
select length(ltrim(" India "));
select length(rtrim(" India "));
select length(trim(" India "));
select position("fruit" in "Orange is a fruit") as name;
select ascii('a');
select ascii('u');
