Jack Hau Story: 2013

Designing Roomes rates Tables (show rowspan and colspan).

HTML

<div id="contents">
    <h2>Rooms Rates :</h2>
        <div class="CSSTableGenerator" >
            <table >
                <tr>
                    <td>Room Type</td>
                    <td >Weekdays (RM- nett)</td>
                    <td>Weekends (RM- nett)</td>
                    <td>S/Peak (RM- nett)</td>
                </tr>
                <tr>
                    <td >SUPERIOR</td>
                    <td colspan="3">129.00</td>
                </tr>
                <tr>
                    <td >DELUXE</td>
                    <td rowspan="2">158.00</td>
                    <td rowspan="2" colspan="2">168.00</td>
                    
                </tr>
                <tr>
                    <td >DELUXE SUITE</td>
                </tr>
                <tr>
                    <td >SUPER DELUXE</td>
                    <td>168.00</td>
                    <td>188.00</td>
                    <td>198.00</td>
                </tr>
                <tr>
                    <td >FAMILY SUPERIOR</td>
                    <td>199.00</td>
                    <td>219.00</td>
                    <td>239.00</td>
                </tr>
                <tr>
                    <td >SUPER DELUXE</td>
                    <td>189.00</td>
                    <td>209.00</td>
                    <td>219.00</td>
                </tr>
            </table>
        </div>
        <p>
            ** THE ABOVE RATE ARE INCLUSIVE OF BREAKFAST
        </p>
</div>

CSS

.CSSTableGenerator 
{
    margin:0px auto;
    width:900px;
    margin-bottom:15px;
    box-shadow: 10px 10px 5px #888888;
    border:1px solid #000000;
    border-radius:5px;
}

.CSSTableGenerator table
{
    border-collapse: collapse;
    width:100%;
    height:100%;
    margin:0px;padding:0px;
}


.CSSTableGenerator tr:nth-child(odd)
{ 
    background-color:#ffffff; 
}

.CSSTableGenerator tr:nth-child(even)   
{ 
    background-color:#D3D3D3;
}
 
 .CSSTableGenerator tr:first-child td
{
        
    background-color:#9370DB;
    border:0px solid #000000;
    text-align:center;
    border-width:0px 0px 1px 1px;
    font-size:14px;
    font-family:Arial;
    font-weight:bold;
    color:#ffffff;
}

 .CSSTableGenerator td
{
    vertical-align:middle;
    border:1px solid #000000;
    border-width:0px 1px 1px 0px;
    text-align:center;
    padding:7px;
    font-size:12px;
    font-family:Arial;
    font-weight:normal;
    color:#000000;
}

Images hover showing the info
HTML

<div id="contents">                     
    <div class="body">
        <h1>Food</h1>
        <ul id="foods">
            <li>
                <h2><a href="foods.html">Food Culture</a></h2>
                <div class="infos">
                    <a href="foods.html"><img src="images/curture_food.jpg" alt="Img" height="169" width="780"><span class="cover"></span></a>
                    <p>
                        <span style="font-style:italic ; font-size:30px;">Satay</span> I'm a product overview. Here you can write more information about your product. Buyers like to know ...
                    </p>
                </div>
            </li>
            <li>
                <h2><a href="foods.html">Sumptuous Desserts</a></h2>
                <div class="infos">
                    <a href="foods.html"><img src="images/desserts.jpg" alt="Img" height="169" width="780"><span class="cover"></span></a>
                    <p>
                        <span style="font-style:italic ; font-size:30px;">Choco Ice Cream Sandwich</span> I'm a product overview. Here you can write more information about your product. Buyers like to know ...
                    </p>
                </div>
            </li>
        </ul>
    </div>
</div>

CSS

#foods li div.infos
 {
    height: 169px;
    width: 780px;
    position: relative;
}

#foods li div.infos p 
{
    background: url(images/bg-foodDetail.png) repeat;
    color: #316e66;
    display: none;
    height: 60px;
    width: 740px;
    padding: 6px 20px 0;
    position: absolute;
    left: 0;
    bottom: 0;
}

#foods li div.infos p span 
{
    display: block;
    font: 22px/22px "Times New Roman";
}

#foods li div.infos:hover span.cover 
{
    background: url(images/bg-foodInfos.png) repeat;
    display: inline-block;
    height: 100%;
    width: 100%;
    position: absolute;
    left: 0px;
    top: 0px;
}

