Tag: databases

Questions Related to databases

A employee table contain below values empno empname deptno salary 1 aaa 10 30000 2 bbb 20 25000 3 ccc 10 Null 4 ddd Null 35000 Select avg(Isnull(salary,10000)) from employee?

  1. 0

  2. 30000

  3. 25000

  4. 35000


Correct Option: C

EMPNO ENAME SAL A822 RAMASWAMY 3500 A812 NARAYAN 5000 A973 UMESH 2850 A500 BALAJI 5750 Use these data for the following Questions: Select SAL from EMP E1 where 3 > ( Select count(*) from Emp E2 Where E1.SAL > E2.SAL ) will retrieve

  1. 3500,5000,2500

  2. 5000,2850

  3. 2850,5750

  4. 5000,5750


Correct Option: A
  1. To protect some of the columns of a table from other users

  2. Occupies data storage space

  3. To hide complexity of a query

  4. To hide complexity of a calculations


Correct Option: B
  1. SELECT dept_id, job_cat, MAX(salary) FROM employees WHERE salary > MAX(salary);

  2. SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id, job_cat;

  3. SELECT dept_id, job_cat, MAX(salary) FROM employees;

  4. SELECT dept_id, job_cat, MAX(salary) FROM employees v

  5. SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id, job_cat, salary;


Correct Option: B
Explanation:

To solve this question, the user needs to know SQL syntax for selecting data from a table, using aggregate functions like MAX(), and grouping data using GROUP BY.

Option A: This option is incorrect because it includes a comparison statement with MAX(salary), which is not valid. The MAX() function can only be used with aggregate functions or in GROUP BY queries.

Option B: This option is correct. It selects the department ID, job category, and maximum salary for each group of department ID and job category. The GROUP BY clause is used to group the data by department ID and job category.

Option C: This option is incorrect because it does not include a GROUP BY clause, so it would return the overall maximum salary in the entire table, rather than the maximum salary for each job category in each department.

Option D: This option is incorrect because it selects the same data as option B, but uses an alias for the table name. This is not necessary and does not affect the query.

Option E: This option is incorrect because it includes the salary column in the GROUP BY clause, which would group the data by salary as well. This would result in multiple rows for each department ID and job category, with the same maximum salary value.

Therefore, the correct answer is: B. SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id, job_cat;

SUBSTR(SQUARE ANS ALWAYS WORK HARD,14,6) will return

  1. ALWAY

  2. S ALWA

  3. ALWAYS

  4. None of the above


Correct Option: C
Explanation:

To solve this question, the user needs to understand the basic syntax of the SUBSTR() function, which allows you to extract a substring from a larger string.

The syntax of the SUBSTR() function is as follows:

SUBSTR(string, start_position, length)

where string is the original string, start_position is the position in the string where you want to start extracting the substring, and length is the number of characters you want to extract.

In this case, the SUBSTR() function is applied to the string "SQUARE ANS ALWAYS WORK HARD", starting at position 14 and extracting 6 characters.

So, to answer the question:

  • The starting position is 14, which means we start counting from the 14th character in the string.
  • The length of the substring to extract is 6, which means we extract 6 characters from the starting position.

Therefore, the answer is:

The Answer is: C. ALWAYS

  1. CREATE

  2. ALTER

  3. ALTER SESSION

  4. None of the above


Correct Option: C

If an UNIQUE KEY constraint on DATE column is created, will it accept the rows that are inserted with SYSDATE ?

  1. Will

  2. Wont

  3. NA

  4. na


Correct Option: B
  1. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command CREATE PRIVATE SYNONYM EDL_VU FOR mary.EMP DEPT_LOC_VU; then he can prefix the columns with this synonym.

  2. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command CREATE SYNONYM EDL_VU FOR mary.EMP_DEPT_LOC_VU; then he can prefix the columns with this synonym.

  3. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command CREATE LOCAL SYNONYM EDL_VU FOR mary.EMP DEPT_LOC_VU; then he can prefix the columns with this synonym.

  4. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command CREATE SYNONYM EDL_VU ON mary(EMP_DEPT_LOC_VU); then he can prefix the columns with this synonym.

  5. Scott cannot create a synonym because synonyms can be created only for tables.

  6. Scott cannot create any synonym for Mary's view. Mary should create a private synonym for the view and grant SELECT privilege on that synonym to Scott.


Correct Option: B
Explanation:

To solve this question, the user needs to know about synonyms in Oracle and how they can be used to eliminate the need to qualify the view name with the owner name.

Option A is incorrect because CREATE PRIVATE SYNONYM is not a valid command in Oracle.

Option B is correct because Scott can create a public synonym for the EMP_DEPT_LOC_VU view using the CREATE SYNONYM command. Once the synonym is created, he can use it to reference the view without specifying the owner name each time.

Option C is incorrect because CREATE LOCAL SYNONYM is not a valid command in Oracle.

Option D is incorrect because the syntax of the CREATE SYNONYM command is incorrect. The correct syntax is CREATE SYNONYM synonym_name FOR object_name, where object_name is the name of the view or table.

Option E is incorrect because synonyms can be created not only for tables but also for views.

Option F is incorrect because Scott can create a public synonym for the view. Mary does not need to create a private synonym.

Therefore, the correct answer is:

The Answer is: B. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command CREATE SYNONYM EDL_VU FOR mary.EMP_DEPT_LOC_VU; then he can prefix the columns with this synonym.