Answers to practice -1

Questions click here

create table hotel02(hotelno varchar(20),hotelname char(20), city char(20));

create table room02(roomno varchar(5),hotelno varchar(2),type char(10),price varchar(10));

create table booking02(hotelno number(10),guestno number(10),date_from varchar(30),date_to varchar(30),roomno number(10))
insert into booking02 values(005,135,'26-aug-2010','30-sep-2010',212)
insert into booking02 values(005,135,'26-aug-2010',30-sep-2010',212)
insert into booking02 values(004,235,'16-aug-2010','1-sep-2010',112)
select * from booking02
insert into booking02 values(003,035,'12-sep-2010','10-oct-2010',103)
insert into booking02 values(002,025,'22-jul-2010','15-aug-2010',301)
insert into booking02 values(001,023,'23-jun-2010','25-aug-2010',204)
insert into booking02 values(001,023,'23-jun-2010',25-aug-2010',204)



select hotelname from hotel02 where city='london'

Alter table booking02 add PRIMARY KEY(hotelno,guestno,date_from)
Alter table room02 add PRIMARY KEY(roomno,hotelno)
Alter table hotel02 add PRIMARY KEY(hotelno)



create table guest02(guestno varchar(5),guestaddress varchar(50),guestname char(10),
                  primary key (guestno));


update room02 set price =price+price*0.05;


select * from room02




select*from booking02 where date_to IS NULL

select count(*)from hotel02

select avg(price)from room02

SELECT HotelNo, COUNT(RoomNo) AS count FROM Room02 GROUP BY Hotelno;

select r.hotelno,COUNT(roomno)from room02 r, hotel02 h where r.hotelno=h.hotelno AND city='london'GROUP BY r.hotelno 

SELECT MAX(X) AS MostlyBook FROM (SELECT Type, COUNT(Type) AS X FROM Booking02 b, Hotel02 h, Room02 r
          WHERE r.roomno = b.roomno AND b.hotelno = h.hotelno AND h.city LIKE '%london%' GROUP BY Type);

select type,price from hotel02,room02 where hotel02.hotelno=room02.hotelno and hotelname='taj'

SELECT * FROM Guest02 WHERE GuestNo IN
(SELECT GuestNo FROM Booking02 WHERE Date_From <= SYSDATE
AND (Date_To >= SYSDATE OR Date_To IS NULL)
AND HotelNo =(SELECT HotelNo FROM Hotel02
WHERE HotelName = 'Grosvenor'));

SELECT r.* FROM Room r LEFT JOIN
(SELECT g.GuestName,h.HotelNo,b.RoomNo FROM Guest02 g,Booking02 b,Hotel02 h
WHERE g.GuestNo = b.GuestNo AND b.HotelNo = h.HotelNo AND
h.HotelName= 'Grosvenor' AND
b.Date_From <=CURRENT_DATE AND b.Date_To >=CURRENT_DATE) AS X
ON r.HotelNo = X.HotelNo AND r.RoomNo = X.RoomNo;


SELECT SUM(price) FROM Booking02 b, Room02 r, Hotel02 h
WHERE (b.Date_From <= CURRENT_DATE AND
b.Date_To >= CURRENT_DATE) AND
r.Hotelno = h.HotelNo AND r.RoomNo = b.RoomNo;

SELECT * FROM Room02 r
WHERE RoomNo NOT IN
(SELECT RoomNo FROM Booking02 b,Hotel02 h
WHERE (Date_From <= CURRENT_DATE AND
Date_To >= CURRENT_DATE) AND
b.HotelNo = h.HotelNo AND HotelName ='pune');

SELECT SUM(Price) FROM Room02 r
WHERE Room_No NOT IN
(SELECT RoomNo FROM Booking02 b, Hotel02 h
WHERE (Date_From <= CURRENT_DATE AND
Date_To >= CURRENT_DATE) AND
b.HotelNo = h.HotelNo AND HotelName = 'pune');

practice -1

Some Practical Examples which i have done : 
 

1) Develop Physical schema for following relations:- 

1. Table name : - Hotel hotelNo hotelName city
2. Table name : -Room roomNo hotelNo type price
3. Table name :- Booking hotelNo guestNo dateFrom dateTo roomNo
4. Table name :-Guest guestNo guestName guestAddress

For the above schema's following primary keys are used:-

1. Table name Primary key
2. Hotel hotelNo
3. Room (roomNo,hotelNo)
4. Booking (hotelNo,guestNo,dateFrom)
5. Guest guestNo

Identify the foreign keys in the schema. 

 2 ) Answers the following queries :

1. Populating tables:
2. Insert minimum 10 rows into each of these tables.
3. Update the price of all room by 5%.

Simple queries :

1. List full details of all hotels.
2. List full details of all hotels in London.
3. List the names and addresses of all guests living in London, alphabetically ordered by name.
4. List all double or family rooms with a price below $40.00 per night, in ascending order of price.
5. List the bookings for which no dateTo has been specified.

Aggregate functions :

