Deo SQL - koda iz prvog videa
U nastavku je ono što je urađeno na času, 22.01. Za ostatak ćete morati sami da pogledate prvi video do kraja i da završite. Ovo nam je potrebno da bismo kreirali bazu i sve potrebne tabele za upite koje ćemo koristiti.
CREATE DATABASE car_transactions;
use car_transactions;
CREATE TABLE Categories
(
CatId INT IDENTITY(1,1) PRIMARY KEY,
Category VARCHAR(50)
);
CREATE TABLE Countries
(
CountryId INT IDENTITY(1,1) PRIMARY KEY,
Country VARCHAR(55)
);
CREATE TABLE Cities
(
CityId INT IDENTITY(1,1) PRIMARY KEY,
CountryId INT FOREIGN KEY REFERENCES Countries(CountryId),
City VARCHAR(33)
);
CREATE TABLE Customers
(
CustId INT IDENTITY(1,1) PRIMARY KEY,
CityId INT FOREIGN KEY REFERENCES Cities(CityId),
Surname VARCHAR(15),
Name VARCHAR(15)
);
CREATE TABLE Salesman
(
SalesmanId INT IDENTITY(1,1) PRIMARY KEY,
Surname VARCHAR(15),
Name VARCHAR(15),
EmpDate DATETIME,
BossId INT
);
CREATE TABLE Company
(
CompanyId INT IDENTITY(1,1) PRIMARY KEY,
CityId INT FOREIGN KEY REFERENCES Cities(CityId),
Company VARCHAR(33)
);
CREATE TABLE Cars
(
CarId INT IDENTITY(1,1) PRIMARY KEY,
CategoryId INT FOREIGN KEY REFERENCES Categories(CatId),
CompanyId INT FOREIGN KEY REFERENCES Company(CompanyId),
Car VARCHAR(50),
Model VARCHAR(50)
);
CREATE TABLE Transactions_facts_table
(
CarId INT FOREIGN KEY REFERENCES Cars(CarId),
CustomerId INT FOREIGN KEY REFERENCES Customers(CustId),
SalesmanId INT FOREIGN KEY REFERENCES Salesman(SalesmanId),
SalesDate DATETIME,
Price MONEY,
Amount INT,
Value MONEY
);
INSERT INTO Categories (Category) Values ('Hatchback');
INSERT INTO Categories (Category) Values ('Sedan');
INSERT INTO Categories (Category) Values ('Coupe');
INSERT INTO Categories (Category) Values ('Convertible');
INSERT INTO Categories (Category) Values ('Wagon');
INSERT INTO Categories (Category) Values ('SUV');
INSERT INTO Countries (Country) Values ('Germany');
INSERT INTO Countries (Country) Values ('USA');
INSERT INTO Countries (Country) Values ('Japan');
INSERT INTO Countries (Country) Values ('UK');
INSERT INTO Countries (Country) Values ('South Korea');
INSERT INTO Countries (Country) Values ('Italy');
INSERT INTO Cities (CountryId, City) Values (1, 'Stutgart');
INSERT INTO Cities (CountryId, City) Values (1, 'Munich');
INSERT INTO Cities (CountryId, City) Values (2, 'Dearborn');
INSERT INTO Cities (CountryId, City) Values (2, 'Warren');
INSERT INTO Cities (CountryId, City) Values (3, 'Toyota');
INSERT INTO Cities (CountryId, City) Values (3, 'Fuchu');
INSERT INTO Cities (CountryId, City) Values (4, 'Gaydon');
INSERT INTO Cities (CountryId, City) Values (4, 'Crewe');
INSERT INTO Cities (CountryId, City) Values (4, 'Whitley');
INSERT INTO Cities (CountryId, City) Values (5, 'Seoul');
INSERT INTO Cities (CountryId, City) Values (6, 'Maranello');
INSERT INTO Cities (CountryId, City) Values (6, 'Modena');
INSERT INTO Company(CityId, Company) Values (1, 'Mercedes-Benz');
INSERT INTO Company(CityId, Company) Values (2, 'Bayerische Motoren Werke AG');
INSERT INTO Company(CityId, Company) Values (3, 'Lincoln');
INSERT INTO Company(CityId, Company) Values (4, 'Cadillac');
INSERT INTO Company(CityId, Company) Values (5, 'Toyota Motor Corporation');
INSERT INTO Company(CityId, Company) Values (6, 'Mazda Motor Corporation');
INSERT INTO Company(CityId, Company) Values (7, 'Aston Martin Lagonda Limited');
INSERT INTO Company(CityId, Company) Values (8, 'Bently Motors Limited');
INSERT INTO Company(CityId, Company) Values (9, 'Jaguar Cars Ltd');
INSERT INTO Company(CityId, Company) Values (10, 'Kia Motors');
INSERT INTO Company(CityId, Company) Values (10, 'Hundai Motor Company');
INSERT INTO Company(CityId, Company) Values (11, 'Ferrari S.P.A');
INSERT INTO Company(CityId, Company) Values (12, 'Maserati');
*************************************************************************************
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (1, 1, 'Merzedes-Benz', 'A-Class');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (1, 1, 'Merzedes-Benz', 'B-Class');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (2, 1, 'Merzedes-Benz', 'C-Class');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (2, 1, 'Merzedes-Benz', 'E-Class');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (3, 1, 'Merzedes-Benz', 'CL-Class');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (3, 1, 'Merzedes-Benz', 'SLS');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (4, 1, 'Merzedes-Benz', 'SL');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (5, 1, 'Merzedes-Benz', 'C-Class Wagon');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (6, 1, 'Merzedes-Benz', 'GLK');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (1, 2, 'BMW', '1 Series');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (2, 2, 'BMW', '3 series');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (2, 2, 'BMW', '5 series');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (3, 2, 'BMW', '3 Series Coupe');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (3, 2, 'BMW', 'M3 Coupe');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (4, 2, 'BMW', '3 Serie Convertible');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (4, 2, 'BMW', 'M3 Convertible');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (5, 2, 'BMW', '3 Series Touring');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (5, 2, 'BMW', '5 Series Touring');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (6, 2, 'BMW', 'X1');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (6, 2, 'BMW', 'X3');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (6, 3, 'Lincoln', 'MKX');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (6, 3, 'Lincoln', 'Navigator');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (2, 4, 'Cadillac', 'CTS');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (2, 4, 'Cadillac', 'STS');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (2, 4, 'Cadillac', 'DTS');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (5, 4, 'Cadillac', 'CTS-V Wagon');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (5, 4, 'Cadillac', 'CTS Sport Wagon');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (6, 4, 'Cadillac', 'Escalade');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (6, 4, 'Cadillac', 'Escalade Hybrid');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (1, 5, 'Toyota', 'Yaris');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (1, 5, 'Toyota', 'Matrix');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (2, 5, 'Toyota', 'Camry');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (2, 5, 'Toyota', 'Avalon');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (6, 5, 'Toyota', 'RAV4');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (6, 5, 'Toyota', '4Runner');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (6, 5, 'Toyota', 'Land Cruiser');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (1, 6, 'Mazda', 'Mazda2');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (1, 6, 'Mazda', 'Mazda3');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (2, 6, 'Mazda', 'Mazda6');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (4, 6, 'Mazda', 'MX-5');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (6, 6, 'Mazda', 'Mazda5');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (6, 6, 'Mazda', 'CX7');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (3, 7, 'Aston Martin', 'DBS Coupe');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (4, 7, 'Aston Martin', 'DBS Volante');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (3, 7, 'Aston Martin', 'Virage Coupe');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (4, 7, 'Aston Martin', 'Virage Volante');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (3, 7, 'Aston Martin', 'V8 Coupe');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (4, 7, 'Aston Martin', 'V8 Roadster');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (1, 7, 'Aston Martin', 'Cygnet');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (2, 7, 'Aston Martin', 'Rapide');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (2, 8, 'Bentley', 'Muisanne');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (2, 8, 'Bentley', 'Continental Flying Spur');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (3, 8, 'Bentley', 'Continental GT');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (4, 8, 'Bentley', 'Continental GTC');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (3, 9, 'Jaguar', 'XK');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (2, 9, 'Jaguar', 'XJ');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (2, 9, 'Jaguar', 'XF');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (3, 9, 'Jaguar', 'R');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (1, 10, 'Kia', 'Picanto');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (6, 10, 'Kia', 'Soul');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (2, 11, 'Hyundai', 'Azera');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (3, 11, 'Hyundai', 'Genesis Coupe');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (3, 12, 'Ferrari', '599 GTO');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (4, 12, 'Ferrari', 'California');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (2, 13, 'Maserati', 'Quatroporte');
INSERT INTO Cars(CategoryId, CompanyId, Car, Model) Values (3, 13, 'Maserati', 'GranTurismo');
INSERT INTO Salesman(Surname, Name, EmpDate, BossId) Values ('Smith', 'John', '2002-05-04', NULL);
INSERT INTO Salesman(Surname, Name, EmpDate, BossId) Values ('Johnson', 'Mark', '2002-06-01', 1);
INSERT INTO Salesman(Surname, Name, EmpDate, BossId) Values ('Kolarov', 'Steve', '2002-06-10', 1);
INSERT INTO Salesman(Surname, Name, EmpDate, BossId) Values ('Williams', 'Michel', '2003-10-01', 1);
INSERT INTO Salesman(Surname, Name, EmpDate, BossId) Values ('Cronenberg', 'Will', '2004-03-01', 1);
INSERT INTO Customers (CityId, Name, Surname) Values (1, 'Josua', 'Brown');
INSERT INTO Customers (CityId, Name, Surname) Values (2, 'Jan', 'Davis');
INSERT INTO Customers (CityId, Name, Surname) Values (3, 'William', 'Novicki');
INSERT INTO Customers (CityId, Name, Surname) Values (4, 'Emma', 'Miller');
INSERT INTO Customers (CityId, Name, Surname) Values (5, 'Laurence', 'Moore');
INSERT INTO Customers (CityId, Name, Surname) Values (6, 'Ethan', 'Johnson');
INSERT INTO Customers (CityId, Name, Surname) Values (7, 'Isabella', 'Wiliams');
INSERT INTO Customers (CityId, Name, Surname) Values (8, 'Jurgen', 'Taylor');
INSERT INTO Customers (CityId, Name, Surname) Values (9, 'Robert', 'Jackson');
INSERT INTO Customers (CityId, Name, Surname) Values (10, 'Isabella', 'Jacov');
INSERT INTO Customers (CityId, Name, Surname) Values (11, 'Daniel', 'Sanchez');
INSERT INTO Customers (CityId, Name, Surname) Values (2, 'Alexander', 'Ramrez');
INSERT INTO Customers (CityId, Name, Surname) Values (1, 'Jayden', 'Flores');
INSERT INTO Customers (CityId, Name, Surname) Values (2, 'Aiden', 'Garca');
INSERT INTO Customers (CityId, Name, Surname) Values (3, 'Jackson', 'Mason');
INSERT INTO Customers (CityId, Name, Surname) Values (4, 'Liam', 'Clark');
INSERT INTO Customers (CityId, Name, Surname) Values (5, 'Jacob', 'Jones');
INSERT INTO Customers (CityId, Name, Surname) Values (6, 'Jayden', 'White');
INSERT INTO Customers (CityId, Name, Surname) Values (7, 'Ethan', 'Lee');
INSERT INTO Customers (CityId, Name, Surname) Values (8, 'Daniel', 'Campbell');
INSERT INTO Customers (CityId, Name, Surname) Values (9, 'Jayden', 'Anderson');
INSERT INTO Customers (CityId, Name, Surname) Values (10, 'Ethan', 'Chan');
INSERT INTO Customers (CityId, Name, Surname) Values (11, 'Alexander', 'Jones');
INSERT INTO Customers (CityId, Name, Surname) Values (12, 'Daniel', 'Fernandez');
INSERT INTO Customers (CityId, Name, Surname) Values (3, 'Jayden', 'Garca');
INSERT INTO Customers (CityId, Name, Surname) Values (1, 'Daniel', 'Martinez');
INSERT INTO Customers (CityId, Name, Surname) Values (2, 'Ethan', 'Gonzales');
INSERT INTO Customers (CityId, Name, Surname) Values (3, 'Alexander', 'Lopez');
INSERT INTO Customers (CityId, Name, Surname) Values (4, 'Jayden', 'Rodrigez');
INSERT INTO Customers (CityId, Name, Surname) Values (5, 'Alexander', 'Taylor');
INSERT INTO Customers (CityId, Name, Surname) Values (6, 'Ethan', 'Cote');
INSERT INTO Customers (CityId, Name, Surname) Values (7, 'Daniel', 'Williams');
INSERT INTO Customers (CityId, Name, Surname) Values (8, 'Jayden', 'White');
INSERT INTO Customers (CityId, Name, Surname) Values (9, 'Daniel', 'Johnson');
INSERT INTO Customers (CityId, Name, Surname) Values (10, 'Alexander', 'Clark');
INSERT INTO Customers (CityId, Name, Surname) Values (11, 'Daniel', 'Wilson');
INSERT INTO Customers (CityId, Name, Surname) Values (12, 'Ethan', 'Gagnon');
INSERT INTO Customers (CityId, Name, Surname) Values (4, 'Alexander', 'Lee');
INSERT INTO Customers (CityId, Name, Surname) Values (1, 'Ethan', 'Tremblay');
INSERT INTO Customers (CityId, Name, Surname) Values (2, 'Alen', 'Roy');
INSERT INTO Customers (CityId, Name, Surname) Values (3, 'Hayk', 'Brown');
INSERT INTO Customers (CityId, Name, Surname) Values (4, 'Arman', 'Martin');
INSERT INTO Customers (CityId, Name, Surname) Values ( 5, 'Eric', 'Lem');
INSERT INTO Customers (CityId, Name, Surname) Values ( 6, 'Gor', 'Smith');
INSERT INTO Customers (CityId, Name, Surname) Values ( 7, 'David', 'Li');
INSERT INTO Customers (CityId, Name, Surname) Values ( 8, 'Emma', 'Waber');
INSERT INTO Customers (CityId, Name, Surname) Values ( 9, 'Matt', 'Cronowski');
INSERT INTO Customers (CityId, Name, Surname) Values ( 10, 'John', 'Davis');
INSERT INTO Customers (CityId, Name, Surname) Values ( 11, 'Jan', 'Taylor');
INSERT INTO Customers (CityId, Name, Surname) Values ( 12, 'Olivia', 'Taylor');
INSERT INTO Customers (CityId, Name, Surname) Values ( 5, 'Ann', 'Thomas');
INSERT INTO Customers (CityId, Name, Surname) Values ( 1, 'Jan', 'Jacov');
INSERT INTO Customers (CityId, Name, Surname) Values ( 2, 'Robert', 'Taylor');
INSERT INTO Customers (CityId, Name, Surname) Values ( 3, 'Joshua', 'Brown');
INSERT INTO Customers (CityId, Name, Surname) Values ( 4, 'Jan', 'Thomas');
INSERT INTO Customers (CityId, Name, Surname) Values ( 5, 'Anthony', 'polakov');
INSERT INTO Customers (CityId, Name, Surname) Values ( 6, 'Olivia', 'Willson');
INSERT INTO Customers (CityId, Name, Surname) Values ( 7, 'Emma', 'Williams');
INSERT INTO Customers (CityId, Name, Surname) Values ( 8, 'Jacob', 'Baranowski');
INSERT INTO Customers (CityId, Name, Surname) Values ( 9, 'John', 'Garca');
INSERT INTO Customers (CityId, Name, Surname) Values ( 10, 'Mark', 'Clark');
INSERT INTO Customers (CityId, Name, Surname) Values ( 11, 'Anthony', 'White');
INSERT INTO Customers (CityId, Name, Surname) Values ( 12, 'Olivia', 'Gonzales');
INSERT INTO Customers (CityId, Name, Surname) Values ( 6, 'Jurgen', 'Lopez');
INSERT INTO Customers (CityId, Name, Surname) Values ( 1, 'Isabella', 'Rodriguez');
INSERT INTO Customers (CityId, Name, Surname) Values ( 2, 'William', 'White');
INSERT INTO Customers (CityId, Name, Surname) Values ( 3, 'Daniel', 'Williams');
INSERT INTO Customers (CityId, Name, Surname) Values ( 4, 'Jan', 'Cote');
INSERT INTO Customers (CityId, Name, Surname) Values ( 5, 'Ethan', 'Taylor');