Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
TRANHOANGLONG
CREATE DATABASE Baitap USE Baitap // TAO BANG CREATE TABLE HANG (MH CHAR(10) NOT NULL, TENHANG CHAR(30) NOT NULL, DVT CHAR(15), NSX CHAR(30), CONSTRAINT K10 PRIMARY KEY (MH)) CREATE TABLE NHAP (HDN CHAR(10) NOT NULL ,NGAYNHAP DATETIME NOT NULL,KH VARCHAR(100), CONSTRAINT K20 PRIMARY KEY (HDN)) CREATE TABLE CTNHAP (HDN CHAR(10) NOT NULL FOREIGN KEY (HDN) REFERENCES NHAP(HDN), MH CHAR(10) NOT NULL FOREIGN KEY(MH) REFERENCES HANG(MH) , SLN FLOAT , DGN FLOAT, CONSTRAINT K30 PRIMARY KEY(HDN,MH)) CREATE TABLE HUYNHAP (HDN CHAR(10) NOT NULL FOREIGN KEY (HDN) REFERENCES NHAP(HDN), NGAYHUY DATETIME , LDHUY VARCHAR(100)) //NHAP DL /1 HANG INSERT INTO HANG (MH, TENHANG, DVT, NSX) VALUES ('1000','Máy tính Dell Vostro 120', 'bộ', 'China') INSERT INTO HANG (MH, TENHANG, DVT, NSX) VALUES ('1001','Máy tinh hp 2450','bộ','Taiwan') INSERT INTO HANG (MH,TENHANG,DVT,NSX) VALUES ('1002','MÁY TÍNH LG 5200','BỘ','KOREA') INSERT INTO HANG (MH,TENHANG,DVT,NSX) VALUES ('1003','MÁY TÍNH IBM 8900','CÁI','USA') INSERT INTO HANG (MH,TENHANG,DVT,NSX) VALUES ('1004','SERVER AS8600','BỘ','USA') /2 NHAP SET DATEFORMAT DMY INSERT INTO NHAP (HDN,NGAYNHAP,KH) VALUES ('N0001','5/7/2019','PHAN THANH TRUNG') INSERT INTO NHAP (HDN,NGAYNHAP,KH) VALUES ('N0002','6/7/2019','NGO QUANG THANH') /3 CT NHAP INSERT INTO CTNHAP (HDN,MH,SLN,DGN) VALUES ('N0001','1000','50','1000') INSERT INTO CTNHAP (HDN,MH,SLN,DGN) VALUES ('N0001','1001','20','1100') INSERT INTO CTNHAP (HDN,MH,SLN,DGN) VALUES ('N0001','1002','30','1200') INSERT INTO CTNHAP (HDN,MH,SLN,DGN) VALUES ('N0002','1001','20','1100') INSERT INTO CTNHAP (HDN,MH,SLN,DGN) VALUES ('N0002','1001','20','1200') INSERT INTO CTNHAP (HDN,MH,SLN,DGN) VALUES ('N0002','1004','50','20000') / 4 HUYNHAP INSERT INTO HUYNHAP(HDN, NGAYHUY,LDHUY) VALUES ('N0001','6/7/2019','CHUA BAN HET HANG') // SUA DL UPDATE HANG SET NSX=('PR OF CHINA') WHERE NSX= 'CHINA' // XOA DL DELETE FROM NHAP WHERE (HDN='N0001') //BAITAP2 CREATE DATABASE BAI2 USE BAI2 // TAO BANG CREATE TABLE DMDONVI(MADV CHAR(10) NOT NULL,TENDV NVARCHAR(50) NOT NULL CONSTRAINT K1 PRIMARY KEY (MADV)) CREATE TABLE DMCHUCVU(MACV CHAR(10) NOT NULL,TENCV NVARCHAR(50) NOT NULL CONSTRAINT K2 PRIMARY KEY (MACV)) CREATE TABLE HSCANBO(MACB CHAR(10) NOT NULL,HO NVARCHAR (30),TEN NVARCHAR(20) NOT NULL,NS DATETIME, PHAI BIT,DC VARCHAR(50),TINHTP NVARCHAR(30),TDHV NVARCHAR(30),TDCM NVARCHAR(30), CONSTRAINT K3 PRIMARY KEY (MACB)) CREATE TABLE QTCONGTAC (MACB CHAR(10) NOT NULL FOREIGN KEY (MACB) REFERENCES HSCANBO(MACB), MADV CHAR(10) NOT NULL FOREIGN KEY(MADV) REFERENCES DMDONVI(MADV), MACV CHAR(10) NOT NULL FOREIGN KEY(MACV) REFERENCES DMCHUCVU(MACV)) //NHAP DMDONVI INSERT INTO DMDONVI (MADV,TENDV) VALUES ('T001','KHU CN TÂN TẠ0') INSERT INTO DMDONVI (MADV,TENDV) VALUES ('T002','KHU CN BIÊN HÒA') INSERT INTO DMDONVI (MADV,TENDV) VALUES ('T003','KHU CN THỦ ĐỨC') INSERT INTO DMDONVI (MADV,TENDV) VALUES ('T004','KHU CN NHÀ BÈ') INSERT INTO DMDONVI (MADV,TENDV) VALUES ('T006','KHU CN BÌNH DƯƠNG') INSERT INTO DMDONVI (MADV,TENDV) VALUES ('T005','KHU CN NHƠN TRẠCH') //NHAP DMCHUCVU INSERT INTO DMCHUCVU(MACV,TENCV) VALUES ('001','CHUYÊN VIÊN CA') INSERT INTO DMCHUCVU(MACV,TENCV) VALUES ('002','CHUYÊN VIÊN') INSERT INTO DMCHUCVU(MACV,TENCV) VALUES ('003','THANH TRA') INSERT INTO DMCHUCVU(MACV,TENCV) VALUES ('004','GIÁM SÁT AN TOÀN') INSERT INTO DMCHUCVU(MACV,TENCV) VALUES ('005','GIÁM SÁT VỆ SINH') INSERT INTO DMCHUCVU(MACV,TENCV) VALUES ('006','GIÁM SÁT TÀI CHÍNH') INSERT INTO DMCHUCVU(MACV,TENCV) VALUES ('007','THỐNG KÊ') INSERT INTO DMCHUCVU(MACV,TENCV) VALUES ('008','KIỂM ĐỊNH') SET DATEFORMAT YMD //NHAP HSCANBO INSERT INTO HSCANBO(MACB, HO,TEN,NS,PHAI,DC,TINHTP,TDHV,TDCM) VALUES ('C001','Phan Thanh','Hùng','1979-03-01','True','100 Đinh Tiên','Tp HCM','Đại học','Cao cấp') INSERT INTO HSCANBO(MACB, HO,TEN,NS,PHAI,DC,TINHTP,TDHV,TDCM) VALUES ('C002','Phạm Thị','Lệ','1978-05-03','False','32 Võ Thị Sáu','Tp HCM','Đại học','Cao cấp') INSERT INTO HSCANBO(MACB, HO,TEN,NS,PHAI,DC,TINHTP,TDHV,TDCM) VALUES ('C003','Nguyễn Văn','Tiên','1980-05-10','True','39 Trần Hưng','Tp HCM','Cao học','Cao cấp') INSERT INTO HSCANBO(MACB, HO,TEN,NS,PHAI,DC,TINHTP,TDHV,TDCM) VALUES ('C004','Ngô Bá','Hùng','1989-08-10','True','','Long An','Trung học','Sơ cấp') INSERT INTO HSCANBO(MACB, HO,TEN,NS,PHAI,DC,TINHTP,TDHV,TDCM) VALUES ('C005','Lê Thị','Bảy','1988-05-06','False','','','Đại học','Trung cấp') INSERT INTO HSCANBO(MACB, HO,TEN,NS,PHAI,DC,TINHTP,TDHV,TDCM) VALUES ('C006','Hồ Thanh','Minh','1986-07-12','True','123 Lê Thị Riêng','Tp HCM','Trung học','Sơ cấp') //NHAP QTCONGTAC INSERT INTO QTCONGTAC(MACB,MADV,MACV,NDCONGTAC,NGABD,NGAYKT) VALUES ('C001','T001',003,'Thống kê tài sản','2019-03-01','2019-03-03') INSERT INTO QTCONGTAC(MACB,MADV,MACV,NDCONGTAC,NGABD,NGAYKT) VALUES ('C001','T005',006,'Thống kê tài chính','2019-03-05','2019-03-09') INSERT INTO QTCONGTAC(MACB,MADV,MACV,NDCONGTAC,NGABD,NGAYKT) VALUES ('C002','T002',001,'Đánh giá hoạt động','2019-03-01','2019-03-07') INSERT INTO QTCONGTAC(MACB,MADV,MACV,NDCONGTAC,NGABD,NGAYKT) VALUES ('C003','T004',003,'Thnah tra đánh giá','2019-03-02','2019-03-04') /* 3/. Sửa DL: Đổi tên TPHCM thành Sài Gòn */ UPDATE HSCANBO SET TINHTP='Sài Gòn' WHERE TINHTP='Tp HCM' /* 4/. Xóa DL: Xóa QTCONGTAC của cán bộ có MACB là C001. */ DELETE FROM QTCONGTAC WHERE (MACB='C001')
run
|
edit
|
history
|
help
0
Task_3_Final
Program1
customers
https://www.hsbc.co.in/ways-to-bank/online-banking/9247/
Chaudhary
Task_7_Final
Joins outside XML element
count events by 2 shift cycles using CTE
QLSV
Cine latino