MID-SEM EXAM RESULT 2011

Note:- The result given has some problem as it is showing absent in subjects in which you may be present. Problem related to this will be solved after the Diwali.

Maths Paper Solution of Mid-Sem (Oct-2011)

Note:- Sorry for the inconvenience the print is not good enough, all the pictures are taken from the cell phone 

Page 1

Page 2

Page 3

Page 4

Page 5

Page 6

Page 7

Page  8

Page 9

Page 10

Page 11

Page 12

Page 13

Page 14

Page 15

Page 16

Page 17

BIS CASE STUDY- Pay Roll System

Introduction:

Pay rolling is the business practice of referring a contingent worker to a staffing vendor or pay-rolling provider so that they are the employer of record responsible for employer taxes, payroll, and all legal matters pertaining to employing workers. Different from sourcing (or recruiting) where the staffing vendor uses internal recruiters to locate contractors on behalf of the requesting company or client, pay-rolled workers are identified by the client.

Often pay-rolled workers are known to the client from previous engagements or as former employees. Because the costs of recruiting workers in to contract positions are eliminated the pay-rollees are often processed at reduced mark up rates.

In the last several years some dedicated pay-rolling companies have emerged in the staffing industry to provide pay-rolling services at greatly reduced pricing around the world. All this has resulted due to the usage of computers widely in this field. Computers have been of great help to man in dealing with accounts and finance in all possible ways.

Background:

Open source software:

Open source software is software where the ownership of the copy is transferred to the end-user and ownership of a particular copy of the software and does not remain with the software publisher; however the ownership of the copyright remains with the software publisher, the source code is also available to the user. Open source payroll software helps the user to make modifications in the software to adjust to company requirements. This helps the management to use the software without much difficulty and effort to understand each program as it has been customized according to its needs. In an open source payroll software redistribution of modifications is also allowed.

Computer payroll software:

It is a payroll software user friendly computer payroll program. Computer payroll software manages your entire payroll, calculates your payroll, including the deductions and withholdings for all relevant tax jurisdictions. Prints and maintains the paychecks, vouchers and payroll reports.

Today almost all businesses require efficient and effective payroll software to manage its employee’s wages, salaries, bonuses and overtime. A business which does not employ a payroll package cannot do with managing its employees in a proper and effective way which may result into employee dissatisfaction which may be harmful to the business in a long run. So it is very important for a company to implement payroll software in its administration to keep a healthy relationship between the employer and the employee. Business payroll software is exactly what a business needs to bridge the gap between a healthy environment of employee- employer relationship and dissatisfied employees. Business payroll software maintains employee records, payroll reports, overtime and bonus reports etc.

Phase I

Study Process - This process allows Computer Home to understand companies' payroll policies in order to implement the project successfully. It consists of interviews conducted with key personnel in the HR and Finance Department to understand more about the following:

• Payroll policies
• Processes workflow
• Data preparation for payroll processing
• Reports generated for distribution and filing
• Time and processing cycle requirements

Documentation - Documenting companies' monthly payroll process procedures. This process documents the procedures for your monthly payroll processes after Computer Home completes the first stage. The document will round up the following procedures responsibilities:

• Manage payroll policies
• Stipulated monthly cut-off date
• Define how transactions will be forwarded to Computer Home for payroll processing
• Generation of reports for distribution and filing
• When are the pay-slips updated
• Allocation of bank to be used for crediting employees' salaries
• Delegated person responsible for sending the bank diskette to the bank, statutory submissions and answer employees' queries.
• Designate formal communication channel between the company and Computer Home
• Measures to safeguard the payroll date

Phase II

System Configuration - The objective of the system configuration process is to do a proper set up for the system with default settings compliant with the company's requirement. This process delivers transition to production readiness and production cutover. Once that is done, the company's project team will take over the production support.

Data Conversion - The data conversion process defines the tasks and deliverables required to convert your employees' database. The objective of the data conversion process is to convert and test all data that is available and feasible then obtain the necessary operation of new applications. The first step of this process is to explicitly define what data is required for conversion.

