1.)
SQL> create table dealerships(
    -> dealer_Id int not null,
    -> Address varchar(100)not null,
    -> city varchar(100) not null,
    -> state varchar(100) not null,
    -> primary key(dealer_Id)
    -> );
Query OK, 0 rows affected (0.87 sec)
SQL> insert into dealerships values('dl001','ad1234','Nai','Nyeri');
Query OK, 1 row affected (0.99 sec)
SQL> insert into dealerships values('dl002','ad4534','Nyahi','Kahawa');
Query OK, 1 row affected (0.40 sec)
SQL> select * from dealerships;
+-----------+---------+-------+--------+
| dealer_Id | Address | city  | state  |
+-----------+---------+-------+--------+
| dl001     | ad1234  | Nai   | Nyeri  |
| dl002     | ad4534  | Nyahi | Kahawa |
+-----------+---------+-------+--------+
2 rows in set (0.00 sec)


----------------------------------------------------------------------------------------------------------------------------------------------------------------
2.)
SQL> create table Vehicles(Vehicle_Code varchar(10) not null,
    -> VIN varchar(10) not null,
    -> OS_ID varchar(10) not null,
    -> Dealer_ID varchar(10) not null,
    -> Vehicle_Status varchar(10) not null,
    -> Model varchar(10) not null,
    -> Make varchar(10) not null,
    -> Where_From varchar(10) not null,
    -> whole_Sale_Cost varchar(10) not null,
    -> primary key(Vehicle_Code)
    -> );
Query OK, 0 rows affected (0.13 sec)

SQL> alter table Vehicles rename OLTP_Vehicles;
Query OK, 0 rows affected (0.05 sec)


SQL> insert into OLTP_Vehicles values('V001','VIN01','OS01','DL001','Running',
'Soul','Kia','Japan','20000');
Query OK, 1 row affected (0.08 sec)

SQL> insert into OLTP_Vehicles values('V002','VIN02','OS02','DL002','Running',
'Sedan','Optma','Japan','120000');
Query OK, 1 row affected (0.08 sec)

SQL> insert into OLTP_Vehicles values('V003','VIN03','OS03','DL003','Running',
'Sedan','Rio','Japan','140000');
Query OK, 1 row affected (0.19 sec)

SQL> insert into OLTP_Vehicles values('V004','VIN04','OS04','DL004','Running',
'Truck','Sierra','Japan','120000');
Query OK, 1 row affected (0.08 sec)


SQL> select * from OLTP_Vehicles ORDER BY Vehicle_Code;
+--------------+-------+-------+-----------+----------------+-------+--------+------------+-----------------+
| Vehicle_Code | VIN   | OS_ID | Dealer_ID | Vehicle_Status | Model | Make   | Where_From | whole_Sale_Cost |
+--------------+-------+-------+-----------+----------------+-------+--------+------------+-----------------+
| V001         | VIN01 | OS01  | DL001     | Running        | Soul  | Kia    | Japan      | 20000           |
| V002         | VIN02 | OS02  | DL002     | Running        | Sedan | Optma  | Japan      | 120000          |
| V003         | VIN03 | OS03  | DL003     | Running        | Sedan | Rio    | Japan      | 140000          |
| V004         | VIN04 | OS04  | DL004     | Running        | Truck | Sierra | Japan      | 120000          |
+--------------+-------+-------+-----------+----------------+-------+--------+------------+-----------------+
4 rows in set (0.00 sec)

-------------------------------------------------------------------------------------------------------------------------------------------------------------
3.)
SQL> create table TIME(
    -> sale_Day date,
    -> date_type varchar(100) not null,
    -> sales_made int not null,
    -> vehicle_code varchar(100) not null,
    -> primary key(sale_Day)
    -> );
Query OK, 0 rows affected (0.11 sec)
SQL> insert into TIME values('3/4/2014','weekday','5','V001');
Query OK, 1 row affected, 1 warning (0.39 sec)
SQL> insert into TIME values('2013/01/01','holiday','3','V003');
Query OK, 1 row affected (0.06 sec)

SQL> insert into TIME values('2014/12/25','holiday','3','V003');
Query OK, 1 row affected (0.06 sec)

