Thursday, 11 December 2014

How to Check Airtel 4g LTE Dongle Data Usage

Airtel 4G - Fourth Generation Technology provides upto 5 times faster speeds than 3G making it suitable for all type of high speed applications and services. It is also touted to be the fastest wireless internet service. The latest after 2G and 3G which gives you a faster and rich internet experience. With 4G hi-speeds, you can notice a visible difference while downloading files, doing video-chat, playing multiplayer games or while viewing HD videos online.




How to Check Airtel 4g LTE Data Usage

To check airtel data usage click on the link below:




Artel 4G can be used at home or your office for :


  •  Wireless internet access.
  • Access rich content and multimedia applications eg: Movies, HD videos, e-learning, HD content and more.
  •  multiple downloads of heavy files, high definition multimedia content at the same time will be possible.
  • Higher uplink and downlink speeds.
  • Seamless online streaming – ZERO buffering.
  •  High definition online gaming Access business applications that require higher speeds and last mile connectivity.
  •  Face-2-face video chat seamlessly.
READ MORE ...

Monday, 15 September 2014

Windows: How to Hide/Unhide Hard Disk Partition using Command Prompt

People generally use drive locker to lock or unlock hard drive partition on their systems to protect data from unauthorized access. Use of software for this task is quite tricky when you forget password because in general life it is difficult to manage passwords from your laptop to ATM. 

So to get rid of this problem we can suggest you to use this method using which you can hide or unhide hard disk partition your windows laptops without use of any software.






















So let's start diskpart prompt to do our task. For this open Command Prompt, it will be much better when you open "CMD" as an administrator.


Now, on cmd prompt type "Diskpart". After few seconds you will be prompted to "Diskpart" prompt.


Type "List Vol" so that you can see all the volumes or drives attached to your PCs as shown in the above figure. Using this command you can find the particular drive information like it's volume number, letters associated with it, labels (if any), file system type, size, status and info.

Now select the dive letter which you want to remove as:

DISKPART>  list vol
DISKPART> select vol 4

Finally remove the letter associated with this drive.

DISKPART> remve letter E 

When you press enter you will get a message : Diskpart successfully removed the drive letter or mount point. After completion of this step your selected drive will be no longer visible to you and no doubt others.


To gain access to this partition again you need to follow the same steps but in place of remove letter you need to enter assign letter. So lets look into this method:


To bring back or unhide partition , select volume which you want to unhide and assign letter to the selected volume. In some cases when you release a particular letter windows automatically assign it to others,  so first check whether the letter which you are going to assign is not in use, otherwise you can assign any other letter.

DISKPART>  list vol
DISKPART>  select vol 4
DISKPART> assign letter E 

After the completion of this step you can see the particular volume. For more tech stuff stay connected on G+, Facebook, Twitter.
READ MORE ...

Sunday, 31 August 2014

Lyrics: Gulabi Aankhein Jo Teri Dekhi Md.Rafi

Gulabi Aankhein Jo Teri Dekhi (Md.Rafi)
Movie/Album: The Train (1970)
Music By: R. D. Burman
Lyrics By: Anand Bakshi
Performed By: Mohammed Rafi


Gulaabi Aankhen Jo Teri Dekhi
Sharaabi Yeh Dil Ho Gaya
Sambhaalo Mujhko O Mere Yaaron
Sambhalna Mushkil Ho Gaya

Dil Mein Mere Khwaab Tere
Bas Tere Jaise Ho Deewaar Pe
Tujhpe Fida Main Kyoon Hua
Aata Hai Gussa Mujhe Pyaar Pe
Main Lut Gaya Maanke Dil Ka Kaha
Main Kahin Tha Na Raha

Kya Kahoon Main Dilruba
Pura Yeh Jaadu Teri Aankhon Ka
Yeh Mera Kaatil Ho Gaya
Gulaabi Aankhen Jo Teri Dekhi
Sharaabi Yeh Dil Ho Gaya

Maine Sada Chaaha Yehi
Daaman Bacha Loon Haseenon Se Main
Teri Kasam Khwaabon Mein Bhi
Bachta Fida Naazneenon Se Main
Tauba Magar Mil Gayi Tujhse Nazar
Mil Gaya Dard-E-Jigar