#foods li div.infos:hover p
{
    display: block;
}

No comments

Building a complete website by only using HTML & CSS codes

Items that MUST be included in your webpage :

• Font : Colour, size, bold, underlined, italics.
• Heading / Paragraph : Alignments (center, right, left).
• Images.
• Tables (show rowspan and colspan).
• Hyperlinks (Text and image).
• Lists : ordered, unordered and image/animated bullet.
• Multimedia elements (sound and movie).
• Cascading Style Sheet (CSS) : External, Internal, Inline.
• Forms.

Hyperlink Navigation :

Designing header navagation
HTML

<div id="navigation">
    <ul>
        <li class="selected">
            <a href="index.html">Home</a>
        </li>
        <li>
            <a href="about.html">About us</a>
        </li>
        <li>
            <a href="rooms.html">Rooms</a>
        </li>
        <li>
            <a href="photo.html">Gallery</a>
        </li>
        <li>
            <a href="foods.html">Food</a>
        </li>
        <li>
            <a href="facilities.html">Facilities</a>
        </li>
        <li>
            <a href="news.html">News</a>
        </li>
        <li>
            <a href="contact.html">Contact</a>
        </li>
    </ul>
</div>

CSS

/** Navigation **/
#navigation 
{
    background: #6B2D5F;
    border-top-style:ridge;
    border-bottom-style:ridge;
    border-width:6px;
    border-color:#761466;
    clear: both;
    height: 40px;
    width: 980px;
}

#navigation ul
 {
    display: inline-block;
    width: 1000px;
    list-style: none;
    margin: 0px;
    padding: 0px;
}

#navigation li 
{
    float: left;
    background-repeat: no-repeat;
    height: 45px;
    width: 122px;
    margin: 0px;
    padding-left: 1px;
    text-align: center;
}

#navigation li a 
{
    color: #fff;
    display: block;
    font: bold 14px/48px Arial;
    height: 45px;
    text-decoration: none;
    text-transform: uppercase;
}

#navigation li a:hover 
{
    background-position: 0 -59px;
    color: #6d6157;
}

#menu_item
{   
   background: url(images/btn.png);
   padding: 0px 10px;
   border-radius: 8px;  
   color: #000000;
   font-size: 15px;
   width:130px;
   height:30px;
   list-style-type:none;
   text-align:center;
   text-decoration: none;

}

#menu_item a
{   
    color:#000000;
    text-decoration:none;
}
    
#menu_item a:hover
{   
    color:#6d6157;
    text-decoration:none;
}

Contact Forms :

Designing contact page for Klebang Beach Resort
HTML

<div id="contacts">
    <fieldset>
        <legend>Feedback</legend>
        <form name="feedback" action="" method="">

            <table>                                 
                <tr>
                    <td>Name:</td>
                    <td><input type="text" name="feedback_name" placeholder="Your name..."></td>
                </tr> 
                <tr>
                    <td>Email:</td>
                    <td><input type="email" name="feedback_email" placeholder="Your email..."></td>
                </tr> 
                <tr>
                    <td>Subject:</td>
                    <td><input type="text" name="feedback_subject" placeholder="Subject..."></td>
                </tr> 
                <tr>
                    <td class="txtarea">Message:</td>
                    <td><textarea placeholder="Comments..."></textarea></td>
                </tr>
                <tr>
                    <td></td>
                    <td>
                        <input type="submit" value="" class="btn">
                        <input type="reset" value="" class="reset"/>
                    </td>
                </tr>                                   
            </table>

        </form>
    </fieldset>
</div>

CSS

#contact fieldset
 {
    float: right;
    color: #5a4535;
    height: 320px;
    width: 420px;
    border: 1px solid #5a4535;
    padding: 19px 19px 6px;
}

#contact textarea 
{
    height: 143px;
    line-height: 17px;
    width: 338px;
    border: 1px solid #5a4535;
    border-radius: 2px;
    overflow: auto;
    resize: none;
}

#contact p span 
{
    display: block;
    text-indent:0px;
    text-transform: uppercase;
}

#contents p 
{
    font-size: 15px;
    text-indent:50px;
    line-height: 30px;
    padding: 0 0 8px 2px;
}

No comments

DATA DEFINITION LANGUAGE (DDL)

1. Show all databases.

show databases;

2. Create a new database.

create database cafe;

3. Delete a database.

        drop database cafe;

