SQL
Basic Structure
Structured Query Language (SQL), is the standard command set used to communicate with the relational database management system. All tasks related to relational data management creating tables, querying the database for information, modifying the data in the database,deleting them, granting access to users and so on can be done with SQL.
Characteristics of SQL
(i) SQL enables end user and system persons to deal with a number of database managment systems where it is available.
(ii) Applications written in SQL can be easily ported across systems. Such porting could be required when the underlying DBMS needs to upgraded because of change in transaction volumes or when a system developed in one environment is to be used on another.
(iii) SQL as a language is independent of the way it is implented internally. A query returns the same result regardless of whether optimizing has been done with indexes or not. This is because SQL specifies what is required and not how it is to be done.
(iv) The language while being simple and easy to learn can cope with complex situations.
(v) The results to be expected are well defined in SQL.
(ii) Applications written in SQL can be easily ported across systems. Such porting could be required when the underlying DBMS needs to upgraded because of change in transaction volumes or when a system developed in one environment is to be used on another.
(iii) SQL as a language is independent of the way it is implented internally. A query returns the same result regardless of whether optimizing has been done with indexes or not. This is because SQL specifies what is required and not how it is to be done.
(iv) The language while being simple and easy to learn can cope with complex situations.
(v) The results to be expected are well defined in SQL.
Advantages and Disadvantages of SQL
SQL is a standard interactive and programming language for querying and modifying data and managing databases. Although SQL is both an ANSI and an ISO standard, many database products support SQL with proprietary extensions to the standard language. The core of SQL is formed by a command language that allows you to retrieve, insert, update, and delete data, and perform management and administrative functions. SQL also includes a call-level interface (SQL/CLI) for accessing and managing data and databases remotely.
Common criticisms of SQL include a perceived lack of cross-platform portability between vendors, inappropriate handling of missing data (see Null (SQL)), and unnecessarily complex and occasionally ambiguous language grammar and semantics.
Theorists and some practitioners note that many of the original SQL features were inspired by, but in violation of, the relational model for database management and its tuple calculus realization. Recent extensions to SQL achieved relational completeness, but have worsened the violations, as documented in The Third Manifesto.
Refer for More Information on http://en.wikipedia.org/wiki/SQL
Common criticisms of SQL include a perceived lack of cross-platform portability between vendors, inappropriate handling of missing data (see Null (SQL)), and unnecessarily complex and occasionally ambiguous language grammar and semantics.
Theorists and some practitioners note that many of the original SQL features were inspired by, but in violation of, the relational model for database management and its tuple calculus realization. Recent extensions to SQL achieved relational completeness, but have worsened the violations, as documented in The Third Manifesto.
Refer for More Information on http://en.wikipedia.org/wiki/SQL
Data Types in SQL
Data types are a classification of a particular type of information . It is easy for human to distinguish between different types of data. Sqp support following data types.
Data types in SQL Server are organized into the following categories:
1) Exact Numeric
(a) bigint (b)numeric (c)smallint (d)bit (e) smallint (f)decimal (g) smallmoney (h)int (i)tinyint (j)money
2) Unicode Character String
(a)nchar (b)nvarchar (c)ntext
3) Approximate numerics
(a) float (b)real
4) Binary String
(a)binary (b)varbinary (c)image
5) Date and Time
(a)date (b)datetimeoffset (c)datetime2 (d)smalldatetime (e)datetime (f)time
6) Character String
(a) char (b)nvarchar (c)ntext
Data types in SQL Server are organized into the following categories:
1) Exact Numeric
(a) bigint (b)numeric (c)smallint (d)bit (e) smallint (f)decimal (g) smallmoney (h)int (i)tinyint (j)money
2) Unicode Character String
(a)nchar (b)nvarchar (c)ntext
3) Approximate numerics
(a) float (b)real
4) Binary String
(a)binary (b)varbinary (c)image
5) Date and Time
(a)date (b)datetimeoffset (c)datetime2 (d)smalldatetime (e)datetime (f)time
6) Character String
(a) char (b)nvarchar (c)ntext
Types Of SQL commands
DDL
Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:
Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:
Data Control Language (DCL) statements. Some examples:
Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.
Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:
- CREATE - to create objects in the database
- ALTER - alters the structure of the database
- DROP - delete objects from the database
- TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
- COMMENT - add comments to the data dictionary
- RENAME - rename an object
Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:
- SELECT - retrieve data from the a database
- INSERT - insert data into a table
- UPDATE - updates existing data within a table
- DELETE - deletes all records from a table, the space for the records remain
- MERGE - UPSERT operation (insert or update)
- CALL - call a PL/SQL or Java subprogram
- EXPLAIN PLAN - explain access path to data
- LOCK TABLE - control concurrency
Data Control Language (DCL) statements. Some examples:
- GRANT - gives user's access privileges to database
- REVOKE - withdraw access privileges given with the GRANT command
Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.
- COMMIT - save work done
- SAVEPOINT - identify a point in a transaction to which you can later roll back
- ROLLBACK - restore database to original since the last COMMIT
- SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use
--->>>Following Video Shows you how to install the Microsoft SQL Server 2005<<<---