Shantanu's Blog

// download and unzip the world database from
// http://saraswaticlasses.net/download/sql/lite.zip
// Start - Run... - command
// Change directory to the one where sqlite3.exe can be found
// start lite with world6 database in use
sqlite3.exe world6

// Show all tables
.tables

// select everything from City table and save it as CSV file
.mode csv
.output mycsv.csv
select * from City;

// change the output mode to screen
// else the output of select statements will be saved in the same csv file
// show the resuts on to the sceen
.output stdout

// create a table city2 based on City and import from CSV file
create table City2 as select * from City;
.import mycsv.csv City2

// select everything from CountryLanguage table and save it as html file
// Tip: you may need to add the table border = 1 and /table tags
.mode html
.output myfile.html
select * from CountryLanguage;

// backup table using sql statements
.output myfile.sql
.dump City

// backup selective records in sql format instead of entire table
.output myfile.sql
.mode insert
select * from City limit 10;

// recreate table City from the SQL statements source
// tip: you may need to delete the begin transaction and commit lines from the source
drop table City
.read myfile.sql

// import csv data into an existing table
.separator ,
.import myexcel.csv test

// show the resuts on to the sceen
.output stdout

// ask for help
.help

Labels: sqlite

So basically, am I just saying that a firefox extension to manage SQLite is the best thing to have happened since sliced bread? May be a bit of exaggerated statement. But this is really useful and has become an indispensable tool for me.

How many of you know that firefox has a built in relational database Management System called SQLite? Most of the info like profile search is saved in it. Mrinal Kant has written an extension so that I can have the GUI to the engine.

1) Download the test sql database from...
http://saraswaticlasses.net/download/sql/lite.zip

2) Install the firefox extension from....

https://addons.mozilla.org/en-US/firefox/addon/5817

3) From Tools choose SQLite Manager.

4) Click on "Open Database" link and navigate to the folder where you have downloaded and unzipped the file mentioned above. Select the 'world9' database to play with. The database has 3 tables viz. City, Country, CountryLanguage. There are no indexes or views. (You will find another database called world3 for that purpose)

5) To select the cities and population of Indian Cities, run the following statement.

CREATE TABLE india as SELECT c.Name, c.District, c.Population
FROM City AS c INNER JOIN Country AS c1
ON c.CountryCode = C1.Code WHERE c1.Code = 'IND'

Now select the table and then click on "Export Table" option on 'Structure' tab.

SQLite is the best solution for small and medium sized companies and the people who have to manage a lot of data. It does not, in any way, compete with the complete and full RDBMS like MySQL or Oracle. The people who have outgrown their database requirement from Excel and do not need an elephant like Oracle will find that this is the perfect solution for them. I did not mention about it in the past because I could not suggest any GUI to manage the data. And I just noticed that the SQLite Manager does exactly what I want it to do.
Nice work.

Labels: sqlite

Amazon like collaborative filtering

Hi, i am trying to create a very basic version of "people who bought this also bought that". so i have a table1 and table2, they look like this table1 is all the products with details. Table2 is who bought what. the product_id in table2 is the table1's id for the product.

table1
id | name
1 | apple
2 | orange
3 | banana
4 | milk
5 | chocolate

table2
user | product_id
john | 4
mike | 3
mike | 1
john | 2
john | 5

so lets say i wanna know " what other products were purchased by those who purchased milk ". what do i do. i need to print out the names of the products excluding the milk itself. I imagine the query has to connect table1 and table2 then find all the users who purchased milk in table2 and then find all the products purchased by those users and then connect those products with table1 to get their names.
can all this be done in just 1 MySQL query?

http://forums.mysql.com/read.php?10,182205,182205#msg-182205

Labels: mysql case study

Query to show all names starting with "M,K,C OR J"

I am trying to produce the names subject to first character.
I am only getting the names with M with the following query.
I want to show all names starting with "M, K, C OR J"

SELECT Ename, LENGTH(Ename)
FROM Employee
WHERE Ename LIKE 'M%'OR 'K%'OR'C%'OR'J%';

http://forums.mysql.com/read.php?10,181129,181129#msg-181129

Labels: mysql case study

I really liked the Alex Netkachov approach of "when there is no variable, then there is no problem."

http://www.alexatnet.com/node/100

1) We are too much dependent on variables and I will be happy to avoid them where ever I can. The reason why we are so liberal while using them is that we have seen everyone using it for almost everything, even if it's not needed. I have seen and used the OR keyword as suggested by Alex. Where? mmmm... yes, now I remember, while connecting to the database.

mysql_select_db('database_name', $g_link) or die('Could not select database.');

But I never realized that the OR keyword can be used anywhere in the script as well. Like this...

fwrite($h, 'some text') or log('Writing failed');

Fantastic. Point noted!

2) I did actually googled for the syntax of ternary operator a while back. I wanted to use it in the function because my function was getting too lengthy to manage. So here it is...

$message = ($age < 16) ? 'Welcome!' : 'You are too old!';

And what about the second method of making the code shorter?

$message = 'You are too old!';
if ($age < 16) {
$message = 'Welcome!';

Clever!

3) Though 'while' looks more logical and readable, 'for' still wins!

Everyone will agree with the points 4, 5 and 6.

So overall 100 marks out of 100!
Thank you Alex.

Labels: php

Is it possible to 'see' the duplicate records?
In my table i want to see all records where values in colA AND colB occur more than once.
A B C D
1 2 3 4
2 3 4 5
3 4 5 6
4 5 6 7
2 3 9 9
2 2 9 9

so i want to see (select) rows 2 and 5

Any ideas, please?

i can get what causes duplicates by this...
SELECT colA,colB,colC,colD,COUNT(*) FROM t1 GROUP BY colA,colB HAVING COUNT(*)>1
but then i have to
SELECT * FROM t1 WHERE colA=2 AND colB=3
but i need a lot of SELECT statements for my large tables!!

http://forums.mysql.com/read.php?10,180556,180556#msg-180556

Labels: mysql case study

I have a Table in which one value can be repeated 2 times at the most. The fact is that i need to find just the values in which column 2 has a specific value and the register at column 1 is NOT repeated.

Supose this:

column1 | column2
----------+-------------
123 | 0
123 | 1 <--- the value of "123" is repeated so we dont want it.
456 | 0
789 | 0
111 | 1 <--- this is not repeated but it has a value of "1" at col2 so we dont want it neither.


How can i find all the "0" (zeroes) in which there exist one and only one row for each col1 value (the value at col1 is not repeated).

In the given example the values im looking for are "123", "456" and "789".

http://forums.mysql.com/read.php?10,180777,180777#msg-180777

Labels: mysql case study