Real Life SQL And .NET

Welcome to the world of SQL and the huge, growing database technologies of today's business all over the SQL world. By reading this article, we have begun accepting the knowledge that will soon be required for survival in today's world of relational database and data management. Alas, for the reason that it is first necessary to provide a background of SQL and cover some preliminary concepts that we need to know, the majority of this article is text in paragraph format.

The highlights of this article include

An introduction to and brief history of SQL
An overview of some basic terms and concepts
An introduction to database management systems

After we have enough information ANSI SQL, we will focus on T-SQL, Jet SQL, and Oracle SQL.

SQL Classification and History

Every business has data, which requires some organized method, or mechanism, for maintaining the data. This mechanism is referred to as a database management system (DBMS). Database management systems have been around for years, many of which started out as flat-file systems on a mainframe. With today's technologies, the accepted use of database management systems has begun to flow in other directions, driven by the demands of growing business, increased volumes of corporate data, and of course Internet technologies.

Today two important technologies are very favorites. First one is Relational Database and second one is Object Database technology. Object Database's capability to allocate and store objects is the most obvious difference between a Relational Database and Object Database. Objects are fussy instances or occurrences of a class. We can straight accumulate an object without first converting the object's member variables into columns in a relational table when using an object database A more accurate model of the real world is provided by the object database approach. We can keep away from the artificial concept of mapping class member variables to database columns by directly storing objects in the database. An object identifier identifies every object stored in an object database. Object identifier is equivalent to a primary identifier in the relational world.

There are some advantages against each other but whatever if we use one of them this technologies, we will need to use SQL.

What is SQL really?

Structured Query Language is the standard language used to communicate with a relational database or object database. The prototype was originally developed by IBM using Dr. E.F. Codd's theory as a model. In 1979, not long after IBM's prototype, the first SQL product, ORACLE, was released by Relational Software, Inc. (now Oracle Corporation). SQL is pronounced either of two ways as the letters S-Q-L (esquel), or "sequel"; both pronounced are acceptable. SQL is the language that we use to express our needs to the database. We may request specific information from within a database in the form of a query, using SQL.

The American National Standards Institute (ANSI) is an organization that approves certain standards in many different industries. SQL has been deemed the standard language is relational database communication, originally approved in 1986 based on IBM's implementation. The current standard is referred to as SQL/92, much revised since 1986.

What is a Data?

Data is a collection of information stored in a database as one of several different data types. Data includes names, numbers, dollar amounts, text, graphics, decimals, figures, calculations, summarization, and anything else we can possibly imagine. Data can be stored in uppercase, lowercase, or mixed case. Also data can stored as an object with object database technology. Data can be manipulated or changed; most data does not remain static for its lifetime.

An Explanation to Database Objects

Database objects are the underlying backbone of the relational database and they are fundamentals of object database technology. These objects are logical units within the database that are used to store information, and are referred to as the back end database. The majority of the instruction during this article revolves around the table, but keep in mind that there are other database objects, many of which are discussed in later articles. A database object is any defined object in a database that is used to store or reference data. Some examples of database objects include tables, views, clusters, sequences, indexes, and synonyms. Also we can keep a record as an object in database.

An Introduction to SQL Statements

Before retrieve data from a table we need create a table if it is not exist already. The CREATE TABLE statement is obviously used to create a table. Although the very act of creating a table is quite simple, much time and effort should be put into planning table structures before the actual execution of the create table statement. Here is the syntax of create table,

CREATE TABLE table_name
( field1 datatype [ not null ],
field1 datatype [ not null ],
field1 datatype [ not null ],
field1 datatype [ not null ],
field1 datatype [ not null ]
)

In this article we will use the popular data types CHAR, VARCHAR, NUMBER, and DATE. Here is we create a table called EMPLOYEE_TABLE in the following code piece,

CREATE TABLE EMPLOYEE_TABLE
( EMPLOYEE_ID CHAR(10) NOT NULL,
EMPLOYEE_NAME VARCHAR(35) NOT NULL,
EMPLOYEE_ADDR VARCHAR(30) NOT NULL,
EMPLOYEE_CITY VARCHAR(15) NOT NULL,
EMPLOYEE_STATE VARCHAR(2) NOT NULL,
EMPLOYEE_ZIP VARCHAR(5) NOT NULL,
EMPLOYEE_PHONE VARCHAR(11) NULL,
EMPLOYEE_NOTES VARCHAR(40) NOT NULL
);

Here are eight different columns make up this table. Please keep in mind, the use of the underscore character to break the column names up into what appears to be separate words. Each column has been assigned a specific data type and length, and by using the NULL/NOT NULL constraint, we have specified which columns require values for every row of data in the table. Another notice we need to keep in mind, a semicolon is the last character in the previous statement. Most of SQL implementations have some character that terminates a statement or submits a statement to the database server. Oracle uses the semicolon; T-SQL uses the GO statement.

Creating a Table from an Existing Table