1. How many hotels are there?
2. What is the average price of a room?
3. What is the total revenue per night from all double rooms?
4. How many different guests have made bookings for August?

Grouping :

1. List the number of rooms in each hotel.
2. List the number of rooms in each hotel in London.
3. What is the average number of bookings for each hotel in August?
4. What is the most commonly booked room type for each hotel in London?
5. What is the lost income unoccupied rooms at each hotel today?

 Subqueries and joins :

1. List the price and type of all rooms at the Grosvenor Hotel.
2. List all guests currently staying at the Grosvenor Hotel.
3. List the details of all rooms at the Grosvenor Hotel, including the name of the guest staying in the room, if the room is occupied.
4. What is the total income from bookings for the Grosvenor Hotel today?
5. List the rooms that are currently unoccupied at the Grosvenor Hotel.
6. What is the lost income from unoccupied rooms at the Grosvenor Hotel?

Answers will be posted Soon.............. 

MySQL and PostgreSQL database

The MySQL database has become the world's most popular open source  database because of its high performance, high reliability and ease of use. 

1. It has 2 layers upper SQLlayer and set of storage engines
2. The most commonly used storage engines in MySQL are InnoDB for ACID support and higher performance on large workloads
3. It provides concurrency and MyISAM for Concurrency workloads.
4. It has been developed with a focus on speed
5. It supports 9 storage engines

• MyISAM
• InnoDB
• NDB Cluster
• MERGE
• MEMORY (HEAP)
• FEDERATED
• ARCHIVE
• CSV

It is also the database of choice for a new generation of applications built on the LAMP stack (Linux, Apache, MySQL, PHP / Perl / Python.) Many of the world's largest and fastest-growing organizations including Facebook, Google, Adobe, Alcatel Lucent and Zappos rely on MySQL to save time and money powering their high-volume Web sites, business-critical systems and packaged software.

The latest version available in the market can be downloaded to go to downloaded by clicking here

1. It has been developed from developers who came from an oracle or SQL server backgreoun.
2. Powerful for object relational database systems.
3. Runs on all Operating Systems.
4. Nativeprogramming interfaces for C/C++,Java,Tcl,Ruby etc.,
5. It boasts features like Multi-Version Concurreny Control(MVCC).
6. It has full support for subqueries (including subselects in the FROM clause), read-committed and serializable transaction isolation levels.
7. GiST (Generalized Search Tree) indexing is an advanced system which brings together a wide array of different sorting and searching algorithms including B-tree, B+-tree, R-tree, partial sum trees, ranked B+-trees and many others. It also provides an interface which allows both the creation of custom data types as well as extensible query methods with which to search them. 



Some general Limits



The latest version available in the market can be downloaded to go to downloaded by clicking here




 

Download Open Source Software

Here are the links to various Open Source Software:

• Operating Systems:

Ubuntu: this offers free downloadable operating system click here to download

for more information click here

Fedora: another version of Linux operating system offers free download of Operating system click here to download fedora 

Many versions are available in the market now some of them are:

1.  Centos
2. Redhat

• Database

Coming to the data base though oracle still the leading database software provider and Microsoft still trying to dominate with its licensed version of SQL server 20XX. The open Source databases available in the world some of them are .... provided with the downloadable links.

MySQL and PostgreSQL: To Download MySQL click here

MongoDB:  One of MongoDB's best capabilities is its support for dynamic (adhoc) queries. It doesnot require any special indexing to find data,users can find data using any criteria. Best for relational queries. 
Click here to download  

Oracle BerkelyDB: It provides a collection of well-proven building-block technologies that can be configured to address any application need from the hand-held device to the data center, from a local storage solution to a world-wide distributed one, from kilobytes to petabytes.click here to download

Biggest Open Source Projects

This tending towards didn't happened overnight. It was rather a painful journey. 'Sharing' was never a good thing for Corporates. These changes are due to success stories of largest Open Source Companies.

List of Companies started the change of Open Source Industry:

1. GNU/Linux
2. Redhat
3. Debian
4. Ubuntu
5. Wordpress
6. Drupal
7. MySql
8. Apache

 

So,The Linux Version(GNU) had made a revolutionary change in the World of Open Source Business.MySQL  in the business of Data Bases, DRUPAL WordPress in the world of Website Designing every open source software developed made changes and had a great impact on the business world.

How Open Source works

Going deep into open source now we need to know what is the difference between open source and freeware then we can easily understand how does it work

so, Open source is not necessarily free, and freeware is not necessarily open source: the terms are not, in fact, mutually exchangeable. However, there's a good deal of overlap between the two, and that which is freeware may also happen to be open source.

World into Open Source

Before going deep into this topic we need to know which companies are now into open source

1. Zenos
2. Mono(from Microsoft)
3. Apache
4. Sugar CRM
5. Drupal
6. Open Solaris
7. Java
8. MySQL

and if go on writing a ordered list like this becomes  this becomes so big that my blogging ends up writing only this post.

So even the world copied software Microsoft unwilling coming into this open source world makes us think how world is tending towards this open source.