1.Learn SQL

****************************************************************
Summary of How this page can help you

The below article will give you a brief overview of what a database
means, SQL means and a little bit of SQL concepts. At the end of
the article we have put some links and also recommended a SQL
book which you can prepare. Most importantly , if you are not
confident in SQL we strongly urge you to take up a Instructor led
course in SQL.


To go back to the dba career shift home page click dbacareershift  

*********************************************************************
*******
What is a Database

For new comers who are just starting their careers or looking out for Jobs, the meaning of what a
database means can be very confusing when read in the internet articles. Some of you may have
just ignored this subject while in college.
You will come across lot of computer related technical words which can sound confusing to you. If
you have patience to find out what a particular word means, you will soon begin to understand the
meaning well enough, as you read articles on the internet.

First of all, as you know a computer has lot of software programs. To write a software program
you need to know the coding language. Once the software program is ready, you need to store all
the information or data that the software program uses, in a specific place. The specific place
where the data is stored can be called a database. I know this is just a broad explanation but the
idea is to give any newcomers reading this material to have an idea of what a database could mean.

Below is a extract from Wikipedia on what a Database means. Don’t get scared by the difficult
words. In time you would understand it better. If you don’t understand a specific word, search for
the word’s meaning in Google and then read the paragraph.

The term database originated within the computing discipline. Although its meaning as been
broadened by popular use, even to include on-electronic databases, this rticle is about computer
databases. The properties and design of database systems re included in the study of information
science.
A computer database is a structured collection of records or data that is stored in a omputer
system so that a computer program or person using a query language can onsult it to answer queries
[1]. The records retrieved in answer to queries are nformation that can be used to make decisions.
The term database refers to the collection of related records, and the software should e referred to
as the database management system or DBMS. When the context is mbiguous, however, many
database administrators and programmers use the term atabase to cover both meanings.

Many Companies have created software to manage databases. Most Popular among them are
Oracle, Sqlserver , DB2 , Sybase etc. These softwares are termed as RDBMS . It means ‘
Relational Database Management systems ‘ .

If you want to see the data that is stored in a Database, you need to use a query. A query is a line
of code. To write the line of code you need know the sql language. SQL stands for ‘ Structured
Query Language ‘ .

For those of you who don’t know what sql is , you should definetly attend a course in SQL to
understand the language and gain experience in using it. This is the first requirement if you want to
proceed further along the DBA Career path.

This page has been provided to give you a general overview of what a database eans, SQL means
and other sql information. Before you proceed to other pages in his website we strongly
recommend that you become proficient in SQL by reading elow mentioned books/links or your
own preferred instructor led courses.

Below is an Overview on what SQL means.

Structured Query Language
Contents
• Database Models
• Relational Databases
• Relational Data Structure
• Domain and Integrity Constraints
• Structure of a Table
o CAR
o DRIVER
o Relationship between CAR and DRIVER
o Example Data
o Columns or Attributes
• Primary Keys
• SQL Basics
• Simple SELECT
• Comments
• SELECT filters
• Comparisons
• Dates
o BETWEEN
• NULL
• LIKE

What is SQL?
To communicate with the database system itself we need a language. SQL is an international
standard language for manipulating relational databases. It is based on an IBM product. SQL is
short for Structured Query Language.
SQL can create schemas, delete them, and change them. It can also put data into schemas and
remove data. It is a data handling language, but it is not a programming language.
SQL is a DSL (Data Sub Language), which is really a combination of two languages.
These are the Data Definition Language (DDL) and the Data Manipulation Language (DML).
Schema changes are part of the DDL, while data changes are part of the DML.
We will consider both parts of the DSL in this discussion of SQL.

What are Database Models?
By studying below definition of a data model, you can imagine how a database is divided into , just
like how a Car is divided into an engine , body , wheels etc.
A data model comprises
• a data structure
• a set of integrity constraints
• operations associated with the data structure
Examples of data models include:
• hierarchic
• network
• relational
Models other than the relational database module used to be quite popular. Each model type is
appropriate to particular types of problem. The Relational model type is the most popular in use
today, and the other types are not discussed further.

What are Relational Databases ?
The relational data model comprises:
• relational data structure
• relational integrity constraints
• relational algebra or equivalent (SQL)
• SQL is an ISO language based on relational algebra
• relational algebra is a mathematical formulation

What is a Relational Data Structure ?
A relational data structure is a collection of tables or relations.
• A relation is a collection of rows or tuples
• A tuple is a collection of columns or attributes
• A domain is a pool of values from which the actual attribute values are taken.


