XAMPP / Praktiline ülesanne/ CREATE TABLE, ALTER TABLE laused.

— 1. Создайте следующие таблицы (CREATE TABLE):

CREATE TABLE Category (
idCategory int primary key AUTO_INCREMENT,
CategoryName varchar(64)
);

CREATE TABLE Product (
idProduct INT PRIMARY key AUTO_INCREMENT,
ProductName varchar(64),
idCategory INT,
Price DECIMAL(4,2)
);

CREATE TABLE Sale (
idSale int primary key AUTO_INCREMENT,
idProduct int,
idCustomer int,
Count int,
DateOfSale date
);

insert into Category( CategoryName )
values(“Shoe”),(“Bandana”),(“hat”);

insert into Product ( ProductName, idCategory, Price )
values(“test1”,1,’12.34′),(“test2”,2,’43.21′),(“test”,3,’11.11′);

insert into Sale ( idProduct, idCustomer, Count, DateOfSale )
values(1,3,5,’2024-01-31′);

–2 Установите необходимые ограничения (NOT NULL, UNIQUE, DEFAULT),
— первичные и внешние ключи (PRIMARY KEY, FOREIGN KEY)



ALTER TABLE Category 
ADD UNIQUE (CategoryName);

ALTER TABLE Category
MODIFY CategoryName varchar(64) NOT NULL;


ALTER TABLE Product
MODIFY ProductName varchar(64) NOT NULL;

ALTER TABLE Product
MODIFY idCategory INT NOT NULL;

ALTER TABLE Product
MODIFY Price DECIMAL(4,2) NOT NULL;

ALTER TABLE Product
ADD FOREIGN KEY (idCategory) REFERENCES Category(idCategory);

ALTER TABLE Sale
MODIFY  idProduct INT NOT NULL;

ALTER TABLE Sale
MODIFY  idCustomer INT NOT NULL;

ALTER TABLE Sale
MODIFY  Count INT NOT NULL;

ALTER TABLE Sale
MODIFY  DateOfSale DATE NOT NULL;

ALTER TABLE Sale
ADD FOREIGN KEY idProduct REFERENCES Product(idProduct);

–Попробуйте изменить тип какого-либо поля

ALTER TABLE Product
MODIFY ProductName varchar(128) NOT NULL;

–Добавьте в таблицу Sale (Продажа) поле Units (Единицы измерения)

ALTER TABLE Sale
ADD COLUMN unit int not null;

–Удалите какое-либо из ограничений

DELETE FROM Category WHERE idCategory = 2;

–LISA tabel Customer (idCustomer, Name, contact) ja siduda tabeliga Sale

CREATE TABLE Customer (
idCustomer, int primary key AUTO_INCREAMENT,
Name varchar(64),
contact varchar(64)
);

ALTER TABLE Sale
ADD FOREIGN KEY (idCustomer) REFERENCES Customer(idCustomer)