Sun Zara O Bekhabar
Zara Sa Haske Jo Dekha Tune
Main Tera Bismil Ho Gaya
Gulaabi Aankhen Jo Teri Dekhi
Sharaabi Yeh Dil Ho Gaya
Sambhaalo Mujhko O Mere Yaaron
Sambhalna Mushkil Ho Gaya

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

गुलाबी आँखें, जो तेरी देखी, शराबी ये दिल हो गया
सम्भालो मुझको, ओ मेरे यारों, सम्भलना मुश्किल हो गया

दिल में मेरे, ख़्वाब तेरे, तस्वीरें जैसे हों दीवार पे
तुझपे फ़िदा, मैं क्यूँ हुआ, आता है गुस्सा मुझे प्यार पे
मैं लुट गया, मान के दिल का कहा
मैं कहीं का ना रहा, क्या कहूँ मैं दिलरुबा
बुरा ये जादू तेरी आँखों का, ये मेरा क़ातिल हो गया
गुलाबी आँखें जो तेरी देखी...

मैंने सदा, चाहा यही, दामन बचा लूं हसीनों से मैं
तेरी क़सम, ख़्वाबों में भी, बचता फिरा नाज़नीनों से मैं
तौबा मगर, मिल गई तुझसे नज़र
मिल गया दर्द-ए-जिगर, सुन ज़रा ओ बेख़बर
ज़रा सा हँस के, जो देखा तूने, मैं तेरा बिस्मिल हो गया
गुलाबी आँखें जो तेरी देखी......

READ MORE ...

Saturday, 30 August 2014

COMPUTER NETWORKS - VTU CSE/ISE CN LAB MANUAL

NETWORKS LABORATORY: CN LAB PROGRAMS FOR VTU CSE/ISE STUDENTS
Subject Code : 06CSL77
IA Marks : 25
No. of Practical Hrs./ Week : 03
Exam Hours : 03
Total No. of Practical Hrs. : 42
Exam Marks : 50


Note: Student is required to solve one problem from PART-A and one
problem from PART-B. The questions are allotted based on lots. Both
questions carry equal marks.




PART - A

SIMULATION EXERCISES

The following experiments shall be conducted using either NS / OPNET or
any other suitable simulator.
1. Simulate a three nodes point – to – point network with duplex links
between them. Set the queue size and vary the bandwidth and find the
number of packets dropped.

2. Simulate a four node point-to-point network with the links connected as
follows:
n0 – n2, n1 – n2 and n2 – n3. Apply TCP agent between n0-n3 and UDP
between n1-n3. Apply relevant applications over TCP and UDP agents
changing the parameter and determine the number of packets sent by
TCP / UDP.

3. Simulate the different types of Internet traffic such as FTP and TELNET
over a network and analyze the throughput.

4. Simulate the transmission of ping messages over a network topology
consisting of 6 nodes and find the number of packets dropped due to
congestion.

5. Simulate an Ethernet LAN using n nodes (6-10), change error rate and
data rate and compare throughput.


6. Simulate an Ethernet LAN using n nodes and set multiple traffic nodes
and determine collision across different nodes.

7. Simulate an Ethernet LAN using n nodes and set multiple traffic nodes
and plot congestion window for different source / destination.

8. Simulate simple ESS and with transmitting nodes in wire-less LAN by
simulation and determine the performance with respect to transmission
of packets.

PART - B

Implement the following in C/C++:
1. Write a program for error detecting code using CRC-CCITT (16- bits).

2. Write a program for frame sorting technique used in buffers.

3. Write a program for distance vector algorithm to find suitable path for
transmission.

4. Using TCP/IP sockets, write a client – server program to make the client
send the file name and to make the server send back the contents of the
requested file if present.

5. Implement the above program using as message queues or FIFOs as IPC
channels.

6. Write a program for simple RSA algorithm to encrypt and decrypt the
data.

7. Write a program for Hamming code generation for error detection and
correction.

8. Write a program for congestion control using leaky bucket algorithm.
READ MORE ...

Friday, 29 August 2014

VTU: COMPUTER GRAPHICS LAB PROGRAMS FOR 6th SEM

COMPUTER GRAPHICS AND VISUALIZATION LABORATORY - VTU

