Chapter 5: Introduction to SQL

SQL is a database computer language designed for managing data in a RDBMS. SQL is an ANSI standard and is non-procedural. T-SQL stands for Transact SQL which is a Microsoft product and is an extension of SQL used in SQL server.

Categories of SQL:

DDL (Data Definition Language)
DCL (Data Control Language)
DML (Data Manipulation Language)

DDL (Data Definition Language)

DDL defines the structure of the database which manages table and index structure. The common statements of DDL are CREATE, ALTER, RENAME, and DROP.

CREATE DATABASE
ALTER DATABASE
CREATE TABLE
ALTER TABLE
	ADD, MODIFY and DROP
DROP TABLE
CREATE INDEX
DROP INDEX
CREATE VIEW
DROP VIEW
CREATE SYNOMY -- CREATES ALIAS FOR A TABLE NAME
DROP SYNOMY -- REMOVES ALIAS FOR A TABLE NAME

DCL (Data Control Language)

DCL defines the privileges granted to database users. The common statements of DCL are GRANT, REVOKE, and DENAY.

DML (Data Manipulation Language)

DML is used to retrieve and modify data. The common statements are SELECT, UPDATE, DELETE, INSERT INTO.

Create Database Statement

CREATE DATABASE database_name

Create Table Statement

CREATE TABLE table_name (
	column_name data_type,
	...
)

CREATE TABLE CUSTOMER (
	CustomerId int PRIMARY KEY,
	CustomerNumber int NOT NULL UNIQUE CHECK (CustomerNumber > 0),
	Country varchar(20) DEFAULT 'Ethiopia',
	...
)

CREATE TABLE CUSTORDER (
	OrderId int IDENTITY(1,1) PRIMARY KEY,
	OrderDescription varchar(500),
	CustomerId int NOT NULL FOREIGN KEY REFERENCES CUSTOMER (CustomerId)
)

Alter Table Statement

ALTER TABLE table_name ADD column_name datatype
ALTER TABLE Customer ADD Email varchar(50)

ALTER TABLE table_name DROP COLUMN column_name
ALTER TABLE Customer DROP COLUMN Email

ALTER TABLE table_name ALTER COLUMN column_name datatype

DROP TABLE table_name
DROP TABLE CUSTOMER