CS Free MCQ Bank

[ Option C ]

A Query Language allows users to specify what data they want rather than how the data should be retrieved. 
For example, in SQL, a user can write a query to fetch required records without worrying about the internal procedures used by the DBMS to access the data. This makes query languages Non-Procedural and easy to use.

[ Option A ]

SQL Injection is a malicious technique in which attackers provide crafted input to SQL queries, tricking the database into executing unintended commands. This can lead to unauthorized data access, modification, or deletion. It is a major security concern for databases connected to web applications.

[ Option B ]

In SQL, the REVOKE command is used to remove privileges that were previously granted to a user.

• The GRANT is used to give privileges.
• The DELETE is used to remove rows from a table.

[ Option A ]

In SQL, the DELETE statement removes rows that satisfy the WHERE condition. If the WHERE clause is omitted, there is no condition to restrict the deletion, so all rows in the table are deleted. The table structure remains intact.
The SQL command DELETE FROM Student; deletes every record from the Student table.

[ Option C ]

A Trigger in SQL is a set of actions automatically executed by the database in response to a specific event, such as an INSERT, UPDATE, or DELETE on a table. Triggers are often used for tasks like maintaining audit logs, enforcing business rules, or validating data automatically.

The action part of a trigger (the code that gets executed) can be performed in two ways:
Row-Level Trigger: The trigger is executed once for every row (tuple) that is modified by the operation.
Statement-Level Trigger: The trigger is executed only once, regardless of how many tuples are affected by the operation.

[ Option D ]

The error in this SQL statement occurs because the FROM clause is missing, which is required to specify the table from which data should be retrieved. A correct SELECT query must always include the FROM keyword followed by the table name. Additionally, if a column name contains spaces (for example, last name), it should be enclosed in quotes or brackets to avoid syntax errors.

[ Option C ]

A View is a virtual table created using a SELECT query. It allows users to see only specific columns or rows of a table while hiding the remaining data. This is commonly used for data security and access control.
E.g.:
CREATE VIEW Employee_View AS
SELECT Empid, Name
FROM Employee;
Here, users can see only Empid and Name, while other sensitive columns like salary,   designation, or address remain hidden.

[ Option D ]

In embedded SQL, we write SQL statements inside a host programming language like C, Java, or COBOL. When we want to fetch data from a table and store it directly into host variables, we use the SELECT INTO statement.
The SELECT is used to read data from a table.
The INTO is used to store the fetched values into host language variables.
EXEC SQL SELECT column_name INTO :host_name FROM table_name WHERE condition;

E.g.:
Host variables in C:
char student_name[50];
int student_marks;
Embedded SQL SELECT INTO:
EXEC SQL SELECT name, marks
     INTO :student_name, :student_marks
     FROM Student
     WHERE roll_no=101;

[ Option B ]

SQL (Structured Query Language) is a Non-Procedural or Declarative language, which means the user specifies what data is required, not how to retrieve it. The DBMS decides the best way to execute the query.

[ Option C ]

Schema change statements in SQL are used to modify the structure of existing database objects like tables, columns, or constraints. These changes can include adding or removing tables, columns, or other schema elements. The most common schema change statements are DROP and ALTER.

• The DROP statement is used to delete schema elements, such as tables or columns, from the database. It can remove an entire schema or individual database objects.
• The ALTER statement is used to modify the structure of existing objects, like adding or dropping columns, modifying column data types, or renaming columns. ALTER allows database schemas to evolve over time without losing data.

[ Option C ]

A view in a database is a virtual table that represents the data retrieved by a stored SQL query from one or more base tables. 

Unlike base tables and indexes, which physically exist and store data on disk, a view does not store data itself. Instead, it holds only the SQL query defining it. 

When a user queries the view, the database engine executes the stored query dynamically on the underlying base tables to fetch the current data. This allows views to provide a customized and simplified representation of data, enforce security by controlling data access, and reduce complexity without duplicating storage. 

