SQL Data Manipulation: INSERT, UPDATE and DELETE

Hello students, welcome to Kwickprep. A teacher wanted to give five grace marks to one student, but every student got them. What went wrong? Today we will add rows with insert, change them with update, remove them with delete, and check every change with select.

We will work on this student table, with three columns: roll, name and marks. Riya has roll one and eighty eight marks. Aman has roll two and seventy two marks. Neha has roll three and sixty five marks. These commands change rows, so they belong to D M L, data manipulation language.

Insert into adds a new row. First we write the table name, and then the column names in brackets. After the word values, we give the values in the same order as those columns. So roll is four, name is Kabir, and marks are sixty seven. Text goes in single quotes, and numbers do not.

We can also leave out the column names. Then we must give a value for every column, in the exact order the table was created. Here, five goes to roll, Meera goes to name, and ninety one goes to marks. If you give only two values, My S Q L gives an error, because the count does not match.

So which form should you use? With column names, you can write the columns in any order and skip some of them. A skipped column gets its default value, or null if it has no default. Without column names, you must fill every column, in the order of the table. In both forms, text and dates go inside single quotes.

Never assume a command worked, always check. Select star from student shows every row. Kabir and Meera are now at the bottom, so both inserts worked. The table now has five rows.

Aman's paper was rechecked, and his marks are now eighty. Update changes values in rows that already exist. Set marks equals eighty says what to change. Where roll equals two says which rows to change. In S Q L, one equals sign after where compares values, unlike double equals in Python. The select shows Aman now has eighty.

Pause and predict. The teacher meant to add five grace marks for Neha only, but forgot the where clause. What happens? Without where, update changes every single row. All five students get five extra marks, and Aman's eighty becomes eighty five. This is the mistake from the start of the lesson.

Remember these rules to stay safe. Update without where changes every row in the table. My S Q L saves each change at once by default, so there is no simple undo. Before an update, run a select with the same where, and check it shows only the rows you want. Use the primary key, like roll, in the where, because it matches exactly one row.

Neha has left the school, so we remove her row. Delete from student where roll equals three removes only that row. Notice that delete has no column list, because it always removes whole rows. The select shows four rows, and roll three is gone.

Delete follows the same where rule as update. With where, it removes only the matching rows. Without where, it removes every row in the table. Even then, the table itself stays, empty but ready for new rows. Drop table, which is a D D L command, removes the table and its structure completely.

Here is a habit that professionals follow every day. First, run a select with the same where clause. Ask yourself, does it show only the rows you want to change? If yes, run the update or delete. If no, fix the where clause before doing anything. Finally, run select again to verify the change.

Let us revise what we learned today. Insert into adds rows, with column names or with a value for every column. Update, set and where change only the rows you choose. Forget where, and every row is updated or deleted. Delete from with where removes the chosen rows. And always use select before and after, to verify the change.