SQL

test

 

SQL

SQL Notes For Database,Table,Constraints and Functions

DATABASE:

1        It is a Collection of  Tables

2        System Databases          

               These system Databases are “Brain’s of SQL Server” Then the SQL server can not operate without them.

 DATABASE CREATION:

Syntax:

CREATE DATABASE database_name

TABLE CREATION:

Syntax:

CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name2 data_type,
...
)

CREATE TABLE [dbo].[DEPARTMENT](

            [DEPID] [int] NOT NULL,

            [EMPID] [int] NULL,

            [DOJ] [datetime] NULL,

            [DEPNAME] [varchar](30) 

 )

            --TO ADD NEW COLUMN IN TABLE:

                  ALTER TABLE Products ADD Average INT

--TO DROP THE COLUMN IN TABLE:

ALTER TABLE Products DROP COLUMN Average

--TO DROP THE COLUMN:

                  DROP TABLE Products

CORE OF SQL:

DML: (Data Manipulation Language)

DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.

Examples: SELECT, UPDATE, INSERT statements

DDL: (Data Definition Language)

DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.

Examples: CREATE, ALTER, DROP statements

DCL: (Data Control Language)

DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.

Examples: GRANT, REVOKE statements

TCL: (Transaction Control Language)

TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.

Examples: COMMIT, ROLLBACK statements

CONSTRAINTS:

Primary Key:

                    It is a column which is uniquely identifies each row of the table.

                    Must contain unique value.

                   Cannot contain null value.

                   

     E.g:       

            CREATE TABLE Persons

           (

           P_Id int NOT NULL PRIMARY KEY,

           LastName varchar(255) NOT NULL,

           FirstName varchar(255),

           Address varchar(255),

           City varchar(255)

           )