SQL Basics- Part 1

Before starting SQL, let’s see the basic terms

Data: Computer Information

Persistence: Preserving data for future use.

 Large amount of Inter related Data – database

 DBMS: Database management System ANSI defn. of DBMS - Readymade s/w allows you to insert update delete and process

            E.g. Ms Excel, dBASE, foxbase, Foxpro, Clipper, Database Dataflex  ,Dataease  etc.

RDBMS: Relational Database Management System (Relational means it maintains relation between tables)

RDBMS examples:

11.     Informix (fastest),

22. Oracle (most popular around 50% market share of world RDBMS market Best s/w tools, mix programming is easy) 

33.  Sybase

44.    MS SQL server (upcoming Limitation Windows Domain)

55.  Ingres, Progress (character based only on DOS and UNIX)

66.     CICS, TELON, IDMS, IBM DB2 (Mainframe like ATM machine)

77.     MS Access Vatcom SQL Paradox (PC based not for n/w)

88.     MySQL 

Difference between DBMS and RDBMS

DBMS	RDBMS
Nomenclature Field                             Record                         File	Column / Attribute Row / Tuple / Entity table/relation/entity class
Relationship between two files maintained programmatically	Relationship between two tables maintained at the time of table creation
High network traffic	Low network traffic
file level locking	row level locking [Oracle 10g automatic row level locking]New version of Oracle cell level locking
Distributed databases not supported	Most RDBMS support Distributed database [Largest Distributed databases in Oracle Yahoo! 20000 Oracle Linux Server in Silicon Valley and in Australia]
No Security	Multiple level of Security [Oracle Server Database crated in another Partition of HDD] logging in [username/password] / command [DBA restriction on using Commands (134command in Oracle 10g)] /object [Grant Permissions on Owner Object]Oracle Tools/Oracle Products

SQL (Structured Query Language)

·         Structured Query Language

·         Standard Query Language

·         Common to all database

·         Conforms to ANSI standards. And ISO standard

·         Initially created by IBM 1978-79. Now controlled by ANSI

SQL Commands (12 commands + TRUNCATE added as SQL 2008 Standards)

1.     DDL (data definition language): Create, drop, alter, truncate

2.     DML (data manipulation language): Insert, update, delete, merge (added in SQL 2003 std/expanded in SQL 2008 std)

3.     DCL (data control language): Grant, Revoke

4.     DQL (data query language): select

5.     DTL (data transactional language): Rollback, commit, savepoint

Rules of table names/ column names:

·         Must be 1-30 characters long in Oracle 9i. (127 characters for DB2) Object names or Qualifier are case insensitive (EMP or emp same)

·         Should not be an Database Server reserved word

·         Must contain only A-Z, a-z, 0-9, _, $, and #. [avoid $ and # may not work in other RDBMS]

·         Must begin with a letter/Alphabet (use descriptive name)

·         Must not a duplicate name of another object owned by same user.

Data Types

            A data type is a type of data.

Range of values that a column can have. (Data type is a data storage format that can contain a specific type or range of values.)

String Data Types:

1.     CHARACTER (CHAR): Fixed-length character strings with a length 1-255.

2.     VARCHAR:  The maximum length is 32704.

Numeric Data Types:

1.     INTEGER (INT): The range is -32768 to +32767.

2.     DECIMAL (NUMERIC): The range is -2147483648 to +2147483647. (If decimal(7,2) and input as 50.605 then rounding takes place as 50.61 while storing)

3.     DOUBLE: The maximum range is 1 - 10³¹ to 10³¹ - 1

4.     SMALLINT, BIGINT, REAL etc.: The range of big integers is -9223372036854775808 to +9223372036854775807.

Date-time Data Types:

1.     DATE: A date is a three-part value representing a year, month, and day in the range of 0001-01-01 to 9999-12-31.

2.     TIME: A time is a three-part value representing a time of day in hours, minutes, and seconds, in the range of 00.00.00 to 24.00.00.

3.     TIMESTAMP: timestamp is a seven-part value representing a date and time by year, month, day, hour, minute, second, and microsecond, in the range of 0001-01-01-00.00.00.000000000 to 9999-12-31-24.00.00.000000000 with nanosecond precision.

XML data type: it is used to define columns of a table that store XML values.