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

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 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 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.

Multiple choice technology databases

The output for the qyery "select lpad('sql functions',18,'*#$') from dual;" is
  1. *#\$*#\$sql function

  2. error
  3. *#$*#sql functions

  4. None of the above
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

lpad pads the string 'sql functions' (13 chars) on the left to reach 18 characters using pattern '#$'. The pattern repeats: '#$#$' (6 chars) + 'sql functions' (12 chars) would be 18, but we need exactly 18. The pattern '#$#' (5 chars) + 'sql functions' (13 chars) = 18 characters. Option C shows '#$*#sql functions' which is the correct padding.