GrassField

I have a mysql running in a box connected to a domain. I tried to connect it from a laptop not connected to the same domain. It is running as a workgroup. Though the parameters are correct, I got an error ‘Error Code: 1045′

An error occured while establishing the connection.
 Type: java.sql.SQLException   Error Code: 1045   SQL State: 28000
 Message:
 Access denied for user 'root'@'192.168.10.74' (using password: NO)

But I am able to connect to Oracle DB which is running in the same environment. Googling fetch me some results saying we need to give permissions suitable for the situation. I ran the following query which solved the connectivity issue.

GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'your_root_password';

Today I had a new install of Mandriva 2008 linux. I wrote a java code to test the mysql connectivity. It ended with the following exception.

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
Last packet sent to the server was 0 ms ago.

Alas, deeper google search gives me a solution that told about the mysql connectivity parameter in /etc/my.cnf

skip-networking

This parameter has been added for some security related reasons. Really I dont know what it is. I just removed that line, which solved this issue.

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;