xzvz

CREATE database test;
use test;

CREATE TABLE lop  (
  LopID int PRIMARY KEY NOT NULL,
  TenLop varchar(255)
);

-- ----------------------------
-- Records of lop
-- ----------------------------
INSERT INTO `lop` VALUES (1, 'CNTT');
INSERT INTO `lop` VALUES (2, 'TOAN');
INSERT INTO `lop` VALUES (3, 'HOA HOC');

-- ----------------------------
-- Table structure for sinhvien
-- ----------------------------
CREATE TABLE sinhvien  (
  MASV int(11) PRIMARY KEY NOT NULL,
  TenSV varchar(255),
  NamSinh int(4),
  LopID int(11)
);



INSERT INTO `sinhvien` VALUES (1, 'Nguyen Van A', 1990, 1);
INSERT INTO `sinhvien` VALUES (2, 'Nguyen Van B', 1991, 1);
INSERT INTO `sinhvien` VALUES (3, 'Nguyen Van C', 1980, 2);
INSERT INTO `sinhvien` VALUES (4, 'Nguyen Van D', 1976, 3);
INSERT INTO `sinhvien` VALUES (5, 'Nguyen Van E', 1990, 2);
INSERT INTO `sinhvien` VALUES (6, 'Nguyen Van f', 1954, 3);
INSERT INTO `sinhvien` VALUES (7, 'Nguyen Van G', 1967, 1);
INSERT INTO `sinhvien` VALUES (8, 'Nguyen Van H', 1978, 1);
INSERT INTO `sinhvien` VALUES (9, 'Nguyen Van I', 1991, 2);
INSERT INTO `sinhvien` VALUES (10, 'Nguyen Van K', 2000, 3);


alter table sinhvien
add constraint FK_LopID	 foreign key (LopID) references lop(LopID);
select * from sinhvien;
select * from lop;

SELECT *
from sinhvien
INNER JOIN lop  on sinhvien.LopID = lop.TenLop;

SELECT *
FROM sinhvien
INNER JOIN lop ON sinhvien.LopID = lop.LopID;
	SELECT
		sinhvien.MASV,
		sinhvien.TenSV,
		sinhvien.NamSinh,
		CASE
			WHEN lop.LopID = 1 THEN 'CNTT'
			WHEN lop.LopID = 2 THEN 'TOAN'
			WHEN lop.LopID = 3 THEN 'HOA HOC'
			ELSE 'Unknown'
		END AS TenLop
	FROM sinhvien
	INNER JOIN lop ON sinhvien.LopID = lop.LopID;


SELECT
  sinhvien.MASV,
  sinhvien.TenSV,
  sinhvien.NamSinh,
  lop.TenLop
FROM sinhvien
INNER JOIN lop ON sinhvien.LopID = lop.LopID;


create table khoa(
	khoaID int(11) primary key,
	TenKhoa varchar(255)
);
 alter table sinhvien