SQL basic

This topic will present how can we use SQL to manipulate with a database. We use MySQL as our DBMS.

Download
- You could download MySQL at:
http://www.mysql.com/downloads/
- You could also download MySQL workbench, a GUI tool to manage your MySQL database at:
http://www.mysql.com/products/workbench/

Create a database
Assume that we have a database for a hotel management, including room booking and payment management. There will be five tables as follows:
- tblHotel: contains information about hotels
- tblRoom: contains information about rooms of the hotels
- tblUser: contains information about the user of the application, including the client of the hotels
- tblBooking: contains information about bookings of clients and the perspective rooms during a period of time
- tblBill: contains information about the payment of booking and/or cancel.



Now we will see how can we use some statements of basic SQL language to manipulate on these tables.

INSERT
Assume that the current data in the tblHotel is:



after using this INSERT statement:
INSERT INTO `tblhotel`(`address`,`id`,`name`,`starLevel`)
VALUES("Sai Gon",5,"Saigon Star",3);
the tblHotel becomes:




UPDATE
after using this UPDATE statement:
UPDATE `tblhotel`
SET `address` = "Quận 1, TP. Hồ Chí Minh"
WHERE id = 5;
the tblHotel becomes:




DELETE
and then, after using this DELETE staement:
DELETE FROM `tblhotel`
WHERE id = 5;
the tblHotel becomes:




SELECT
One of the most interesting statements in SQL language is SELECT because it enables us to search, filter, and collect some pies of data based on our demand. We will see some examples of this statement from easy to more complex. Assume that the current data in fives tables are:
- tblHotel:
 

- tblRoom:
 

- tblUser:
 

- tblBooking:
 

- tblBill:
 

 Now, we could find all users whose name contains the character 'a':
SELECT * FROM tblUser
WHERE fullName LIKE "%a%";

its results are:



We could also find all clients who has booked room to stay in a hotel from 15/08/2013 to 30/08/2013:
SELECT a.fullName, a.idCardNumber, a.idCardType, a.address, b.name AS `room`, b.type, c.startDate, c.endDate, c.price
FROM tblUser a, tblRoom b, tblBooking c
WHERE c.startDate BETWEEN "2013-08-15" AND "2013-08-30"
AND c.endDate BETWEEN "2013-08-15" AND "2013-08-30"
AND b.id = c.idRoom
AND a.id = c.idUser;

its results are:


More complex, we could find all clients who has some payment and sort them based on the amount of payment from highest to lowest:
SELECT a.id, a.fullName, a.idCardNumber, a.idCardType, a.address, SUM(c.amount) AS `amount`
FROM tblUser a INNER JOIN tblBooking b ON b.idUser = a.id
INNER JOIN tblBill c ON c.idBooking = b.id
GROUP BY a.id, a.fullName, a.idCardNumber, a.idCardType, a.address
ORDER BY `amount` DESC;

and its results are: