CREATE TABLE PASSENGER(P_NAME VARCHAR(30),TICKET_NO NUMBER(10),
COACH NUMBER(20))
INSERT INTO PASSENGER(P_NAME,TICKET_NO,COACH)
VALUES(&P_NAME,&TICKET_NO,&COACH)
SELECT
CASE
WHEN (LENGTH(REPLACE(LOWER(ENAME), 'l', '')) <= LENGTH(ENAME) - 2) OR
(LENGTH(REPLACE(LOWER(ENAME), 'r', '')) <= LENGTH(ENAME) - 2)
THEN LOWER(ENAME) || ' ' || UPPER(JOB) || ' ' || SUBSTR(ENAME, 1, 3)
ELSE UPPER(ENAME) || ' ' || LOWER(JOB) || ' ' ||
REVERSE(SUBSTR(REVERSE(JOB), 1, 3))
END AS result
FROM EMP
7TH
1 SELECT
2 E.ENAME AS Employee_Name,
3 M.ENAME AS Manager_Name,
4 MM.ENAME AS Managers_Manager_Name,
5 D.DNAME AS Department_Name
6 FROM
7 EMP E
8 JOIN
9 EMP M ON E.MGR = M.EMPNO
10 JOIN
11 EMP MM ON M.MGR = MM.EMPNO
12 JOIN
13 DEPT D ON E.DEPTNO = D.DEPTNO
14 WHERE
15 E.JOB = (SELECT JOB FROM EMP WHERE ENAME = 'ALLEN')
16 OR E.JOB = (SELECT JOB FROM EMP WHERE ENAME = 'MILLER')
17 AND TO_CHAR(M.HIREDATE,'YY') = 81
18* AND D.LOC LIKE '%LL%'
SQL> /
EMPLOYEE_N MANAGER_NA MANAGERS_M DEPARTMENT_NAM
---------- ---------- ---------- --------------
SMITH FORD JONES RESEARCH
ALLEN BLAKE KING SALES
WARD BLAKE KING SALES
MARTIN BLAKE KING SALES
TURNER BLAKE KING SALES
1 SELECT LOC
2 FROM DEPT JOIN EMP
3 ON DEPT.DEPTNO =EMP.DEPTNO
4 WHERE SAL = (SELECT SAL
5 FROM(SELECT ROWNUM SLRO ,SAL
6 FROM (SELECT DISTINCT SAL
7 FROM EMP
8 ORDER BY SAL DESC))
9* WHERE SLRO = 7)
SQL> /
LOC
-------------
CHICAGO
