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

" in sybase is changed to ' in sql

  1. True

  2. False
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Different database systems and SQL dialects use different string delimiters. Sybase and some older SQL variants use double quotes (") for string literals, while standard SQL and most modern databases use single quotes (') for string literals. Converting from Sybase to SQL requires changing all string delimiters from double to single quotes.

Multiple choice technology databases

exp1 LIKE exp2 in sybase is changed to

  1. exp1 like exp2

  2. exp1 like ltrim(exp2)

  3. exp1 like rtrim(exp2)

  4. ltrim(exp1) like exp2
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Sybase behaves differently with trailing spaces in char fields. In migrations or compatibility adjustments, right-trimming the pattern (exp2) using rtrim ensures matching behavior matches expected standard patterns without trailing space mismatches.

Multiple choice technology databases

*= operator belongs to SQL server 2008

  1. True

  2. False
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The non-standard *= outer join operator was deprecated in SQL Server 2005 and is not supported in SQL Server 2008 compatibility modes under level 100, which requires the standard ANSI LEFT OUTER JOIN.

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?

  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');
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

EMPLOYEE_ID is a Primary Key with NOT NULL constraint, so it cannot be NULL. Option A violates this constraint. Option B fails because it omits the required Primary Key column. Option C works because it provides all three values with employee_id='1000' (as string), first_name='JOHN', and last_name='NULL' as a string literal. Option D has syntax errors (missing quote, wrong order).

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

To solve this question, the user needs to understand SQL syntax for granting privileges to a specific role or user.

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

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

This option grants SELECT, INSERT, and UPDATE privileges on the STUDENT_GRADES table to a user named "manager". However, it does not grant the ability to pass on these privileges to others.

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

This option is similar to Option A, but grants privileges to a role named "manager" instead of a specific user. However, it still does not grant the ability to pass on these privileges to others.

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

This option is incorrect because there is no "MODIFY" privilege in SQL. It should be "UPDATE" instead.

D. GRANT select, insert, update ON student_grades TO manager WITH GRANT OPTION;

This option is correct. It grants SELECT, INSERT, and UPDATE privileges on the STUDENT_GRADES table to a user named "manager" and also includes the "WITH GRANT OPTION" clause, which allows this user to pass on these privileges to others.

Therefore, the correct answer is:

The Answer is: D

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

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

When a table name exists in both your local schema and as a public synonym, SQL always gives priority to the local schema table. The database first looks for objects in your own schema before checking public synonyms. This means your query will return data from your own HR table, not the public synonym pointing to HUMAN_RESOURCES.

Multiple choice technology databases

Which statement about views are true?

  1. A view can be created as read only.

  2. A view cannot have an ORDER BY clause in the SELECT statement.

  3. A view cannot be created with a GROUP BY clause in the SELECT statement.

  4. A view must have aliases defined for the column names in the SELECT statement.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Views can be explicitly created as read-only (using constructs like WITH READ ONLY in Oracle). Views can contain ORDER BY, GROUP BY, and do not strictly require aliases for all columns unless expressions or duplicates exist.

Multiple choice technology databases

Examine the description of the EMPLOYEES table: EMP_ID NUMBER(4) NOT NULL LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(30) DEPT_ID NUMBER(2) JOB_CAT VARCHARD2(30) SALARY NUMBER(8,2) Which statement shows the maximum salary paid in each job category of each department?

  1. SELECT dept_id, job_cat, MAX(salary)

  2. ELECT dept_id, job_cat, MAX(salary)

  3. SELECT dept_id, MAX(salary)

  4. SELECT dept_id, salary
Reveal answer Fill a bubble to check yourself
B Correct answer
Multiple choice technology databases

Management has asked you to calculate the value 12*salary* commission_pct for all the employees in the EMP table. The EMP table contains these columns: LAST NAME VARCNAR2(35) NOT NULL SALARY NUMBER(9,2) NOT NULL COMMISION_PCT NUMBER(4,2) Which statement ensures that a value is displayed in the calculated columns for all employees?

  1. SELECT last_name, 12*salary* commission_pct FROM emp;

  2. SELECT last_name, 12*salary* (commission_pct,0) FROM emp;

  3. SELECT last_name, 12*salary*(nvl(commission_pct,0)) FROM emp;

  4. SELECT last_name, 12*salary*(decode(commission_pct,0)) FROM emp;
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

To solve this question, the user needs to know SQL syntax and the concept of NULL values. The user must use the proper SQL function to ensure that all employees' values are displayed in the calculated columns.

Option A: SELECT last_name, 12*salary* commission_pct FROM emp; This option is incorrect because it does not handle NULL values. If any employee has a NULL value in the commission_pct column, the entire expression would result in NULL, and no value would be displayed.

Option B: SELECT last_name, 12*salary* (commission_pct,0) FROM emp; This option is incorrect because it contains a syntax error. The expression (commission_pct, 0) is not valid SQL syntax.

Option C: SELECT last_name, 12*salary*(nvl(commission_pct,0)) FROM emp; This option is correct. The NVL function is used to handle NULL values in the commission_pct column. The function replaces any NULL value with 0, ensuring that all employees have a value in the calculated column.

Option D: SELECT last_name, 12*salary*(decode(commission_pct,0)) FROM emp; This option is incorrect because the DECODE function is not used correctly. The DECODE function should have a second argument to return if the first argument is not equal to 0. Without the second argument, the function would not handle any non-zero values in the commission_pct column.

Therefore, the correct answer is:

The Answer is: C. SELECT last_name, 12*salary*(nvl(commission_pct,0)) FROM emp;

Multiple choice technology databases

Examine the description of the STUDENTS table: STD_ID NUMBER(4) COURSE_ID VARCHARD2(10) START_DATE DATE END_DATE DATE Which two aggregate functions are valid on the START_DATE column? (Choose two)

  1. SUM(start_date)

  2. AVG(start_date)

  3. COUNT(start_date)

  4. AVG(start_date, end_date)
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Explanation: Aggregate functions operate on a set of rows and return a single result. The two valid aggregate functions on the START_DATE column are: C. COUNT(start_date) - This function returns the number of rows where START_DATE is not null.

Option A is invalid because the SUM function is used to add numeric values and cannot be used with a DATE data type. Option B is invalid because the AVG function will not work with DATE, you can convert DATE to NUMERIC then it can be used, but without Casting it will not work Option D is invalid because the AVG function can only be used with a single column, and the argument passed in should be numeric.

Multiple choice technology databases

The EMPLOYEE tables has these columns: LAST_NAME VARCHAR2(35) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(5,2) You want to display the name and annual salary multiplied by the commission_pct for all employees. For records that have a NULL commission_pct, a zero must be displayed against the calculated column. Which SQL statement displays the desired results?

  1. SELECT last_name, (salary * 12) * commission_pct FROM EMPLOYEES;

  2. SELECT last_name, (salary * 12) * IFNULL(commission_pct, 0) FROM EMPLOYEES;

  3. SELECT last_name, (salary * 12) * NVL2(commission_pct, 0) FROM EMPLOYEES;

  4. SELECT last_name, (salary * 12) * NVL(commission_pct, 0) FROM EMPLOYEES;
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Explanation: To display the name and annual salary multiplied by the commission_pct for all employees, we need to use the multiplication (*) operator. For records that have a NULL commission_pct, a zero must be displayed against the calculated column, we need to use the NVL function.

Option A) SELECT last_name, (salary * 12) * commission_pct FROM EMPLOYEES; This option is incorrect because it does not account for NULL commission_pct values and does not use the NVL function.

