Implementation/SQL

Introduction:

An assignment to make sure you get Postgres installed and can execute basic statements. The subject of the table you will work with is our local cities and people. We will start with a single table and modify this in the weeks to come.

The assignment:

1. Download and install Postgres.
2. In pgAdmin 4, on the left, explode the Servers to choose one, after which you should see the Databases item. Right-click on that item to create a database or use the default database called postgres
3. If you right-click on the database, the next-to-last menu option is Query Tool. Choose that and then you can save your work in a script called create.sql to define the table city.
   1. Remember that SQL is code, even if the syntax is new for you. Do not use Word or another word processor to edit it. The Query Tool feature is not the greatest, but it has syntax highlighting.
      1. When saving your files, remember to add .sql at the end the pgAdmin editor does not do this automatically
      2. Execute your scripts to test them.
   2. To distinguish assignments for grading purposes, create a schema named using your last name (up to 6 letters), appending your first initial, so my schema would be yangd
      1. You can find examples at: PostgreSQL: Documentation: 14: CREATE SCHEMA
      2. This general section of the Postgres documentation has all the SQL syntax, so its a decent bookmark to have for this semester
   3. Define the table within the schema for me, this would be yangd.city
   4. The fields are:
      1. id : a SERIAL field city names are far from unique
      2. name : a string for the name of the city
      3. county : a string for the name of the county
      4. state : a string for the name of the state
      5. incorporated: date on which the city was officially established

2. Create a script insert_ok.sql to insert acceptable data. Use INSERT statements to insert records into the city table
   1. Insert enough records so that any queries beyond the first one will return at least one, but not all records
   2. You may find PostgreSQL: Documentation: 14: 9.8. Data Type Formatting Functions a useful resource for the incorporated attribute just call the function instead of a literal value
      1. SQL itself does not have the kind of variables like Python or C++ does, so do not try to separate this into multiple statements
      2. [Databases like Postgres do support stored procedures for more complicated operations, but that is a separate language]
3. Create a script query.sql that includes semicolon-separated queries to do the following (terminate each query with a semicolon so the grader can run the whole script at once) :
   1. List name, county and state of all cities
   2. List the DISTINCT counties of California in your database (of course, you are not expected to insert data for all counties, but you should include at least two California counties, at least one county outside of California, and at least two cities in one of the California counties). No hard-coding write the query against the data you insert.
   3. List how many years old (rounded down) Hayward is again, you only get credit for writing the query against your data, not for hard-coding a response. You should find the age() and date_trunc() functions useful. See PostgreSQL: Documentation: 14: 9.9. Date/Time Functions and Operators for examples
4. Create a script insert_bad.sql that uses INSERT statements to insert data that should not be allowed, violating the rules below. We will eventually cover the various features of SQL that allow you to block these inserts, but for now, they should be allowed
   1. City name plus county plus state should be unique (Note that city name by itself is not unique, even within a single state)
   2. The date of incorporation cannot be in the future
   3. The name of the state cannot be null