2020-11-27 07:16:54

create database BT1770
use BT1770

create table product(
	id int identity(1,1),
	constraint pk_product primary key(id),
	title nvarchar (200),
	thumnail nvarchar(500),
	content text
)

create table list(
	id int identity(1,1),
	name nvarchar(100)
)

alter table list add constraint pk_list primary key (id)

alter table product add price float

alter table product drop column price 

alter table product add price float

alter table product add num int

alter table product add created_at datetime

alter table product add updated_at datetime

alter table product add id_cat int

select * from product

alter table product add constraint fk_id_cat foreign key (id_cat) references list(id)

insert into list (name)
values
('A'),
('B'),
('C')

insert into product (title, thumnail, content, price, num, created_at , updated_at , id_cat)
values
('A', 'http1', 'abc', '1000', '1', '2020-04-04 10:00:00', '2020-04-04 10:00:05', '1'),
('B', 'http2', 'aaa', '1000', '1', '2020-04-04 10:00:01', '2020-04-04 10:00:06', '1'),
('C', 'http3', 'vvv', '1000', '1', '2020-04-04 10:00:02', '2020-04-04 10:00:07', '1'),
('D', 'http4', 'www', '1000', '1', '2020-04-04 10:00:03', '2020-04-04 10:00:08', '1'),
('E', 'http5', 'qqq', '1000', '1', '2020-04-04 10:00:04', '2020-04-04 10:00:09', '1')

insert into product(title, thumnail, content, price, num, created_at, updated_at, id_cat)
values
('aaa', 'httpp1','aaaa', '111','2', '2020-04-03 10:00:00', '2020-11-25 10:00:00','2'),
('bbb', 'httpp2','bbbb', '222','3', '2020-04-02 10:00:00', '2020-11-25 10:00:00','2'),
('ccc', 'httpp3','cccc', '333','5', '2020-04-01 10:00:00', '2020-11-25 10:00:00','2'),
('ddd', 'httpp4','dddd', '444','2', '2020-04-05 10:00:00', '2020-11-25 10:00:00','2'),
('eee', 'httpp5','eeee', '555','1', '2020-04-06 10:00:00', '2020-11-25 10:00:00','2')


insert into product(title, thumnail, content, price, num, created_at, updated_at, id_cat)
values
('qqq', 'httppp1','qq', 100,'5', '2020-04-04 10:00:00', '2020-11-25 10:00:00','3'),
('www', 'httppp2','ww', '','5', '2020-04-04 10:00:00', '2020-11-25 10:00:00','3'),
('eee', 'httppp3','ee', 0, '', '2020-04-04 10:00:00', '2020-11-25 10:00:00','3'),
('rrr', 'httppp4','rr', 5,'5', '2020-04-04 10:00:00', '2020-11-25 10:00:00','3'),
('ttt', 'httppp5','tt', 8,'5', '2020-04-04 10:00:00', '2020-11-25 10:00:00','3')

select * from product
select * from list

update product
set price = 5000 where  price in (0, null, '')

update product set price = 5000 where price is null

update product set price = (price * 0.9) where created_at <= '2020/06/06'

delete from product where created_at < '2016/12/31'

2020-11-27 07:11:41

create database BT1770
go

use BT1770
go  

create table product (
	id int primary key identity(1, 1),
	title nvarchar(100),
	thumbnail nvarchar(500),
	content text
)
go

create table Sanpham (
	id int primary key identity(1, 1),
	name nvarchar(100) not null
)
go

alter table Sanpham
add price float,
num int,
created_at date,
updated_at date

alter table Sanpham
add id_cat int


select * from product
select * from Sanpham


insert into product(title, thumbnail, content)
values
('Tivi 1', '', 'San pham 1'),
('Tivi 2', '', 'San pham 2'),
('Tivi 3', '', 'San pham 3'),
('Tivi 4', '', 'San pham 4'),
('Tivi 5', '', 'San pham 5')

insert into Sanpham(name, price, num, created_at, updated_at, id_cat)
values
('Sp 1', 12.000, 12, '2020-11-12', '2020-11-23', 1),
('Sp 2', 12.000, 12, '2020-11-12', '2020-11-23', 2),
('Sp 3', 12.000, 12, '2020-11-12', '2020-11-23', 3),
('Sp 4', 12.000, 12, '2020-11-12', '2020-11-23', 4),
('Sp 5', 12.000, 12, '2020-11-12', '2020-11-23', 5)

update Sanpham
set price = 5000 where price in (0,null,'')

2020-11-27 07:01:57

create database Quanlybanhang
use Quanlybanhang

create table product(
	id int primary key identity(1,1),
	title nvarchar(100),
	thumbnail varbinary(300),
	content text
)
go

select * from product

alter table product
add price float, number int, created_at datetime, updated_at datetime, id_cat int references category(id)

