use master;

go

drop database FastFoodClass04;

go

create database FastFoodClass04;

go

use FastFoodClass04;

go

create table Food(

    fID int constraint PK_Food primary key,

    Name nvarchar(30),

    Price money

);

go

create table FoodStuff(

    sID int constraint PK_FoodStuff primary key,

    Name nvarchar(30),

    Type tinyint

);

go

create table FoodDetail(

    fID int constraint FK_Food references Food,

    sID int constraint FK_FoodStuff references FoodStuff,

    constraint PK_FoodDetail primary key(fID, sID)

);

go

--chen du lieu

insert into Food values (1,N'Gà hấp xì dầu',27000);

insert into Food values (2,N'Sườn nõn sốt chanh',33000);

insert into Food values (3,N'Bò xào hành tỏi',23000);

insert into Food values (4,N'Cá thu sốt',31000);

select * from Food;

go

insert into FoodStuff values (1,N'Thịt gà',1);

insert into FoodStuff values (2,N'Thịt lợn',1);

insert into FoodStuff values (3,N'Thịt bò',1);

insert into FoodStuff values (4,N'Cá thu',1);

insert into FoodStuff values (5,N'Hành',2);

insert into FoodStuff values (6,N'Tỏi',2);

insert into FoodStuff values (7,N'Cà chua',2);

insert into FoodStuff values (8,N'Xì dầu',2);

insert into FoodStuff values (9,N'Chanh',2);

insert into FoodStuff values (10,N'Hạt tiêu',2);

select * from FoodStuff;

go

insert into FooDDetail values (1,1);

insert into FooDDetail values (1,8);

insert into FooDDetail values (2,2);

insert into FooDDetail values (2,9);

insert into FooDDetail values (2,7);

insert into FooDDetail values (2,5);

insert into FooDDetail values (3,3);

insert into FooDDetail values (3,5);

insert into FooDDetail values (3,6);

insert into FooDDetail values (4,4);

insert into FooDDetail values (4,7);

select * from FooDDetail;

go

--2

select  a.Name [Food Name], c.name [FoodStuff Name]

from Food a, FoodDetail b, FoodStuff c

where a.fid=b.fid and b.sid=c.sid

order by a.Name

;

--3

select * from FoodStuff where sID not in

(select sID from FoodDetail);

--4

select a.Name, Count(a.Name) [So mon che bien]

from FoodStuff a,FoodDetail b

where a.sID = b.sID

group by a.Name having count(a.Name)>1;

--5

select top 1 with ties a.Name, count(*) [So thuc pham]

from Food a, FoodDetail b

where a.fID = b.fID

group by a.Name

order by [So thuc pham] desc

;

--6

select  Name, case

                when type=1 then N'Thực phẩm chính'

                when type=2 then N'Gia vị'

             end  [Type]

from FoodStuff;

--cach 2

select Name, N'Thực phẩm chính' [Type]

from FoodStuff where Type=1

union

select Name, N'Gia vị' [Type]

from FoodStuff where type=2;

--7 Lay ra nhung mon an co gia lon hon 30 nghin

select * from Food where Price>=30000;

--8 Dua ra mon an co gia lon hon trung binh cong

--gia cua tat ca cac mon an

select * from Food

where Price>=(select avg(Price) from Food);

--9 Dua ra cac mon co gia dat nhat

select top 1 with ties * from Food

order by Price desc;

--10 Bao gia mon an tang them 10%

select  fID, Name, Price*1.1 [Price]

from Food;

--thu nhap ngay theo tieng viet

create table test(

    id int primary key,

    birth datetime

);

go

set dateformat dmy;

go

insert into test values (1,'29/11/2008');

select * from test;

select id, convert(varchar(10),birth,103) birth

from test;

alter table test add phone varchar(20);