Differentiate between the following commands: a) ALTER and UPDATE b) DELETE and DROP

Exercises · Q7

Q. Differentiate between the following commands:

a) ALTER and UPDATE

b) DELETE and DROP

West Bengal WbchseTextbookSubjective· 3mImportance★★★★★est

73% · 33/45 Questions

🔒 Locked · start free trial →

You're viewing a preview — the full solution, concept, methods & PYQ mapping are locked.

Start your 14-day free trial to unlock the full solution →

Concept understanding — SQL Clause Functions

SQL Clause Functions — The Intuition First

Imagine you're sorting through a pile of exam answer sheets. You want to find the top 3 scores, then group them by city, and finally show only those cities where the average score is above 80. Each of these steps — sorting, grouping, filtering after grouping — is done by a clause in SQL. A clause is just a keyword that tells the database how to process the rows.

Now, a clause function is a built-in operation that works inside a clause to transform or summarise data. The most common ones are aggregate functions (like SUM, AVG, COUNT, MAX, MIN) and scalar functions (like UPPER, LENGTH, ROUND). But the key idea is this: they don't stand alone — they are used within a clause to produce a single value from many rows, or to modify each row individually.

Note

A clause function is not a clause itself. It's a tool you place inside SELECT, WHERE, HAVING, or ORDER BY to compute something.

The Precise Statement

A SQL clause function is a predefined routine that accepts input (one or more values from rows) and returns a single output value, used inside a SQL clause to perform calculations, transformations, or aggregations on data.

There are two broad families:

Family	Purpose	Example	Where used
Aggregate functions	Combine many rows into one result	`AVG(marks)`	`SELECT`, `HAVING`
Scalar functions	Operate on each row individually	`UPPER(name)`	`SELECT`, `WHERE`, `ORDER BY`

How They Work in Practice

Consider a table Students:

name	city	marks
Ravi	Delhi	85
Priya	Mumbai	92
Anil	Delhi	78
Sita	Mumbai	88

Example 1 — Aggregate in SELECT

SELECT AVG(marks) FROM Students;

AVG is the clause function. It looks at all marks values, adds them, divides by the count, and returns 85.75. The SELECT clause simply displays that single number.

Example 2 — Scalar in WHERE

SELECT name FROM Students WHERE LENGTH(name) > 4;
``` …

Start your 14-day free trial to unlock the full solution →

Structure vs data is the whole story:

ALTER TABLE student ADD email VARCHAR(50);         -- DDL: changes STRUCTURE
UPDATE student SET class = 12 WHERE rollno = 101;  -- DML: changes DATA
DELETE FROM student WHERE rollno = 101;            -- DML: removes ROWS, table stays
DROP TABLE student;                                -- DDL: removes the TABLE itself …

Start your 14-day free trial to unlock the full solution →

Both pairs split along the same fault line — structure vs data. ALTER (DDL) changes a table's structure while UPDATE (DML) changes the data inside it; DROP (DDL) removes the entire table, definition and all, while DELETE (DML) removes only rows, leaving the empty table standing.

a) ALTER vs UPDATE

Basis	ALTER TABLE	UPDATE
Category	DDL (Data Definition Language)	DML (Data Manipulation Language)
Acts on	The table's structure (schema)	The data in existing rows
Typical uses	Add/drop/rename a column, change a data type, add a constraint	Change the values stored in one or more rows
Needs the table's data to exist?	No — works even on an empty table	Yes — it modifies existing rows

Seen side by side on the same table:

ALTER TABLE student ADD email VARCHAR(50);        -- structure: a NEW column appears
UPDATE student SET class = 12 WHERE rollno = 101; -- data: one existing value changes

After the ALTER, every row has a new (initially NULL) email column — the shape of the table changed. After the UPDATE, the shape is untouched; only the class value of row 101 changed. The WHERE clause is what confines UPDATE to specific rows — omit it and all rows are updated.

b) DELETE vs DROP

Basis	DELETE	DROP
Category	DML	DDL
Removes	Rows (some or all, via `WHERE`)	The whole object — table definition and all its data (or an entire database)
Table structure afterwards	Still exists (can be empty)	Gone — querying it errors
Can target specific rows?	Yes — `WHERE` clause	No — all-or-nothing

DELETE FROM student WHERE rollno = 101;  -- removes one row; table remains
DELETE FROM student;                     -- removes ALL rows; empty table remains
DROP TABLE student;                      -- removes the table itself
``` …

Start your 14-day free trial to unlock the full solution →

←Previous questionQ6 — Using the sports database containing two relations (TEAM, MATCHDETAILS…→Next questionQ8 — Create a database called STUDENTPROJECT having the following tables. C…

Q.Differentiate between the following commands:

a) ALTER vs UPDATE

b) DELETE vs DROP

🎓Unlock everything free for 14 days

Unlock everything free for 14 days