Shantanu's Blog

Database Consultant

November 16, 2007

 

SQLite Explained

// 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:


November 15, 2007

 

SQLite Manager : The best thing since sliced bread

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:


November 13, 2007

 

MySQL Case Study - 154

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 - 153

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:


November 02, 2007

 

6 PHP coding tips by Alex

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:


 

MySQL Case Study - 152

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 - 151

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:


Archives

June 2001   July 2001   January 2003   May 2003   September 2003   October 2003   December 2003   January 2004   February 2004   March 2004   April 2004   May 2004   June 2004   July 2004   August 2004   September 2004   October 2004   November 2004   December 2004   January 2005   February 2005   March 2005   April 2005   May 2005   June 2005   July 2005   August 2005   September 2005   October 2005   November 2005   December 2005   January 2006   February 2006   March 2006   April 2006   May 2006   June 2006   July 2006   August 2006   September 2006   October 2006   November 2006   December 2006   January 2007   February 2007   March 2007   April 2007   June 2007   July 2007   August 2007   September 2007   October 2007   November 2007   December 2007   January 2008   February 2008   March 2008   April 2008   July 2008   August 2008   September 2008   October 2008   November 2008   December 2008   January 2009   February 2009   March 2009   April 2009   May 2009   June 2009   July 2009   August 2009   September 2009   October 2009   November 2009   December 2009   January 2010   February 2010   March 2010   April 2010   May 2010   June 2010   July 2010   August 2010   September 2010   October 2010   November 2010   December 2010   January 2011   February 2011   March 2011   April 2011   May 2011   June 2011   July 2011   August 2011   September 2011   October 2011   November 2011   December 2011   January 2012   February 2012   March 2012   April 2012   May 2012   June 2012   July 2012   August 2012   October 2012   November 2012   December 2012   January 2013   February 2013   March 2013   April 2013   May 2013   June 2013   July 2013   September 2013   October 2013   January 2014   March 2014   April 2014   May 2014   July 2014   August 2014   September 2014   October 2014   November 2014   December 2014   January 2015   February 2015   March 2015   April 2015   May 2015   June 2015   July 2015   August 2015   September 2015   January 2016   February 2016   March 2016   April 2016   May 2016   June 2016   July 2016   August 2016   September 2016   October 2016   November 2016   December 2016   January 2017   February 2017   April 2017   May 2017   June 2017   July 2017   August 2017   September 2017   October 2017   November 2017   December 2017   February 2018   March 2018   April 2018   May 2018   June 2018   July 2018   August 2018   September 2018   October 2018   November 2018   December 2018   January 2019   February 2019   March 2019   April 2019   May 2019   July 2019   August 2019   September 2019   October 2019   November 2019   December 2019   January 2020   February 2020   March 2020   April 2020   May 2020   July 2020   August 2020   September 2020   October 2020   December 2020   January 2021   April 2021   May 2021   July 2021   September 2021   March 2022   October 2022   November 2022   March 2023   April 2023   July 2023   September 2023   October 2023   November 2023  

This page is powered by Blogger. Isn't yours?