4. Start using the database.

     use cafe;

5. Create a table.

create table FOOD

FoodID int(3) not null auto_increment primary key,

FoodName varchar(100) not null,

FoodType varchar(50),

FoodPrice decimal(5,2),

FoodCalorie int(4),

Fooddate date

);

create table FOOD

(

FoodID int(3) not null auto_increment,

FoodName varchar(100) not null,

FoodType varchar(50),

FoodPrice decimal(5,2),

FoodCalorie int(4),

Fooddate date,

Primary Key (FoodID)

);

create table test

(

id int(2) not null primary key,

name varchar(20) not null unique,

fax char(10) not null default '07-1234567'

);

CREATE TABLE satu

(

id int(2) not null primary key,

state enum('Melaka','Johor','Pahang') default NULL

);

6. To see the structure of the table.

     describe FOOD;

7. To show all the tables in a database.

     show tables;

8. To remove the table.

     Drop table foods;

9. Rename table.

     rename table FOOD to FOODS;

     rename table [old name] to [new name];

10. Remove Primary Key. This can be done if the attribute is not auto_increment.

     alter table FOODS drop primary key;

11. Add Primary key.

alter table foods add primary key (FoodID);

12. Remove a column.

     alter table FOODS drop FoodName;

13. Add a new column to the table at specific positions.

     alter table FOODS add FoodName varchar(100) not null after FoodID;

        alter table FOODS add FoodName varchar(100) not null first;

14. Change column name only. Used to rename a column, change its datatype, or move it within the structure.

     alter table Foods change FoodName FoodN varchar(100) not null;

15. Modify column characteristics. Used to do everything CHANGE can, but without renaming the column.

     alter table Foods modify FoodN varchar(80) not null;

16. Add a unique key

     Create unique index FoodName on Foods(FoodName);

17. Drop a unique key

     Drop index FoodName on foods;

18. Set a default value

alter table foods alter FoodCalorie set default 0;

alter table foods alter FoodType set default "Asian";

19. Drop default value

alter table foods alter FoodType drop default;

DATA MANIPULATION LANGUAGE (DML)

1. Add/Insert records into the table

insert into Foods

(FoodName, FoodType, FoodPrice, FoodCalorie, FoodDate) values

('Coffee Cheese Cake', 'Cake', 8.50, 450, '2011-12-29'),

('Tom Yam Soup', 'Asian', 12.90, 350, '2010-02-17'),

('Fish and Chips', 'Western', 10.00, 625, '2010-05-02'),

('Nasi Lemak', 'Asian', 3.50, 400, '2012-11-14'),

('Black Forest Cake', 'Cake', 9.90, 580, '2012-10-10'),

('Mango Papaya salad', 'Asian', 7.50, 220, '2012-04-25'),

('Chicken Chop with Mashed Potato', 'Western', 15.90, 675, '2013-01-11'),

('Asam Pedas', 'Asian', 4.50, 335, '2010-11-04'),

('Caesar Salad', 'Western', 7.00, 240, '2012-09-12'),

('salmon with Brown Rice', 'Western', 18.90, 790, '2013-06-19');

2. Update a record in the file.

Update Foods set FoodName = "Salmon with Brown Rice",

FoodPrice = 18.50 where FoodId = 10;

3. Delete a specific record.

delete from Foods where FoodID = 9;

truncate Foods;

delete from Foods;

- Truncate = empties the table. When a new entry is entered, the FoodID starts at 1 again.

- Delete from Foods – this statement empties the table. But any new record entered will continue from the last FoodID value.

4. View all records.

select * from Foods;

5. View only some attributes.

select FoodName, FoodPrice from Foods;

6. Sort list.

        select FoodName, FoodPrice from Foods order by FoodName desc;

select FoodName, FoodPrice from Foods order by FoodName asc;

select FoodName, FoodPrice from Foods order by FoodName asc, FoodPrice desc;

7. To show list without repetition/duplication

select distinct FoodType from Foods;

8. To search for food that ends with “ad” using wildcard.

Select * from Foods where FoodName like '%ad';

9. To search for food that starts with “C” using wildcard.

Select * from Foods where FoodName like 'c%’;

10. To search for food that is Asian and below RM5.00.

Select * from Foods where FoodType = 'Asian' and FoodPrice < 5;

11. To search for foods introduced in 2010.

Select * from Foods where year(FoodDate) = 2010;

