sqllearn

 --1. FIRST DAY OF PRESENT YAER  select to_char(trunc(sysdate,'yyyy'),'day') from dual; --2. Next year first day select to_char (add_months(trunc(sysdate,'yyyy'),12),'day') from dual; --3. next year first monday date. select  next_day(add_months(trunc(sysdate,'yyyy'),12),'monday') from dual; --4. FIRST DAY OF THE CURRENT WEEK (IT ALWAYS STARTS WITH SUNDAY)  select trunc(sysdate) from dual; --5. FIRST DAY OF THE NEXT WEEK  select trunc(sysdate)+ 7 from dual; --6. FIRST DAY OF THE PREVIOUS WEEK  select trunc(sysdate - 7 ) from dual; --7. FIRST DAY OF THE NEXT MONTH  select to_char(add_months ( trunc(sysdate,'Mon'),1),'day') from dual; --8. FIRST DAY OF THE PREVIOUS MONTH  select to_char(add_months ( trunc(sysdate,'Mon'),-1),'day') from dual; --9. LAST DAY OF THE PREVIOUS MONTH  select  to_char(trunc(sysdate,'Mon')-1,'day') from dual; --10. First day of the current ...

Exceptions: Error occurring at run time is to handle the error is called exception. Warring or error code is called an exception. Exceptions can be internally defined (by the run-time system) or user defined 1.      internally defined exceptions 2.      user defined exceptions Internally Defined Exceptions: common internal exceptions have predefined names, such as ZERO_DIVIDE and STORAGE_ERROR. The other internal exceptions can be given names.    Predefined PL/SQL Exceptions: An internal exception is raised implicitly whenever your PL/SQL program violates an Oracle rule. Exception Oracle Error SQLCODE Value ACCESS_INTO_NULL ORA-06530 -6530 CASE_NOT_FOUND ORA-06592 -6592 COLLECTION_IS_NULL ORA-06531 -6531 CURSOR_ALREADY_OPEN ORA-06511 -6511 D...

Pragma is statement it processed at compile time It pass information to complier

SELECT   e1.sal FROM employee e1 WHERE &N = (SELECT COUNT(DISTINCT (sal) )             FROM employee e2             WHERE e1.sal <= e2.sal);

SIMPLE RANK FUNCTION: CREATE TABLE STUDENT   (     S_ID        NUMBER(10),     S_NAME      VARCHAR(30),     TAMIL_MARKS NUMBER(10),     ENGLISH_MARKS NUMBER(10),     MATHS_MARKS NUMBER(10),     SCIENCE_MARKS NUMBER(10),     S_SCIENCE_MARKS NUMBER(10)   );      INSERT INTO STUDENT VALUES(1,'PANDIAN',35,45,55,65,75);   INSERT INTO STUDENT VALUES(2,'MUTHU',37,47,57,67,77);   INSERT INTO STUDENT VALUES(3,'ASHOK',40,50,60,70,80);      INSERT INTO STUDENT VALUES(4,'KUMAR',33,43,53,63,73);   INSERT INTO STUDENT VALUES(5,'RANA',36,46,56,66,76);         SELECT * FROM STUDENT; 1 PANDIAN 35 45 55 65 75 2 MUTHU 37 47 57 67 77 3 ASHOK 40 50 60 70 80 4 KUMAR 33 43 53 63 73 5 RANA 36 46 56 66 76  SELECT S_NAME,TOTAL,R...

    Scenarios: ---1. Display the client names who do not have a fax number SELECT CLIENT_NAME FROM CLIENT_2 WHERE FAX IS NULL; --2. Display the clients that have more than one project SELECT CLIENT_ID,COUNT(PROJECT_ID) FROM ASSIGNMENT_2 GROUP BY CLIENT_ID HAVING COUNT(PROJECT_ID) >1; --3. Display the project titles and their expected durations in years for all the projects that are still not closed SELECT  TITLE FROM PROJECT_2 WHERE TO_CHAR(EXPECTED_END_DATE,'YYYY') = TO_CHAR(SYSDATE,'YYYY'); --4. Display the no of women resources from India SELECT COUNT(RES_ID) FROM RESOURCE_2 WHERE GENDER = 'FEMALE' AND COUNTRY ='INDIA'; --5. Display the resource name and no of projects under which they are working as on today SELECT  R.RES_NAME,COUNT(A.PROJECT_ID) FROM ASSIGNMENT_2 A,RESOURCE_2 R,PROJECT_2 P WHERE R.RES_ID = A.RES_ID AND P.PROJECT_ID = A.PROJECT_ID AND A.ASSIGNED_DATE  BETWEEN P.START_DATE AND SYSDATE GR...