admin

Generating Test Data

Visit http://generatedata.com … it’s VERY useful.

Duplicating a table

Useful for testing.

CREATE TABLE new_table LIKE original_table;
INSERT INTO new_table SELECT * FROM original_table;

Joins

The basic form of queries is:

SELECT A1, A2, ..., An
 FROM R1, R2, ..., Rn
WHERE P;

$A_i$ represents an attribute, $R_i$ represents a relation, and $P$ is a predicate.

CROSS PRODUCT

If you list two Relations, R1,R2, the DB performs a cross product of the two relations, which includes all combinations of tuples from both relations.

SELECT * FROM College, Tryout;

[!NOTE] As you might expect, performing a cross product on two large relations can take a lot of time and memory.

Cross product (a.k.a. Cartesian Product) is generally used with a WHERE predicate.

JOIN Operations

JOIN operations take two relations and return another relation as the result. JOINs are often used as subquery expressions in the FROM clause.

• JOIN condition
  determines which tuples match, and with which attributes

    NATURAL
    ON <predicate>
    USING (A1, A2, ...

• JOIN type
  specifies what to do with the unmatched tuples

  INNER JOIN
  LEFT OUTER JOIN
  RIGHT OUTER JOIN
  FULL OUTER JOIN (**note** FULL is not supported by MySQL)
  

Outer is used to avoid the loss of any information. It adds in tuples from one relation that doesn’t match tuples in the other, which one is decided by LEFT or RIGHT.

SIMPLE JOIN

Same as Cross Product but with a WHERE predicate that matches equal keys in each relation. Using the JOIN term is the same as using a ,.

SELECT  * 
  FROM  Player, Tryout 
  ORDER BY Player.pID;
/* */
SELECT * 
  FROM Player, Tryout 
 WHERE Player.pID=Tryout.pID 
   AND decision='yes';

For simple JOINs matching attributes are included in the resulting relation. Here, the pIDs from both relations are included in the resulting relation.

NATURAL JOIN

This join matches tuples with the same VALUES for all common attributes, keeping one copy of each common attribute.

This is the same as SIMPLE JOIN except that only one copy of the attribute that was used for the JOIN is included in the resulting relation.

SELECT *
  FROM Player
  NATURAL JOIN Tryout;
/* */
SELECT *
FROM Player
NATURAL JOIN Tryout
WHERE decision="yes";

Notice that the NATURAL JOIN has the same result as the SIMPLE JOIN with the explicit pID=pID condition, except for the duplicate pID attribute in the result.

SELECT *
FROM Player, Tryout
WHERE Player.pID=Tryout.pID;
/* */
SELECT *
FROM Player
NATURAL JOIN Tryout;

OUTER JOINs

See Resources/Examples/SQL/sql_files/OuterJoins.sql from website.

/* This works but produces NULLs for courses with no prereq */
SELECT *
   FROM course
NATURAL LEFT OUTER JOIN prereq;
/* This works but only produces courses WITH a prereq*/
SELECT *
FROM course
NATURAL RIGHT OUTER JOIN prereq;

MySQL doesn’t have FULL OUTER JOIN, so to get that you just UNION a LEFT OUTER JOIN and a RIGHT OUTER JOIN.

UN-NATURAL JOINs

Compare this to the LEFT OUTER JOIN:

Rename using AS

Attributes of relations may be renamed to avoid conflicts

SELECT pID, pName, HS/1000
  FROM Player;
/* */
SELECT pID, pName, HS/1000 AS HSScaled
  FROM Player;

/* Rename is especially useful with Self-Joins */
/* Find the names of all the players who have yCards AND
came from a HS smaller than some other Player*/
SELECT *
  FROM Player;
/* */
SELECT DISTINCT A.pName
  FROM Player AS A , Player AS B
 WHERE A.yCard = "yes"
   AND A.HS < B.HS;

String functions

LIKE: ‘%’ matches 0 or more characters, ‘_’ matches exactly 1 character and these patterns are case-sensitive

SELECT DISTINCT pName
  FROM Player
 WHERE pName LIKE '%a%';
/* */
SELECT DISTINCT pName
  FROM Player
 WHERE pName LIKE '__a%';

SQL supports a variety of other string operations :

SELECT  EMP_NUM, CONCAT(EMP_FNAME," ",EMP_LNAME) AS Person,
    	CONCAT("was hired on a ",DAYNAME(EMP_HIRE_DATE)) AS Hired,
    	CONCAT("for job code: ", CAST(JOB_CODE AS CHAR), ".") AS JobCode
  FROM Employee;

+---------+--------------------+--------------------------+--------------------+
| EMP_NUM | Person             | Hired                    | JobCode            |
+---------+--------------------+--------------------------+--------------------+
| 101     | John News          | was hired on a Wednesday | for job code: 502. |
| 102     | David Senior       | was hired on a Wednesday | for job code: 501. |
| 103     | June Arbough       | was hired on a Monday    | for job code: 503. |
| 104     | Anne Ramoras       | was hired on a Tuesday   | for job code: 501. |
| 105     | Alice Johnson      | was hired on a Tuesday   | for job code: 502. |
| 106     | William Smithfield | was hired on a Wednesday | for job code: 500. |
| 107     | Maria Alonzo       | was hired on a Monday    | for job code: 500. |
| 108     | Ralph Washington   | was hired on a Tuesday   | for job code: 501. |
| 109     | Larry Smith        | was hired on a Sunday    | for job code: 501. |
| 110     | Gerald Olenko      | was hired on a Wednesday | for job code: 505. |
| 111     | Geoff Wabash       | was hired on a Tuesday   | for job code: 506. |
| 112     | Darlene Smithson   | was hired on a Monday    | for job code: 507. |
| 113     | Delbert Joenbrood  | was hired on a Tuesday   | for job code: 508. |
| 114     | Annelise Jones     | was hired on a Tuesday   | for job code: 508. |
| 115     | Travis Bawangi     | was hired on a Thursday  | for job code: 501. |
| 116     | Gerald Pratt       | was hired on a Sunday    | for job code: 510. |
| 117     | Angie Williamson   | was hired on a Sunday    | for job code: 509. |
| 118     | James Frommer      | was hired on a Wednesday | for job code: 510. |
+---------+--------------------+--------------------------+--------------------+
18 rows in set (0.02 sec)

BETWEEN operator

SELECT pName
  FROM Player
 WHERE HS between 900 AND 1500;

Comparison of tuples (including subsets of tuples)

SELECT pName, pPos
  FROM Player, Tryout
 WHERE (Player.pID, yCard) = (Tryout.pID, "yes");

Set operations

For these operations the two “sets” must be UNION Compatible

UNION

SELECT cName, enr, state
  FROM College
 WHERE enr > 12000
   AND cName LIKE "_SU";
/* */
SELECT cName, enr, state
  FROM College
 WHERE enr > 10000
   AND state="LA";
/* */
    (SELECT cName, enr, state
       FROM College
      WHERE enr > 12000
        AND cName LIKE "_SU")
  UNION
    (SELECT cName, enr, state
       FROM College
      WHERE enr > 10000
        AND state="LA");

INTERSECT … NOT!

MySQL doesn’t support INTERSECT, so we do it this way:

SELECT cName
  FROM Tryout
 WHERE pPos="goalie"
   AND (cName) IN
                (SELECT cName
                   FROM Tryout
                  WHERE decision="no");

Note that this example returns two results when we expected one … why?

EXCEPT or MINUS … NOT!

MySQL doesn’t support EXCEPT or MINUS, so we do it this way:

SELECT cName
  FROM Tryout
 WHERE pPos="goalie"
   AND (cName) NOT IN
                (SELECT cName
                   FROM Tryout
                  WHERE decision="yes");

Notice that we’re “subtracting” more than we have … it’s ok

Here is another example from the MySQL Documentation:

CREATE TABLE seqs (i INT);
INSERT INTO seqs
VALUES (1),(2),(3),(4),(5),(6);

SELECT i
  FROM seqs
 WHERE i <= 3

 UNION

SELECT i
  FROM seqs
 WHERE i>=3;
+------+
| i    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+

SELECT i
  FROM seqs
 WHERE i <= 3

 EXCEPT

SELECT i
  FROM seqs
 WHERE i>=3;

+------+
| i    |
+------+
|    1 |
|    2 |
+------+

SELECT i
  FROM seqs
 WHERE i <= 3

 INTERSECT

 SELECT i
   FROM seqs
  WHERE i>=3;

+------+
| i    |
+------+
|    3 |
+------+

AUTO_INCREMENT

An integer attribute of a relation may be declared as AUTO_INCREMENT which will cause the database to automatically increment the attributes value whenever a INSERT is performed. In the INSERT no value is provided for an AUTO_INCREMENT field.

You may obtain the last value stored as a result of an AUTO_INCREMENT using the last_insert_id() function:

SELECT last_insert_id() AS theLastOne
  FROM relation;

SWITCH TO UNIVERSITY.SQL (From Silberschatz, et.al.) for the next examples

Explain with some SELECT * examples

Subqueries in FROM

Find the average instructors’ salaries of those departments where the average salary is greater than $42,000

SELECT dept_name, avg_salary
FROM
        (SELECT dept_name, avg (salary) AS avg_salary
           FROM instructor
          GROUP BY dept_name
        ) AS T
 WHERE avg_salary > 42000;

NOT EXISTS

Find all students who have taken all courses offered in the Biology department. Note: this will not work in MySQL due to the EXCEPT .

SELECT DISTINCT S.ID, S.name
  FROM student AS S
 WHERE NOT EXISTS (
        (SELECT course_id
           FROM Course
          WHERE dept_name = 'Biology'
        )
        EXCEPT (
        (SELECT T.course_id
           FROM takes AS T
          WHERE S.ID = T.ID
        )
    )
 );

WITH

This clause provides a way of defining a temporary view whose definition is available only to the query in which the with clause occurs. Note: this will not work in MySQL prior to version 8.0.

/* Find all departments with the maximum allowed budget */
WITH max_budget (value) AS (
                            SELECT MAX(budget)
                              FROM Department
                        )
SELECT budget
  FROM Department, max_budget
 WHERE Department.budget = max_budget.value;

Scalar subquery

These may be used when a single value is expected.

SELECT dept_name,  (
                    SELECT COUNT(*)
                      FROM instructor
                     WHERE Department.dept_name = instructor.dept_name
                   ) AS num_instructors
FROM Department;

Case Statement for conditional updates to the DB

UPDATE instructor2
   SET salary = case
  WHEN salary <= 60000 THEN salary * 1.05
  WHEN salary <= 80000 THEN salary * 1.04
  ELSE salary * 1.03
   END

---

Authorization in SQL

SELECT, INSERT, UPDATE, and DELETE authorizations. Schema mods include INDEX, RESOURCES (new relations), ALTER, and DROP

Use grant to give authorization

GRANT SELECT ON Tryout TO user1, user2, ...;
GRANT SELECT ON Student TO PUBLIC;
GRANT SELECT ON cs_instructor to user1, user2, ...;  /* where cs_instructor is a view */*
GRANT SELECT ON Tryout to *ROLE*;
GRANT ALL PRIVILEGES to CCPalmer;

and revoke undoes grant.

In other SQL’s, there is create role Then you grant privileges to that role and then you can grant role to a person or a relation.

and even grant the right to grant, as in grant select on Player to ccpalmer with grant option;

---

VIEWS and updatable VIEWS

There may be times when it is inappropriate for some users to see every attribute in every table.

• consider a student who needs to know an instructor’s name and department but not their salary. They need to see something like

SELECT name, dept_name
  FROM instructor
 WHERE name="Mozart";

A SQL view provides a mechanism for limiting the attributes that certain users may see.

A SQL view also provides a way to hide attribute names, which might change, from an application.

Lou Franco described views this way:

“In a way, a view is like an interface. You can change the underlying table structure all you want, but the view gives a way for the code to not have to change.

Views are a nice way of providing something simple to report writers. If your business users want to access the data from something like Crystal Reports, you can give them some views in their account that simplify the data – maybe even denormalize it for them.

The view can be based on any valid SQL expression. You can specify names for the output relation by placing the comma-separated desired names within parentheses after the view name.

Once defined, the view can be used to refer to the virtual relation it generates.

This does not create a new relation/table , it essentially just saves a query.

DROP VIEW IF EXISTS faculty;
CREATE VIEW faculty AS
                        SELECT ID, name, dept_name
                          FROM instructor;
--
SELECT *
  FROM faculty;
SELECT *
  FROM faculty
 WHERE name="Mozart";

or a view of department salary totals

-- Just in case
DROP VIEW departments_total_salary;
-- Show what's in instructor relation
SELECT *
 FROM instructor;
-- create the view, naming its output columns
CREATE VIEW departments_total_salary(dept_name, total_salary) AS
            SELECT dept_name, SUM(salary)
                FROM instructor
                GROUP BY dept_name;
-- try the new view
SELECT *
  FROM departments_total_salary;

Views can be defined in terms of other views

DROP VIEW IF EXISTS physics_fall_2009;
CREATE VIEW physics_fall_2009 AS
            SELECT course.course_id, sec_id, building, room_number
              FROM course, section
             WHERE course.course_id = section.course_id
               AND course.dept_name = 'Physics'
               AND section.semester = 'Fall'
               AND section.year = '2009';
--
DROP VIEW IF EXISTS physics_fall_2009_watson;
CREATE VIEW physics_fall_2009_watson AS
            SELECT course_id, room_number
              FROM physics_fall_2009
             WHERE building = 'Watson';
--
SELECT *
  FROM physics_fall_2009;
--
SELECT *
  FROM physics_fall_2009_watson;

To display all the views defined for a database, use:

SHOW FULL TABLES;
-- or to just see the views, which are just special tables:
 SHOW FULL TABLES
WHERE table_type = 'VIEW';

A “regular” view is a virtual table representing the result of a database query. Each time the view is accessed, the RDBMS must run the query to produce the result set for the view.

Materialized views

Views are temporary in nature. Everytime you use a View the DB has to recreate the results of running the view. These normal views can be thought of as “ephemeral”. If a view is used a lot, the performance of the database may suffer.

Materialized Views are created as actual physical tables.

• this is done for performance reasons
• it is potentially a maintenance problem since the view must be updated when the underlying relations are updated … and you will forget about the materialized view !

Materialized views are not supported in MySQL. Here’s an example FROM Oracle:

CREATE MATERIALIZED VIEW LOG ON sales
  WITH SEQUENCE, ROWID
                 (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold)
                 INCLUDING NEW VALUES;

---

TRIGGERS

A Trigger is a SQL statement that is automatically executed by the DBMS as a side effect of a modification to the db.

It can be set to run before OR after the mod.

  DROP TABLE IF EXISTS players_audit;
CREATE TABLE players_audit (
        id          INT(11) NOT NULL AUTO_INCREMENT,
        pID         INT(11) NOT NULL,
        pName       varCHAR(50) NOT NULL,
        changed_on datetime DEFAULT NULL,
        action      VARCHAR(50) DEFAULT NULL,
                    PRIMARY KEY (id)
);

DELIMITER $$

DROP TRIGGER IF EXISTS before_player_update;
CREATE TRIGGER before_player_update
    BEFORE UPDATE ON Player
    FOR EACH ROW BEGIN

        INSERT INTO players_audit
           SET action = 'update',
               pID = OLD.pID,
               pName = OLD.pName,
               changed_on = NOW();
    END$$
DELIMITER ;

mysql> SELECT *
         FROM Player;
+-------+---------+-------+------+
| pID   | pName   | yCard | HS   |
+-------+---------+-------+------+
| 10001 | Andrew  | no    | 1200 |
| 20002 | Blake   | no    | 1600 |
| 30003 | Charles | no    |  600 |
| 40004 | David   | yes   | 1600 |
| 40004 | David   | yes   | 1600 |
| 50005 | Eddie   | yes   |  300 |
+-------+---------+-------+------+
6 rows in set (0.01 sec)

mysql> SET SQL_SAFE_UPDATES=0;  -- just in case
mysql> UPDATE Player
          SET pName = 'Bryan'
        WHERE pID = 20002;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT *
         FROM Player;
+-------+---------+-------+------+
| pID   | pName   | yCard | HS   |
+-------+---------+-------+------+
| 10001 | Andrew  | no    | 1200 |
| 20002 | Bryan   | no    | 1600 |
| 30003 | Charles | no    |  600 |
| 40004 | David   | yes   | 1600 |
| 40004 | David   | yes   | 1600 |
| 50005 | Eddie   | yes   |  300 |
+-------+---------+-------+------+
6 rows in set (0.00 sec)

mysql> SELECT *
         FROM players_audit;
+----+-------+-------+---------------------+--------+
| id | pID   | pName | changed_on          | action |
+----+-------+-------+---------------------+--------+
|  1 | 20002 | Blake | 2015-04-27 00:25:34 | update |
+----+-------+-------+---------------------+--------+
1 row in set (0.00 sec)

mysql>

The Trigger above will catch UPDATEs, but not DELETEs. You would need another Trigger:

DELIMITER $$
DROP TRIGGER IF EXISTS before_player_delete;
CREATE TRIGGER before_player_delete
    BEFORE DELETE ON Player
       FOR EACH ROW BEGIN

    INSERT INTO players_audit
       SET action = 'delete',
           pID = OLD.pID,
           pName = OLD.pName,
           changed_on = NOW();
        END$$
DELIMITER ;

You can drop Triggers, but you can’t ALTER them.

You can list them, but it’s tough to understand the output:

SELECT *
  FROM Information_Schema.Triggers
 WHERE Trigger_schema = 'class'
   AND Trigger_name = 'before_player_update';

You can also create them to run automatically on some schedule.

---

STORED PROCEDURES

A stored procedure is a set of SQL statements stored inside the database. It can be invoked at the command line, by triggers, by other procedures, or by application code (e.g., C, Python).

Procedures and Functions are all about readability and usability!

Advantages

• performance
• reduces traffic since table entries don’t have to be sent to the calling application, just a few parameters come in and all the table entries are worked on in the database
• they are reusable, making it easier for appl’s to use the db
• they are secured by the DBA

Disadvantages

• memory utilization of the DB may increase
• CPU utilization of the DB may increase No surprise here - the DB is doing the appl’s work!
• They are tough to debug

You must use the DELIMITER command since you will need semi-colons inside!

DROP PROCEDURE IF EXISTS GetAllPlayers;
DELIMITER //
CREATE PROCEDURE GetAllPlayers()
  BEGIN
        SELECT *
          FROM Player;
  END//
DELIMITER ;

Then later, perhaps at the command line:

call GetAllPlayers();

and you get the results from the SELECT.

You can declare and use variables inside the PROCEDURE:

DECLARE total_count INT DEFAULT 0
    SET total_count = 10;

and you can SELECT INTO a variable:

DECLARE total_count INT DEFAULT 0
 SELECT COUNT(*) INTO total_count
   FROM inventory;

You can pass parameters to them:

DROP PROCEDURE IF EXISTS CountPlayersByStatus;
DELIMITER $$
CREATE PROCEDURE CountPlayersByStatus(
    IN pStatus VARCHAR(25),
    OUT total INT)
    BEGIN
        SELECT count(pStatus)
        INTO total
        FROM Tryout
        WHERE decision = pStatus;
    END$$
DELIMITER ;

Then

CALL CountPlayersByStatus("no",@totalno);
SELECT @totalno;
+----------+
| @totalno |
+----------+
|        4 |
+----------+

---

Stored functions

These are simpler Procedures that return a single value.

Specify DETERMINISTIC if the function always returns the same value for the same input. This enables optimization by the DBMS. NOT DETERMINISTIC is the non-default alternative.

DROP FUNCTION IF EXISTS StudentClass;
DELIMITER $$
CREATE FUNCTION StudentClass(s_credits integer) RETURNS VARCHAR(16)
    DETERMINISTIC
BEGIN
    DECLARE lvl varchar(16);

    IF s_credits > 100 THEN
    SET lvl = 'SENIOR';
    ELSEIF (s_credits <= 100 AND s_credits > 60) THEN
        SET lvl = 'JUNIOR';
    ELSEIF (s_credits <= 60 AND s_credits > 30) THEN
        SET lvl = 'SOPHOMORE';
    ELSEIF (s_credits <= 30) THEN
        SET lvl = 'FRESHPERSON';
    END IF;

    RETURN (lvl);
END$$
DELIMITER ;

and it’s used like this:

SELECT  name, StudentClass(tot_cred)
  FROM student;

What’s the difference between stored procedures and functions?

The MySQL DOcumentation says:

“A stored procedure is invoked using the CALL statement. A procedure does not have a return value but can modify its parameters for later inspection by the caller. It can also generate result sets to be returned to the client program. A stored function is used much like a built-in function. you invoke it in an expression and it returns a value during expression evaluation.”

StackOverflow’s Sean K. Anderson put it this way:

“Functions follow the computer-sciency definition in that they MUST return a value and cannot alter the data they receive as parameters (the arguments). Functions are not allowed to change anything, must have at least one parameter, and they must return a value. Stored procs do not have to have a parameter, can change database objects, and do not have to return a value.”

---

Constraints

NOT NULL and UNIQUE

Can have many UNIQUEs:

DROP TABLE IF EXISTS Tryout2;
CREATE TABLE Tryout2 (
    Pid         NUMERIC(5, 0),
    Cname       VARCHAR(20),
    Ppos        VARCHAR(8),
    Decision    VARCHAR(3),
                UNIQUE (Pid, Cname),
                UNIQUE (Pid, Ppos)
);
DESCRIBE Tryout2;

results in

mysql> DESCRIBE Tryout2;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| Pid      | decimal(5,0) | YES  | MUL | NULL    |       |
| Cname    | varchar(20)  | YES  |     | NULL    |       |
| Ppos     | varchar(8)   | YES  |     | NULL    |       |
| Decision | varchar(3)   | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

which means a player can try out once at a college AND a player can try out only once for a specific position. The MUL means > multiple occurrences of a given value are allowed within the column. The column is the first column of a non-unique index or a unique-valued index that can contain NULL values.”

CHECK

Used to verify any range constraints when defining a table

mysql> DROP TABLE IF EXISTS Playerxyz;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE Playerxyz (
    ->     pID      NUMERIC(5, 0) NOT NULL,
    ->     pName    VARCHAR(20),
    ->     yCard    VARCHAR(3),
    ->     HS       NUMERIC(5, 0),
    ->     GPA      REAL CHECK (
    ->                      GPA <= 4.0
    ->                  AND GPA >= 0.0
    ->     )
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO Playerxyz
    -> VALUES (42, 'CCP', 'yes', 12030, 3.4);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO Playerxyz
    -> VALUES (42, 'XYP', 'yes', 14230, 4.4);
ERROR 4025 (23000): CONSTRAINT `Playerxyz.GPA` failed for `cs61`.`Playerxyz`
mysql>

Erroneous UPDATEs are caught by these constraints

• It may depend on the order in which a multiple tuple update is made whether there is an error or not.
• Suppose we had this table Application

-- this would work
mysql> CREATE TABLE Application (
            pID     int UNIQUE,
            pName   VARCHAR(20),
            HS      int,
            GPA     REAL CHECK (
                            GPA <= 4.0
                        AND GPA >= 0.0)
        );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO Application
       VALUES (123, "Alice", 2300, 3.5);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO Application
       VALUES (234, "Bob", 210, 3.2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO
    Application VALUES (345, "Carol", 1024, 3.4);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT *
       FROM Application;
+------+-------+------+------+
| pID  | pName | HS   | GPA  |
+------+-------+------+------+
|  123 | Alice | 2300 |  3.5 |
|  234 | Bob   |  210 |  3.2 |
|  345 | Carol | 1024 |  3.4 |
+------+-------+------+------+
3 rows in set (0.00 sec)

-- this would NOT work
mysql> UPDATE Application
          SET GPA = GPA + 0.5;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT *
       FROM Application;
+------+-------+------+------+
| pID  | pName | HS   | GPA  |
+------+-------+------+------+
|  123 | Alice | 2300 |    4 |
|  234 | Bob   |  210 |  3.7 |
|  345 | Carol | 1024 |  3.9 |
+------+-------+------+------+
3 rows in set (0.00 sec)

-- while this sequence might not (from Widom)
mysql> UPDATE Application
          SET pID = pID - 111;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT *
         FROM Application;
+------+-------+------+------+
| pID  | pName | HS   | GPA  |
+------+-------+------+------+
|   12 | Alice | 2300 |    4 |
|  123 | Bob   |  210 |  3.7 |
|  234 | Carol | 1024 |  3.9 |
+------+-------+------+------+
3 rows in set (0.00 sec)

mysql> UPDATE Application
          SET pID = pID + 111;
ERROR 1062 (23000): Duplicate entry '123' for key 'pID'
mysql>

PRIMARY KEYS

Declared with the attribute, or declared at the end

CREATE TABLE Part (
        pID       INT PRIMARY KEY,
        sID       INT,
        pName     VARCHAR(20),
        unitPrice REAL );

Declaring at the end enables multiple attribute PK’s

CREATE TABLE Part (
        pID         INT,
        sID         INT,
        pName       VARCHAR(20),
        unitPrice   REAL,
                    PRIMARY KEY (pID, sID) );

And this is a preferred practice so you only need to look at one place for PRIMARY key definitions.

Tuple based constraints (not in MySQL prior to 10.2)

CREATE TABLE Tryout2 (
        pID      NUMERIC (5,0) NOT NULL,
        cName    VARCHAR(20),
        pPos     VARCHAR(3),
        decision VARCHAR(6),
                 CHECK (
                       pPos = 'goalie'
                    OR pPos = 'mid'
                    OR pPos = 'mid'
                 )
        );

• Here’s a neat trick to ensure that the pID entered with a new Tryout entry is a valid student pID.

CREATE TABLE Tryout (pID numeric (5,0) NOT NULL, ... ,
                     CHECK (pID IN
                        ( SELECT pID FROM Student) ) );

---

Referential Integrity

Referential Integrity means that when one tuple in a relation refers to corresponding tuple in another relation , that corresponding tuple will exist.

Deleting a Player that still has entries in the Tryout table.

• Default behavior is RESTRICT but there are two other options
• SET NULL means if the Player is deleted then the references to it are set to NULL
• CASCADE means if the Player is deleted then cascade that delete to delete the tuples that referenced it.

Updating a player pID

• RESTRICT disallows it
• SET NULL sets the referencing tables pID to NULL
• CASCADE sets the referencing tables pID to THE NEW VALUE

Examples using our usual soccer.sql file

• Can’t create a Tryout tuple before creating the Player and College tuples
• Try updating a valid Tryout to a new pID (Error) then to an existing pID (ok)

DROP TABLE IF EXISTS Tryout2;
CREATE TABLE Tryout2 (
        pID      INT REFERENCES Player(pid) ON DELETE SET NULL,
        cName    VARCHAR(20) REFERENCES College(cName) ON UPDATE CASCADE,
        pPos     VARCHAR(8),
        decision VARCHAR(3));

INSERT INTO Tryout2 VALUES (30003, 'LSU', 'mid', 'yes');
INSERT INTO Tryout2 VALUES (50005, 'USC', 'goalie', 'no');
INSERT INTO Tryout2 VALUES (20002, 'ASU', 'strike', 'yes');

SELECT * FROM Tryout2;
+-------+-------+--------+----------+
| pID   | cName | pPos   | decision |
+-------+-------+--------+----------+
| 30003 | LSU   | mid    | yes      |
| 50005 | USC   | goalie | no       |
| 20002 | ASU   | strike | yes      |
+-------+-------+--------+----------+
3 rows in set (0.00 sec)

SELECT * FROM Player;
+-------+---------+-------+------+
| pID   | pName   | yCard | HS   |
+-------+---------+-------+------+
| 10001 | Andrew  | no    | 1200 |
| 20002 | Blake   | no    | 1600 |
| 30003 | Charles | no    |  600 |
| 40004 | David   | yes   | 1600 |
| 40004 | David   | yes   | 1600 |
| 50005 | Eddie   | yes   | 2300 |
+-------+---------+-------+------+
6 rows in set (0.00 sec)

SELECT * FROM College;
+-------+-------+-------+
| cName | state | enr   |
+-------+-------+-------+
| ASU   | AZ    | 12000 |
| LSU   | LA    | 18000 |
| OU    | OK    | 22000 |
| USC   | CA    | 19000 |
+-------+-------+-------+
4 rows in set (0.00 sec)

• DELETE a referenced pID from the Player table and the SET NULL should happen.
• UPDATE a cName in the College table and it should be cascaded.

---

Example from Murach (2nd ed.) of a complex query using subqueries

-- use ap;
SELECT
  t1.vendor_state,
  vendor_name,
  t1.sum_of_invoices
FROM
  (
      -- invoice totals by vendor
      SELECT
          vendor_state,
          vendor_name,
          SUM(invoice_total) AS sum_of_invoices
      FROM
          vendors v
          JOIN invoices i ON v.vendor_id = i.vendor_id
      GROUP BY
          vendor_state,
          vendor_name
  ) t1
  JOIN (
      -- top invoice totals by state
      SELECT
          vendor_state,
          MAX(sum_of_invoices) AS sum_of_invoices
      FROM
          (
              -- invoice totals by vendor
              SELECT
                  vendor_state,
                  vendor_name,
                  SUM(invoice_total) AS sum_of_invoices
              FROM
                  vendors v
                  JOIN invoices i ON v.vendor_id = i.vendor_id
              GROUP BY
                  vendor_state,
                  vendor_name
          ) t2
      GROUP BY
          vendor_state
  ) t3 ON t1.vendor_state = t3.vendor_state
  AND t1.sum_of_invoices = t3.sum_of_invoices
ORDER BY
  vendor_state;

“This query retrieves the vendor from each state that has the largest invoice total.” - Murach 2nd.ed.

Note the comments among the subqueries … good idea!

Here is the result of the EXPLAIN command on the above query:

+------+-------------+------------+--------+---------------------+---------+---------+------------------------------------+------+---------------------------------+
| id   | select_type | table      | type   | possible_keys       | key     | key_len | ref                                | rows | Extra                           |
+------+-------------+------------+--------+---------------------+---------+---------+------------------------------------+------+---------------------------------+
|    1 | PRIMARY     | <derived2> | ALL    | NULL                | NULL    | NULL    | NULL                               |  114 | Using where; Using filesort     |
|    1 | PRIMARY     | <derived3> | ref    | key0                | key0    | 17      | t1.vendor_state,t1.sum_of_invoices |   10 |                                 |
|    3 | DERIVED     | <derived4> | ALL    | NULL                | NULL    | NULL    | NULL                               |  114 | Using temporary; Using filesort |
|    4 | DERIVED     | i          | ALL    | invoices_fk_vendors | NULL    | NULL    | NULL                               |  114 | Using temporary; Using filesort |
|    4 | DERIVED     | v          | eq_ref | PRIMARY             | PRIMARY | 4       | ap.i.vendor_id                     |    1 |                                 |
|    2 | DERIVED     | i          | ALL    | invoices_fk_vendors | NULL    | NULL    | NULL                               |  114 | Using temporary; Using filesort |
|    2 | DERIVED     | v          | eq_ref | PRIMARY             | PRIMARY | 4       | ap.i.vendor_id                     |    1 |                                 |
+------+-------------+------------+--------+---------------------+---------+---------+------------------------------------+------+---------------------------------+
7 rows in set (0.03 sec)