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?
technology databases

  1. 0

  2. 30000

  3. 25000

  4. 35000

Show answer
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

technology databases

  1. 3500,5000,2500

  2. 5000,2850

  3. 2850,5750

  4. 5000,5750

Show answer
Correct Option: A

Which of the following is not correct about a View ?

technology databases

  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

Show answer
Correct Option: B

AI Explanation

To answer this question, you need to understand the concept of a View.

A View is a virtual table that is derived from one or more tables in a database. It does not occupy data storage space as it does not store any data itself. Instead, it is a saved query that can be used to retrieve data from the underlying tables.

Let's go through each option to understand why it is correct or incorrect:

Option A) To protect some of the columns of a table from other users - This option is correct because a View can be used to restrict access to certain columns of a table, allowing users to see only the columns that they have permission to access.

Option B) Occupies data storage space - This option is incorrect because a View does not occupy any data storage space. It is just a virtual table that is created based on the underlying tables.

Option C) To hide complexity of a query - This option is correct because a View can be used to simplify complex queries. Instead of writing a complex query each time, users can create a View that encapsulates the complexity and provides a simplified interface to retrieve the desired data.

Option D) To hide complexity of calculations - This option is correct because a View can be used to encapsulate complex calculations. Users can create a View that performs the necessary calculations and presents the results in a simplified manner.

The correct answer is B) Occupies data storage space. This option is incorrect because a View does not occupy any data storage space.

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 VARCHAR2(30) SALARY NUMBER(8,2) Which statement shows the maximum salary paid in each job category of each department?
technology databases

  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;

Show answer
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;

Can an Integrity Constraint be enforced on a table if some existing table data does not satisfy
technology databases

  1. Yes

  2. No

  3. NA

  4. na

Show answer
Correct Option: B

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

  1. ALWAY

  2. S ALWA

  3. ALWAYS

  4. None of the above

Show answer
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

Is it possible to modify a Datatype of a column when column contains Data
technology databases

  1. Yes

  2. No

  3. NA

  4. na

Show answer
Correct Option: B

Which is not part of the Data Definiton Language

technology databases

  1. CREATE

  2. ALTER

  3. ALTER SESSION

  4. None of the above

Show answer
Correct Option: C

AI Explanation

To answer this question, you need to understand the Data Definition Language (DDL) and its various commands.

DDL is a subset of SQL (Structured Query Language) that is used to define and manage the structure of a database. It includes commands for creating, modifying, and deleting database objects such as tables, indexes, and views.

Let's go through each option to understand why it is correct or incorrect:

Option A) CREATE - This option is part of the Data Definition Language (DDL). The CREATE command is used to create new database objects like tables, views, indexes, and schemas.

Option B) ALTER - This option is part of the Data Definition Language (DDL). The ALTER command is used to modify the structure of existing database objects, such as adding or deleting columns in a table.

Option C) ALTER SESSION - This option is not part of the Data Definition Language (DDL). The ALTER SESSION command is used to modify the session-specific parameters or settings associated with a user session, such as changing the date format or language settings. It is part of the Data Manipulation Language (DML) rather than DDL.

Option D) None of the above - This option is incorrect because both Option A (CREATE) and Option B (ALTER) are part of the Data Definition Language (DDL). Option C (ALTER SESSION) is the correct answer as it is not part of DDL.

The correct answer is C) ALTER SESSION. This option is not part of the Data Definition Language (DDL) because it belongs to the Data Manipulation Language (DML).

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

technology databases

  1. Will

  2. Wont

  3. NA

  4. na

Show answer
Correct Option: B

Mary has a view called EMP_DEPT_LOC_VU that was created based on the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables. She granted SELECT privilege to Scott on this view. Which option enables Scott to eliminate the need to qualify the view with the name MARY.EMP_DEPT_LOC_VU each time the view is referenced?

technology databases

  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.

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