What are Domain and Integrity Constraints ?
• Domain Constraints
o limit the range of domain values of an attribute
o specify uniqueness and `nullness' of an attribute
o specify a default value for an attribute when no value is provided.
• Entity Integrity
o every tuple is uniquely identified by a unique non-null attribute, the primary key.
• Referential Integrity
o rows in different tables are correctly related by valid key values (`foreign' keys
refer to primary keys).

What is the Structure of a Table?
In the design process tables are defined, and the relationships between tables identified. Remember
a relationship is just a link between two concepts. Consider a table holding "drivers" and a table
holding "car" information... Each car is owned by a driver, and therefore there is a link between
"car" and "driver" to indicate which driver owns which car.

In the subsequent pages we will refer back to this driver and car arrangement. To make the
examples easier, lets create some example data.
CAR
The CAR table has the following structure:
• REGNO : The registration number of the car
• MAKE : The manufacturer of the car
• COLOUR: The colour of the car
• PRICE : The price of the car when it was bought new
DRIVER
The DRIVER table has the following structure:
• NAME : The full name of the driver
• DOB : The data of birth of the driver

Relationship between CAR and DRIVER
The DRIVER and the CAR has a relationship between them of N:1. This indicates that a CAR can
have only 1 DRIVER, but that a DRIVER can own more than 1 CAR simultaneously.


In the design section we can see that this requires a FOREIGN KEY in the CAR end of the
relationship. This foreign key allows us to implement the relationship in the database. We will call
this field OWNER.
Example Data

DRIVER
NAME DOB
Jim Smith 11 Jan 1980
Bob Smith 23 Mar 1981
Bob Jones 3 Dec 1986

CAR
REGNO MAKE COLOUR PRICE OWNER
F611 AAA FORD RED 12000 Jim Smith
J111 BBB SKODA BLUE 11000 Jim Smith
A155 BDE MERCEDES BLUE 22000 Bob Smith
K555 GHT FIAT GREEN 6000 Bob Jones
SC04 BFE SMART BLUE 13000

What are Columns or Attributes?
Each column is given a name which is unique within a table
Each column holds data of one specified type. E.g.

integer decimal
character text data

-- the range of values can be further constrained
If a column of a row contains no data, we say it is NULL. For example, a car just off the
production line might not have an owner in the database until someone buys the car. A NULL
value may also indicate that the value is unavailable or inappropriate . This might be the case for a
car which is being destroyed or a car where two people are arguing in court that they are both the
owner.

Some important rules:
• All rows of a table must be different in some way from all other rows.
• Sometimes a row is referred to as a Tuple.
• Cardinality is the number of ROWS in a table.
• Arity is the number of COLUMNS in a table.

What are Primary Keys ?
A table requires a key which uniquely identifies each row in the table. This is entity integrity.
The key could have one column, or it could use all the columns. It should not use more columns
than necessary. A key with more than one column is called a composite key.
A table may have several possible keys, the candidate keys, from which one is chosen as the
primary key.
No part of a primary key may be NULL.
If the rows of the data are not unique, it is necessary to generate an artificial primary key.

In our example, DRIVER has a primary key of NAME, and CAR has a primary key of REGNO.
This database will break if there are two drivers with the same name, but it gives you an idea what
the primary key means...

Note that if for some reason JIM SMITH decided to change his name to "BRIAN SMITH", then
not only would this have to be changed in DRIVER, but it would also have to be changed in CAR.
If you changed it only in DRIVER, there would be some foreign keys pointing to DRIVER looking
for a driver who does not exist. This would be an error called a REFERENTIAL INTEGRITY
error, and the DBMS stops you making changes to the database which would result in such an
error.

SQL Basics
Basic SQL Statements include:
• CREATE - a data structure
• SELECT - read one or more rows from a table
• INSERT - one or more rows into a table
• DELETE - one or more rows from a table
• UPDATE - change the column values in a row
• DROP - a data structure
In the remainder of this section only simple SELECT statements are considered.

How can you write a Simple SELECT Statement?
The syntax of a SELECT statement is :

SELECT column FROM tablename

This would produce all the rows from the specified table, but only for the particular column
mentioned. If you want more than one column shown, you can put in multiple columns separating
them with commas, like:

SELECT column1,column2,column3 FROM tablename

If you want to see all the columns of a particular table, you can type:

SELECT * FROM tablename

Lets see it in action on CAR...

SELECT * FROM car;
REGNO MAKE COLOUR PRICE OWNER
F611 AAA FORD RED 12000 Jim Smith
J111 BBB SKODA BLUE 11000 Jim Smith
A155 BDE MERCEDES BLUE 22000 Bob Smith
K555 GHT FIAT GREEN 6000 Bob Jones
SC04 BFE SMART BLUE 13000
SELECT regno FROM car;
REGNO
F611 AAA
J111 BBB
A155 BDE
K555 GHT
SC04 BFE

