TSQL - Cardinalités

lena-my

Jul 11th, 2024 (edited)

Never

Add comment

Not a member of Pastebin yet? Sign Up, it unlocks many cool features!

SQL 4.21 KB | None | 0 0

raw download clone embed print report

CREATE DATABASE stock;
-- create table product
CREATE TABLE product (
id INT PRIMARY KEY IDENTITY(1,1),
name VARCHAR(80) NOT NULL,
price DECIMAL(10, 2)
)
-- create table category
CREATE TABLE category (
id INT PRIMARY KEY IDENTITY(1,1),
name VARCHAR(80) NOT NULL,
)
-- create transition table to many to many relation
CREATE TABLE productCategory (
idProduct INT,
CONSTRAINT fkProduct
FOREIGN KEY (idProduct)
REFERENCES product(id),
idCategory INT,
CONSTRAINT fkCategory
FOREIGN KEY (idCategory)
REFERENCES category(id)
)
-- create table purchase
CREATE TABLE purchase (
id INT PRIMARY KEY IDENTITY(1,1),
DATE DATE,
fkProduct INT,
CONSTRAINT fkOrderProduct
FOREIGN KEY (fkProduct)
REFERENCES product(id),
)
-- create table stock
CREATE TABLE stock (
id INT PRIMARY KEY IDENTITY(1,1),
quantity INT,
fkProduct INT,
CONSTRAINT fkStockProduct
FOREIGN KEY (fkProduct)
REFERENCES product(id)
)
-- filling the table category
INSERT INTO category (name)
VALUES ('hygiene');
INSERT INTO category (name)
VALUES ('beaute');
INSERT INTO category (name)
VALUES ('jouets');
INSERT INTO category (name)
VALUES ('loisir');
-- filling table product
INSERT INTO product (name, price)
VALUES ('dentrifice', 6.79);
INSERT INTO product (name, price)
VALUES ('vernis a ongles', 28.67);
INSERT INTO product (name, price)
VALUES ('papier toilette', 10.56);
INSERT INTO product (name, price)
VALUES ('savon de marseille', 2.58);
INSERT INTO product (name, price)
VALUES ('lego classic', 50.99);
INSERT INTO product (name, price)
VALUES ('poupon cry babies', 48.45);
INSERT INTO product (name, price)
VALUES ('simon', 47.33);
INSERT INTO product (name, price)
VALUES ('lego friends', 35.98);
INSERT INTO product (name, price)
VALUES ('velo', 200.45);
-- filling table productCategory
INSERT INTO productCategory (idProduct, idCategory)
VALUES (1, 1);
INSERT INTO productCategory (idProduct, idCategory)
VALUES (2, 1);
INSERT INTO productCategory (idProduct, idCategory)
VALUES (2, 2);
INSERT INTO productCategory (idProduct, idCategory)
VALUES (3, 1);
INSERT INTO productCategory (idProduct, idCategory)
VALUES (4, 1);
INSERT INTO productCategory (idProduct, idCategory)
VALUES (4, 2);
INSERT INTO productCategory (idProduct, idCategory)
VALUES (5, 3);
INSERT INTO productCategory (idProduct, idCategory)
VALUES (5, 4);
INSERT INTO productCategory (idProduct, idCategory)
VALUES (6, 3);
INSERT INTO productCategory (idProduct, idCategory)
VALUES (6, 4);
INSERT INTO productCategory (idProduct, idCategory)
VALUES (7, 3);
INSERT INTO productCategory (idProduct, idCategory)
VALUES (7, 4);
INSERT INTO productCategory (idProduct, idCategory)
VALUES (8, 3);
INSERT INTO productCategory (idProduct, idCategory)
VALUES (8, 4);
INSERT INTO productCategory (idProduct, idCategory)
VALUES (9, 3);
INSERT INTO productCategory (idProduct, idCategory)
VALUES (9, 4);
-- filling table purchase
INSERT INTO purchase (DATE, fkProduct)
VALUES ('2000-12-12', '2');
INSERT INTO purchase (DATE, fkProduct)
VALUES ('2024-01-01', '1');
INSERT INTO purchase (DATE, fkProduct)
VALUES ('2022-03-12', '3');
INSERT INTO purchase (DATE, fkProduct)
VALUES ('2024-01-02', '1');
INSERT INTO purchase (DATE, fkProduct)
VALUES ('2023-12-12', '5');
INSERT INTO purchase (DATE, fkProduct)
VALUES ('2024-09-05', '6');
INSERT INTO purchase (DATE, fkProduct)
VALUES ('2024-06-12', '6');
INSERT INTO purchase (DATE, fkProduct)
VALUES ('2023-11-05', '7');
INSERT INTO purchase (DATE, fkProduct)
VALUES ('2023-07-12', '6');
-- fill table stock
INSERT INTO stock (quantity, fkProduct)
VALUES ('20', '6');
INSERT INTO stock (quantity, fkProduct)
VALUES ('12', '3');
INSERT INTO stock (quantity, fkProduct)
VALUES ('89', '1');
INSERT INTO stock (quantity, fkProduct)
VALUES ('11', '2');
INSERT INTO stock (quantity, fkProduct)
VALUES ('28', '4');

Add Comment

Please, Sign In to add comment