Parallel Run –Computer Home will perform parallel runs to ascertain that the payroll computation is consistent with the companies' existing payroll computation. By comparing payroll reports generated from Catalyst HRMS and your existing payroll application, we will be able to fine-tune all inaccurate and incorrect settings on the payroll application.

Training – Computer Home will provide user training to key personnel involved in the outsourcing project. The user training will focus on online approval of time sheets and claim forms, submission of payroll transactions to Computer Home and downloading and viewing payroll reports.

Live Implementation - Live run will commence upon the completion of the implementation processes

Advantages and usefulness of computers:

Monthly Payroll Processing.

·         All authorized input forms and interface files will be forwarded to computer Home for input of new employees, resigned Employees, allowances & deductions, overtime hours, salary adjustments and change of bank account no.

·         After the data has been entered, an audit trail will be generated for checking against input forms.

·         After checking, Computer Home goes on to process the payroll.

·         Bank diskettes/emails are generated for salary crediting.

·         Electronic pay-slips are generated for employees every month.

·         Computer Home generates regular, timely reports on the following: - Detailed Payroll Register by employee - Summarized Payroll Register by branch and department - Net Pay Summary, including payment method - Salary Reconciliation Report - PF/ESI/Professional Tax – Income tax Queries of the employee will be handled by Computer Home

Outsourcing Benefits

·         Real Time Management - Reports can be made available to companies so that they can react to and control excessive overtime and claims. Important decisions can be made and executed in a timely fashion and this reduces turnover, rehiring or retraining.

·         Minimize External Costs - No additional costs required for software licensing, upgrade or maintenance and companies save on license charges that are applicable for end users.

·         Relive Laborious HR Tasks - Free your HR department of tedious and time-consuming administrative work such as data entry, info digging, matching and updating. Outsourcing reduces companies' burden of tight payroll deadline and takes away operating headaches of data polling, updating, checking and backup. This in turn allows the internal HR department to focus on productive HR activities such as manpower, compensation planning and settle union grievances.

Recommend Best Practices: Upon understanding and documenting the companies' payroll processes, Computer Home recommends industry best practices to streamline your processes to increase productivity and efficiency. Our recommendations include:

·         System customizations

·         Re-engineering processes

·         Introducing automation

Design Input Forms - In this process, both parties design input forms, which will be forwarded to Computer Home for payroll processing. The input form may take on many variations to accommodate different situations, for example:

·         New Employees

·         Resignations, including date resigned and any allowances or deductions to be processed

·         Promotions, including date promoted, new salary & effective date and any new allowances to be paid

·         Transfer, including new department & effective date and any new allowances to be paid

·         Allowances / Deductions to be processed

Prepare Schedules - Preparing annual payroll schedule – Computer Home will prepare a schedule for the year, ending 31 March, with exact dates for the submission and/or approval of:

·         Payroll transactions to Computer Home

·         Payroll reports to the company

·         Payroll reports and payment for outsourcing services and salaries

·         Salary crediting to bank

·         Statutory submission to the concern authorities

 Problems and their solutions:

Solutions in this category address comprehensive accounting needs: they help record, organize and interpret the financial transactions of your business. So be a “Master of the Balance Sheet” and have it all at hand, with the ability to drill down details instantly. Keep track of your numbers in every form – statements of cash flow, balance sheet, income statements and more. Hence the computers help in keeping a track on the followings:

·         Keep an uniform database for all accounting needs

·         Keep track of daily, monthly and annual accounts

·         Keep track of inventory details such as vouchers, slips, order forms, delivery forms and more

·         Plan your future business budget

·         Be organized and ready for easy auditing

·         Keep track of performance data about your business

India's Top 10 Information Technology Companies with their growth, Details & revenues ,List of Top 10 IT companies with their growth & revenues in India

Tata Consultancy Services (Rank 1)

Revenue FY11: Rs 33,112 crore

Revenue FY10: Rs 26,576 crore

Growth FY11: 25%

TCS is the largest provider of information technology services in Asia and second largest provider of business process outsourcing services in India.

Headquartered in Mumbai, TCS has over 198,500 employees.

Infosys Technologies (Rank 2)

Revenue FY11: Rs 25,997 crore