SELECT colour,owner FROM car;

COLOUR OWNER
RED Jim Smith
BLUE Jim Smith
BLUE Bob Smith
GREEN Bob Jones
BLUE

In SQL, you can put extra space characters and return characters just about anywhere without
changing the meaning of the SQL. SQL is also case-insensitive (except for things in quotes). In
addition, SQL in theory should always end with a ';' character. You need to include the ';' if you
have two different SQL queries so that the system can tell when one SQL statement stops and
another one starts. If you forget the ';' the online
interface will put one in for you. For these reasons all of the following statements are identical and
valid.

SELECT REGNO FROM CAR;

SELECT REGNO FROM CAR

Select REGNO from CAR

select regno FROM car


SELECT
regno
FROM car;

What are Comments?
Sometimes you might want to write a comment in somewhere as part of an SQL statement. A
comment in this case is a simple piece of text which is meaningful to yourself, but should be ignored
by the database. The characters '--', when they appear in a query, indicate the start of a comment.
Everything after that point is ignored until the end of that line. The following queries are all
equivalent.

SELECT regno
FROM car;

SELECT regno -- The registration number
FROM car -- The car storage table
;
Warning: You cannot put a comment immediately after a ';'. Comments are only supported within
the text of an SQL statement. The following will cause SQL errors:

SELECT regno
FROM car; -- Error here as comment is after the query

-- Error here as comment is before the start of the query
SELECT regno
FROM car;

What are the different SELECT filters?
Displaying all the rows of a table can be handy, but if we have tables with millions of rows then this
type of query could take hours. Instead, we can add "filters" onto a SELECT statement to only
show specific rows of a table. These filters are written into an optional part of the SELECT
statement, known as a WHERE clause.

SELECT columns
FROM table
WHERE rule

The "rule" section of the WHERE clause is checked for every row that a select statement would
normally show. If the whole rule is TRUE, then that row is shown, whereas if the rule is FALSE,
then that row is not shown.

The rule itself can be quite complex. The simplest rule is a single equality test, such as "COLOUR
= 'RED'".

Without the WHERE rule would show:

SELECT regno from CAR;

REGNO
F611 AAA
J111 BBB
A155 BDE
K555 GHT
SC04 BFE

From the database we know that only F611 AAA is RED, and the rest of the cars are either
BLUE or GREEN. Thus a rule COLOUR = 'RED' is only true on the row with F611 AAA, and
false elsewhere. With everything in a query:


SELECT regno from CAR
WHERE colour = 'RED';

REGNO
F611 AAA

An important point to note is that queries are case sensitive between the quotes. Thus 'RED' will
work, but 'red' will produce nothing. The case used in the quotes must match perfectly the case
stored in the table. SQL is not forgiving and if you forget you can be scratching you head for hours
trying to fix it.
Note also that "colour" does not have to appear on the SELECT line as a column name. It can if
you want to see the colour, but there is no requirement for it to be there.
Therefore this will work too:

SELECT regno,colour from CAR
WHERE colour = 'RED';
REGNO COLOUR
F611 AAA RED

What are the different Comparison Operators?

SQL supports a variety of comparison rules for use in a WHERE clause. These include
=,!=,<>, <, <=, >, and >=.

Examples of a single rule using these comparisons are:
WHERE colour = 'RED' The colour attribute must be RED
WHERE colour != 'RED' The colour must be a colour OTHER THAN RED
WHERE colour <> 'RED' The same as !=
WHERE PRICE > 10000 The price of the car is MORE THAN 10000
WHERE PRICE >= 10000 The price of the car is EQUAL TO OR MORE THAN
10000
WHERE PRICE < 10000 The price of the car is LESS THAN 10000
WHERE PRICE <= 10000 The price of the car is EQUAL TO OR LESS THAN 10000

Note that when dealing with strings, like RED, you must say 'RED'. When dealing with
numbers, like 10000, you can say '10000' or 10000. The choice is yours.

Dates
Date rules are some of the hardest rules to get right in writing SQL, yet there is nothing particularly
complex about them. The hard part is working out what it means to be GREATER THAN a
particular date.
In date calculations, you can use all the normal comparators.

SELECT name,dob from driver
NAME DOB
Jim Smith 11 Jan 1980
Bob Smith 23 Mar 1981
Bob Jones 3 Dec 1986

SELECT name,dob from driver
WHERE DOB = '3 Dec 1986'
NAME DOB
Bob Jones 3 Dec 1986

