8th IT, All Subject Question Bank
20111024
20111021
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.
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
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
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
- Write a PL/SQL block to display whether the given number is odd or even.
- Write a PL/SQL block to display LJIET 10 times using for loop
- Write a PL/SQL block using cursor to update salary of a given programmer by 25%.
- Write a PL/SQL block to display addition of all the numbers in the given range
- Write a PL/SQL block to display the detail about given employee from EMP table
- Write a PL/SQL block to find the salary of a given employee and raise his salary by 20%.
- Write a PL/SQL procedure to print the following output.
* * *
* *
*
- 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’
- Write a PL/SQL procedure to find the table structure of a given number
- Write a function on programmer table to calculate number of female programmer
- Write a function on programmer table to return total number of programmers having knowledge of specified language
- Write a function on programmer table to return age of specified person.
- Write a function on software table to calculate selling cost of all software of a specified person
- Write a function on studies table to return course fee of a specified course.
- Write a function on studies and programmer table to return number of months required to overcome the course fee he/she has studied
- Write a PL/SQL block with cursor ,showing the use of SQL%FOUND attribute
- Write a PL/SQL block with cursor ,showing the use of SQL% ROWCOUNT attribute
- Write a PL/SQL block with cursor ,showing the use of SQL% ISOPEN attribute
- Create trigger on Supplier Detail on update or insert of Sname to convert Sname into capital letter
- Create trigger on Supplier Detail on update or insert of Scity to convert first letter of scity into capital letter.
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;
Subscribe to:
Posts (Atom)
Blog Archive
-
▼
2011
(21)
-
▼
October
(8)
- MID-SEM EXAM RESULT 2011
- Maths Paper Solution of Mid-Sem (Oct-2011)
- BIS CASE STUDY- Pay Roll System
- India's Top 10 Information Technology Companies wi...
- DBMS PL/SQL Practical List with output of Practica...
- L .J. INSTITUTE OF ENGINEERING & TECHNOLOGYSub: -...
- ASSIGNMENT 2 & 3 & PRACTICALS OF DBMS
- DFS LAB PRACTICAL (5,7,8,9)
-
▼
October
(8)