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;