insert into product(title, thumbnail, content, price, number, created_at, updated_at, id_cat)
values 
('ao khoac','url_aokhoac','ao khoac made in vietnam', 0, 100, '25/11/2020 4:39', '25/11/2020 4:39'),
('ao phong','url_aophong','ao phong made in vietnam', 100000, 250, '5/03/2020 4:39', '25/11/2020 4:39'),
('vay','url_vay','vay made in vietnam', 260000, 20, '25/04/2015 4:39', '25/11/2020 4:39'),
('quan bo','url_quanbo','quan bo made in vietnam', 520000, 7600, '31/1/2020 4:39', '25/11/2020 4:39'),
('quan dui','url_quandui','quan dui made in vietnam', 85000, 120, '25/11/2020 4:39', '25/11/2020 4:39')

create table category(
	id int primary key identity(1,1),
	category nvarchar(100)	
)
go

select * from category

insert into category
values
('ao'),
('quan'),
('vay')

update product set id_cat = 1 where title like '%ao%'
update product set id_cat = 2 where title like '%quan%'
update product set id_cat = 3 where title like '%vay%'

update product set price = 5000 where price=0 or price=null

update product set price = price*0.9 where created_at <= '06/06/2020'

delete from product where created_at < '31/12/2016'

2020-11-27 06:53:14

create database quan_tri_CSDL

use quan_tri_CSDL

create table product(
  id int primary key identity(1,1),
  title text not null,
  thubmnail nvarchar(200),
  content text
)
go

create table list_danh_muc(
 id int primary key identity(1,1),
 name nvarchar(100),
)
go

alter table list_danh_muc
add price float

alter table list_danh_muc
add num int,
    created_at date,
	updated_at date,
	id_cat int references product(id)

select *  from list_danh_muc
select * from product

insert into product(title,thubmnail,content)
values
('quan1','url 01','quan den quan sin vip pro 123'),
('quan2','url 02','quan den quan sin vip pro 123'),
('quan3','url 03','quan den quan sin vip pro 123'),
('quan4','url 04','quan den quan sin vip pro 123'),
('quan5','url 05','quan den quan sin vip pro 123')

insert into list_danh_muc(name,price,num,created_at,updated_at,id_cat)
values
('quan a au',200000,1,'2020-11-25 16:46:00','2020-11-25 16:46:00',1),
('quan au',300000,1,'2020-11-25 16:46:00','2020-11-25 16:46:00',2),
('quan a',400000,0,'2020-11-25 16:46:00','2020-11-25 16:46:00',3)

update list_danh_muc
set price = 5000
where num =0  or num = null

update list_danh_muc
set price = price - price*0.1
where created_at <= '2020-06-06'

delete from list_danh_muc where updated_at <= '2020-12-31

2020-11-27 06:52:18

---hệ thống quản trị CSDL cho một cửa hàng thời trang tại hà nội---
create table product (
id int identity (1,1) primary key,
title nvarchar (200),
thumbnail nvarchar (200),
content text
)
go

---danh mục sản phẩm---
create table product_1(
id int identity (1,1),
name nvarchar (200)
)
go
alter table product_1
add price float;
alter table product_1
add num float;
alter table product_1
add created_at datetime;
alter table product_1
add updated_at datetime;
alter table product_1
add id_cat int;

--- bổ sung cột vảo bảng sản phẩm ---

insert into product (title,thumbnail,content)
values
('quan','abc.jpg','abc'),
('ao','bbc.jpg','bbc'),
('vay','cbc.jpg','cbc'),
('mu','dbc.jpg','dbc'),
('khan','ebc.jpg','ebc')

select *from product
 

2020-11-27 04:24:56

create database gokisoft_buoi4_quanlybanhang
use gokisoft_buoi4_quanlybanhang

create table product(
id int identity(1,1),
constraint pk_product primary key(id),
title nvarchar (200),
thumnail nvarchar(500),
content text
)

create table list(
id int identity(1,1),
name nvarchar(100)
)

alter table list
add constraint pk_list primary key (id)

alter table product
add price float

alter table product
drop column price 

alter table product
add price float

alter table product
add num int

alter table product
add created_at datetime

alter table product
add updated_at datetime

alter table product
add id_cat int

select * from product

alter table product
add constraint fk_id_cat foreign key (id_cat) references list(id)

--Them du lieu san pham

insert into list (name)
values
('Trang suc kim cuong'),
('Trang suc vang'),
('Trang suc bac')

insert into product (title, thumnail, content, price, num, created_at , updated_at , id_cat)
values
('Nhan kim cuong 1 carat', 'url_kc1','Bang kim cuong nang 1 carat', '1000','5', '2020/11/25 10:00:00', '2020/11/25 10:00:00','1'),
('Nhan kim cuong 2 carat', 'url_kc2','Bang kim cuong nang 1 carat', '2000','5', '2020/06/10 10:00:00', '2020/11/25 10:00:00','1'),
('Nhan kim cuong 3 carat', 'url_kc3','Bang kim cuong nang 1 carat', '3000','5', '2020/11/25 10:00:00', '2020/11/25 10:00:00','1'),
('Nhan kim cuong 4 carat', 'url_kc4','Bang kim cuong nang 1 carat', '4000','5', '2020/11/25 10:00:00', '2020/11/25 10:00:00','1'),
('Nhan kim cuong 5 carat', 'url_kc5','Bang kim cuong nang 1 carat', '5000','5', '2020/04/25 10:00:00', '2020/11/25 10:00:00','1')

