Final_Project.sql

CREATE DATABASE SpaceShip
USE SpaceShip

CREATE TABLE Passenger (
	id INT IDENTITY (1,1) PRIMARY KEY, 
	PassengerId VARCHAR(8) UNIQUE NOT NULL ,
	HomePlanet VARCHAR(10) NOT NULL,
	Age INT NOT NULL,
	VIP BIT NOT NULL,
	GroupNo INT NOT NULL,
	Group_Size INT NOT NULL
)
CREATE TABLE Cabin (
    id INT IDENTITY(1,1) PRIMARY KEY,
    Cabin VARCHAR(10) NOT NULL,
    Deck VARCHAR(1) NOT NULL,
    CabinNumber INT NOT NULL,
    Side VARCHAR(1) NOT NULL,
    CONSTRAINT UQ_Cabin_Cabin UNIQUE (Cabin)
)
CREATE TABLE SpaceShip(
	id INT IDENTITY (1,1) Primary Key,
	PassengerID INT Foreign key references Passenger(id) NOT NULL,
	Transported BIT NOT NULL, 
	CabinID INT Foreign Key references Cabin(id) NOT NULL,
	IsSolo BIT NOT NULL,
	CryoSleep BIT NOT NULL,
	RoomService INT NOT NULL,
	FoodCourt INT NOT NULL, 
	ShoppingMall INT NOT NULL,
	Spa INT NOT NULL,
	VRDeck INT NOT NULL,
	TotalSpending INT NOT NULL
)
ALTER TABLE SpaceShip
ADD CONSTRAINT UQ_SpaceShip_PassengerID UNIQUE (PassengerID)

ALTER TABLE Passenger 
ADD Name VARCHAR (20) NOT NULL

ALTER TABLE SpaceShip
ADD Destination VARCHAR (20) NOT NULL

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME IN ('Passenger','Cabin','SpaceShip')

EXEC sp_help 'SpaceShip'

SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN ('SpaceShip','Passenger','Cabin')

CREATE TABLE Passenger_Stage (
    PassengerId VARCHAR(8),
    HomePlanet VARCHAR(10),
    Age INT,
    VIP BIT,    
	Name VARCHAR(20),
    GroupNo INT,
    Group_Size INT,
)
BULK INSERT Passenger_Stage
FROM 'D:\Passenger.csv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    TABLOCK
)
INSERT INTO Passenger (PassengerId, HomePlanet, Age, VIP, Name, GroupNo, Group_Size)
SELECT PassengerId, HomePlanet, Age, VIP, Name, GroupNo, Group_Size
FROM Passenger_Stage
DROP TABLE IF EXISTS Passenger_stage

CREATE TABLE Cabin_Stage (
	Side VARCHAR(1) NOT NULL,
	CabinNumber INT NOT NULL,
	DECK VARCHAR(1) NOT NULL,
	Cabin VARCHAR (10) NOT NULL
)
BULK INSERT Cabin_Stage 
FROM 'D:\Cabin.csv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    TABLOCK
)
INSERT INTO Cabin (Side, CabinNumber, DECK, Cabin )
SELECT Side, CabinNumber, DECK, Cabin
FROM Cabin_stage
DROP TABLE IF EXISTS Cabin_Stage

CREATE TABLE SpaceShip_Stage (
    Destination   VARCHAR(20),
    RoomService   INT,
    FoodCourt     INT,
    ShoppingMall  INT,
    Spa           INT,
    VRDeck        INT,
    Transported   BIT,
    TotalSpending INT,
    Is_Solo       BIT,
    PassengerId   VARCHAR(8),
    Cabin         VARCHAR(10),
    CryoSleep     BIT
)
BULK INSERT SpaceShip_Stage
FROM 'D:\SpaceShip.csv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '0x0d0a',
    TABLOCK
)
INSERT INTO SpaceShip
(
    PassengerID, Transported, CabinID, IsSolo, CryoSleep,
    RoomService, FoodCourt, ShoppingMall, Spa, VRDeck, TotalSpending,
    Destination
)
SELECT
    p.id,
    s.Transported,
    c.id,
    s.Is_Solo,
    s.CryoSleep,
    s.RoomService,
    s.FoodCourt,
    s.ShoppingMall,
    s.Spa,
    s.VRDeck,
    s.TotalSpending,
    s.Destination
FROM SpaceShip_Stage s
JOIN Passenger p ON s.PassengerId = p.PassengerId
JOIN Cabin c ON s.Cabin = c.Cabin;
Drop Table IF EXISTS SpaceShip_Stage
-- CHECK YOUR SCHEMA 
SELECT DB_NAME()

SELECT 'Passenger', COUNT(*) AS RowsCount FROM Passenger

SELECT 'Cabin', COUNT(*) AS RowsCount FROM Cabin

SELECT 'SpaceShip', COUNT(*) AS RowsCount FROM SpaceShip

SELECT COUNT(*) AS CabinRows,
COUNT(DISTINCT Cabin) AS DistinctCabins
FROM Cabin

SELECT COUNT(*) AS OrphanPassengerFK
FROM SpaceShip s
LEFT JOIN Passenger p ON s.PassengerID = p.id
WHERE p.id IS NULL;

SELECT COUNT(*) AS OrphanCabinFK
FROM SpaceShip s
LEFT JOIN Cabin c ON s.CabinID = c.id
WHERE s.CabinID IS NULL;

SELECT TOP 20 *
FROM SpaceShip
WHERE TotalSpending <> (RoomService + FoodCourt + ShoppingMall + Spa + VRDeck);

SELECT COUNT(*) AS VIP FROM Passenger WHERE VIP = 1
SELECT COUNT(*) AS Adults FROM Passenger WHERE Age >= 18
SELECT COUNT(*) AS Adults FROM Passenger WHERE Age < 18