sql Djay

gistfile1.txt

import sqlite3
import pandas as pd

conn = sqlite3.connect("ladder.db")
# pd.read_sql("SELECT * FROM mytable;", conn)

# pd.read_sql("SELECT * FROM pets;", conn)
# pd.read_sql("SELECT * FROM employees;", conn)
# pd.read_sql("SELECT * FROM transactions;", conn)
pd.read_sql("SELECT * FROM yum;", conn)


# 1) Query all the data in the `pets` table.
pd.read_sql("SELECT * FROM pets;", conn)
# 2) Query only the first 5 rows of the `pets` table.
pd.read_sql("SELECT * FROM pets LIMIT(5);", conn)
# 3) Query only the names and ages of the pets in the `pets` table.
pd.read_sql("SELECT name, age FROM pets;", conn)
# 4) Query the pets in the `pets` table, sorted youngest to oldest.
pd.read_sql("SELECT * FROM pets ORDER BY age;", conn)
# 5) Query the pets in the `pets` table alphabetically.
pd.read_sql("SELECT * FROM pets ORDER BY name;", conn)
# 6) Query all the male pets in the `pets` table.
pd.read_sql("SELECT * FROM pets WHERE sex = 'M'", conn)
# 7) Query all the cats in the `pets` table.
pd.read_sql("SELECT * FROM pets WHERE species = 'cat'", conn)
# 8) Query all the pets in the `pets` table that are at least 5 years old.
pd.read_sql("SELECT * FROM pets WHERE age >= 5", conn)
# 9) Query all the male dogs in the `pets` table. Do not include the sex or species column, since you already know them.
pd.read_sql("SELECT name, age FROM pets WHERE sex = 'M' AND species = 'dog'", conn)
# 10) Get all the names of the dogs in the `pets` table that are younger than 5 years old.
pd.read_sql("SELECT name FROM pets WHERE age < 5 AND species = 'dog'", conn)
# 11) Query all the pets in the `pets` table that are either male dogs or female cats.
pd.read_sql("SELECT * FROM pets WHERE (sex = 'M' AND species = 'dog') OR (sex = 'F' AND species = 'cat')", conn)
# 12) Query the five oldest pets in the `pets` table.
pd.read_sql("SELECT * FROM pets ORDER BY age DESC LIMIT(5)", conn)
# 13) Get the names and ages of all the female cats in the `pets` table sorted by age, descending.
pd.read_sql("SELECT name, age FROM pets WHERE species = 'cat' ORDER BY age DESC", conn)
# 14) Get all pets from `pets` whose names start with P.
pd.read_sql("SELECT * FROM pets WHERE name LIKE 'P%'", conn)
# 15) Select all employees from `employees_null` where the salary is missing.
pd.read_sql("SELECT * FROM employees_null WHERE salary IS NULL", conn)
# 16) Select all employees from `employees_null` where the salary is below $35,000 or missing.
pd.read_sql("SELECT * FROM employees_null WHERE salary < 35000 OR salary IS NULL", conn)
# 17) Select all employees from `employees_null` where the job title is missing. What do you see?
pd.read_sql("SELECT * FROM employees_null WHERE job IS NULL", conn)
# 18) Who is the newest employee in `employees`? The most senior?
pd.read_sql("SELECT * FROM employees ORDER BY startdate LIMIT(1)", conn)
pd.read_sql("SELECT * FROM employees ORDER BY startdate DESC LIMIT(1)", conn)
# 19) Select all employees from `employees` named Thomas.
pd.read_sql("SELECT * FROM employees WHERE firstname = 'Thomas'", conn)
# 20) Select all employees from `employees` named Thomas or Shannon.
pd.read_sql("SELECT * FROM employees WHERE (firstname = 'Thomas') OR (firstname = 'Shannon')", conn)
# 21) Select all employees from `employees` named Robert, Lisa, or any name that begins with a J. 
# In addition, only show employees who are _not_ in sales. 
# This will be a little bit of a longer query.
#     * _Hint:_ There will only be 6 rows in the result.
pd.read_sql("SELECT * FROM employees WHERE (job != 'Sales') AND ((firstname = 'Robert') OR (firstname = 'Lisa') OR (firstname LIKE 'J%'))", conn)





# 22) Query the top 5 rows of the `employees` table to get a glimpse of these new data.
pd.read_sql("SELECT * FROM employees LIMIT(5)", conn)
# 23) Query the `employees` table, but convert their salaries to Euros. 
#     * _Hint:_ 1 Euro = 1.1 USD.
#     * _Hint2:_ If you think the output is ugly, try out the `ROUND()` function.
pd.read_sql("SELECT *, ROUND(salary * 1.1, 2) FROM employees", conn)
# 24) Repeat the previous problem, but rename the column `salary_eu`.
pd.read_sql("SELECT *, ROUND(salary * 1.1, 2) AS salary_eu FROM employees", conn)
# 25) Query the `employees` table, but combine the `firstname` and `lastname` columns to be "Firstname, Lastname" format. Call this column `fullname`. 
# For example, the first row should contain `Thompson, Christine` as `fullname`. 
# Also, display the rounded `salary_eu` instead of `salary`.
#     * _Hint:_ The string concatenation operator is `||`
pd.read_sql("SELECT *, firstname || ', ' || lastname AS fullname, ROUND(salary * 1.1, 2) AS salary_eu FROM employees", conn)
# 26) Query the `employees` table, but replace `startdate` with `startyear` using the `SUBSTR()` function. 
# Also include `fullname` and `salary_eu`.

pd.read_sql("SELECT *, SUBSTR(startdate, 1, 4) AS startyear, firstname || ', ' || lastname AS fullname, ROUND(salary * 1.1, 2) AS salary_eu FROM employees", conn)
# 27) Repeat the above problem, but instead of using `SUBSTR()`, use `STRFTIME()`.
pd.read_sql("SELECT *, STRFTIME('%Y', startdate) AS startyear, firstname || ', ' || lastname AS fullname, ROUND(salary * 1.1, 2) AS salary_eu FROM employees", conn)

# 28) Query the `employees` table, replacing `firstname`/`lastname` with `fullname` and `startdate` with `startyear`. 

# Print out the salary in USD again, except format it with a dollar sign, comma separators, and no decimal. 
# For example, the first row should read `$123,696`. This column should still be named `salary`.
#     * _Hint:_ Check out SQLite's `printf` function.
#     * _Hint2:_ The format string you'll need is `$%,.2d`. You should read more about such formatting strings as they're 
# useful in Python, too!