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
Which of the following is NOT a Structured Query Language Data type?
Consider the following relational schema:
Students (rollno: integer, name: string, age: integer, cgpa: real)
Courses (courseno: integer, cname: string, credits: integer)
Enrolled (rollno: integer, courseno: integer, grade: string)
Which of the following options is/are correct SQL query/queries to retrieve the names of the students enrolled in course number (i.e., courseno) 1470?Consider a table Employee(EmpID, TeamID), where the column EmpID (ID of an employee) is the primary key. The column TeamID denotes the team ID of the team of which the employee is a member. TeamID is a NOT NULL column.
We want to display the size of the team (denoted as TeamSize) in which each employee is a member by using SQL. As an example, the desired output for the given Employee table is also shown in tabular form.Which of the following is/are correct?
Employee
EmpID TeamID 1 8 2 8 3 8 4 7 5 7 6 9
Output
EmpID TeamSize 1 3 2 3 3 3 4 2 5 2 6 1 Let Account be a relation as shown.
Account
AccNo Balance A1 5000 A2 5000 A3 10000 A4 15000 A5 18000 Consider the given SQL query.
SELECT AccNo FROM Account AS A
WHERE (SELECT COUNT(*) FROM Account AS B
WHERE A.Balance < B.Balance) >= (SELECT COUNT(*)
FROM Account AS C WHERE A.Balance > C.Balance)
The number of rows returned by the SQL query is __________ . (Answer in integer)
Consider the following two tables named Raider and Team in a relational database maintained by a Kabaddi league. The attribute ID in table Team references the primary key of the Raider table, ID.
Raider ID Name Raids RaidPoints 1 Arjun 200 250 2 Ankush 190 219 3 Sunil 150 200 4 Reza 150 190 5 Pratham 175 220 6 Gopal 193 215 Team City ID BidPoints Jaipur 2 200 Patna 3 195 Hyderabad 5 175 Jaipur 1 250 Patna 4 200 Jaipur 6 200 The SQL query described below is executed on this database:
SELECT *
FROM Raider, Team
WHERE Raider.ID=Team.ID AND City="Jaipur" AND
RaidPoints > 200;
The number of rows returned by this query is ________.