Views serve as logical abstractions that help organize and present data efficiently without physically occupying storage space.

[ Option C ]

An aggregate function in SQL performs a calculation on a set of values and returns a single summarized result. These functions are commonly used to analyze and summarize data, especially when combined with the GROUP BY clause. The aggregate functions include:

• COUNT, which counts the number of rows.
• SUM, which adds up numeric values.
• AVG, which calculates the average.
• MIN, which find the smallest value.
• MAX, which find the largest value.

Aggregate functions help in generating reports, insights, and statistical summaries from large datasets efficiently.

[ Option A ]

The goal is to insert into the Studio table the names of all studios that appear in the Movie relation but are not already present in Studio.
To insert into the relation Studio all studios that appear in the Movie relation but are not already present, we use the INSERT INTO ... SELECT statement. The correct query is:
INSERT INTO Studio(name)
SELECT DISTINCT studioName
FROM Movie
WHERE studioName NOT IN (SELECT name FROM Studio);
This query selects all unique studio names from the Movie table and inserts them into the Studio table, but only if they do not already exist in Studio. The DISTINCT keyword ensures that duplicate names are not inserted, and the NOT IN condition prevents inserting studios already present.

[ Option D ]

In SQL, aggregate functions generally ignore NULL values while performing calculations.

• MAX returns the maximum value, ignoring NULLs.
• SUM adds only non-NULL numeric values.
• COUNT(colname) counts only non-NULL values but COUNT(*) counts all rows.

[ Option A ]

In SQL, the LIKE operator is used with wildcard characters to match patterns in string data. The two main wildcards are:

In the given query, we need to find customer names where the second character is ‘P’ and the name ends with ‘A’.

• To make the second character ‘P’, we use an underscore (_) for the first character.
• Then, to allow any number of characters between ‘P’ and the ending ‘A’, we use the percent sign (%).
• Finally, to ensure the name ends with ‘A’, we write A at the end.

Thus, the correct pattern is '_P%A', which means, any first character (_), followed by ‘P’, then any number of characters (%), and ending with ‘A’.

[ Option D ]

In SQL, commands are categorized into DDL, DML, DQL, DCL and TCL based on their purpose.

[ Option B ]

In SQL, the LIKE keyword is used to compare string values based on patterns using wildcard characters such as % (represents any number of characters) and _ (represents a single character).
E.g.:
SELECT * FROM Employee
WHERE name LIKE 'S%';
This query selects all Employee whose names start with the letter S.

[ Option D ]

In a database, an Assertion is a logical condition that defines a rule or constraint which must always be satisfied by the data in the database. It ensures the consistency and correctness of stored data. 

Common examples of assertions include domain constraints, which restrict the permissible values of an attribute, and referential integrity constraints, which ensure valid relationships between tables.

[ Option B ]

In SQL, removing data from a table can be done using different commands, but each serves a different purpose. The DROP command deletes the entire table (with all its data and structure), while TRUNCATE removes all rows quickly but keeps the table structure.

To remove a specific tuple or row from a table, the DELETE statement is used with a WHERE clause to target the exact tuple. If no condition is given (in WHERE clause), all rows are deleted but the table structure remains. This ensures that only the intended row is deleted without affecting the rest of the table.

A Database Object is any structure or entity in a database that is created to store, manage, or manipulate data. Examples of database objects include tables, views, indexes, sequences, triggers, procedures, functions, etc.

[ Option B ]

The schema of a database defines the structure of the database, including tables, columns, data types, and constraints. To create, modify, or delete this structure, Data Definition Language (DDL) is used in DBMS. 
The DDL commands such as create, alter, and drop define and manage the database schema.
 

[ Option D ]

[ Option C ]

Set operations in SQL are used to combine or compare the results of two SELECT queries. Common set operations include UNION, INTERSECT, and MINUS or EXCEPT.
The LIKE is a pattern-matching operator used in the WHERE clause to filter rows based on string patterns.

