Bài chữa 9-4 của cô giáo

create database ElecStore
use ElecStore
-- tao 4 bang
create table Customer(
cID int not null,
cName varchar(25),
cAge tinyint,
cGender varchar(10))



create table Orders(
oID int not null,
cID int not null,
oDate datetime)



create table Product(
pID int not null,
pName varchar(25),
pPrice int)


create table OrderDetail(
oID int not null,
pID int not null,
odQTY int)

-- them cac rang buoc vao cac bang
alter table Customer
add constraint pk_customer primary key(cID)

alter table Orders
add constraint pk_orders primary key (oID)

alter table Orders
add constraint fk_orders foreign key (cID) references Customer(cID)


alter table Product
add constraint pk_product primary key(pID)

alter table OrderDetail
add constraint fk_ODetail_Order foreign key(oID) references Orders(oID)

alter table OrderDetail
add constraint fk_ODetail_Product foreign key(pID) references Product(pID)


--nhap du lieu cho cac bang

insert Customer values(1,'Elisha Cuthbert',26,'0')
insert Customer values(2,'Cristiano Ronaldo',23,'1')
insert Customer values(3,'Gemma Atkinson',24,'0')
insert Customer(cID,cName,cAge) values(4,'Maria Sharapova',22)




insert Orders values(1,1,'3/21/2008')
insert Orders values(2,2,'3/23/2008')
insert Orders values(3,1,'3/16/2008')


insert Product values(1,'Washing Machine',3)
insert Product values(2,'Fridge',5)
insert Product values(3,'Air Conditioner',7)
insert Product values(4,'Electric Fan',1)
insert Product values(5,'Electric Cooker',2)


insert OrderDetail values(1,1,3)
insert OrderDetail values(1,3,7)
insert OrderDetail values(1,4,2)
insert OrderDetail values(2,1,1)
insert OrderDetail values(3,1,
insert OrderDetail values(2,5,4)
insert OrderDetail values(2,3,3)

--thuc hien cac cau lenh truy van
-- 2. Hiển thị danh sách các hóa đơn (chú ý: danh sách phải được sắp xếp theo trường oDate):
select * from Orders
order by oDate

--3. Hiển thị danh sách khách hàng gồm tên và ký tự đầu tiên của tên:
select cName , left(cName,1) as KyTuDau
from Customer

--4. Thêm trường Status có kiểu varchar(100) vào bảng Customer.
alter table Customer
add Status varchar(100)

/*5. Sửa giá trị trong Status theo các luật sau:
- Nếu cGender=0 thì gán Status=’Female’
- Nếu cGender=1 thì gán Status=’Male’
- Nếu cGender=Null thì gán Status=’Unknow’
Sau đó hiển thị danh sách khách hàng:
*/
update Customer
set Status='Female'
where cGender = 0


update Customer
set Status='Male'
where cGender = 1



update Customer
set Status='Unknow'
where cGender is null

select * from Customer

--6. Hiển thị sản phẩm có giá(price) cao nhất:(chu y phan biet hai cau 6 va 7 nhe)
select top 1 pName, pPrice
from Product
order by pPrice desc

--7. Hiển thị tất cả các sản phẩm có giá(price) thấp nhất:
select top 1 with ties pName, pPrice
from Product
order by pPrice asc

--8. Hiển thị danh sách sản phẩm gồm tên và giá kết hợp trong 1 cột
select 'Price of '+pName+' is '+convert(varchar(10),pPrice) as 'Price of Product'
from Product

--9. Tạo một bảng đặt tên là ‘Top3Product’ bảng này chứa 3 sản phẩm có giá cao nhất gồm các trường sau Thứ Hạng(Rank), Tên và Giá như sau:
select top 3 rank() over (order by pPrice desc) as Rank, pName, pPrice
into Top3Product
from Product

select * from Top3Product

--10. Hiển thị những khách hàng mà tên có độ dài 15 ký tự như sau:
select * from Customer
where len(cName)=15

--11. Trong bảng Product trên trường pName:
--a. Thay đổi kiểu dữ liệu từ varchar(25) thành varchar(max)
alter table Product
alter column pName varchar(max)
--b. Thêm chuỗi ‘New ‘ vào đầu tên của tất cả các sản phẩm, sau đó hiển thị lên như sau:
--Cach 1:
update Product
set pName = 'New '+ pName
--Cach 2:
update Product
set pName .write('New ',0,0)
select * from Product

--c. Thay thế 3 ký tự đầu của tên bằng chuỗi ‘Old’, rồi hiển thị lên như sau:
update Product
set pName .write('Old',0,3)
select * from Product

--12. Xóa tất cả các khóa ngoại trong các bảng trên.
alter table Order
drop constraint fk_orders

alter table OrderDetail
drop constraint fk_ODetail_Order

alter table OrderDetail
drop constraint fk_ODetail_Product

--13. Hiển thị tất cả các sản phẩm có chuỗi ‘Electric’ ở trong pName, sau đó hiển thị thêm tổng số dòng của danh sách vừa hiện như sau:
select * from Product
where pName like '%Electric%'

select count(*) as 'Total row'
from Product
where pName like '%Electric%'

--14. Hiển thị ngày giờ hiện tại và ngày giờ hiện tại cộng thêm 5000 phút như sau:
select getdate() as 'Current Date',dateadd(mm,5000,getdate()) as 'Current Date + 5000 minutes'