3rd Day Training. About Table creating, Alter table, insert values in table- 11-06-2025

Rename a table
ALTER TABLE RENAME TO .

*Rename a column
*
ALTER TABLE

RENAME TO

*CHANGE DATA TYPE OF COLUMN *

ALTER TABLE

ALTER COLUMN < column name> TYPE

****To check the datatypes of the column

---

SELECT column_name, data_type FROM information_schema.columns where table_name='your_table_name';

**ADD NEW COLUMN

ALTER TABLE

ADD COLUMN < column name data type ;

CONSTRAINTS

SQL constraints are rules applied to columns or tables in a relational database to limit the type of data that can be inserted, updated, or deleted. These rules ensure the data is valid, consistent, and adheres to the business logic or database requirements.

Constraints can be enforced during table creation or later using the ALTER TABLE statement

TYPES-

PRIMARY- A PRIMARY KEY constraint is a combination of the NOT NULL and UNIQUE constraints. It uniquely identifies each row in a table. A table can only have one PRIMARY KEY, and it cannot accept NULL values. This is typically used for the column that will serve as the identifier of records.

Example:

CREATE TABLE Student
(
ID int(6) NOT NULL UNIQUE,
NAME varchar(10),
ADDRESS varchar(20),
PRIMARY KEY(ID)
);
Explanation: In this case, the ID column is set as the primary key, ensuring that each student’s ID is unique and cannot be NULL.

FOREIGN KEY

A FOREIGN KEY constraint links a column in one table to the primary key in another table. This relationship helps maintain referential integrity by ensuring that the value in the foreign key column matches a valid record in the referenced table.

*UNIQUE KEY
*
The UNIQUE constraint ensures that all values in a column are distinct across all rows in a table. Unlike the PRIMARY KEY, which requires uniqueness and does not allow NULLs, the UNIQUE constraint allows NULL values but still enforces uniqueness for non-NULL entries.

Example:

CREATE TABLE Student
(
ID int(6) NOT NULL UNIQUE,
NAME varchar(10),
ADDRESS varchar(20)
);
Explanation: Here, the ID column must have unique values, ensuring that no two students can share the same ID. We can have more than one UNIQUE constraint in a table.

** NOT NULL Constraint
**
The NOT NULL constraint ensures that a column cannot contain NULL values. This is particularly important for columns where a value is essential for identifying records or performing calculations.

Source- https://www.geeksforgeeks.org/sql-constraints/

*SERIES OF NUMBERS- SERIAL
*

CREATE TABLE < table name > (column name SERIAL PRIMARY KEY )

*Create table with primary keys and constraints-
*

CREATE TABLE < table name > (column name SERIAL PRIMARY KEY, COLUMN 2 DATA TYPE, COLUMN 3 DATA TYPE )

**

INSERTION

**

Insert values based on the above table created.

INSERT INTO

VALUES (1,MANGO,200)

*Multiple values.
*
INSERT INTO

(C2,C3,C4) VALUES (BANANA, 20) (APPLE, 30)

*UPDATE :
*

UPDATE

SET prod_name=' ALPHONSO MANGO' where prod_id=5;
UPDATE SET prod_name='ALPHONSO MANGO' where prod_NAME=MANGO;
UPDATE SET prod_name='ALPHONSO MANGO', MRP=60 where prod_ID=5

*DELETE query:
* DELETE from

where prod_id=2;