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

Which two statements about views are true?

  1. A view can be created as read only.

  2. A view can be created as a join of two or more tables.

  3. A view can not have an ORDER BY clause in the SELECT statement.

  4. A view can not be created with a GROUP BY clause in the SELECT statement.
Reveal answer Fill a bubble to check yourself
A,B Correct answer
Explanation

SQL views can be created with the WITH CHECK OPTION to make them read-only, preventing inserts or updates through the view. Views can also be created as joins of two or more tables to present combined data. However, standard SQL prohibits ORDER BY in view definitions - ordering belongs in queries accessing the view. Views can use GROUP BY for aggregation.

Multiple choice technology databases

Which three statements about SubQueries are true?

  1. A main query can have mroe than one subquery.

  2. A subquery can have more than one main query.

  3. The sub query and main query must retrieve data from the same table.

  4. The subquery and main query can retrieve data from different tables.

  5. Only one column or expression can be compared between the subquery and main query.
Reveal answer Fill a bubble to check yourself
A,D,E Correct answer
Explanation

A main query can indeed contain multiple subqueries in different clauses (WHERE, FROM, SELECT). Subqueries and main queries can access entirely different tables - they're independent queries. When comparing a subquery result to the main query in a WHERE clause, only one column or expression can be compared (this is the scalar subquery pattern). Option B is false because a subquery is nested within one main query, not multiple.

Multiple choice technology mainframe

Level 66 used for?

  1. For Renames Clause

  2. For Condition Names

  3. Elementary level item. Cannot be subdivisions of other items (cannot be qualified), nor can they be subdivided themselves.

  4. All of the above
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Level 66 is specifically used for the RENAMES clause in COBOL. It allows you to regroup existing data elements by giving a new name to a contiguous set of elementary items. Level 88 is for condition names, and level 77 is for independent elementary items.

Multiple choice technology databases

SCOTT user has grants on HR schema objects. You are connected to SCOTT schema and issued following command:Create synonym emp for hr.employees;What happens when you issue this statement?

  1. An error is generated.

  2. A public synonym is created for employees table.

  3. You create an alternative name for the employees table of HR schema in HR schema

  4. You create an alternative name for the employees table of HR schema in SCOTT schema
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

When connected as SCOTT and creating a synonym 'emp' for 'hr.employees', you create an alternative name (emp) that references the employees table in the HR schema. The synonym itself is created in the SCOTT schema where you're connected. A and B are wrong - the statement succeeds and creates a private synonym, not a public one.

