<jupyter_start><jupyter_text>Table of Contents1&nbsp;&nbsp;Connect the sql2&nbsp;&nbsp;Know your database2.1&nbsp;&nbsp;All the tables in the database2.2&nbsp;&nbsp;Example Table:  columns, datatypes, and first value2.3&nbsp;&nbsp;Example Table:  columns, datatypes, and first value2.4&nbsp;&nbsp;Example Table:  columns, datatypes, and first value3&nbsp;&nbsp;Create pandas dataframes4&nbsp;&nbsp;Pandas dataframes datatypes5&nbsp;&nbsp;Create pandas dataframe of all tables and all columns6&nbsp;&nbsp;Questions6.1&nbsp;&nbsp;qn16.2&nbsp;&nbsp;qn26.3&nbsp;&nbsp;qn36.4&nbsp;&nbsp;qn46.5&nbsp;&nbsp;qn56.6&nbsp;&nbsp;qn66.7&nbsp;&nbsp;qn 76.8&nbsp;&nbsp;qn 86.9&nbsp;&nbsp;qn 96.10&nbsp;&nbsp;qn 106.11&nbsp;&nbsp;qn 116.12&nbsp;&nbsp;qn 126.13&nbsp;&nbsp;qn 136.14&nbsp;&nbsp;qn 146.15&nbsp;&nbsp;qn 15  Connect the sqlFirst create a database caled cd, which has three tables cd.facilites, cd.bookings, and cd.memebers.- Get data from github and put in ~/Datasets/SQL/cd/no_unzip_exercises- Open your pdAdmin and to the browser server link- Go to Servers, choose your postgres version, create new database called cd- In the cd database, right click and restore data from data path `~/Datasets/SQL/cd/no_unzip_exercises`  (I need to show all the files, this is initially hidden)- It gives some error, but ignore it, all is well.- Refresh the databse cd.- Open Query tool for database cd- `select * from cd.facilities limit 5;` hit F5 or click Flash button,  it should give the results.- Also, when there should be column names for given tables such as:   `cd > Tables > bookings > Columns > bookid, facid etc`  - Now we have created a postgres database called `cd`, we can use any tools to   access it and do the manipulations.    To know the user and other credentials of postgres database, run following  commands in pgAdmin Query Tool:  ```sqlselect user  -- gives username eg postgresselect current_database()  -- gives database name e.g dvdrental or cd select  inet_server_port( ); -- port number is usually 5432  ```  NOTE: In pgAdmin, the server displayed is like this:```cd/postgres@PostgreSQL10```Right click properties gives:```database: cdowner: postgres```  We can save these credentials in local machine as a yaml file and access from jupyter notebook.<jupyter_code>import numpy as np
import pandas as pd
import os
import yaml

with open( os.path.expanduser('~') + "/.postgres_conf.yml", 'r') as stream:
    try:
        yaml_dict = yaml.safe_load(stream)
    except yaml.YAMLError as exc:
        print(exc)

pw = yaml_dict['password']
port = yaml_dict['port']

%load_ext sql

%sql postgres://postgres:$pw@localhost:$port/cd<jupyter_output><empty_output><jupyter_text>Know your database  All the tables in the database<jupyter_code>%%sql
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_CATALOG='cd'
and TABLE_NAME  not like 'pg_%'
and TABLE_NAME  not like 'sql_%'<jupyter_output>* postgres://postgres:***@localhost:5432/cd
3 rows affected.<jupyter_text>Example Table:  columns, datatypes, and first value<jupyter_code>%%sql
select column_name, data_type, character_maximum_length
    from INFORMATION_SCHEMA.COLUMNS 
    where table_name = 'facilities';
%%sql
select * from cd.facilities limit 5;<jupyter_output>* postgres://postgres:***@localhost:5432/cd
5 rows affected.<jupyter_text>Example Table:  columns, datatypes, and first value<jupyter_code>%%sql
select column_name, data_type, character_maximum_length
    from INFORMATION_SCHEMA.COLUMNS 
    where table_name = 'bookings';
%%sql
select * from cd.bookings limit 2;<jupyter_output>* postgres://postgres:***@localhost:5432/cd
2 rows affected.<jupyter_text>Example Table:  columns, datatypes, and first value<jupyter_code>%%sql
select column_name, data_type, character_maximum_length
    from INFORMATION_SCHEMA.COLUMNS 
    where table_name = 'members';