Subject Code : 06CSL67 IA Marks : 25
No. of Practical Hrs./ Week : 03 Exam Hours : 03
Total No. of Practical Hrs. : 42 Exam Marks : 50










PART - A

IMPLEMENT THE FOLLOWING PROGRAMS IN C / C++

1. Program to recursively subdivide a tetrahedron to from 3D Sierpinski
gasket. The number of recursive steps is to be specified by the user.

2. Program to implement Liang-Barsky line clipping algorithm.

3. Program to draw a color cube and spin it using OpenGL transformation
matrices.

4. Program to create a house like figure and rotate it about a given fixed
point using OpenGL functions.

5. Program to implement the Cohen-Sutherland line-clipping algorithm.
Make provision to specify the input line, window for clipping and view
port for displaying the clipped image.

6. Program to create a cylinder and a parallelepiped by extruding a circle
and quadrilateral respectively. Allow the user to specify the circle and
the quadrilateral.

7 Program, using OpenGL functions, to draw a simple shaded scene
consisting of a tea pot on a table. Define suitably the position and
properties of the light source along with the properties of the properties
of the surfaces of the solid object used in the scene.

8. Program to draw a color cube and allow the user to move the camera
suitably to experiment with perspective viewing. Use OpenGL functions.

9. Program to fill any given polygon using scan-line area filling algorithm.
(Use appropriate data structures.)

10. Program to display a set of values { fij } as a rectangular mesh.

PART - B
Develop a suitable Graphics package to implement the skills learnt in the
theory and the exercises indicated in Part A. Use the OpenGL.
READ MORE ...

VTU CSE/ISE WEB PROGRAMMING LAB MANUAL

WEB PROGRAMMING LABORATORY 7th SEM


Subject Code : 06CSL78
IA Marks : 25
No. of Practical Hrs./ Week : 03
Exam Hours : 03
Total No. of Practical Hrs. : 42
Exam Marks : 50







1. Develop and demonstrate a XHTML document that illustrates the use
external style sheet, ordered list, table, borders, padding, color, and the
tag.

2. Develop and demonstrate a XHTML file that includes Javascript script
for the following problems:
a) Input: A number n obtained using prompt
Output: The first n Fibonacci numbers
b) Input: A number n obtained using prompt
Output: A table of numbers from 1 to n and their squares using alert

3. Develop and demonstrate a XHTML file that includes Javascript script
that uses functions for the following problems:
a) Parameter: A string
Output: The position in the string of the left-most vowel
b) Parameter: A number
Output: The number with its digits in the reverse order

4. a) Develop and demonstrate, using Javascript script, a XHTML
document that collects the USN ( the valid format is: A digit from 1 to 4
followed by two upper-case characters followed by two digits followed by
two upper-case characters followed by three digits; no embedded spaces
allowed) of the user. Event handler must be included for the form element
that collects this information to validate the input. Messages in the alert
windows must be produced when errors are detected.
b) Modify the above program to get the current semester also
(restricted to be a number from 1 to 8)

5. a) Develop and demonstrate, using Javascript script, a XHTML
document that contains three short paragraphs of text, stacked on top of
each other, with only enough of each showing so that the mouse cursor
can be placed over some part of them. When the cursor is placed over the
exposed part of any paragraph, it should rise to the top to become
completely visible.
b) Modify the above document so that when a paragraph is moved from
the top stacking position, it returns to its original position rather than to
the bottom.

6. a) Design an XML document to store information about a student in an
engineering college affiliated to VTU. The information must include
USN, Name, Name of the College, Brach, Year of Joining, and e-mail id.
Make up sample data for 3 students. Create a CSS style sheet and use it to
display the document.
b) Create an XSLT style sheet for one student element of the above
document and use it to create a display of that element.

7. a) Write a Perl program to display various Server Information like
Server Name, Server Software, Server protocol, CGI Revision etc.
b) Write a Perl program to accept UNIX command from a HTML form
and to display the output of the command executed.

8. a) Write a Perl program to accept the User Name and display a
greeting message randomly chosen from a list of 4 greeting messages.
b) Write a Perl program to keep track of the number of visitors visiting
the web page and to display this count of visitors, with proper headings.

9. Write a Perl program to display a digital clock which displays the
current time of the server.

