SQL CREATE TABLE
Creating table
CREATE table table_name (
Column1 datatype (size),
column2 datatype (size),
. . columnN datatype(size)
);
Example
CREATE TABLE emp (
empno INT ,
name VARCHAR(50),
salary DECIMAL(10,2)
);
Inserting data into table
Syntax
NSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
INSERT INTO emp (empno, name, salary)
VALUES (101, 'Alice', 55000.00);
Inserting multiple rows at once
INSERT INTO emp (empno, name, salary)
VALUES
(102, 'Bob', 60000.00),
(103, 'Charlie', 45000.00),
(104, 'Diana', 70000.00);
Create table from existing table
CREATE TABLE new_table_name AS
SELECT column1, column2, ...
FROM existing_table_name
WHERE ...;
CREATE TABLE empCopy AS
SELECT empno, name
FROM emp;
View structure of table
Desc table_name
Example
Desc emp;
Dropping table
The SQL DROP TABLE statement is used to delete tables in a database, along
with all associated data
Drop table table_name;
Drop table emp;
List the table names
Show tables;
Renaming the table
Rename table old_name to new_name
Rename table emp to employee;
Truncate table
The TRUNCATE TABLE statement in SQL is a powerful command used to swiftly
remove all rows from a table, leaving the table structure intact
Syntax
TRUNCATE TABLE table_name;
TRUNCATE TABLE EMPLOYEE;
Copy Table
SQL Cloning is an operation that means making a copy of a table.
CREATE TABLE clone_table SELECT * FROM original_table;
CREATE TABLE STUDENT_COPY AS SELECT * FROM STUDENT;
SELECT * FROM STUDENT_COPY;
OR
CREATE TABLE clone_table LIKE original_table;
SQL ALTER TABLE
SQL ALTER TABLE statement modify the structure of an existing table in a
database.
ALTER TABLE table_name [ADD | DROP | MODIFY] column_name datatype;
ADD
1. ADD
The ADD clause is used to add a new column to an existing table. You must specify the name
of the new column and its data type.
Syntax:
ALTER TABLE table_name
ADD column_name datatype;
Example:
ALTER TABLE Students
ADD Email varchar(255);
Here, we are adding a column named Email to Student table
Modify
2. MODIFY
The MODIFY (or ALTER COLUMN in some databases like SQL Server) clause is used to modify
the definition of an existing column, such as changing its data type or size.
Syntax:
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
Example:
ALTER TABLE Students
MODIFY COLUMN Address VARCHAR(100);
Here, we are modifying the column named Address datatype that is VARCHAR(100).
Drop
3. DROP
The DROP clause allows you to remove a column from a table. Be cautious when using this
command as it will permanently remove the column and its data.
Query:
ALTER TABLE table_name
DROP COLUMN column_name;
Example:
ALTER TABLE Students
DROP COLUMN Grade;
Here, we are removing a column named Grade from Student table
Rename To
5. RENAME TO
We can rename an entire table using the RENAME TO clause. This changes the name of the
table while preserving its structure and data.
Query:
ALTER TABLE table_name
RENAME TO new_table_name;
Example:
ALTER TABLE Customer
RENAME TO Clients;
Adding columns
1. Adding Columns (AGE and COURSE)
To add new columns AGE and COURSE to the Student table, use ALTER TABLE
statement with the ADD clause.
Query:
ALTER TABLE Student ADD
(AGE number (3), COURSE VARCHAR (40));
Modifying Column
2. Modify Column COURSE to Reduce its Size
To reduce the size of the COURSE column from VARCHAR(40) to VARCHAR(20),
use the MODIFY clause.
Query:
ALTER TABLE Student
MODIFY COURSE varchar(20);
Explanation: COURSE column will now allow a maximum of 20 characters
instead of 40.
Dropping column
3. Drop COURSE Column from Student Table
To remove the COURSE column from the Student table, use the DROP COLUMN
clause.
Query:
ALTER TABLE Student
DROP COLUMN COURSE;
Explanation: This permanently deletes the COURSE column from the table.