glasses in front of computers with random code on the screen

May 29, 2019 - By Bredet Services

SQL Statements to create, manipulate and delete SQL tables:

In this blog, I would like to show some useful queries which are helpful for SQL beginners to create, manipulate and delete SQL data or tables.

 

–CREATE TABLE:

 

CREATE TABLE USERS

(

USERID INT,

FIRSTNAME VARCHAR(100),

LASTNAME VARCHAR (100),

CITY VARCHAR (100)

);

INSERT INTO USERS

VALUES (55, ‘ROHIT’, ‘SHARMA’, ‘MUMBAI’);

 

–CREATE TABLE WITH PRIMARY KEY:

 

CREATE TABLE COUNTRY

(

COUNTRY_ID INT,

COUNTRYNAME  VARCHAR (50),

COUNTRYCODE VARCHAR (3),

CAPITAL VARCHAR(50),

PRIMARY KEY (COUNTRY_ID)

);

 

–SET ‘ID’ FIELD TO AUTO INCREMENT AND SET FIELD PROPERTIES TO ‘NOT NULL’ TO DETEMINE NO NULL VALUES ARE ALLOWED:

 

CREATE TABLE WAREHOUSE

(

ID INT NOT NULL IDENTITY,

WAREHOUSE_NAME VARCHAR (20) NOT NULL,

CITY VARCHAR (20) NOT NULL,

STATE VARCHAR (20) NOT NULL,

COUNTRY VARCHAR (20) NOT NULL,

PRIMARY KEY (ID)

);

 

–ADD COLUMNS:

 

ALTER TABLE EMPLOYEES ADD DOB DATE;

 

–DROP COLUMNS:

 

ALTER TABLE EMPLOYEES DROP COLUMN DOB;

 

–INSERT INTO – YOU DON’T NEED TO MENTION ALL VALUES FOR COLUMNS.:

 

INSERT INTO Employees

VALUES (11,‘DAVID’,‘YOUNG’,35);

 

–ALTER DATA:

 

UPDATE Employees

SET SALARY=6000

WHERE ID=1

 

UPDATE Employees

SET Salary=5000, FirstName=‘Robert’

WHERE ID=1

 

–CHANGE DATA TYPES OF COLUMNS:

 

ALTER TABLE EMPLOYEES

ALTER COLUMN FIRSTNAME VARCHAR(100) NOT NULL

 

–RENAME COLUMN NAMES:

 

sp_rename ‘EMPLOYEES.SALARY01’, ‘SALARY’, ‘COLUMN’;

 

RENAME TABLE:

sp_rename ‘TEMP1’, ‘TEMP’;

 

–DELETE DATA:

 

DELETE FROM Employees

WHERE ID=1;

 

–DROP TABLE:

 

DROP TABLE TEMP;

 

–CREATE VIEWS:

 

CREATE VIEW LIST AS

SELECT FIRSTNAME, SALARY

FROM Employees;

 

–MODIFY VIEWS:

 

ALTER VIEW LIST AS

SELECT FIRSTNAME, LASTNAME, SALARY

FROM Employees;

 

–DROP VIEWS:

DROP VIEW TEMP1;

 

Find this blog helpful? Click here to check out part one of this blog series.

Share this story: