-- SQL Technical Assessment

-- -------------------------------------------------------------------------------------------------------

-- Assessment # 1

-- We have 3 tables relating to insured personal and the vehicles with active coverage.

--

-- insured: Table provides names of insured and their ids

-- vehicles: Table contains insured id and vehicles owned and their value. Primary is is vehicle_id

-- vehicle_confidential: Contains vin numbers for vehicles. Primary key is vehicle_id

-- ::: Develop a query to display insured name, the most expensive vehicle owned by insured, and that vehicles VIN.

--select * from insured;

--select * from vehicles;

--select * from vehicle_confidential;

-- -------------------------------------------------------------------------------------------------------

-- Assessment # 2

-- We have four tables:

--

-- policy - stores policy information

-- vehicle - stores vehicle information for each policy

-- vehicle_record - store maintenance records for each vehicle

-- claim - records loss date, policyid and vehicleid for each loss

--

-- Question 1: Write a query to display insured name and number of claims for each insured

--

-- Question 2: Write a query to display insured name and veicle model for vehicles that have never had a claim

--

-- Question 3: For each claim, list the insured name, vehicle model, record date and second most recent vehicle

-- mileage prior to the claim loss date

--

--select * from policy;

--select * from vehicle;

--select * from vehicle_record;

---- select * from claim;

-- Python Techncial Assessment

-- ::: Define and call a function to check IP Address validity. Function should return "valid" or "not valid"

-- Valid IP Address requirements:

-- 1. Contains 4 integers seperated by a period (x.x.x.x)

-- 2. 1st Integer can have a range (1-127)

-- 3. 2nd, 3rd, and 4th integers can have a range of (0-255)

-- isvalid('0.200.154.132') # Not Valid

-- isvalid('1.136.255.0') # Valid

-- isvalid('23.35.123.243.123') # Not Valid

-- isvalid('5.1A3.196.145') # Not Valid

-- isvalid('2.103.196.') # Not Valid

-- Hint: Can be accomplished using .split() function, if statements, and try and except statement