Shantanu's Blog

Database Consultant

August 26, 2005

 

Open Up

Open Borders
Open Minds
Open Source
Open Office



GOT IT?

August 25, 2005

 

Forms in Word

Did you know that you can create "protected" forms using Microsoft word?
Use this form for getting information from new job seekers, employees or customers.

1. Start Word
2. Insert a table with 2 columns and 5 rows.
3. Type Name in the first cell.
4. Click in the next cell and then select "Text form field" button on Forms toolbar. If the forms toolbar is not visible, right click on any toolbar and choose Forms from the context menu.


5. Right click on the newly added form field and choose Properties. Set the Maximum length to 56 or any number you prefer. Select Title Case as text format for the name field. Now click on "Add Help Text..." button to create a tooltip that will help the applicant. The help message will also appear on the status bar.
6. Type Address in the next cell.
7. Add a text form field as explained above in the next cell.
8. Type City in the next row and add Drop down form field instead of Text form field. Add the city choices for e.g. Mumbai, Pune, Delhi.
9. Type Gender and create two Check box fields. Male and female.
10. In the salary Expected field add the Text form field as you did in the first step, but select "Number" as type instead of text so that the applicant can not type any text in the salary field (only numbers). You can also choose the format of the number.
11. Finally select Tools - Protect document and add a password. (Make sure that the "Forms" check box is selected.

Save the document and send it to clients/ students.
Now you can expect to get the information in the right "FORM"!

August 24, 2005

 

viksoe.dk - GMail Drive shell extension

viksoe.dk - GMail Drive shell extension:
GMail Drive enables you to save and retrieve files stored on your Gmail account directly from inside Windows Explorer. GMail Drive literally adds a new drive to your computer under the My Computer folder, where you can create new folders, copy and drag'n'drop files to.

August 23, 2005

 

Google Talk

Google Talk: "Google Talk enables you to call or send instant messages to your friends for free–anytime, anywhere in the world."

 

A great collaboration tool

If you are looking for simple collaboration software for your organization, here is the easiest and very powerful tool available called "TiddlyWiki".

http://www.tiddlywiki.com/empty.html

Right click on the above link and choose 'Save link as...' or 'Save target as...'.
Create a new directory (for e.g. called 'todo') and the save the file. You can decide what to call it (but keep the .HTML extension). Do not use the File/Save command in your browser to save TiddlyWiki.

Now open the file from where you saved it.
Click on 'close all' link on the right.
Click on 'new tiddler' on the left.
Type the title and then the text. Type tags related to the topic. Click on 'done' to save.

You can also create a new journal by clicking on 'new journal' link.

Within the main story column you can click on bold links to read a linked tiddler. Click on italic links within tiddlers to create a new tiddler, or use the 'new tiddler' button in the main menu. You can edit the text of any tiddler by double-clicking on it (or selecting 'edit' from the toolbar),


_____

To delete a Tiddler, Select the entry from Timeline, choose Edit and then click on delete.
Do not delete the entries those you have not created since those may be necessary to run the software.
_____

WikiWOrd is nothing but two capital letters in a word. The word will be automatically hyperlinked. You can alternatively use the "New tiddler" link on the left navigation. To make a tiddler that doesn't have a WikiWord as its name, you can enclose the name in [[Shantanu Prabhakar Oak]]. You can disable a wikiword by preceding the WikiWord with a tilde (~). For example, JamesBond, JavaScript and TiddlyWiki.

To make quoted bits of text stand out, you can use BlockQuotes within your [[tiddler]]s, like this:

JeremyRuston said:
<<<
This is indented text.
<<<

Like BulletPoints and NumberedBulletPoints, you can have three different levels of BlockQuotes. Just [[edit]] this tiddler to see how it's done.

>level 1
>level 1
>>level 2
>>level 2
>>>level 3
>>>level 3
>>level 2
>level 1

Creating BulletPoints is simple.
* Just add an asterisk
** start the line with two asterisks
*** use three asterisks for three levels

It's easy to create NumberedBulletPoints.
# Use a single '#' at the start of each line
## within any bullets


Headers:

!Header 1
!!Header 2
!!!Header 3
!!!!Header 4
!!!!!Header 5


insert images:

[img[Fractal vegetable|fractalveg.jpg]]
(This curious vegetable is called 'Romanesque broccoli' and is one of [[my photos|http://www.flickr.com/photos/jermy/]])


Word Formatting:

''Bold''
==Strike==
__Underline__
//Italic//
@@highlight@@
@@color(green):green colored@@
@@bgcolor(#ff0000):color(#ffffff):red colored@@

You can divide a tiddler into
----
sections by typing four dashes on a line by themselves


You can also have monospaced blocks (useful for source code):
{{{
var winBot = winTop + winHeight;
if(posTop < winTop)
return(posTop);
else if(posBot > winBot)
{
if(e.offsetHeight < winHeight)
return(posTop - (winHeight - e.offsetHeight));
}}}

_____

TiddlyWiki has almost limitless possibilities:

* It works great as a documentation manager for products, software, etc.
* It can store little bits of information, reminders, and notes
* It makes a great FAQ page.
* Turn it into a todo list, with items as tiddlers.
* Some use it as a blog.
* Some use it as a website.
* Make it your own personal dictionary/encyclopedia.

August 20, 2005

 

MySQL Case Study - 55

selecting into with substrings:
I have data in a text field such as
rank1 type1 name1
rank1 type2 name2
rank2 type1 name3
rank3 type 3 name 4

What I would like to do is break it apart so that the rank and type data are in separate columns. For the ranks, it wasn't so hard to do, I just did an

UPDATE table SET rank=rank1 WHERE text=LEFT(rank1,5)

for each of the ranks.

The next step is where I'm starting to run into problems. I need to update the text field to remove the rank information, and then do the same process with the types.
Any ideas on how to update the text field to remove the types?
Any ideas on how to do both steps at once?

August 19, 2005

 

MySQL Case Study - 54

MySQL Forums :: Newbie :: Find double (not uniqe) entries in a table: I've got a table that looks like this (offcourse, the real one is much bigger):

id| name | city
1 | john | 19
2 | bella | 11
3 | john | 12
4 | mike | 06
5 | alex | 12
6 | simon | 19
7 | alex | 11

.. and I want to select the 'id', 'name' and 'city' of those entries that the field
'name' isn't uniqe, in this case, john and alex.

How should my sql-query look like? The sql statements to recreate the table are...

CREATE TABLE `mydupes` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) default NULL,
`city` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `mydupes` VALUES ( '1', 'john', '19');
INSERT INTO `mydupes` VALUES ( '2', 'bella', '11');
INSERT INTO `mydupes` VALUES ( '3', 'john', '12');
INSERT INTO `mydupes` VALUES ( '4', 'mike', '6');
INSERT INTO `mydupes` VALUES ( '5', 'alex', '12');
INSERT INTO `mydupes` VALUES ( '6', 'simon', '19');
INSERT INTO `mydupes` VALUES ( '7', 'alex', '11');



Answer:

The simple way to find out the duplicates is as follows:

SELECT id, name, city, count(*) AS cnt
FROM mydupes
GROUP BY name
HAVING cnt > 1

id name city cnt
5 alex 12 2
1 john 19 2

_____

But if you want all records of alex and john
then you need a subquery joined by IN or EXISTS.

id name city
1 john 19
3 john 12
5 alex 12
7 alex 11

SELECT id, name, city FROM mydupes
WHERE name IN (SELECT name
FROM mydupes
GROUP BY name
HAVING COUNT(name) > 1)

SELECT id, name, city FROM mydupes as t1
WHERE EXISTS(SELECT name
FROM mydupes t2
WHERE t2.name = t1.name
GROUP BY t2.name
HAVING COUNT(t2.name) > 1)

_____

Assuming id is always unique, there is another way out.

SELECT id, name, city FROM mydupes t1
WHERE EXISTS (SELECT NULL
FROM mydupes t2
WHERE t1.name = t2.name AND t1.id <> t2.id)

If id by itself is not unique, you can add the city field too:
SELECT id, name, city
FROM mydupes t1
WHERE EXISTS (SELECT NULL
FROM mydupes t2
WHERE t1.name = t2.name AND (t1.id <> t2.id OR t1.city <> t2.city))

 

MySQL Case Study - 53

MySQL Forums :: Newbie :: Merge Tables ?:
Has anyone tried to merge two tables in MySQL ? It seems to be a headache !

What I have are two tables that I want to merge into one.

Lets take an example ...

TABLE A
-----------
|NAME|TOWN|
------------------------
1.|PIET | KLAWER |
2.|JOHN | PARYS |
3.|KOOS| | DURBAN|

TABLE B
----------
TOWN| PCODE|
-----------------
1.KLAWER|1212|
2.PARYS|1234
3.DURBAN|4000

Ok, assuming you understand and my Graphics turn out OK,

I want to now create TABLE C - Which should look like ..

TABLE C
-----------
|NAME|TOWN|PCODE
------------------------
1.|PIET | KLAWER |1212
2.|JOHN | PARYS |1234
3.|KOOS| | DURBAN|4000


Any help will be much appreciated !

 

MySQL Case Study - 52

MySQL Forums :: Newbie :: Avoid couples in the resultset:
I use a query like this:
SELECT t1.id_topics, i1.id_indices, t2.id_topics
FROM indices i1, indices i2, topics t1, topics t2
WHERE (i1.ind = 'test' AND i1.id_urls = t1.id_urls
AND i2.id_urls = t2.id_urls AND i1.ind = i2.ind
AND t1.id_topics <> t2.id_topics);

it gives results like those:
'id_topic1','id_indices','id_topic2'
36,682,34
37,682,36
36,682,37
37,682,34

I would like to eliminate the 'inverted' mates of couples in the resultset. That means in the example
'36,682,37' should be eliminated since '37,682,36' is already part of the resultset. Is it possoble to express this request in the query?

 

MySQL Case Study - 51

MySQL Forums :: Newbie :: Pulling my hair out with this query:
I have a PHP/MySQL database that has student grade data. Every student has at least one row of data, though they may have up to 3 rows depending on how many years they have been enrolled (each year gets one row).

Here is a very simplified view:

NAME|YEAR|TEST1|
------|------|-------|
John-|2002|---80|
Anna-|2002|---67|
John-|2003|---78|
Bill---|2003|---76|
Anna-|2003|---77|
John-|2004|---88|
Bill---|2004|---77|
Phil--|2004|---76|

*Note that John is there for all 3 years, Anna for only the first two, Bill for only the last 2, and Phil came in only in the last year.

The data needs to be displayed on the web page ordered by name with the 3 years in columns exactly like this:

Name|2002|2003|2004|
------|------|------|------|
Anna-|--67|--77--|null|
Bill---|null-|--76--|--77|
John-|--80-|--78-|--88|
Phil--|null-|null---|--76|

Currently I created 4 dataset queries like this:

'Select distinctrow NAME from mydata ORDER BY NAME'; //returns all names
'SELECT * FROM mydata WHERE `YEAR` = 2002 ORDER BY NAME';
'SELECT * FROM mydata WHERE `YEAR` = 2003 ORDER BY NAME';
'SELECT * FROM mydata WHERE `YEAR` = 2004 ORDER BY NAME';

I then use the result set to populate the columns.

The problem is that since not every student has data in all 3 years, the columns begin to shift and the data is placed in the incorrect row like this:

Name|2002|2003|2004|
-------|-----|------|-----|
Anna-|--67-|--77-|--77|
Bill---|--80-|--76-|--88|
John-|------|--78-|--76|
Phil--|------|null--|----|

Is there a way to create ONE query to return data for all of the students, but insert null values where there is no data? Is this done through a 'join' statement? What's the best approach?

Please help, I'm pulling my hair out!

August 14, 2005

 

A New World Economy

A New World Economy: "As Cisco's Scheinman puts it: 'We came to India for the costs, we stayed for the quality, and we're now investing for the innovation.'"

August 12, 2005

 

MySQL Case Study - 50

I have a student Table and a SubjectGrade table

Create Table Student(
StudentID INT NOT NULL,
StudentName VARCHAR(30)
)

Create Table SubjectGrade(
StudentID INT NOT NULL,
Subject VARCHAR(30) NOT NULL,
Grade CHAR(1)
)

let's say have following record in SubjectGrade

1 Maths A
1 Phys B
1 Chem A
2 Maths A
2 Chem A
3 Bio C
3 Chem A

I want to find out students who have got A in both Maths and Chem
How the SQL look like?

I am using mysql 4.0.25 with no subquery support.

 

MySQL Case Study - 49

I'm having difficulty writing a query as follows. I'm unsure if I need a
subquery, a union or if this isn't actually possible without using
temporary tables.

I have two tables for members.

Table 1 (members) Table 2 (payments)
+----------+------------+ +---------+--------------+
| memno | group | | memno | payment_type |
+----------+------------+ +---------+--------------+
| 1 | a | | 1 | cash |
| 2 | b | | 2 | cash |
| 3 | a | | 3 | creditcard |
| 4 | a | | 4 | check |
| 5 | c | | 5 | creditcard |
... ...

I'd like a query that returns the total number in each group, together
with the number paying by credit card.

Obviously I can build two queries, and use a temporary table, but is
there a way to get a table like that below in a single query?

+----------+------------+------------------+
| group | members | pay_by_card |
+----------+------------+------------------+
| a | 5 | 3 |
| b | 26 | 18 |
...

This will be using MySQL 5 if that helps

 

MySQL Case Study - 48

I'm searching a table of people who own properties, and I want to also include the total count of related properties, and the count of related properties whose (status is 'Active' and approval is 'Active'). I've got:

select accounts.name, count(properties.property_id) as totalcount
from accounts, properties
where accounts.account_id=properties.account_id
group by accounts.account_id;

Works fine. Now I just need to figure out how to add that second count of property records meeting the two conditions. Anyone?

 

MySQL Case Study - 47

I have two tables: news and comments.

i want to count the number of comments for each newsid and update the
count in news.comment

comments.newsid belongs to news.id

can i do this with sub queries? im using v4.1.x
i have many records in the comments table so im not sure what the most
efficient way to do it.

i will likely run this as update every few minutes because doing a join
on the fly is going to hurt performance when i need to display the
comment count on the webpage i think.

any help?

table: news
+----+-----------+
| id | comments |
+----+-----------+
| 26 | 0 |
| 21 | 0 |
| 29 | 0 |
+---------------

table: comments
+-----+--------+
| id | newsid |
+-----+--------+
| 1 | 26 |
| 2 | 21 |
| 3 | 29 |
| 4 | 29 |
| 5 | 29 |
+-------------+

 

MySQL Case Study - 46

I have a table name "p" like that:

mysql> select * from p;
+-------+-------+--------+
| id | price | vendor |
+-------+-------+--------+
| OG012 | 40 | a |
| OG012 | 20 | b |
| OG012 | 20 | c |
| OG013 | 40 | c |
+-------+-------+--------+

and I have another table name q:
mysql> select * from q;
+--------+--------+
| vendor | rating |
+--------+--------+
| a | 1 |
| b | 2 |
| c | 3 |
+--------+--------+

My question is how do I find the minimal price for each id and vendor rating is highest.

I wish my result is:

+-------+-------+--------+
| id | price | vendor |
+-------+-------+--------+
| OG012 | 20 | b |
| OG013 | 40 | c |
+-------+-------+--------+

Since vender b and c are the same price for id(OG012), but vendor b has minimal rating then vendor c.

August 11, 2005

 

Order Order!!

In which order does MySQL store the data? When I insert a new record does it gets stored at the end?

No. You can not control the way MySQL stores the records. You cannot expect your results to be ordered wihout an explicit ORDER BY clause. Without ORDER BY, mysql is free to return rows in any order it chooses (generally in the order they are found). For example:

CREATE TABLE atable (pk INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
acol CHAR(3), bcol INT, ccol CHAR(3),
INDEX an_idx (acol, bcol, ccol)
);

INSERT INTO atable (acol, bcol, ccol)
VALUES ('aaa',1,'aaa'), ('bbb',1,'aaa'), ('aaa',3,'aaa'),
('aaa',2,'abc'), ('aaa',1,'bbb'), ('bbb',3,'abc'),
('aaa',2,'aaa'), ('aaa',1,'abc'), ('abc',3,'def'),
('bbb',2,'aaa');

DELETE FROM atable WHERE bcol = 3;

INSERT INTO atable (acol, bcol, ccol)
VALUES ('ccc',1,'aaa'), ('ccc',2,'aaa'), ('ccc',1,'bbb');

SELECT pk, acol, bcol, ccol FROM atable;
+----+------+------+------+
| pk | acol | bcol | ccol |
+----+------+------+------+
| 1 | aaa | 1 | aaa |
| 2 | bbb | 1 | aaa |
| 13 | ccc | 1 | bbb |
| 4 | aaa | 2 | abc |
| 5 | aaa | 1 | bbb |
| 12 | ccc | 2 | aaa |
| 7 | aaa | 2 | aaa |
| 8 | aaa | 1 | abc |
| 11 | ccc | 1 | aaa |
| 10 | bbb | 2 | aaa |
+----+------+------+------+
10 rows in set (0.00 sec)

You see? Without ORDER BY, the rows are returned as they are found. Note that rows from the second INSERT are where the deleted rows were.

An exception may occur if you only ask for columns which are covered by a single index. In that case, mysql may choose to read the data from the covering index, rather than from the table. Then you'd get results in index order:

mysql> SELECT pk FROM atable;
+----+
| pk |
+----+
| 1 |
| 2 |
| 4 |
| 5 |
| 7 |
| 8 |
| 10 |
| 11 |
| 12 |
| 13 |
+----+
10 rows in set (0.00 sec)

mysql> SELECT acol, bcol, ccol FROM atable;
+------+------+------+
| acol | bcol | ccol |
+------+------+------+
| aaa | 1 | aaa |
| aaa | 1 | abc |
| aaa | 1 | bbb |
| aaa | 2 | aaa |
| aaa | 2 | abc |
| bbb | 1 | aaa |
| bbb | 2 | aaa |
| ccc | 1 | aaa |
| ccc | 1 | bbb |
| ccc | 2 | aaa |
+------+------+------+
10 rows in set (0.00 sec)

I wouldn't count on this though. If you need ordered results, you need ORDER BY.

The indexed records are always stored in ascending order.
Note the last line of this excerpt from The Fine Manual :-)
http://dev.mysql.com/doc/mysql/en/create-index.html

> An index_col_name specification can end with ASC or DESC. These keywords
> are allowed for future extensions for specifying ascending or descending
> index value storage. Currently they are parsed but ignored; index values
> are always stored in ascending order.

August 09, 2005

 

Angel Investor News.com

Angel Investor News.com: "Many times I want to strangle the writer to simply tell me what they do in five words or less."

August 08, 2005

 

MySQL Case Study - 45

I have a table candidateSkills where I store the skillName and skillDuration. The table looks like:

skillid | ReumeId| skillName | skillDuration |
---------------------------------------------
1. | 1 | java | 32 |
2. | 1 | jsp | 43 |
3. | 1 | oracle | 34 |
4. | 2 | jsp | 48 |
5. | 2 | oracle | 83 |
6. | 3 | jsp | 67 |

I want to query such that I get the resumeid where the skillName is jsp and the skill duration is > 10
and the skill name is oracle and skillDuration is > 15.

While I was using oracle, the sql query that worked was

SELECT ResumeId from candidateSkills where skillName = 'jsp' AND
skillDuration > 10
INTERSECT
SELECT ResumeId from candidateSkills where skillName = 'oracle' AND
skillDuration > 15

The output was [1, 2]

But, I see that mysql does not support intersect. How do I go about ?

August 07, 2005

 

MySQL Case Study - 44

I'd like to know a way to know which users had a birthday in last 7 days...

Example, I've this:

Name - Born
John - 1963-12-24
Lisa - 1979-11-23
Peter - 1981-08-01

Then, script should return "Peter, 1981-08-01"

The php script will be execd through cron once a week, and no matter which day it is executed, it must give the same result...

How could I do a query to acchieve this ?

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?