10. Write a Perl program to insert name and age information entered by
the user into a table created using MySQL and to display the current
contents of this table.

11. Write a PHP program to store current date-time in a COOKIE and
display the ‘Last visited on’ date-time on the web page upon reopening of
the same page.

12. Write a PHP program to store page views count in SESSION, to
increment the count on each refresh, and to show the count on web page.

13. Create a XHTML form with Name, Address Line 1, Address Line 2,
and E-mail text fields. On submitting, store the values in MySQL table.
Retrieve and display the data based on Name.

14. Using PHP and MySQL, develop a program to accept book
information viz. Accession number, title, authors, edition and publisher
from a web page and store the information in a database and to search for
a book with the title specified by the user and to display the search results
with proper headings.
READ MORE ...

Sunday, 24 August 2014

VTU CSE/ISE DBMS Lab Programs

 Database - DBMS Lab Manual - All Programs


VTU 5 th Sem 

Vtu Forum 


SQL> create user username identified by password;
SQL>create user santosh identified by manager

SQL> grant connect, resource to username;
 SQL> grant connect, resource to santosh;
login with same account




Program 1:

 Consider the Insurance database given below. The primary keys are underlined and the data types are specified:

PERSON (driver-id:string,name:string,address:string)
CAR (Regno:string,model:string,year:int)
ACCIDENT (report-number:int,date:date,location:string)
OWNS (driver-id:string,regno:string)
PARTICIPATED (driver-id:string,regno:string,report-number:int,damage-amount:int)


i)    create the above tables by properly specifying the primary keys and the foreign keys
ii)    Enter atleast five tuples for each relation
iii)    Demonstrate how you
a.    Update the damage amount for the car with a specific regno in accident with report number 12 to 25000
b.    Add a new accident to the database
iv)    Find the total number of people who owned cars that were involved in accidents in 2006.
v)    Find the number of accidents in which cars belonging to a specific model were involved.
vi)    Generation of suitable reports   
vii)    Create suitable front end for querying and display the results



SOLUTION:

i)    Create the above tables by properly specifying the primary keys and the foreign keys

create table person(driver_id char(4) primary key,
name varchar(30),
address varchar(30)
);

create table car(reg_no char(15) primary key,
model char(20),
year number(4)
);

create table accident(report_no number primary key,
acc_date date,
location varchar(30)
);

create table owns(driver_id references person,
reg_no references car
);

create table participated( driver_id references person,
reg_no references car,
report_no references accident,
damage_amt number(10,2)
);


ii)    Enter atleast five tuples for each relation

insert into person values('1234','amith','no a-1-12 koppal');
insert into person values('2345','anil','23 vijaya apts');
insert into person values('3412','john','no 3423 vicky apts');
insert into person values('4567','arun','kamal nivas koppal');
insert into person values('4522','sunil','no 54 ravi nagar');


insert into car values('ka37k32','hyundai',2004);
insert into car values('ka05d34','maruti 800',1998);
insert into car values('ka23j90','zen',2002);
insert into car values('ka35f45','fiat',2001);
insert into car values('ka36m78','benz',2000);

insert into accident values(12,'12-feb-1990','vit cross');
insert into accident values(34,'31-jan-1999','jayanagar');
insert into accident values(56,'12-dec-1998','btm layout');
insert into accident values(67,'07-jul-2003','jp nagar');
insert into accident values(87,'01-may-2001','allalsandra');

insert into owns values('1234','ka37k32');
insert into owns values('2345','ka05d34');
insert into owns values('3412','ka23j90');
insert into owns values('4567','ka35f45');
insert into owns values('4522','ka36m78');

insert into participated values('1234','ka37k32',12,12000);
insert into participated values('2345','ka05d34',34,13000);
insert into participated values('3412','ka23j90',56,14000);
insert into participated values('4567','ka35f45',67,12450);
insert into participated values('4522','ka36m78',87,10000);



iii)    Demonstrate how you
a.    Update the damage amount for the car with a specific regno in accident with report number 12 to 25000
b.    Add a new accident to the database


Solution:

a.    Update participated set  damage_amt = 25000 where reg_no = ‘&reg_no’ and report_no = 12;
b.    Insert into accident values(93,’02-may-2002’,’allalsandra’);