Revenue FY10: Rs 21,355 crore

Growth FY11: 22%

Infosys is the second largest IT company in India with 133,560 employees as of March 2011.

Infosys is ranked 28th globally in the list of IT services providing firms.

It has offices in 33 countries and development centers in India, China, Australia, UK, Canada, Brazil and Japan.

Wipro (Rank 3)

Revenue FY11: Rs 24,899 crore (Rs 248.99 billion)

Revenue FY10: Rs 21,949 crore (Rs 219.49 billion)

Growth FY11: 13%

Wipro is the third largest IT services company in India and employs more than 122,385 people worldwide as of March 2011.

Wipro is ranked 31 globally in 2011 in the list of IT service providers. The company does business in information technology, consumer care, lighting, engineering and healthcare businesses.

Hewlett-Packard India (Rank 4)

Revenue FY11: Rs 23,227 crore (Rs 232.27 billion)

Revenue FY10: Rs 17,831 crore (Rs 178.31 billion)

Growth FY11: 30%

HP is one of the world's largest information technology companies, operating in almost every country.

HP specialises in developing and manufacturing computing, data storage and networking hardware, designing software and delivering services. It is headquartered in Palo Alto, California.

Cognizant Technology Solutions (Rank 5)

Revenue FY11: Rs 21,393 crore (Rs 213.93 billion)

Revenue FY10: Rs 15,646 crore (Rs 156.46 billion)

Growth FY11: 37%

Cognizant Technology Solutions has beaten Wipro for the first time in the June quarter to become the third largest software exporter from India.

Cognizant is an American multinational provider of information technology, consulting and business process outsourcing services.

It has been named to the 2010 Fortune 100 fastest-growing companies for the eighth consecutive yea

IBM India (Rank 6)

Revenue FY11: Rs 14,132 crore (Rs 141.32 billion)

Revenue FY10: Rs 12,388 crore (Rs 123.88 billion)

Growth FY11: 14%

Headquarted in New York, IBM is a multinational computer technology and IT consulting corporation.

IBM has been present in India since 1992. IBM India's solutions and services span all major industries including financial services, healthcare, government, automotive, telecommunications and education.

HCL Technologies (Rank 7)

Revenue FY11: Rs 14,111 crore (Rs 141.11 billion)

Revenue FY10: Rs 10,983 crore (Rs 109.83 billion)

Growth FY11: 28%

Headquartered in Noida, HCL Technologies is the IT services arm of HCL Enterprise, a $5.5 billion IT company.

HCL Technologies is fifth largest IT company in India and is ranked 48 in the global list of IT services providers.

HCL Infosystems (Rank 8)

Revenue FY11: Rs 12,137 crore (Rs 121.37 billion)

Revenue FY10: Rs 11,956 crore (Rs 119.56 billion)

Growth FY11: 2%

HCL Infosystems, a subsidiary of HCL, has a presence in 170 locations and 300 service centres throughout India.

It offers a wide spectrum of ICT products that includes computing, storage, networking, security, telecom, imaging and retail.

Its manufacturing facilities are based in Chennai, Pondicherry and Uttarakhand. It is headquartered at Noida.

Ingram Micro India (Rank 9)

Revenue FY11: Rs 9,766 crore (Rs 97.66 billion)

Revenue FY10: Rs 7,234 crore (Rs 72.34 billion)

Growth FY11: 35%

Ingram Micro Inc is the world's largest technology distributor, providing sales, marketing and logistics services for the IT industry worldwide. Ingram Micro India is a subsidiary of this company, which operates in 34 countries.

Redington India (Rank 10)

Revenue FY11: Rs 9,274 crore (Rs 92.74 billion)

Revenue FY10: Rs 7,024 crore (Rs 70.24 billion)

Growth FY11: 32%

Redington India commenced the operations in 1993 distributing information technology products.

The company gradually expanded its operations across India covering a broad range of IT and telecom products with offices in India, West Asia and Africa.

src-rediff

DBMS PL/SQL Practical List with output of Practical 1,2 &3

L.J. INSTITUTE OF ENGINEERING AND TECHNOLOGY

