L .J. INSTITUTE OF ENGINEERING & TECHNOLOGY
Sub:- Basic Electronics
Note:- Sorry for the inconvenience the print is not good enough, all the pictures are taken from the cell phone
8th IT, All Subject Question Bank
Download all question Bank
[Link contains question bank of Advance Computer Network(ACN), Data Compression(DC) & Design and Analysis of Algorithm(DAA) ]
Download all MSE-I Syllabus
20110930
20110928
Business Information System(BIS) Question Bank, Notes & Case Studies
L .J. INSTITUTE OF ENGINEERING & TECHNOLOGY
Sub:- Business Information System(BIS)
[Question Bank + Notes+ Case Studies ]
for December 2011 Examination
for December 2011 Examination
GTU B.E. SEM – III IT-2011
Documents
Ch-1,5,6 (Refer H.D Clifton)Ch-4,7,9,10(Refer Tech-Max)Ch2,3,8 (Refer Notes available on below link)Case Studies(available on below link)
20110922
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
Digital Logic & Computer Design by Morish Mano, Database System Concepts by Henry Korth, Let us C by Yashavant P. Kanetkar, An Introduction To Database Systems by Chris J. Date
Author(s): M. Moris Mano
Publisher: Prentice Hall College Div (April 1979)
Pages : 612
Format : PDF
Language : English
ISBN-10: 0132145103
ISBN-13: 978-0132145107
-----------------------------------------------------------------------------------------------------------------------------------
Database System Concepts, 5/e, is intended for a first course in databases at the junior or senior undergraduate, or first-year graduate, level. In addition to basic material for a first course, the text contains advanced material that can be used for course supplements, or as introductory material for an advanced course.
Key Handles: • Early coverage of SQL in two chapters • Think of SQL as doing or creating Queries • Silberschatz uses a bank analogy throughout his text with Running Examples • Case studies are incorporated that represent a different database, this is in the last Part of the text • Focuses on cutting edge material, such as xml, web based database systems
----------------------------------------------------------------------------------------------------------------------------------
Yashavant P. Kanetkar is an Indian computer science author, known for his books on programming languages.
He has authored several books on topics such as C, C++, VC++, C#, .NET, DirectX and COM programming. He is also a popular speaker on various technology subjects and is a regular columnist for publications Express Computers and Developer 2.0. His best-selling books include Let Us C, Understanding Pointers In C and Test Your C Skills. These books are specific to the long outdated Turbo C compilers, and do not contain reliable information on standard C language. He has created, moulded and groomed many of IT professionals in the last decade and half. He has been awarded the "Best .NET Technical Contributor" and "Microsoft Most Valuable Professional" awards by Microsoft.His books still remain popular inside the Indian Subcontinent and is one of the major materials for IT companies placement preparation.
--------------------------------------------------------------------------------------------------------------------------------
Details of Book: An Introduction To Database Systems
Book: An Introduction To Database Systems
Author: Chris J. Date
ISBN: 0321197844
ISBN-13: 9780321197849, 978-0321197849
Publishing Date: Jul 2003
Publisher: Addison Wesley Longman
Edition: 8th Edition
Language: English
CLICK HERE TO DOWNLOAD THIS BOOK
20110917
L .J. INSTITUTE OF ENGINEERING & TECHNOLOGY
Sub: - Database Management System
Question Bank
GTU B.E. SEM – III (CE & IT-2011)
Note: This is the total syllabus of DBMS. The yellow points and yellow question are syllabus and questions for MSE – I.
1) Database System Concepts, fifth edition,
By Abraham Silberschatz, Henry F. Korth, S. Sudarshan
Topics: 1.1 to 1.5, 2.1 to 2.4, 6.1 to 6.9, 13.1 to 13.5, 13.7, 14.1 to 14.5,
15.1, 15.2, 15.4 to 15.8, 16.1.1 to 16.1.4, 16.6, 17.3, 17.4
2) An Introduction to Database Systems, eighth edition
By C. J. Date, A kannan, S. Swamynathan
Topics: 2.1 to 2.7, 11.1 to 11.6, 12.1 to 12.5, 13.1 to 13.3, 15.1 to 15.5, 16.1 to 16.4,
16.8, 16.9, 17.1 to 17.3, 17.5
3) SQL, PL/SQL The programming language of ORACLE, 3rd revised edition,
By Ivan Bayross
Topics: ch-7, ch-8, ch-9, ch-10, ch-11 (page 251-256), ch-12, ch-15, ch-16(page
336- 345), ch-18(379-386,403-407)
- Explain applications of database management system. Or Discuss the Utilities of the Database.
- Explain functions of DBA.
- What is Instance and Schema?
- Distinguish between DBMS & File management system?
- What is data model? Which are different data models?
- Explain different database languages ( DDL, DML, DCL)
- Explain three level database architecture.
- Explain different database users.
- Explain relational model.
- Explain: super key, candidate key, primary key, alternate key, foreign key, domain, attribute, relation.
- Explain different operations of relational algebra.
- Define: entity, entity set, relationship, relationship set, attribute, weak entity set, strong entity set.
- Explain ER model.
- Draw the ER diagram for different examples.
- Explain different types of attributes in ER model.
- Explain different types of mapping cardinalities.
- Explain ER diagram with all symbols and example.
- Explain binary versus n-ary relationship.
- Explain extended ER features (specialization, generalization, attribute inheritance, aggregation).
- Explain query processing.
- What are the factors for calculating the query cost?
- How expressions are evaluated? Or Explain materialization and pipelining.
- What is transaction?
- Explain different properties (ACID) of transaction.
- Explain different states of transaction.
- Explain system recovery.
- Explain two phase commit.
- What is concurrent execution? Explain its advantages and disadvantages.
- Explain three concurrency problems with example.
- Explain conflict serializability.
- Explain view serializability.
- Explain recoverable schedule and cascadeless schedules.
- How can we test whether the schedules are serializable or not?
- Explain different types of locks.
- Explain lock based protocol and two phase locking protocol.
- What is strict two phase locking and rigorous two phase locking?
- Explain lock table.
- What is deadlock? Explain deadlock handling.
- Explain deadlock prevention, deadlock detection and recovery.
- Explain isolation level and intent locking.
- Explain log based recovery.
- Explain deferred database modification, immediate database modification.
- Explain checkpoint method for transaction recovery.
- Explain functional dependency.
- Explain irreducible functional dependency.
- Explain trivial and nontrivial dependency.
- Explain closer of a set of functional dependency.
- Explain closure of a set of attributes.
- Explain normalization.
- Explain: 1 NF , 2 NF, 3 NF, BCNF, 4 NF, 5 NF
- Explain GOOD or BAD decomposition.
- Explain dependency preservation. Or How can we check that relations are independent or not?
- Give the difference between integrity and security.
- Explain discretionary access control and mandatory access control system.
- Explain audit trail.
- Explain data encryption with example.
- Explain Order by & Group by Clause.
- Explain i) Aggregate functions
ii) String functions
Iii) Date functions
iv) View
- Explain Concept of Join.
- Explain advantages of PL/SQL over SQL.
- Explain PL/SQL block structure.
- What is Integrity Constrain? Explain with Example.
- Explain SELECT & CREATE statement in SQL.
- Explain multivalued dependency & Join Dependency with examples.
- Explain DROP, UPDATE & DELETE statement in SQL.
- What is Cursor?
- What is Optimization?
- Solve the small problems on Functional Dependency given in the book, such as whether the decomposition is lossy or lossless? Whether it is dependency preserving? Whether it is irreducible?
- Solve the SQL queries based on the syllabus?
- Explain the steps in Query Processing?
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
1002 Sejal Surat
1004 Miti London
1007 Rajesh Baroda
1003 Anand New Delhi
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
2002 Gita Rome
2003 Lalit Surat
2004 Govind Bombay
2006 Chirag London
2008 Chinmay Surat
2007 Pratik Rome
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
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
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
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
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
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
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
6. Double the commission of all salesmen of London
7. Set ratings for all customers in London
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
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
there as well.
17. List all salesmen in London
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)