iv)    Find the total number of people who owned cars that were involved in accidents in 2006.

SQL> Select  count(*)  from accident where to_char(acc_date,’yy’) = 02;
v)    Find the number of accidents in which cars belonging to a specific model were involved.

SQL> Select count(a.report_no) from accident a,participated p,car c where c.reg_no =                      
          p.reg_no and
          a.report_no = p.report_no and c.model = '&model' group by a.report_no

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


Program 2:


 Consider the following relations for an order processing database application in a company.

CUSTOMER (Cust #: int, Cname: string, City: string)
ORDER (Order #: int, Odate: date, Cust #: int, Ord-Amt: int)
ORDER-ITEM (Order #: int, Item #: int, qty: int)
ITEM (Item #: int, Unit Price: int)
SHIPMENT (Order #: int, Warehouse #: int, Ship-Date: date)
WAREHOUSE (Warehouse #: int, City: string)

i)    Create the above tables by properly specifying the primary keys and the foreign keys.
ii)    Enter at least five tuples for each relation.
iii)    Produce a listing: CUSTNAME, NO_OF_ORDERS, AVG_ORDER_AMT, where the middle column is the total number of orders by the customer and the last column is the average order amount for that customer.
iv)    List the Order# for the orders that were shipped from all the warehouses that the company has in a specific city.
v)    Demonstrate how you delete Item# 10 from the ITEM table and make that field null in the ORDER-ITEM table.
vi)    Generation of suitable reports.
vii)    Create a suitable front end for querying and displaying the results.



Solution:

i)    Create the above tables by properly specifying the primary keys and the foreign keys.


create table customer(cust_no number primary key,
cname varchar(30),
city varchar(30));

create table orders( order_no number primary key,
odate date,
cust_no references customer,
ord_amt number);

create table item( item_no number primary key,
unit_price number);

create table orders_item( order_no references orders,
item_no references item on delete cascade,
qty number);

create table warehouse(warehouse_no number primary key,
city varchar(30));

create table shipment(order_no references orders,
warehouse_no references warehouse,
ship_date date);
ii)    Enter at least five tuples for each relation.

insert into customer values(10,'ajay','bangalore');
insert into customer values(21,'arun','hyderabad');
insert into customer values(32,'ikram','hubli');
insert into customer values(45,'suraj','kanpur');
insert into customer values(78,'niraja','panji');

insert into orders values(12345,'25-mar-2005',10,0);
insert into orders values(12346,'26-mar-2005',21,0);
insert into orders values(12347,'30-mar-2005',32,0);
insert into orders values(12348,'01-apr-2005',45,0);
insert into orders values(12349,'01-apr-2005',78,0);

insert into item values(10,100);
insert into item values(20,60);
insert into item values(30,140);
insert into item values(40,35);
insert into item values(50,150);

insert into orders_item values(12345,10,4);
insert into orders_item values(12346,20,2);
insert into orders_item values(12347,30,5);
insert into orders_item values(12348,40,10);
insert into orders_item values(12349,50,18);

insert into warehouse values(1501,'bangalore');
insert into warehouse values(1502,'hyderabad');
insert into warehouse values(1503,'hubli');
insert into warehouse values(1504,'delhi');
insert into warehouse values(1505,'belgaum');

insert into shipment values(12345,1501,'5-mar-2005');
insert into shipment values(12346,1502,'28-mar-2005');
insert into shipment values(12347,1503,'01-apr-2005');
insert into shipment values(12348,1504,'04-apr-2005');
insert into shipment values(12349,1505,'05-apr-2005');

update orders set ord_amt = (select sum(oi.qty * i.unit_price) from orders_item oi,item I where oi.order_no = orders.order_no and I.item_no = oi.item_no);

commit;

iii)    Produce a listing: CUSTNAME, NO_OF_ORDERS, AVG_ORDER_AMT, where the middle column is the total number of orders by the customer and the last column is the average order amount for that customer.

SQL> select  cname “CustName”,count(*) “No of Orders”,avg(ord_amt) “Average order  
          amt” from orders o,customer c where o.cust_no = c.cust_no group by cname;

CustName                       No of Orders  Average amt
------------------------------  ------------        ---------------------------
ajay                                       1                         400
arun                                      1                         120
ikram                                    1                         700
niraja                                    1                        2700
suraj                                     1                         350


