Class Twelve; Text (Modifying data within database)

INSERT

The INSERT statement in SQL is used to add new records to a database table.

Basic Syntax:

INSERT INTO [employees]

  VALUES (‘Loveth’, ‘Pat’, ‘HR’);

Types of INSERT statements:

1. Single-row INSERT: Inserts one record at a time.

INSERT INTO [StudentsInformation]

VALUES (‘Philip Momoh’, 25, ‘[email protected]’, ‘NO 10 Sapele Road ‘, ‘08012345678’, ‘Lagos’);

2. Multi-row INSERT: Inserts multiple records at once.

INSERT INTO [StudentsInformation]

VALUES 

(‘Jane Doe’, 30, ‘[email protected]’, ‘NO 20 Secondary Street’, ‘0701234567’, ‘Abuja’),

(‘Bob Smith’, 35, ‘[email protected]’, ‘NO 30 Tertiary Street’, ‘0901234567’, ‘Port Harcourt’);

Update Statement

The UPDATE statement in SQL is used to modify existing records in a database table.

Basic Syntax:

UPDATE [table name]

SET [table name].the Header of the line you want to replace = new info

WHERE [table name].header = ‘Unique identifier’

Example

UPDATE [dbo].[Pupils_information]

SET [dbo].[Pupils_information].Age = 11

WHERE [dbo].[Pupils_information].Name = ‘Martins Ogbodo’

Types of UPDATE statements

1. Single-column UPDATE Updates one column.

Example

UPDATE [StudentsInformation]

SET Email = ‘[email protected]’

WHERE Name = ‘Martins James’ AND Age = 25;

2. Multi-column UPDATE: Updates multiple columns.

Example

UPDATE [Student_Info]

 SET [Course]

=Case

WHEN Last name = ‘James’ THEN ‘Data Analytics’

WHEN Last name = ‘Monday’ THEN ‘Business Analytic’

WHEN Last name = ‘Oboh’ THEN ‘Data Science’

WHEN Lat name =’Ose’ THEN ‘Database Admin’

   ELSE NULL

    END;

 DELETE STATEMENT

Deleting a column in SQL is one of the most common operations when dealing with a database. For this reason, knowing how to properly delete a column in SQL is critical. Without the right procedures and precautions, you could run into data integrity and data loss issues.

What Does it Mean to Delete a Column in SQL?

In SQL, deleting a column refers to the process of permanently removing a specific column from a table. All data associated with the column will be removed from the disk. Similarly, the column’s metadata will be removed from the table’s schema. When you delete a column, you essentially eliminate its existence within the table structure. The column will be no longer available for queries and its data will be lost.

Alter table [table Name]

Drop Column [column name]

Example

ALTER TABLE [dbo].[Student_information]

DROP COLUMN State

Can use one Query to delete multiple columns

Alter Table [Table Name]

Drop Column [Column 1 Name]

Drop Column [column 2 Name]

Drop Column [Column 3 Name]

HOW TO UPDAT SINGLE INFO FROM A COLUMN IN A TABLE WITHOUT AFFECTING OTHER INFO

UPDATE [table name]

SET [table name].the Header of the line you want to replace = new info

WHERE [table name].header = ‘Unique identifier’

Example

UPDATE [dbo].[Pupils_information]

SET [dbo].[Pupils_information].Age = 11

WHERE [dbo].[Pupils_information].Name = ‘Martins Ogbodo’

TO DELETE DUPLICATE

WITH DUPLICATE_CTE AS (

SELECT Name, phone, Age, Email, State, Religion,

ROW_NUMBER () OVER (PARTITION BY Name, phone, Age, Email, State, Religion ORDER BY NAME) AS DUPLICOUNT

FROM [dbo].[Employee]

)

DELETE FROM DUPLICATE_CTE

WHERE DUPLICOUNT>1

Highlight and execute