BE. SEM – III CE & IT

DATABASE MANAGEMENT SYSTEM (DBMS)

PRACTICAL LIST

1. Create the following Databases.

     Salesmen

     SNUM      SNAME               CITY           COMMISSION

     -------------------------------------------------------

     1001      Piyush               London              12 %

     1002      Sejal                  Surat               13 %

     1004      Miti                   London              11 %

     1007      Rajesh               Baroda              15 %  

     1003      Anand               New Delhi           10 %

    

     SNUM      : A unique number assigned to each salesman.

     SNAME     : The name of salesman.

     CITY      : The location of salesmen.

     COMMISSION: The Salemen's commission on orders.

    

     Customers

     CNUM      CNAME               CITY      RATING    SNUM

     -------------------------------------------------------

     2001      Harsh              London      100     1001

     2002      Gita                Rome        200     1003

     2003      Lalit               Surat       200     1002

     2004      Govind              Bombay      300     1002

     2006      Chirag              London      100     1001

     2008      Chinmay             Surat       300     1007

     2007      Pratik              Rome        100     1004

     CNUM   : A unique number assigned to each customer.

     CNAME  : The name of the customer.

     CITY   : The location of the customer.

     RATING : A level of preference indicator given to this customer.

     SNUM   : The number of salesman assigned to this customer.

     Orders

     ONUM      AMOUNT         ODATE          CNUM      SNUM

     -------------------------------------------------------

     3001       18.69         10/03/97       2008      1007

     3003      767.19         10/03/97       2001      1001

     3002     1900.10         10/03/97       2007      1004

     3005     5160.45         10/03/97       2003      1002

     3006     1098.16         10/03/97       2008      1007

     3009     1713.23         10/04/97       2002      1003

     3007       75.75         10/04/97       2004      1002

     3008     4723.00         10/05/97       2006      1001

     3010     1309.95         10/06/97       2004      1002

     3011     9891.88         10/06/97       2006      1001

     ONUM   : A unique number assigned to each order.  

     AMOUNT : The amount of an order.

     ODATE  : The date of an order.

     CNUM   : The number of customer making the order.

     SNUM   : The number of salesman credited with the sale.

Practical List - 1

Solve the following queries using above databases and where clause range searching and pattern matching.

1.  Produce the order no, amount and date of all orders.

2.  Give  all  the information about all the  customers  with  salesman

    number 1001.

3.  Display the following information in the order of city, sname,  snum

    and commission.

4.  List of rating followed by the name of each customer in Surat.

5.  List of snum of all salesmen with orders in order table without  any

    duplicates.

6.  List of all orders for more than Rs. 1000.

7.  List of names and cities of all salesmen in London with  commission

    above 10%.

8.  List all customers excluding those with rating <= 100  unless  they

    are located in Rome.

9.  List all orders for more than Rs.1000 except the orders of snum<1006    

    of 10/03/97.

10. List all orders taken on October 3rd or 4th or 6th, 1997.

11. List all customers whose names begins with a letter 'C'.

12. List all customers whose names begins with letter 'A' to 'G'.

13. List all orders with zero or NULL amount.

Practical List - 2

Solve the following queries using above databases and group by clause.

1. Find out the largest orders of salesman 1002 and 1007.

2. Count all orders of October 3, 1997.

3. Calculate the total amount ordered.

4. Calculate the average amount ordered.

5. Count the no. of salesmen currently having orders.

6. Find the largest order taken by each salesman on each date.

7. Find the largest order taken by each salesman on 10/03/1997.

8. Count the no. of different non NULL cities in the Customer table.

9. Find out each customer's smallest order.

10. Find out the first customer in alphabetical order whose name  begins

    with 'G'.

11. Count the no. of salesmen registering orders for each day.

Practical List - 3

Solve the following queries using above databases and formatted output and order by clause.

1. List all salesmen with their % of commission.

2. Display the no. of orders for each day in the descending order of the

    no. of orders in the following format.

    FOR dd-mon-yy, there are __ Orders.

3. Assume  each salesperson has a 12% commission. Write a query on  the

    order table that will produce the order number, salesman no and  the

    amount of commission for that order.