iv)    List the Order# for the orders that were shipped from all the warehouses that the company has in a specific city.


SQL> Select  order_no  from warehouse w,shipment s where w.warehouse_no =      
          s.warehouse_no and w.city = ‘&city’;

v)    Demonstrate how you delete item# 10 from the item table and make that field null in the order table.


SQL> Delete from item where item_no = 10;

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

Program 3:


 Consider the following database of student enrollement in courses and books adopted for  each course .

STUDENT (regno :string , name : string , major : string , bdate : int)
COURSE (course# : int , cname : string , dept : string)
ENROLL ( regno : string , course#: int , sem : int , marks : int )
BOOK_ADOPTION ( course#: int , sem : int , book_isbn :int)
TEXT( book_isbn : int , book-title : string , publisher : string , author : string).

i)    Create the above tables by properly specifying the primary keys and the foreign key .
ii)    Enter atleast five tuples for each relation .
iii)    Demonstrate how you add a new text book to the database and make this book be adopted by some department.
iv)    Produce a list of text books( include course # ,book_isbn,book-title) in the alphabetical order for courses offered by the cs department that use more than 2 books.
v)    List any department that has all its adopted books published by specific publisher.
vi)    Generation of suitable reports.
Create suitable front end for querying and display the results



Solution:
i)    Create the above tables by properly specifying the primary keys and the foreign key .


create table student(reg_no char(10) primary key,
name varchar(30),
major char(1),
bdate date
);


create table course(course_no number primary key,
cname varchar(30),
dept varchar(30)
);

create table enroll(reg_no references student,
course_no references course,
sem number(1),
marks number(3),
primary key(reg_no,course_no,sem)
);

create table text(book_isbn number(4) primary key,
book_title varchar(30),
publisher varchar(30),
author varchar(30)
);

create table book_adoption(course_no references course,
sem number(1),
book_isbn references text
);


ii)    Enter atleast five tuples for each relation .

insert into student values('1mv02is033','prasanna','y','24-oct-84');
insert into student values('1mv02is050','sunil','y','22-may-84');
insert into student values('1mv02cs001','aditya','y','11-jan-83');
insert into student values('1mv02mca22','raju','n','30-mar-83');
insert into student values('1mv02mba33','vishal','y','02-apr-82');

insert into course values(1,'be','ise');
insert into course values(2,'be','cse');
insert into course values(3,'be','ece');
insert into course values(4,'mca','ca');
insert into course values(5,'mba','hr');

insert into enroll values('1mv02is033',1,6,97);
insert into enroll values('1mv02is050',2,5,66);
insert into enroll values('1mv02cs001',3,4,45);
insert into enroll values('1mv02mca22',4,1,77);
insert into enroll values('1mv02mba33',5,2,88);

insert into text values(1234,'dbms','bpb','navathe');
insert into text values(2345,'cn','lpe','tanenbaum');
insert into text values(3456,'ds','galgotia','padmareddy');
insert into text values(4567,'cpp','pearson','herbert');
insert into text values(5678,'unix','bpb','das');
insert into text values(1333,'cn','pearson','tanenbaum');
insert into text values(1444,'cn','pearson','tanenbaum');

insert into book_adoption values(1,6,1234);
insert into book_adoption values(2,5,2345);
insert into book_adoption values(3,4,3456);
insert into book_adoption values(5,1,5678);
insert into book_adoption values(4,2,1234);
insert into book_adoption values(2,6,4567);
insert into book_adoption values(2,1,5678);
insert into book_adoption values(2,6,1333);
insert into book_adoption values(2,6,1444);


commit;

iii)    Demonstrate how you add a new text book to the database and make this book be adopted by some department.

SQL> insert into text values(1222,’maths’,’DSC’,’KSC’);
SQL> insert into book_adoption values(4,6,1222);

iv)    Produce a list of text books( include course # ,book_isbn,book-title) in the alphabetical order for courses offered by the cs department that use more than 2 books.

SQL> select b.course_no,b.book_isbn,t.book_title from book_adoption b,text t where b.book_isbn=t.book_isbn and b.course_no in(select course_no from book_adoption where course_no in(select course_no from course where dept like 'cse') group by course_no having count(course_no)>=2);


v)    List any department that has all its adopted books published by specific publisher.


