Revision history for UsefulResearchQueries


Revision [57815]

Last edited on 2021-09-03 17:08:45 by walker [add postgres website]
Additions:
PgAdmin>>Please note: interacting with our database through SQL is a privilege for high-performing interns and staff. Its use and practice should not interfere with the accomplishment of your normal responsibilities and duties as previously described in any way.
We use PostgreSQL and documentation is located here: https://www.postgresql.org/
Deletions:
PgAdmin>>Please note: interacting with our database through SQL is a privilege for high performing interns and staff. Its use and practice should not interfere with the accomplishment of your normal responsibilities and duties as previously described in any way.

Revision [56543]

Edited on 2020-01-27 10:18:20 by walker [add postgres website]
Additions:
PvsadminSchema

Revision [54671]

Edited on 2019-04-04 15:27:52 by JamesW [Adding Practice Queries Answer Page]
Additions:
PracticeQueriesAnswers

Revision [21449]

Edited on 2018-05-25 09:08:39 by walker [removed instructions for Base, use pgadmin]
Deletions:
>>When describing how an intern or new staff member can access the database and work with SQL:
~- In the desktop Research folder, identify the .odb (Base) file (/export/research/pvsadmin_staff (copy 1).odb). "Copy to" the desktop. It is important that they don't drag and drop or do any other weird things; that will affect the original file. You just want a copy. This is due to the various permissions the IT department has set up.
~- The username is "staff" and the password is "bananastand" and it can be kind of difficult to type when you can't see the letters.
~- Sometimes, you or they might get an error message about having the wrong "host" or something like that. In this situation, use the "Edit" drop down to find "Database" and then "Properties." Once the properties window has popped up, rename the host to "host=chickostick.votesmart.org dbname=pvsadmin". You can test the connection with the bananastand password again. Once it works, make sure to save it.>>With the trainees, walk through the practice queries on TrainingPracticeQueries. Do the first one for them and point out some of the details when using OOo Base. That is, define the different reserved words, the types of clauses, and point out the way OOo Base recognizes most reserved words and color codes them. It's also useful to describe conventions like the way we capitalize and rename tables and fields.

Revision [20029]