4. Find the highest rating in each city in the form :

    For the city (city), the highest rating is : (rating)

5. List all in descending order of rating.

6. Calculate the total of orders for each day and place the result  in

    descending order.

Practical List - 4

Solve the following queries using above databases and join.

1. Show the name of all customers with their salesman's name.

2. List all customers and salesmen who shared a same city.

3. List all orders with the names of their customer and salesman.

4. List  all orders by the customers not located in the same  city  as

    their salesman.

5. List  all customers serviced by salespeople with  commission  above

    12%.

6. Calculate the amount of the salesman commission on each order by  a

    customer with rating above 100.

7. Find  all  pairs  of customers having  the  same  rating  with  out

    duplication.

8. Company policy is to assign each salesperson three customers,one  at

    each of the three ratings. Display all possible combination of  such

    three customers.

9. List  all  customers located in cities  where  salesman  Sejal  has

    customers.

10. Find  all pairs of customers served by a single salesman  with  the

    salesman's name and no.

11. List all salesmen who are living in the same city with out duplicate   

    rows.

12. List all pairs of orders by a given customer with customer name.

13. Produce the name and city of all the customers with the same  rating

    as Harsh.

14. Extract all orders of Miti.

15. Extract all orders of Baroda's salesmen.

16. Find all orders of the salesman who services 'Harsh'.

17. List all orders that are greater than the average of October 4,1997.

18. Find the average commission of salesmen in London.

Practical List - 5

Solve the following queries using above databases and join and subquery.

1. Find all orders attributed to salesmen in 'London' using  both  the

    subquery and join methods.

2. List the commission of all salesmen serving customers in 'London'.

3. Find all customers whose cnum is 1000 above than the snum of Sejal.

4. Count the no. of customers with the rating above than the average of

    'Surat'.

5. List all orders of the customer 'Chirag'.

6. Produce the name and rating of all customers who have above  average

    orders.

7. Select  the total amount in orders for each salesman for  whom  this

    total is greater than the amount of the largest order in the table.

8. Find all customers with orders on 3rd Oct., 1997  using  correlated

    subquery.

9. List  the  name and number of all salesmen who has  more  than  one

    customer.

10. Find all orders with amount atleast equal to the average amounts for    

    their customers.

11. Calculate  the total amount ordered on each day  eliminating  those

    days  where  the  total amount was not atleast  Rs.  2000  above  the

    maximum amount of that day.

12. Using correlated subquery, find the name and number of all customers

    with rating equal to maximum for their city.

13. Select  the name and number of all salesmen who have  customers  in

    their cities who they do not service.

14. Find  the  number of all the  salesmen  having  multiple  customers        

    using EXIST.

15. Find the name,number and city of all the salesmen  having  multiple    

    customers using EXIST.

16. Find  the name and number of all the salesmen who  serve  only  one

    customer.

17. Find all salesmen with more than one current order.

18. Display the customer information if and only if one or more of  the

    customers in are located in 'Surat.

19. Find  all salesmen who have customers with more  than  one  current

    order.

20. Find all salesmen who have customers with rating > 300 using  EXIST

    and using join.

21. List all salesmen with coustomers located in their cities  who  are

    not assigned to them.

22. write  a query to extract from the customers table  every  customer

    assigned  to a salesman who currently has atleast one other  customer

    with orders in the order table.

23. Find all salesmen with customers located in their cities  using  ANY

    and IN.

24. Find all salesmen for whom there are customers that follow them  in

    alphabetical order.

25. Find  all  customers having rating greater  than  any  customer  in

    'Rome'.

26. List all orders that has amount grater than atleat one of the orders

    from 6th October, 1997.

27. Find all orders with amounts smaller than any amount for a  customer

    in 'London'.

28. Find all the customers who have greater rating than every  customer

    in 'Rome'.

29. Select all customers whose rating doesn't match with any rating  of    

    customer of 'Surat'.

30. List all customers whose ratings are equal to or greater than ANY of

   'Sejal'.

31. Find all salesmen who have no customers located in their city  using

    ANY and ALL.