SQL> insert into TIME values('2014/07/04','holiday','3','V005');
Query OK, 1 row affected (0.08 sec)

SQL> select date_type,count(*),min(sale_Day),max(sale_Day)from TIME group by d
ate_type order by date_type;
SQL> select date_type,count(*),min(sale_Day),max(sale_Day)from TIME group by d
ate_type order by date_type;
+-----------+----------+---------------+---------------+
| date_type | count(*) | min(sale_Day) | max(sale_Day) |
+-----------+----------+---------------+---------------+
| holiday   |        4 | 2013-01-01    | 2014-12-25    |
| weekday   |        1 | 0000-00-00    | 0000-00-00    |
| weekend   |        2 | 2014-01-01    | 2014-05-03    |
+-----------+----------+---------------+---------------+
3 rows in set (0.00 sec)

--------------------------------------------------------------------------------------------------------------------------------------------------------------
4.)
SQL> Create table Sales_Facts(Sale_ID varchar(10) not null,
    -> Cust_ID varchar(10) not null,
    -> Salesperson_ID varchar(10) not null,
    -> Total_Vehicle_Sold int(10) not null,
    -> Gross_Sales_Amount varchar(10) not null,
    -> Sale_Day date not null,
    -> Vehicle_Code varchar(10) not null,
    -> Plan_Code varchar(10) not null,
    -> Dealer_ID varchar(10) not null,
    -> Primary key(Sale_ID),
	-> Foreign key (Dealer_ID) references dealerships(Dealer_ID),
	-> Foreign key (Sale_Day) references Times(Sale_Day),
	-> Foreign key (Vehicle_Code) references Vehicles(Vehicle_Code),
	-> Foreign key (Plan_Code) references Financing_Plans(Plan_Code),
    -> );
Query OK, 0 rows affected (0.13 sec)

SQL> describe sales_Facts;
+--------------------+-------------+------+-----+---------+-------+
| Field              | Type        | Null | Key | Default | Extra |
+--------------------+-------------+------+-----+---------+-------+
| Sale_ID            | varchar(10) | NO   | PRI | NULL    |       |
| Cust_ID            | varchar(10) | NO   |     | NULL    |       |
| Salesperson_ID     | varchar(10) | NO   |     | NULL    |       |
| Total_Vehicle_Sold | int(10)     | NO   |     | NULL    |       |
| Gross_Sales_Amount | varchar(10) | NO   |     | NULL    |       |
| Sale_Day           | date        | NO   |     | NULL    |       |
| Vehicle_Code       | varchar(10) | NO   |     | NULL    |       |
| Plan_Code          | varchar(10) | NO   |     | NULL    |       |
| Dealer_ID          | varchar(10) | NO   |     | NULL    |       |
+--------------------+-------------+------+-----+---------+-------+
9 rows in set (0.05 sec)

SQL> insert into Sales_Facts values('SL001','Cust001','Slm001',3,'120000','201
4/10/01','V001','Pl001','DL001');
Query OK, 1 row affected (0.08 sec)

SQL> insert into Sales_Facts values('SL002','Cust002','Slm002',3,'120000','201
4/10/02','V002','Pl002','DL002');
Query OK, 1 row affected (0.06 sec)

SQL> insert into Sales_Facts values('SL003','Cust003','Slm003',3,'130000','201
4/10/03','V003','Pl003','DL003');
Query OK, 1 row affected (0.08 sec)

SQL> insert into Sales_Facts values('SL004','Cust004','Slm004',4,'140000','201
4/10/04','V004','Pl004','DL004');
Query OK, 1 row affected (0.06 sec)

SQL> insert into Sales_Facts values('SL005','Cust005','Slm005',5,'150000','201
4/10/05','V005','Pl005','DL005');
Query OK, 1 row affected (0.11 sec)

SQL> insert into Sales_Facts values('SL006','Cust006','Slm006',0,'0','2014/10/
06','V006','Pl006','DL006');
Query OK, 1 row affected (0.08 sec)

SQL> select count(*) from Sales_Facts;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

SQL> select sum(Total_Vehicle_Sold) from Sales_Facts;
+-------------------------+
| sum(Total_Vehicle_Sold) |
+-------------------------+
|                      18 |
+-------------------------+
1 row in set (0.03 sec)