Computer Knowledge

Database and SQL

4,213 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

From SQL*Plus, you issue this SELECT statement: SELECT* From orders; You use this statement to retrieve data from a data table for __________.

  1. updating

  2. viewing

  3. deleting

  4. inserting

  5. truncating
Reveal answer Fill a bubble to check yourself
B,D Correct answer
Explanation

A SELECT query retrieves data for viewing. In the context of Oracle SQL training, it is also noted that the retrieved data can be used directly for inserting into another table via INSERT INTO ... SELECT, making both options correct.

Multiple choice technology databases

Evaluate this SQL*Plus command: START delaccount Which task will this command accomplish?

  1. It executes the DELACCOUNT PL/SQL routine.

  2. It runs the DELACCOUNT.SQL script file.

  3. It creates the DELACCOUNT file using the default file extension.

  4. It invokes the editor to edit the contents of the DELACCOUNT file.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

To solve this question, the user needs to have some basic knowledge about SQL*Plus. This command starts a SQL*Plus script from the current directory.

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

A. It executes the DELACCOUNT PL/SQL routine. - This option is incorrect as START command is used to start a SQL script or a file and not a PL/SQL routine.

B. It runs the DELACCOUNT.SQL script file. - This option is correct as the START command is used to start a SQL script or a file. Here, the command START delaccount will run the DELACCOUNT.SQL script file.

C. It creates the DELACCOUNT file using the default file extension. - This option is incorrect as the START command is used to start an existing SQL script or file and not for creating a new file.

D. It invokes the editor to edit the contents of the DELACCOUNT file. - This option is incorrect as the START command is used to start an existing SQL script or file and not for invoking the editor to edit the contents of a file.

Therefore, the answer is:

The Answer is: B

Multiple choice technology databases

Which /SQL*Plus feature can be used to replace values in the WHERE clause?

  1. Substitution variables

  2. Replacement variables

  3. Prompt variables

  4. Instead-of variables
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To solve this question, the user needs to know the basic functionalities of SQL*Plus.

The feature that can be used to replace values in the WHERE clause is Substitution variables.

Option A is correct. Substitution variables can be used to replace values in the WHERE clause at runtime. They begin with an ampersand (&) and can be used to replace any part of the SQL query, including the WHERE clause.

Option B is incorrect. Replacement variables are not a feature of SQL*Plus, and they cannot be used to replace values in the WHERE clause.

Option C is incorrect. Prompt variables are also not a feature of SQL*Plus and cannot be used to replace values in the WHERE clause.

Option D is incorrect. Instead-of variables are not a feature of SQL*Plus and cannot be used to replace values in the WHERE clause.

The answer is: A

Multiple choice technology databases

Evaluate the SQL statement: SELECT LPAD (salary,10,*) FROM EMP WHERE EMP_ID = 1001; If the employee with the EMP_ID 1001 has a salary of 17000, what is displayed?

  1. 17000.00

  2. 17000*****

  3. ****170.00

  4. **17000.00

  5. an error statement
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

To understand this SQL statement, the user should know about the LPAD function, which pads a string with a specified character until the string reaches a specified length. In this case, the salary column will be padded with asterisks (*) until it is 10 characters long.

The WHERE clause restricts the query to employees with an EMP_ID of 1001.

Assuming the employee with EMP_ID 1001 has a salary of 17000, the LPAD function will pad the salary with asterisks () until it has a length of 10 characters, resulting in the string "***17000". Therefore, the correct answer is:

The Answer is: A. ****170.00

Multiple choice technology databases

Which one is a system privilege?

  1. select

  2. delete

  3. execute

  4. alter table

  5. create table
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

A system privilege like CREATE TABLE allows users to perform administrative actions across the database schema. The other options, like SELECT, DELETE, EXECUTE, and ALTER TABLE, are object privileges that govern actions on specific existing database objects.

Multiple choice technology databases

Which two are types of functions available in SQL?

  1. String

  2. Character

  3. Integer

  4. Calander

  5. Numeric

  6. Translation
Reveal answer Fill a bubble to check yourself
B,E Correct answer
Explanation

SQL functions are categorized into types including Character functions (for string manipulation) and Numeric functions (for mathematical operations). Options A, C, D, and F are incorrect - 'String' and 'Integer' are data types not function categories, 'Calendar' should be 'Date', and 'Translation' is not a SQL function category.

