GrassField

Today we had a peculiar problem with our redhat linux v 9.0 , in which we are suppose to compile and run a C code. we, java guys, with a blind guess, we installed GCC and all the packs found under developer tools. at that time we are able to get the code compiled. but we got a peculiar error. “stdio.h: No such file or directory“. being a basic package, it is not given in the developer utils. it was suppose to be there in /usr/include folder. but, to complete your installation, you need to install glibc-devel and kernel-headers. also libstdc++-devel can also be considered.

this post is for my note. i may forget it tomorrow

The most important aspect of SQL is it’s relational features,
where the data available in the tables combined with the necessary relations.
very frequently we will be coming across the situation, we have to combine two tables.

• make sure that you have the same name and same data type in both the tables.
• the keywords LEFT and RIGHT are not absolute. they operate within the context of the given statement.
• we can reverse the order of the tables and reverse the keywords and the result would be the same.
• if the type of join is not specified as inner or outer then it will be executed as an INNER JOIN

Demo dbs

create table english
(
id int(1),
word varchar(5)
);

create table hindi
(
id int(1),
word varchar(5)
);

insert into english values (0, ‘zero’);
insert into english values (1, ‘one’);
insert into english values (2, ‘two’);
insert into english values (3, ‘three’);
insert into hindi values (5, ‘five’);

insert into hindi values (1, ‘ek’);
insert into hindi values (2, ‘do’);
insert into hindi values (3, ‘theen’);
insert into hindi values (4, ‘char’);

for those who doesnt understand, the hindi db gives the corresponding terms in english db

CARTESIAN JOIN
A cartesian join is when you join every row of one table to every row of another table.

select * from hindi, english;

mysql> select * from hindi, english;
+——+——-+——+——-+
| id | word | id | word |
+——+——-+——+——-+
| 1 | ek | 1 | one |
| 1 | ek | 2 | two |
| 1 | ek | 3 | three |
| 1 | ek | 0 | zero |
| 2 | do | 1 | one |
| 2 | do | 2 | two |
| 2 | do | 3 | three |
| 2 | do | 0 | zero |
| 3 | theen | 1 | one |
| 3 | theen | 2 | two |
| 3 | theen | 3 | three |
| 3 | theen | 0 | zero |
| 5 | five | 1 | one |
| 5 | five | 2 | two |
| 5 | five | 3 | three |
| 5 | five | 0 | zero |
| 4 | char | 1 | one |
| 4 | char | 2 | two |
| 4 | char | 3 | three |
| 4 | char | 0 | zero |
+——+——-+——+——-+
20 rows in set (0.00 sec)

INNER JOIN
Inner join gives the all row from both the tables, where there is a match.
If there are any rows those dint fall in the condition are simply IGNORED

select english.id, english.word, hindi.word from english inner join hindi on english.id=hindi.id;

+——+——-+——-+
| id | word | word |
+——+——-+——-+
| 1 | one | ek |
| 2 | two | do |
| 3 | three | theen |
+——+——-+——-+
3 rows in set (0.00 sec)

select * from hindi join english using (id);
+——+——-+——-+
| id | word | word |
+——+——-+——-+
| 1 | ek | one |
| 2 | do | two |
| 3 | theen | three |
+——+——-+——-+
3 rows in set (0.00 sec)

LEFT JOIN
left outer join gives ALL the rows from the first table, and selected rows from second table those fall in the condition.
rows of second table, those dint fall in the condition are IGNORED.

select english.id, english.word, hindi.word from english left join hindi on english.id=hindi.id;
+——+——-+——-+
| id | word | word |
+——+——-+——-+
| 1 | one | ek |
| 2 | two | do |
| 3 | three | theen |
| 0 | zero | NULL |
+——+——-+——-+
4 rows in set (0.00 sec)

RIGHT JOIN
right outer join gives ALL the rows from the second table, and selected rows from first table those fall in the condition.
rows of first table, those dint fall in the condition are IGNORED.

select english.id, english.word, hindi.word from english right join hindi on english.id=hindi.id;

+——+——-+——-+
| id | word | word |
+——+——-+——-+
| 1 | one | ek |
| 2 | two | do |
| 3 | three | theen |
| NULL | NULL | five |
| NULL | NULL | char |
+——+——-+——-+
5 rows in set (0.00 sec)

another basic class room exercise for sql!

create table grades
(
id int(5),
name varchar(32),
maths int (3),
physics int(3),
literature int(3)
);

insert into grades values (1, ‘vijay’, 68, 37, 54);
insert into grades values (2, ’subramani’, 96, 89, 92);
insert into grades values (3, ‘richard’, 65, 12, 57);
insert into grades values (4, ‘govinda’, 69, 25, 82);

