C1 : drop table R;
C2 : delete from R;
Which of the following statements is TRUE ?
I. Both C1 and C2 delete the schema for R.
II. C2 retains relation R, but deletes all tuples in R.
III. C1 deletes not only all tuples of R, but also the schema for R.
SQL Command Analysis: DROP vs DELETE
This solution analyzes the impact of two SQL commands, $C1: DROP TABLE R;$ and $C2: DELETE FROM R;$, on a database relation $R$.
Understanding C1: $DROP TABLE R;$
- $DROP TABLE R;$ completely removes the table named $R$ from the database.
- This action deletes both the table's structure (schema) and all the data (tuples) it contains.
- The table $R$ ceases to exist after this command.
Understanding C2: $DELETE FROM R;$
- $DELETE FROM R;$ removes all the rows (tuples) from the table $R$.
- However, this command leaves the table's structure (schema) intact.
- The table $R$ still exists but becomes empty.
Evaluating Statement Truthfulness
-
Statement I: Both C1 and C2 delete the schema for R.
- C1 deletes the schema.
- C2 does not delete the schema.
- Therefore, Statement I is False.
-
Statement II: C2 retains relation R, but deletes all tuples in R.
- C2 deletes all tuples.
- C2 retains the relation R (its schema).
- Therefore, Statement II is True.
-
Statement III: C1 deletes not only all tuples of R, but also the schema for R.
- C1 deletes all tuples.
- C1 deletes the schema for R.
- Therefore, Statement III is True.
Conclusion on Statements
Based on the analysis, statements II and III are true.
Important Questions from SQL
Consider a relation book (title, price) which contains the titles and prices of different books.
Assuming that no two books have the same price, what does the following SQL query list ?
Select title
from book as B
where (select count (*)
from book as T
where T.price > B.price) < 7Match the following -
List I List II (a) DDL (i) LOCK TABLE (b) DML (ii) COMMIT (c) TCL (iii) Natural Difference (d) Binary operation (iv) REVOKE Given the following STUDENT‐COURSE scheme : STUDENT (Rollno, Name, courseno) COURSE (courseno, coursename, capacity), where Rollno is the primary key of relation STUDENT and courseno is the primary key of relation COURSE. Attribute coursename of COURSE takes unique values only. Which of the following query(ies) will find total number of students enrolled in each course, along with its coursename.
A. SELECT coursename, count(*) 'total' from STUDENT natural join COURSE group by coursename;
B. SELECT C.coursename, count(*) 'total' from STUDENT S, COURSE C where S.courseno = C.courseno group by coursename;
C. SELECT coursename, count(*) 'total' from COURSE C where courseno in (SELECT courseno from STUDENT);
Which of the following is used to create a database schema?
Which of the component module of DBMS does rearrangement and possible ordering of operations, eliminate redundancy in query and use efficient algorithms and indexes during the execution of a query?