32. List  all orders for amount greater than any for the  customers  in

    London.

33. Find all salesmen and customers located in London.

34. Find out which salesman produce largest and smallest orders on  each

    date.

35. Find out which salesman produce largest and smallest orders on  each

    date in the order of order number.

36. List  salesman  no,  salesman name,cusotmer  name  &  comission  of

    salesman who have customers in their cities as well as those     who

    don't  have  in  which case the customer name  should  indicate  'NO    

    MATCH'.

Practical List - 6

Solve the following queries using above databases and table from another table and set operation.

1. Create  a  union of two queries that shows the  names,  cities  and

    ratings of all customers. Those with rating of >= 200 should display

    'HIGH RATING' and those with < 200 should display 'LOW RATING'.

2. Produce the name and number of each salesman and each customer  with

    more than one current order in the alphabetical order of names.

3. Create union of three queries. First select snum of all salesman  in

    Surat,  second, the cnum of all customers in 'Surat' and third,  the

    onum  of all orders of 3rd Oct. Retain duplicates between  the  last

    two  queries  but remove the duplicates between either of  them  and

    the first.

4. Insert  a  row into salesmen table with the values  snum  is  1005,

    salesman name is Rakesh, city is unknown and commission is 14%.

5. Insert  a row in to customer table with values London,  Pratik  and

    2005 for the columns city, name and number.

6. Create another table Londonstaff having same structure as  salesmen

    table.

7. Insert  all  the  rows of salesmen table with  city  London  in  to

    Londonstaff table.

8. Create another table Daytotals with two attributes date  and  total

    and insert rows into this table from order table.

9. Create a duplicate of the salesmen table with a name Multicust.  Now

    delete all the rows from the salesmen table.

10. Get back all the rows of salesmen table from its  duplicate  table.

Practical List - 7

Solve the following queries using above databases and delete and update.

1. Remove all orders from customer Chirag from the orders table.

2. set the ratings of all the customers of Piyush to 400.

3. Increase the rating of all customers in Rome by 100.

4. Salesman Sejal has left the company. Assign her customers to Miti.

5. Salesman Miti has resigned. Reassign her number to a  new  salesman

    Gopal whose city is Bombay and commission is 10%.

6. Double the commission of all salesmen of London.

7. Set ratings for all customers in London to NULL.

8. Suppose  we  have  a  table  called  SalesManager  with  the   same

     definition  as Salesmen table. Company decides to  promot  salesmen

     having  total   order more than 5000 to  SalesManager.Fill  up  the

     SalesManager table.

9. Assume that we have a table called smcity. Store the information of     

     all salesmen with the customers in their home cities into smcity.

10. Create  a  table Bonous that contains datewise  maximum  amount  of

     order  for all salesmen.

11. Create a table Mltcust containing the salesmen with more  than  one

     customer.

12. New  Delhi  office has closed. Remove  all  customers  assigned  to

     salesmen in New Delhi.

13. Delete all salesmen who have at least one customer with a rating of     

     100 from salesmen table.

14. Delete  all salesmen who don't have any customer with a  rating  of    

     100 from salesmen table.

15. Delete the salesmen who produce the lowest order for each day.

16. Delete  the  salesmen who produce the lowest order  for  each  day

     unless he has a commission > 12%.

17. Increase  the  commission  of all salesmen by  2%  who  have  been

     assigned at least two customers.

18. Find the smallest order for each day. Reduce the commission of  all

     salesmen by 2% who produce this order.

19. Delete all customers with no current orders.

20. Double  the rating of all customers having more than  one  current

     order.

21. Write a command to find out the orders by date.

Practical List - 8

Solve the following queries using above databases and alter table and table constraints..

1. How the onum field is forced to be an unquie?

2. Create an index to permit each salesman to find out his orders  by

     date quickly.

3. Write a command to enforce that each salesman is to have only  one

     customer of a given rating.

4. Write a command to add the item-name column to the order table.

5. Create a copy of your order table. Drop the original order table.

6. Write  a command to create the order table so that all onum  values     

     as  well  as all combinations of cnum and snum are  different  from     

     one  another  and so that NULL values are excluded  from  the  date     

     field.

