Homework 0
CS422, Summer 2005

Due: Tuesday, June 28

Please put all the SQL statements in a script file and upload it using the online turnin server. Note that file uploading will be disabled automatically after 11:59PM of the due date, so please turn in your work on time.

---

[Reading]

• Textbook Chapter 1.

[CREATE TABLE ] (10pt) Create the following three tables in the database with proper attribute types and constraints (primary key, foreign key, and not null).

• Senators ( senator_id, first_name, last_name, state, party )
• Bills ( bill_id, bill_name )
• Votes( senator_id, bill_id, vote ), note that vote could be Yes, No, Abstain.

[INSERT] (10pt) Use INSERT statements to populate the tables with some data so that each table has at least four records.

[SELECT] (20pt) Complete the following queries:

• Find the state which senator Hillary Clinton represents.
• Find the names of the bills on which Senator Hillary Clinton has voted Yes.
• Find the number of Republican senators in the Senate.
• Find the names of the bills which have been passed, e.g. the bills that have more Yes votes on them than No votes.