Multiple choice technology databases

Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) Which three statements inserts a row into the table? (Choose three)

  1. INSERT INTO employees VALUES (NULL, 'JOHN','Smith');

  2. . INSERT INTO employees( first_name, last_name) VALUES ('JOHN','Smith');

  3. INSERT INTO employees VALUES ('1000','JOHN','NULL');

  4. INSERT INTO employees(first_name,last_name, employee_id) VALUES ('1000, 'john','Smith');

  5. . INSERT INTO employees (employee_id) VALUES (1000);

  6. INSERT INTO employees (employee_id, first_name, last_name) VALUES ( 1000, 'john',");
Reveal answer Fill a bubble to check yourself
C,E,F Correct answer
Explanation

Option C inserts numeric 1000 into employee_id (NUMBER column) and strings for names. Option E inserts only the employee_id with NULLs for name columns (valid for nullable columns). Option F inserts all three columns with proper values. Options A and B fail because employee_id is PRIMARY KEY (cannot be NULL, and must provide all NOT NULL columns). Option D has syntax errors with quotes.

Multiple choice technology databases

You need to give the MANAGER role the ability to select from, insert into, and modify existing rows in the STUDENT_GRADES table. Anyone given this MANAGER role should be able to pass those privileges on to others. Which statement accomplishes this

  1. GRANT select, insert, update ON student_grades TO manager

  2. GRANT select, insert, update ON student_grades TO ROLE manager

  3. GRANT select, insert, modify ON student_grades TO manager WITH GRANT OPTION;

  4. GRANT select, insert, update ON student_grades TO manager WITH GRANT OPTION;
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The GRANT statement must use 'update' not 'modify' for SQL modification privileges. 'WITH GRANT OPTION' allows the role to pass these privileges to others. Option A is correct but lacks the GRANT OPTION clause. Option B incorrectly uses ROLE keyword. Option C uses 'modify' which isn't a valid SQL privilege keyword. Option D is complete and correct.

Multiple choice technology databases

Which are true about aggregate functions? (Choose all that apply)

  1. You can use aggregate functions in any caluse of the SELECT statement.

  2. You can user aggregate funcitons only in column list of select clause and where clause of select statement.

  3. You can mix single row functions with aggregate functions in the column list of a SELECT statement by grouping on single row columns.

  4. You can pass column names, expressions, constants, or functions as parameter to an aggregate function.
Reveal answer Fill a bubble to check yourself
A,D Correct answer
Explanation

Aggregate functions (COUNT, SUM, AVG, etc.) can be used in SELECT clause, HAVING clause, and ORDER BY clause - making 'any clause' true. They CANNOT be used in WHERE clause (that requires single-row functions or subqueries). You can mix single-row and aggregate functions only by grouping on the single-row columns. Aggregate functions accept columns, expressions, constants, or other function results as parameters.

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

CREATE TABLE is a system privilege (DDL permission) that applies across the database, unlike SELECT/DELETE/EXECUTE which are object-specific privileges tied to particular tables or procedures. System privileges control structural database operations, while object privileges control data access.

Multiple choice technology databases

The database administrator of your company created a public synonym called HR for the HUMAN_RESOURCES table of the GENERAL schema, because many users frequently use this table. As a user of the database, you created a table called HR in your schema. What happens when you execute this query? SELECT * FROM HR;

  1. You obtain the results retrieved from the public synonym HR created by the database administrator.

  2. You obtain the results retrieved from the HR table that belongs to your schema

  3. You get an error message because you cannot retrieve from a table that has the same name as a public synonym

  4. You obtain the results retrieved from both the public synonym HR and the HR table that belongs to your schema, as a Cartesian product.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

In SQL, name resolution follows a specific order: it first checks for an object in your own schema, then looks for public synonyms. Since you have a table named HR in your schema, the database will use that instead of the public synonym pointing to GENERAL.HUMAN_RESOURCES. The public synonym is only used when no object with that name exists in your schema.

Multiple choice technology databases

Evaluate the SQL statement: 1 SELECT a.emp_name, a.sal, a.dept_id, b.maxsal 2 FROM employees a, 3 (SELECT dept_id, MAX(sal) maxsal 4. FROM employees 5 GROUP BY dept_id) b 6 WHERE a.dept_id = b.dept_id 7 AND a. asl < b. maxsal; What is the result of the statement?

  1. The statement produces an error at line 1.

  2. The statement produces an error at line 3

  3. The statement produces an error at line 6.

  4. The statement returns the employee name, salary, department ID, and maximum salary earned in the department of the employee for all departments that pay less salary then the maximum salary paid in the company

  5. The statement returns the employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department.
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

To evaluate the SQL statement, let's go through each line and understand its purpose:

  1. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal: This line selects the employee name, salary, department ID from the "employees" table, and also selects the maximum salary for each department from the subquery aliased as "b".

  2. FROM employees a: This line specifies the "employees" table and aliases it as "a".

  3. (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) b: This line is a subquery that selects the department ID and maximum salary for each department from the "employees" table. It is aliased as "b".

  4. WHERE a.dept_id = b.dept_id AND a.sal < b.maxsal: This line applies a condition to join the main query with the subquery. It matches the department IDs and ensures that the salary of an employee in department "a" is less than the maximum salary in that department as obtained from the subquery "b".

Now, let's go through the options:

A. The statement produces an error at line 1: This option is incorrect because there is no apparent error in line 1.

B. The statement produces an error at line 3: This option is incorrect because there is no apparent error in line 3.

C. The statement produces an error at line 6: This option is incorrect because there is no line 6 in the SQL statement.

D. The statement returns the employee name, salary, department ID, and maximum salary earned in the department of the employee for all departments that pay less salary than the maximum salary paid in the company: This option is incorrect because the statement does not retrieve the maximum salary paid in the company. It only retrieves the maximum salary for each department.

E. The statement returns the employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department: This option is correct. The statement retrieves the employee name, salary, department ID, and the maximum salary earned in the department for all employees who earn less than the maximum salary in their department.

Therefore, the correct answer is option E.

Multiple choice technology databases

The EMP table contains these columns: LAST NAME VARCHAR2(25) SALARY NUMBER(6,2) DEPARTMENT_ID NUMBER(6) You need to display the employees who have not been assigned to any department. You write the SELECT statement: SELECT LAST_NAME, SALARY, DEPARTMENT_ID FROM EMP WHERE DEPARMENT_ID = NULL; What is true about this SQL statement?

  1. The SQL statement displays the desired results

  2. The column in the WHERE clause should be changed to display the desired results

  3. The operator in the WHERE clause should be changed to display the desired results

  4. The WHERE clause should be changed to use an outer join to display the desired results
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

In SQL, comparing a column to NULL using the = operator (e.g., DEPARTMENT_ID = NULL) always evaluates to unknown and returns no rows. To find null values, the operator must be changed to IS NULL.

Multiple choice technology databases

For which two constraints does the Oracle Server implicitly create a unique index? (Choose two.)

  1. NOT NULL

  2. PRIMARY KEY

  3. FOREIGN KEY

  4. CHECK

  5. UNIQUE
Reveal answer Fill a bubble to check yourself
B,E Correct answer
Explanation

Oracle automatically creates unique indexes for PRIMARY KEY and UNIQUE constraints to enforce uniqueness. NOT NULL, FOREIGN KEY, and CHECK constraints do not trigger automatic unique index creation - they use different enforcement mechanisms (check constraints, referential integrity checks).

Multiple choice technology databases

Which two statements about subqueries are true? (Choose two.)

  1. A single row subquery can retrieve data from only one table

  2. A SQL query statement cannot display data from table B that is referred to in its subquery, unless table B is included in the main query's FROM clause.

  3. A SQL query statement can display data from table B that is referred to in its subquery, without including table B in its own FROM clause.

  4. A single row subquery can retrieve data from more than one table

  5. A single row subquery cannot be used in a condition where the LIKE operator is used for comparison

  6. A multiple-row subquery cannot be used in a condition where the LIKE operator is used for comparison.
Reveal answer Fill a bubble to check yourself
B,D Correct answer
Explanation

A subquery can reference table B in its FROM clause, but the main query must also include table B in its FROM clause to display its columns - table references in subqueries are not automatically visible in the main query. Single-row subqueries can retrieve data from multiple tables using joins in their own FROM clause. Multiple-row subqueries can use LIKE comparisons.