Lec-56: Difference between Alter and Update in SQL with examples in Hindi | DBMS

Summary

In this video, the presenter explains the key differences between the SQL commands ALTER and UPDATE. ALTER is a Data Definition Language (DDL) command used to modify the structure of a table, such as adding or removing columns, changing data types, or renaming columns or tables. UPDATE, on the other hand, is a Data Manipulation Language (DML) command used to modify data within a table, such as changing values in specific rows based on conditions. The video highlights the importance of understanding these commands for exams, job placements, and Oracle certification.

Takeaways

• 😀 `ALTER` is a Data Definition Language (DDL) command used to modify the structure of a database table.
• 😀 `UPDATE` is a Data Manipulation Language (DML) command used to modify the data within a table, not its structure.
• 😀 The `ALTER` command allows you to add, drop, or modify columns in an existing table.
• 😀 You can change the datatype of a column using the `ALTER` command, for example, changing an `int` column to `varchar`.
• 😀 With `ALTER`, you can change the size of a column's data type, such as increasing the size of a `varchar` from 10 to 20 characters.
• 😀 `UPDATE` can modify specific data in a table, such as changing the value of the `salary` column for certain rows.
• 😀 The `UPDATE` command can be used with a `WHERE` clause to target specific rows, such as updating the salary for only one employee.
• 😀 `ALTER` can rename columns or tables, allowing changes to the schema without affecting the data.
• 😀 Understanding the difference between `ALTER` and `UPDATE` is important for competitive exams, university exams, placements, and certifications like Oracle's OCE.
• 😀 The commands discussed in the video are written with Oracle SQL syntax in mind, but the logic applies to other SQL variants like MySQL and SQL Server, though syntax may differ.

Q & A

What is the primary difference between 'ALTER' and 'UPDATE' commands in SQL?

The 'ALTER' command is used to modify the structure of a table (e.g., adding or removing columns), while the 'UPDATE' command is used to modify the data within the table (e.g., changing the values in rows).

Which SQL command is used to modify the structure of a table?

'ALTER' is the SQL command used to modify the structure of a table, such as adding, deleting, or changing columns or their data types.

What is the role of 'UPDATE' in SQL?

'UPDATE' is used to modify the data in a table. For example, it can change the values of specific rows based on a given condition.

Can you change the data type of a column using 'UPDATE'?

No, 'UPDATE' only modifies data values. To change the data type of a column, the 'ALTER' command must be used.

What is a common use case for the 'ALTER' command?

A common use case for 'ALTER' is when you need to add a new column to an existing table, remove an unnecessary column, or change the size or data type of an existing column.

How would you add a new column named 'Email' to an existing table using 'ALTER'?

You would use the 'ALTER' command like this: 'ALTER TABLE table_name ADD COLUMN Email VARCHAR(255);'. This adds a new 'Email' column to the table.

What happens if you execute the 'UPDATE' command without a 'WHERE' clause?

If you execute an 'UPDATE' command without a 'WHERE' clause, it will update the specified column for all rows in the table, not just a specific row or subset.

Can you change the name of a column or a table using 'ALTER'?

Yes, you can change the name of a column or a table using the 'ALTER' command. For example, you can use 'ALTER TABLE table_name RENAME COLUMN old_name TO new_name;' to rename a column.

What does the 'UPDATE' command look like if you want to double the salary of an employee with 'Id' 1?

The 'UPDATE' command would be: 'UPDATE employee SET salary = salary * 2 WHERE Id = 1;'. This doubles the salary for the employee with 'Id' 1.

In which scenarios would you use the 'ALTER' command instead of 'UPDATE'?

You would use 'ALTER' when modifying the structure of the table, such as adding or removing columns, or changing their data types. 'UPDATE' would be used when changing the values of the data itself, like modifying a salary or name.