SQL>  select c.dept,t.publisher from course c,book_adoption b,text t where
           c.course_no = b.course_no and
           b.book_isbn = t.book_isbn and
           t.publisher = '&publisher'
           group by c.dept,t.publisher;

Enter value for publisher: bpb
old   4:  t.publisher = '&publisher'
new   4:  t.publisher = 'bpb'

DEPT                           PUBLISHER
------------------------------ ------------------------------
ca                             bpb
cse                            bpb
hr                             bpb
ise                            bpb

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


Program 4:  

Consider the following relations for the details maintained by a book dealer.

AUTHOR (Author-id: int, Name: string, City: string, Country: string)
PUBLISHER (Publisher-id: int, Name: string, City: string, Country: string)
CATALOG (Book-id: int, title: string, author-id: int, Publisher-id: int, Category-id: int, Year: int, Price: int)
CATEGORY (Category-id: int, Description: string)
ORDER-DETAILS (Order-no : int, Book-id: int, Quantity: int)
i.    Create the above tables by properly specifying the primary keys and the foreign keys.
ii.    Enter at least five tuples for each relation.
iii.    Give the details of the authors who have 2 or more books in the catalog and the price of the books is greater than the average price of the books in the catalog and the year of publication is after 2000.
iv.    Find the author of the book which has maximum sales.
v.    Demonstrate how you increase the price of books published by a specific publisher by 10%.
vi.    Generation of suitable reports.
vii.    Create a suitable front end for querying and displaying the results.



Solution:…
i)    Create the above tables by properly specifying the primary keys and the foreign keys.

create table author(author_id number(4) primary key,
aname varchar(30),
acity varchar(30),
acountry varchar(30)
);

create table publisher(pub_id number(4) primary key,
pname varchar(30),
pcity varchar(30),
pcountry varchar(30)
);

create table category(cat_id number(4) primary key,
description varchar(30)
);

create table catalog(book_id number(4) primary key,
title varchar(30),
author_id references author,
pub_id references publisher,
cat_id references category,
year number(4),
price number(6,2)
);

create table order_details(order_no number(4) primary key,
book_id references catalog,
qty number(5)
);

ii)    Enter at least five tuples for each relation.

insert into author values(1001,'balaguruswamy','hyderabad','india');
insert into author values(1002,'tenanbaum','california','usa');
insert into author values(1003,'padma reddy','bangalore','india');
insert into author values(1004,'godse','pune','india');
insert into author values(1005,'oppenham','los angeles','usa');

insert into publisher values(3001,'suhas','bangalore','india');
insert into publisher values(3002,'bpb','delhi','india');
insert into publisher values(3003,'prentice','delhi','india');
insert into publisher values(3004,'pearson','california','usa');
insert into publisher values(3005,'subhas','bangalore','india');

insert into category values(4001,'programming');
insert into category values(4002,'os');
insert into category values(4003,'database');
insert into category values(4004,'networks');
insert into category values(4005,'logic design');

insert into catalog values(1,'let us c',1001,3001,4001,1999,375);
insert into catalog values(2,'database',1002,3002,4003,2002,450);
insert into catalog values(3,'network',1003,3003,4002,1998,500);
insert into catalog values(4,'logic design',1004,3004,4004,2004,750);
insert into catalog values(5,'c++',1005,3005,4005,2005,450);
insert into catalog values(6,'c',1001,3002,4002,2003,880);
insert into catalog values(7,'cn',1001,3004,4002,2005,750);

insert into order_details values(1,1,34);
insert into order_details values(2,1,45);
insert into order_details values(3,2,45);
insert into order_details values(4,2,75);
insert into order_details values(5,3,69);

commit;

iii)    Give the details of the authors who have 2 or more books in the catalog and the price of the books is greater than the average price of the books in the catalog and the year of publication is after 2000.

SQL> select * from author where author_id in
(select author_id from catalog c where c.year>2000 and c.price>(select avg(price) from catalog) group by author_id having count(*)>=2);

Find the author of the book which has maximum sales.

SQL> Select a.author_id,a.aname from author a,catalog c,max_sales m where
          a.author_id = c.author_id and
          c.book_id = m.book_id and
          m.book_id in (select book_id from max_sales where quantity =
          (select max(quantity) from max_sales));