Multiple choice technology databases

Which two statements are true about constraints?

  1. The UNIQUE constraint does not permit a null value for the column.

  2. A UNIQUE index gets created for columns with PRIMARY KEY and UNIQUE constraints.

  3. The PRIMARY KEY and FOREIGN KEY constraints create a UNIQUE index.

  4. The NOT NULL constraint ensures that null values are not permitted for the column.
Reveal answer Fill a bubble to check yourself
A,D Correct answer
Multiple choice technology databases

The user Alice wants to grant all users query privileges on her DEPT table. Which SQL statement accomplishes this?

  1. GRANT select ON dept TO ALL_USERS;

  2. GRANT select ON dept TO ALL;

  3. GRANT QUERY ON dept TO ALL_USERS;

  4. GRANT select ON dept TO PUBLIC;
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

In SQL, the PUBLIC keyword represents a special user group containing all database users. Granting privileges to PUBLIC allows everyone to access the table, while other terms like ALL_USERS or ALL are syntactically incorrect here.

Multiple choice technology databases

Which best describes an inline view?

  1. a schema object

  2. a subquery that can contain an ORDER BY clause

  3. another name for a view that contains group functions

  4. a subquery that is part of the FROM clause of another query
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

An inline view is a subquery included in the FROM clause of a SELECT statement, which the database treats as a temporary table. Options A, B, and C are incorrect - it is not a schema object, not defined by ORDER BY support, and not merely a view with group functions.

Multiple choice technology databases

The DBA issues this SQL command: CREATE USER scott IDENTIFIED by tiger; What privileges does the user Scott have at this point?

  1. no privileges

  2. only the SELECT privilege

  3. only the CONNECT privilege

  4. all the privileges of a default user
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

When a user is created with CREATE USER, they have no privileges at all - not even the ability to log in. The user must be explicitly granted privileges (like CREATE SESSION) before they can do anything. Options B, C, and D are incorrect - no default privileges are granted.

Multiple choice technology databases

Which is iSQL*Plus commands?

  1. Insert

  2. Update

  3. Select

  4. Describe

  5. Delete

  6. Rename
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

DESCRIBE is an iSQL*Plus command that displays the structure of a table, view, or other database object. Options A, B, C, E, and F are SQL commands (INSERT, UPDATE, SELECT, DELETE, RENAME), not iSQL*Plus-specific commands.

Multiple choice technology databases

Which SQL statement returns a numeric value?

  1. SELECT ADD_MONTHS(MAX(hire_Date), 6) FROM EMP;

  2. SELECT ROUND(hire_date)FROM EMP;

  3. SELECT sysdate-hire_date FROM EMP;

  4. SELECT TO_NUMBER(hire_date + 7)FROM EMP;
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

In Oracle SQL, subtracting two dates returns a numeric value representing the number of days between them. The sysdate-hire_date expression performs date arithmetic and yields a number. ADD_MONTHS returns a date, ROUND applied to a date returns a date, and TO_NUMBER cannot directly convert a date expression.

Multiple choice technology databases

Which object privileges can be granted on a view?

  1. none

  2. DELETE, INSERT,SELECT

  3. ALTER, DELETE, INSERT, SELECT

  4. DELETE, INSERT, SELECT, UPDATE
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Object privileges that can be granted on a view in Oracle are SELECT, INSERT, UPDATE, and DELETE. These are the standard DML privileges applicable to views. ALTER is a structure modification privilege that cannot be granted on views - only the view owner or someone with ALTER ANY VIEW privilege can modify the view definition.

Multiple choice technology databases

Top N analysis requires _____ and _____.

  1. the use of rowed

  2. a GROUP BY clause

  3. an ORDER BY clause

  4. only an inline view

  5. an inline view and an outer query
Reveal answer Fill a bubble to check yourself
C,E Correct answer
Explanation

Top-N analysis requires both an inline view (to order and rank the data) and an outer query (to limit the results to the top N rows). The inline view contains the ORDER BY clause to sort the data, and the outer query applies ROWNUM or a limiting clause to select only the top N. A GROUP BY clause is not required for Top-N analysis.