I need help on the following SQL questions. 1.The following is the correct code

Question

I need help on the following SQL questions.

1.The following is the correct code to create a view:

a. CREATE VIEW kljview AS

b. CREATE OR REPLACE VIEW kljview

c. CREATE kljview

d. REPLACE VIEW kljview

2. Which of the following is/are correct to give an alias for a column? Use the table Dual to check your answer!

a. SELECT sysdate ''Bach'' s' '' Home''

b. SELECT sysdate 'Beethoven's Home'

c. SELECT sysdate ''Brahms''s Home''

d. SELECT sysdate 'Mozart' Home' e. SELECT sysdate ''Katherine's Home''

3. Choose which letter accurately describes the relationship of the following entities

a. 0:0

b. 1:0

c. 1:1

d. 1:M e. M:M

4. The SQL code: SELECT &last_name, &first_name from employees;

a. involve substitution variables

b. asks for direct user input

c. will be ignored by the server

d. a and b

e. b and c

5. Marge enters the following date in RR format for the employee’s date of birth: 25-Dec-25 Marge enters this information on September 26th, 2051. What is the year of birth as recognized by Oracle?

a. 2025

b. 2005

c. 1925

d. 1905

e. 2125

6. Only primary and foreign keys have the constraint NULL.

a. True

b. False

7. The column heading is used to customize what the reader will see for the name of the column field.

a. True

b. False

8. To determine the total of all the employees of department_id = 90 using the table called employees, the SQL code is SELECT TOTAL(last_NAME) FROM employees WHERE department_id=90;

a. True b. False

9.What does the following query yield?

SELECT last_name, first_name, Oracle_test_score FROM students Where Oracle_test_score > (SELECT AVG(Oracle_test_score) from students)

10. . Concatenate the result returned by the following number functions.

ROUND(112.199,-2), TRUNC(112.199,-2)

11. . What are the number of days to reach the day after January 1st, 2017?

12. What is the proper code to format a column that contains numbers to represent currency? Once you have determined the format, use the code to format the number 500 to represent currency to the second decimal.

13. Give the code to concatenate the following two fields:

Catalog_ID, Catalog_Desc

14. . Name at least two reasons to construct a View.

15. Give two examples of using the CHECK constraint.

Explanation / Answer

1.       Option a is the correct answer. The syntax for creating a view in SQL is: CREATE VIEW view_name AS

2.       Only option e is the correct. All other options will give syntax errors

3.       The concerned entities are not mentioned in the question

4.       Option d (and b) is the answer. & is used to create substitution variables in SQL query, which is used for taking inputs from users

5.       Option c 1925

6.       False. Primary Key can’t have null. And any column can have NULL values.

7.       True

8.       False. The statement should be: SELECT COUNT(*) FROM employees WHERE department_id=90;

9.       This query will give last_name, first_name, Oracle_test_score of the students whose Oracle_test_score is greater than the average Oracle_test_score of all the students

10.   The ROUND(112.199,-2) and TRUNC(112.199,-2) both will return 100. So the concatenated output will be 10000. This can be achieved by: CONCAT(ROUND(112.199,-2), TRUNC(112.199,-2))

11.   …

12.   …

13.   SELECT CONCAT(Catalog_ID, Catalog_Desc)

14.   Reasons to construct a view:

a.       When there are some calculated fields

b.       When we want to restrict the users for viewing only a few columns of the table

15.   CHECK constraint is used to restrict the values entered by users. Some examples:

a.       We want the user to enter the values for an age filed to be >=18

b.       The values for a Gender field should be “Male”, “Female” or “Others”

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.