%%sql
select * from cd.members limit 5;<jupyter_output>* postgres://postgres:***@localhost:5432/cd
5 rows affected.<jupyter_text>Create pandas dataframes<jupyter_code>facilities = %sql select * from cd.facilities;
facilities = facilities.DataFrame()
facilities.head()
bookings = %sql select * from cd.bookings;
bookings = bookings.DataFrame()
bookings.head()
members = %sql select * from cd.members;
members = members.DataFrame()
members.head()<jupyter_output>* postgres://postgres:***@localhost:5432/cd
31 rows affected.<jupyter_text>Pandas dataframes datatypes<jupyter_code>tables = ['facilities', 'bookings', 'members']
df_tables = [facilities, bookings, members]
def show_first_value_and_dtype(num):
    df_tables_dtypes = [df_tables[i].dtypes.to_frame()
                        for i in range(len(df_tables)) ]
    df_tables_first_value = [df_tables[i].head(1).T
                             for i in range(len(df_tables)) ]

    display(pd.concat([df_tables_first_value[num], df_tables_dtypes[num]],
                      axis=1, sort=True,ignore_index=True)
     .rename(columns={0: 'value', 1: 'dtype'})
     .style.apply(lambda x: ['background: lightblue' 
                             if x['dtype'] == 'object'
                             else ''
                             for _ in x],axis=1)
            .set_caption('Dataframe name: ' + tables[num])
    )
show_first_value_and_dtype(2)
facilities['guestcost'] = pd.to_numeric(facilities.guestcost, errors='coerce')
facilities.initialoutlay = pd.to_numeric(facilities.initialoutlay, errors='coerce')
facilities.membercost = pd.to_numeric(facilities.membercost, errors='coerce')
facilities.monthlymaintenance = pd.to_numeric(facilities.monthlymaintenance, errors='coerce')<jupyter_output><empty_output><jupyter_text>Create pandas dataframe of all tables and all columns<jupyter_code>all_columns = [df.columns.tolist() for df in df_tables]
df_tables_cols = pd.DataFrame(all_columns).T.fillna('')
df_tables_cols.columns = tables
# find repeated column names
repeated_cols = (pd.Series([i for sub in all_columns for i in sub])
                 .value_counts()
                 .loc[lambda x: x>1]
                 .index.values.tolist()
                )


cells = repeated_cols
colors = ['salmon', 'khaki','rosybrown','tomato',
          'olive', 'gray',  'mediumpurple',
          'orchid',  'plum','lavender', 'lightgreen',
          'lightsteelblue', 
          'lightblue','skyblue','orange','orangered'][:len(cells)]
cell_colors = dict(zip(cells,colors))
df_tables_cols.style.apply(lambda x: ["background: %s" % cell_colors[v] 
                          if  v in cell_colors.keys()
                          else "" for v in x], axis = 1)<jupyter_output><empty_output><jupyter_text>QuestionsThese questions start off really basic and then get continually more difficult:1. How can you retrieve all the information from the cd.facilities table?1. You want to print out a list of all of the facilities and their cost to members. - How would you retrieve a list of only facility names and costs?1. How can you produce a list of facilities that charge a fee to members?- How can you produce a list of facilities that charge a fee to members, and that fee is less than 1/50th of the monthly maintenance cost? Return the facid, facility name, member cost, and monthly maintenance of the facilities in question.- How can you produce a list of all facilities with the word 'Tennis' in their name?- How can you retrieve the details of facilities with ID 1 and 5? Try to do it without using the OR operator.- How can you produce a list of members who joined after the start of September 2012? Return the memid, surname, firstname, and joindate of the members in question.- How can you produce an ordered list of the first 10 surnames in the members table? The list must not contain duplicates.- You'd like to get the signup date of your last member. How can you retrieve this information?- Produce a count of the number of facilities that have a cost to guests of 10 or more.Skip this one, no question for 11.- Produce a list of the total number of slots booked per facility in the month of September 2012. Produce an output table consisting of facility id and slots, sorted by the number of slots.- Produce a list of facilities with more than 1000 slots booked. Produce an output table consisting of facility id and total slots, sorted by facility id.- How can you produce a list of the start times for bookings for tennis courts, for the date '2012-09-21'? Return a list of start time and facility name pairings, ordered by the time.- How can you produce a list of the start times for bookings by members named '<NAME>'?  qn1> How can you retrieve all the information from the cd.facilities table?<jupyter_code>%%sql
