Oracle function Exercises

Show all employee names in lowercase to show the names of all employees in uppercase format. Displays the name of the employee who is exactly 5 characters. Displays the first three characters of all employee names. Displays the names of all employees in uppercase letters, followed by lowercase. Displays all employee names in uppercase after the first letter lowercase. Show the names of all employees, replace all "a" with "I am the tiger" Find the name and date of the employee who has been recruited for more than 8 months and shows the number of employees who have been in service for 10 years. For each employee, displays the number of days it joined the company. Find all employees employed on the 3rd day of the month. When showing a salary, add the local currency unit to the previous show all employees in 1981 showed all employees who were in employment in October

Case 1:
Problem:
A company printed a number of recharge cards, card passwords are randomly generated, now this problem: Card inside the "O and 0" (oh and 0) "I and 1" (EH and a), users reflect that not clear, the company decided to store in the database in the password of all the "Oh" changed to "0", all the "I" are changed to "1";
Write SQL statements to achieve the above requirements; database table name: Card; password field name: PassWord

Case 2:
Problem:
The following character data is available in the database table, such as:
13-1, 13-2, 13-3, 13-10, 13-100, 13-108, 13-18, 13-11, 13-15, 14-1, 14-2
Now you want to sort by an SQL statement, and first you sort by the numbers in the first half, and then the numbers in the second half are sorted, and the output is sorted like this:
13-1, 13-2, 13-3, 13-10, 13-11, 13-15, 13-18, 13-100, 13-108, 14-1, 14-2
Write SQL statements to achieve the above requirements; database table name: sellrecord; field Name: Listnumber
Code:
–1, select lower (ename) from EMP;
–2, select Upper (ename) from EMP;
–3, select ename from emp where length (ename) = 5;
–4, select substr (ename,1,3) from EMP;
–5, select Upper (substr (ename,1,1)) | | Lower (substr (ename,2)) from EMP;
–6, select Lower (substr (ename,1,1)) | | SUBSTR (ename,2) from EMP;
–7, select Replace (ename, ' a ', ' I am Tiger ') from EMP;
–8, select ename from emp where (add_months (hiredate,8) <=sysdate);
–9, select Ename,hiredate from emp where (add_months (hiredate,120) <=sysdate);
–10, select Floor (sysdate-hiredate) from EMP;
Select Ceil (sysdate-hiredate) from EMP;
Select Trunc (sysdate-hiredate) from EMP;
–11, select ename from emp where hiredate = (Last_day (hiredate)-2);
–12, select ename, To_char (Sal, ' L9999.99 ') from EMP;
–13, select Ename,hiredate from emp where (To_char (hiredate, ' yyyy ') = 1981;
–14, select Ename,hiredate from emp where (To_char (hiredate, ' mm ')) = 10;
– Case 1, CREATE TABLE card (PassWord varchar2 (20));
Insert into the card values (' o345i ');
Insert into the card values (' o702i ');
Update card set Password=replace (replace (PassWord, ' o ', ' 0 '), ' I ', ' 1 ');
– Case 2, CREATE table Sellrecord (Listnumber varchar2 (20));
INSERT into Sellrecord values (' 13-1 ');
INSERT into Sellrecord values (' 13-2 ');
INSERT into Sellrecord values (' 13-3 ');
INSERT into Sellrecord values (' 14-1 ');
INSERT into Sellrecord values (' 14-2 ');
Select Listnumber from Sellrecord to To_number (substr (Listnumber,1,instr (Listnumber, '-')-1), To_number ( Listnumber,instr (Listnumber, '-') +1));