SET NAMES UTF8;
DROP DATABASE IF EXISTS md;
CREATE DATABASE md CHARSET=UTF8;
USE md;

CREATE TABLE stu(
    sid INT PRIMARY KEY AUTO_INCREMENT,
    gender VARCHAR(1),
    class VARCHAR(3)
);

CREATE TABLE phoneType(
    ptId INT PRIMARY KEY AUTO_INCREMENT,
    ptName VARCHAR(16)
);

CREATE TABLE phone(
    pid INT PRIMARY KEY AUTO_INCREMENT,
    pname VARCHAR(32),
    ptId INT
);

CREATE TABLE sale(
    id INT PRIMARY KEY AUTO_INCREMENT,
    pid INT,
    sid INT,
    price DECIMAL(7,2),
    total INT
);

INSERT INTO stu VALUES(NULL,'M','171');
INSERT INTO stu VALUES(NULL,'F','172');
INSERT INTO stu VALUES(NULL,'F','173');
INSERT INTO stu VALUES(NULL,'M','171');
INSERT INTO stu VALUES(NULL,'M','172');
INSERT INTO stu VALUES(NULL,'M','171');

INSERT INTO phoneType VALUES(NULL,'iPhone');
INSERT INTO phoneType VALUES(NULL,'Xiaomi');
INSERT INTO phoneType VALUES(NULL,'HUAWEI');
INSERT INTO phoneType VALUES(NULL,'OPPO');

INSERT INTO phone VALUES(NULL,'iPhone 11',1);
INSERT INTO phone VALUES(NULL,'iPhone X',1);
INSERT INTO phone VALUES(NULL,'HUAWEI Mate 30',2);
INSERT INTO phone VALUES(NULL,'HUAWEI Pro 30',2);
INSERT INTO phone VALUES(NULL,'Xiaomi 9',3);
INSERT INTO phone VALUES(NULL,'Xiaomi 8',3);
INSERT INTO phone VALUES(NULL,'OPPO K5',4);
INSERT INTO phone VALUES(NULL,'OPPO Reno 2',4);

INSERT INTO sale VALUES(NULL,1,1,12399.00,1);
INSERT INTO sale VALUES(NULL,2,2,9999.00,2);
INSERT INTO sale VALUES(NULL,3,3,12000.00,3);
INSERT INTO sale VALUES(NULL,4,4,14000.00,3);
INSERT INTO sale VALUES(NULL,5,5,3999.00,6);
INSERT INTO sale VALUES(NULL,6,6,2999.00,5);
INSERT INTO sale VALUES(NULL,7,6,4999.00,3);
INSERT INTO sale VALUES(NULL,8,1,5999.00,4);
INSERT INTO sale VALUES(NULL,2,1,9999.00,1);
INSERT INTO sale VALUES(NULL,2,3,9999.00,1);
INSERT INTO sale VALUES(NULL,5,3,3999.00,3);
INSERT INTO sale VALUES(NULL,5,3,3999.00,4);
INSERT INTO sale VALUES(NULL,5,5,3999.00,6);
INSERT INTO sale VALUES(NULL,6,4,2999.00,8);
INSERT INTO sale VALUES(NULL,8,1,5999.00,2);
INSERT INTO sale VALUES(NULL,1,3,12399.00,1);