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

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 

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

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)


2f4de33c0fca303b8766e02b23c8644.gif (468×60)

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)

TCS office.
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)

Infosys campus, Mysore.

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)

Wipro, Noida.

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)

H-P Lab, Bangalore.

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)

Cognizant office.

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)

IBM India.

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)

HCL Technologies.

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)

HCL Infosystems.

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)

Ingram Micro.

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)

Redington India started operations in 1993.
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)

  1. Explain applications of database management system. Or Discuss the Utilities of the Database.
  2. Explain functions of DBA.
  3. What is Instance and Schema?
  4. Distinguish between DBMS & File management system?
  5. What is data model? Which are different data models?
  6. Explain different database languages ( DDL, DML, DCL)
  7. Explain three level database architecture.
  8. Explain different database users.
  9. Explain relational model.
  10. Explain: super key, candidate key, primary key, alternate key, foreign key, domain, attribute, relation.
  11. Explain different operations of relational algebra.
  12. Define: entity, entity set, relationship, relationship set, attribute, weak entity set, strong entity set.
  13. Explain ER model.
  14. Draw the ER diagram for different examples.
  15. Explain different types of attributes in ER model.
  16. Explain different types of mapping cardinalities.
  17. Explain ER diagram with all symbols and example.
  18. Explain binary versus n-ary relationship.
  19. Explain extended ER features (specialization, generalization, attribute inheritance, aggregation).
  20. Explain query processing.
  21. What are the factors for calculating the query cost?
  22. How expressions are evaluated? Or Explain materialization and pipelining.
  23. What is transaction?
  24. Explain different properties (ACID) of transaction.
  25. Explain different states of transaction.
  26. Explain system recovery.
  27. Explain two phase commit.
  28. What is concurrent execution? Explain its advantages and disadvantages.
  29. Explain three concurrency problems with example.
  30. Explain conflict serializability.
  31. Explain view serializability.
  32. Explain recoverable schedule and cascadeless schedules.
  33. How can we test whether the schedules are serializable or not?
  34. Explain different types of locks.
  35. Explain lock based protocol and two phase locking protocol.
  36. What is strict two phase locking and rigorous two phase locking?
  37. Explain lock table.
  38. What is deadlock? Explain deadlock handling.
  39. Explain deadlock prevention, deadlock detection and recovery.
  40. Explain isolation level and intent locking.
  41. Explain log based recovery.
  42. Explain deferred database modification, immediate database modification.
  43. Explain checkpoint method for transaction recovery.
  44. Explain functional dependency.
  45. Explain irreducible functional dependency.
  46. Explain trivial and nontrivial dependency.
  47. Explain closer of a set of functional dependency.
  48. Explain closure of a set of attributes.
  49. Explain normalization.
  50. Explain: 1 NF , 2 NF, 3 NF, BCNF, 4 NF, 5 NF
  51. Explain GOOD or BAD decomposition.
  52. Explain dependency preservation. Or How can we check that relations are independent or not?
  53. Give the difference between integrity and security.
  54. Explain discretionary access control and mandatory access control system.
  55. Explain audit trail.
  56. Explain data encryption with example.
  57. Explain Order by & Group by Clause.
  58. Explain i) Aggregate functions
ii) String functions
Iii) Date functions
iv) View
  1. Explain Concept of Join.
  2. Explain advantages of PL/SQL over SQL.
  3. Explain PL/SQL block structure.
  4. What is Integrity Constrain? Explain with Example.
  5. Explain SELECT & CREATE statement in SQL.
  6. Explain multivalued dependency & Join Dependency with examples.
  7. Explain DROP, UPDATE & DELETE statement in SQL.
  8. What is Cursor?
  9. What is Optimization?
  10. 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? 
  11. Solve the SQL queries based on the syllabus?
  12. 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              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.
*   *    *
*   *
     *
  1. 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’
  2. Write a PL/SQL procedure to find the table structure of a given number
  3. Write a function on programmer table to calculate number of female programmer
  4. Write a function on programmer table to return total number of programmers having knowledge of specified language
  5. Write a function on programmer table to return age of specified person.
  6. Write a function on software table to calculate selling cost of all software of a specified person
  7. Write a function on studies table to return course fee of a specified course.
  8. Write a function on studies and programmer table to return number of months required to overcome the course fee he/she has studied
  9. Write a PL/SQL block with cursor ,showing the use of SQL%FOUND attribute
  10. Write a PL/SQL block with cursor ,showing the use of SQL% ROWCOUNT attribute
  11. Write a PL/SQL block with cursor ,showing the use of SQL% ISOPEN attribute
  12. Create trigger on Supplier Detail on update or insert of Sname to convert Sname into capital letter
  13. 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;


Floating Vertical Bar With Share Buttons widget