12. To use simple formula in SQL statement.

Select *, (FoodPrice + 5) as 'New Price' from Foods;

13. Show all the Cakes

Select * from Foods where FoodType = "Cake";

Select * from Foods where FoodType != "Asian" and FoodType <> "Western";

Select * from Foods where FoodType NOT IN ("Asian","Western");

14. Show all food which are Western and Asian.

Select * from Foods where FoodType IN ("Asian","Western");

Select * from Foods where FoodType = "Asian" or FoodType = "Western";

Select * from Foods where NOT (FoodType = "Cake");

15. Show all food that has calorie between 400 and 600. Between is inclusive

Select * from Foods where FoodCalorie between 400 and 600;

Select * from Foods where FoodCalorie >= 400 and Foodcalorie <= 600;

USING AGGREGATE FUNCTIONS (AVG, COUNT, MIN, MAX, SUM)

16. Count all food.

Select count(*) as "Total Food" from Foods;

17. Count the number of food types.

select count(distinct FoodType) from foods;

18. Count the number of food for Cake.

select count(*) from foods where FoodType = "Cake";

19. Average price of food.

Select avg(FoodPrice) as "Average Food Price" from Foods;

Select round(avg(FoodPrice)) as "Average Food Price" from Foods;

Select round(avg(FoodPrice),2) as "Average Food Price" from Foods;

20. Minimum and Maximum price of food.

Select min(FoodPrice) from Foods;

Select max(FoodPrice) from Foods;

Select min(FoodPrice) from Foods where foodtype = "Western";

21. Lists all foods which cost below the average price.

Select * from Foods where FoodPrice < (select avg(FoodPrice)

from Foods);

Select * from Foods where FoodCalorie < (select avg(FoodCalorie)

from Foods);

22. Count the number of dishes available for each food type.

Select count(distinct FoodType) from Foods;

23. Count the number of food for each type.

Select FoodType, count(*) from Foods group by FoodType;

24. Sum/Add up the calories for all food.

Select sum(FoodCalorie) from Foods;

COMMIT, ROLLBACK AND SAVEPOINT

· By default all commnds executed in MySQL will be automatically saved.

· If lets say, you want to do some testing and you don’t want to save the changes that you will make, you can do the following:

1. First start the transaction

start transaction;

2. Next you can execute as many commands as you wish.

delete from owner where owner_id = 1;

delete from owner where owner_id = 2;

3. If you want to save the changes that you have made, execute the following command.

commit;

4. If you want to get the original data, execute the following command.

rollback;

· Sometimes you want to revert to certain part of your testing. Here you can use the savepoint command.

start transaction;

delete from owner where owner_id = 1;

savepoint p1;

delete from owner where owner_id = 2;

savepoint p2;

delete from owner where owner_id = 3;

rollback to savepoint p1;

select * from owner; ( This command will display all records except record 1)

commit;

FOREIGN KEY

create table owner

(

owner_id int(3) not null auto_increment primary key,

owner_name varchar(50) not null,

owner_state varchar(20) not null

)engine=innodb;

create table pet

pet_id int(3) not null auto_increment primary key,

pet_name varchar(50) not null,

pet_weight float(5,3) not null,

owner_id int(3),

foreign key (owner_id) references owner (owner_id)

)engine=innodb;

DROP FOREIGN KEY

1. First execute this command:

show create table child;

The result of the above code is:

