<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Introduction to SQLite &
Selecting Sources from the Sloan Digital Sky Survey
Version 0.1
By AA Miller 2019 Mar 25
As noted earlier, there will be full lectures on databases over the remainder of this week.
This notebook provides a quick introduction to SQLite a lightweight implementation of a Structured Query Language (SQL) database. One of the incredibly nice things about SQLite is the low overhead needed to set up a database (as you will see in a minute). We will take advantage of this low overhead to build a database later in the week.
Step1:  At the most basic level - databases store your bytes, and later return those bytes (or a subset of them) when queried. 
They provide a highly efficient means for filtering your bytes (there are many different strategies that the user can employ). 
The backend for most databases is the Structured Query Language or SQL, which is a standard declarative language. 
There are many different libraries that implement SQL
Step2:  Without diving too much into the weeds (we'll investigate this further later this week), we need to establish a connection to the database. From the connection we create a cursor, which allows us to actually interact with the database.
Step4:  And just like that - we have now created a new database intro.db, with which we can "store bytes" or later "retrieve bytes" once we have added some data to the database. 
Aside - note that unlike many SQL libraries, SQLite does not require a server and creates an actual database file on your hard drive. This improves portability, but also creates some downsides as well.
Now we need to create a table and insert some data. We will interact with the database via the execute() method for the cursor object.
Recall that creating a table requires a specification of the table name, the columns in the table, and the data type for each column. Here's an example where I create a table to store info on my pets
Step8:  Once a table is created, we can use the database to store bytes. If I were to populate my PetInfo table I would do the following
Step12:  Now that we have bytes in the database, we can retrieve those bytes with one (or several) queries. There are 3 basic building blocks to a query
Step13:  In closing this brief introduction to databases, note that good databases follow the 4 ACID properties
Step15:  astroquery enables seemless connections to the SDSS database via the Python shell.
Problem 2a
Select 20 random sources from the PhotoObjAll table and return all columns in the table. 
Hint - while this would normally be accomplished by starting the query select limit 20 ..., SDSS CasJobs uses Microsoft's SQL Server, which adopts select top 20 ... to accomplish an identical result.
Step17:  That's more columns than we will likely ever need. Instead, let's focus on objID, a unique identifier, cModelMag_u, cModelMag_g, cModelMag_r, cModelMag_i, and cModelMag_z, the source magnitude in $u', g', r', i', z'$, respectively.
We will now introduce the concept of joining two tables. 
The most common operation is known as an inner join (which is often referred to as just join). An inner join returns records that have matching sources in both tables in the join. 
Less, but nevertheless still powerful, is the outer join. An outer join returns all records in either table, with NULL values for columns in a table in which the record does not exist.
Specialized versions of the outer join include the left join and right join, whereby all records in either the left or right table, respectively, are returned along with their counterparts. 
Problem 2b
Select objid and $u'g'r'i'z'$ from PhotoObjAll and the corresponding class from specObjAll for 20 random sources. 
There are multiple columns you could use to join the tables, in this case match objid to bestobjid from specObjAll and use an inner join.
Step19:  Problem 2c
Perform an identical query to the one above, but this time use a left outer join (or left join).
How do your results compare to the previous query?
Step21:  Problem 2d
This time use a right outer join (or right join).
How do your results compare to the previous query?
Step23:  Challenge Problem
To close the notebook we will perform a nested query. In brief, the idea is to join the results of one query with a separate query.
Here, we are going to attempt to identify bright AGN that don't have SDSS spectra. To do so we will need the photoObjAll table, the specObjAll table, and the rosat table, which includes all cross matches between SDSS sources and X-ray sources detected by the Rosat satellite.
Create a nested query that selects all Rosat sources that don't have SDSS spectra with cModelFlux_u + cModelFlux_g + cModelFlux_r + cModelFlux_i + cModelFlux_z &gt; 10000 (this flux contraint ensures the source is bright without making any cuts on color) and type = 3, this last constraint means the source is extended in SDSS images. 
Hint - you may run into timeout issues in which case you should run the query on CasJobs.
<ASSISTANT_TASK:>
Python Code:
import matplotlib.pyplot as plt
%matplotlib notebook
Explanation: Introduction to SQLite &
Selecting Sources from the Sloan Digital Sky Survey
Version 0.1
By AA Miller 2019 Mar 25
As noted earlier, there will be full lectures on databases over the remainder of this week.
This notebook provides a quick introduction to SQLite a lightweight implementation of a Structured Query Language (SQL) database. One of the incredibly nice things about SQLite is the low overhead needed to set up a database (as you will see in a minute). We will take advantage of this low overhead to build a database later in the week.
End of explanation
import sqlite3
Explanation: At the most basic level - databases store your bytes, and later return those bytes (or a subset of them) when queried. 
They provide a highly efficient means for filtering your bytes (there are many different strategies that the user can employ). 
The backend for most databases is the Structured Query Language or SQL, which is a standard declarative language. 
There are many different libraries that implement SQL: MySQL, PostgreSQL, Greenplum, Microsoft SQL server, IBM DB2, Oracle Database, etc. 
Problem 1) Basic SQL Operations with SQLite
The most basic implementation is SQLite a self-contained, SQL database engine. We will discuss SQLite further later in the week, but in brief - it is a nice stand alone package that works really well for small problems (such as the example that we are about to encounter).
End of explanation
conn = sqlite3.connect("intro.db")
cur = conn.cursor()
Explanation: Without diving too much into the weeds (we'll investigate this further later this week), we need to establish a connection to the database. From the connection we create a cursor, which allows us to actually interact with the database.
End of explanation
cur.execute( # complete
cur.execute(create table DSFPstudents(
                                    Name text, 
                                    Institution text,
                                    Year tinyint
                                    ))
Explanation: And just like that - we have now created a new database intro.db, with which we can "store bytes" or later "retrieve bytes" once we have added some data to the database. 
Aside - note that unlike many SQL libraries, SQLite does not require a server and creates an actual database file on your hard drive. This improves portability, but also creates some downsides as well.
Now we need to create a table and insert some data. We will interact with the database via the execute() method for the cursor object.
Recall that creating a table requires a specification of the table name, the columns in the table, and the data type for each column. Here's an example where I create a table to store info on my pets:
cur.execute(create table PetInfo(
                                    Name text, 
                                    Species text,
                                    Age tinyint,
                                    FavoriteFood text
                                    ))
Problem 1a
Create a new table in the database called DSFPstudents with columns Name, Institution, and Year, where Year is the year in graduate school.
End of explanation
cur.execute( # complete
cur.execute(insert into DSFPstudents(Name, Institution, Year) 
                            values ("Adam Miller", "Northwestern", 13))
cur.execute(insert into DSFPstudents(Name, Institution, Year) 
                            values ("Lucianne Walkowicz", "Adler", 14))
Explanation: Once a table is created, we can use the database to store bytes. If I were to populate my PetInfo table I would do the following:
cur.execute(insert into PetInfo(Name, Species, Age, FavoriteFood) 
                            values ("Rocky", "Dog", 12, "Bo-Nana"))
cur.execute(insert into PetInfo(Name, Species, Age, FavoriteFood) 
                            values ("100 Emoji-Flames Emoji", "Red Panda", 2, "bamboo leaves"))
Note - column names do not need to be explicitly specified, but for clarity this is always preferred.
Problem 1b
Insert data for yourself, and the two people sitting next to you into the database.
End of explanation
cur.execute( # complete
cur.fetchall()
cur.execute(select Institution from DSFPstudents where year > 2)
cur.fetchall()
Explanation: Now that we have bytes in the database, we can retrieve those bytes with one (or several) queries. There are 3 basic building blocks to a query:
SELECT...
FROM...
WHERE...
Where SELECT specifies the information we want to retrieve from the database, FROM specifies the tables being queried in the database, and WHERE specifies the conditions for the query. 
Problem 1c
Select the institutions for all students in the DSFPstudents table who have been in grad school for more than 2 years.
Hint - to display the results of your query run cur.fetchall().
End of explanation
# you may need to run conda install -c astropy astroquery
from astroquery.sdss import SDSS
Explanation: In closing this brief introduction to databases, note that good databases follow the 4 ACID properties:
Atomicity
Consistency
Isolation
Durability
In closing this brief introduction to databases, note that good databases follow the 4 ACID properties:
Atomicity - all parts of transaction succeed, or rollback state of database
Consistency
Isolation
Durability
In closing this brief introduction to databases, note that good databases follow the 4 ACID properties:
Atomicity - all parts of transaction succeed, or rollback state of database
Consistency - data always meets validation rules
Isolation
Durability
In closing this brief introduction to databases, note that good databases follow the 4 ACID properties:
Atomicity - all parts of transaction succeed, or rollback state of database
Consistency - data always meets validation rules
Isolation - no interference across transactions (even if concurrent)
Durability
In closing this brief introduction to databases, note that good databases follow the 4 ACID properties:
Atomicity - all parts of transaction succeed, or rollback state of database
Consistency - data always meets validation rules
Isolation - no interference across transactions (even if concurrent)
Durability - a committed transaction remains committed (even if there's a power outage, etc)
Problem 2) Complex Queries with SDSS
Above we looked at the most basic operations possible with a database (recall - databases are unnecessary, and possibly cumbersome, with small data sets). A typical database consists of many tables, and these tables may be joined together to unlock complex questions for the data. 
As a reminder on (some of) this functionality, we are now going to go through some problems using the SDSS database. The full SDSS schema explains all of the tables, columns, views and functions for querying the database. We will keep things relatively simple in that regard.
End of explanation
SDSS.query_sql( # complete
SDSS.query_sql(select top 20 * from PhotoObjAll)
Explanation: astroquery enables seemless connections to the SDSS database via the Python shell.
Problem 2a
Select 20 random sources from the PhotoObjAll table and return all columns in the table. 
Hint - while this would normally be accomplished by starting the query select limit 20 ..., SDSS CasJobs uses Microsoft's SQL Server, which adopts select top 20 ... to accomplish an identical result.
End of explanation
SDSS.query_sql( # complete
SDSS.query_sql(select top 20 objid, cModelMag_u, cModelMag_g, cModelMag_r, cModelMag_i, cModelMag_z, 
                         class
                  from photoobjall p 
                      inner join specobjall s on p.objid = s.bestobjid)
Explanation: That's more columns than we will likely ever need. Instead, let's focus on objID, a unique identifier, cModelMag_u, cModelMag_g, cModelMag_r, cModelMag_i, and cModelMag_z, the source magnitude in $u', g', r', i', z'$, respectively.
We will now introduce the concept of joining two tables. 
The most common operation is known as an inner join (which is often referred to as just join). An inner join returns records that have matching sources in both tables in the join. 
Less, but nevertheless still powerful, is the outer join. An outer join returns all records in either table, with NULL values for columns in a table in which the record does not exist.
Specialized versions of the outer join include the left join and right join, whereby all records in either the left or right table, respectively, are returned along with their counterparts. 
Problem 2b
Select objid and $u'g'r'i'z'$ from PhotoObjAll and the corresponding class from specObjAll for 20 random sources. 
There are multiple columns you could use to join the tables, in this case match objid to bestobjid from specObjAll and use an inner join.
End of explanation
SDSS.query_sql(select top 20 objid, cModelMag_u, cModelMag_g, cModelMag_r, cModelMag_i, cModelMag_z, 
                         class
                  from photoobjall p 
                      left outer join specobjall s on p.objid = s.bestobjid)
Explanation: Problem 2c
Perform an identical query to the one above, but this time use a left outer join (or left join).
How do your results compare to the previous query?
End of explanation
SDSS.query_sql(select top 20 objid, cModelMag_u, cModelMag_g, cModelMag_r, cModelMag_i, cModelMag_z, 
                         class
                  from photoobjall p 
                      right outer join specobjall s on s.bestobjid = p.objid
                )
Explanation: Problem 2d
This time use a right outer join (or right join).
How do your results compare to the previous query?
End of explanation
SDSS.query_sql(select rm.*
from 
(select r.objid, r.sourcename, r.ra, r.dec, r.cps, r.hr1, r.hr2, cModelMag_u, cModelMag_g, cModelMag_r, cModelMag_i, cModelMag_z
from photoobjall p join rosat r on p.objid = r.objid
  where (cModelFlux_u + cModelFlux_g + cModelFlux_r + cModelFlux_i + cModelFlux_z > 10000)
    and p.type = 3) as rm
  left join specobjall p on rm.objid = p.bestobjid
  where p.bestobjid is null 
                )
Explanation: Challenge Problem
To close the notebook we will perform a nested query. In brief, the idea is to join the results of one query with a separate query.
Here, we are going to attempt to identify bright AGN that don't have SDSS spectra. To do so we will need the photoObjAll table, the specObjAll table, and the rosat table, which includes all cross matches between SDSS sources and X-ray sources detected by the Rosat satellite.
Create a nested query that selects all Rosat sources that don't have SDSS spectra with cModelFlux_u + cModelFlux_g + cModelFlux_r + cModelFlux_i + cModelFlux_z &gt; 10000 (this flux contraint ensures the source is bright without making any cuts on color) and type = 3, this last constraint means the source is extended in SDSS images. 
Hint - you may run into timeout issues in which case you should run the query on CasJobs.
End of explanation
<END_TASK>