[ Option C ]

A table function is a user-defined function that returns a table and can be used within a query’s FROM clause, allowing it to behave like a regular table. 
Similarly, stored procedures are invoked using the CALL statement, which is the standard method for executing procedures in SQL. Therefore, both statements I and II are correct.

[ Option B ]

In SQL, the COUNT() function is an aggregate function used to determine the number of records in a table. It can be used in different forms, such as COUNT(*), COUNT(Column_Name), and COUNT(DISTINCT Column_Name).
When we use COUNT(*), it counts all rows in the specified table, regardless of whether any column contains a NULL value. This means it simply counts the total number of entries present.
In the query SELECT COUNT(*) FROM employees;, the database counts all the rows in the employees table, including those having NULL values in one or more columns.

[ Option A ]

In SQL, the operation used to define automatic actions that the database system should perform when specific events occur (such as INSERT, UPDATE, or DELETE) is called Trigger. A trigger is a stored program that executes automatically whenever a specified event happens on a table.
Triggers help in maintaining data integrity, enforcing business rules, automating tasks like auditing changes, and validating data before or after modification. They run without explicit invocation by users, ensuring consistent and automatic enforcement of rules defined at the database level.

[ Option B ]

Integrity constraints are rules that ensure the accuracy, consistency, and validity of data in a database, such as Primary Key, Foreign Key, Unique, Not Null, and Check constraints. 
These constraints are defined when creating or modifying the structure of database tables. In DBMS, Data Definition Language (DDL) is used to define the database schema, including tables and their constraints, using commands like Create and Alter.

[ Option A ]

In a database system, controlling who can access a table and what operations they are allowed to perform is an important security feature. This task is handled by Data Control Language (DCL).
DCL commands are used to permit or prohibit access to database objects such as tables, views, and databases. Common DCL commands include GRANT, which gives access permissions, and REVOKE, which removes them.

[ Option A ]

Both queries aim to find the department(s) with the maximum total credits from the course relation. 
Query Q1 uses a nested subquery with ANY and MAX to select the department(s) whose total credits equal the maximum total credits. 
Query Q2 uses >= ALL on a subquery that computes the total credits per department, which also selects department(s) having the highest total credits.

• Q1 uses = ANY (max(...)) which evaluates to max value.
• Q2 uses >= ALL (...) which also evaluates to max value.

[ Option C ]

In SQL, a column defined as NUMERIC(3, 1) can store a total of three digits, out of which one digit is reserved for the decimal part and the remaining two for the integer part. Hence, valid numbers range approximately from –99.9 to 99.9. Among the given options, only 44.5 satisfies these conditions.
So, in SQL,

• p (Precision) : Total number of digits (both left and right of the decimal).
• s  (Scale) : Number of digits to the right of the decimal point.

So, for NUMERIC(3, 1):

• Total digits allowed : 3
• Digits after decimal : 1
• Therefore, digits before decimal = 3 − 1 = 2
• Thus, the maximum value that can be stored is 99.9, and the minimum is -99.9.

[ Option A ]

In SQL (Structured Query Language), a view is a virtual table that is created based on the result of a query. It does not store data physically.
To create a view, we use the CREATE VIEW command followed by the view name and a SELECT query that determines the view's contents.

Syntax:

CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

[ Option A ]

In SQL, the DISTINCT keyword is used with the SELECT statement to remove duplicate rows from the query result.
E.g.:
SELECT DISTINCT City FROM Student;
This query returns each city only once, even if it appears multiple times in the table.

• LIKE is used for pattern matching.
• ORDER BY is used to sort the result set.
• DISTINCT ALL is not a valid SQL keyword.

[ Option A ]

