Question

Develop a C++/Python program using visual studio connected to mysql workbench to show all vendor's name and phone (vendor_name and vendor_phone) in the state "CA" and the city "Los Angeles".

here is the database tables

CREATE TABLE general_ledger_accounts
(
account_number INT PRIMARY KEY,
account_description VARCHAR(50) UNIQUE
);

CREATE TABLE terms
(
terms_id INT PRIMARY KEY AUTO_INCREMENT,
terms_description VARCHAR(50) NOT NULL,
terms_due_days INT NOT NULL
);

CREATE TABLE vendors
(
vendor_id INT PRIMARY KEY AUTO_INCREMENT,
vendor_name VARCHAR(50) NOT NULL UNIQUE,
vendor_address1 VARCHAR(50),
vendor_address2 VARCHAR(50),
vendor_city VARCHAR(50) NOT NULL,
vendor_state CHAR(2) NOT NULL,
vendor_zip_code VARCHAR(20) NOT NULL,
vendor_phone VARCHAR(50),
vendor_contact_last_name VARCHAR(50),
vendor_contact_first_name VARCHAR(50),
default_terms_id INT NOT NULL,
default_account_number INT NOT NULL,
CONSTRAINT vendors_fk_terms
FOREIGN KEY (default_terms_id)
REFERENCES terms (terms_id),
CONSTRAINT vendors_fk_accounts
FOREIGN KEY (default_account_number)
REFERENCES general_ledger_accounts (account_number)
);

CREATE TABLE invoices
(
invoice_id INT PRIMARY KEY AUTO_INCREMENT,
vendor_id INT NOT NULL,
invoice_number VARCHAR(50) NOT NULL,
invoice_date DATE NOT NULL,
invoice_total DECIMAL(9,2) NOT NULL,
payment_total DECIMAL(9,2) NOT NULL DEFAULT 0,
credit_total DECIMAL(9,2) NOT NULL DEFAULT 0,
terms_id INT NOT NULL,
invoice_due_date DATE NOT NULL,
payment_date DATE,
CONSTRAINT invoices_fk_vendors
FOREIGN KEY (vendor_id)
REFERENCES vendors (vendor_id),
CONSTRAINT invoices_fk_terms
FOREIGN KEY (terms_id)
REFERENCES terms (terms_id)
);

CREATE TABLE invoice_line_items
(
invoice_id INT NOT NULL,
invoice_sequence INT NOT NULL,
account_number INT NOT NULL,
line_item_amount DECIMAL(9,2) NOT NULL,
line_item_description VARCHAR(100) NOT NULL,
CONSTRAINT line_items_pk
PRIMARY KEY (invoice_id, invoice_sequence),
CONSTRAINT line_items_fk_invoices
FOREIGN KEY (invoice_id)
REFERENCES invoices (invoice_id),
CONSTRAINT line_items_fk_acounts
FOREIGN KEY (account_number)
REFERENCES general_ledger_accounts (account_number)
);

-- create the indexes
CREATE INDEX invoices_invoice_date_ix
ON invoices (invoice_date DESC);

-- create some test tables that aren't explicitly
-- related to the previous five tables
CREATE TABLE vendor_contacts
(
vendor_id INT PRIMARY KEY,
last_name VARCHAR(50) NOT NULL,
first_name VARCHAR(50) NOT NULL
);

CREATE TABLE invoice_archive
(
invoice_id INT NOT NULL,
vendor_id INT NOT NULL,
invoice_number VARCHAR(50) NOT NULL,
invoice_date DATE NOT NULL,
invoice_total DECIMAL(9,2) NOT NULL,
payment_total DECIMAL(9,2) NOT NULL,
credit_total DECIMAL(9,2) NOT NULL,
terms_id INT NOT NULL,
invoice_due_date DATE NOT NULL,
payment_date DATE
);

Answer 1

Hii, I'm unaware of the password, username, database name, and of course host name so, I'm nor able to show you the output but the below program is quite correct and you can run in your machine. Just replace the database name, username, password, and host name with yours and that's pretty much it, you are good to go. Enjoy!

