Studiu Individual 1, Sql Server

Studiu Individual 1

CREATE TABLE Classification(
    IdClassification INT PRIMARY KEY NOT NULL,
    NameClassification VARCHAR (10)
    )
    
INSERT INTO Classification VALUES
    (1001, 'SUV'),
    (1002, 'Sedan'),
    (1003, 'Universal'),
    (1004, 'Hatchback'),
    (1005, 'Coupe'),
    (1006, 'Liftback');
    
SELECT IdClassification AS [Codul clasificatiei], NameClassification AS [Numele clasificatiei]
FROM Classification;

CREATE TABLE Engines(
    IdEngines INT PRIMARY KEY NOT NULL,
    ModelEngines VARCHAR (30),
    Power INT,
    Torque VARCHAR (30),
    Transmission VARCHAR (30),
    Year VARCHAR (30)
    )
    
INSERT INTO Engines VALUES
    (2001, '1.4 TSI 125', 125, '200 N⋅m', '6-speed manual', '2016–2018'),
    (2002, '1.4 TSI 150', 150, '250 N⋅m', '6-speed manual', '2016–2018'),
    (2003, '1.5 TSI 130', 130, '220 N⋅m', '6-speed manual', '2019–present'),
    (2004, '1.5 TSI 150', 150, '250 N⋅m', '6-speed manual	', '2018–present'),
    (2005, '2.0 TSI 180 4 Motion', 180, '320 N⋅m', '7-speed DSG', '2016–2018'),
    (2006, '2.0 TSI 190 4 Motion', 190, '320 N⋅m', '7-speed DSG', '2019–2020'),
    (2007, '2.0 TSI 220 4 Motion', 220, '350 N⋅m', '7-speed DSG', '2016–2018'),
    (2008, '2.0 TSI 230 4 Motion', 230, '350 N⋅m', '7-speed DSG', '2019–2020'),
    (2009, '2.0 TSI 320 4 Motion', 320, '420 N⋅m', '7-speed DSG', '2020–present');
    
SELECT IdEngines AS [Codul motorului], ModelEngines AS [Modelul motorului], Power AS [Puterea motorului], 
       Torque AS [Cuplu], Transmission AS [Cutia de viteze], Year AS [Anul]
FROM Engines;

CREATE TABLE Wheels(
    IdWheels INT PRIMARY KEY NOT NULL,
    ModelWheels VARCHAR (30),
    Size INT,
    Color VARCHAR (30)
    )
    
INSERT INTO Wheels VALUES
    (3001, '5-spoke Two-Toned Machined', 19, 'Black'),
    (3002, 'Tornado', 19, 'Silver'),
    (3003, 'Trenton', 20, 'Galvano Grey'),
    (3004, 'Angled Split 5 Spoke', 20, 'Siver'),
    (3005, 'Two Tone Machined', 21, 'Siver'),
    (3006, 'Prisma Winter', 18, 'Diamond Silver'),
    (3007, 'Blade', 17, 'Black');
    
SELECT IdWheels AS [Codul jantelor], ModelWheels AS [Modelul jantelor], Size AS [Marimea jantelor], Color AS [Culoarea]
FROM Wheels;

CREATE TABLE Model(
    IdModel INT PRIMARY KEY NOT NULL,
    NameModel VARCHAR (10),
    NumberDoors INT,
    Engine INT NOT NULL FOREIGN KEY REFERENCES Engines (IdEngines),
    Wheels INT NOT NULL FOREIGN KEY REFERENCES Wheels (IdWheels),
    IdClassification INT NOT NULL FOREIGN KEY REFERENCES Classification (IdClassification)
    )
    
INSERT INTO Model VALUES
    (4001, 'Alltrack', 4, 2005, 3004, 1003),
    (4002, 'Arteon', 4, 2002, 3006, 1006),
    (4003, 'Atlas', 4, 2008, 3003, 1001),
    (4004, 'Beetle', 2, 2004, 3007, 1005),
    (4005, 'Golf', 2, 2003, 3002, 1004),
    (4006, 'Jetta', 4, 2007, 3004, 1002),
    (4007, 'Passat', 4, 2008, 3001, 1002),
    (4008, 'Sportwagen', 4, 2002, 3005, 1003),
    (4009, 'Tiguan', 4, 2006, 3003, 1001);
    
    
SELECT IdModel AS [Codul modelului], NameModel AS [Numele modelului], NumberDoors AS [Numarul de usi], 
Engine AS [Motor], Wheels AS [Jante], IdClassification AS [Codul clasificatiei]
FROM Model;

CREATE TABLE Dealership(
    IdDealership INT PRIMARY KEY NOT NULL,
    Country VARCHAR (20)
    )
    
INSERT INTO Dealership VALUES
    (5001, 'America de nord'),
    (5002, 'Mexico'),
    (5003, 'China'),
    (5004, 'Australia'),
    (5005, 'India'),
    (5006, 'Malaysia'),
    (5007, 'Indonezia'),
    (5008, 'Europa');
    
    
SELECT IdDealership AS [Codul dealership - ului], Country AS [Numele tarii]
FROM Dealership;


run \| edit \| history \| help	0