SQL Overview

A client/server database management system consists of two components: a front-end component (the client), which is used to present and manipulate data; and a back-end component (the database server), which is used to store, retrieve, and protect the databases.

In a client/server system, the majority of the data processing is done on the server instead of the clients. This means that a client/server system can reduce your network traffic.

You will typically create a database to contain all of the associated tables, indexes, and other database objects for a particular application.

Tables
One of the most critical components of a database is its tables. That's because SQL Server uses tables to store data. You create a table to contain a set of related information. When you create a table, you define its columns. Columns refer to the individual pieces of information (fields) you want to track for a specific table. For example, if you want to create a table to store customer information, you should define columns to store such information as an account number, name, address, city, state, ZIP code, and telephone number for each customer

The Structured Query Language
The commands you primarily use to query a database on a database server are part of the Structured Query Language (SQL).
The Structured Query Language is a standardized set of commands used to work with databases. Microsoft SQL Server 2000 supports an enhanced version of SQL referred to as Transact-SQL. You use Transact-SQL commands to create, maintain, and query databases.

You can group SQL statements into three categories:

1. Data Definition Language (DDL) statements: Enable you to create database objects.
2. Data Control Language (DCL) statements: Enable you to determine, set, or revoke users’ permission to sql databases and their objects.
3. Data Manipulation Language (DML) statements: Enable you to query or modify data.

The primary DDL statements are CREATE, ALTER, and DROP

• Create: Create a database or a database object
  
• Alter: Modify the structure of a database or one of its objects
  
• Drop: Delete a database or object

You use DCL statements to set users' permissions.

• Grant: Assign permissions to users or roles to enable them to access a database or to execute specific SQL statements.
  
• Deny: Prevent a user or role from accessing a database, or one of its objects.
  
• Revoke: Remove a permission you have granted.

Example

GRANT INSERT, UPDATE, DELETE  ON stores TO BOB

DCL to set permissions on those objects

• SELECT: use the SELECT statement to display data from one or more tables
• INSERT: use the INSERT statement to add rows to a table
• UPDATE: Use UPDATE to modify one or more rows in a table
• DELETE: use the DELETE statement to delete one or more rows from a table.

SELECT st_name, st_contact
FROM pubs.dbo.store

1. SQL Keyword are all uppercase letters like SELECT
2. User supplied parameters of SQL statements are all lowercase letters like SELECT * FROM stores
3. Optional Syntax are enclosed in square brackets [] 
4. Options that are alternative to each other are separated by a vertical bar character | like WITH [ NO_LOG | TRUNCATE_ONLY]

SQL Server enables you to execute SQL statements using a variety of techniques.

SQL Query Analyzer enables you to work with queries in a graphical environment.
Command-line query utility
Batches and Scripts. : A SQL batch is simply a set of SQL statements executed together.

Data Types:

• Binary: Hexadecimal values to represent one or more bytes of information. SQL Data Types(binary, varbinary, and bit)
• Characters: Letters, numbers, and special characters. SQL Data Types(char, varchar, nchar, and nvarchar)
• Dates: Date and time information. SQL Data Types (Datetime, smalldatetime)
• Image: Graphic images. SQL Data Types (image)
• Numbers: Numeric Data. SQL Data Types (int, tinyint, smallint, bigint, numeric, decimal, money, smallmoney, float, real)
• Text: Large amounts of character and binary data. AQL Data types (text, ntext)

Referring to SQL Server Objects

theserver.store.owner_name.customer
theserver(servername).store(database).owner_name.customer(table)

The owner of an object is typically the same as the owner of the database. By default, the owner of a database is usually the database user account named dbo

Naming Objects

• Names must be between 1 and 128charecters in length(including letters, numbers, and symbols)  No spaces in a standard identifier
• The first character of the objects name must begin with a letter a-z or A-Z
• Any subsequent characters you use in the name can be any character including letters, numbers or the @,  #,  $, or _ symbols
• Use only letters or underscores (try to avoid numbers)
  
• Try to use underscore characters as little as possible.
  
• Use a letter as the first character of the name. (don't start names with underscores)
  
• Avoid abbreviations (can lead to misinterpretation of names)
  
• Avoid using spaces in names even if the system allows it.
  
• Avoid using reserved SQL Keywords, such as SELECT or GRANT