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

CREATE TABLE dept(deptno NUMBER(2),dname VARCNAR2(14),1oc VARCNAR2 (13));ROLLBACK;DESCRIBE DEPTWhat 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 introduced before the ROLLBACK statement.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The CREATE TABLE statement is a DDL command that, in Oracle, performs an implicit COMMIT before executing. After the table is created, the ROLLBACK only affects any uncommitted DML operations (like INSERT, UPDATE, DELETE) that occurred after the implicit commit. Since no DML operations were performed, the DEPT table still exists when DESCRIBE DEPT is executed, so it will display the table structure successfully.

Multiple choice technology databases

You added a PHONE_NUMBER column of NUMBER data type to an existing EMPLOYEES table. The EMPLOYEES table already contains records of 100 employees. Now, you want to enter the phone numbers of each of the 100 employees into the table. Some of the employees may not have a phone number available. Which data manipulation operation do you perform?

  1. MERGE

  2. INSERT

  3. UPDATE

  4. ADD

  5. ENTER
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

When you need to add data to existing records in a table, you use the UPDATE statement. The INSERT statement creates new rows, whereas UPDATE modifies existing rows. Since the table already has 100 employee records and you want to add phone numbers to those records (some of which may be NULL if unavailable), UPDATE is the correct operation.

Multiple choice technology databases

In which case would you use a FULL OUTER JOIN?

  1. Both tables have NULL values

  2. You want all unmatched data from one table

  3. You want all matched data from both tables

  4. You want all unmatched data from both tables

  5. One of the tables has more data than the other

  6. You want all matched and unmatched data from only one table
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

A FULL OUTER JOIN returns all matched rows from both tables, plus all unmatched rows from both tables. The option stating it is used when 'You want all unmatched data from both tables' is the closest correct description among the choices.

Multiple choice technology databases

let sysdate='19-SEP-2009' select round(sysdate,'YEAR'),round(sysdate,'MONTH') from dual would results in?

  1. 09,SEP

  2. 1/1/2010,1/10/2009

  3. 1/1/2009,1/1/2009

  4. 2009,SEPTEMBER
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

ROUND(date, 'YEAR') rounds to January 1st of the nearest year. September is past mid-year, so it rounds up to 2010. ROUND(date, 'MONTH') rounds to the 1st of nearest month. Day 19 is past mid-month, so September rounds to October 1, 2009.

Multiple choice technology databases

which of the following are true about natural join?

  1. No advantage

  2. eliminates aliases

  3. Natural join doesnot exist in oracle

  4. eliminates join comparision
Reveal answer Fill a bubble to check yourself
B,D Correct answer
Explanation

NATURAL JOIN automatically joins tables on columns with identical names, eliminating the need to specify join conditions in an ON clause. This removes both column aliases (duplicate column names) and the explicit join comparison condition.

Multiple choice technology databases

State true or false All the columns in the column clause of select statment that are not in a group function must be listed in the group by clause.However,a column listed in group by clause needn't appear in column clause

  1. True

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

In SQL, any column in SELECT that isn't inside an aggregate function (like COUNT, SUM, AVG) must appear in GROUP BY. This ensures each row in the result represents a unique group. The reverse isn't required - a column can be in GROUP BY without being selected.

Multiple choice technology databases

select next_day('19-SEP-09','FRIDAY') from dual and select next_day('19-SEP-09',7) from dual would results in?(answers in mm/dd/yyyy formate)

  1. syntax error and 09/26/2009,

  2. 09/25/2009 and 09/25/2009

  3. 09/25/2009 and syntax error

  4. 09/25/2009,09/26/2009
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

NEXT_DAY('19-SEP-09', 'FRIDAY') finds the first Friday on or after Sept 19, 2009. Sept 19, 2009 is a Saturday, so the next Friday is Sept 25, 2009. NEXT_DAY('19-SEP-09', 7) does the same - 7 represents Friday in Oracle (1=Sunday). Both return 09/25/2009.

Multiple choice technology platforms and products

What is the meaning of the below expression in update strategy? IIF( SKU_ID = 100000, 1,2)

  1. IIF( SKU_ID = 100000, DD_UPDATE, DD_DELETE)

  2. IIF( SKU_ID = 100000, DD_INSERT, DD_UPDATE)

  3. IIF( SKU_ID = 100000, DD_DELETE, DD_REJECT)

  4. IIF( SKU_ID = 100000, TRUE, FALSE)
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Update Strategy uses numeric constants: DD_INSERT=0, DD_DELETE=1, DD_UPDATE=2, DD_REJECT=3. The IIF returns 1 (DD_UPDATE) when SKU_ID equals 100000, and 2 (DD_DELETE) otherwise, perfectly matching option A's description.

Multiple choice technology databases

select substr('DECCANPARK',-5,6) FROM DUAL

  1. ERROR

  2. NPARK

  3. DECCAN

  4. NULL
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Oracle's SUBSTR with negative start counts from the string end. -5 points to the 'N' in 'DECCANPARK' (position 6 from start). The length parameter (6) exceeds available characters (only 5 from 'N' to end), so Oracle returns all remaining characters 'NPARK' without error.

Multiple choice technology

UserResponse function returns

  1. SQL of user created variable

  2. Name of User

  3. User entered prompt value in string

  4. User entered prompt value in Number
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The UserResponse function is designed to capture and return user input as a string. This is the most common behavior for functions named to handle user responses in interactive systems.

Multiple choice technology databases

The database administrator of your company created a public synonum 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 DBA.

  2. You obtain the results retrieved from the HR table that belongs to you 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 publich 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

SQL name resolution prioritizes local schema objects over public synonyms. When you execute SELECT * FROM HR, the database first finds your local table HR in your schema and uses it, ignoring the public synonym. The public synonym HR is only used if no local object named HR exists.

Multiple choice technology databases

When we use DISTINCT keyword in a SELECT query it will sort the output by the column-name we specified in the DISTINCT clause.

  1. True

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

In most SQL implementations (especially Oracle), the DISTINCT keyword performs an implicit sort to identify and eliminate duplicate rows. The returned result set is typically in sorted order based on the DISTINCT columns, though this is implementation-dependent behavior.