A copy of existing table can be created using a combination of the CREATE TABLE statement and the SELECT statement. The new table has the same column definitions. All columns or specific columns can be selected. New columns that are created via functions or a combination of columns automatically assume the size necessary to hold the data. The basic syntax for creating a table from another table is as shown,

Create table copy_table_name as Select [ * | column1, column2 ] From table_name [ where ]

Please keep in mind some new keyword in the syntax, particularly the SELECT keyword. SELECT is a database query, and is discussed in more detail later in this article. However, it is important to know that you can create a table based on the results from a query.

First, let's do a simple query to view the data in the BOOKS_TBL table.

Select * from products_tbl;

ISBN_ID PROD_DESC COST
2343456 Database Programming with C# 50.00
6585235 COM+ Programming and .NET 60.00
4568787 Multitherading with C# 55.00

In this statement "Select*" selects data from all fields in the given data. Now, let's create a table BOOKS_TMP based on the previous query.

Create table books_tmp as Select * from books_tbl;

from the original table.

Select * From books_tmp;

ISBN_ID PROD_DESC COST
2343456 Database Programming with C# 50.00
6585235 COM+ Programming and .NET 60.00
4568787 Multitherading with C# 55.00

When creating a table from an existing table, the new table takes on the same storage attributes as the original table.

Primary Key, Unique and Foreign Key Constraints

Primary key

Primary key is the term used to identify one or more columns in a table that make a row of data unique. Although the primary key typically consists of one column in a table, more than one column can comprise the primary key. For example, either the book's ISBN number or an assigned book code number is the logical primary key for a books table. The objective is for every record to have a unique primary key, or value, for the book's record number. Because there is probably no need to have more than one record for each book in a books table, the book ISBN number makes a logical primary key. The primary key is assigned upon table creation. The following example identifies the ISBN column as the PRIMARY KEY for the BOOKS table.

CREATE TABLE BOOKS_TBL
(BOOK_ID CHAR(9) NOT NULL PRIMARY KEY,
BOOK_ISBN CHAR(12) NOT NULL,
BOOK_NAME CHAR(40) NOT NULL,
BOOK_AUTHOR CHAR(30) NOT NULL,
BOOK_PUBLISHER CHAR(30) NOT NULL,
BOOK_CLASS1 NUMBER(1) NULL,
BOOK_CLASS2 NUMBER(1) NULL,
BOOK_PRICE NUMBER(6) NULL
);

Unique key

A unique column constraint in a table is similar to a primary key in that the value in that column for every row of data in the table must have a unique value. While a primary key constraint is placed on one column, we can place a unique constraint on another column even though it is not actually for use as the primary key. Take a look following example,

CREATE TABLE BOOKS_TBL
(BOOK_ID CHAR(9) NOT NULL PRIMARY KEY,
BOOK_ISBN CHAR(12) NOT NULL UNIQUE,
BOOK_NAME CHAR(40) NOT NULL,
BOOK_AUTHOR CHAR(30) NOT NULL,
BOOK_PUBLISHER CHAR(30) NOT NULL,
BOOK_CLASS1 NUMBER(1) NULL,
BOOK_CLASS2 NUMBER(1) NULL,
BOOK_PRICE NUMBER(6) NULL
);

The primary key in this example is BOOK_ID, meaning that the book identification number is the column that is used to ensure that every record in the table is unique. The primary key is a column that is normally referenced in queries, particularly to join tables. The column BOOK_ISBN has been designed as a UNIQUE value, meaning that no two books may have the same ISBN number. There is not a lot of difference between UNIQUE and Primary Key, except that the primary key is used to provide an order to data in a table, and in the same respect, join related tables.

Foreign Key

A foreign key constraint is the main mechanism used to enforce referential integrity between tables in a relational database. A column defined as a foreign key is used to reference a column defined as a primary key in another table. This mean, a foreign key is a column in a child table that references a column in the parent table. Here is an example for foreign key,

CREATE TABLE BOOKINFO_TBL
( BOOK_ID CHAR(9) NOT NULL,
BOOK_RANK NUMBER(9) NULL,
BOOK_PAGES NUMBER(5) NULL,
FOREIGN KEY BOOK_ID_AU (BOOK_ID) REFERENCES BOOK_TBL (BOOK_ID)
);

The BOOK_ID column in this example has been designated as the foreign key for the BOOKINFO _TBL table. This foreign key, as you can see, references the BOOK_ID column in the BOOK_TBL table. This foreign key ensures that for every BOOK_ID in the BOOKINFO _TBL, there is a corresponding BOOK_ID in the BOOK_ID. This is called a parent/child relationship. The parent table is the BOOK_TBL, and BOOKINFO_TBL is child table.

continue article

1 Comment

Join the conversation! Your thoughts help the community grow.

peter20y ago
Hi, I liked your article... do you happen to know how to work with serverside cursors? I need to have the database return 10 or 20 rows at a time from the result set (a select/join of two tables). Then the user should pres "next 10", and the database should return the next rows from its result. I cannot let the database return the entire resultset to a DataSet, as the result consists of thousands of rows, taking several minutes to build. My database is an IBM AS400