Tag: databases

Questions Related to databases

" in sybase is changed to ' in sql
technology databases

  1. True

  2. False

Show answer
Correct Option: A

exp1 LIKE exp2 in sybase is changed to

technology databases

  1. exp1 like exp2

  2. exp1 like ltrim(exp2)

  3. exp1 like rtrim(exp2)

  4. ltrim(exp1) like exp2

Show answer
Correct Option: C

*= operator belongs to SQL server 2008
technology databases

  1. True

  2. False

Show answer
Correct Option: B

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?

technology databases

  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');

Show answer
Correct Option: C

AI Explanation

To answer this question, let's go through each option to understand why it is correct or incorrect:

Option A) INSERT INTO employees VALUES (NULL, 'JOHN','Smith'); This option is incorrect because it tries to insert a NULL value for the primary key column EMPLOYEE_ID. Since EMPLOYEE_ID is defined as the primary key, it cannot have a NULL value.

Option B) INSERT INTO employees(first_name, last_name) VALUES ('JOHN','Smith'); This option is incorrect because it only specifies the values for the columns FIRST_NAME and LAST_NAME, but it does not specify a value for the primary key column EMPLOYEE_ID. Since EMPLOYEE_ID is defined as the primary key, it must have a value specified during the insertion.

Option C) INSERT INTO employees VALUES ('1000','JOHN','NULL'); This option is correct because it specifies values for all columns - EMPLOYEE_ID, FIRST_NAME, and LAST_NAME. The value '1000' is inserted into the EMPLOYEE_ID column, and the values 'JOHN' and 'NULL' are inserted into the FIRST_NAME and LAST_NAME columns, respectively.

Option D) INSERT INTO employees(first_name,last_name, employee_id) VALUES ('1000, 'john','Smith'); This option is incorrect because it specifies the column names in the wrong order. It tries to insert '1000' into the FIRST_NAME column, 'john' into the LAST_NAME column, and 'Smith' into the EMPLOYEE_ID column. The column names and values do not match correctly.

The correct answer is C. This option correctly inserts a row into the EMPLOYEES table by specifying values for all columns in the correct order.

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

  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;

Show answer
Correct Option: D
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

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

  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

Show answer
Correct Option: B

Which statement about views are true?

technology databases

  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.

Show answer
Correct Option: A

AI Explanation

To answer this question, let's go through each option to understand why it is correct or incorrect:

Option A) A view can be created as read-only - This option is correct. A view can be created with the READ ONLY clause, which restricts any modifications to the underlying data. Users can only query the view and cannot perform any DML (Data Manipulation Language) operations on it.

Option B) A view cannot have an ORDER BY clause in the SELECT statement - This option is incorrect. A view can have an ORDER BY clause in the SELECT statement. The ORDER BY clause is used to sort the result set returned by the view.

Option C) A view cannot be created with a GROUP BY clause in the SELECT statement - This option is incorrect. A view can be created with a GROUP BY clause in the SELECT statement. The GROUP BY clause is used to group rows based on specified columns and perform aggregate functions on each group.

Option D) A view must have aliases defined for the column names in the SELECT statement - This option is incorrect. While it is a good practice to provide aliases for column names in a view, it is not mandatory. If aliases are not provided, the column names in the view will inherit the column names from the underlying tables.

The correct answer is Option A) A view can be created as read-only. This option is correct because a view can be explicitly created as read-only, preventing any modifications to the underlying data.

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

  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

Show answer
Correct Option: B

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

  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;

Show answer
Correct Option: C
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;

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

  1. SUM(start_date)

  2. AVG(start_date)

  3. COUNT(start_date)

  4. AVG(start_date, end_date)

Show answer
Correct Option: C
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.