GST Billing Software

4.2 USE 4.3 Alter

header image

DLL

Erachana Line

4.1 Create

It is used to create table and database.

4.1.1 Create database

The CREATE DATABASE statement is used to create a new database.

Syntax :

CREATE DATABASE databasename;

Ex :

CREATE DATABASE ContactManager;

4.1.2 Create Table

The CREATE TABLE statement is used to create a new table in a database.

Syntax

CREATE TABLE table_name (

column1 datatype,

column2 datatype,

column3 datatype,

....

);

The column parameters specify the names of the columns of the table.

The datatype parameter specifies the type of data the column can hold (e.g. varchar, integer, date, etc.).

EX

CREATE TABLE CUSTOMER

(

CUSTOMER_ID INT PRIMARY KEY,

CUSTOMER_NAME VARCHAR(255),

CUSTOMER_ADD VARCHAR(255),

PHONE_NO NUMERIC(10)

);

4.1.3 Create table with Constraints

Constraints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with the ALTER TABLE statement.

Syntax

CREATE TABLE table_name (

column1 datatype constraint,

column2 datatype constraint,

column3 datatype constraint,

....

);

SQL NOT NULL Constraint on CREATE TABLE

The following SQL ensures that the "ID", "LastName", and "FirstName" columns will NOT accept NULL values:

CREATE TABLE Persons (

ID int NOT NULL,

LastName varchar (255) NOT NULL,

FirstName varchar (255) NOT NULL,

Age int

);

The following SQL creates a UNIQUE constraint on the "ID" column when the "Persons" table is created:

SQL UNIQUE Constraint on CREATE TABLE

CREATE TABLE Persons (

ID int NOT NULL UNIQUE,

LastName varchar (255) NOT NULL,

FirstName varchar (255),

Age int

);

SQL PRIMARY KEY on CREATE TABLE

The following SQL creates a PRIMARY KEY on the "ID" column when the "Persons" table is created:

CREATE TABLE Persons (

ID int NOT NULL PRIMARY KEY,

LastName varchar (255) NOT NULL,

FirstName varchar (255),

Age int

);

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

CREATE TABLE Persons (

ID int NOT NULL,

LastName varchar (255) NOT NULL,

FirstName varchar (255),

Age int,

CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)

);

SQL FOREIGN KEY on CREATE TABLE

The following SQL creates a FOREIGN KEY on the "PersonID" column when the "Orders" table is created:

CREATE TABLE Orders (

OrderID int NOT NULL PRIMARY KEY,

OrderNumber int NOT NULL,

PersonID int FOREIGN KEY REFERENCES Persons (PersonID)

);

SQL CHECK on CREATE TABLE

The following SQL creates a CHECK constraint on the "Age" column when the "Persons" table is created. The CHECK constraint ensures that you cannot have any person below 18 years:

CREATE TABLE Persons (

ID int NOT NULL,

LastName varchar (255) NOT NULL,

FirstName varchar (255),

Age int CHECK (Age>=18)

);

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

CREATE TABLE Persons (

ID int NOT NULL,

LastName varchar (255) NOT NULL,

FirstName varchar (255),

Age int,

City varchar (255),

CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')

);

SQL DEFAULT on CREATE TABLE

The following SQL sets a DEFAULT value for the "City" column when the "Persons" table is created:

CREATE TABLE Persons (

ID int NOT NULL,

LastName varchar (255) NOT NULL,

FirstName varchar (255),

Age int,

City varchar (255) DEFAULT 'Sandnes'

);

The following SQL statement defines the "ID" column to be an auto-increment primary key field in the "Persons" table:

CREATE TABLE Persons (

ID int IDENTITY (1,1) PRIMARY KEY,

LastName varchar (255) NOT NULL,

FirstName varchar (255),

Age int

);

• The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature.

• In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record.

When you have multiple databases in your SQL Schema, then before starting your operation, you would need to select a database where all the operations would be performed.

The SQL USE statement is used to select any existing database in the SQL schema.

Syntax

USE DATABASENAME;

EX: USE Contact_Manager

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

4.3.1 Add modify and Drop

ALTER TABLE - ADD Column

syntax:

ALTER TABLE table_name
ADD column_name datatype;

EX:

ALTER TABLE Customer
ADD Salary int;

ALTER TABLE - DROP COLUMN

To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):

ALTER TABLE table_name
DROP COLUMN column_name;

EX:

ALTER TABLE Customer
DROP COLUMN Salary;

ALTER TABLE - ALTER/MODIFY COLUMN

syntax:

ALTER TABLE table_name
ALTER COLUMN column_name datatype;

EX:

ALTER TABLE Customer

ALTER COLUMN Gener int;

4.3.2 Add modify and Drop with Constraints

SQL UNIQUE Constraint on ALTER TABLE

To create a UNIQUE constraint on the "ID" column when the table is already created, use the following SQL:

ALTER TABLE Persons
ADD UNIQUE (ID);

To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the following SQL syntax:

ALTER TABLE Persons
ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);

To drop a UNIQUE constraint, use the following SQL:

ALTER TABLE Persons
DROP CONSTRAINT UC_Person;

SQL PRIMARY KEY on ALTER TABLE

To create a PRIMARY KEY constraint on the "ID" column when the table is already created, use the following SQL:

ALTER TABLE Persons
ADD PRIMARY KEY (ID);

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);

DROP a PRIMARY KEY Constraint

To drop a PRIMARY KEY constraint, use the following SQL:

ALTER TABLE Persons
DROP CONSTRAINT PK_Person;

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

SQL FOREIGN KEY on ALTER TABLE

To create a FOREIGN KEY constraint on the "PersonID" column when the "Orders" table is already created, use the following SQL:

ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons (PersonID);

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons (PersonID);

DROP a FOREIGN KEY Constraint

ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;

SQL CHECK on ALTER TABLE

To create a CHECK constraint on the "Age" column when the table is already created, use the following SQL:

ALTER TABLE Persons
ADD CHECK (Age>=18);

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');

DROP a CHECK Constraint

To drop a CHECK constraint, use the following SQL:

ALTER TABLE Persons
DROP CONSTRAINT CHK_PersonAge;

SQL DEFAULT on ALTER TABLE

To create a DEFAULT constraint on the "City" column when the table is already created, use the following SQL:

ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'Sandnes';

DROP a DEFAULT Constraint

To drop a DEFAULT constraint, use the following SQL:

ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;

4.4 Drop

The SQL DROP TABLE statement is used to remove a table definition and all the data, indexes, triggers, constraints and permission specifications for that table.

Syntax

DROP TABLE tablename;

4.5 Truncate

The SQL TRUNCATE TABLE command is used to delete complete data from an existing table.

You can also use DROP TABLE command to delete complete table, but it would remove complete table structure form the database and you would need to re-create this table once again if you wish you store some data.

Syntax

TRUNCATE TABLE table_name;

Assignment

Create Contact Manager Database, include tables Contactdetails (ID, FirstName, MiddleName, Last Name, Phone no1, Phone no2, Placeid, stateid, countryid, designationid, age, gender) country(Country id, countryname), state(stated,name,countryid), Place(placeid, name, stated) and designation(designationid,designation). Use all the constraints mentioned above. (Try to use all DDL syntax).