select * from cd.facilities;
facilities<jupyter_output><empty_output><jupyter_text>qn2> You want to print out a list of all of the facilities and their cost to members. How would you retrieve a list of only facility names and costs?<jupyter_code>%%sql
select name, membercost from cd.facilities;
facilities[['name','membercost']]<jupyter_output><empty_output><jupyter_text>qn3> How can you produce a list of facilities that charge a fee to members?<jupyter_code>%%sql
select * from cd.facilities
where membercost > 0.0;
facilities[facilities.membercost > 0.0]<jupyter_output><empty_output><jupyter_text>qn4> How can you produce a list of facilities that charge a fee to members, and that fee is less than 1/50th of the monthly maintenance cost? Return the facid, facility name, member cost, and monthly maintenance of the facilities in question.<jupyter_code>%%sql
select facid, name, membercost, monthlymaintenance, membercost*50 as membercost_50
from cd.facilities
where membercost > 0.0
  and membercost < (monthlymaintenance/50)
;
(facilities[['facid', 'name', 'membercost', 'monthlymaintenance']]
 .loc[lambda x: (x.membercost > 0) & (x.membercost < x.monthlymaintenance/50)]
)<jupyter_output><empty_output><jupyter_text>qn5> How can you produce a list of all facilities with the word 'Tennis' in their name?<jupyter_code>%%sql
select * from cd.facilities
where name like '%Tennis%';
%%sql
select * from cd.facilities
where lower(name) like '%tennis%';
%%sql
select * from cd.facilities
where name ilike '%tennis%';
facilities[facilities['name'].str.contains('Tennis')]<jupyter_output><empty_output><jupyter_text>qn6> How can you retrieve the details of facilities with ID 1 and 5? Try to do it without using the OR operator.<jupyter_code>%%sql
select * from cd.facilities
where facid in (1,5);
%%sql
select * from cd.facilities
where facid = 1 or facid = 5;
facilities[facilities['facid'].isin([1,5])]<jupyter_output><empty_output><jupyter_text>qn 7> How can you produce a list of members who joined after the start of September 2012? Return the memid, surname, firstname, and joindate of the members in question.<jupyter_code>%%sql
select memid, surname, firstname, joindate  from cd.members
where joindate >=  '2012-09-01'
;
(members[['memid', 'surname', 'firstname', 'joindate']]
 .loc[lambda x: x.joindate > '2012 september']
)<jupyter_output><empty_output><jupyter_text>qn 8> How can you produce an ordered list of the first 10 surnames in the members table? The list must not contain duplicates.<jupyter_code>%%sql
select distinct(surname)
from cd.members
order by surname
limit 10;
members.surname.drop_duplicates().sort_values().head(10)<jupyter_output><empty_output><jupyter_text>qn 9> You'd like to get the signup date of your last member. How can you retrieve this information?<jupyter_code>%%sql
select max(joindate) as last_joindate from cd.members;
%%sql
select joindate from cd.members
order by joindate desc
limit 1
;
members.joindate.max()<jupyter_output><empty_output><jupyter_text>qn 10> Produce a count of the number of facilities that have a cost to guests of 10 or more.<jupyter_code>%%sql
select count(*) from cd.facilities
where guestcost >= 10
;
%%sql -- just look at this
select * from cd.facilities
order by guestcost;
facilities.guestcost[facilities.guestcost>10].count()<jupyter_output><empty_output><jupyter_text>qn 11> Skip this one, no question for 11.  qn 12> Produce a list of the total number of slots booked per facility in the month of September 2012. Produce an output table consisting of facility id and slots, sorted by the number of slots.<jupyter_code>%%sql
select  facid, sum(slots) as total_slots from cd.bookings
where starttime between '2012-09-01' and '2012-09-30'
group by facid
order by sum(slots);
%%sql
select  facid, sum(slots) as count_slots from cd.bookings
where extract(month from starttime) = 9 and extract(year from starttime) = 2012
group by facid
order by sum(slots)
;
%%sql
select  facid, sum(slots) as total_slots from cd.bookings
where starttime >= '2012-09-01' and starttime < '2012-09-30'
group by facid
order by sum(slots);<jupyter_output>* postgres://postgres:***@localhost:5432/cd
9 rows affected.<jupyter_text>qn 13> Produce a list of facilities with more than 1000 slots booked. Produce an output table consisting of facility id and total slots,sorted by facility id.<jupyter_code>%%sql --look at tables
select * from cd.bookings limit 2;
%%sql --look at distinct facility ids.
select distinct(facid) from cd.bookings;
%%sql
select facid, sum(slots) as total_slots
from cd.bookings
group by facid
having sum(slots) > 1000
order by facid;
(bookings[['facid','slots']]
 .groupby(['facid'])['slots']
 .sum()
 .loc[lambda x: x > 1000]
 .sort_index()
 .reset_index()
)<jupyter_output><empty_output><jupyter_text>qn 14> How can you produce a list of the start times for bookings for tennis courts,for the date '2012-09-21'?Return a list of start time and facility name pairings, ordered by the time.<jupyter_code>%%sql --look at table
select * from cd.bookings limit 2;
%%sql --look at table
select * from cd.facilities limit 2;
%%sql
select b.facid, f.name, b.starttime
from cd.bookings as b
inner join cd.facilities as f
on b.facid = f.facid
where to_char(b.starttime, 'YYYY-MM-DD')  = '2012-09-21'
order by b.starttime -- 69 rows given
limit 5;
%%sql
select b.facid, f.name, b.starttime
from cd.bookings as b
inner join cd.facilities as f
on b.facid = f.facid
where extract( year from b.starttime) = 2012 
    and extract( month from b.starttime) = 9
    and extract( day from b.starttime) = 21
