from sqlsetup import connect, run, commit, tbl, close, sel
connect('bank')
sel("SHOW TABLES;")
sel("SELECT fname, lname FROM employee;")
sel("SELECT * FROM department;")
sel("SELECT * FROM employee;")
sel("SELECT emp_id, 'Active', emp_id * .5, UPPER(lname) FROM employee")
sel("SELECT VERSION(), USER(), DATABASE();") # no FROM needed
sel("""SELECT UPPER(lname) AS last_name_upper,
emp_id AS employee_id FROM employee""")
sel("SELECT DISTINCT cust_id FROM account")
run("""CREATE VIEW employee_vw AS
SELECT emp_id, fname, lname, YEAR(start_date) start_year
FROM employee;""")
commit()
sel("""SELECT employee.emp_id, employee.fname,
department.name dept_name
FROM employee INNER JOIN department
ON employee.dept_id = department.dept_id;""")
run("""INSERT INTO person
(person_id, fname, lname, gender, birth_date)
VALUES (8, 'Lee', 'Starr', 'M', '1968-2-3');""")
run("""INSERT INTO person
(person_id, fname, lname, gender, birth_date)
VALUES (null, 'Sharon', 'Walker', 'F', '1973-9-12');""")
# IMPORTANT: COMMIT CHANGES
commit()
run("""SELECT person_id, fname, lname, birth_date
FROM person;""")
tbl()
sel("""SELECT person_id, fname, lname FROM person;""")
sel("""SELECT person_id, fname, lname FROM person
WHERE person_id = 8;""")
run("""INSERT INTO favorite_food (person_id, food)
VALUES(8, 'nachos');""")
commit()
sel("""SELECT * FROM favorite_food;""")
sel("""SELECT food FROM favorite_food
WHERE person_id = 8
ORDER BY food;""")