SQL Note - Manipulation

This blog post is an instructional guide on SQL query writing for database manipulation, covering fundamental commands and column constraints with practical examples.

Columns constraints

Column constraints are rules applied to the values of individual columns to ensure data integrity and specific data characteristics.

Description:

A Primary Key is essential for uniquely identifying each row.
Unique columns ensure distinct values in each row.
Not Null columns must always contain a value.
Default sets a predefined value when no other is provided.

Sample Query:

CREATE TABLE student (
 id INTEGER PRIMARY KEY, 
 name TEXT UNIQUE, 
 grade INTEGER NOT NULL, 
 age INTEGER DEFAULT 10 
);

CREATE TABLE

The CREATE TABLE command establishes a new table, defining its name and the nature of its columns.

Description:

Specify the table name and column names with their data types.

Sample Query:

CREATE TABLE classroom (
  class_id INTEGER PRIMARY KEY,
  class_name TEXT NOT NULL,
  teacher_id INTEGER
);

INSERT INTO

The INSERT INTO statement is used for populating tables with new records.

Description:

You can insert values in the order of columns or specify column names directly.

Sample Queries:

Insert into columns in order:

INSERT INTO classroom
VALUES (101, 'Mathematics', 15);

Insert into columns by name:

INSERT INTO classroom (class_id, class_name)
VALUES (102, 'Science');

ALTER TABLE

The ALTER TABLE statement allows modifications to an existing table’s structure, such as adding new columns.

Description:

Use this to add or modify columns in an existing table.

Sample Query:

ALTER TABLE classroom
ADD column seat_count INTEGER;

DELETE

The DELETE statement is crucial for removing specific records from a table.

Description:

It’s often combined with a WHERE clause to target specific records.

Sample Query:

DELETE FROM classroom
WHERE class_name = 'Mathematics';

UPDATE

The UPDATE statement is used for altering existing records within a table.

Description:

Includes a SET clause to specify columns for updating and a WHERE clause to select records.

Sample Query:

UPDATE classroom
SET teacher_id = 20
WHERE class_id = 101;