Selectable – The QueryCell Blog

Hi

Just a quick post today.

So what is the main difference between Microsoft Excel and Relational Databases such as MS Access, SQL Server, Oracle, DB2, Postgresql, Mysql, Firebird etc ?

The main difference is that Excel focuses on individual data items, cells, a databases focus on collections of data items, rows.

Think about how you edit data within Excel, you select the cell you are interested in and modify the value. In a database data is edited (updated in database parlance) by using an Update Statement. Update statements operate on all rows that match the specified criteria.

Of course you … Continue Reading

Suppose you wanted to know whether a cell contained one of four permissible values :

NO SALE
GIFT
SL-XXXX

Where XXXX is an integer value. ‘SL-9839′ for example.

In Excel you would be able to use a formula such as

=OR(ISNUMBER(MATCH(“GIFT”,A7,0)),ISNUMBER(MATCH(“NO SALE”,A7,0)),ISNUMBER(MATCH(“SL-$???”,A7,0)))

A7 – The cell whose value you wish to check
Match :
The Match function searches for a value in an array and returns the relative position of that item.
If no match is found it will return a #N/A error.
Match( value, array, match_type )
value is the value to search for in the array.
array is a range of cells that contains the value that you are … Continue Reading

Question

I have a table of data containing an employee name and the date they were hired, how can I get Excel to calculate the length of employment of each employee based on the date they were hired ?

Answer :

Well suppose your excel table looks like this :

Employee_Name
Hired_date

J Smith
10/07/1980

A Jones
6/03/2008

R Shah
12/12/2007

B Chen
15/02/2009

I Holmes
1/01/2010

** Note : **
We are going to be using the database DATEDIFF function. The column containing the date value must be named
something ending in _DATE in order for QueryCell to realise it … Continue Reading

Question

I sell widgets, I have a table of orders which includes the number of widgets for that order.

I would like to generate a discount percentage for each order based on the rule :

1 – If the order is for 10 or less widgets the discount is 10%
2 – For every 5 additional wigets I want the discount to go up by a percent.

So an order of 10 widgets gets a 10% discount, an order of 15 widgets gets an 11% discount, an order of 20 widgets gets
a discount of 12% etc…

Answer :

Well suppose … Continue Reading

Continuing on from the last post we are going to look at the StackOverflow data to see which SQL related questions are garnering the most interest.

Firstly we will look at the top five SQL related questions the questions based on the number of comments they have received.

Question
Num Comments
Selectable Blog Comment

I have SQL SERVER database that have a huge proliferation of stored procedures. In my Oracle databases it’s not a problem because Oracle supports packages.

Even if SQL Server didn’t support the cool features of packages like State and encapsulation, what do programmers do to get around this … Continue Reading

There are plenty of communities and forums that can help with your SQL and Excel problems.

ExcelForum , Mr Excel and OzGrid are a couple of examples of the best Excel communities around, but there are many out there.

One of the most recent and popular communities to spring up is StackOverflow

StackOverflow is a general question and answer site for technical programming problems.

Since StackOverflow opens up it’s data for public analysis we can use it to get an insight into the nature of Excel and SQL users as part of the overall programming / technical community.

To do this … Continue Reading

Allan and Wilson books is going to be presenting information about their new titles to distributors and book stores all around the country.  Read how PowerMerge and QueryCell help them do it quicker, easier and with less mistakes.

My name is Jenny Malhouse and I’m the general IT manager at Allan and Wilson books.  When I say IT Manager I mean manager, developer, administrator, innovator and dogsbody.  Since we are a small ( but growing ) company everyone has to pitch in where they can. I find myself handling most of the computer issues that crop up.

Over a few months our … Continue Reading

I’m going to take a look at the professions and university degrees of national leaders and see if anything interesting comes out.
Notes :
I looked at the leader of a country on the 19th day of the 9th month of the 9th year of every decade.

The countries I included were United States, United Kingdom, China, Australia, France and India.

For India I thought it reasonable to include prime ministers since independance, starting with
Nehru in 1949.

The Chinese history was by far the most confusing. There have been presidents,
Chairmen of the Communist Party, General Secretaries and … Continue Reading

My thought process in determining a primary key goes like this.

“One record in this table will represent …?”

… Continue Reading

I’m going to take a look at the 25 most viewed videos on youtube, just for fun, to see if anything interesting comes out.
Notes :
The youtube data I am looking at is from the 8th of September 2009.
The Location setting was ‘WorldWide’.  You will see a different list of ‘Most Views’ videos if your Location setting is for your specific country, which I believe it is by default.

#
posted date
Title
Views Num
Comments Num
Video Responses Num
Category
User

1
4-Jun-2006
Evolution of Dance link
125,567,407
392,010
1550
Comedy
judsonlaipply

2
27-Feb-2007
Avril Lavigne – Girlfriend … Continue Reading