Match 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
Understanding Database Command Categories and Operations
This question asks us to match different types of database commands and operations with specific examples. Let's understand each category and example provided in the lists.
List I: Categories and Operation Type
- DDL (Data Definition Language): These commands are used to define, modify, or drop database objects like tables, indexes, users, and permissions. They deal with the structure or schema of the database.
- DML (Data Manipulation Language): These commands are used for managing data within the database objects. They are used to insert, update, delete, or retrieve data.
- TCL (Transaction Control Language): These commands are used to manage transactions in the database. A transaction is a sequence of database operations performed as a single logical unit of work. TCL commands ensure data integrity.
- Binary operation (Relational Algebra): In relational algebra, binary operations are operations that take two relations (tables) as input and produce a new relation.
List II: Specific Commands and Operations
- LOCK TABLE: A command used to explicitly acquire a lock on a table. This is typically used to control concurrent access to data within a table, often classified under DML or a utility related to DML.
- COMMIT: A TCL command that saves all changes made during the current transaction permanently to the database.
- Natural Difference: A binary operation in relational algebra that returns the tuples that are in the first relation but not in the second relation.
- REVOKE: A DDL command used to remove previously granted permissions (privileges) from a user or role.
Matching the Lists
Now let's match the categories from List I with the specific examples from List II based on their functions:
- (a) DDL deals with database structure and permissions. From List II, REVOKE is used to remove permissions, which falls under DDL. So, (a) matches with (iv).
- (b) DML deals with manipulating data or controlling access for data manipulation. LOCK TABLE controls access to data in a table, often considered part of DML or related utilities. So, (b) matches with (i).
- (c) TCL deals with managing transactions. COMMIT is a core TCL command used to finalize transactions. So, (c) matches with (ii).
- (d) Binary operation is an operation that takes two relations. Natural Difference is a well-known binary operation in relational algebra. So, (d) matches with (iii).
Summary of Matching
| List I | List II | Match |
|---|---|---|
| (a) DDL | (i) LOCK TABLE | (a) - (iv) |
| (b) DML | (ii) COMMIT | (b) - (i) |
| (c) TCL | (iii) Natural Difference | (c) - (ii) |
| (d) Binary operation | (iv) REVOKE | (d) - (iii) |
The correct matching is a - (iv), b - (i), c - (ii), d - (iii).
Revision Table: Database Commands & Operations
| Category | Purpose | Example from List II | Other Examples |
|---|---|---|---|
| DDL (Data Definition Language) | Define/modify database structure, permissions | REVOKE | CREATE, ALTER, DROP, GRANT |
| DML (Data Manipulation Language) | Manage data within objects | LOCK TABLE | SELECT, INSERT, UPDATE, DELETE |
| TCL (Transaction Control Language) | Manage transactions | COMMIT | ROLLBACK, SAVEPOINT |
| Binary Relational Operation | Combine two relations | Natural Difference | Union (\( \cup \)), Intersection (\( \cap \)), Cartesian Product (\( \times \)) |
Additional Information: Relational Algebra and SQL
Relational algebra is a procedural query language used in database systems. It provides a set of operations to manipulate relations (tables) and produce new relations. These operations can be unary (acting on one relation) or binary (acting on two relations).
SQL (Structured Query Language) is the standard language for managing relational databases. While SQL has commands that correspond to relational algebra concepts, it also includes commands for data definition, transaction control, and other database management tasks, categorized into DDL, DML, and TCL.
- SQL commands like
CREATE TABLE,ALTER TABLE,DROP TABLE,GRANT, andREVOKEare DDL. - SQL commands like
SELECT,INSERT,UPDATE, andDELETEare DML. - SQL commands like
COMMIT,ROLLBACK, andSAVEPOINTare TCL. - Relational algebra operations like Union, Intersection, Difference, and various forms of Join are implemented in SQL, typically within DML statements (e.g., using
UNION,EXCEPT/MINUS,JOINclauses).
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) < 7Given 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?
Which of the following statements are DML statements?
(a) Update [tablename] Set [columnname] = VALUE
(b) Delete [tablename]
(c) Select * from [tablename]