insert into product(title, thumnail, content, price, num, created_at, updated_at, id_cat)
values
('Lac tay vang 10 carat', 'url_v1','Bang vang nang 10 carat', '100','5', '2020-04-04 10:00:00', '2020-11-25 10:00:00','2'),
('Lac tay vang 20 carat', 'url_v2','Bang vang nang 20 carat', '200','5', '2020-11-23 10:00:00', '2020-11-23 10:00:00','2'),
('Lac tay vang 30 carat', 'url_v3','Bang vang nang 30 carat', '300','5', '2020-11-23 10:00:00', '2020-11-23 10:00:00','2'),
('Lac tay vang 40 carat', 'url_v4','Bang vang nang 40 carat', '400','5', '2020-11-23 10:00:00', '2020-11-23 10:00:00','2'),
('Lac tay vang 50 carat', 'url_v5','Bang vang nang 50 carat', '500','5', '2016-01-25 10:00:00', '2020-11-25 10:00:00','2')

insert into product(title, thumnail, content, price, num, created_at, updated_at, id_cat)
values
('Vong co bac 10 carat', 'url_b1','Bang bac nang 10 carat', 100,'5', '2020-04-04 10:00:00', '2020-11-25 10:00:00','3'),
('Vong co bac 20 carat', 'url_b2','Bang bac nang 20 carat', 200,'5', '2020-11-23 10:00:00', '2020-11-23 10:00:00','3'),
('Vong co bac 30 carat', 'url_b3','Bang bac nang 30 carat', 0 ,'5', '2020-11-23 10:00:00', '2020-11-23 10:00:00','3'),
('Bac mieng che tac 400 carat', 'url_b4','Bang bac che tac nang 400 carat','','1', '2016-01-25 10:00:00', '2020-11-25 10:00:00','3'),
('Bac nguyen khoi 500 carat', 'url_b5','Bang bac nguyen khoi nang 500 carat',null,'1', '2016-01-25 10:00:00', '2020-11-25 10:00:00','3')

select * from product
select * from list

update product
set price = 5000 where  price in (0,null,'')

update product
set price = 5000 where  price is null

update product
set price = price*0.9 
where created_at <= '2020/06/06'

delete from product 
where created_at < '2016/12/31'

2020-11-26 14:31:27

----bang sản phẩm
create table product (
id int identity (1,1) primary key,
title nvarchar (100),
thumbnail nvarchar (500),
content text
)
go
----bang danh muc san pham
create table product_list(
id int identity (1,1),
name nvarchar (100)
)
go
alter table product
add price int
alter table product
add num int
alter table product
add created_at datetime
alter table product
add updated_at datetime
alter table product
add id_cat int

alter table product_list
add constraint fk_id_sp foreign key (id) references product(id)

select * from product
select * from product_list

insert into product(title,thumbnail,content)
values
('ban','aaaaa','tot'),
('ghe','aaaaa','tot'),
('gia sach','aaaaa','tot'),
('tu','aaaaa','tot'),
('giuong','aaaaa','tot')

insert into product_list(name)
values
('van phong'),
('phong ngu'),
('hoc tap')
 update product
 set price = 500
 where price =0 or price is null 

 update product
 set num = 12
 where num =0 or num is null 

 update product
 set created_at =
 '2020-06-05 10:00:00'
 where product.id =1
 update product
 set created_at =
 '2020-06-05 10:00:00'
 where product.id =5
 update product
 set created_at =
 '2020-07-05 10:00:00'
 where product.id =2
 update product
 set created_at =
 '2020-08-05 10:00:00'
 where product.id =3
 update product
 set created_at =
 '2020-09-05 10:00:00'
 where product.id =4

 update product
 set updated_at ='2020-09-05 10:00:00'
  where product.id =1
  update product
 set updated_at ='2020-09-05 10:00:00'
  where product.id =2
  update product
 set updated_at ='2020-09-05 10:00:00'
  where product.id =3
  update product
 set updated_at ='2020-09-05 10:00:00'
  where product.id =4
  update product
 set updated_at ='2020-09-05 10:00:00'
  where product.id =5

  select * from product


  update product
  set id_cat = 1
  where product.id=1
  update product
  set id_cat = 1
  where product.id=2
  update product
  set id_cat = 2
  where product.id=3
  update product
  set id_cat = 3
  where product.id=4 
  update product
  set id_cat = 3
  where product.id=5 
  update product
  set price = 450
  where product.created_at <'2020-06-06 00:00:00'

  delete from product
  where product.created_at < '2016/12/31 00:00:00'