SQL Quiz 2

USE ap;

-- Question 1

SELECT i.invoice_id,
       ili.invoice_sequence,
       FORMAT(i.payment_total, 2),
       CONVERT(ili.line_item_amount, SIGNED),
       LENGTH(i.invoice_number) as "Invoice Length",
       DATE_FORMAT(i.invoice_date, '%b-%d-%Y')
FROM invoices i
JOIN invoice_line_items ili on i.invoice_id = ili.invoice_id
ORDER BY i.invoice_id;


-- Question 2

USE ex;

CREATE OR REPLACE VIEW employee_manager AS
SELECT CONCAT(e.first_name, ' ', e.last_name) as "Employee",
       CONCAT(m.first_name, ' ', m.last_name) as "Manager"
FROM employees e
JOIN employees m on m.employee_id = e.manager_id;

-- Question 3.1

USE ap;

CREATE TABLE IF NOT EXISTS addresses
(
address_id int Primary key NOT NULL AUTO_INCREMENT,
street_address varchar(100) NOT NULL,
city varchar(45) NOT NULL,
province varchar(2) NOT NULL DEFAULT 'NL'
);

-- Question 3.2

ALTER TABLE vendor_contacts
ADD address_id INT;

-- Question 3.3

ALTER TABLE vendor_contacts
ADD CONSTRAINT vendor_contacts_fk_addresses
FOREIGN KEY (address_id)
    REFERENCES  addresses (address_id);