10. All Employee’s with the job as ‘CLERK’ are now called as Administrative Officers.
Update the Employee table for this.
11. Update salaries of all the SALESMAN , by increasing it by Rs.1000/-.
12. Increase the salary of KING by Rs3000/-.
13. Delete the employees who gets commission less then 100.
14. Find the SUM, AVERAGE, MINIMUM, MAXIMUM salary from the Employee table.
15. Count the number of employees in each department.
16. Count the number of items ordered by the customerid 10298. (Use items_ordered table).
17. List the Item name whose price is the highest .
18. List the sum of salary department wise.
19. Count the number of ‘CLERK’ in each department and their sum of salary.
20. List the department number who has more than 2 ANALYST.
21. Write a query using a join to determine which items were ordered by each of the customers in the customers table. Select the customerid, firstname, lastname, order_date, item, and price for everything each customer purchased in the items_ordered table.
22. List the name, department name, and employee number of all managers. Write a SQL query that will retrieve this information. Order the list in alphabetical order by department name.
23. List the employee name and department name of all employees that work in a department that has at least 3 employees. Write a SQL query to retrieve this information. Order the list in alphabetical order first by department name, then by employee name.
24. Write a SQL query that will return all the unique department numbers that are represented in the EMPLOYEE table, ordered by department number.
25. Write a SQL query to return the employee number, name and salary of all ‘CLERK’ who work in Department 10.
26. Write a SQL query to find employee number, name and job of all employees who are not managers.
27. Write a SQL query to find employee number, name and salary of all employees who get less than 1000 or more than 4000, ordered by salary.
28. Write a SQL query to find the name and employee number of all salesmen whose name begins with the letter S.
29. Use the IN operator to write a SQL query to find the name and employee number of all employees who are analysts or managers, ordered alphabetically by name.
30. Write a SQL query to find the employee number, name, salary and commission of all employees who have been paid commission and whose salary is greater than 2000.
31. Consider that the value in the Salary column is a monthly figure. Suppose that you are the owner of this company and you want to find out how much each employee would be paid per month if you increased his or her salary by 5%. Write a SQL query to get this information.
32. Again, consider that the value in the Salary column is a monthly figure. Suppose that you as the owner of this company want to compute how much each employee would be paid per month if you gave everyone a 3% pay cut. Write a SQL query to get this information.
Day3
33. Using subquery list the employee name and job of the employees whose job is same as ‘ALLEN’ job.
34. List all the details from the department table whose employees exists in the employee table. (use correlated subquery).
35. List the employee name , department number and salary of the employee whose salary is the maximum in his department.
36. List the names of the customers who have ordered items from us.
37. List the names of the employees from the employee table who are Project managers (also use the project table).
38. Using subquery list the employee name , job of the employees who do not belong to the department which in ‘CHICAGO’.
39. List the name of the project which has the highest number of persons in the project.
40. List the customerid, customer name whose order_date is the oldest one.
Day4
41. Write a program that declares and assigns values to the variables a,b,c, and then does the following :
Halves the values of a,
Doubles b,
Multiplies c by itself
42. Write a program that enables a user to input an integer. The program should then state whether the integer is evenly divisible by 5.
43. Ask the user to input two integers a, b and then determine whether or not a id divisible by b.
44. Your block should read in two real numbers and tell whether the product of the two numbers is equal to or greater than 100.
45. Write a program that inputs two numbers a,b, and thendetermines whether the product of the two numbers is greater than half of a.
46. Input a number and determine whether it is within a given range ( for example between 1 and 10). The low and high values of the range may be input by the user rather than fixed by the program.
47. Check if a given year is a leap year. The condition is year should be divisible by 4 and not divisible by 100 or divisible by 400.
48. See if a number is odd or even.
49. Check if a number is positive or negative.
50. Ask the user to enter the weight of an apple box. If the
Weight is >= 10kg, rate= Rs. 5 / Kg
Weight is <10Kg, rate= Rs.7 / Kg
Calculate the cost of the apple box.
51. Program should accept the age of the user. Depending upon the following conditions it should output:-
Age < 18 years, “child”
Age >= 18 years and <21 years “major”
Age>=21 years “adult”
52. Write a program to determine if one character string exists inside another character string.
53. Suppose the grade obtained by a student depends upon his scores and the grading rules is as follows:
Scores Grades
95-100 A
85-94 B
70-84 C
60-69 D
0-59 E
Write a block to accept a student’s marks and accordingly output his grade.
54. Write a program containing a loop that counts from 1 to 1000 using a variable I, which is incremented each time around the loop. The program should output the value of I every hundred iterations (i.e. the output should be 100, 200, etc).
55. Write a procedure which accepts the department number as an argument and updates the salary of the employees belonging to that department by 1000.
56. Write a procedure that accepts employee number as a parameter and displays the department number and salary.
57. Write a procedure to accept the employee number and display the grade of the employee using the salgrade table.
58. Write a procedure which deletes the duplicate rows in a table whose name is accepted as an argument from the user.
59. Write a function which excepts employee number as the argument and returns the hiredate for that employee.