CREATE TABLE `pet` (

`pet_id` int(3) NOT NULL AUTO_INCREMENT,

`pet_name` varchar(50) NOT NULL,

`pet_weight` float(5,3) NOT NULL,

`owner_id` int(3) DEFAULT NULL,

PRIMARY KEY (`pet_id`),

KEY `owner_id` (`owner_id`),

CONSTRAINT `pet_ibfk_1` FOREIGN KEY (`owner_id`) REFERENCES `owner` (`owner_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

2. Next drop the key.

        ALTER TABLE pet DROP KEY owner_id;

3. Finally drop the foreign key.

ALTER TABLE pet DROP FOREIGN KEY pet_ibfk_1;

ADD FOREIGN KEY

alter table pet add foreign key (owner_id) references owner (owner_id);

ADDING RECORDS INTO TABLES

insert into owner (owner_name, owner_state) values

('Ali','Melaka'),

('Kumar','Kedah'),

('Mary','Melaka'),

('Jeff','Penang'),

('Siti','Perak'),

('Raymond','Kedah');

insert into pet(pet_name, pet_weight,owner_id) values

('Scooby',2.33,2),

('Kitty',11.10,1),

('Chicky',1.20,3),

('Moose',4.01,2),

('Tweety',7.25,4),

('Chip',3.90,6),

('Scrappy',3.10,5),

('Woof',15.80,5),

('Poop',13.05,4);

1. Display all owners with their pets

select * from owner, pet where owner.owner_id = pet.owner_id;

2. Display all of Jeff’s pets

select * from owner, pet where owner.owner_id = pet.owner_id and

owner.owner_name = 'Jeff';

3. Counts number of Jeff’s pets

select owner.owner_name, count(*) as "Total" from owner, pet

where owner.owner_id = pet.owner_id and

owner.owner_name = 'Jeff';

4. Counts number of pets of each owner

select owner.owner_name, count(*) as "Total" from owner, pet

where owner.owner_id = pet.owner_id group by owner.owner_id;

5. Average pet weight per owner

select owner.owner_name, round(avg(pet.pet_weight),2) as "Average"

from owner, pet

where owner.owner_id = pet.owner_id group by owner.owner_name;

6. Average pet weight per owner

select owner.owner_name, min(pet.pet_weight), max(pet.pet_weight) from owner, pet

where owner.owner_id = pet.owner_id group by owner.owner_name;

The HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

7. Finidng the owner with more than one pet

select owner.owner_name, count(pet.pet_id) from pet, owner

where owner.owner_id = pet.owner_id group by owner.owner_name

having count(pet.pet_id) > 1;

8. Finidng the owner with pets total weight more than 10 kg

select owner.owner_name, sum(pet.pet_weight) as "Total" from pet, owner

where owner.owner_id = pet.owner_id group by owner.owner_name

having total > 10;

MULTIPLE TABLE EXAMPLE

1. Create the relevant tables which are customer, invoice and product

create table customer

(

cid int(2) not null auto_increment primary key,

cname varchar(20) not null

);

create table product

(

pid int(2) not null primary key,

pname varchar(20) not null,

pprice decimal(6,2) not null

);

create table invoice

(

iid int(3) not null auto_increment primary key,

idate date not null,

iqty int(3) not null,

cid int(2),

pid char(3),

foreign key (cid) references customer(cid),

foreign key (pid) references product(pid)

);

2. Insert records into the tables.

insert into customer (cname) values

("Ali"), ("Kamal"), ("Mary"), ("Jenny");

insert into product (pid,pname,pprice) values

("P1","Chair", 34.00),

("P2","Desk", 100.00),

("P3","Keyboard", 21.00),

("P4","Mouse", 32.00),

("P5","Printer", 399.00);

insert into invoice (idate,iqty,cid,pid) values

("2009-10-10",10,2,"P2"),

("2010-03-21", 2,1,"P1"),

("2009-02-17", 1,1,"P5"),

("2013-12-10",25,2,"P5"),

("2012-09-15",110,3,"P3"),

("2009-05-23", 9,2,"P4"),

("2008-07-19",10,4,"P2");

3. Display all information about what the customers have bought.

select * from customer, invoice, product where

customer.cid = invoice.cid and invoice.pid = product.pid;

4. Display all information about what the customers have bought and sort then by name. By default will be in ascending order.

select * from customer, invoice, product where

customer.cid = invoice.cid and invoice.pid = product.pid

order by cname;

5. Display all details including the total amount to be paid per invoice.

select *, (product.pprice*invoice.iqty) as "Pay"

from customer, invoice, product

where customer.cid = invoice.cid and invoice.pid = product.pid

order by customer.cname;

6. Display the total amount each customer has to payall details including the total amount to be paid per invoice.

select customer.*, sum(product.pprice*invoice.iqty) as "Pay" from customer, invoice, product

where customer.cid = invoice.cid and invoice.pid = product.pid

group by customer.cname;

7. Display the total quantity ordered for every product.

select product.*, sum(invoice.iqty) as "Total Order" from invoice, product

where invoice.pid = product.pid

group by product.pid;

8. Display the total quantity and total price ordered for every product.

select product.*, sum(invoice.iqty) as "Quantity", invoice.iqty*product.pprice

from invoice, product

where invoice.pid = product.pid

group by product.pid;

No comments

About me

Labels

recent posts

Follow Us

Blog Archive