——————————-

select * from grades;
/*list all the students scored over 90 in their maths paper*/
select name from grades where maths>90;
/*more than 85 in all*/
select name from grades where maths>85 and physics>85 and literature>85;
/*results*/
select *,
maths+physics+literature as total,
(maths+physics+literature)/3 as average,
if ((maths>35 and physics>35 and literature>35), ‘pass’, ‘fail’) as result
from grades;

/*average marks all students*/
select avg(physics) as p, avg(maths) as m, avg(literature)as l from grades;

/*max and min in maths*/

select max(maths), min(maths) from grades;

/*top 3 in maths*/

select * from grades order by maths desc limit 3;

Here is one table and its related q&a. will be kept for future reference, or will be useful to beginners!

create table ex1
( id int(6),
name varchar(32),
surname varchar(32),
flathave varchar(32),
flatwant varchar(32));

insert into ex1 values(1, ‘pandian’, ‘muruga’, ‘pudukkottai’, ”);
insert into ex1 values(2, ‘pandian’, ‘muruga’, ‘chennai’, ”);
insert into ex1 values(3, ‘pandian’, ‘muruga’, ”, ‘coimbatore’);
insert into ex1 values(4, ‘amutha’, ‘radhakrishnan’, ”, ‘pudukkottai’);
insert into ex1 values(5, ‘Ram’, ‘narayan’, ”, ‘tirunelveli’);
insert into ex1 values(6, ‘kiran’, ‘vasudeva’, ”, ‘Palakkad’);
insert into ex1 values(7, ‘nanda’, ‘kumar’, ‘Mettur’, ”);
insert into ex1 values(8, ‘nirmal’, ‘raj’, ‘chidambaram’, ”);
insert into ex1 values(9, ‘nanda’, ‘kumar’, ‘coimbatore’, ”);

—————————————————————————————-

/*data*/
SELECT * FROM ex1 e;

/*who has a flat in pudukkottai and who wants to buy one?*/
select * from ex1 where flathave=’pudukkottai’ or flatwant=’pudukkottai’;

/*where does ‘pandian muruga’ own flats and where does he want to buy one?*/
select * from ex1 where name=’pandian’ and surname=’muruga’;

/*howmany entries are there and show splitups*/
select count(*) as total from ex1 where (flathave<>” and flathave is not null) or (flatwant<>” and flatwant is not null);
select count(*) as own from ex1 where flathave<>” and flathave is not null;
select count(*) as want from ex1 where flatwant<>” and flatwant is not null;

/*how many cliets do we have*/
select count(distinct name, surname) distinct_clients from ex1;

/*list the customers whose name starts with A*/
select name from ex1 where name like ‘A%’;

/*sort the table by name*/
select * from ex1 order by name;
select * from ex1 order by name desc;

one more mysql post for this week!

variables are mysql specific extension.

They hold single values. They are case sensitive. if you assign to a variable using a statement, that returns multiple rows, the value from the last variable is used. if there are no items in the query, and if it is assigned to a variable, no values will be assigned to the variable. instead the old value is retained, if it has any!

User variables are set for the duration of the thread. they are useful particularly when we work with mysql via console.

here are some examples

mysql> select * from delhi;
+—————+——–+
| first_name | amount |
+—————+——–+
| kavitha | 25 |
| raman | 68 |
| anbu | 45 |
| selva | 98 |
| nalini | 25 |
| amutha | 36 |
| richard | 75 |
| lessthanseven | 5 |
+—————+——–+
8 rows in set (0.00 sec)

mysql> set @testvar=5;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from delhi where amount<@testvar+1;
+—————+——–+
| first_name | amount |
+—————+——–+
| lessthanseven | 5 |
+—————+——–+
1 row in set (0.00 sec) mysql> set @a=5, @b=6, @c=7; Query OK, 0 rows affected (0.00 sec)

mysql> set @a=5, @b=6, @c=7;
Query OK, 0 rows affected (0.00 sec)

mysql> select @a:=(@b:=@b+1);
+—————-+
| @a:=(@b:=@b+1) |
+—————-+
| 7 |
+—————-+
1 row in set (0.00 sec)

A user variable cannot be used directly in an SQL statement as an identifier or as part of an identifier

mysql> set @thisname:=’first_name’;
Query OK, 0 rows affected (0.00 sec)

mysql> select @thisname from delhi;
+————+
| @thisname |
+————+
| first_name |
| first_name |
| first_name |
| first_name |
| first_name |
| first_name |
| first_name |
| first_name |
+————+
8 rows in set (0.00 sec)

Very smart