DEV Community

loading...

SQL: DDL Basic command examples

supriya2371997 profile image Supriya Kolhe Updated on ・1 min read

[-linkedin: supriyakolhe123-]
[-link to the below queries:
https://github.com/supriya2371997/SQL/blob/main/DDL
-]

1---> ALTER, CREATE, RENAME TABLE
CREATE DATABASE DDL;
USE DDL;

CREATE TABLE Alter_Example
(
id INT,
name VARCHAR(5),
date DATE,
PRIMARY KEY (id, name)
);

INSERT INTO Alter_Example VALUES(1,'sejal','1997-07-23');
INSERT INTO Alter_Example VALUES(2,'sup','1997-07-22');
INSERT INTO Alter_Example VALUES(3,'sunny','1997-07-21');

SELECT* FROM Alter_Example;
add column in the existing table

ALTER TABLE Alter_Example add age INT,
user_location VARCHAR(10);

DROP existing column
ALTER TABLE Alter_Example DROP COLUMN age;

alter column datatype
ALTER TABLE Alter_Example ALTER COLUMN DATE DATETIME;

add CHECK CONSTRAINT USING ALTER: 2 ways

  1. without allocating name: dropping CONSTRAINT isn't possible. fOR that TABLE must be dropped
  2. allocating name: recommended becaUSE dropping constraint is possible without dropping the table

ALTER TABLE Alter_Example add CHECK (age>18 AND age <40);
ALTER TABLE Alter_Example add age INT CONSTRAINT cs_age CHECK (age>18 AND age<40);

date format: yyyy-mm-dd

DROP CONSTRAINT
ALTER TABLE Alter_Example DROP CONSTRAINT cs_age;

rename table
sp_rename 'Alter_Example','Alter_Empl';
sp_rename 'Alter_Example.name','first_name','COLUMN';
OR
sp_rename 'Alter_Example.name','first_name';

ALTER TABLE Employee_old RENAME TO Employee_current;

2---> TRUNCATE, DROP, DELETE*
remove data but the structure stays intact
TRUNCATE TABLE temp;

Delete data AND TABLE structure
DROP TABLE temp;

Delete data AND TABLE structure
DROP TABLE temp;

Discussion (4)

Collapse
walternascimentobarroso profile image
Walter Nascimento

Excellent post, if you have time take a look at markdown, it will help you style your post, making it even more elegant ;)

dev.to/walternascimentobarroso/qui...

Collapse
supriya2371997 profile image
Supriya Kolhe Author

Thank you for your suggestions sir. Feedbacks and suggestions are always welcome.

Collapse
sumit profile image
Sumit Singh

Thanks for contributing to community. Your post will look much better if you follow proper coding convention of the markdown.

Collapse
supriya2371997 profile image
Supriya Kolhe Author

Thank you for your suggestions sir. Feedbacks and suggestions are always welcome.

Forem Open with the Forem app