mysql> SELECT CURDATE(), DATE_ADD(CURDATE(), INTERVAL 50 DAY);
+------------+--------------------------------------+
| CURDATE()  | DATE_ADD(CURDATE(), INTERVAL 50 DAY) |
+------------+--------------------------------------+
| 2013-11-08 | 2013-12-28                           |
+------------+--------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT NOW(), DATE_ADD(NOW(), INTERVAL 50 HOUR);
+---------------------+-----------------------------------+
| NOW()               | DATE_ADD(NOW(), INTERVAL 50 HOUR) |
+---------------------+-----------------------------------+
| 2013-11-08 16:11:58 | 2013-11-10 18:11:58               |
+---------------------+-----------------------------------+
1 row in set (0.00 sec)

Exercises:

1. Format a date-time string to look like: November 10, 2013 06:00:00. You can test it using the NOW() function.

   SELECT DATE_FORMAT(NOW(), <format>);
   

2. Add 1,000,000 seconds to the current date and time.
3. Convert the string '1/1/9999' to a date-time value.

The command to create a new database is:

CREATE ( DATABASE | SCHEMA ) [IF NOT EXISTS] <name>

If it exists, you get an error unless you specify "IF NOT EXISTS".
DATABASE and SCHEMA are synonymous.

The command to create a new table is:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] <tbl_name>
    (<col1> <type1>, ..., <coln> <typen>)

For each table in the database, you have to pick a name, columns, and types for the columns. There are additional options for performance and integrity constraints, but the required choices are the table name, the columns, and the data types.

Exercises:

1. Define a table Person with fields Birthdate (date type), Name (string type), and Age (numeric type). How long will you make the name? What string type will you use? What numeric type should you use for the Age? What is the command that defines the table?
2. Define a table Order with fields OrderID (numeric type), CustomerID (numeric type), OrderDate (date type), ShippingDate (date type), and Amount (numeric type).

The insert command requires the table name, the column names, and the values. There are two options:

INSERT [INTO] <tablename> [<col1>, ..., <coln>]
VALUES (<val1>, ..., <valn>]

or

INSERT [INTO] <tablename> 
SET <col1>=<val1>, ..., <coln>=<valn>

Exercises:

1. Add the following rows to the Person relation:

   Birthdate	Name	Age
   1/6/1982	George Facello	31
   2/24/1978	Parto Bamford	35

2. Add a row to the employees table in the employees database, with the gender unspecified (you'll have to give the values for all of the other fields) and birth_date='1959-01-01'.
3. Add a new row to the department table, for department "d010" named "Something".

The DELETE command specifies the table name and a condition that rows to be removed must satisfy:

DELETE FROM <tablename>
WHERE <condition>

You can add a limit to the rows to reduce the risk you run if you make a mistake!

Exercises:

1. Remove the row that you added to the employees table above.
2. Remove all rows for employees over 65 (try computing this using the DATE_SUB function).

Like the DELETE, the UPDATE command lets you specify a condition. You could select a row having a given primary key, which will limit the update to a single row, or you can change a value throughout the database to another value. The actual uses are limited only by your imagination!

UPDATE <tablename>
SET <col1>=<val1>, ..., <coln>=<valn>
WHERE <condition>

Exercises: Change the to_date from '9999-01-01' to '9999-12-31'.

Example:

1. Here is a query that finds the largest salary:

   SELECT salary 
   FROM salaries 
   WHERE salary >= ALL (SELECT salary FROM salaries);
   

   We'll find an easier way to do this later.
2. Here is a query that prints the name and salary of the employee with the largest salary:

   SELECT first_name, last_name, salary
   FROM employees JOIN salaries 
   USING (emp_no) 
   WHERE salary >= ALL (SELECT salary FROM salaries);
   

   The above is a pretty straightforward way of doing it -- the subquery selects the largest salary, and the WHERE clause in the outer query checks that the employee's salary is equal to the maximum.
3. List employees that used to work for the company but are not working for it now.

   SELECT first_name, last_name 
   FROM employees  
   WHERE emp_no NOT IN (SELECT emp_no FROM dept_emp WHERE to_date='9999-01-01');
   

There are several kinds of joins:

• INNER JOIN: We've already seen inner joins, which happen when two tables are "pasted together" on some columns -- often a foreign key in one table matched to a primary key in another.

  Example: To list departments with their employees, you can use

  SELECT dept_no, first_name, last_name
  FROM dept_emp JOIN employees
  WHERE employees.emp_no=dept_emp.emp_no and
        to_date='9999-01-01';
  

  or

  SELECT dept_no, first_name, last_name 
  FROM dept_emp JOIN employees 
  USING (emp_no) 
  WHERE to_date='9999-01-01';
  

  Exercises:

  1. List employees of department d001 that started on 1/1/1985.
  2. List employees that began in department d001 on 1/1/1990.
  3. List employees that transferred from another department to d001 on 1/1/1990.
• LEFT OUTER JOIN: An inner join can only return values if there are matching rows in the two relations. This is not always quite what you want. Consider this example: you want to print a list of departments with their current employees, but there are some new departments that don't have any employees. You still want them listed. Here is the query:

  SELECT emp_no, dept_no
  FROM departments LEFT OUTER JOIN dept_emp
  USING (dept_no);
  

  Note that all the rows of the left-most table (departments) appear in the result set.

• RIGHT OUTER JOIN: The same as left outer join, except all the rows of the right-most table appear in the result set.

Exercises: Try both left and right outer join. How do you manipulate the order of the relations to make this work?

1. List employees born on 1/1/1959, together with their departments.
2. List departments, together with a history of their managers.

Use the ORDER BY clause and list the columns you want used to sort.

Example: List departments ordered by current manager's last name.

SELECT dept_no, first_name, last_name
FROM departments JOIN (dept_manager JOIN employees USING (emp_no)) USING (dept_no) 
WHERE to_date>curdate()
ORDER BY last_name;

Aggregation operators:

• AVG(<column>)
• COUNT
  • COUNT(*): row count
  • COUNT(<column>): number of rows with non-null values in column
  • COUNT(DISTINCT <column>): number of distinct values in column
• MIN(<column>): minimum value in column
• MAX(<column>): maximum value in column
• STD(<column>): standard deviation of column
• SUM(<column>): sum of values in column

Example: Total current monthly payroll for department d001.

SELECT SUM(salary/12) 
FROM dept_emp JOIN (employees JOIN salaries USING (emp_no)) USING (emp_no)
WHERE salaries.to_date>curdate() AND dept_emp.to_date>curdate() AND dept_no="d001";

1. What data type would you use for, and why:
   1. True/False answers to a series of questions.
   2. People's ages
   3. People's salaries
   4. Astronomical distances
   5. Currency
   6. Concentration of a protein in a cell
   7. Numbers of cells in various animal or plant species
   8. A name
   9. A description
   10. The time of day
   11. The date
   12. When a shipment was put on a truck
2. Review all the exercises and examples above.