Option B) SELECT last_name, (salary * 12) * IFNULL(commission_pct, 0) FROM EMPLOYEES; This option is incorrect because the IFNULL function is not valid in Oracle SQL, and it does not use the NVL function.

Option C) SELECT last_name, (salary * 12) * NVL2(commission_pct, 0) FROM EMPLOYEES; This option is incorrect because the NVL2 function is not valid in Oracle SQL. It should be replaced with the NVL function.

Option D) SELECT last_name, (salary * 12) * NVL(commission_pct, 0) FROM EMPLOYEES; This option is correct because it uses the multiplication operator to calculate the annual salary multiplied by the commission_pct and the NVL function to handle NULL commission_pct values.

Therefore, the correct answer is D.

Multiple choice technology databases

Which data dictionary table should you query to view the object privileges granted to the user on specific columns?

  1. USER_TAB_PRIVS_MADE

  2. USER_TAB_PRIVS

  3. USER_COL_PRIVS_MADE

  4. USER_COL_PRIVS
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Oracle's data dictionary provides several views for tracking privileges. USER_COL_PRIVS displays column-level privileges granted to the current user, while USER_COL_PRIVS_MADE shows column privileges the user granted to others. USER_TAB_PRIVS and USER_TAB_PRIVS_MADE are for table-level privileges.

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, NULL represents an unknown value and cannot be compared using the = operator. Any comparison with NULL returns NULL (unknown), never true or false. To check for NULL values, you must use the IS NULL or IS NOT NULL operators. The statement WHERE DEPARTMENT_ID IS NULL would correctly find employees without a department assignment.

Multiple choice technology databases

Evaluate the set of SQL statements: CREATE TABLE dept (deptno NUMBER(2), dname VARCNAR2(14), 1oc VARCNAR2 (13)); ROLLBACK; DESCRIBE DEPT What is true about the set?

  1. The DESCRIBE DEPT statement displays the structure of the DEPT table.

  2. The ROLLBACK statement frees the storage space occupies by the DEPT table.

  3. The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist.

  4. The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a COMMIT statement
Reveal answer Fill a bubble to check yourself
A Correct answer