Tutorials in ‘Database-Servers’ Category

How to backup and restore your MySQL database or table structure to in Linux

Whenever there is a database involved, an important task that should never be neglected is backing it up. In the case of MySQL on a Linux machine, backing up consists of executing a single command.
Backing up a MySQL database can be done with one simple command:

mysqldump -h <host> -u <user> -p<password> <db_name> > backup.sql

For example if you want to backup a local database called db1 using the user user1 with password pass1 you should write:

mysqldump -h localhost -u user1 -ppass1 db1 > backup.sql

The backup.sql file contains an SQL query for creating the exact same database. Restoring such a dump-file is just a matter of one command too:

mysql -h <host> -u <user> -p<password> <db_name> < backup.sql

Following the naming from our previous example will result in the following command to restore the database:

mysql -h localhost -u user1 -ppass1 db1 < backup.sql

In the perfect environment the backup should be created by a cronjob or anacronjob.
Another useful thing that one may want to backup is the table structure. For tables whose structure is relatively often changed this can prove to be very helpful. It is done by using one simple modification of the command for backing up an entire database:

mysqldump -d -h <host> -u <user> -p<password> <db_name> > backup.sql

Did you notice the difference? It is the -d option, which specifies that data should not be dumped. Using our previous examples this will be transform to:

mysqldump -d -h localhost -u user1 -ppass1 db1 > backup.sql
VN:F [1.9.18_1163]
Rating: 6.3/10 (3 votes cast)

How to Round a Numeric Column to a Specified Decimal Count in SQL Server 2008 R2

This tutorial will show how to round a column to a specified decimal count. This is possible with the ROUND function. The ROUND function is used to round a numeric field to the number of decimals specified. It returns a numeric value, rounded to the length or precision specified by the programmer.

Setting Up

In this tutorial we will create a table called Products with columns of Prod_Id, ProductName, Unit, and UnitPrice. Prod_Id uses a data type of int, ProductName and Unit will be of the nvarchar data type, and UnitPrice will be of the float data type. To create a table in SQL Server, we use the CREATE TABLE statement.

ProductsTable.sqlce
Create a Products table.
CREATE TABLE Products
(
Prod_Id int not null,
ProductName nvarchar(30) not null,
Unit nvarchar(30) not null,
UnitPrice float not null
);

Now that we have a table created, we can add data into it. To do this, we use the INSERT INTO, SELECT, and UNION statements. The INSERT INTO statement specifies what table and columns to insert values into, the SELECT statement is the data being inserted into the table, and the UNION statement prevents duplicates from being inserted into the table… Read the rest of this entry »

VN:F [1.9.18_1163]
Rating: 0.0/10 (10 votes cast)

How to Handle Null Values with the COALESCE Function in SQL Server

This tutorial will show how to use the COALESCE function to handle null values at run time. The COALESCE() function is used to specify how we want to treat NULL values. It returns the first non-null expression among its arguments.

Setting Up

In this example we will create a table called Products with columns of P_Id, ProductName, UnitPrice, UnitsInStock, and UnitsOnOrder. To create a table we must use the CREATE TABLE statement.

CreateProductsTable.sqlce
Create a Products table.
CREATE TABLE Products
(
P_Id int not null,
ProductName nvarchar(50) not null,
UnitPrice float not null,
UnitsInStock int not null,
UnitsOnOrder int null
)

Yes, it is possible to find a good web host. Sometimes it takes a while to find one you are comfortable with. After trying several, we went with Server Intellect and have been very happy thus far. They are by far the most professional, customer service friendly and technically knowledgeable host we’ve found so far.

Notice we added null to the UnitsOnOrder column, as we will need a null value to work with when using the COALESCE function.

Now that we have our table created, we need to insert data into it. This is done with the INSERT INTO, SELECT, and UNION statements. INSERT INTO specifies where we want to enter the values, SELECT are the values we want to insert, and UNION does not allow duplicate values.

InsertProductsValues.sqlce
Insert data into the Products table.
INSERT INTO Products (P_Id, ProductName, UnitPrice, UnitsInStock, UnitsOnOrder)
SELECT 1, ”Soda”, 10.45, 16, 15
UNION
SELECT 2, ”Bread”, 32.56, 23, null
UNION
SELECT 3, ”Apples”, 15.67, 9, 20

