HowToDoThings.com Recent Databases Articles

How To Optimize MySQL Queries

kakulz — Mon, 18 Aug 2008 12:57:39 -0500

How To Code a Matrix Report in SQL

ndoshi — Wed, 12 Mar 2008 15:45:05 -0500

A matrix report is a grid-like representation of data. It has a row of labels, a column of labels and, in a grid, data related to both row and column. Matrix reports are also called crosstab reports.

A matrix report has one group of data for rows, one group of data for columns, one group of data cross-product and one group of data as the value for cross-product. The cross-product group represents all possible combinations of row and column groups.

Let's take a look at an example. Assume you have a table called Inv_summary. It has three columns: Inv_date, Inv_status and Inv_value. Now, you want to generate a report that shows total value of open and close invoices for each month, for example like below.

MONTH OPEN CLOSE

-------- ------------ ------------

How To Add Day, Hour, Minute, Second to a Date Value in Oracle

ndoshi — Mon, 03 Mar 2008 16:46:14 -0600

Date arithmetic is very common in database application. In Oracle, you can add, subtract and compare DATE columns, but you can not multiply or divide it. Oracle stores century, year, month, day, hour, min and seconds as part of the DATE column.

Now let’s take a look at how to add day/hour/minute/second to a date value. Oracle expects a number constant in date arithmetic as number of days. In other words, you need to convert hour, minute and seconds to fraction of a day and then you can add or subtract that value from a date value. Here are some examples:

How To Use Structured Query Language (SQL)

kakulz — Mon, 25 Feb 2008 13:49:37 -0600

Structured Query Language (SQL) is a computer language for creating database and manipulating data. SQL is an ANSI (American National Standard Institute) standard and is supported by almost all Relational Data Base Management Systems (RDBMS) like Oracle, MySQL, SQLServer, MS Access, PostGreSQL etc. SQL has two parts:

Data Definition Language (DDL): to create, alter, or drop tables and indexes.
Data Manipulation Language (DML): to insert, update, retrieve or delete the data in the tables.

Here's how to use SQL.

How To Get Rid of Duplicate Rows in an Oracle SQL Table

ndoshi — Thu, 31 Jan 2008 11:18:09 -0600

If you are using Oracle Database, a common problem is duplicate rows in tables. Usually this happens when unique constraints are removed during loading. Whatever may be the reason, you can remove duplicate records. Duplicate records have identical values for all columns (OR columns that are part of unique key).

There is more than one way to delete duplicate records. Let's assume Table_A has the following data:

Col1 Col2
------ -------
101 201
102 202
103 203
102 202
104 204
101 201

Let's look at the first method.

How To Use Formatting in Oracle SQL

miniguru — Thu, 25 Oct 2007 14:20:26 -0500

Once you retrieve your data, you should think about how you would like to view it. You can do a lot of formatting in Oracle SQL, which translates into saved time for you. After all who wants dirty data?

Formatting is a presentation issue. It allows you to present the data you have gathered so that it looks good. And as someone once said, "Perception is key." I use the following 8 formatting tricks. There are many more, but the ones below are essential to know.

How To Use Oracle SQL

miniguru — Thu, 20 Sep 2007 14:31:33 -0500

SQL is shorthand for Structured Query Language. Oracle SQL is a language and not an application. It is a language that can be used to communicate with databases. Oracle SQL can be used to query databases and analyze data.

Before you use Oracle SQL, you need to understand what a database is. What is a database? It’s a collection of data stored in an organized fashion. The data is stored as Tables inside of a database. Data within tables are stored as rows. All data within a database is either a column or a row. Vertical data is called a column, and horizontal data is called a row.

Oracle SQL is composed of keywords such as SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY. The 6 keywords mentioned above allow you to query a database and analyze its data.

How To Learn MySQL Commands

Daniel452 — Thu, 22 Feb 2007 22:13:00 -0600

A database organizes data so that it can be easily accessed, altered, and managed. Like virtually all database software, MySQL uses Sstructured Query Language (SQL-pronounced sequel), the standard language for requesting information from a database. It is an open-source software, meaning that its free to use and modify for your personal use. Despite it's low price, MySQL has increasingly found wide adoption in industry because of its fast performance, reliability, ease of use, and versatility in working with programming languages. Join the 5 million active users of MySQL including NASA, FedEx and the computer nerd down the block by learning to use the basic asdcommands of MySQL.