7. Write  a command to create the salesmen table so that  the  default

     commission is 10% with no NULLs permitted, snum is the primary  key

     and all names contain alphabeticals only.

8. Write a command to create the order table making sure that the onum

     is greater than cnum and cnum is greater than the snum. Allow  NULL

     in any of these fields.

9. Give the commands to create our sample tables ( salesmen, customer,

     orders)  with all the necessary constraints like PRIMARY  KEY,  NOT

     NULL , UNIQUE, FOREIGN KEY.

Practical List - 9

Solve the following queries using above databases and view.

1. Create a view called Bigorders which stores all orders larger  than

     Rs. 4000.

2. Create a view Ratecount that gives the count of no. of customers at

     each rating.

3. Create  a view that shows all the customers who have  the  highest

     ratings.

4. Create a view that shows all the number of salesmen in each city.

5. Create  a view that shows the average and total  orders  for  each

     salesmen after his name and number.

6. Create a view that shows all the salesmen with multiple customers.

7. Create a view that shows all the salesmen with multiple  customers

     with rating > 200.

8. Create a view to keep track of the total no of customers  ordering,

     no of salesmen taking orders, the no of orders, the average  amount

     ordered, and the total amount ordered for each day.

9. Create  a  view Showname that shows for each order  the  order  no,

     amount, salesman name and the customer name.

10. List all orders of salesman 'Rajesh' using Showname View along with

     his commission.

11. Create  a view Maxsales to store the name and number  of  salesman,

     along with the date, who have the highest order on any given date.

12. Using above view, find out the name and number of salesman who have

     the  highest order atleast two times. Store the result  in  another

     view.

13. create a view Same city that shows the no and name and city of  the

     customers along with the city of the salesman serving them.

14. Create a view Commission of salesmen table to include only snum and

     commission  field  so that through this view someone can  enter  or

     change the commission but only to values between 10% and 20%.

15. Assume that the CURDATE is a constant representing  current  date.

     Give  a  command to create orders table with CURDATE as  a  default

     odate.

16. List all salesmen in London who had atleast one  customer  located

     there as well.

17. List all salesmen in London who didn't have any customer there.

18. Develop an input form in SQL Forms to enter the students marks  in

     order to prepare marksheet of student result.

19. Develop  an  application to maintain payroll of  employees  in  an

     organization. Use SQL Forms for data entry

Practical List – 10

Solve the following using PL/SQL Block , cursors , Procedure , Function ,Trigger

1. Write a PL/SQL block to display whether the given number is odd or even.  
2. Write a PL/SQL block to display LJIET 10 times using for loop
3. Write a PL/SQL block using cursor to update salary of a given programmer by 25%.
4. Write a PL/SQL block to display addition of all the numbers in the given range
5. Write a PL/SQL block to display the detail about given employee from EMP table
6. Write a PL/SQL block to find the salary of a given employee and raise his salary by 20%.
7. Write a PL/SQL procedure to print the following output.

*   *    *

*   *

     *

8. Create a cursor emp_cur,fetch record from emp table and check whether sal>10000 then update Grade = ‘A’ else if sal = > 5000 and sal <= 10000 then update Grade = ‘B’
9. Write a PL/SQL procedure to find the table structure of a given number
10. Write a function on programmer table to calculate number of female programmer
11. Write a function on programmer table to return total number of programmers having knowledge of specified language
12. Write a function on programmer table to return age of specified person.
13. Write a function on software table to calculate selling cost of all software of a specified person
14. Write a function on studies table to return course fee of a specified course.
15. Write a function on studies and programmer table to return number of months required to overcome the course fee he/she has studied
16. Write a PL/SQL block with cursor ,showing the use of SQL%FOUND attribute
17. Write a PL/SQL block with cursor ,showing the use of SQL% ROWCOUNT attribute
18. Write a PL/SQL block with cursor ,showing the use of SQL% ISOPEN attribute
19. Create trigger on Supplier Detail on update or insert of Sname to convert Sname into capital letter
20. Create trigger on Supplier Detail on update or insert of Scity to convert first letter of scity into capital letter.

 Output

