Binary Cipher
1
–> Explain set operators with examples?
Solve the following queries:
1) Write a query that displays the employee’s last names with the first letter capitalized and all other letters lowercase and the length of the name for all employees whose name starts with J, A, or M. Give each column an appropriate label. Sort the results by the employees’ last names.
2) Display the employee last name and department name for all employees who have an a (lowercase) in their last names
3) Display the last name and salary of every employee who reports to King
4) Create a report to display the employee last name, job, and hire date for all employees who started between a given range. Use SQL script operations.
5) Write a sql statement that If JOB_ID is IT_PROG, the salary increase is 10%; if JOB_ID is ST_CLERK, the salary increase is 15%; if JOB_ID is SA_REP, and the salary increase is 20%. For all other job roles, there is no increase in salary. (Use conditional expressions CASE and DECODE)
6) Write a PL/SQL program to add 10% salary to employee 176 and 102 and display the results.
2
––> What are integrity constraints? Write syntax for integrity constraints
Solve the following queries:
1) Display the last name and hire date of every employee who was hired in 1994
2) For each employee, display the employee’s last name, and calculate the number of months between today and the date the employee was hired. Label the column MONTHS_WORKED. Order your results by the number of months employed. Round the number of months up to the closest whole number.
3) Write a query to display the last name, job, department number, and department name for all employees who work in Toronto
4) Create a report to display the employee last name, job, and hire date for all employees who started between a given range. Use SQL script operations.
5) Write a sql statement that If JOB_ID is IT_PROG, the salary increase is 10%; if JOB_ID is ST_CLERK, the salary increase is 15%; if JOB_ID is SA_REP, and the salary increase is 20%. For all other job roles, there is no increase in salary. (Use conditional expressions CASE and DECODE)
6) Write a PL/SQL program to display the manager id for employee 176 and 102.
3
—> what is a sequence? How to createa sequence and explain with an example?
Solve the following queries:
1) Display each employee’s last name, hire date, and salary review date, which is the first Monday after six months of service. Label the column REVIEW. Format the dates to appear in the format similar to “Monday, the Thirty-First of July, 2000.”
2) Display the highest, lowest, sum, and average salary of all employees. Label the columns Maximum, Minimum, Sum, and Average, respectively. Round your results to the nearest whole number
3) Write a query that displays the employee numbers and last names of all employees who work in a department with any employee whose last name contains a u
4) Create a report to display the employee last name, job, and hire date for all employees who started between a given range. Use SQL script operations.
5) Create a view called SALARY_VUbased on the employee last names, department names, salaries, and salary grades for all employees. Use the EMPLOYEES, DEPARTMENTS, and JOB_GRADES tables. Label the columns Employee, Department, Salary, and Grade, respectively.
6) Write a PL/SQL program to add 10% salary to employee 176 use non pl/sql variables
4
—> what is a view? How to create a view and explain with an example?
Solve the following queries:
1) Create a query to display the last name and salary for all employees. Format the salary to be 15 characters long, left-padded with $. Label the column SALARY.
2) Display the employee last name and employee number along with their manager’s last name and manager number. Label the columns Employee, Emp#, Manager, and Mgr#, respectively.
3) Create a query that will display the total number of employees and, of that total, the number of employees hired in 1995, 1996, 1997, and 1998. Create appropriate column headings.
4) Using the DECODE function; write a query that displays the grade of all employees based on the value of the column JOB_ID, as per the following data:
JOB GRADE
AD_PRES A, ST_MAN B, IT_PROG C, SA_REP D, ST_CLERK E
None of the above 0
5) Write a sql statement that If JOB_ID is IT_PROG, the salary increase is 10%; if JOB_ID is ST_CLERK, the salary increase is 15%; if JOB_ID is SA_REP, and the salary increase is 20%. For all other job roles, there is no increase in salary. (Use conditional expressions CASE and DECODE)
6) Write a PL/SQL program to display all pl/sql variables with appropriate examples.
5
—> what is index? How to create an index and explain with an example?
Solve the following queries:
1) Create a query that displays the employees’ last names and commission amounts. If an employee does not earn commission, put “No Commission.”
2) Display the names and hire dates for all employees who were hired before their managers, along with their manager’s names and hire dates. Label the columns Employee, Emp Hired, Manager, and Mgr Hired, respectively.
3) Write a query to display each department’s name, location, number of employees, and the average salary for all employees in that department. Label the columns Name, Location, Number of People, and Salary, respectively. Round the average salary to two decimal places.
4) Create a report to display the employee last name, job, and hire date for all employees who started between a given range. Use SQL script operations.
5) Write a sql statement that If JOB_ID is IT_PROG, the salary increase is 10%; if JOB_ID is ST_CLERK, the salary increase is 15%; if JOB_ID is SA_REP, and the salary increase is 20%. For all other job roles, there is no increase in salary. (Use conditional expressions CASE and DECODE)
6) Write a PL/SQL program to display the salary of employees 176 and 102 use non pl/sql variables
6
—> what is TOP-N analysis? Explain with examples?
Solve the following queries:
1) For each employee, display the employee number, last_name, salary, and salary increased by 25% and expressed as a whole number. Label the column NewSalary.
2) Display the manager number and the salary of the lowest paid employee for that manager. Exclude anyone whose manager is not known. Exclude any groups where the minimum salary is $6,000 or less. Sort the output in descending order of salary.
3) Create a report to display the employee last name, job, and hire date for all employees who started between a given range. Use SQL script operations.
4) Create a query that will display the total number of employees and, of that total, the number of employees hired in 1995, 1996, 1997, and 1998. Create appropriate column headings.
5) Using the DECODE function; write a query that displays the grade of all employees based on the value of the column JOB_ID, as per the following data:
JOB GRADE
AD_PRES A, ST_MAN B,IT_PROG C,SA_REP D,ST_CLERK E
None of the above 0
6) Write a PL/SQL program to display the salary of employees 176 and 102 use non-plsql variables.
7
—-> Explain Sql-99 joins with an exaples?
Solve the following queries:
1) Create a query to display the employee numbers and last names of all employees who earn more than the average salary. Sort the results in ascending order of salary.
2) Display the department number, last name, and job ID for every employee in the Executive department.
3) Create a report to display the employee last name, job, and hire date for all employees who started between a given range. Use SQL script operations.
4) Create a sequence to be used with the primary key column of the DEPTtable. The sequence should start at 200 and have a maximum value of 1000. Have your sequence increment by ten numbers. Name the sequence DEPT_ID_SEQ.
5) Using the DECODE function; write a query that displays the grade of all employees based on the value of the column JOB_ID, as per the following data:
JOB GRADE
AD_PRES A ST_MAN B IT_PROG C SA_REP D ST_CLERK E
None of the above 0
6) Write a PL/SQL program to add 10% salary to employee 176 and 102 and display the results.
8
–> Explain joins with an examples ( Oracle8i) ?
Solve the following queries:
1) Write a query that displays the employee’s last names with the first letter capitalized and all other letters lowercase and the length of the name for all employees whose name starts with J, A, or M. Give each column an appropriate label. Sort the results by the employees’ last names.
2) Display the names and hire dates for all employees who were hired before their managers, along with their manager’s names and hire dates. Label the columns Employee, Emp Hired, Manager, and Mgr Hired, respectively.
3) Write a query to display the last name, job, department number, and department name for all employees who work in Toronto
4) Create a report to display the employee last name, job, and hire date for all employees who started between a given range. Use SQL script operations.
5) Write a sql statement that If JOB_ID is IT_PROG, the salary increase is 10%; if JOB_ID is ST_CLERK, the salary increase is 15%; if JOB_ID is SA_REP, and the salary increase is 20%. For all other job roles, there is no increase in salary. (Use conditional expressions CASE and DECODE)
6) Write a PL/SQL program to add 10% salary to employee 176 and 102 and display the results by using non-plsql variables.
9
––> Explain group functions with examples?
Solve the following queries:
1) Display the last name and hire date of every employee who was hired in 1998
2) Display the manager number and the salary of the lowest paid employee for that manager. Exclude anyone whose manager is not known. Exclude any groups where the minimum salary is $6,000 or less. Sort the output in descending order of salary.
3) Display the department number, last name, and job ID for every employee in the Executive department.
4) Create a report to display the employee last name, job, and hire date for all employees who started between a given range. Use SQL script operations.
5) Write a sql statement that If JOB_ID is IT_PROG, the salary increase is 10%; if JOB_ID is ST_CLERK, the salary increase is 15%; if JOB_ID is SA_REP, and the salary increase is 20%. For all other job roles, there is no increase in salary. (Use conditional expressions CASE and DECODE)
6) Write a PL/SQL program to display the manager id for employee 176 and 102 use non-plsql variables.
10
—> What are various types of date functions? Explain with examples?
Solve the following queries:
1) Display each employee’s last name, hire date, and salary review date, which is the first Monday after six months of service. Label the column REVIEW. Format the dates to appear in the format similar to “Monday, the Thirty-First of July, 2000.”
2) Display the highest, lowest, sum, and average salary of all employees. Label the columns Maximum, Minimum, Sum, and Average, respectively. Round your results to the nearest whole number
3) Write a query that displays the employee numbers and last names of all employees who work in a department with any employee whose last name contains a u and a
4) Create a report to display the employee last name, job, and hire date for all employees who started between a given range. Use SQL script operations.
5) Create a view called SALARY_VUbased on the employee last names, department names, salaries, and salary grades for all employees. Use the EMPLOYEES, DEPARTMENTS, and JOB_GRADES tables. Label the columns Employee, Department, Salary, and Grade, respectively.
6) Write a PL/SQL program to add 10% salary to employee 176 use non pl/sql variables