In other comparators, it is important to realise that a date gets bigger as you move into the future,
and smaller as you move into the past. Thus to say 'DATE1 < DATE2' you are stating that DATE1
occurs before DATE2 on a calender. For example, to find all drivers who were born on or after
the 1st Jan 1981 you would do:

SELECT name,dob from driver
WHERE DOB >= '1 Jan 1981'
NAME DOB
Bob Smith 23 Mar 1981
Bob Jones 3 Dec 1986

The syntax for dates does change slightly on difference database systems, but the syntax '1 Jan
2000' works in general on all systems. Oracle also allows dates like '1-Jan-2000' and '1-Jan-00'.
If you specify a year using only the last two digits, Oracle uses the current date to compute the
missing parts of the year, converting '00' to '2000'.
Do not get confused by saying '87' for '1987' and ending up with '2087'!

BETWEEN

Sometimes when you are dealing with dates you want to specify a range of dates to check. The
best way of doing this is using BETWEEN. For instance, to find all the drivers born between 1995
and 1999 you could use:
SELECT name,dob from driver WHERE DOB between '1 Jan 1985' and '31 Dec 1999'
NAME DOB
Bob Jones 3 Dec 1986

Note that the dates have day of the month and month in them, and not just the year. In SQL, all
dates must have a month and a year. If you try to use just a year the query will fail.

BETWEEN works for other things, not just dates. For instance, to find cars worth between 5000
and 10000, you could execute:

SELECT regno
FROM car
where price between 5000 and 10000;
REGNO PRICE
K555 GHT

NULL

The NULL value indicates that something has no real value. For this reason the normal value
comparisons will always fail if you are dealing with a NULL. If you are looking for NULL, for
instance looking for cars without owners using OWNER of CAR, all of the following are wrong!
SELECT regno from CAR WHERE OWNER = NULL WRONG!
SELECT regno from CAR WHERE OWNER = 'NULL' WRONG!

Instead SQL has a special comparison operator called IS which allows us to find NULL values.
There is also an opposite to IS, called IS NOT, which finds all the values which are not NULL. So
finding all the regnos of cars with current owners would be (note that if they have an owner, then
the owner has a value and thus is NOT NULL):
SELECT REGNO from CAR
WHERE OWNER is not NULL
REGNO
F611 AAA
J111 BBB
A155 BDE
K555 GHT
And finding cars without owners would be:
SELECT REGNO from CAR
WHERE OWNER is NULL
REGNO
SC04 BFE

LIKE

When dealing with strings, sometimes you do not want to match on exact strings like ='RED', but
instead on partial strings, substrings, or particular patterns. This could allow you, for instance, to
find all cars with a colour starting with 'B'. The LIKE operator provides this functionality.

The LIKE operator is used in place of an '=' sign. In its basic form it is identical to '='.
For instance, both of the following statements are identical:
SELECT regno FROM car WHERE colour = 'RED';
SELECT regno FROM car WHERE colour LIKE 'RED';

The power of LIKE is that it supports two special characters, '%' and '-'. These are equivalent to
the '*' and '?' wildcard characters of DOS. Whenever there is an '-' character in the string, any
character will match. Whenever there is an '%' character in the string, 0 or more characters will
match. Consider these rules:
name LIKE 'Jim Smith' Matches 'Jim Smith'
name LIKE '_im Smith' Matches things like 'Jim Smith' or 'Tim Smith'
name LIKE '___ Smith' Matches 'Jim Smith' and 'Bob Smith'
name LIKE '% Smith' Matches 'Jim Smith' and 'Bob Smith'
name LIKE '% S%' Matches 'Jim Smith' and 'Bob Smith'
name LIKE 'Bob %' Matches 'Bob Jones' and 'Bob Smith'
name LIKE '%' Matches anything not null
Note however that LIKE is more powerful than a simple '=' operator, and thus takes longer to run.
If you are not using any wildcard characters in a LIKE operator then you
should always replace LIKE with '='.


More Information can be found through below links
http://db.grussell.org/index.html
http://sqlcourse.com/

You can also read the Free SQL Book from oracle website.The link to the book has been given
on the
Important Oracle Links page on this website.

Also we would like to recommend the book
Introduction to Oracle9i SQL Study Guide
by Chip Dawes and Biju Thomas

This is the Amazon Link for this book
http://www.amazon.com/OCA-OCP-Introduction-Oracle9i-Study/dp/0782140629


You can test your SQL skills at the below Link
http://www.w3schools.com/sql/sql_quiz.asp

To go to the next prerequisite for the new dba please click  Learn PL/SQL