create table salesman_21(snum number(4),sname varchar(25),city varchar(20),comm varchar(10));

create table customer_21(cnum number(4),cname varchar(25),city varchar(20),rating number(4),snum number(4));

create table orders_21(onum number(4),amount float,odate date,cnum number(4),snum number(4));

insert into salesman_21 values(1001,'piyush','london','12%');

insert into salesman_21 values(1002,'sejal','surat','13%');

insert into salesman_21 values(1004,'miti','london','11%');

insert into salesman_21 values(1007,'rajesh','baroda','15%');

insert into salesman_21 values(1003,'anand','new delhi','10%');

insert into customer_21 values(2001,'harsh','london',100,1001);

insert into customer_21 values(2002,'gita','rome',200,1003);

insert into customer_21 values(2003,'lalit','surat',200,1002);

insert into customer_21 values(2004,'govind','bombay',300,1002);

insert into customer_21 values(2006,'chirag','london',100,1001);

insert into customer_21 values(2008,'chinmay','surat',300,1007);

insert into customer_21 values(2007,'pratik','rome',100,1004);

insert into orders_21 values(3001,'18.69','10-mar-97',2008,1007);

insert into orders_21 values(3003,'767.19','10-mar-97',2001,1001);

insert into orders_21 values(3002,'1900.10','10-mar-97',2007,1004);

insert into orders_21 values(3005,'5160.45','10-mar-97',2003,1002);

insert into orders_21 values(3006,'1098.16','10-mar-97',2008,1007);

insert into orders_21 values(3009,'1713.23','10-apr-97',2002,1003);

insert into orders_21 values(3007,'75.75','10-apr-97',2004,1002);

insert into orders_21 values(3008,'4723.00','10-may-97',2006,1001);

insert into orders_21 values(3010,'1309.95','10-jun-97',2004,1002);

insert into orders_21 values(3011,'9891.88','10-jun-97',2006,1001);

Practical 1:

************

select onum,amount,odate from orders_21;

select * from customer_21 where (snum='1001');

select city,sname,snum,comm from salesman_21;

select cname,rating from customer_21 where(city='surat');

select * from orders_21 where(amount>1000);

select sname,city from salesman_21 where city='london' and comm>'10%';

select * from customer_21 where(rating>100 or city='rome');

select * from orders_21 where(amount>1000 and odate='10-mar-97' and snum>=1006);

select * from orders_21 where odate in('10-mar-97','10-apr-97','6-mar-97');

select * from customer_21 where cname like 'c%';

select * from customer_21 where cname between 'a%' and 'h%';

select * from orders_21 where (amount='0' or amount='');

***********************************************************************

Practical 2:

***********

select max(amount) from orders_21 where (snum=1002 or snum=1007);

select count(*) from orders_21 where odate='10-mar-97';

select sum(amount) from orders_21;

select avg(amount) from orders_21;

alter table salesman_21 add primary key(snum);

alter table customer_21 add primary key(cnum);

insert into salesman_21(snum) values(1001);

alter table orders_21 add primary key(onum);

alter table orders_21 add foreign key(snum) references salesman_21(snum);

alter table orders_21 add foreign key(cnum) references customer_21(cnum);

alter table customer_21 add foreign key(snum) references salesman_21(snum);

select count(*),city from customer_21 group by city;

select max(amount),snum,odate from orders_21 group by snum,odate;

select max(amount),snum,odate from orders_21 where odate='10-mar-97' group by snum,odate;

select count(*),city from customer_21 where city is not null group by city;

select min(amount),cnum from orders_21 group by cnum;

select cname from customer_21 where cname='g%' group by cname;

***********************************************************************

Practical 3:
***********

select comm,sname from salesman_21;

select 'for ' ||odate|| ' there are ' ||count(onum)|| ' orders' from orders_21 group by odate;

select onum,snum,(amount*(12/100)) from orders_21;

select 'for the city ' ||city|| ' the highest rating is ' ||max(rating) from customer_21 group by city;

select * from customer_21 order by rating desc;

select sum(amount),odate from orders_21 group by odate order by sum(amount) desc;