order by b.starttime --69 rows are given
limit 5;
%%sql
select b.facid, f.name, b.starttime
from cd.bookings as b
inner join cd.facilities as f
on b.facid = f.facid
where  b.starttime between '2012-09-21' and '2012-09-22'
order by b.starttime -- 69 rows given
limit 5;
%%sql
select b.starttime as start, f.name as name 
from cd.facilities f
inner join cd.bookings b 
on f.facid = b.facid 
where f.facid in (0,1) 
  and b.starttime >= '2012-09-21' 
  and b.starttime < '2012-09-22'

order by b.starttime;
df_tables_cols
(pd.merge(bookings[['facid','starttime']]
            .loc[lambda x: x.starttime.between("2012-09-21", "2012-09-22")],
          facilities[['facid','name']], on='facid', how='inner')
.sort_values('starttime')
.head()
)
(pd.merge(bookings[['facid','starttime']]
            .query( """  '2012-09-21' < starttime <  '2012-09-22' """),
          facilities[['facid','name']], on='facid', how='inner')
.sort_values('starttime')
.head()
)<jupyter_output><empty_output><jupyter_text>qn 15> How can you produce a list of the start times for bookings by members named '<NAME>'?<jupyter_code>%%sql
select * from cd.bookings limit 2;
%%sql
select * from cd.members limit 2;
%%sql
select b.memid, b.starttime, m.firstname, m.surname, m.firstname || ' ' || m.surname as full_name
from cd.members as m
inner join cd.bookings as b
    on m.memid = b.memid
    where m.firstname = 'David' and m.surname = 'Farrell'
limit 5;
%%sql
select b.starttime 
from cd.bookings b 
inner join cd.members m
on m.memid = b.memid 
where m.firstname='David' and m.surname='Farrell'
limit 5;
# the above query joins two tables then filter out for person
# we can use subquery to first filter out person and then join with another table.
%%sql
select b.memid, b.starttime, mm.firstname, mm.surname, mm.firstname || ' ' || mm.surname as full_name
from cd.bookings as b
inner join ( select * from cd.members m where m.firstname = 'David' and m.surname = 'Farrell') as mm
    on b.memid = mm.memid
    
limit 5;<jupyter_output>* postgres://postgres:***@localhost:5432/cd
5 rows affected.
{'fraction_non_alphanumeric': 0.10573498710548938, 'fraction_numerical': 0.02437676531990667, 'mean_word_length': 4.672936259143156, 'pattern_counts': {'":': 0, '<': 90, '<?xml version=': 0, '>': 116, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '22', 'dir': 'jupyter-structured-clean-dedup', 'id': '75360', 'license': 'Apache-2.0', 'n_tokens_mistral': 5509, 'n_tokens_neox': 5137, 'n_words': 1809, 'path': 'Section7_Assessment2/section7_assessment2.ipynb', 'repo_name': 'bhishanpdl/Complete_SQL_bootcamp_Udemy', 'size': '16300'}