Advanced SQL
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 tuplesINNER 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 pID
s 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 newTryout
entry is a valid studentpID
.
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 NULLCASCADE
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 NULLCASCADE
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)