Posts Tagged ‘SQL’

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)

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)

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)

Creating, updating and deleting views in MS SQL

This tutorial is about how to create, update and delete views in MS SQL 2008.

In SQL views are basically virtual tables based on the result of some SQL statement. Each view contains rows and columns, exactly like a real table. Each field in a view is a field from a real table in the database.

Even though views can contain fields from different tables, users can use WHERE and JOIN statements along with SQL functions to present the data as a single table. The syntax for creating a view is: Read the rest of this entry »

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

Using the UNION, EXCEPT and INTERSECT operators in SQL

The UNION, EXCEPT and INTERSECT operators can be used to provide a single result set from multiple SQL queries. Using them we can easily manipulate results from different queries on different tables as long as they are provided in a uniform manner and follow a few basic rules.
The UNION operator is used for combining the results of multiple SELECT statements into one result set. Even though the syntax of the UNION operator is quite straightforward (you simply add UNION or UNION ALL between the SELECT statements), one must first understand a few basic principles to write a successful query. When using the UNION operator, all of the queries must return the same number of columns and the data types in the respective columns must be compatible. Let’s review a simple example using the Northwind database. Customers have a customer ID which is a string while employees have a numeric ID, in this case the employee ID must be converted to char: Read the rest of this entry »

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

Create database diagrams using MS SQL Server 2008

This how-to is about how to create database diagrams using MS SQL Server
Sometimes the best way to visualize data is by using a diagram. This goes double for complex databases. Here we will show you how to create diagrams of a database or a part of database.
First open MS SQL Server Management Studio. In our example we will show you how to create a diagram of a part of the AdventureWorks database.
Expand the Databases folder in the Object Explorer:
Read the rest of this entry »

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