fork download
copy 
  1. 1. List the Name and Designation code of the staff who have joined before Jan 2003 and whose salary range is between 12000 and 25000. Display the columns with user defined Column headers. Hint: Use As clause along with other operators 
  2.  
  3. SQL>SELECT STAFF_NAME,DESIGN_CODE FROM STAFFMASTER WHERE (HIREDATE <'01-JAN-2003') AND STAFF_SAL BETWEEN 12000 AND 25000;
  4.  
  5. 2. List the staff code, name, and department number of the staff who have experience of 18 or more years and sort them based on their experience
  6.  
  7. SQL>SELECT STAFF_CODE,STAFF_NAME,DEPT_CODE FROM STAFFMASTER WHERE (MONTHS_BETWEEN(SYSDATE,HIREDATE))>=216  ORDER BY HIREDATE DESC;
  8.  
  9.  
  10. 3. Display the staff details who do not have manager. Hint: Use is null   
  11.  
  12. SQL>SELECT * FROM STAFFMASTER WHERE MGR_CODE IS NULL;
  13.  
  14.  
  15. 4. Display the Book details that were published during the period of 2001 to 2004. Also display book details with Book name having the character ‘&’ anywhere. 
  16.  
  17. SQL>SELECT * FROM BOOK_MASTER WHERE BOOK_PUB_YEAR BETWEEN 2001 AND 2004 AND BOOK_NAME LIKE '%[&]%';
  18.  
  19.  
  20. 5. List the names of the staff having ‘_’ character in their name.  
  21.  
  22. SQL>SELECT STAFF_NAME FROM STAFFMASTER WHERE STAFF_NAME LIKE '%_%';
  23.  
  24.  
  25.  
  26.  
  27.  
  28. 1. Create a query which will display Staff Name, Salary of each staff. Format the salary to be 15 characters long and left padded with ‘$’.  
  29.  
  30. SQL>SELECT STAFF_NAME,'$'||STAFF_SAL AS STAFF_SALARY FROM STAFFMASTER;
  31.  
  32. 2. Display name and date of birth of students where date of birth must be displayed in the format similar to “January, 12 1981” for those who were born on Saturday or Sunday. 
  33.  
  34. SQL>  SELECT STUDENT_NAME,TO_CHAR(STUDENTDOB,'MONTH DD YYYY') AS STUDENT_DOB FROM STUDENTMASTER WHERE TO_CHAR(STUDENTDOB,'DAY') LIKE  ('%SATURDAY5') OR TO_CHAR(STUDENTDOB,'DAY') LIKE  ('%SUNDAY5') ;
  35.  
  36. 3. Display each Staff name and number of months they worked for the organization. Label the column as ‘Months Worked’. Order your result by number of months employed. Also Round the number of months to closest whole number
  37.  
  38. SQL> SELECT STAFF_NAME,ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATE)) AS MONTHS_WORKED FROM STAFFMASTER;
  39.  
  40. 4. List the details of the staff who have joined in first half of December month (irrespective of the year)
  41.  
  42. SQL>SELECT * FROM STAFFMASTER WHERE TO_CHAR(HIREDATE,'DD') BETWEEN 1 AND 15 AND TO_CHAR(HIREDATE,'MONTH') LIKE '%DECEMBER%' ;
  43.  
  44. 5. Write a query that displays Staff Name, Salary, and Grade of all staff. Grade depends on the following table.  
  45. Salary Grade 
  46. Salary >=50000 A 
  47. Salary >= 25000 < 50000 B 
  48. Salary>=10000 < 25000 C 
  49. OTHERS D 
  50.  
  51. SQL>SELECT STAFF_NAME,STAFF_SAL 
  52. 	CASE 
  53. 	WHEN STAFF_SAL >=50000 THEN 'A'  
  54. 	WHEN STAFF_SAL  >25000 AND  STAFF_SAL<50000 THEN 'B' 
  55. 	WHEN STAFF_SAL  >10000 AND  STAFF_SAL<25000 THEN 'C' 
  56. 	ELSE 'D' 
  57. 	END CASE
  58. 	FROM STAFFMASTER;
  59.  
  60.  
  61.  
  62. 6. Display the Staff Name, Hire date and day of the week on which staff was hired. Label the column as DAY. Order the result by the day of the week starting with Monday.        Hint :Use to_char with hiredate and formats ‘DY’ and ’D’ 
  63.  
  64. SQL>SELECT STAFF_NAME,TO_CHAR(HIREDATE,'DD MONTH YYYY') AS HIRE_DATE,TO_CHAR(HIREDATE,'DAY')AS DAY FROM STAFFMASTER ORDER BY TO_CHAR(HIREDATE,'DAY') DESC;
  65.  
  66. 7. Write a query to find the position of third occurrence of ‘i’ in the given word ‘Mississippi’.  
  67.  
  68. SQL> SELECT INSTR('Mississippi','i',2,3) FROM DUAL;
  69.  
  70. 8. Write a query to find the pay date for the month. Pay date is the last Friday of the month. Display the date in the format “Twenty Eighth of January, 2002”. Label the heading as PAY DATE. Hint: use to_char, next_day and last_day functions 
  71.  
  72. SQL>SELECT TO_CHAR(NEXT_DAY(SYSDATE,'TUESDAY'),'DD MONTH ,YYYY') AS DAY FROM DUAL WHERE NEXT_DAY(SYSDATE,'TUESDAY')<LAST_DAY(SYSDATE) ;
  73.  
  74.  
  75. 9. Display Student code, Name and Dept Name. Display “Electricals” if dept code = 20, “Electronics” if Dept code =30 and “Others” for all other Dept codes in the Dept Name column. Hint : Use Decode   
  76.  
  77.  
  78.  
Not running #stdin #stdout 0s 0KB
comments (?)
stdin
copy 
Standard input is empty
stdout
copy 
Standard output is empty
https://ideone.com/a6mHum
language:
Text (text 6.10)
created:
4 years ago
visibility:
public

Share or Embed source code

Discover > Sphere Engine API

The brand new service which powers Ideone!

Discover > IDE Widget

Widget for compiling and running the source code in a web browser!