Edited on 2017-05-03 15:48:41 by walker [removed instructions for Base, use pgadmin]
Additions:
>>**Useful Links**
ResearchSQLTutorial
[[https://dl.dropboxusercontent.com/u/50470502/pvsadmin.public.png Database Map]]
PgAdmin>>Please note: interacting with our database through SQL is a privilege for high performing interns and staff. Its use and practice should not interfere with the accomplishment of your normal responsibilities and duties as previously described in any way.
Deletions:
>>ResearchSQLTutorial
[[https://dl.dropboxusercontent.com/u/50470502/pvsadmin.public.png Database Map]]>>Please note: interacting with our database through SQL is a privilege for high performing interns and staff. Its use and practice should not interfere with the accomplishment of your normal responsibilities and duties as previously described in any way.

Revision [20022]

Edited on 2017-05-03 14:39:35 by walker [removed instructions for Base, use pgadmin]
Additions:
Or, if you learn better with tables and text, check out the [[PvsadminSchema]]

Revision [18041]

Edited on 2016-07-06 12:31:52 by walker [fixed the properties information to connect to DB]
Additions:
~- In the desktop Research folder, identify the .odb (Base) file (/export/research/pvsadmin_staff (copy 1).odb). "Copy to" the desktop. It is important that they don't drag and drop or do any other weird things; that will affect the original file. You just want a copy. This is due to the various permissions the IT department has set up.
~- The username is "staff" and the password is "bananastand" and it can be kind of difficult to type when you can't see the letters.
~- Sometimes, you or they might get an error message about having the wrong "host" or something like that. In this situation, use the "Edit" drop down to find "Database" and then "Properties." Once the properties window has popped up, rename the host to "host=chickostick.votesmart.org dbname=pvsadmin". You can test the connection with the bananastand password again. Once it works, make sure to save it.>>With the trainees, walk through the practice queries on TrainingPracticeQueries. Do the first one for them and point out some of the details when using OOo Base. That is, define the different reserved words, the types of clauses, and point out the way OOo Base recognizes most reserved words and color codes them. It's also useful to describe conventions like the way we capitalize and rename tables and fields.
Deletions:
~- In the desktop Research folder, identify the OOo Base shortcut. "Copy to" the desktop. It is important that they don't drag and drop or do any other weird things. This is due to the various permissions the IT department has set up.
~- The password is "bananastand" and it can be kind of difficult to type when you can't see the letters.
~- Sometimes, you or they might get an error message about having the wrong "host" or something like that. In this situation, use the "Edit" drop down to find "database" and then "properties." Once the properties window has popped up, rename the host to "host=admin.votesmart.org dbname=pvsadmin". You can test the connection with the bananastand password again. Once it works, make sure to save it.>>With the trainees, walk through the practice queries on TrainingPracticeQueries. Do the first one for them and point out some of the details when using OOo Base. That is, define the different reserved words, the types of clauses, and point out the way OOo Base recognizes most reserved words and color codes them. It's also useful to describe conventions like the way we capitalize and rename tables and fields.

Revision [16862]

Edited on 2015-10-29 15:38:39 by walker [fixed the properties information to connect to DB]
Additions:
Before being trained, please read this page. It will give you some background for the upcoming training:
Deletions:
Before being trained, please read this page. It will give you some background for the upcoming training.

Revision [14696]

Edited on 2015-04-09 15:07:12 by MichaelM [reorganization with other SQL pages]
Additions:
====New to SQL====
>>ResearchSQLTutorial
TrainingPracticeQueries
[[https://dl.dropboxusercontent.com/u/50470502/pvsadmin.public.png Database Map]]>>Please note: interacting with our database through SQL is a privilege for high performing interns and staff. Its use and practice should not interfere with the accomplishment of your normal responsibilities and duties as previously described in any way.
Understand that this is not the easiest stuff to wrap your head around, so feel free to ask if you need help. Try out [[http://www.w3schools.com/sql/default.asp this tutorial]] if you are interested in further developing your SQL skills.
====How to Train====
~~- Let's look at the “Candidate” table as an example. BE SURE TO EMPHASIZE THAT THEY ARE NOT TO MAKE AND DEFINITELY NOT SAVE CHANGES WHEN VIEWING A TABLE. I believe this permission has been restricted by the IT department, but it's better to be safe than sorry.
>>When describing how an intern or new staff member can access the database and work with SQL:
~- In the desktop Research folder, identify the OOo Base shortcut. "Copy to" the desktop. It is important that they don't drag and drop or do any other weird things. This is due to the various permissions the IT department has set up.
~- The password is "bananastand" and it can be kind of difficult to type when you can't see the letters.
~- Sometimes, you or they might get an error message about having the wrong "host" or something like that. In this situation, use the "Edit" drop down to find "database" and then "properties." Once the properties window has popped up, rename the host to "host=admin.votesmart.org dbname=pvsadmin". You can test the connection with the bananastand password again. Once it works, make sure to save it.>>With the trainees, walk through the practice queries on TrainingPracticeQueries. Do the first one for them and point out some of the details when using OOo Base. That is, define the different reserved words, the types of clauses, and point out the way OOo Base recognizes most reserved words and color codes them. It's also useful to describe conventions like the way we capitalize and rename tables and fields.
~- [[ResearchQueries Answers]] to practice queries with notes
ResearchQueries
[[QueriesCandidatePreferredName Returns candidate table showing preferred name]]
[[QueriesActiveStateLegOfficials Returns all currently active officials in state legislatures with some biographical information]]
[[QueriesForCandidates Queries For Candidates (Office Seeking)]]
[[QueriesForCommittees Queries For Committees]]
[[QueriesForEndorsements Queries For Endorsements]]
[[QueriesForPastOfficials Queries for Former Office Holders]] - To display lists of state and federal officials from prior years.
Deletions:
====**New to SQL**====
Please note: interacting with our database through SQL is a privilege for high performing interns and staff. Its use and practice should not interfere with the accomplishment of your normal responsibilities and duties as previously described in any way.
Understand that this is not the easiest stuff to wrap your head around, so feel free to ask if you need help. Try out **[[http://www.w3schools.com/sql/default.asp this tutorial]]** if you are interested in further developing your SQL skills.
====**How to Train**====
~~- Let's look at the “Candidate” table as an example. BE SURE TO EMPHASIZE THAT THEY ARE NOT TO MAKE AND SAVE CHANGES WHEN VIEWING A TABLE. I believe this permission has been restricted by the IT department, but it's better to be safe than sorry.
With the trainees, walk through the practice queries on http://wiki.votesmart.org/ResearchSQLTutorial. Do the first one for them and point out some of the details when using OOo Base. That is, define the different reserved words, the types of clauses, and point out the way OOo Base recognizes most reserved words and color codes them. It's also useful to describe conventions like the way we capitalize and rename tables and fields.
~- Answers to practice queries with notes
**[[QueriesCandidatePreferredName Returns candidate table showing preferred name]]**
**[[QueriesActiveStateLegOfficials Returns all currently active officials in state legislatures with some biographical information]]**
**[[QueriesForCandidates Queries For Candidates (Office Seeking)]]**
**[[QueriesForCommittees Queries For Committees]]**
**[[QueriesForEndorsements Queries For Endorsements]]**
**[[QueriesForPastOfficials Queries for Former Office Holders]]** - To display lists of state and federal officials from prior years.

Revision [14688]

Edited on 2015-04-07 17:01:30 by MichaelM [reorganization with other SQL pages]
Additions:
These pages include queries to find errors (pyquals and others) as well as measures of productivity and generally interesting queries.
~- Answers to practice queries with notes
~- Queries for the Categorization project
~- [[QueriesForOfficials Queries]] for the Politicians project
~- [[QueriesForSpeeches Queries]] for the Public Statements project
~- [[QueriesForRatings Queries]] for the Special Interest Groups project
~- Queries for Political Resources and associated projects
~- Queries for Campaign Finance data
Deletions:
These pages include queries to find errors (pyquals) as well as measures of productivity and generally interesting queries.
For the Categorization project:
[[QueriesForOfficials For the Politicians project:]]
[[QueriesForSpeeches For the Public Statements project:]]
[[QueriesForRatings For the Special Interest Groups project:]]
For Political Resources and associated projects:
For Campaign Finance data:

Revision [14683]

Edited on 2015-04-07 14:02:43 by MichaelM [reorganization with other SQL pages]

No Differences

Revision [14682]

Edited on 2015-04-07 14:02:02 by MichaelM [reorganization with other SQL pages]
Additions:
=====**__SQL at PVS__**=====
====**New to SQL**====
Please note: interacting with our database through SQL is a privilege for high performing interns and staff. Its use and practice should not interfere with the accomplishment of your normal responsibilities and duties as previously described in any way.
Once you become comfortable with these basic concepts, we can show you how to use different types of JOINs, as well as subqueries. More info on joins can be found on the [[SQLJoins]] page.
Understand that this is not the easiest stuff to wrap your head around, so feel free to ask if you need help. Try out **[[http://www.w3schools.com/sql/default.asp this tutorial]]** if you are interested in further developing your SQL skills.
====**How to Train**====
The Vote Smart SQL training routine has two main sections and three overarching knowledge areas. The sections are addressed in order and the different knowledge areas are emphasized as they come up within each section. The whole training session usually lasts from 2-4 hours depending on how quickly the trainees pick it up and the number of questions. Before the training session, I have them read over this wiki page: http://wiki.votesmart.org/ResearchSQLTutorial (this is mentioned at the top of this page) just to get a handle on some of the vocabulary.
In order of lowest to highest types of knowledge, those areas are:
1. //knowledge about databases// - general database structures (tables, fields, cells, data types, etc.) and relationships as well as the organization specific data as seen on https://dl.dropboxusercontent.com/u/50470502/pvsadmin.public.png (while still conceptually useful, this map may be outdated).
2. //knowledge about the SQL language// - it is important to stress that this is a language in that it requires an understanding of vocabulary and rules of grammar not to mention style and structure.
3. //knowledge about how to ask a question// - ask for the data that will answer the question and build in checks to avoid duplicates, false positives, and other errors.
==Part 1==
The first main section is most closely related to knowledge area # 1 and it covers basic ideas about relational databases, tables, fields, datatypes, along with some details about our tables and fields that they should already know through their work in admin. I make sure to define the various terms starting with the most basic:
~- A piece of data is the fundamental unit in a database. These items are saved as they arrived, whole and intact, in a unique location within the larger database structure, without being broken down, duplicated or otherwise altered.
~~- One example is a birthdate, another is a first name, another is a last name. Notice that the individual letters or numbers of each piece of data are not pieces of data themselves (at least not in the context of Vote Smart's database).
~~- However, not all units are equal. There are different types and each type can have different characteristics not to mention sizes. We will talk more about the different data types shortly.
~~- This may be obvious or not, sometimes it depends on seemingly arbitrary database specific conventions that may not be conventions in another database or evem in another table in the same database.
~~- Imagine an entire database as a large cloud of millions, billions, trillions of individual pieces of data. When we consider it like this the image is immense, unwieldy, incomprehensible and not at all user friendly. Hold on to that thought for a moment.
~- A table is a flat file, basically like a spreadsheet, with rows and columns where the intersection of one column and one row is a cell.
~~- A Table is a mid level organizational unit of a database. Some databases can have many tables, some only a few. Some have so many tables that they are then organized into “schemas”. For the purposes of this training and 99% of the SQL work you will do at PVS, we will only concern ourselves with the tables in Vote Smart's “Public” schema.
~~- In a table, a row (the horizontal one) is properly called a “record”, and a column (the vertical one) is properly called a “field”.
~~~- A record is a series of horizontally adjacent cells that are all related to one another because of their common relationship to a “primary key”. The primary key is what keeps a record together and ensures the first name “Barack” and the last name “Obama” appear together instead of “Barack” and “Romney”, for example. If you did find a discrepency like this, that would be a research error.
~~~- A field is a series of vertically adjacent cells that are all related to one another by their data type. For example, in the “Birthdate” field, you would not find the first name “Barack”. If you did find a discrepency like this, that could be a research error, although many of our tables and fields have various restrictions and rules to prevent this kind of thing.
~~- Each piece of data occupies a cell in a Table. This is the location where it is saved.
~~- There are several types of table: storage, junction, and definition.
~~~- A storage table is large and basically stores large amounts of data. It's the place you go to look for something. The PVS database has a number of storage tables.
~~~~- One example is our Candidate table where all sorts of data on each candidate is stored. We'll take a look at this shortly.
~~~~- Another is our speech table where all of our public statements are stored.
~~~- A junction table is one that joins two other tables in order to establish relationships between them and the data within each. This is where the “Relational” part of relational database comes from.
~~~~- We have a junction table that joins the primary keys from the candidate table to the speech table in order to indicate that Barack Obama gave the 2015 State of the Union Address and not someone else.
~~~- A definition table is another efficiency structure. It allows us to create a relationship between two terms, one of which is drastically shorter or easier to reproduce and the other would be a longer term that might appear frequently throughout the database.
~~~~- The gender table is one example, where male is related to Y and female is related to X. Now across the hundreds of thousands of times where we might otherwise have used 4 characters in one cell to indicate that a politician is a male and 6 characters to indicate that she is female, we only have one character. You can do the math for how much storage space this saves and therefore how much more quickly various pieces of data can be accessed.
~~- Go back to our image of a cloud of data, snap your fingers and imagine that it is organized into tables. Oh look, there it is: https://dl.dropboxusercontent.com/u/50470502/pvsadmin.public.png
~~- With this organizational model, it becomes fairly straightforward to identify an individual cell or ranges of cells by specifying a table, a row, and a cell, or ranges thereof.
~~~- It is worth mentioning that dividing the database into a series of tables enhances computational efficiency by allowing us to access select data with a lower burden on our individual computer's memory. The difference is comparable to trying to read Moby Dick by looking at all 1200 pages all at once versus looking at one page at a time.
~~~~- There will be some tables that you will want to avoid opening using these computers, because the tables are so large they may crash the computer.
~~- Let's look at the “Candidate” table as an example. BE SURE TO EMPHASIZE THAT THEY ARE NOT TO MAKE AND SAVE CHANGES WHEN VIEWING A TABLE. I believe this permission has been restricted by the IT department, but it's better to be safe than sorry.
~~- If you want to think of a heirarchy of units in a database, from the largest it is: “THE DATABASE”, then schemas (optional), then tables (including records and fields), then individual pieces of data.
At this point, we've given the trainees a decent introduction to databases, structures, vocabulary, and some of our data. That should form the foundation of knowledge area #1.
==Part 2==
The second main section really covers the basics of knowledge area #2 and some key parts of #3. Here are some definitions and notes for those basic terms:
~-'SELECT' command - tells the computer that you want to see data from the listed fields.
~~- mention how to call a field by it's full name/location - [table.field]
~~- in a given query fields can be renamed with 'AS' - [table.field AS newname]
~~- this is where aggregate functions like COUNT are used
~- 'FROM' clause - tells the computer what tables to use or "where to look".
~~- mention how to rename tables (generally to something shorter that makes sense) - [tablename newname]
~- 'JOIN' using 'ON' and 'USING' - combines tables in order to accommodate more complex queries and produce more complex datasets.
~~- required when you want to use data from more than one table.
~~- think of the hinge of a door - you want to use a field (or combination of fields in more advanced queries) that is common to both tables but uniquely identifies each record. For this reason, "State_ID" is rarely a valuable joining field.
~- 'WHERE' clause and limiting factors
~~- must be an expression that the computer can evaluate for veracity. "WHERE religion = christian" is good because the computer can identify records where this is true or false. "WHERE christian" is not because it is not an expression.
~~- other operators include but are not limited to: >, >=, <, <=, =!, LIKE, ILIKE, BETWEEN () AND (), NULL, NOT NULL
~~- in simple queries, combine limiting factors with 'AND' and 'OR'
~- 'GROUP BY' - required when using aggregate functions like COUNT in your SELECT clause
~- 'ORDER BY' - orders the query results
~~- ASC is default and you have to specify DESC
~~- separate multiple ordering fields with commas
~- It's good form to end all queries with a semi-colon ;
With the trainees, walk through the practice queries on http://wiki.votesmart.org/ResearchSQLTutorial. Do the first one for them and point out some of the details when using OOo Base. That is, define the different reserved words, the types of clauses, and point out the way OOo Base recognizes most reserved words and color codes them. It's also useful to describe conventions like the way we capitalize and rename tables and fields.
Have the trainees take turns working through the other practice queries. As the trainer, you should help them with direct assistance, leading questions, as well as misleading questions to emphasize certain points. This is especially useful with items related to knowledge area #3. They can help each other as well.
We never get through all 7 of them, so I tell the trainees to work on the others in their spare time. Before ending the training I like to show them some interesting queries we've saved to the wiki (see below). This last step gives the new trainees an idea of what's possible with slightly more advanced queries.
====**Useful Research Queries**====
These pages include queries to find errors (pyquals) as well as measures of productivity and generally interesting queries.
For the Categorization project:
[[QueriesForOfficials For the Politicians project:]]
[[QueriesForSpeeches For the Public Statements project:]]
[[QueriesForRatings For the Special Interest Groups project:]]
For Political Resources and associated projects:
For Campaign Finance data:
Deletions:
=====**__SQL and the Research Department__**=====
**New to SQL?**
Please note that this is extra and should not interfere with the accomplishment of your normal responsibilities and duties in any way.
Once you become comfortable with these basic concepts, we can show you how to use different types of JOINs, as well as subqueries. More info on joins can be found on the [[SQLJoins]] page.
Understand that this is not the easiest stuff to wrap your head around, so feel free to ask if you need help. Once you've finished going over this, feel free to try out some of the TrainingPracticeQueries on the wiki. Try out **[[http://www.w3schools.com/sql/default.asp this tutorial]]** if you are interested in further developing your SQL skills.
===**__Useful Research Queries__**===
===General Queries===
**[[QueriesForOfficials Queries For Officials (Office Held)]]**
**[[QueriesForRatings Queries For Ratings]]**
**[[QueriesForSpeeches Queries for Speeches]]**

Revision [14674]

Edited on 2015-04-03 12:03:01 by MichaelM [reorganization and formatting in progress]
Additions:
=====**__SQL and the Research Department__**=====
**New to SQL?**
Before being trained, please read this page. It will give you some background for the upcoming training.
http://wiki.votesmart.org/ResearchSQLTutorial
During training, we will practice with these queries. It's unlikely we'll get through all 7, so please try the others in your spare time.
http://wiki.votesmart.org/TrainingPracticeQueries
Please note that this is extra and should not interfere with the accomplishment of your normal responsibilities and duties in any way.
This map of our database may help when you're looking for information about the tables: https://dl.dropboxusercontent.com/u/50470502/pvsadmin.public.png
Once you become comfortable with these basic concepts, we can show you how to use different types of JOINs, as well as subqueries. More info on joins can be found on the [[SQLJoins]] page.
Understand that this is not the easiest stuff to wrap your head around, so feel free to ask if you need help. Once you've finished going over this, feel free to try out some of the TrainingPracticeQueries on the wiki. Try out **[[http://www.w3schools.com/sql/default.asp this tutorial]]** if you are interested in further developing your SQL skills.
===**__Useful Research Queries__**===
===General Queries===
**[[QueriesCandidatePreferredName Returns candidate table showing preferred name]]**
**[[QueriesActiveStateLegOfficials Returns all currently active officials in state legislatures with some biographical information]]**
Deletions:
=====**__Useful Research Queries__**=====
====General Queries====
- **[[QueriesCandidatePreferredName Returns candidate table showing preferred name]]**
- **[[QueriesActiveStateLegOfficials Returns all currently active officials in state legislatures with some biographical information]]**

Revision [8796]

Edited on 2013-05-02 15:24:59 by OrenSamet [reorganization and formatting in progress]
Additions:
**[[QueriesForPastOfficials Queries for Former Office Holders]]** - To display lists of state and federal officials from prior years.
Deletions:
**[[QueriesForPastOfficials Queries for Former Office Holders]]**

Revision [8791]

Edited on 2013-05-02 15:23:05 by OrenSamet [reorganization and formatting in progress]
Additions:
**[[QueriesForPastOfficials Queries for Former Office Holders]]**

Revision [8790]

Edited on 2013-05-02 15:22:30 by OrenSamet [reorganization and formatting in progress]
Deletions:
- SELECT c.firstname, c.lastname, vec.state_id, vec.office, vec.districtname, c.gender, c.birthdate FROM candidate c JOIN v_election_candidate vec USING (candidate_id) WHERE vec.state_id = 'MS' AND vec.officetype_id = 'L' AND vec.electionyear = '2007' AND vec.electioncandidatestatus_id = 11 AND vec.electionstage_id = 'G' ORDER BY vec.state_id, vec.office, vec.districtname_id
**This query displays a list of candidates that were elected in a specific state in a specific year (in this case, in Mississippi in 2007). This is useful when you want to display all legislators that were "active" in a state in a given year in the past.**
- SELECT a.firstname, a.lastname, a.state_id, a.office, a.districtname, a.gender, a.birthdate FROM (SELECT c.candidate_id, c.firstname, c.lastname, vec.state_id, vec.office, vec.districtname, c.gender, c.birthdate, vec.districtname_id FROM candidate c JOIN v_election_candidate vec USING (candidate_id) WHERE vec.state_id = 'LA' AND vec.officetype_id = 'L' AND vec.electionyear = '2007' AND vec.electioncandidatestatus_id = 11 AND vec.electionstage_id = 'P') a LEFT OUTER JOIN (SELECT c.candidate_id, c.firstname, c.lastname, vec.state_id, vec.office, vec.districtname, c.gender, c.birthdate, vec.electioncandidatestatus_id, vec.electionstage_id FROM candidate c JOIN v_election_candidate vec USING (candidate_id) WHERE vec.state_id = 'LA' AND vec.officetype_id = 'L' AND vec.electionyear = '2007' AND vec.electioncandidatestatus_id != 11 AND vec.electionstage_id = 'G') b USING (candidate_id) WHERE b is null ORDER BY a.office, a.districtname_id
**This query allows us to exclude a set of candidates that won Primary elections but lost their General elections from a list that displays all candidates that were elected to office in a given year. This query is useful in cases like Louisiana (displayed here), where candidates receiving greater than 50 percent of the vote in a so-called "Jungle Primary" are automatically elected and do not need to advance to a general election contest. Therefore, to find all legislators serving in Louisiana in a particular year in the past, you need to identify all those candidates who won primaries and did have a general election and those candidates that won general election contests. It uses the "LEFT OUTER JOIN" command and then specifies "WHERE b is null."**

Revision [8747]

Edited on 2013-04-30 08:54:20 by OrenSamet [reorganization and formatting in progress]
Additions:
**[[QueriesForSpeeches Queries for Speeches]]**

Revision [8263]

Edited on 2013-03-26 14:03:19 by JasonChow [reorganization and formatting in progress]
Additions:
- **[[QueriesActiveStateLegOfficials Returns all currently active officials in state legislatures with some biographical information]]**
Deletions:
- SELECT c.firstname, c.lastname, voc.state_id, voc.office, voc.districtname, c.gender, c.birthdate FROM candidate c JOIN v_office_candidate voc USING (candidate_id) WHERE voc.status = 'active' AND voc.officetype_id = 'L' ORDER BY voc.state_id, voc.office, voc.districtname_id
**This query displays all currently serving state legislators in the country and some basic biographical information about them. It draws from the "candidate" table and the "v_office_candidate" view table.**

Revision [8262]

Edited on 2013-03-26 13:58:12 by JasonChow [reorganization and formatting in progress]
Additions:
- **[[QueriesCandidatePreferredName Returns candidate table showing preferred name]]**
Deletions:
**[[QueriesCandidatePreferredName Returns candidate table showing preferred name]]**

Revision [8261]

Edited on 2013-03-26 13:56:51 by JasonChow [reorganization and formatting in progress]
Additions:
=====**__Useful Research Queries__**=====
Deletions:
=====Useful Research Queries=====

Revision [8259]

Edited on 2013-03-26 13:55:21 by JasonChow [reorganization and formatting in progress]
Additions:
=====Useful Research Queries=====
Deletions:
**Useful Research Queries**

Revision [8258]

Edited on 2013-03-26 13:54:39 by JasonChow [reorganization and formatting in progress]
Additions:
====General Queries====
**[[QueriesCandidatePreferredName Returns candidate table showing preferred name]]**

Revision [7548]

Edited on 2013-01-09 16:19:19 by JasonChow [reorganization and formatting in progress]
Additions:
**[[QueriesForCommittees Queries For Committees]]**

Revision [7507]

Edited on 2012-12-21 14:47:56 by JasonChow [reorganization and formatting in progress]
Additions:
**[[QueriesForOfficials Queries For Officials (Office Held)]]**
**[[QueriesForCandidates Queries For Candidates (Office Seeking)]]**
**[[QueriesForEndorsements Queries For Endorsements]]**
**[[QueriesForRatings Queries For Ratings]]**
Deletions:
- SELECT M.electionyear, M.sig, M.election_candidate_id, M.candidate_id, c.lastname, c.firstname, c.middlename, c.nickname, c.suffix, o.name office, M.state_id, st.name state, M.districtname_id
FROM
(SELECT X.* FROM
(SELECT Z.*
FROM (SELECT A.electionyear, A.election_id, A.sig, A.endorse_id, B.election_candidate_id, B.candidate_id, B.officetype_id, B.office_id, B.state_id, B.districtname_id
FROM (SELECT states_w_nra2012end.* FROM (SELECT e.electionyear, e.state_id elec_state, s.state_id sig_state, s.name sig, endo.endorse_id, endo.sig_id, endo.election_id FROM sig s LEFT JOIN endorse endo USING (sig_id) JOIN election e USING (election_id) WHERE sig_id = 1034 AND e.electionyear = '2012' AND e.officetype_id = 'C' ORDER BY elec_state) states_w_nra2012end) A
JOIN (SELECT allC2012_elec_cand.* FROM (SELECT e.officetype_id, e.electionyear, ec.election_candidate_id, ec.election_id, ec.candidate_id, ec.office_id, ec.state_id, ec.districtname_id FROM election e LEFT JOIN election_candidate ec USING (election_id) WHERE e.electionyear = '2012' AND e.officetype_id = 'C' ORDER BY e.officetype_id, ec.state_id, ec.districtname_id) allC2012_elec_cand) B USING (election_id)
WHERE A.elec_state = B.state_id
ORDER BY B.office_id, B.state_id, B.districtname_id) Z
GROUP BY Z.electionyear, Z.election_id, Z.sig, Z.endorse_id, Z.election_candidate_id, Z.candidate_id, Z.officetype_id, Z.office_id, Z.state_id, Z.districtname_id) X
JOIN endorse_candidate end_cand USING (endorse_id) WHERE X.election_candidate_id = end_cand.election_candidate_id) M JOIN candidate c USING (candidate_id) JOIN office o USING (office_id) JOIN state st ON (M.state_id = st.state_id) ORDER BY st.name, o.name DESC, M.districtname_id ;

**This query shows endorsed candidates given sig_id, officetype_id, and electionyear. For this particular query, we used the NRA.**

Revision [7502]

Edited on 2012-12-20 15:29:42 by JasonChow [reorganization and formatting in progress]

No Differences

Revision [7501]

Edited on 2012-12-20 15:29:06 by JasonChow [reorganization and formatting in progress]
Additions:

- SELECT M.electionyear, M.sig, M.election_candidate_id, M.candidate_id, c.lastname, c.firstname, c.middlename, c.nickname, c.suffix, o.name office, M.state_id, st.name state, M.districtname_id
FROM
(SELECT X.* FROM
(SELECT Z.*
FROM (SELECT A.electionyear, A.election_id, A.sig, A.endorse_id, B.election_candidate_id, B.candidate_id, B.officetype_id, B.office_id, B.state_id, B.districtname_id
FROM (SELECT states_w_nra2012end.* FROM (SELECT e.electionyear, e.state_id elec_state, s.state_id sig_state, s.name sig, endo.endorse_id, endo.sig_id, endo.election_id FROM sig s LEFT JOIN endorse endo USING (sig_id) JOIN election e USING (election_id) WHERE sig_id = 1034 AND e.electionyear = '2012' AND e.officetype_id = 'C' ORDER BY elec_state) states_w_nra2012end) A
JOIN (SELECT allC2012_elec_cand.* FROM (SELECT e.officetype_id, e.electionyear, ec.election_candidate_id, ec.election_id, ec.candidate_id, ec.office_id, ec.state_id, ec.districtname_id FROM election e LEFT JOIN election_candidate ec USING (election_id) WHERE e.electionyear = '2012' AND e.officetype_id = 'C' ORDER BY e.officetype_id, ec.state_id, ec.districtname_id) allC2012_elec_cand) B USING (election_id)
WHERE A.elec_state = B.state_id
ORDER BY B.office_id, B.state_id, B.districtname_id) Z
GROUP BY Z.electionyear, Z.election_id, Z.sig, Z.endorse_id, Z.election_candidate_id, Z.candidate_id, Z.officetype_id, Z.office_id, Z.state_id, Z.districtname_id) X
JOIN endorse_candidate end_cand USING (endorse_id) WHERE X.election_candidate_id = end_cand.election_candidate_id) M JOIN candidate c USING (candidate_id) JOIN office o USING (office_id) JOIN state st ON (M.state_id = st.state_id) ORDER BY st.name, o.name DESC, M.districtname_id ;

**This query shows endorsed candidates given sig_id, officetype_id, and electionyear. For this particular query, we used the NRA.**

Revision [7481]

The oldest known version of this page was created on 2012-12-14 15:41:14 by JasonChow [reorganization and formatting in progress]
Valid XHTML :: Valid CSS: :: Powered by WikkaWiki