Types of Databases and statements, Create, Alter, delete

msi_fci

SQL Server has 2 types of Databases :

System Tables: Store Information about SQL server as a whole
User Tables : Databses that user creates.

Installing SQL Server creates system tables ( as in master DB) and sample user databases such as:
(master , model , tempdb , msdb , distribution , pubs and Northwind)

There are 3 types of statements to use in SQL :

DDL Statements : Create and Drop Tables , Views , Indexes and Constarints
DML Statements: Insert , Update , Delete and Select from Tables or Views
DCL Statements : Create and Drop Users or Roles + Grant and Revoke Privileges
DCL Statements .

Environment of SQL Server:

Service Manager : To Start , Stop or Pause Server
Enterprise Manager : To do all SQL functions through interface
Query Analyzer :To do all SQL functions by hand (writing statements)

DDL Statements

On both Enterprise Manager & Query Analyzer

1.Creating Tables

Used to create a table with a specific structure inside a selected database

Code:

CREATE  TABLE <tablename>
<column name> <data type> [<column constraint>
PRIMARY KEY <column list>
FOREIGN KEY <column list> REFERENCES <table specifications>

Example:

Code:

CREATE TABLE STAFF
 (
  STAFF_ID int Primary Key,
  STAFF_NAME char(20) Not Null,
  STAFF_SALARY real Null,
  STAFF_PHOTO image Null
);

2. Dropping Tables

Used to remove a table from a selected database

Code:

DROP TABLE <tablename>

Example:

Code:

DROP TABLE STAFF;

3. Altering Tables

Used to change the table structure

Code:

ALTER TABLE <tablename>
ADD [column] <column name><data type>
DROP [column] <column name>

Example:

Add the column STAFF_EMAIL to the table STAFF

Code:

ALTER TABLE STAFF
ADD STAFF_EMAIL char(30);

Example:

Remove the column STAFF_EMAIL from the table STAFF

Code:

ALTER TABLE STAFF
DROP COLUMN STAFF_EMAIL;

Java

C/C++

PHP

C#

HTML

CSS

ASP

Javascript

JQuery

AJAX

XSD

Python

Matlab

R Scripts

Weka

Types of Databases and statements, Create, Alter, delete