My Sql

Only available on StudyMode
  • Download(s) : 91
  • Published : December 2, 2012
Open Document
Text Preview
This query will create a table

create table furniture
(
no int(6) NOT NULL PRIMARY KEY,
itemname varchar(20) default NULL,
type varchar(10) default NULL,
dateofstock date default NULL,
price decimal(6,0),
discount int(2)
);
Query OK, 0 rows affected (0.22 sec)

This query will create a table

create table arrival
(
no int(6) NOT NULL PRIMARY KEY,
itemname varchar(20) default NULL,
type varchar(20) default NULL,
dateofstock date default NULL,
price int(6) default NULL,
discount int(2)
);
Query OK, 0 rows affected (0.22 sec)

mysql> desc furniture;
+-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | no | int(6) | NO | PRI | NULL | | | itemname | varchar(20) | YES | | NULL | | | type | varchar(10) | YES | | NULL | | | dateofstock | date | YES | | NULL | | | price | decimal(6,0) | YES | | NULL | | | discount | int(2) | YES | | NULL | | +-------------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)

inserting values into the table furniture and arrival

insert into furniture
values(1,"white lotus","double Bed","2002/02/23",30000,25);

insert into furniture
values(2,"Pink Feather","Baby cot","2002/01/20",7000,20);

insert into furniture
values(3,"Dolphine","Baby cot","2002/02/19",9500,20);

insert into furniture
values(4,"Decent","Office Table","2002/01/01",25000,30);

insert into furniture
values(5,"Comfort Zone","Double Bed","2002/01/12",25000,25);

insert into furniture
values(6,"Donald","Baby cot","2002/01/12",6500,15);

insert into furniture
values(7,"Royal Finish","office Table","2002/10/20",18000,25);

insert into furniture
values(8,"Royal Tiger","Sofa","2002/03/22",31000,25);

insert into furniture
values(9,"Econo Sitting","Sofa","2001/05/10",9500,25);

insert into furniture
values(10,"Eating Paradise","Dining Table","2002/08/19",11500,25);

insert into arrival
values(11,"Wood Comfort","double Bed","2003/02/23",25000,25);

insert into arrival
values(12,"Old Fox","Sofa","2005/01/20",17000,20);

insert into arrival
values(13,"Mickey","Baby cot","2004/02/19",7500,15);

1 . This query will display all records from the table furniture where the type is "baby cot"

select * from furniture
where type="baby cot";

+----+--------------+----------+-------------+-------+----------+ | no | itemname | type | dateofstock | price | discount | +----+--------------+----------+-------------+-------+----------+ | 2 | Pink Feather | Baby cot | 2002-01-20 | 7000 | 20 | | 3 | Dolphine | Baby cot | 2002-02-19 | 9500 | 20 | | 6 | Donald | Baby cot | 2002-01-12 | 6500 | 15 | +----+--------------+----------+-------------+-------+----------+ 3 rows in set (0.00 sec)

2. This query will display all records from the table furniture where the price is greater than 15000

select * from furniture
where price>15000;
+----+--------------+--------------+-------------+-------+----------+ | no | itemname | type | dateofstock | price | discount | +----+--------------+--------------+-------------+-------+----------+ | 1 | white lotus | double Bed | 2002-02-23 | 30000 | 25 | | 4 | Decent | Office Table | 2002-01-01 | 25000 | 30 | | 5 | Comfort Zone | Double Bed | 2002-01-12 | 25000 | 25 | | 7 | Royal Finish | office Table | 2002-10-20 | 18000 | 25 | | 8 | Royal Tiger | Sofa | 2002-03-22 | 31000 | 25 | +----+--------------+--------------+-------------+-------+----------+ 5 rows in set (0.00 sec)

3. This query will display item name and type from the table furniture where the date of stock is greater than "2002/01/22"

select itemname,type from furniture
where...
tracking img