Assignment 10

Structured Query Language or SQL is a language used to get data from a database. Databases are used to store data in a persisted long-term location.

The most popular implementation of SQL is MySQL. We will be using that for our database work. This assignment will be about setting up MySQL and getting used to SQL before we start to integrate it with our PHP backend.

  1. Install MySQL Community Edition. This will install MySQL server and MySQL Workbench, among other things. The server is what actually hosts the database on your machine. The Workbench is a visual program to look at your database contents and perform queries. By default, the "root" user account will be created, but I'd like you to add another account during installation with full access.

  2. In MySQL Workbench, create a new Schema and call it "myDatabase". This is your new database. Schemas are shown in the "schemas" tab. In that schema make a new table named "users", with these fields: "id" (int), "name" (string), "email" (string), "zip" (int), "accountCreated" (datetime). Make "id" the primary key. Please look up tutorials that will walk you through things you do not understand. Understanding this is very important. There are many options you can select from and you should understand what they are for.

  3. You can add data in the workbench by executing code directly in a SQL file or by viewing the database contents and manually adding things into the table, then hitting Apply. Add 5 test user rows into your database.

  4. If you noticed, as you created the table and created the users, MySQL workbench gave you a preview of SQL code it was executing. Make an SQL script file that will 1. Create the table from question 2, and then 2. add 5 users like in question 3. Making scripts like this is useful for recreating sets of test data without manual entry.


Next you will be turning in queries for the following:

  1. Selecting all users

  2. Selecting all users sorted by their name from a-z

  3. Selecting all users sorted by their email from z-a

  4. Selecting users with the length of their name greater than 5 sorted by accountCreated

  5. Users with gmail emails

  6. Users without gmail emails

  7. Just the zip codes of users

  8. Only unique zip codes of users (make several users have the same zip to test)

  9. The names of users and their account creation date

  10. The id of users with email "noshowup@neverever.com" and name "Noelle"