Computer Knowledge

Database and SQL

3,929 Questions

Master structured query language commands, database joins, table constraints, and alias generation. This section covers relational database management concepts and query outputs essential for technical aptitude. These technical questions feature prominently in banking IT officer exams and computer knowledge sections.

SQL queries and aliasesDatabase table constraintsDatabase joins and transformationsStored procedures and functions

Database and SQL Questions

Multiple choice technology databases

How many rows will the following SQL return : "Select * from emp Where rownum = 10" ?

  1. 10th row

  2. 1st row

  3. no rows returned

  4. First 10 rows
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

To solve this question, the user needs to have basic knowledge of SQL and the concept of the rownum keyword in SQL.

The rownum keyword is used to assign a unique number to each row returned by a query in Oracle. It is often used to limit the number of rows returned by a query.

Now, let's go through each option and explain why it is right or wrong:

A. 10th row: This option is incorrect. The rownum keyword assigns a unique number to each row returned by a query. However, the where clause in the given SQL limits the result set to only the row where rownum equals 10. This means that the SQL query will only return one row, and that row will be the 10th row in the result set.

B. 1st row: This option is incorrect. The where clause in the given SQL limits the result set to only the row where rownum equals 10. This means that the SQL query will only return one row, and that row will be the 10th row in the result set.

C. no rows returned: This option is incorrect. The SQL query will return one row, which is the 10th row in the result set.

D. First 10 rows: This option is incorrect. The where clause in the given SQL limits the result set to only the row where rownum equals 10. This means that the SQL query will only return one row, and that row will be the 10th row in the result set.

The Answer is: C

Multiple choice technology databases

Assuming today is Monday, 10 July 2000, what is returned by this statement: SELECT to_char(NEXT_DAY(sysdate, 'MONDAY'), 'DD-MON-RR') FROM dual?

  1. 10-JUL-00

  2. 12-JUL-00

  3. 11-JUL-00

  4. 14-JUL-00

  5. 17-JUL-00
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

NEXT_DAY finds the first occurrence of the specified day after the given date. Starting from Monday July 10, the next Monday is July 17. The format mask 'DD-MON-RR' gives 17-JUL-00. The other options are incorrect dates.

Multiple choice technology databases

Which of the following is not correct about a View ?

  1. To protect some of the columns of a table from other users

  2. Ocuupies data storage space

  3. To hide complexity of a query

  4. To hide complexity of a calculations
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

A view is a virtual table that does not occupy physical storage space - it stores only the query definition. Options A, C, and D describe legitimate uses of views: column security, simplifying complex queries, and hiding computational logic. Therefore, the statement that views occupy data storage space is the INCORRECT statement, making B the right choice.

Multiple choice technology mainframe

The keyword OPTIONAL in select clause is used only for

  1. Output files

  2. Input files

  3. Both the files

  4. All the above is correct
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

OPTIONAL specifies that a file may not be present at job execution time. For input files (READ mode), this prevents ABEND if the file is missing. Output files with optional status would create runtime errors if referenced but absent.

Multiple choice technology mainframe

SQL can

  1. Create Database

  2. Update Table

  3. Drop Views

  4. Reverse Table
Reveal answer Fill a bubble to check yourself
A,B,C Correct answer
Explanation

SQL is a comprehensive language that can create databases, update tables with INSERT/UPDATE, and drop views with DROP VIEW. However, SQL has no 'REVERSE TABLE' command - reversing table order is done through queries with ORDER BY, not a DDL operation.

Multiple choice technology mainframe

Which SQL statement is used to return only different values?

  1. SELECT DISTINCT

  2. SELECT DIFFERENT

  3. SELECT UNIQUE

  4. SELECT SAME
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The SELECT DISTINCT statement is the standard SQL syntax used to return only unique (different) values from a query, filtering out duplicate rows. SELECT UNIQUE is an older, non-standard synonym supported by some databases, but DISTINCT is the primary standard keyword.

Multiple choice technology databases

CREATE OR REPLACE FUNCTION CALC_PLAYER_AVG (V_ID in PLAYER_BAT_STAT.PLAYER_ID%TYPE) RETURN NUMBER IS V_AVG NUMBER; BEGIN SELECT HITS / AT_BATS INTO V_AVG FROM PLAYER_BAT_STAT WHERE PLAYER_ID = V_ID; RETURN (V_AVG); END; Which statement will successfully invoke this function in SQL *Plus

  1. SELECT CALC_PLAYER_AVG(PLAYER_ID) FROM PLAYER_BAT_STAT;

  2. EXECUTE CALC_PLAYER_AVG(31);

  3. CALC_PLAYER(‘RUTH’);

  4. CALC_PLAYER_AVG(31);
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Functions in Oracle/PLSQL must be invoked within a SQL statement like SELECT. Option A correctly embeds the function call in a SELECT statement, passing a column from the table. EXECUTE (B) is for procedures, not functions. Option C uses wrong function name. Option D lacks required SELECT wrapper - plain function calls aren't valid SQL.

Multiple choice technology databases

Which is the true statements about dependent objects?

  1. An object with status of invalid cannot be a referenced object.

  2. The Oracle server automatically records dependencies among objects.

  3. All schema objects have a status that is recorded in the data dictionary.

  4. You can view whether an object is valid or invalid in the USER_STATUS data dictionary view.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Oracle automatically tracks dependency relationships between schema objects (e.g., a view depends on its base table). When a referenced object changes, dependent objects are marked invalid. Option A is false - invalid objects CAN be referenced (they're just marked invalid). Option C is false - not all objects have status. Option D is false - the view is USER_OBJECTS, not USER_STATUS.

Multiple choice technology databases

You have created a stored procedure DELETE_TEMP_TABLE that uses dynamic SQL to remove a table in your schema. You have granted the EXECUTE privilege to user A on this procedure. When user A executes the DELETE_TEMP_TABLE procedure, under whose privileges are the operations performed by default?

  1. SYS privileges

  2. Your privileges

  3. Public privileges

  4. User A’s privileges
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

By default, stored procedures in Oracle execute with the privileges of the owner (definer's rights), not the invoker (user A). Therefore, the dynamic SQL operations run under the creator's/owner's privileges.

Multiple choice technology databases

CREATE OR REPLACE PRODECURE add_dept (p_dept_name VARCHAR2 DEFAULT ‘placeholder’, p_location VARCHAR2 DEFAULT ‘Boston’) IS BEGIN INSERT INTO departments VALUES (dept_id_seq.NEXTVAL, p_dept_name, p_location); END add_dept; / Which is invalid call to the add_dep procedure? (Choose three

  1. add_dept;

  2. add_dept(‘Accounting’);

  3. add_dept(, ‘New York’);

  4. add_dept(p_location=>’New York’);
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

PL/SQL does not support positional parameters with missing arguments like add_dept(, 'New York'). The stored answer correctly identifies this invalid call, but the question contains a typo ('Choose three' when only one option is correct or three are valid) and misspells the procedure name. However, since the option marked correct is indeed the only invalid call among the choices, the answer is valid.

Multiple choice technology web technology

Which statement about packages is true?

  1. Packages can be nested.

  2. You can pass parameters to packages.

  3. A package is loaded into memory each time it is invoked.

  4. The contents of packages can be shared by many applications.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

PL/SQL packages are stored database objects whose specifications and bodies can be shared across multiple applications. Packages cannot be nested, cannot accept parameters directly (though their subprograms can), and are loaded into memory once per session rather than on every invocation.