ch10answers.pks 3.07 KB
Newer Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
/**
problem 1

Write an SQL query to display a text string 'your_first_name your_last_name' that represents your full name using the DUAL table.
*/

SELECT 'MICHAEL_LUNDQUIST' FROM DUAL;

/**
problem 2

Modify the query you created in problem 1. This time, use the INITCAP function to convert characters in the text string 'your_first_name your_last_name' to mixed case.
*/

SELECT INITCAP('MICHAEL_LUNDQUIST') FROM DUAL;

/**
problem3 

Modify the query you created in problem 1. This time, use the INSTR function to determine the position of the single blank space in the text string 'your_first_name your_last_name'.
*/

SELECT INSTR('MICHAEL LUNDQUIST', ' ') FROM DUAL;


/**
problem 4

Modify the query you created in problem 1. This time, use the SUBSTR function to extract a substring that represents your last name from the text string 'your_first_name your_last_name'. Assume you know the number of characters you need to extract.
*/

SELECT SUBSTR('MICHAEL LUNDQUIST', 9, 19) AS LASTNAME FROM DUAL;

/**
problem 5

Modify the query you created in problem 1. This time, use the INSTR and SUBSTR functions to extract a substring that represents your first name from the text string 'your_first_name your_last_name'. Assume you do not know the number of characters to extract.
*/

SELECT SUBSTR('MICHAEL LUNDQUIST', 0, INSTR('MICHAEL LUNDQUIST', ' ')) AS LASTNAME FROM DUAL;

/**
problem 6

Modify the query you created in problem 1. This time, use the INSTR, SUBSTR, and LENGTH functions to extract a substring that represents your last name from the text string 'your_first_name your_last_name'. Assume you do not know the number of characters you need to extract.
*/

SELECT SUBSTR('MICHAEL LUNDQUIST', INSTR('MICHAEL LUNDQUIST', ' '), LENGTH('MICHAEL LUNDQUIST')) AS LASTNAME FROM DUAL;

/**
problem 7

Write an SQL query to join three character strings: your first name, blank space, and your last name. Use a nested CONCAT function and the DUAL table.
*/

SELECT CONCAT('MICHAEL', CONCAT(' ', 'LUNDQUIST')) FROM DUAL;

/**
problem 8

Write an SQL query to retrieve the current date using the SYSDATE function and the DUAL table.
*/

SELECT SYSDATE FROM DUAL;

/**
problem 9

Write an SQL query to calculate the number of months until Christmas. Use the MONTHS_BETWEEN function and the DUAL table. Then, round off the answer to an integer. Decide which function - ROUND or TRANC - to be used for rounding off. Explain your reasoning.
*/

SELECT TRUNC(MONTHS_BETWEEN('25-DEC-2019', SYSDATE)) FROM DUAL;

-- I used trunc because even if there are 9 months and a few days, there are only 9 full months.

/**
problem 10

Write an SQL query to display a text string 'JANUARY 21, 2013' as a date using the TO_DATE function and the DUAL table. (Hint: correct format model to use for this string is 'MONTH DD, YYYY').
*/

 SELECT TO_DATE('JANUARY 21, 2013', 'MONTH DD, YYYY') FROM DUAL;

/**
problem 11

Write an SQL query to display a date '21-JAN-13' as a text string using the TO_CHAR function and the DUAL table. You may use any format model that is different from the default Oracle date format.
*/

SELECT TO_CHAR('21-JAN-13') FROM DUAL;