iv)    Demonstrate how you increase the price of books published by a specific publisher by 10%.


SQL> update catalog set price = price*1.1 where pub_pd = &publisher_id;

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

Program 5

Consider the following database for a banking enterprise

BRANCH (branch_name: string, branch_city: string, assets: real)
ACCOUNT (accno: int, branch_name: string, balance: real)
CUSTOMER (customer_name: string, customer_street: string, city:string)
DEPOSITOR (customer_name: string, accno: int)
LOAN (loan_number: int, branch_name: string, amount: real)
BORROWER (customer_name: string, loan_number: int) 

i)    Create the above tables by properly specifying the primary keys and the foreign keys.   
ii)    Enter atleast five tuples for each relation.
iii)    Find all the customers who atleast two accounts at the MAIN branch.
iv)    Find all the customers who have an account at all branches located in a specific city.
v)    Demonstrate how you delete all account tuples at every branch located in a specific city.
vi)    Generation of suitable reports.
vii)    Create suitable front end for querying and displaying the results.



Solution:

i)    Create the above tables by properly specifying the primary keys and the foreign keys.


create table branch(br_name varchar(30) primary key,
br_city varchar(30),
assets number(10,2)
);

create table account(acc_no number(4) primary key,
br_name references branch,
balance number(10,2)
);

create table customers(c_name varchar(30) primary key,
c_street varchar(30),
c_city varchar(30)
);

create table depositor(c_name references customers,
acc_no references account,
qty number
);

create table loan(loan_no number(4) primary key,
br_name references branch,
amt number(10,2)
);

create table borrower(c_name references customers,
loan_no references loan
);

ii)    Enter atleast five tuples for each relation.

insert into branch values('rajaji nagar','bangalore',1000000);
insert into branch values('jayanagar','bangalore',50000);
insert into branch values('mvit','bangalore',10000);
insert into branch values('jawahar nagar','bangalore',100000);
insert into branch values('rajbhavan','bangalore',23566);

insert into account values(1000,'rajaji nagar',2500);
insert into account values(2000,'rajaji nagar',8996);
insert into account values(3000,'rajaji nagar',7415);
insert into account values(4000,'jayanagar',2121);
insert into account values(5000,'mvit',8596);
insert into account values(6000,'jawahar nagar',9999);
insert into account values(7000,'rajbhavan',235);

insert into customers values('prasanna','patel road','raichur');
insert into customers values('harish','indiranagar','bangalore');
insert into customers values('sunil','ring road','bangalore');
insert into customers values('srinivas','woc road','bangalore');
insert into customers values('rudre','maruti galli','belgaum');

insert into depositor values('prasanna',1000,2000);
insert into depositor values('prasanna',2000,3000);
insert into depositor values('harish',3000,5000);
insert into depositor values('sunil',4000,1520);
insert into depositor values('srinivas',5000,1120);
insert into depositor values('rudre',6000,1250);
insert into depositor values('prasanna',7000,1250);

insert into loan values(100,'rajaji nagar',5000);
insert into loan values(200,'rajaji nagar',4000);
insert into loan values(300,'jayanagar',6323);
insert into loan values(400,'mvit',4512);
insert into loan values(500,'jawahar nagar',1235);
insert into loan values(600,'rajbhavan',9632);
insert into loan values(700,'rajbhavan',3456);

insert into borrower values('prasanna',100);
insert into borrower values('harish',200);
insert into borrower values('sunil',300);
insert into borrower values('srinivas',400);
insert into borrower values('rudre',500);

iii)    Find all the customers who atleast two accounts at the MAIN branch.


SQL>Select c_name from depositor d,account a where
         a.acc_no = d.acc_no and
         a.br_name = 'rajaji nagar’
         group by c_name
         having count(*) > 2;

iv)    Find all the customers who have an account at all branches located in a specific city.

SQL> select distinct c_name from depositor where acc_no in
(select acc_no from account where br_name in
(select br_name from branch where br_city='bangalore'));

Demonstrate how you delete all account tuples at every branch located in a specific city.


SQL> Delete from account where br_name in
          (select br_name from branch where br_city = 'bangalore);
READ MORE ...