Exercise 43: SQL Management

Exercise 43: SQL Administration

Translator: Flying Dragon

Protocol: CC BY-NC-SA 4.0

Proudly using Google Translate

The word “administration” is overloaded in the database. It means “keeping the PostgreSQL server running”, or “changing and migrating tables for deployment of new software”. In this exercise, I only show you how to change and migrate simple Outlines. Managing a full database server is beyond the scope of this book.

Destroy and change tables

You’ve already encountered DROP TABLE as a way to DROP a TABLE. I’ll show you another way to use it and how to use ALTER TABLE to add or remove columns from a TABLE.

/* Only drop table if it exists. */
DROP TABLE IF EXISTS person;

/* Create again to work with it. */
CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    age INTEGER
);

/* Rename the table to peoples. */
ALTER TABLE person RENAME TO peoples;

/* Add a hatred column to peoples. */
ALTER TABLE peoples ADD COLUMN hatred INTEGER;

/* Rename peoples back to person. */
ALTER TABLE peoples RENAME TO person;

.schema person

/* We don't need that. */
DROP TABLE person;
Copy the code

I’m making some fake changes to the TABLE to demonstrate these commands, but using ALTER TABLE and DROP TABLE statements, this is just about everything you can do in SQLite3. I’ll explain it so you know what’s going on:

ex21.sql:2

Using the IF EXISTS modifier, the table is discarded only IF it already EXISTS. This inhibits errors when you run your.SQL script on a new database with no tables.

ex21.sql:5

Just recreate the table to process it.

ex21.sql:13

Rename it to Peoples using ALTER TABLE.

ex21.sql:16

Add a new column to the newly named table Peoples, which is an INTEGER.

ex21.sql:19

Rename Peoples back to Person, as this is a silly name for a table.

ex21.sql:21

Dump the outline of Person, so you can see that it has new hatred columns.

ex21.sql:24

After this exercise, discard the table to clean it up.

Migrate and evolve data

Let’s apply some of the techniques you’ve learned. I’ll let you pick your database and “evolve” the schema into different forms. You need to make sure you have a good understanding of previous exercises and that your code.sql works. If you haven’t done each of these things, go back and figure everything out.

To make sure you try this exercise in the right state, when you run your code. SQL, you should be able to run.schema, like this:

$ sqlite3 ex13.db < code.sql
$ sqlite3 ex13.db .schema
CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    age INTEGER
);
CREATE TABLE person_pet (
    person_id INTEGER,
    pet_id INTEGER
);
CREATE TABLE pet (
    id INTEGER PRIMARY KEY,
    name TEXT,
    breed TEXT,
    age INTEGER,
    dead INTEGER,
    dob DATETIME
);
Copy the code

Make sure your watch looks like mine. And if not, go back and remove any commands that execute ALTER TABLE or whatever after the last exercise.

Challenging exercise

The tasks you need to complete are the following list of database changes:

  • topersonadddeadColumns, likepetsIn that way.
  • topersonaddphone_numberThe column.
  • topersonaddsalaryColumn, it isfloat.
  • topersonandpetadddobColumn, it isDATETIME.
  • toperson_petaddpurchased_onColumn, it isDATETIME.
  • topetaddparentColumn, it isINTEGERAnd hold its parent’sid.
  • useUPDATEStatement to update existing database records with new column data. Don’t forgetperson_petIn relational tablepurchased_onColumn to indicate when the person bought the pet.
  • Add four more people and five pets and assign them affiliations and which pet is the parent. In the last section, remember that you get the parent’s ID and then set it toparentColumn.
  • Write a query to find all pets purchased after 2004 and the names of their owners. The key is based onpurchased_onColumn willperson_petMap topetandparent.
  • Write a query to find the parents of a given pet. Look again atpet.parentTo implement it. It’s actually quite simple, so don’t make a big deal out of it.
  • Update yourcode.sqlFile, you’ve put all your code in there and let it use itDROP TABLE IF EXISTSSyntax.
  • useALTER TABLEtopersonaddheightandweightColumn and place it in yourcode.sqlFile.
  • Run the newcode.sqlScript to reset the database, you should have no errors.

You should do this by writing an ex13.sql file that contains these new things. Then test it by resetting the database with code.sql, then running ex13.sql to change the database, and performing a SELECT query to confirm that you made the correct changes.

Learn more

Continue reading the documentation for DROP TABLE and ALTER TABLE, then visit the SQLite3 language page and read the rest of the CREATE and DROP statements in the documentation.