How To Convert Access to MySQL

Daniel452 — Mon, 02 Oct 2006 23:44:00 -0500

Both Microsoft Access and MySQL have found wide use in many companies though for different reasons. The main advantage of Access is its relative ease of use while MySQL's strength comes from its versatility. MySQL's versatility allows it to work in conjunction with programs written in various languages as well as even Access. It allows multiple-user access, makes management of large databases easier, provides increased security, and simplifies backup management. In addition MySQL is provided free of charge. To migrate from an Access to MySQL we will be using a free program called DBTools. DBTools has a feature that allows us to directly import data from Access files.

How To Install MySQL

Daniel452 — Sun, 01 Oct 2006 21:52:00 -0500

MySQL (pronounced My-Ess-Cue-Ell) is a powerful open source relational database management system that uses SQL. This means that MySQL stores data in tables rather than in one large file. This allows for greater efficiency, flexibility, and speed. MySQL is often used in conjunction with other products from the open source community such as PHP and Apache. This tutorial will teach you how to install and configure MySQL on your computer.

Backup remote DB locally

Peter5 — Thu, 07 Aug 2003 23:00:00 -0500

How to backup a remote database directly to the local machine.

My company had a server with a nearly-full hard disk. I needed to backup the database but didn't have enough disk space to store both database and backup.

IBConsole executes the backup command on the server and only seems to allow the backup file to be written to the same machine. To backup to my local machine I had to use GBAK in a command prompt, like so

gbak -b -z ServerName:C:\DatabasePath\DatabaseName.gdb c:\MyLocalBackupPath\MyLocalBackupName.gbk -verbose -user

sysdba -password masterkey

This did the trick!

Microsoft access cannot start

Peter5 — Wed, 23 Jul 2003 23:00:00 -0500

A solution to the error message "Microsoft access cannot start because there is no license for it on this machine".

I have seen this error arise a few times now. It often occurs after you have installed MS Access. When you try to start access you are presented with the error.

"Microsoft access cannot start because there is no license for it on this machine".

Access then closes.

Strangely enough this problem is caused by a font. To fix the problem follow these instructions.

1) Find the font named Hatten.TTF in your {windows}\fonts folder (usually c:\windows\fonts).

2) Rename the file to Hatten.TTFX

3) Reinstall MS ACCESS

4) Rename the font file back to Hatten.TTF

Access will now start without any problems.

Make ADO connection string at runtime

Fri, 07 Feb 2003 23:00:00 -0600

How to use ADO connection string dialog in your application

Udocumented, can be found in ADODB.

NewConnectionString := PromptDataSource (0, CurrentConnectString);

NewConnectionString and CurrentConnectString are strings.

You can leave CurrentConnectString blank or replace with '',

if you pass it the dialog will show up with current configuration.

Backup to another PC

Peter5 — Thu, 06 Feb 2003 23:00:00 -0600

How to backup another PC to the local machine.

The following GBAK command will backup a database on another server to the local machine.

GBAK -B -user SYSDBA -password masterkey servername:c:\data\database.gdb c:\BackUps\localbackup.gbk

Tables, fields, and foreign keys

Peter5 — Wed, 26 Sep 2001 23:00:00 -0500

A complicated SQL query I had to work out to tell me information about the current database.

This query will tell you

TableName, FieldName, FieldType, FieldSubType, ForeignTableName, ForeignFieldName

select distinct

  Rel.rdb$relation_name TableName,

  Rel.rdb$field_name FieldName,

  Fld.rdb$field_type FieldType,

  Fld.rdb$field_sub_type FieldSubType,

  FCon.rdb$Relation_Name ForeignTableName,

  FIseg.rdb$Field_Name ForeignFieldName

from

  rdb$relation_fields Rel

  left join

    rdb$relation_constraints Con

  on

    (Con.rdb$relation_name = Rel.rdb$relation_name and

     Con.rdb$constraint_type like 'FOREIGN%')

  left join

    rdb$indices IDX

  on