SQLServer: SQL Basics

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

SQL BASICS

Structured Query Language (SQL)

• SQL is a standard language for accessing and manipulating databases.
• According to ANSI (American National Standards Institute), it is the standard language for relational database management systems.
•  SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database.
• The standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create", and "Drop".
• The data in RDBMS is stored in database objects called tables.
•  A table is a collections of related data entries and it consists of columns and rows.
•  SQL can be divided into two parts: The Data Manipulation Language (DML) and the Data Definition Language (DDL) ,DCL and TCL .
•  The query and update commands form the DML part of SQL.

1.   SELECT - extracts data from a Table.
2.   UPDATE - updates data in a Table.
3.   DELETE - deletes data from a Table.
4.  INSERT INTO - inserts new data into a Table.

•  The DDL part of SQL permits database tables to be created or deleted. The most important DDL statements in SQL are:

1.  CREATE DATABASE - creates a new database.
2.  ALTER DATABASE - modifies a database.
3.  CREATE TABLE - creates a new table.
4.  ALTER TABLE - modifies a table.
5.  DROP TABLE - deletes a table.
6.  CREATE INDEX - creates an index (search key).

DCL: DCL stands for Data Control Language. DCL is used to create roles, grant and revoke permissions, establish referential integrity etc.

Examples: GRANT, REVOKE statements

 TCL: TCL stands for Transactional Control Language. TCL is used to manage transactions within a database.

Examples: COMMIT, ROLLBACK statements

Features Of Structured Query Language :

1.         It allows the user to communicate with server.
2.         Its highly efficient.
3.         Its easy to  use,
4.         By allowing the user to retrieve and manipulate the data from the system.
5.         It’s a platform independent and architecture independent.

Creating  And Managing Tables :

·        An SQL database is a collection of multiple data structures.

·        These data structures are called as Database objects.

·        The major database objects associated with database programmer.

·        Table->Used to store data, basic unit in SQL.

Rules To Create A Table  :

·        The user should have permission on CREATE TABLE  command, and storage area should be allocated.

·        Names cannot be Oracle servers reserved words.

·        Tables is a collection of attribute names and SQLdata types and required with along with required constraints.

 

CREATE Table Statement :

Syntax :

CREATE TABLE <Table_Name>

          (

          Column_Name1 <DataType>(Width),

          Column_Name2 <DataType>(Width),

          Column_NameN <DataType>(Width),

          );

Note:

•         All Data Types may not have the width property .
•         No two columns in the same table can have the same name.

 Data Types in SQL Server:

·        The Data Type is associated a fixed set of properties with that value stored.

·        The values of one data type are different from another data type.

·        The data types defines the domain of values that each column can contain.

	Data type name	Number of bytes	Comments
Integer	tinyint	1
	smallint	2
	int	4
	bigint	8
Exact numeric	decimal[p[,s]]	5-17
	numeric[p[,s]]	5-17
Appropiate numeric	float[(n)[	8
	real	4
Monetary	smallmoney	4
	money	8
Date and time	smalldatetime	4
	datetime	8
Characters	char[(n)]	0-8000	non-Unicode
	varchar[(n)]	0-8000	non-Unicode
	varchar(max)	0-2 GB	non-Unicode, 16 bytes pointer on row, ,preferred over text data type
	text	0-2 GB	non-Unicode, 16 bytes pointer or in row, obsolete, varchar(max) prefered
	nchar[(n)]	0-8000	max 4000 unicode characters
	nvarchar[(n)]	0-8000	max 4000 unicode characters
	nvarchar(max)	0-2 GB	16 bytes pointer or in row, ,preferred over ntext data type
	ntext	0-2 GB	16 bytes pointer, obsolete, nvarchar(max) prefered
Binary	binary[(n)]	0-8000
	varbinary[(n)]	0-8000
	varbinary(max)	0-2 GB	16 bytes pointer or in row, ,preferred over image data type
Image	image	0-2 GB	16 bytes pointer, obsolete, varbinary(max) prefered
Global identifier	uniqueidentifier	16
XML	xml	0-2GB	16 bytes pointer
Special	bit	1	1 byte for every 8 bit columns
	cursor	0-8
	timestamp	8	one column per table
	sysname	256
	table	-
	sql_variant	0-8016