The TO_DATE function in SQL is used to convert a character string into a DATE value using a specified format. This is necessary when the date or time does not match the database default date format.
E.g.:
INSERT INTO Employee (empid, joindate)
VALUES (101, TO_DATE('22-02-2023 12:15', 'DD-MM-YYYY HH24:MI'));

[ Option C ]

In SQL, the asterisk (*) symbol is used in the SELECT statement to indicate all columns of a table.
E.g.:
SELECT * FROM Student;
This query retrieves every column and all rows from the Student table.

[ Option A ]

In a Relational Database Management System (RDBMS), a materialized view is a database object that stores the results of a query physically. Unlike a regular view, which calculates data only when it is accessed, a materialized view keeps the data ready in advance, which helps speed up complex queries.

Since the original tables can change with INSERT, UPDATE, or DELETE operations, the data in a materialized view can become outdated. To keep it current automatically, RDBMS uses triggers, which are special procedures that run whenever the underlying table data changes, ensuring the materialized view stays up-to-date.

• A trigger is a type of procedural code in a database that runs automatically in response to specific events on a table or view, such as INSERT, UPDATE, or DELETE operations. 
• Triggers help enforce business rules, maintain data integrity, and automate repetitive tasks without manual intervention.
• In the context of materialized views, when the underlying table data changes, a trigger can automatically refresh or update the materialized view. This ensures that the view always reflects the latest data, keeping it consistent with the base tables.
   

[ Option A ]

In SQL, a column defined as NUMERIC(4, 1) can store a total of four digits, out of which one digit is reserved for the decimal part and the remaining three for the integer part. Hence, valid numbers range approximately from –999.9 to 999.9. Among the given options, only 444.5 satisfies these conditions.
So, in SQL,

• p (Precision) : Total number of digits (both left and right of the decimal).
• s  (Scale) : Number of digits to the right of the decimal point.

So, for NUMERIC(4, 1):

• Total digits allowed : 4
• Digits after decimal : 1
• Therefore, digits before decimal = 4 − 1 = 3
• Thus, the maximum value that can be stored is 999.9, and the minimum is -999.9.

[ Option C ]

In SQL, any arithmetic operation involving NULL results in NULL. The value NULL represents an unknown or missing value, so dividing a number by NULL produces an unknown result, which is returned as NULL.

[ Option D ]

Set operations are used in SQL to combine the results of two or more SELECT queries.

• UNION returns all distinct rows from both queries.
• INTERSECT returns only the common rows from both queries.
• EXCEPT or MINUS (in oracle) returns rows from the first query that are not present in the second query.

[ Option C ]

The SQL command DELETE FROM EMPLOYEE; without a WHERE clause will delete all tuples (rows) from the EMPLOYEE relation or table. It removes every record, but the table structure, columns, and schema remain intact.

• If there is no WHERE clause, all rows will be deleted.
• If a WHERE clause is used, only the rows satisfying the condition will be deleted.
• The table itself is not dropped; only the data inside gets deleted.

[ Option D ]

In SQL, the SELECT statement commonly uses conditions in the WHERE clause to filter records. The operators IN, BETWEEN, and LIKE are all used with SELECT to specify such conditions.

• IN checks whether a value matches any value in a list.
• BETWEEN selects values within a given range.
• LIKE is used for pattern matching with wildcards.

E.g.:
SELECT * FROM Student 
WHERE marks BETWEEN 75 AND 95 
  AND name LIKE 'S%' 
  AND class IN ('BCA', 'MCA');

[ Option C ]

In SQL (Structured Query Language), a view is a virtual table that is created based on the result of a query. It does not store data physically.
To create a view, we use the CREATE VIEW command followed by the view name and a SELECT query that determines the view's contents.
CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

[ Option D ]

The Data Manipulation Language (DML) is the part of SQL used to query and update data in a database.

[ Option A ]

In DBMS, NULL represents missing, unknown, or not applicable information. It does not mean zero, blank, or empty. It simply means that the value is not known or not provided.