COALESCE Function Query

So now that we have our table set up with data, we are now able to write query to manipulate the data. What we want to do is handle null values in the UnitsOnOrder column with the help of the COALESCE function at execution of the query.

In this query we will select the ProductName and UnitPrice columns, but we will set unit price to multiply the total of UnitsInStock and UnitsOnOrder columns. Meaning we must first add UnitsInStock and UnitsOnOrder and then multiply that number with the UnitPrice to get the total we are looking for. As you may have noticed, we intentionally put a null into the Bread entity so we can change it when called in the COALESCE function.

CoalesceFunction.sqlce
Use the COALESCE function to handle the null values.
SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder,0)) AS UnitPrice
FROM Products Read the rest of this entry »

VN:F [1.9.18_1163]
Rating: 0.0/10 (10 votes cast)

Using SQL SELECT INTO statement

Quite often, when managing SQL databases, we wish to create a copy of a whole table or a few columns of it. This comes handy when we want to create a backup of a table or to archive a part of a table and is done quite easy using the SELECT INTO statement.
Let’s review this with a few examples.

The syntax for copying a whole table is:
Read the rest of this entry »

VN:F [1.9.18_1163]
Rating: 0.0/10 (0 votes cast)

Using SQL views

Views are virtual tables. Basically they represent a part of a result set. Views are used both for managing access to data (in combination with rights management) and for creating an interface to the database so that if the structure of the database is altered the changes to the software using it are minimal.

A view has the following syntax:
Read the rest of this entry »

VN:F [1.9.18_1163]
Rating: 0.0/10 (0 votes cast)

Using the SQL Auto Increment field

Quite often, when creating a new SQL table, one can’t simply think of a proper column for the Primary Key. Whether it is because of the structure of the table or because you are creating it in the middle of the night, you decide to add a column containing consecutive ints. But each time you insert a new row you must be careful to use a unique int. Isn’t there an easier way? Actually there is – the SQL auto increment field. This field allows you to generate a unique number for each new row which is inserted in the table. Let’s review the syntax.

For SQL Server:
Read the rest of this entry »

VN:F [1.9.18_1163]
Rating: 10.0/10 (1 vote cast)

Using SQL Check constraint

If you want to limit the range of the values a certain column can store you can do that by using check constraints. Check constraints can be defined over columns or over tables. A check constraint defined over a column limits only the values of the given column. A check constraint defined over a table can limit values in given columns based on the values of other columns. Let’s review some examples.

We can define the check constraint when we create the table using the following syntax:

For SQL Server, Oracle and Access:
Read the rest of this entry »

VN:F [1.9.18_1163]
Rating: 10.0/10 (1 vote cast)

Using SQL Primary Key constraint

In SQL the Primary Key is used to uniquely identify each row in a table. Therefore the values of primary keys must be unique and a column which is a part of the primary key cannot contain NULL. Every table has exactly one primary key. It is also possible for the primary key to be composed of different columns.

Primary keys can be specified when the table is created. For example:
For SQL Server, Oracle and Access:
Read the rest of this entry »

VN:F [1.9.18_1163]
Rating: 0.0/10 (0 votes cast)

Creating database diagrams using IBM Data Studio Developer

This tutorial will show you how to create database diagrams using IBM Data Studio Developer.
When dealing with complex databases it is quite useful to be able to visualize data. If you use IBM Data Studio Developer then you are in luck, because it has a built-in feature that helps users visualize data really quick.
First open IBM Data Studio Developer. Expand the desired database: Read the rest of this entry »

VN:F [1.9.18_1163]
Rating: 1.8/10 (15 votes cast)

Using SQL Wildcards

SQL wildcards are used when searching for data in a database. They can substitute one or more characters when working with string types. They are a sort of pattern which is used to match a character string.
The SQL wildcards are:

% – substitutes zero or more characters
_ – substitutes one character
[list_of_characters] – substitutes one character from list_of_characters
[!list_of_characters] or [^list_of_characters] – substitutes one character from not in list_of_characters

All SQL wildcards are used with the LIKE operator.
Let’s review a few examples. We will use the table Person:
Read the rest of this entry »

VN:F [1.9.18_1163]
Rating: 0.0/10 (10 votes cast)