Source Code in Python, refer to screenshot for indentation

import mysql.connector

db = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)

cursor = db.cursor()

#creating tables for this program
cursor.execute("CREATE TABLE general_ledger_accounts(account_number INT PRIMARY KEY,account_description VARCHAR(50) UNIQUE)");

cursor.execute("CREATE TABLE terms(terms_id INT PRIMARY KEY AUTO_INCREMENT,terms_description VARCHAR(50) NOT NULL,terms_due_days INT NOT NULL)");

cursor.execute("CREATE TABLE vendors(vendor_id INT PRIMARY KEY AUTO_INCREMENT,vendor_name VARCHAR(50) NOT NULL UNIQUE,vendor_address1 VARCHAR(50),vendor_address2 VARCHAR(50),vendor_city VARCHAR(50) NOT NULL,vendor_state CHAR(2) NOT NULL,vendor_zip_code VARCHAR(20) NOT NULL,vendor_phone VARCHAR(50),vendor_contact_last_name VARCHAR(50),vendor_contact_first_name VARCHAR(50),default_terms_id INT NOT NULL,default_account_number INT NOT NULL,CONSTRAINT vendors_fk_termsFOREIGN KEY (default_terms_id) REFERENCES terms (terms_id),CONSTRAINT vendors_fk_accounts FOREIGN KEY (default_account_number) REFERENCES general_ledger_accounts (account_number))");

cursor.execute("CREATE TABLE invoices(invoice_id INT PRIMARY KEY AUTO_INCREMENT,vendor_id INT NOT NULL,invoice_number VARCHAR(50) NOT NULL,invoice_date DATE NOT NULL,invoice_total DECIMAL(9,2) NOT NULL,payment_total DECIMAL(9,2) NOT NULL DEFAULT 0,credit_total DECIMAL(9,2) NOT NULL DEFAULT 0,terms_id INT NOT NULL,invoice_due_date DATE NOT NULL,payment_date DATE,CONSTRAINT invoices_fk_vendors FOREIGN KEY (vendor_id) REFERENCES vendors (vendor_id),CONSTRAINT invoices_fk_termsF OREIGN KEY (terms_id) REFERENCES terms (terms_id))");

cursor.execute("CREATE TABLE invoice_line_items(invoice_id INT NOT NULL,invoice_sequence INT NOT NULL,account_number INT NOT NULL,line_item_amount DECIMAL(9,2) NOT NULL,line_item_description VARCHAR(100) NOT NULL,CONSTRAINT line_items_pk PRIMARY KEY (invoice_id, invoice_sequence),CONSTRAINT line_items_fk_invoices FOREIGN KEY (invoice_id) REFERENCES invoices (invoice_id),CONSTRAINT line_items_fk_acounts FOREIGN KEY (account_number) REFERENCES general_ledger_accounts (account_number))");

# creating indexes for the table
cursor.execute("CREATE INDEX invoices_invoice_date_ix ON invoices (invoice_date DESC)");

# creating table that arn't related to the above tables
cursor.execute("CREATE TABLE vendor_contacts(vendor_id INT PRIMARY KEY,last_name VARCHAR(50) NOT NULL,first_name VARCHAR(50) NOT NULL)")

cursor.execute("CREATE TABLE invoice_archive(invoice_id INT NOT NULL,vendor_id INT NOT NULL,invoice_number VARCHAR(50) NOT NULL,invoice_date DATE NOT NULL,invoice_total DECIMAL(9,2) NOT NULL,payment_total DECIMAL(9,2) NOT NULL,credit_total DECIMAL(9,2) NOT NULL,terms_id INT NOT NULL,invoice_due_date DATE NOT NULL,payment_date DATE)");

# quering out desired output through SELECT and WHERE clause
cursor.execute("SELECT vendor_name, vendor_phone from vendors WHERE vendor_state='CA' and vendor_city='Los Angeles'")

result = cursor.fetchall()

for row in result:
print(row)
  
  
Source Code indentation