Database tutorials for beginners
Starting with the fundamental database concepts, this set of tutorials will guide you through
various database programming practices.
Tutorials
Chapter 1 : What is a database?
Chapter 2 : Database, tables, records and fields
Chapter 3 : Different database systems
Chapter 4 : What is SQL?
Chapter 5 : Definitions of SQL on the Web
Chapter 6 : CRUD Operation
Chapter 7 : Examples of SQL Statements
Chapter 8 : Sample SQL statements
What is a database?
This chapter gives an introduction to database concepts.
A database is a place to store your application data. It can be a simple text file, xml file, binary
file, MS Access database, SQL Server database, Oracle database etc. In other words, your
database need not be SQL Server or Oracle - it can be just plain text files.
However, there are several key differences between storing data in text files and database server
based databases. Text file based database storage may be good enough for small applications
which deal with very less amount of data. Most of the applications developers followed this
approach before database systems like FoxPro, MS Access, SQL Server etc became popular.
Data storage and retrieval become very efficient if you store very large volume of data in text files.
The popular database systems in the market are SQL Server and Oracle along with few others. In
the coming chapters, you will learn how the database systems evolved and how to use them.
Database, tables, records and fields
This chapter explains what is database, tables, records and fields.
What is a Database ?
A database is a collection of all data required for an application. Each database application will
have only one database.
What is a Table ?
- Tables are part of database.
- A database is composed of several tables.
- You need to create separate tables to store different type of data. For examples, if you have a
School Management Software, you may need to create the following tables:
Students - to store list of all students
Teachers - to store list of all students
Attendance - to track the attendance of all students
MarkList - to store the mark list of all students
What is a Record?
A record represents one entry in a Table. A table can have any number of records.
If you have a "Students" table to store the student information, a record in the table represent a
student. To add a student, you will add a record to the "Students" table. To delete a student from
the software, you will delete a record from this table.
What is a Field ?
A field is a column in the table. A record is a collection of fields. All records in the same table will
have the same set of fields.
If you have the "Students" table, you may have the following fields:
Name - to store the name of the student
Address - to store the address
DateofBirth - to store the date of birth of the student
RegistrationDate - to store the date on which the student registered.
etc.
If you add a field to the Table, it is applicable to all records in the same table. In the above
example, all records in the "Students" table will have the same 4 fields.
Summary
A database a collection of Tables
A Table is a collection of Records.
All records in the same table will have the same fields.
In most cases, an application will have one database which has several tables.
Different database systems
This chapter talks about various database systems and how they evolved.
First generation database systems
These are file based systems. Even though they are structured databases, they are using files in
the backend. Each table is stored as a separate file. There is no relation between files (tables).
DBase
Clipper
Foxbase
FoxPro
Among these, FoxPro was promoted by Microsoft and is still available in the market. The new
form of FoxPro is called 'Visual FoxPro'.
The above systems allow data storage as well as programming against the data. For example,
using FoxPro, you can save data as well as create fully functional FoxPro applications (This
feature is not available in modern database systems like SQL Server).
Second generation database system
Microsoft introduced MS Access as a database system to compete with other industry leading
database systems. MS Access is still file based, but all tables in a database is merged into one
file. It is easy to move database into another place and all files are safe within the database.
MS Access allow you to store data as well as write fully functional applications. You can create
rich applications with windows forms using MS Access.
Third generation database systems
SQL Server, Oracle, Sybase etc belong to this category. Even though data is still saved as file,
you do not deal with them as files. You will always use an application called 'database server' to
deal with the data. When you want to store or retrieve data, you will access the database server
to do this job. You have to use a different application (like C#, VB.NET, C++) etc and
communicate with the database server using some technology like ADO.NET.
In most cases, you will not even know where the database file is located. Instead, you will just
need to know how to connect to the database server, user id, password etc. Once you are
connected to the database server, you will send commands to the server to manipulate data.
Database servers understand a specific language called SQL. You will learn more about SQL in
the upcoming chapters.
What is SQL?
This chapter explains the definition of SQL.
Modern database systems will not allow you to directly access the database files. Instead, you
must access an application called 'database server' and send commands to the server. This
server will understand the commands and perform appropriate action.
In order for the database server to understand the commands, you must use a specific language.
This language is called 'Structured Query language' (SQL).
What is the need for a specific language for database access?
There are several database servers exist now (For example, SQL Server, Oracle, Sybase etc). If
each of these systems have their own protocols and commands to communicate with them, it will
be very hard for programmers to learn each one of them. This is the primary reason a common
language was introduced. All database systems are required to understand this common
language (SQL).
SQL is an ANSI (defined by American National Standards Institute) standard computer language
for accessing and manipulating database systems. All ANSI compliant SQL systems should
support the language rules defined by ANSI.
The bottom rule is, if you know SQL, you can work with any modern database systems.
SQL Extensions
All relational database systems are required to support the standard SQL. In addition to the
standard SQL, each of the database systems support additional commands (extensions to SQL).
If you are using any extensions provided by SQL Server, it may not work with Oracle and vice
versa.
Definitions of SQL on the Web
This chapter talks about some of the common definitions of SQL
You can find several definitions for the term 'SQL' in the web. Some of the popular definitions are
shown below. You may search in Google for the term "define:SQL" to find more definitions.
Here is some of the definitions of SQL:
- Structured Query Language (SQL), pronounced "sequel", is a language that provides an
interface to relational database systems. It was developed by IBM in the 1970s for use in System
R. SQL is a de facto standard, as well as an ISO and ANSI standard.
- SQL (Structured Query Language) is a standard interactive and programming language for
getting information from and updating a database.
- An acronym for Structured Query Language, this is a standard method of conveying information
to and from a database
- The standardized query language for requesting information from a database.
- Stands for "Structured Query Language", and can be pronounced as either "sequel" or "SQL". It
is a query language used for accessing and modifying information in a database. Some common
SQL commands include "insert", "update", and "delete". The language was first created by IBM in
1975 and was called SEQUEL for "Structured English Query Language." Since then, it has
undergone a number of changes, with the a lot of influence from Oracle Corporation.
CRUD Operation
This chapter talks about the CRUD operation in database.
All database operations can be broadly classified into 4 categories:
1. Insert data (Create)
2. Get existing data (Read)
3. Modify existing data (Update)
4. Delete data (Delete)
This set of tasks are called CRUD.
The term CRUD stands for Create, Read, Update, Delete which corresponds to the basic
database operations mentioned above.
If you are working on a database application, you may be asked to implement the CRUD tasks for
the commonly used tables (Entities). So be sure that you know what is meant by CRUD !
For example, if you are developing a student management software, you may have a table called
'Students'. Most probably, you will have to implement the following screens:
1. Screen to add a student
2. Screen which displays all the existing students
3. Screen to edit and modify existing student
4. Screen to select and delete a student.
Did you notice that the above operations match with the CRUD tasks?
In this case, the 'Student' represents an ENTITY. You will learn more about entities in upcoming
chapters.
Examples of SQL Statements
This chapter talks about some of the most commonly used SQL statements.
As you already know by now, SQL is the language used to communicate with database systems.
To execute commands on a database, you will compose SQL statements and send to a database
or database server. The database system will parse the SQL statement and execute.
What is SQL Statement ?
An SQL statement represents a set of commands that can be executed by a database system.
Here is a sample SQL statement:
select name from employee where age > 20
The above SQL statement will find the name of all employees from the EMPLOYEE table whoes
Age is above 20. The words marked in blue color are keywords.
Let us break the statement into 3 sections:
select name - SELECT is a keyword and NAME represents the database field to select.
from employee - FROM is a keyword and EMPLOYEE represents the database table to select
data from.
where age > 20 - WHERE is a keyword and AGE > 20 is the condition based on which the
records are selected from the table.
Basically, the select statement has the following syntax:
SELECT [FIELD 1, FIELD 2, ...] FROM [TABLE NAME] WHERE [CONDITION]
The WHERE condition is optional. If there is no WHERE condition, then the sql statement will
return all the records from the table.
In the next chapter, you will learn about the other types of SQL statements.
Sample SQL statements
In this chapter, you will see some sample SQL statements to perform the CRUD
operations.
Assume that you have a database table called 'EMPLOYEE' with the following fields:
1. Id
2. Name
3. Address
4. Salary
The below examples demonstrate the sql statements to perform the CRUD operations.
INSERT
INSERT INTO Employee (Id, Name, Address, Salary) VALUES (1, 'John',
'8900 Research Park Dr', 1200)
The above statement will insert a new record into the employee table. The name of the employee
used in the above example is 'John'.
UPDATE
UPDATE Employee SET Name = 'Mr. John', Address = '', Salary = 1400
WHERE id = 1
DELETE
DELETE FROM Employee WHERE id = 1
The above statement deletes the record of employee whoes Id is 1.
DELETE FROM Employee
There is no WHERE condition in the above statement and it will delete all records from employee
table.
SELECT
SELECT Id, Name, Address, Salary FROM Employee where ID = 1
The above sql statement finds the record of the employee with the ID = 1.
SELECT Id, Name, Address, Salary FROM Employee
In the above statement, there is no WHERE condition. So, it will return all records from the
employee table.