Total members 11889 |It is currently Thu Mar 28, 2024 4:41 pm Login / Join Codemiles

Java

C/C++

PHP

C#

HTML

CSS

ASP

Javascript

JQuery

AJAX

XSD

Python

Matlab

R Scripts

Weka





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;




_________________
Recommend my post if you found it helpful.


Author:
Newbie
User avatar Posts: 15
Have thanks: 0 time
Post new topic Reply to topic  [ 1 post ] 

  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     -  
 What are the types of jdbc drivers.?     -  



cron





Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
All copyrights reserved to codemiles.com 2007-2011
mileX v1.0 designed by codemiles team
Codemiles.com is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for sites to earn advertising fees by advertising and linking to Amazon.com