Switch to full style
Learn how to use MS-DB
Post a reply

Types of Databases and statements, Create, Alter, delete

Sat Apr 07, 2007 6:43 pm

SQL Server has 2 types of Databases :

  1. System Tables: Store Information about SQL server as a whole
  2. 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 :

  1. DDL Statements : Create and Drop Tables , Views , Indexes and Constarints
  2. DML Statements: Insert , Update , Delete and Select from Tables or Views
  3. DCL Statements : Create and Drop Users or Roles + Grant and Revoke Privileges
  4. 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;




Post a reply
  Related Posts  to : Types of Databases and statements, Create, Alter, delete
 DDL Statements-Adding Constraints-CREATE-ALTER-TABLE     -  
 DML Statements, insert row, delete row     -  
 SQL ALTER Command     -  
 clarification of high dimensional databases using query     -  
 For statement without all three statements     -  
 java statements     -  
 translate XQuery to SQL statements     -  
 Types of Pointers in C++     -  
 Types of Registers     -  
 temperature transformer between different types     -