SQL
, but none of its implementation.This chapter will be “code-driven”: the code will illustrate and help you understand some concepts. You may want to have a look at the “Setting Up Your Work Environment” Section, as early as possible in this lecture. On top of being a step-by-step guide to install and configure a relational database managment system, it contains a list of useful links.
“Common”/Relational | SQL |
---|---|
“Set of databases” | Catalog (named collection of schema) |
“Database” | Schema |
Relation | Table |
Tuple | Row |
Attribute | Column, or Field |
A schema is made of
Type and domains are two different things in some implementations, cf. for instance PostgreSQL, where a domain is defined to be essentially a datatype with constraint.
SQL
is a programming language: it has a strict syntax, sometimes cryptic error messages, it evolves, etc. Some of its salient aspects are:
SQL
is “kind of” case-insensitive, does not care about spaces and new lines--
, multi-line comments uses /* …*/
.The following is an adaptation of W3Resource, the canonical source being MySQL’s documentation:
INTEGER
(or its short-hand notation INT
) or SMALLINT
.FLOAT
and DOUBLE
(or its synonym, REAL
). MySQL also allows the syntax FLOAT(M,D)
or REAL(M,D)
, where the values can be stored up to M
digits in total where D
represents the decimal point.DECIMAL(10, 2)
(or its synonym in MySQL NUMERIC
).CHAR
and VARCHAR
: the length (resp. maximal length) of the CHAR
(resp. VARCHAR
) has to be declared, and CHAR
are right-padded with spaces to the specified length. Historically, 255 was the size used, because it is the largest number of characters that can be counted with an 8-bit number, but, whenever possible, the “right size” should be used.BIT(1)
, and a boolean using BOOLEAN
.DATE
, TIME
, DATETIME
and TIMESTAMP
.There are many other datatypes, but they really depends on the particular implementation, so we will not consider them too much.
/* code/sql/HW_Faculty.sql */ -- We first drop the schema if it already exists: DROP SCHEMA IF EXISTS HW_Faculty; -- Then we create the schema: CREATE SCHEMA HW_Faculty; /* Or we could have use the syntax: CREATE DATABASE HW_FACULTY; */ -- Now, let us create a table in it: CREATE TABLE HW_Faculty.PROF ( Fname VARCHAR(15), /* No String! The value "15" vas picked randomly, any value below 255 would more or less do the same. Note that declaring extremely large values without using them can impact the performance of your database, cf. for instance https://dba.stackexchange.com/a/162117/ */ Room INT, /* shorthand for INTEGER, are also available: SMALLINT, FLOAT, REAL, DEC The "REAL" datatype is like the "DOUBLE" datatype of C# (they are actually synonyms in SQL): more precise than the "FLOAT" datatype, but not as exact as the "NUMERIC" datatype. cf. https://dev.mysql.com/doc/refman/8.0/en/numeric-types.html */ Title CHAR(3), -- fixed-length string, padded with blanks if needed Tenured BIT(1), Nice BOOLEAN, -- True / False (= 0) / Unknown Hiring DATE, /* The DATE is always supposed to be entered in a YEAR/MONTH/DAY variation. To tune the way it will be displayed, you can use the "DATE_FORMAT" function (cf. https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format), but you can enter those values only using the "standard" literals (cf. https://dev.mysql.com/doc/refman/8.0/en/date-and-time-literals.html ) */ Last_seen TIME, FavoriteFruit ENUM ('apple', 'orange', 'pear'), PRIMARY KEY (Fname, Hiring) ); /* Or, instead of using the fully qualified name HW_Faculty.PROF, we could have done: USE HW_Faculty; CREATE TABLE PROF(…) */ -- Let us use this schema, from now on. USE HW_Faculty; -- Let us insert some "Dummy" value in our table: INSERT INTO PROF VALUES ("Clément", -- Or 'Clément'. 290, 'PhD', 0, NULL, '19940101', -- Or '940101', '1994-01-01', '94/01/01' '090500', -- Or '09:05:00', '9:05:0', '9:5:0', '090500' -- Note also the existence of DATETIME, with 'YYYY-MM-DD
-- HH:MM:SS' 'Apple' -- This is not case-sensitive, oddly enough. );
The following commands are particularly useful. They allow you to get a sense of the current state of your databases.
In the following, <SchemaName>
should be substituted with an actual schema name.
SHOW SCHEMAS; -- List the schemas. SHOW TABLES; -- List the tables in a schema. DROP SCHEMA <SchemaName>; -- "Drop" (erase) SchemaName.
You can also use the variation
DROP SCHEMA IF EXISTS <SchemaName>;
that will not issue an error if <SchemaName>
does not exist.
In the following, <TableName>
should be substituted with an actual table name.
SHOW CREATE TABLE <TableName>-- Gives the command to "re-construct" TableName. DESCRIBE <TableName>; -- Show the structure of TableName. DROP TABLE <TableName>; -- "Drop" (erase) TableName.
You can also use the variation
DROP TABLE IF EXISTS <TableName>;
that will not issue an error if <TableName>
does not exist.
SELECT * FROM <TableName> -- List all the rows in TableName. SHOW WARNINGS; -- Show the content of the latest warning issued.
There are six different kind of constraints that one can add to an attribute:
NOT NULL
UNIQUE
DEFAULT
CHECK
We already know the first two from the relational model. The other four are new, and could not be described in this model.
We will review them below, and show how they can be specified at the time the table is declared, or added and removed later. For more in-depth examples, you can refer to W3Resource.
We will now see how to declare those constraints when we create the table (except for the foreign key, which we save for later).
/* code/sql/HW_ConstraintsPart1.sql */
DROP SCHEMA IF EXISTS HW_ConstraintsPart1; CREATE SCHEMA HW_ConstraintsPart1; USE HW_ConstraintsPart1; CREATE TABLE HURRICANE ( NAME VARCHAR(25) PRIMARY KEY, WindSpeed INT DEFAULT 76 CHECK (WindSpeed > 74 AND WindSpeed < 500), -- 75mph is the minimum to be considered as a hurricane -- cf. https://www.hwn.org/resources/bws.html Above VARCHAR(25) ); CREATE TABLE STATE ( NAME VARCHAR(25) UNIQUE, Postal_abbr CHAR(2) NOT NULL );
If we wanted to combine multiple constraints, we would have to follow the order described at MySQL.
MySQL
can output a description of those tables for us:
MariaDB [HW_ConstraintsPart1]> DESCRIBE HURRICANE; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | Name | varchar(25) | NO | PRI | NULL | | | WindSpeed | int(11) | YES | | 76 | | | Above | varchar(25) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) MariaDB [HW_ConstraintsPart1]> DESCRIBE STATE; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | Name | varchar(25) | NO | PRI | NULL | | | Postal_abbr | char(2) | NO | UNI | NULL | | +-------------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
Note that more than one attribute can be the primary key, in which case the syntax needs to be something like the following:
/* code/sql/HW_PKtest.sql */ DROP SCHEMA IF EXISTS HW_PKtest; CREATE SCHEMA HW_PKtest; USE HW_PKtest; CREATE TABLE TEST ( A INT, B INT, PRIMARY KEY (A, B) );
Note that in this case, a statement like
INSERT INTO TEST VALUE (1, NULL);
would result in an error: all the values that are part of the primary key needs to be non-NULL
.
For the UNIQUE
constraint, note that NULL
can be inserted: the rationale is that all the values need to be different from one another or NULL
.
A couple of comments about the CHECK
constraint:
WindSpeed INT CHECK (WindSpeed > 74 AND WindSpeed < 500)
would have been parsed but would not have any effect, cf. MariaDB. Since MariaDB 10.2.1, the CHECK
constraint are enforced.CHECK
constraint, with a command like
then the insertion would not take place, and the system would issue an error message:INSERT INTO HURRICANE VALUES ("Test1", 12, NULL);
Note that you could still insert a value of NULL for the wind, and it would not triggered the error.ERROR 4025 (23000): CONSTRAINT `HURRICANE.WindSpeed` failed for `HW_ConstraintsPart1]>`.`HURRICANE`
To use the DEFAULT
value, use
INSERT INTO HURRICANE VALUES ("Test2", DEFAULT, NULL);
Note that, by default, the DEFAULT
value is NULL
, regardless of the datatype:
/* code/sql/HW_DefaultTest.sql */
DROP SCHEMA IF EXISTS HW_DEFAULT_test; CREATE SCHEMA HW_DEFAULT_test; USE HW_DEFAULT_test; CREATE TABLE TEST ( TestA VARCHAR(15), TestB INT, TestC FLOAT, TestD BOOLEAN, TestE BIT(1), TestF DATE ); INSERT INTO TEST VALUES (DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT); SELECT * FROM TEST;
Let us know pretend that we want to edit some attributes, by either adding or removing constraints. SQL
’s syntax is a bit inconsistent on this topic, because it treats the constraints as being of different natures.
Adding a primary key:
ALTER TABLE STATE ADD PRIMARY KEY (Name);
Removing the primary key:
ALTER TABLE STATE DROP PRIMARY KEY;
UNIQUE
ConstraintAdding a UNIQUE
constraint:
ALTER TABLE STATE ADD UNIQUE (Postal_abbr);
Removing a UNIQUE
constraint:
ALTER TABLE STATE DROP INDEX Postal_abbr;
Note the difference between adding and removing the UNIQUE
constraint: the parenthesis around (Postal_abbr)
are mandatory when adding the constraint, but would cause an error when removing it!
NOT NULL
ConstraintAdding the NOT NULL
constraint:
ALTER TABLE STATE MODIFY Postal_abbr CHAR(2) NOT NULL;
Removing the NOT NULL
constraint:
ALTER TABLE STATE MODIFY Postal_abbr CHAR(2);
The syntax of NOT NULL
comes from the fact that this constraint is taken to be part of the datatype.
Changing the default value:
ALTER TABLE HURRICANE ALTER COLUMN WindSpeed SET DEFAULT 74;
Removing the default value:
ALTER TABLE HURRICANE ALTER COLUMN WindSpeed DROP DEFAULT;
Adding a foreign key constraint:
ALTER TABLE HURRICANE ADD FOREIGN KEY (Above) REFERENCES STATE(Name);
Removing a foreign key constraint is out of the scope of this lecture. If you are curious, you can have a look at W3Schools: dropping a foreign key constraint requires your constraint to have a name, something we did not introduce.
Two important remarks:
Refer to Problem 3.4 (Constraints on foreign keys) for a slightly more accurate picture of the constraints related to the creation of foreign keys. Note that a foreign key could be declared at the time of creation of the table as well, using the syntax we will introduce below.
Let us test our constraints:
INSERT INTO STATE VALUES('Georgia', 'GA'); INSERT INTO STATE VALUES('Texas', 'TX'); INSERT INTO STATE VALUES('FLORIDA', 'FL'); UPDATE STATE SET Name = 'Florida' WHERE Postal_abbr = 'FL'; -- There's an error with the following request. Why? INSERT INTO HURRICANE VALUES('Irma', 150, 'FL'); /* ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`HW_ConstraintsPart1`.`HURRICANE`, CONSTRAINT `HURRICANE_ibfk_1` FOREIGN KEY (`Above`) REFERENCES `STATE` (`Name`)) */ INSERT INTO HURRICANE VALUES('Harvey', DEFAULT, 'Texas'); INSERT INTO HURRICANE VALUES('Irma', 150, 'Florida'); DELETE FROM HURRICANE WHERE Name = 'Irma'; INSERT INTO HURRICANE VALUES('Irma', 150, 'Georgia'); UPDATE HURRICANE SET Above = 'Georgia' WHERE Name = 'Irma'; /* MariaDB [HW_ConstraintsPart1]> SELECT * FROM HURRICANE; +--------+-----------+---------+ | Name | WindSpeed | Above | +--------+-----------+---------+ | Harvey | 74 | Texas | | Irma | 150 | Georgia | +--------+-----------+---------+ */ -- There's an error with the following request. Why? UPDATE HURRICANE SET Above = 'North Carolina' WHERE Name = 'Irma'; -- Let's patch it, by adding North Carolina to our STATE table. INSERT INTO STATE VALUES('North Carolina', 'NC'); UPDATE HURRICANE SET Above = 'North Carolina' WHERE Name = 'Irma';
Let us come back more specifically to foreign key.
In the example below, we introduce the foreign key update and delete rules. We also introduce, passing by, the enumerated data type, and how to edit it.
CREATE TABLE STORM ( NAME VARCHAR(25) PRIMARY KEY, Kind ENUM ('Tropical Storm', 'Hurricane'), WindSpeed INT, Creation DATE ); -- We can change the enumerated datatype: ALTER TABLE STORM MODIFY Kind ENUM ('Tropical Storm', 'Hurricane', 'Typhoon'); CREATE TABLE STATE ( NAME VARCHAR(25) UNIQUE, Postal_abbr CHAR(2) PRIMARY KEY, Affected_by VARCHAR(25), FOREIGN KEY (Affected_by) REFERENCES STORM (NAME) ON DELETE SET NULL ON UPDATE CASCADE );
Note that we can “inline” the foreign key constraint like we “inlined” the primary key constraint (cf. Stack Overflow), but that it will not be enforced!
Let us now illustrate this table by introducing some data in it:
INSERT INTO STORM VALUES ('Harvey', 'Hurricane', 130, '2017-08-17'); -- In the following, the entry gets created, but date is -- "corrected" to '2017-17-08'! -- INSERT INTO STORM -- VALUES ('Dummy', 'Hurricane', 120, '2017-17-08'); -- The error message returned is -- ERROR 1292 (22007) at line 34: Incorrect date value: -- '2017-17-08' for column `HW_STORM`.`STORM`.`Creation` -- at row 1 -- In the following, we explicitely use 'DATE', and since -- the date is incorrect, nothing gets inserted. -- INSERT INTO STORM -- VALUES ('Dummy2', 'Hurricane', 120, DATE -- '2017-17-08'); -- ERROR 1525 (HY000) at line 40: Incorrect DATE value: -- '2017-17-08' -- The next one sets NULL for DATE. INSERT INTO STORM VALUES ('Irma', 'Tropical Storm', 102, DEFAULT);
MySQL will always notify you if there is an error in a date attribute when you use the DATE
prefix.
INSERT INTO STATE VALUES ('Georgia', 'GA', NULL); INSERT INTO STATE VALUES ('Texas', 'TX', NULL); INSERT INTO STATE VALUES ('Florida', 'FL', NULL); -- This instruction is not using the primary key, is that a -- problem? UPDATE STATE SET Affected_by = 'Harvey' WHERE Name = 'Georgia'; UPDATE STORM SET Name = 'Harley' WHERE Name = 'Harvey'; DELETE FROM STORM WHERE Name = 'Harley';
We will see in the “Reverse-Engineering” section why this schema is poorly designed, but for now, let’s focus on the foreign keys and their restrictions.
The following is a code-driven explanation of the foreign key update and delete rules (or “restrictions”). It is intended to make you understand the default behavior of foreig keys, and to understand how the system reacts to the possible restrictions.
CREATE TABLE F_Key( Attribute VARCHAR(25) PRIMARY KEY ); CREATE TABLE Table_default( Attribute1 VARCHAR(25) PRIMARY KEY, Attribute2 VARCHAR(25), FOREIGN KEY (Attribute2) REFERENCES F_Key(Attribute)
); -- By default, this foreign key will restrict. CREATE TABLE Table_restrict( Attribute1 VARCHAR(25) PRIMARY KEY, Attribute2 VARCHAR(25), FOREIGN KEY (Attribute2) REFERENCES F_Key(Attribute) ON DELETE RESTRICT ON UPDATE RESTRICT ); CREATE TABLE Table_cascade( Attribute1 VARCHAR(25) PRIMARY KEY, Attribute2 VARCHAR(25), FOREIGN KEY (Attribute2) REFERENCES F_Key(Attribute) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE Table_set_null( Attribute1 VARCHAR(25) PRIMARY KEY, Attribute2 VARCHAR(25), FOREIGN KEY (Attribute2) REFERENCES F_Key(Attribute) ON DELETE SET NULL ON UPDATE SET NULL ); /* * You might encounter a * ON UPDATE SET DEFAULT * but this reference option (cf. https://mariadb.com/kb/en/library/foreign-keys/ ) * worked only with a particular engine ( https://mariadb.com/kb/en/library/about-pbxt/ ) * and will not be treated here. */ INSERT INTO F_Key VALUES('First Test'); INSERT INTO Table_default VALUES('Default', 'First Test'); INSERT INTO Table_restrict VALUES('Restrict', 'First Test'); INSERT INTO Table_cascade VALUES('Cascade', 'First Test'); INSERT INTO Table_set_null VALUES('Set null', 'First Test'); SELECT * FROM Table_default; SELECT * FROM Table_restrict; SELECT * FROM Table_cascade; SELECT * FROM Table_set_null; -- The following will fail because of the Table_default table: UPDATE F_Key SET Attribute = 'After Update' WHERE Attribute = 'First Test'; DELETE FROM F_Key WHERE Attribute = 'First Test'; -- Let us drop this table, and try again. DROP TABLE Table_default; -- The following fails too, this time because of the Table_restrict table: UPDATE F_Key SET Attribute = 'After Update' WHERE Attribute = 'First Test'; DELETE FROM F_Key WHERE Attribute = 'First Test'; -- Let us drop this table, and try again. DROP TABLE Table_restrict; -- Let's try again: UPDATE F_Key SET Attribute = 'After Update' WHERE Attribute = 'First Test'; -- And let's print the situation after this update: SELECT * FROM Table_cascade; SELECT * FROM Table_set_null; /* MariaDB [HW_CONSTRAINTS_PART3]> SELECT * FROM Table_cascade; +------------+--------------+ | Attribute1 | Attribute2 | +------------+--------------+ | Cascade | After Update | +------------+--------------+ 1 row in set (0.00 sec) MariaDB [HW_CONSTRAINTS_PART3]> SELECT * FROM Table_set_null; +------------+------------+ | Attribute1 | Attribute2 | +------------+------------+ | Set null | NULL | +------------+------------+ 1 row in set (0.00 sec) */ -- Let's make a second test. INSERT INTO F_Key VALUES('Second Test'); INSERT INTO Table_cascade VALUES('Default', 'Second Test'); INSERT INTO Table_set_null VALUES('Restrict', 'Second Test'); DELETE FROM F_Key WHERE Attribute = 'Second Test'; /* MariaDB [HW_CONSTRAINTS_PART3]> SELECT * FROM Table_cascade; +------------+--------------+ | Attribute1 | Attribute2 | +------------+--------------+ | Cascade | After Update | +------------+--------------+ 1 row in set (0.00 sec) MariaDB [HW_CONSTRAINTS_PART3]> SELECT * FROM Table_set_null; +------------+------------+ | Attribute1 | Attribute2 | +------------+------------+ | Restrict | NULL | | Set null | NULL | +------------+------------+ 2 rows in set (0.00 sec) */
/* code/sql/HW_ProfExample.sql */ CREATE TABLE PROF ( Login VARCHAR(25) PRIMARY KEY, NAME VARCHAR(25), Department CHAR(5) ); CREATE TABLE DEPARTMENT ( Code CHAR(5) PRIMARY KEY, NAME VARCHAR(25), Head VARCHAR(25), FOREIGN KEY (Head) REFERENCES PROF (LOGIN) ON UPDATE CASCADE ); ALTER TABLE PROF ADD FOREIGN KEY (Department) REFERENCES DEPARTMENT (Code);
Note the structure of the ALTER TABLE
command:
KEY
Department REFERENCES
Code;⇒ errorKEY
(Department) REFERENCES
(Code);⇒ errorKEY
PROF(Department) REFERENCES
DEPARTMENT(Code); ⇒ okCREATE TABLE STUDENT ( Login VARCHAR(25) PRIMARY KEY, NAME VARCHAR(25), Registered DATE, Major CHAR(5), FOREIGN KEY (Major) REFERENCES DEPARTMENT (Code)
); CREATE TABLE GRADE ( Login VARCHAR(25), Grade INT, PRIMARY KEY (LOGIN, Grade), FOREIGN KEY (LOGIN) REFERENCES STUDENT (LOGIN) );
We can insert multiple values at once:
INSERT INTO DEPARTMENT VALUES ('MATH', 'Mathematics', NULL), ('CS', 'Computer Science', NULL);
We can specify which attributes we are giving:
INSERT INTO DEPARTMENT (Code, Name) VALUES ('CYBR', 'Cyber Secturity');
And we can even specify the order (even the trivial one):
INSERT INTO PROF (LOGIN, Department, Name) VALUES ('caubert', 'CS', 'Clément Aubert'); INSERT INTO PROF (LOGIN, Name, Department) VALUES ('aturing', 'Alan Turing', 'CS'), ('perdos', 'Paul Erdős', 'MATH'), ('bgates', 'Bill Gates', 'CYBR'); INSERT INTO STUDENT (LOGIN, Name, Registered, Major) VALUES ('jrakesh', 'Jalal Rakesh', DATE '2017-12-01', 'CS'), ('svlatka', 'Sacnite Vlatka', '2015-03-12', 'MATH'), ('cjoella', 'Candice Joella', '20120212', 'CYBR'), ('aalyx', 'Ava Alyx', 20121011, 'CYBR'), ('caubert', 'Clément Aubert', NULL, 'CYBR'); INSERT INTO GRADE VALUES ('jrakesh', 3.8), ('svlatka', 2.5);
Note the date literals.
Note that we can create foreign keys to primary keys made of multiple attributes, and to the primary key of the table we are currently creating.
/* code/sql/HW_AdvancedFK.sql */ CREATE TABLE T1(A1 INT, A2 INT, B INT, PRIMARY KEY(A1, A2) ); CREATE TABLE T2(A1 INT, A2 INT, B1 INT PRIMARY KEY, B2 INT, -- We can create a "pair" of foreign key in one line, as follows: FOREIGN KEY (A1,A2) REFERENCES T1(A1,A2), -- We can create a foreign key that references the primary key of the table we are currently creating, and name it, as follows: CONSTRAINT My_pk_to_T1 FOREIGN KEY (B2) REFERENCES T2(B1) ); -- The benefit of naming our fk constraint is that, if we violate it, for instance with -- INSERT INTO T2 VALUES (1, 1, 1, 3); -- then the name of the constraint (here "My_pk_to_T1") would be displayed in the error message: -- Cannot add or update a child row: a foreign key constraint fails (`db_9_9837c1`.`t2`, CONSTRAINT `My_pk_to_T1` FOREIGN KEY (`B2`) REFERENCES `t2` (`B1`))
Order of clauses does not matter, not even for optimization purpose.
UPDATE <table> SET <attribute1> = <value1>, <attribute2> = <value2>, … WHERE <condition>; SELECT <attribute list, called projection attributes> FROM <table list> WHERE <condition>; DELETE FROM <table list> WHERE <condition>;
Conditions can
=
, equal to>
, greater than,<
less than,>=
greater than or equal to,<=
less than or equal to,<>
not equal to.AND
condition2OR
condition2NOT
conditionLIKE
,\
,_
will match one character (any character), %
will match any number of character,REGEXP
keyword.SELECT LOGIN FROM STUDENT; UPDATE DEPARTMENT SET Head = "aturing" WHERE Code = "MATH"; UPDATE DEPARTMENT SET Head = "bgates" WHERE Code = "CS" OR Code = "CYBR"; SELECT LOGIN FROM STUDENT WHERE NOT Major = "CYBR"; SELECT LOGIN, Name FROM PROF WHERE Department = "CS"; SELECT LOGIN FROM STUDENT WHERE Major = "CYBR" AND Registered > DATE "20121001"; SELECT LOGIN FROM STUDENT WHERE Name LIKE "Ava%"; SELECT Name FROM PROF WHERE LOGIN LIKE "_aubert";
Note that LIKE
is by default case-insensitive, both in MariaDB and in MySQL. The COLLATE operator can be used to force the search to be case-sensitive, as well as LIKE
BINARY
.
Cf. (Elmasri and Navathe 2010, 5.1.1), (Elmasri and Navathe 2015, 7.1.1)
NULL
NULL
is
What is the date of birth of Jack the Ripper?
Does P equal NP?
What is the number of english spies in France?
What is the VIN of your car?
What is the identity of the Tiananmen Square person?
What is the US SSN of a french person?
What is the email address of an author of the XIXth century?
If NULL
is involved in a comparison, the result evaluates to “Unknown.” If NULL
is involved in a comparison, the result evaluates to “Unknown”.
AND | T | F | U |
---|---|---|---|
T | T | F | U |
F | F | F | F |
U | U | F | U |
OR | T | F | U |
---|---|---|---|
T | T | T | T |
F | T | F | U |
U | T | U | U |
NOT | |
---|---|
T | F |
F | T |
U | U |
You can test if a value is NULL
with IS NULL
.
INSERT INTO DEPARTMENT VALUES ("Hist", "History", NULL); SELECT * FROM DEPARTMENT WHERE Head IS NULL; SELECT * FROM DEPARTMENT WHERE Head IS NOT NULL; SELECT COUNT(*) FROM GRADE WHERE Grade IS NULL;
Note that you can not use IS
to compare values: this key word is reserved to test if a value is (not) NULL
, and nothing else.
There are no if…then…else
statements in SQL
, but you can do something similar with CASE
(cf. MySQL). However, note that SQL
is probably not the right place to try to control the flow of execution.
This probably depends on the system a lot, but one could wonder if MySQL uses some form of short-cut evaluation when comparing with NULL
. Unfortunately, even with three times (!) the verbose option, MySQL does not give more insight as to whenever it drops comparing values once a NULL was encountered (cf. MySQL, you can log-in using mysql -u testuser -p --password=password -v -v -v
to activate the most verbose mode). Normally, EXPLAIN
(MySQL) should be useful in answering this question, but failed to answer it as well.
For DISTINCT
, ALL
and UNION
, cf. (Elmasri and Navathe 2010, 4.3.4) or (Elmasri and Navathe 2015, 6.3.4). For ORDER BY
, cf. (Elmasri and Navathe 2010, 4.3.6) or (Elmasri and Navathe 2015, 6.3.6). For aggregate functions, cf. (Elmasri and Navathe 2010, 5.1.7) or (Elmasri and Navathe 2015, 7.1.7).
AUTO_INCREMENT
Something that is not exactly a constraint, but that can be used to “qualify” domains, is the AUTO_INCREMENT
feature of MySQL. Cf. MySQL, you can have MySQL increment a particular attribute (most probably intended to be your primary key) for you.
We can save the current state, and start a series of transactions, with the command
START TRANSACTION;
All the commands that follows are “virtually” executed: you can undo them all using
ROLLBACK;
This puts you back in the state you were in before starting the transaction. If you want all the commands you typed in-between to be actually enforced, you can use the command
COMMIT;
Nested transactions are technically possible, but they are counter-intuitive and should be avoided, cf. SQL Skills.
DISTINCT
/ ALL
The result of a SELECT
query, for instance, is a table, and SQL
treats tables as multi-set, hence there can be repetitions in the result of a query, but we can remove them:
SELECT DISTINCT Major FROM STUDENT;
The default behaviour is equivalent to specifying ALL, and it display the duplicates. In this case, it would be
> SELECT Major FROM STUDENT; +-------+ | Major | +-------+ | CS | | CYBR | | CYBR | | CYBR | | MATH | +-------+
UNION
Set-theoretic operations are available as well. For instance, one can use:
(SELECT Login FROM STUDENT) UNION (SELECT Login FROM PROF);
to collect all the logins from both tables.
There is also INTERSECT
and EXCEPT
in the specification, but MySQL does not implement them (cf. Wikipedia).
ORDER BY
You can have ORDER BY
specifications:
SELECT LOGIN FROM GRADE WHERE Grade > 2.0 ORDER BY Grade; SELECT LOGIN FROM GRADE WHERE Grade > 2.0 ORDER BY Grade DESC; SELECT LOGIN, Major FROM STUDENT ORDER BY Major, Name;
ORDER BY
order by ascending order by default.
You can use MAX
, SUM
, MIN
, AVG
, COUNT
:
SELECT MAX(Registered) FROM STUDENT;
returns the “greatest” date of registration of a student, i.e., the date of the latest registration.
SELECT COUNT(Name) FROM STUDENT;
returns the number of names, i.e., the number of students.
SELECT COUNT(DISTINCT Name) FROM STUDENT;
returns the number of different names (which in this case is the same as the number of names, since we have no homonyms).
Note that AVG
returns the average of all non-NULL
values, as we can see on the following example:
/* code/sql/HW_Avg.sql */ CREATE TABLE TEST ( Test INT ); INSERT INTO TEST VALUES (NULL), (0), (10); SELECT AVG(Test) FROM TEST; -- Returns 5.0
We can use aliases for the columns. Compare
SELECT Login FROM PROF; +---------+ | Login | +---------+ | aturing | | caubert | | bgates | | perdos | +---------+
with
SELECT Login AS "Username" FROM PROF; +----------+ | Username | +----------+ | aturing | | caubert | | bgates | | perdos | +----------+
Aliases can also be used on table names. Aliases for columns are a helpful way of describing the result of the query, while alias on table have a specific purpose that will be clearer as we study select-project-join queries.
For select-project-join, cf. (Elmasri and Navathe 2010, 4.3.1) or (Elmasri and Navathe 2015, 6.3.1). For aliases, cf. (Elmasri and Navathe 2010, 4.3.2) or (Elmasri and Navathe 2015, 6.3.2), For nested queries, cf. (Elmasri and Navathe 2010, 5.1.2) or (Elmasri and Navathe 2015, 7.1.2).
SELECT LOGIN FROM PROF, DEPARTMENT WHERE DEPARTMENT.Name = "Mathematics" AND Department = Code;
Department.Name = 'Mathematics'
is the selection conditionDepartment = Code
is the join condition, because it combines two tuples.Name
?SELECT Name FROM STUDENT, GRADE WHERE Grade > 3.0 AND STUDENT.Login = GRADE.Login;
Grade > 3.0
is the selection conditionSTUDENT.Login = GRADE.Login
is the join conditionWe can have two join conditions!
SELECT PROF.Name FROM PROF, DEPARTMENT, STUDENT WHERE STUDENT.Name = "Ava Alyx" AND STUDENT.Major = DEPARTMENT.Code AND DEPARTMENT.Head = PROF.Login;
Note that for the kind of join we are studying (called “inner joins”), the order does not matter.
In Problem 3.3 (Duplicate rows in SQL
), we saw that SQL
was treating tables as multi-sets, i.e., repetitions are allowed. This can lead to strange behaviour when performing Select-Project-Join queries. Consider the following example:
We can use aliases on tables to shorten the previous query:
SELECT PROF.Name FROM PROF, DEPARTMENT, STUDENT AS B WHERE B.Name = "Ava Alyx" AND B.Major = DEPARTMENT.Code AND DEPARTMENT.Head = PROF.Login;
We can use multiple aliases to make it even shorter (but less readable):
SELECT A.Name FROM PROF AS A, DEPARTMENT AS B, STUDENT AS C WHERE C.Name = "Ava Alyx" AND C.Major = B.Code AND B.Head = A.Login;
For those two, aliases are convenient, but not required to write the query. In some cases, we cannot do without aliases. For instance if we want to compare two rows in the same table:
SELECT Other.Login FROM GRADE AS Mine, GRADE AS Other WHERE Mine.Login = "aalyx" AND Mine.Grade < Other.Grade;
Generally, when you want to perform a join within the same table, then you have to “make two copies of the tables” and name them differently using aliases. Let us try to write a query that answers the question
What are the login of the professors that have the same department as the professor whose login is caubert?
We need a way of distinguising between the professors we are projecting on (the one whole login is caubert) and the one we are joining with (the ones that have the same department). This can be done using something like:
SELECT JOINT.Login FROM PROF AS PROJECT, PROF AS JOINT WHERE PROJECT.Login = "caubert" AND PROJECT.Department = JOINT.Department;
Note that we are “opening up two copies of the PROF tables”, and naming them differently (PROJECT
and JOINT
).
Another (improved) example of a similar query is
SELECT Fellow.Name AS "Fellow of Ava" FROM STUDENT AS Ava, STUDENT AS Fellow WHERE Ava.Name = "Ava Alyx" AND Fellow.Major = Ava.Major AND NOT Fellow.Login = Ava.Login;
A couple of remarks about this query:
"Fellow of Ava"
is another kind of aliasing, mentioned in a previous section.NOT Fellow.Login = Ava.Login
guarantees that we will not select Ava again, and exclude her from the results (Ava is not supposed to be a fellow of herself).NOT Fellow.Name = Me.Name;
instead of NOT Fellow.Login = Ava.Login
would prevent the homonym from occuring in the results.AND NOT Me = Fellow
by NOT Fellow.Login = Ava.Login
would not work: you have to compare attributes of the tuples, not the tuples.Let us look at a first example
SELECT LOGIN FROM GRADE WHERE Grade > ( SELECT AVG(Grade) FROM GRADE);
A nested query is made of an outer query (SELECT Login…
) and an inner query (SELECT AVG(Grade)…
). Note that the inner query does not terminate with a ;.
Logical operators such as ALL
or IN
can be used in nested queries. To learn more about those operators, refer to W3Schools.
An example could be
SELECT LOGIN FROM GRADE WHERE Grade >= ALL ( SELECT Grade FROM GRADE WHERE Grade IS NOT NULL);
Note that
>=
, and not >
, since no grade is strictly greater than itself.IS NOT NULL
is needed: otherwise, if one of the grade is NULL
, then the comparison would yelds “unknown”, and no grade would be greater than all of the others.MAX
:SELECT LOGIN FROM GRADE WHERE Grade >= ( SELECT MAX(Grade) FROM GRADE);
Answering the question
What are the logins of the professors belonging to a department that is the major of at least one student whose name ends with an “a”?
–that sounds like the what would ask a police officer in a whodunit– could be answer using
SELECT LOGIN FROM PROF WHERE DEPARTMENT IN ( SELECT Major FROM STUDENT WHERE LOGIN LIKE "%a");
For this query, we could not use =
, since more than one major could be returned.
Furthermore, nested query that uses =
can often be rewritten without being nested. For instance,
SELECT LOGIN FROM PROF WHERE DEPARTMENT = ( SELECT Major FROM STUDENT WHERE LOGIN = "cjoella");
becomes
SELECT PROF.Login FROM PROF, STUDENT WHERE DEPARTMENT = Major AND STUDENT.Login = "cjoella";
Conversly, you can sometimes write select-project-join as nested queries For instance,
SELECT Name FROM STUDENT, GRADE WHERE Grade > 3.0 AND STUDENT.Login = GRADE.Login;
becomes
SELECT Name FROM STUDENT WHERE LOGIN IN ( SELECT LOGIN FROM GRADE WHERE Grade > 3.0);
A “stored” procedure is a SQL
function statements that can take arguments and may be called from another part of your program. Stated differently, a procedure is a serie of statements stored in a schema, that can easily be executed repeatedly, cf. MySQL or MariaDB.
Imagine we have the following:
/* code/sql/HW_ProcedureExamples.sql */ CREATE TABLE STUDENT ( Login INT PRIMARY KEY, NAME VARCHAR(30), Major VARCHAR(30), Email VARCHAR(30) ); INSERT INTO STUDENT VALUES (123, "Test A", "CS", "a@a.edu"), (124, "Test B", "IT", "b@a.edu"), (125, "Test C", "CYBR", "c@a.edu");
SQL
is extremely literal: when it reads the delimiter ;, it must execute the command that was shared. But a procedure, being composed of commands, will contain the ; symbol. To “solve” this (weird) issue, and be able to define a procedure, we have to “temporarily alter the language”, using DELIMITER //
that makes the delimiter being // instead of ;.
The symbols$$
are often used too, and the documentation, at MySQL, reads:You can redefine the delimiter to a string other than // and the delimiter can consist of a single character or multiple characters. You should avoid the use of the backslash (The minus sign twice is also a poor choice, since it is used for commenting.\
) character because that is the escape character for MySQL.
In any case, we can then define and execute a simpe procedure called STUDENTLIST
as follows:
DELIMITER $$ CREATE PROCEDURE STUDENTLIST () BEGIN SELECT * FROM STUDENT; -- This ";" is not the end of the procedure definition! END; $$ -- This is the delimiter that marks the end of the procedure -- definition. DELIMITER ; -- Now, we want ";" to be the "natural" delimiter again. CALL STUDENTLIST (); -- Now, we want ";" to be the "natural" delimiter again. CALL STUDENTLIST ();
A procedure an also take arguments, and an example could be:
DELIMITER $$ CREATE PROCEDURE STUDENTLOGIN ( NameP VARCHAR(30) ) BEGIN SELECT LOGIN FROM STUDENT WHERE NameP = Name; END; $$ DELIMITER ; SHOW CREATE PROCEDURE STUDENTLOGIN; -- This display information about the procedure just created. -- We can pass quite naturally an argument to our -- procedure. CALL STUDENTLOGIN ("Test A");
A trigger is a series of statements stored in a schema that can be automatically executed whenever a particular event in the schema occurs. Triggers are extremely powerfull, and are a way of automating part of the work in your database. In MariaDB, you could have the following program.
Imagine we have the following:
CREATE TABLE STUDENT ( Login VARCHAR(30) PRIMARY KEY, Average FLOAT ); CREATE TABLE GRADE ( Student VARCHAR(30), Exam VARCHAR(30), Grade INT, PRIMARY KEY (Student, Exam), FOREIGN KEY (Student) REFERENCES STUDENT (LOGIN) );
We want to create a trigger that counts the number of times something was inserted in our STUDENT
table. SQL
supports some primitive form of variables (cf. MySQL and MariaDB). There is no “clear” form of type, MySQL reads:
In addition, the default result type of a variable is based on its type at the beginning of the statement. This may have unintended effects if a variable holds a value of one type at the beginning of a statement in which it is also assigned a new value of a different type. To avoid problems with this behavior, either do not assign a value to and read the value of the same variable within a single statement, or else set the variable to 0, 0.0, or ’’ to define its type before you use it.
In other words, SQL
just “guess” the type of your value and go with it. Creating a simple trigger that increment a variable every time an insertion is performed in the STUDENT
table can be done as follows:
SET @number_of_student = 0; CREATE TRIGGER NUMBER_OF_STUDENT_INC AFTER INSERT ON STUDENT FOR EACH ROW SET @number_of_student = @number_of_student + 1;
Now, assume we want to create a trigger that calculates the average for us. Note that the trigger will need to manipulate two tables (STUDENT
and GRADE
) at the same time.
CREATE TRIGGER STUDENT_AVERAGE AFTER INSERT ON GRADE FOR EACH ROW -- Woh, a whole query inside our trigger! UPDATE STUDENT SET STUDENT.Average = ( SELECT AVG(Grade) FROM GRADE WHERE GRADE.Student = STUDENT.Login) WHERE STUDENT.Login = NEW.Student; -- The "NEW" keyword here refers to the "new" entry -- that is being inserted by the INSERT statement -- triggering the trigger.
The source code contains examples of insertion and explanations on how to witness the trigger in action.
This part is a short tutorial to install and configure a working relational DBMS. We will proceed in 5 steps:
You will install the MySQL DataBase Managment System, or its community-developed fork, MariaDB. Below are the instruction to install MySQL Community Edition on Windows 10 and macOS, and MariaDB on Linux-based distribution, but both are developped for every major operating system (macOS, Windows, Debian, Ubuntu, etc.): feel free to pick one or the other, it will not make a difference in this course (up to some minor aspects). MySQL is more common, MariaDB is growing, both are released under GNU General Public License, well-documented and free of charge for their “community” versions.
It is perfectly acceptable, and actually encouraged, to install MySQL or MariaDB on a virtual machine for this class. You can use the Windows Subsystem for Linux, VMware or Virtual Box to run a “sandboxed” environment that should keep your data isolated from our experimentations.
Below are precise and up-to-date instructions, follow them carefully, read the messages displayed on your screen, make sure a step was correctly executed before moving to the next one, and everything should be all right. Also, remember:
The following links could be useful:
Windows (x86, 32-bit), MSI Installer XXX YYY (mysql-installer-web-community-XXX.msi)where XXX is a number version (e.g., 8.0.13.0.), and YYY is the size of the file (e.g., 16.3M). On the next page, click on the (somewhat hidden) “No thanks, just start my download.” button.
If this command fails, it is probably because the version number changed: open the file explorer, go tocd "C:\Program Files\MySQL\MySQL Server 8.0\bin"
C:\Program Files\MySQL\
, look for the right version number, and update the command accordingly.
Then, enter
and enter the password you picked previously for the root account. You are now logged as root in your database management system, you should see a brief message, followed by a promptmysql -u root -p
mysql >
The instructions are almost the same as for Windows. Read MySQL OSX Installation and download the file from MySQL Downloads once you selected “macOS” as your operating system. Install it, leaving everything by default but adding a password (refer to the instructions for windows). Then, open a command-line interface (the terminal), enter
mysql -u root -p
and enter the password you picked previously for the root account. You are now logged as root in your database management system, you should see a brief message, followed by a prompt
mysql >
Now, move on to “Creating a User”.
apt
or aptitude
for debian-based systems, pacman
for Arch Linux, etc.), the packages mysql-client
and mysql-server
(or default-mysql-client
and default-mysql-server
) as well as their dependencies.
Yes, the package is calledmysql-server
, but it actually install the packagemariadb-server-10.3
or higher… So do not be confused: we are, indeed, installing MariaDB!
or, as root,/etc/init.d/mysql status
to see if MySQL is running: if you read something containingservice mysql status
then you can move on to the next step, otherwise run (as root)Active: active (running)
and try again.service mysqld start
You will be asked to provide the current password for the root MySQL user: this password has not be defined yet, so just hit “Enter”. You will be asked if you want to set a new password (that you can freely chose, just make sure to memorize it). Then, answer “n” to the question “Remove anonymous users?”, “Y” to “Disallow root login remotely?”, “n” to “Remove test database and access to it?” and finally “Y” to “Reload privilege tables now?”.mysql_secure_installation
and enter the password you picked previously for the root account. You are now logged as root in your database management system: you should see a brief message, followed by a promptmysql -u root -p
MariaDB [(none)]>
This step will create a non-root user and grant it some rights. Copy-and-paste or type the following three commands, one by one (that is, enter the first one, hit “enter”, enter the second, hit “enter”, etc.). This step will create a non-root user and grant it some rights. Copy-and-paste or type the following three commands, one by one (that is, enter the first one, hit “enter”, enter the second, hit “enter”, etc.).
By default, MySQL and MariaDB only create a root user with all privileges and no password, but we added a password at the previous step.
We first create a new user called testuser on our local installation, and give it the password password
:
CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'password';
Then, we grant the user all the privileges on the databases whose name starts with HW_
:
GRANT ALL PRIVILEGES ON `HW\_%` . * TO 'testuser'@'localhost';
Be careful: backticks (`
) are surrounding HW\_%
whereas single quotes ('
) are surrounding testuser
and localhost
.
And then we quit the DBMS, using
EXIT;
The message displayed after the two first commands should be
Query OK, 0 rows affected (0.00 sec)
and the message displayed after the last command should be
Bye
We now log in as the normal user called “testuser”.
Linux users should type as a normal user, i.e., not as root, in their terminal the following, and Windows users should type in their command prompt the following:
mysql -u testuser -p
Provided the working directory is stillC:\Program Files\MySQL\MySQL Server 8.0\bin
or similar. Cf. MySQL to add the MySQL bin directory to your Windows systemPATH
environment variable.
Enter password
as your password. If you are prompted with a message
ERROR 1045 (28000): Access denied for user 'testuser'@'localhost' (using password: YES)
then you probably typed the wrong password. Otherwise, you should see a welcoming message from MySQL or MariaDB and a prompt.
To save yourself the hassle of typing the password, you can use
mysql -u testuser -ppassword
or
mysql -u testuser -p --password=password
to log-in as testuser immediately.
If at some point you want to know if you are logged as root or testuser, simply enter
\s;
Now, let us create our first schema, our first table, populate it with data, and display various information.
We first create the schema (or database) HW_FirstTest
:
CREATE DATABASE HW_FirstTest; -- Or CREATE SCHEMA HW_FirstTest;
Let us make sure that we created it:
SHOW DATABASES;
Let us use it:
USE HW_FirstTest;
And see what it contains now:
SHOW TABLES;
We now create a table called TableTest
, with two integer attributes called Attribute1
and Attribute2
:
CREATE TABLE TableTest (Attribute1 INT, Attribute2 INT);
And can make sure that the table was indeed created:
SHOW TABLES;
We can further ask our DBMS to display the structure of the table we just created:
DESCRIBE TableTest; -- Can be abbreviated as DESC TableTest;
And even ask to get back the code that would create the exact same structure (but without the data!):
SHOW CREATE TABLE TableTest;
Now, let us populate it with some data:
INSERT INTO TableTest VALUES (1,2), (3,4), (5,6);
Note that the SQL syntax and your DBMS are completely fine with your statement spreading over multiple lines. Let us now display the data stored in the table:
SELECT * FROM TableTest;
After that last command, you should see
+------------+------------+ | Attribute1 | Attribute2 | +------------+------------+ | 1 | 2 | | 3 | 4 | | 5 | 6 | +------------+------------+
Finally, we can erase the content of the table, then erase (“drop”) the table, and finally the schema:
DELETE FROM TableTest; -- Delete the rows DROP TABLE TableTest; -- Delete the table DROP DATABASE HW_FirstTest; -- Delete the schema
You’re all set! All you have to do is to quit, using the command
EXIT;
Note that we were quite careless when we set-up our installation:
testuser
that is common to all of our installation.All of those are obvious security risks, and make this installation unsafe to be a production environment. We will only use it as a testing / learning environment, but it is strongly recommended to:
Exercise 3.1
For each of the following, fill in the blanks:
Exercise 3.2
What does it mean to say that SQL is at the same time a “data definition language” and a “data manipulation language”?
Exercise 3.3
Name three kind of objects (for lack of a better word) a CREATE
statement can create.
Exercise 3.4
Write a SQL
statement that adds a primary key constraint to an attribute named ID
in an already existing table named STAFF
.
Exercise 3.5
Complete each row of the following table with either a datatype or two different examples:
Data type | Examples |
---|---|
4 , -32 |
|
Char(4) | |
VarChar(10) | 'Train' , 'Michelle' |
Bit(4) | |
TRUE , UNKNOWN |
Exercise 3.6
In the datatype CHAR(3)
, what does the 3 indicate?
Exercise 3.7
Explain this query: CREATE SCHEMA FACULTY;
.
Exercise 3.8
Write code to
You are free to come up with an example (even very simple or cryptic) or to re-use an example from class.
Exercise 3.9
Explain this query:
ALTER TABLE TABLEA DROP INDEX Attribute1;
Exercise 3.10
If I want to enter January 21, 2016, as a value for an attribute with the DATE
datatype, what value should I enter?
Exercise 3.11
Write a statement that inserts the values "Thomas"
and 4
into the table TRAINS
.
Exercise 3.12
If PkgName
is the primary key in the table MYTABLE
, what can you tell about the number of rows returned by the following statement?
SELECT * FROM MYTABLE WHERE PkgName = 'MySQL';
Exercise 3.13
If you want that every time a referenced row is deleted, all the refering rows are deleted as well, what mechanism should you use?
Exercise 3.14
By default, does the foreign key restrict, cascade, or set null on update? Can you justify this choice?
Exercise 3.15
If a database designer is using the ON UPDATE SET NULL
for a foreign key, what mechanism is (s)he implementing (i.e., describe how the database will react a certain operation)?
Exercise 3.16
If the following is part of the design of a table:
FOREIGN KEY (DptNumber) REFERENCES DEPARTMENT(Number) ON DELETE SET DEFAULT ON UPDATE CASCADE;
What happen to the rows whose foreign key DptNumber
are set to 3
if the row in the DEPARTMENT
table with primary key Number set to 3
is…
5
?Exercise 3.17
If the following is part of the design of a WORKER
table:
FOREIGN KEY WORKER(DptNumber) REFERENCES DEPARTMENT(DptNumber) ON UPDATE CASCADE;
What happen to the rows whose foreign key DptNumber
are set to 3
if the row in the DEPARTMENT
table with primary key Number
set to 3
is…
5
?Exercise 3.18
Given a relation TOURIST(Name, EntryDate, Address)
, write a SQL
statement printing the name and address of all the tourists who entered the territory after the 15 September, 2012.
Exercise 3.19
Describe what the star do in the statement
SELECT ALL * FROM MYTABLE;
Exercise 3.20
What is the fully qualified name of an attribute? Give an example.
Exercise 3.21
If DEPARTMENT
is a database, what is DEPARTMENT.*
?
Exercise 3.22
What is a multi-set? What does it mean to say that MySQL treats tables as multisets?
Exercise 3.23
What is the difference between
SELECT ALL * FROM MYTABLE;
and
SELECT DISTINCT * FROM MYTABLE;
How are the results the same? How are they different?
Exercise 3.24
What is wrong with the statement
SELECT * WHERE Name = 'CS' FROM DEPARTMENT;
Exercise 3.25
Write a query that returns the number of row (i.e., of entries, of tuples) in a table named BOOK
.
Exercise 3.26
When is it useful to use a select-project-join query?
Exercise 3.27
When is a tuple variable useful?
Exercise 3.28
Login
is 'caubert'
to 'Hugo Pernot'
in the table PROF
.
Exercise 3.29
Can an UPDATE
statement have a WHERE
condition using an attribute that is not the primary key? If no, justify, if yes, tell what could happen.
Exercise 3.30
Give the three possible meaning of the NULL
value, and an example for each of them.
Exercise 3.31
What are the values of the following expressions (i.e., do they evaluate to TRUE
, FALSE
, or UNKNOWN
)?
TRUE AND FALSE
TRUE AND UNKNOWN
NOT UNKNOWN
FALSE OR UNKNOWN
Exercise 3.32
Write the truth table for AND
for the three-valued logic of SQL
.
Exercise 3.33
What comparison expression should you use to test if a value is different from NULL
?
Exercise 3.34
Explain this query:
SELECT Login FROM PROF WHERE Department IN ( SELECT Major FROM STUDENT WHERE Login = 'jrakesh');
Can you rewrite it without nesting queries?
Exercise 3.35
What is wrong with this query?
SELECT Name FROM STUDENT WHERE Login IN ( SELECT Code FROM Department WHERE head = 'aturing');
Exercise 3.36
Write a query that returns the sum of all the values stored in the Pages
attribute of a BOOK
table.
Exercise 3.37
Write a query that adds a Pages
attribute of type INT
into a (already existing) BOOK
table.
Exercise 3.38
Write a query that removes the default value for a Pages
attribute in a BOOK
table.
Exercise 3.39
Under which conditions does SQL
allow you to enter the same row in a table twice?
Solution 3.1
The blanks can be filled as follow:
SQL
, a relation is called a table.SQL
, every statement ends with a semi-colon (;), and in-line comments start with a two minus signs (--).SQL
, there is no string
datatype, so we have to use VARCHAR(x) or CHAR(x) where x is an integer reflecting the maximum (or fixed) size of the string.SQL
’s role is to control access to the data stored, by creating users and granting them rights.Solution 3.2
It can specify the conceptual and internal schema, and it can manipulate the data.
Solution 3.3
Database (schema), table, view, assertion, trigger, etc.
Solution 3.4
ALTER TABLE STAFF ADD PRIMARY KEY(ID);
Solution 3.5
Data type | Examples |
---|---|
INT |
4 , -32 |
CHAR(4) |
'abCD' , "dEfG" |
VARCHAR(10) |
'Train' , 'Michelle' |
BIT(4) |
B'1010' , B'0101' |
BOOL``
|TRUE,FALSE,NULL`
NULL
is actually a valid answer for every single type of
Solution 3.6
That we can store exactly three characters.
Solution 3.7
It creates a schema, i.e., a database, named Faculty
.
Solution 3.8
A simple and compact code could be:
/* code/sql/HW_Short.sql */ CREATE TABLE A ( Att1 INT PRIMARY KEY, Att2 INT ); CREATE TABLE B ( Att3 INT PRIMARY KEY, Att4 INT, FOREIGN KEY (Att4) REFERENCES A (Att1) ); INSERT INTO A VALUES ( 1, 2); INSERT INTO B VALUES ( 3, 1);
Solution 3.9
It removes the UNIQUE
constraint on the Attribute1
in the TABLEA
table.
Solution 3.10
DATE'2016-01-21', '2016-01-21', '2016/01/21', '20160121'
Solution 3.11
INSERT INTO TRAINS VALUES('Thomas', 4);
Solution 3.12
We know that at most one (but possibly 0) row will be returned.
Solution 3.13
We should use a referential triggered action clause, ON DELETE CASCADE
.
Solution 3.14
By default, the foreign key restricts updates. This prevents unwanted update of information: if an update needs to be propagated, then it needs to be “acknowledged” and done explicitely.
Solution 3.15
If the referenced row is updated, then the attribute of the referencing rows are set to NULL
.
Solution 3.16
In the referencing rows,
Solution 3.17
DEPARTMENT
table with primary key Number
set to 3
cannot be deleted if a row in the WORKER
table references it.Solution 3.18
We could use the following:
SELECT Name, Address FROM TOURIST WHERE EntryDate > DATE'2012-09-15';
Solution 3.19
It selects all the attributes, it is a wildcard.
Solution 3.20
The name of the relation with the name of its schema and a period beforehand. An example would be EMPLOYEE.Name
.
Solution 3.21
All the tables in that database.
Solution 3.22
A multiset is a set where the same value can occur twice. In MySQL, the same row can occur twice in a table.
Solution 3.23
They both select all the rows in the MYTABLE
table, but ALL
will print the duplicate values, whereas DISTINCT
will print them only once.
Solution 3.24
You cannot have the WHERE
before FROM
.
Solution 3.25
SELECT COUNT(*) FROM BOOK;
Solution 3.26
We use those query that projects on attributes using a selection and join conditions when we need to construct for information based on pieces of data spread in multiple tables.
Solution 3.27
It makes the distinction between two different rows of the same table, it is useful when we want to select a tuple in a relation that is in a particular relation with a tuple in the same relation. Quoting Stack Overflow:
They are useful for saving typing, but there are other reasons to use them:
- If you join a table to itself you must give it two different names otherwise referencing the table would be ambiguous.
- It can be useful to give names to derived tables, and in some database systems it is required… even if you never refer to the name.
Solution 3.28
We could use the following:
UPDATE PROF SET Name = 'Hugo Pernot' WHERE Login = 'caubert';
Solution 3.29
Yes, we can have select condition that does not use primary key. In that case, it could be the case that we update more than one tuple with such a command (which is not necessarily a bad thing).
Solution 3.30
Unknown value (“Will it rain tomorrow?”), unavailable / withheld (“What is the phone number of Harry Belafonte?”), N/A (“What is the email address of Abraham Lincoln?”).
Solution 3.31
TRUE AND FALSE → FALSE
TRUE AND UNKNOWN → UNKNOWN
NOT UNKNOWN → UNKNOWN
FALSE OR UNKNOWN → FALSE
Solution 3.32
TRUE AND TRUE → TRUE
TRUE AND FALSE → FALSE
TRUE AND UNKNOWN → UNKNOWN
FALSE AND FALSE → FALSE
UNKNOWN AND UNKNOWN → UNKNOWN
FALSE AND UNKNOWN → FALSE
For a more compact presentation, refer to the three-valued truth table.
Solution 3.33
IS NOT
Solution 3.34
It list the login of the professors teaching in the department where the student whose login is “jrakesh” is majoring. It can be rewritten as
SELECT PROF.Login FROM PROF, STUDENT WHERE Department = Major AND STUDENT.Login = 'jrakesh';
Solution 3.35
It tries to find a Login
in a Code
.
Solution 3.36
SELECT SUM(Pages) FROM BOOK;
Solution 3.37
ALTER TABLE BOOK ADD COLUMN Pages INT;
Solution 3.38
ALTER TABLE BOOK ALTER COLUMN Pages DROP DEFAULT;
Solution 3.39
Essentially, if there are no primary key in the relation, and if no attribute has the UNIQUE
constraint.
Problem 3.1 (Discovering the documentation)
The goal of this problem is to learn where to find the documentation for your DBMS, and to understand how to read the syntax of SQL
commands.
You can consult (Elmasri and Navathe 2010, Table 5.2, p. 140) or (Elmasri and Navathe 2015, Table 7.2, p. 235), for a very quick summary of the most common commands. Make sure you are familiar with the Backus–Naur form (BNF) notation commonly used:
<…>
[…]
{…}
(…|…|…)
The most complete lists of commands are probably at
Those are the commands implemented in the DBMS you are actually using. Since there are small variations from one implementation to the other, it is better to take one of this link as a reference in the future.
As a starting point, looking at the syntax for CREATE TABLE
commands is probably a good start, cf. MariaDB or MySQL.
Problem 3.2 (Create and use a simple table in SQL
)
This problem will guide you in manipulating a very simple table in SQL
.
Pb 3.2 – Question 1
Log in as testuser
, create a database named HW_Address
, use it, and create two tables:
CREATE TABLE NAME( FName VARCHAR(15), LName VARCHAR(15), ID INT, PRIMARY KEY(ID) ); CREATE TABLE ADDRESS( StreetName VARCHAR(15), Number INT, Habitants INT, PRIMARY KEY(StreetName, Number) );
Pb 3.2 – Question 2
Observe the output produced by the command DESC ADDRESS;
.
Pb 3.2 – Question 3
Add a foreign key to the ADDRESS
table, using
ALTER TABLE ADDRESS ADD FOREIGN KEY (Habitants) REFERENCES NAME(ID);
And observe the new output produced by the command DESC ADDRESS;.
Is it what you would have expected? How informative is it? Can you think of a command that would output more detailled information, including a reference to the existence of the foreign key?
Pb 3.2 – Question 4
Draw the relational model corresponding to that database and identify the primary and foreign keys.
Pb 3.2 – Question 5
Add this data to the NAME
table:
INSERT INTO NAME VALUES ('Barbara', 'Liskov', 003); INSERT INTO NAME VALUES ('Tuong Lu', 'Kim', 004); INSERT INTO NAME VALUES ('Samantha', NULL, 080);
What command can you use to display this infomation back? Do you notice anything regarding the values we entered for the ID
attribute?
Pb 3.2 – Question 6
Add some data into the ADDRESS table:
INSERT INTO ADDRESS VALUES ('Armstrong Drive', 10019, 003), ('North Broad St.', 23, 004), ('Robert Lane', 120, NULL);
What difference do you notice with the insertions we made in the NAME
table? Which syntax seems more easy to you?
Pb 3.2 – Question 7
Write a SELECT
statement that returns the ID
number of the person whose first name is “Samantha”.
Pb 3.2 – Question 8
Write a statement that violates the entity integrity constraint. What is the error message returned?
Pb 3.2 – Question 9
Execute an UPDATE
statement that violates the referential integrity constraint. What is the error message returned?
Pb 3.2 – Question 10
Write a statement that violates another kind of constraint. Explain what constraint you are violating and explain the error message.
Problem 3.3 (Duplicate rows in SQL
)
Log in as testuser
and create a database titled HW_REPETITION
. Create in that database a table (the following questions refer to this table as EXAMPLE
, but you are free to name it whatever you want) with at least two attributes that have different data types. Do not declare a primary key yet. Answer the following:
Pb 3.3 – Question 1
Add a tuple to your table using
INSERT INTO EXAMPLE VALUES(X, Y);
where the X
and Y
are values have the right datatype. Try to add this tuple again. What do you observe? (You can use SELECT * FROM EXAMPLE;
to observe what is stored in this table.)
Pb 3.3 – Question 2
Alter your table to add a primary key, using
ALTER TABLE EXAMPLE ADD PRIMARY KEY (Attribute);
where Attribute
is the name of the attribute you want to be a primary key. What do you observe?
Pb 3.3 – Question 3
Empty your table using
DELETE FROM EXAMPLE;
and alter your table to add a primary key, using the command we gave at the previous step. What do you observe?
Pb 3.3 – Question 4
Try to add the same tuple twice. What do you observe?
Problem 3.4 (Constraints on foreign keys)
From the notes, recall the following about foreign keys:
Two important remarks:
- The datatype of the foreign key has to be the exactly the same as the datatype of the attribute to which we are referring.
- The target of the foreign key must be the primary key.
But, the situation is slightly more complex. Test for yourself by editing the following code as indicated:
/* code/sql/HW_FKtest.sql */ DROP SCHEMA IF EXISTS HW_FKtest; CREATE SCHEMA HW_FKtest; USE HW_FKtest; CREATE TABLE TARGET ( Test VARCHAR(15) PRIMARY KEY ); CREATE TABLE SOURCE ( Test VARCHAR(25), FOREIGN KEY (Test) REFERENCES TARGET (Test) );
PRIMARY KEY
constraint.PRIMARY KEY
with UNIQUEM
.VARCHAR(25)
with CHAR(25)
.VARCHAR(25)
with INT
.VARCHAR(25)
with VARCHAR(15)
Problem 3.5 (Revisiting the PROF table)
Create the PROF
, DEPARTMENT
, STUDENT
and GRADE
tables as in the “Constructing and populating a new example” section. Populate them with some data (copy it from the notes or come up with your own data).
To obtain exactly the same schema as the one we developed and edited, you can use mysqldump to “dump” this table, with a command like
mysqldump -u testuser -ppassword\ -h localhost --add-drop-database\ --skip-comments --compact\ HW_ProfExample > dump.sql
The code we studied during the lecture is more or less the following.
/* code/sql/HW_ProfExampleRevisitedRevisited.sql */ DROP SCHEMA IF EXISTS HW_ProfExampleRevisited; CREATE SCHEMA HW_ProfExampleRevisited; USE HW_ProfExampleRevisited; CREATE TABLE PROF ( Login VARCHAR(25) PRIMARY KEY, NAME VARCHAR(25), Department CHAR(5) ); CREATE TABLE DEPARTMENT ( Code CHAR(5) PRIMARY KEY, NAME VARCHAR(25), Head VARCHAR(25), FOREIGN KEY (Head) REFERENCES PROF (LOGIN) ON UPDATE CASCADE ); ALTER TABLE PROF ADD FOREIGN KEY (Department) REFERENCES DEPARTMENT (Code); CREATE TABLE STUDENT ( Login VARCHAR(25) PRIMARY KEY, NAME VARCHAR(25), Registered DATE, Major CHAR(5), FOREIGN KEY (Major) REFERENCES DEPARTMENT (Code) ); CREATE TABLE GRADE ( Login VARCHAR(25), Grade INT, PRIMARY KEY (LOGIN, Grade), FOREIGN KEY (LOGIN) REFERENCES STUDENT (LOGIN) ); INSERT INTO DEPARTMENT VALUES ( 'MATH', 'Mathematics', NULL), ( 'CS', 'Computer Science', NULL); INSERT INTO DEPARTMENT ( Code, Name) VALUES ( 'CYBR', 'Cyber Secturity'); INSERT INTO PROF ( LOGIN, Department, Name) VALUES ( 'caubert', 'CS', 'Clément Aubert'); INSERT INTO PROF ( LOGIN, Name, Department) VALUES ( 'aturing', 'Alan Turing', 'CS'), ( 'perdos', 'Paul Erdős', 'MATH'), ( 'bgates', 'Bill Gates', 'CYBR'); INSERT INTO STUDENT ( LOGIN, Name, Registered, Major) VALUES ( 'jrakesh', 'Jalal Rakesh', DATE '2017-12-01', 'CS'), ( 'svlatka', 'Sacnite Vlatka', '2015-03-12', 'MATH'), ( 'cjoella', 'Candice Joella', '20120212', 'CYBR'), ( 'aalyx', 'Ava Alyx', 20121011, 'CYBR'), ( 'caubert', 'Clément Aubert', NULL, 'CYBR'); INSERT INTO GRADE VALUES ( 'jrakesh', 3.8), ( 'svlatka', 2.5);
We will resume working on this model, and enhance it.
Pb 3.5 – Question 1
Draw the complete relational model for this database (i.e., for the PROF
, DEPARTMENT
, STUDENT
and GRADE
relations).
Pb 3.5 – Question 2
Create and populate a LECTURE
table as follows:
Name
, Instructor
, Code
, and Year
, of types VARCHAR(25)
for the first two, CHAR(5)
for Code
, and YEAR(4)
for Year
.Year
and Code
attributes should be the primary key (yes, have two attributes be the primary key).Instructor
attribute should be a foreign key referencing the Login
attribute in PROF
.LECTURE
table with some made-up data.Try to think about some of the weakenesses of this representation. For instance, can it accomodate two instructors for the same class? Write down two possibles scenarios in which this schema would not be appropriate.
Pb 3.5 – Question 3
The GRADE
table had some limitations too. For example, every student could have only one grade. Add two columns to the GRADE
table using:
ALTER TABLE GRADE ADD COLUMN LectureCode CHAR(5), ADD COLUMN LectureYear YEAR(4);
Add a foreign key:
ALTER TABLE GRADE ADD FOREIGN KEY (LectureYear, LectureCode) REFERENCES LECTURE(Year, Code);
Use DESCRIBE
and SELECT
to observe the schema of the GRADE
table and its rows. Is it what you would have expected?
Pb 3.5 – Question 4
Update the tuples in GRADE
with some made-up data. Now every row should contain, in addition to a login and a grade, a lecture year and a lecture code.
Pb 3.5 – Question 5
Update the relational model you previously drew to reflect the new situation of your tables.
Pb 3.5 – Question 6
Write SELECT
statements answering the following questions (where PROF.Name
, LECTURE.Name
, YYYY
, LECTURE.Code
and STUDENT.Login
should be relevant values considering your data):
LECTURE.Name
in YYYY
?”YYYY
without any duplicates?”LECTURE.Code
?”LECTURE.Code
taught?”LECTURE.Code
?”STUDENT.Login
?”Problem 3.6 (TRAIN table and more advanced SQL
coding)
Look at the SQL code below and then answer the following questions.
/* code/sql/HW_Train.sql */ CREATE TABLE TRAIN ( ID VARCHAR(30), Model VARCHAR(30), ConstructionYear YEAR (4) ); CREATE TABLE CONDUCTOR ( ID VARCHAR(20), NAME VARCHAR(20), ExperienceLevel VARCHAR(20) ); CREATE TABLE ASSIGNED_TO ( TrainId VARCHAR(20), ConductorId VARCHAR(20), Day DATE, PRIMARY KEY (TrainId, ConductorId) );
Pb 3.6 – Question 1
Modify the CREATE
statement that creates the TRAIN
table (lines 1–5), so that ID
would be declared as the primary key. It is sufficient to only write the line(s) that need to change.
Pb 3.6 – Question 2
Write an ALTER
statement that makes ID
become the primary key of the CONDUCTOR
table.
Pb 3.6 – Question 3
Modify the CREATE
statement that creates the ASSIGNED_TO
table (lines 13–18), so that it has two foreign keys: ConductorId
references the ID
attribute in CONDUCTOR
and TrainId
references the ID
attribute in TRAIN
. It is sufficient to only write the line(s) that need to change.
Pb 3.6 – Question 4
Write INSERT
statements that insert one tuple of your choosing in each relation (no NULL
values). These statements should respect all the constraints (including the ones we added in the previous questions) and result in actual insertions. (Remember that four digits is a valid value for an attribute with the YEAR(4)
datatype.)
Pb 3.6 – Question 5
Write a statement that sets the value of the ExperienceLevel
attribute to “Senior” in all the tuples where the ID
attribute is “GP1029” in the CONDUCTOR
relation.
Pb 3.6 – Question 6
Write a SELECT
statement that answers each of the following questions:
Problem 3.7 (Read, correct, and write SQL
statements for the COFFEE database)
Suppose we have the relational model depicted below, with the indicated data in it:
COFFEE
Ref | Origin | TypeOfRoast | PricePerPound |
---|---|---|---|
001 | Brazil | Light | 8.90 |
121 | Bolivia | Dark | 7.50 |
311 | Brazil | Medium | 9.00 |
221 | Sumatra | Dark | 10.25 |
CUSTOMER
CardNo | Name | |
---|---|---|
001 | Bob Hill | b.hill@isp.net |
002 | Ana Swamp | swampa@nca.edu |
003 | Mary Sea | brig@gsu.gov |
004 | Pat Mount | pmount@fai.fr |
SUPPLY
Provider | Coffee |
---|---|
Coffee Unl. | 001 |
Coffee Unl. | 121 |
Coffee Exp. | 311 |
Johns & Co. | 221 |
PROVIDER
Name | |
---|---|
Coffee Unl. | bob@cofunl.com |
Coffee Exp. | pat@coffeex.dk |
Johns & Co. | NULL |
In the following, we will assume that this model was implemented in a DBMS (MySQL or MariaDB), the primary keys being COFFEE.Ref
, CUSTOMER.CardNo
, SUPPLY.Provider
and SUPPLY.Coffee
, and PROVIDER.Name
, and the foreign keys being as follows:
FavCoffee in the CUSTOMER relation | refers to | Ref in the COFFEE relation |
---|---|---|
Provider in the SUPPLY | refers to | Name in the PROVIDER relation |
Coffee in the SUPPLY | refers to | Ref in the COFFEE relation |
Read and write SQL
commands for the following “what-if” scenarios. Assume that:
SQL
’s syntax (there’s no “a semi-colon is missing” trap).You can use COFFEE.1 to denote the first tuple (or row) in COFFEE, and similarly for other relations and tuples (so that, for instance, SUPPLY.4 corresponds to "Johns & Co".
, 221
).
Pb 3.7 – Question 1
Draw the relational model of this table.
Pb 3.7 – Question 2
Determine if the following insertion statements would violate the the entity integrity constraint, (“primary key cannot be NULL
and should be unique”), the referential integrity constraint (“the foreign key must refer to something that exists”), if there would be some other kind of error (ignoring the plausability / revelance of inserting that tuple), or if it would result in successful insertion.
INSERT INTO CUSTOMER VALUES(005, 'Bob Hill', NULL, 001); INSERT INTO COFFEE VALUES(002, "Peru", "Decaf", 3.00); INSERT INTO PROVIDER VALUES(NULL, "contact@localcof.com"); INSERT INTO SUPPLY VALUES("Johns Co.", 121); INSERT INTO SUPPLY VALUES("Coffee Unl.", 311, 221);
Pb 3.7 – Question 3
Assuming that the referential triggered action clause ON UPDATE CASCADE
is used for each of the foreign keys in this database, list the tuples modified by the following statements:
UPDATE CUSTOMER SET FavCoffee = 001 WHERE CardNo = 001; UPDATE COFFEE SET TypeOfRoast = 'Decaf' WHERE Origin = 'Brazil'; UPDATE PROVIDER SET Name = 'Coffee Unlimited' WHERE Name = 'Coffee Unl.'; UPDATE COFFEE SET PricePerPound = 10.00 WHERE PricePerPound > 10.00;
Pb 3.7 – Question 4
Assuming that the referential triggered action clause ON DELETE CASCADE
is used for each of the foreign keys in this database, list the tuples modified by the following statements:
DELETE FROM CUSTOMER WHERE Name LIKE '%S%'; DELETE FROM COFFEE WHERE Ref = 001; DELETE FROM SUPPLY WHERE Provider = 'Coffee Unl.' AND Coffee = 001; DELETE FROM PROVIDER WHERE Name = 'Johns & Co.';
Pb 3.7 – Question 5
Assume that there is more data in our table than what was given at the beginning of the problem. Write SQL
queries that answer the following questions:
Problem 3.8 (Write select queries for the DEPARTMENT table)
Consider the following SQL
code:
/* code/sql/HW_Department.sql */ CREATE TABLE DEPARTMENT ( ID INT PRIMARY KEY, NAME VARCHAR(30) ); CREATE TABLE EMPLOYEE ( ID INT PRIMARY KEY, NAME VARCHAR(30), Hired DATE, Department INT, FOREIGN KEY (Department) REFERENCES DEPARTMENT (ID) ); INSERT INTO DEPARTMENT VALUES ( 1, "Storage"), ( 2, "Hardware"); INSERT INTO EMPLOYEE VALUES ( 1, "Bob", 20100101, 1), ( 2, "Samantha", 20150101, 1), ( 3, "Mark", 20050101, 2), ( 4, "Karen", NULL, 1), ( 5, "Jocelyn", 20100101, 1);
Write queries that return the following information. The values returned in this set-up will be in parenthesis, but keep the queries general.
Storage
department ("Bob"
, "Samantha"
, "Karen"
and "Jocelyn"
),"Mark"
),"Bob"
and "Jocelyn"
).Problem 3.9 (Write select queries for the COMPUTER table)
Consider the following SQL
code:
/* code/sql/HW_Computer.sql */ DROP SCHEMA IF EXISTS HW_Computer; CREATE SCHEMA HW_Computer; USE HW_Computer; CREATE TABLE COMPUTER ( ID VARCHAR(20) PRIMARY KEY, Model VARCHAR(40) ); CREATE TABLE PRINTER ( ID VARCHAR(20) PRIMARY KEY, Model VARCHAR(40) ); CREATE TABLE CONNEXION ( Computer VARCHAR(20), Printer VARCHAR(20), PRIMARY KEY (Computer, Printer), FOREIGN KEY (Computer) REFERENCES COMPUTER (ID), FOREIGN KEY (Printer) REFERENCES PRINTER (ID) ); INSERT INTO COMPUTER VALUES ( 'A', 'DELL A'), ( 'B', 'HP X'), ( 'C', 'ZEPTO D'), ( 'D', 'MAC Y'); INSERT INTO PRINTER VALUES ( '12', 'HP-140'), ( '13', 'HP-139'), ( '14', 'HP-140'), ( '15', 'HP-139'); INSERT INTO CONNEXION VALUES ( 'A', '12'), ( 'A', '13'), ( 'B', '13'), ( 'C', '14');
Write queries that return the following information. The values returned in this set-up will be in parenthesis, but keep the queries general.
ID
is '13'
(2
).2
).ID
is 'A'
('HP-140'
and 'HP-139'
).'D'
).Problem 3.10 (Write select queries for the SocialMedia schema)
Consider the following SQL
code:
/* code/sql/HW_SocialMedia.sql */ CREATE TABLE ACCOUNT ( ID INT PRIMARY KEY, NAME VARCHAR(25), Email VARCHAR(25) UNIQUE ); CREATE TABLE SUBSCRIBE ( Subscriber INT, Subscribed INT, DATE DATE, FOREIGN KEY (Subscriber) REFERENCES ACCOUNT (ID), FOREIGN KEY (Subscribed) REFERENCES ACCOUNT (ID), PRIMARY KEY (Subscriber, Subscribed) ); CREATE TABLE VIDEO ( ID INT PRIMARY KEY, Title VARCHAR(25), Released DATE, Publisher INT, FOREIGN KEY (Publisher) REFERENCES ACCOUNT (ID) ); CREATE TABLE THUMBS_UP ( Account INT, Video INT, DATE DATE, PRIMARY KEY (Account, Video), FOREIGN KEY (Account) REFERENCES ACCOUNT (ID), FOREIGN KEY (Video) REFERENCES VIDEO (ID) ); INSERT INTO ACCOUNT VALUES ( 1, "Bob Ross", "bob@ross.com"), ( 2, NULL, "anon@fai.com"), ( 3, "Martha", NULL); INSERT INTO SUBSCRIBE VALUES ( 2, 1, DATE "2020-01-01"), ( 3, 1, DATE "2019-03-03"), ( 3, 2, DATE "2019-03-03"), ( 2, 2, DATE "2019-03-03"), ( 1, 2, DATE "2019-03-03"); -- The first entry means that 2 subscribed to 1, not the -- other way around. -- And similarly for the other entries. INSERT INTO VIDEO VALUES ( 10, "My first video!", DATE "2020-02-02", 1), ( 20, "My second video!", DATE "2020-02-03", 1), ( 30, "My vacations", DATE "2020-02-04", 2); INSERT INTO THUMBS_UP VALUES ( 2, 10, DATE "2020-02-02"), ( 3, 10, DATE "2020-02-02"), ( 2, 20, DATE "2020-02-02"), ( 1, 30, DATE "2020-02-05");
Write queries that return the following information. The values returned in this set-up will be in parenthesis, but keep the queries general.
"My first video!"
, "My second video!"
, "My vacations"
)."My first video!"
("2020-02-02"
)."2"
)."video"
("10"
, "20"
)."My vacations"
("1"
)."My first video!"
)."Bob Ross"
)."2"
).Problem 3.11 (Write select queries for a variation of the COMPUTER table)
Consider the following SQL
code:
CREATE TABLE COMPUTER ( ID VARCHAR(20) PRIMARY KEY, Model VARCHAR(40) ); CREATE TABLE PERIPHERAL ( ID VARCHAR(20) PRIMARY KEY, Model VARCHAR(40), TYPE ENUM ('mouse', 'keyboard', 'screen', 'printer') ); CREATE TABLE CONNEXION ( Computer VARCHAR(20), Peripheral VARCHAR(20), PRIMARY KEY (Computer, Peripheral), FOREIGN KEY (Computer) REFERENCES COMPUTER (ID), FOREIGN KEY (Peripheral) REFERENCES PERIPHERAL (ID) ); INSERT INTO COMPUTER VALUES ( 'A', 'Apple IIc Plus'), ( 'B', 'Commodore SX-64'); INSERT INTO PERIPHERAL VALUES ( '12', 'Trendcom Model', 'printer'), ( '14', 'TP-10 Thermal Matrix', 'printer'), ( '15', 'IBM Selectric', 'keyboard'); INSERT INTO CONNEXION VALUES ( 'A', '12'), ( 'B', '14'), ( 'A', '15');
Write queries that return the following information. The values returned in this set-up will be in parenthesis, but keep the queries general.
ID
is 'A'
('Apple IIc Plus'
).ID
is '14'
(printer
).NAME
starts with 'IBM'
('IBM Selectric'
).ID
is 'A'
(Trendcom Model
, IBM Selectric
).2
).Problem 3.12 (Improving a role-playing game with a relational model)
A friend of yours wants you to review and improve the code for a role-playing game.
The original idea was that each character has a name, a class (e.g., Bard, Assassin, Druid), a certain amount of experience, a level, one or more weapons (providing bonuses) and the ability to complete quests. A quest has a name and rewards the characters who completed it with a certain amount of experience and, on rare occaisions, with a special item.
Your friend came up with the following code:
CREATE TABLE CHARACTER( Name VARCHAR(30) PRIMARY KEY, Class VARCHAR(30), XP INT, LVL INT, Weapon_Name VARCHAR(30), Weapon_Bonus INT, Quest_Completed VARCHAR(30) ); CREATE TABLE QUEST( ID VARCHAR(20) PRIMARY KEY, Completed_By VARCHAR(30), XP_Gained INT, Special_Item VARCHAR(20), FOREIGN KEY (Completed_By) REFERENCES CHARACTER(Name) ); ALTER TABLE CHARACTER ADD FOREIGN KEY (Quest_Completed) REFERENCES QUEST(ID);
However, there are several problems with the code:
CHARACTER
table to add an arbitrary number of weapons ended up creating horrible messes.)Can you provide a relational model (there is no need to write the SQL
code, but do remember to indicate the primary and foreign keys) that would solve all of your friend’s troubles?
Problem 3.13 (A simple database for books)
Consider the following code:
/* code/sql/HW_SimpleBook.sql */ DROP SCHEMA IF EXISTS HW_SimpleBook; CREATE SCHEMA HW_SimpleBook; USE HW_SimpleBook; CREATE TABLE AUTHOR ( FName VARCHAR(30), LName VARCHAR(30), Id INT PRIMARY KEY ); CREATE TABLE PUBLISHER ( NAME VARCHAR(30), City VARCHAR(30), PRIMARY KEY (NAME, City) ); CREATE TABLE BOOK ( Title VARCHAR(30), Pages INT, Published DATE, PublisherName VARCHAR(30), PublisherCity VARCHAR(30), FOREIGN KEY (PublisherName, PublisherCity) REFERENCES PUBLISHER (NAME, City), Author INT, FOREIGN KEY (Author) REFERENCES AUTHOR (Id), PRIMARY KEY (Title, Published) ); INSERT INTO AUTHOR VALUES ( "Virginia", "Wolve", 01), ( "Paul", "Bryant", 02), ( "Samantha", "Carey", 03); INSERT INTO PUBLISHER VALUES ( "Gallimard", "Paris"), ( "Gallimard", "New-York"), ( "Jobs Pub.", "New-York"); INSERT INTO BOOK VALUES ( "What to eat", 213, DATE '20170219', "Gallimard", "Paris", 01), ( "Where to live", 120, DATE '20130212', "Gallimard", "New-York", 02), ( "My Life, I", 100, DATE '18790220', "Gallimard", "Paris", 03), ( "My Life, II", 100, DATE '18790219', "Jobs Pub.", "New-York", 03);
The values inserted in the database is just to provide some examples; you should assume there is more data in it than what we have inserted. In this long problem, you will be asked to write commands to select, update, delete, insert data, and to improve upon the relational model.
Pb 3.13 – Question 1
Write a command that selects:
Title
of all the books.Name
of the publishers.Titles
and Published
dates of the books published since January 31, 2012."Gallimard"
(from any city)."New-York"
.ID
of the authors who published a book whose name starts with "Where"
."Wolve"
.Pb 3.13 – Question 2
Write a command that updates the title of all the books written by the author whose ID
is 3
to "BANNED"
. Is there any reason for this command to be rejected by the system? If yes, explain the reason.
Pb 3.13 – Question 3
Write one or multiple commands that would delete the author whose ID
is 3
and all the books written by that author. Make sure you do not violate any foreign key constraints.
Pb 3.13 – Question 4
Write a command that would create a table used to record the awards granted to authors for particular books. Assume that each award has its own name, is awarded every year, and that it is awarded to an author for a particular book. Pick appropriate attributes, datatypes, primary and foreign keys, and, as always, avoid redundancy.
You can use the DATE
datatype to store a year.
Pb 3.13 – Question 5
Draw the relational model of the database you created (including all the relations given in the code and the ones you added).
Pb 3.13 – Question 6
Discuss two limitations of the model and how to improve it.
Problem 3.14 (A database for website certificates)
A certificate for a website has a serial number (SN
) and a common name (CN
). It must belong to an organization and be signed by a certificate authority (CA
). The organization and CA
must both have an SN
and a CN
. A CS
can be trusted, not trusted, or not evaluated. The code below is an attempt to represent this situation and is populated with examples.
CREATE TABLE ORGANIZATION( SN VARCHAR(30) PRIMARY KEY, CN VARCHAR(30) ); CREATE TABLE CA( SN VARCHAR(30) PRIMARY KEY, CN VARCHAR(30), Trusted BOOL ); CREATE TABLE CERTIFICATE( SN VARCHAR(30) PRIMARY KEY, CN VARCHAR(30), Org VARCHAR(30) NOT NULL, Issuer VARCHAR(30) NOT NULL, Valid_Since DATE, Valid_Until DATE, FOREIGN KEY (Org) REFERENCES ORGANIZATION(SN) ON DELETE CASCADE, FOREIGN KEY (Issuer) REFERENCES CA(SN) ); INSERT INTO ORGANIZATION VALUES ('01', 'Wikimedia Foundation'), ('02', 'Free Software Foundation'); INSERT INTO CA VALUES ('A', "Let's Encrypt", true), ('B', 'Shady Corp.', false), ('C', 'NewComer Ltd.', NULL); INSERT INTO CERTIFICATE VALUES ('a', '*.wikimedia.org', '01', 'A', 20180101, 20200101), ('b', '*.fsf.org', '02', 'A', 20180101, 20191010), ('c', '*.shadytest.org', '02', 'B', 20190101, 20200101), ('d', '*.wikipedia.org', '01', 'C', 20200101, 20220101);
CN
’s of all certificates ("*.wikimedia.org, \*.fsf.org, \*.shadytest.org, \*.wikipedia.org"
).SN
’s of the organizations whose CN
contains "Foundation"
("01, 02"
).CN
’s and expiration dates of all the certificates that expired, assuming today is the 6th of December 2019 ("\*.fsf.org"
, 2019-10-10).CN
’s of the CA
’s that are not trusted ("Shady Corp., NewComer Ltd."
),CA
whose CN
is "Let's encrypt"
(2
).CN
’s of the organizations along with the CN
’s of their certificates ("Wikimedia Foundation, \*.wikimedia.org, Free Software Foundation, \*.fsf.org, Free Software Foundation, \*.shadytest.org, Wikimedia Foundation, \*.wikipedia.org"
).DELETE FROM
CA WHERE
SN = 'A'
;UPDATE
ORGANIZATION SET
CN = "FSF"
WHERE
SN = '02'
;UPDATE
ORGANIZATION SET
SN = "01"
WHERE
SN = '02'
;DELETE FROM
ORGANIZATION;Problem 3.15 (A simple database for published pieces of work)
Consider the following code:
/* code/sql/HW_Work.sql */ CREATE TABLE AUTHOR ( NAME VARCHAR(30) PRIMARY KEY, Email VARCHAR(30) ); CREATE TABLE WORK ( Title VARCHAR(30) PRIMARY KEY, Author VARCHAR(30), FOREIGN KEY (Author) REFERENCES AUTHOR (NAME) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE BOOK ( ISBN INT PRIMARY KEY, Work VARCHAR(30), Published DATE, Price DECIMAL(10, 2), FOREIGN KEY (WORK) REFERENCES WORK (Title) ON DELETE RESTRICT ON UPDATE CASCADE ); CREATE TABLE EBOOK ( ISBN INT PRIMARY KEY, Work VARCHAR(30), Published DATE, Price DECIMAL(10, 2), FOREIGN KEY (WORK) REFERENCES WORK (Title) ON DELETE RESTRICT ON UPDATE CASCADE ); INSERT INTO AUTHOR VALUES ( "Virginia W.", "vw@isp.net"), -- A.1 ( "Paul B.", "pb@isp.net"), -- A.2 ( "Samantha T.", "st@fai.fr") -- A.3 ; INSERT INTO WORK VALUES ( "What to eat", "Virginia W.") -- W.1 ; INSERT INTO BOOK VALUES ( 15155627, "What to eat", DATE '20170219', 12.89) -- B.1 ; INSERT INTO EBOOK VALUES ( 15155628, "What to eat", DATE '20170215', 9.89) -- E.1
Assume the following:
SQL
’s syntax (there’s no “a semi-colon is missing” trap).Also, note that each row inserted between line 39 and 50 is given a name in comment ("A.1, A.2, A.3, W.1"
, etc.).
Pb 3.15 – Question 1
Draw the relational model corresponding to this series of commands.
Pb 3.15 – Question 2
Determine if the following insertion statements would violate the the entity integrity constraint, the referential integrity constraint, if there would be some other kind of error, or if it would result in sunsuccessful insertion.
INSERT INTO EBOOK VALUES (0, NULL, 20180101, 0); INSERT INTO AUTHOR VALUES("Mary B.", "mb@fai.fr", NULL); INSERT INTO WORK VALUES("My Life", "Claude A."); INSERT INTO BOOK VALUES(00000000, NULL, DATE'20001225', 90.9); INSERT INTO AUTHOR VALUES("Virginia W.", "alt@isp.net");
Pb 3.15 – Question 3
List the rows (A.2, W.1, etc.) modified by the following statements. Be careful about the conditions on foreign keys!
UPDATE AUTHOR SET Email = 'Deprecated' WHERE Email LIKE '%isp.net'; UPDATE WORK SET Title = "How to eat" WHERE Title = "What to eat"; DELETE FROM WORK; DELETE FROM AUTHOR WHERE Name = "Virginia W.";
Pb 3.15 – Question 4
Assume that there is more data than what we inserted. Write a command that selects:
Pb 3.15 – Question 5
Write a command that updates the title of all the pieces of work written by the author whose name is “Virginia W.” to “BANNED.” Is there any reason for this command to be rejected by the system? If yes, explain the reason.
Pb 3.15 – Question 6
Write one or multiple commands that would delete the work whose title is “My Life”, as well as all of the book and ebook versions of it.
Pb 3.15 – Question 7
Discuss two limitations of the model and how to improve it.
Problem 3.16 (A simple database for authors of textbooks)
Consider the following code:
/* code/sql/HW_TextbookAuthored.sql */ DROP SCHEMA IF EXISTS HW_TEXTBOOKAUTHORED; CREATE SCHEMA HW_TEXTBOOKAUTHORED; USE HW_TEXTBOOKAUTHORED; CREATE TABLE TEXTBOOK ( Title VARCHAR(50), ISBN CHAR(13) PRIMARY KEY, Price DECIMAL(10, 2) ); CREATE TABLE AUTHOR ( LName VARCHAR(30), FName VARCHAR(30), Email VARCHAR(30), PRIMARY KEY (Lname, Fname) ); CREATE TABLE AUTHORED ( Book CHAR(13), FOREIGN KEY (Book) REFERENCES TEXTBOOK (ISBN), AuthorLName VARCHAR(30), AuthorFName VARCHAR(30), FOREIGN KEY (AuthorLName, AuthorFName) REFERENCES AUTHOR (LName, Fname) ); INSERT INTO TEXTBOOK VALUES ( 'Starting Out with Java: Early Objects', 9780133776744, 30.00), ( 'NoSQL for Mere Mortals', 9780134023212, 47.99); INSERT INTO AUTHOR VALUES ( 'Sullivan', 'Dan', NULL), ( 'Gaddis', 'Tony', NULL); INSERT INTO AUTHORED VALUES ( 9780134023212, 'Sullivan', 'Dan'), ( 9780133776744, 'Gaddis', 'Tony');
The meaning of the AUTHORED table is that a tuple <I, L, F> represents that the author whose last name is L and whose first name is F wrote the textbook whose ISBN is I.
Answer the following:
TEXTBOOK
table. No value should be NULL
.Price
attribute of the TEXTBOOK
relation.TEXTBOOK
table and have the price set to the default value.EDITOR
with three attributes: Name
, Address
, and Website
. The Name
attribute should be the primary key. Insert two tuples in the EDITOR
table, making sure that one should has the Name
attribute set to “Pearson.”PUBLISHED
with two attributes: Editor
and Textbook
. The Editor
attribute should reference the EDITOR
table and the Textbook
attribute should reference the TEXTBOOK
table.Answer the following short questions based on what is in our model so far:
ISBN
be inserted in the TEXTBOOK
relation?Problem 3.17 (A database for residencies)
Consider the following code:
/* code/sql/HW_Residency.sql */ DROP SCHEMA IF EXISTS HW_RESIDENCY; CREATE SCHEMA HW_RESIDENCY; USE HW_RESIDENCY; CREATE TABLE PERSON ( FName VARCHAR(40), LName VARCHAR(40), SSN VARCHAR(11) PRIMARY KEY, Birthdate DATE ); CREATE TABLE HOUSE ( Address VARCHAR(40) PRIMARY KEY, Color ENUM ("blue", "white", "green") ); CREATE TABLE RESIDENCY ( Person VARCHAR(11), House VARCHAR(40), PrincipalResidence BOOLEAN, Status ENUM ("own", "rent", "squat", "other"), FOREIGN KEY (Person) REFERENCES PERSON (SSN), FOREIGN KEY (House) REFERENCES HOUSE (Address) ON DELETE CASCADE ); INSERT INTO PERSON VALUES ( NULL, "Doe", "000-00-0000", NULL), -- P.1 ( "Michael", "Keal", "000-00-0001", DATE "1983-02-11"), -- P.2 ( "James", "Baldwin", "000-00-0002", DATE "1967-01-01"), -- P.3 ( "Mridula", "Warrier", "000-00-0003", DATE "1990-02-11"); -- P.4 INSERT INTO HOUSE VALUES ( "123 Main St.", "blue"), -- H.1 ( "456 Second St.", "white"), -- H.2 ( "11 Third St.", "blue"); -- H.3 INSERT INTO RESIDENCY VALUES ( "000-00-0001", "123 Main St.", TRUE, "own"), -- R.1 ( "000-00-0001", "456 Second St.", FALSE, "own"), -- R.2 ( "000-00-0002", "123 Main St.", TRUE, "rent"), -- R.3 ( "000-00-0003", "456 Second St.", TRUE, "own"); -- R.4
Note that each row inserted in the PERSON
, HOUSE
and RESIDENCY
tables is given the name and noted as afterwards as a comment ("P.1, P.2, P.3, P.4, H.1"
, etc.).
Answer the following questions and problems, assuming that none of the commands in the rest of the problem are actually executed.
Pb 3.17 – Question 1
Draw the relational model corresponding to this series of commands (it is not necessary to include the state).
Pb 3.17 – Question 2
Write a command that violates the entity integrity constraint.
Pb 3.17 – Question 3
Write a command that violates the referential integrity constraint.
Pb 3.17 – Question 4
List the rows (‘“P.2, H.1, or“none””’{sqlmysql}) modified by the following statements:
UPDATE HOUSE SET COLOR = "green";
DELETE FROM RESIDENCY WHERE House LIKE "1%";
DELETE FROM HOUSE WHERE Address = "456 Second St.";
DELETE FROM PERSON WHERE Birthdate=DATE"1990-02-11";
Pb 3.17 – Question 5
Write queries that return the following information. The values returned in this set-up will be in parenthesis, but keep the queries general.
Addresses
of the houses in the system (“11 Third St., 123 Main St., 456 Second St.”
).SSN
’s of the people whose first name was not entered in the system ("000-00-0000"
)."white, blue"
)."James Baldwin"
("123 Main St."
)."James"
)."Michael Keal"’
s principal residency address ("123 Main St."
)."Michael Keal, Mridula Warrier"
).SSN
’s of the people that have the same principal residency as "James Baldwin"
("000-00-0001"
).Pb 3.17 – Question 6
Write a command that updates the SSN
of "James Baldwin"
to "000-00-0010"
. Is there any reason for this command to be rejected by the system? If yes, explain the reason.
Pb 3.17 – Question 7
Answer the following short questions from the data in our model, as it is currently:
SSN
to be any series of 11 characters?Pb 3.17 – Question 8
Consider the data currently in the RESIDENCY
table and give a possible primary key.
Pb 3.17 – Question 9
Discuss why the primary key identified from the previous question for the RESIDENCY
table is a good choice.
Solution to Problem 3.2 (Create and use a simple table in SQL)
This problem is supposed to be a straightforward application of what we studied in class. Look back at Setting Up Your Work Environment if you feel like you are stuck before referencing this solution.
Pb 3.2 – Solution to Q. 1
We simply log-in as indicated in the “Logging-in as testuser” section. Then we enter:
CREATE DATABASE HW_Address; USE HW_Address;
This creates the tables asked for in the problem.
Pb 3.2 – Solution to Q. 2
Ommiting the Extra
column, we have:
MariaDB [HW_Address]> DESC ADDRESS; +------------+-------------+------+-----+---------+ | Field | Type | Null | Key | Default | +------------+-------------+------+-----+---------+ | StreetName | varchar(15) | NO | PRI | NULL | | Number | int(11) | NO | PRI | NULL | | Habitants | int(11) | YES | | NULL | +------------+-------------+------+-----+---------+
Pb 3.2 – Solution to Q. 3
We add the foreign key, still omitting the Extra
column:
MariaDB [HW_Address]> DESC ADDRESS; +------------+-------------+------+-----+---------+ | Field | Type | Null | Key | Default | +------------+-------------+------+-----+---------+ | StreetName | varchar(15) | NO | PRI | NULL | | Number | int(11) | NO | PRI | NULL | | Habitants | int(11) | YES | MUL | NULL | +------------+-------------+------+-----+---------+
The only difference is the MUL
value, which is a bit surprising: quoting MySQL,
IfKey
isMUL
, then the column is the first column of a nonunique index in which multiple occurrences of a given value are permitted within the column.
In other words, this does not carry any information about the fact that ADDRESS.Habitants
is now a foreign key referencing NAME.ID
. A way of displaying information about that foreign key is using SHOW CREATE TABLE
:
MariaDB [HW_Address]> SHOW CREATE TABLE ADDRESS; +---------+----------------------+ | Table | Create Table +---------+----------------------+ | ADDRESS | CREATE TABLE `ADDRESS` ( `StreetName` varchar(15) NOT NULL, `Number` int(11) NOT NULL, `Habitants` int(11) DEFAULT NULL, PRIMARY KEY (`StreetName`,`Number`), KEY `Habitants` (`Habitants`), CONSTRAINT `ADDRESS_ibfk_1` FOREIGN KEY (`Habitants`) REFERENCES `NAME` (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | +---------+----------------------+ 1 row in set (0.01 sec)
Pb 3.2 – Solution to Q. 4
Pb 3.2 – Solution to Q. 5
To display the information back, we can use
SELECT * FROM NAME;
We should notice that the ID
attribute values lost their leading zeros.
Pb 3.2 – Solution to Q. 6
This syntax is better for “bulk insertion” since it allows for us to write fewer commands and to focus on the data being inserted. However, if an error occurs, then nothing gets inserted.
Pb 3.2 – Solution to Q. 7
SELECT ID FROM NAME WHERE FName = 'Samantha';
Pb 3.2 – Solution to Q. 8
This is a command that violates the entity integrity constraint:
INSERT INTO NAME VALUES ('Maria', 'Kashi', NULL);
The error message that it returns is:
ERROR 1048 (23000): Column 'ID' cannot be null
Another way of violating the entity integrity constraint is:
INSERT INTO NAME VALUES ('Maria', 'Kashi', 80);
The error message that it returns is:
ERROR 1062 (23000): Duplicate entry '80' for key 'PRIMARY'
Pb 3.2 – Solution to Q. 9
This is an UPDATE
statement that violates the entity integrity constraint:
UPDATE ADDRESS SET Habitants = 340 WHERE Number = 120;
The error message that it returns is:
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`HW_Address`.`ADDRESS`, CONSTRAINT `ADDRESS_ibfk_1` FOREIGN KEY (`Habitants`) REFERENCES `NAME` (`ID`))
Pb 3.2 – Solution to Q. 10
Here is the query that violates another type of constraint:
INSERT INTO NAME VALUE ('Hi');
The error message that it returns is:
ERROR 1136 (21S01): Column count does not match value count at row 1
The query statement violates the implicit constraint by trying to insert a row with fewer values than there are attributes in the table.
Another example of a statement that violates another type of constraint is:
INSERT INTO ADDRESS VALUES ('Maria', 'Random', 98);
This is a violation of an explicit constraint, which is that the value must match the domain (datatype) of the attribute where it is inserted. However, MySQL and MariaDB do not return an error, they simply replace 'Random'
with 0
.
Solution to Problem 3.3 (Duplicate rows in SQL)
Here is how we created our table:
CREATE SCHEMA HW_REPETITION; USE HW_REPETITION; CREATE TABLE EXAMPLE( X VARCHAR(15), Y INT );
Pb 3.3 – Solution to Q. 1
The command to add a tuple to our table is:
INSERT INTO EXAMPLE VALUES('Train', 4);
If we execute this command twice, then SQL is OK with it, and inserts the same tuple twice:
SELECT * FROM EXAMPLE;
Displays:
+-------+------+ | X | Y | +-------+------+ | Train | 4 | | Train | 4 | +-------+------+
This is an illustration of the fact that the data in a table in SQL is not a set, as opposed to a state in a relation in the relational model.
Pb 3.3 – Solution to Q. 2
The command:
ALTER TABLE EXAMPLE ADD PRIMARY KEY (X);
Should return:
ERROR 1062 (23000): Duplicate entry 'Train' for key 'PRIMARY'
We tried to declare that X
was a primary key, but SQL disagreed, since two rows have the same value for that attribute.
Pb 3.3 – Solution to Q. 3
Once the table is empty, X now qualifies as a candidate key, and can now be made a primary key. SQL stops complaining and lets us assign it as a primary key.
Pb 3.3 – Solution to Q. 4
After trying this insertion statement twice:
INSERT INTO EXAMPLE VALUES('Train', 4);
SQL refuses to insert the tuple after the second attempt:
ERROR 1062 (23000): Duplicate entry 'Train' for key 'PRIMARY'
Notice that this is exactly the same error message as before, when we tried to add the primary key while we had a duplicate row of tuples!
Solution to Problem 3.4 (Constraints on foreign keys)
PRIMARY KEY
constraint, SQL throws the following error message:
ERROR 1005 (HY000): Can't create table `HW_FK_test`.`SOURCE` (errno: 150 "Foreign key constraint is incorrectly formed")
PRIMARY KEY
with UNIQUE
does not generate any error messages.VARCHAR
(25
) with CHAR
(25
) does not generate any error messages.VARCHAR
(25
) with INT
results in this error message:
ERROR 1005 (HY000): Can't create table `HW_FK_test`.`SOURCE` (errno: 150 "Foreign key constraint is incorrectly formed")
VARCHAR
(25
) with VARCHAR
(15
) does not generate any error messages.
- The datatype of the foreign key has to be “compatible” with the datatype of the attribute to which we are referring.
- The target of the foreign key must be the primary key or have the
UNIQUE
constraint.
Solution to Problem 3.5 (Revisiting the PROF table)
Pb 3.5– Solution to Q. 1
Ignoring the LECTURE relation, we have:
Pb 3.5– Solution to Q. 2
The code is straightforward:
CREATE TABLE HW_Lecture ( NAME VARCHAR(25), Instructor VARCHAR(25), Year YEAR(4), Code CHAR(5), PRIMARY KEY (Year, Code), FOREIGN KEY (Instructor) REFERENCES PROF (LOGIN) ); INSERT INTO HW_Lecture VALUES ( 'Intro to CS', 'caubert', 2017, '1304'), ( 'Intro to Algebra', 'perdos', 2017, '1405'), ( 'Intro to Cyber', 'aturing', 2017, '1234');
However, this representation can not handle the following situations:
We come back to those short-coming in the “Reverse-Engineering” section, using more abstract tools (such as Entity Diagrams) that have not been introduced yet.
Pb 3.5– Solution to Q. 3
The statements are immediate:
DESCRIBE GRADE; SELECT * FROM GRADE;
What may be surprising is that the values for LectureCode
and LectureYear
are set to NULL
in all the tuples.
Pb 3.5– Solution to Q. 4
We use UPDATE
statements:
UPDATE GRADE SET LectureCode = '1304', LectureYear = 2017 WHERE LOGIN = 'jrakesh' AND Grade = '2.85'; UPDATE GRADE SET LectureCode = '1405', LectureYear = 2017 WHERE LOGIN = 'svlatka' OR (LOGIN = 'jrakesh' AND Grade = '3.85'); UPDATE GRADE SET LectureCode = '1234', LectureYear = 2017 WHERE LOGIN = 'aalyx' OR LOGIN = 'cjoella';
Pb 3.5– Solution to Q. 5
We refer back to the solution to Q. 1.
Pb 3.5– Solution to Q. 6
We use SELECT
statements:
SELECT LOGIN, Grade FROM GRADE WHERE Lecturecode = '1304' AND LectureYear = '2017'; SELECT DISTINCT Instructor FROM HW_Lecture WHERE Year = 2017; SELECT Name, Grade FROM STUDENT, GRADE WHERE GRADE.LectureCode = 1405 AND STUDENT.Login = GRADE.Login; SELECT Year FROM HW_Lecture WHERE Code = '1234'; SELECT Name FROM HW_Lecture WHERE Year IN ( SELECT Year FROM HW_Lecture WHERE CODE = '1234'); SELECT B.name FROM STUDENT AS A, STUDENT AS B WHERE A.Name = 'Ava Alyx' AND A.Registered > B.Registered; SELECT COUNT(DISTINCT PROF.Name) AS 'Head Teaching This Year' FROM HW_Lecture, DEPARTMENT, PROF WHERE Year = 2017 AND Instructor = Head AND Head = PROF.Login;
Solution to Problem 3.6 (TRAIN table and more advanced SQL coding)
The code below includes the answers to all of the questions for this problem:
-- Question 1: CREATE TABLE TRAIN ( Id VARCHAR(30) PRIMARY KEY, -- This line was changed. Model VARCHAR(30), ConstructionYear YEAR(4) ); -- Question 2 : CREATE TABLE CONDUCTOR ( Id VARCHAR(20), NAME VARCHAR(20), ExperienceLevel VARCHAR(20) ); ALTER TABLE CONDUCTOR ADD PRIMARY KEY (Id); -- Question 3 CREATE TABLE ASSIGNED_TO ( TrainId VARCHAR(20), ConductorId VARCHAR(20), Day DATE, PRIMARY KEY (TrainId, ConductorId), FOREIGN KEY (TrainId) REFERENCES TRAIN (Id), -- This line was changed FOREIGN KEY (ConductorId) REFERENCES CONDUCTOR (Id) -- This line was changed ); -- Question 4: /* We insert more than one tuple, to make the SELECT statements that follow easier to test and debug. */ INSERT INTO TRAIN VALUES ( 'K-13', 'SurfLiner', 2019), ( 'K-12', 'Regina', 2015); INSERT INTO CONDUCTOR VALUES ( 'GP1029', 'Bill', 'Junior'), ( 'GP1030', 'Sandrine', 'Junior'); INSERT INTO ASSIGNED_TO VALUES ( 'K-13', 'GP1029', DATE '2015/12/14'), ( 'K-12', 'GP1030', '20120909'); -- Question 5: UPDATE CONDUCTOR SET ExperienceLevel = 'Senior' WHERE Id = 'GP1029'; -- Question 6: -- 1. SELECT Id FROM TRAIN; -- 2. SELECT Name FROM CONDUCTOR WHERE ExperienceLevel = 'Senior'; -- 3. SELECT ConstructionYear FROM TRAIN WHERE Model = 'SurfLiner' OR Model = 'Regina'; -- 4. SELECT ConductorId FROM ASSIGNED_TO WHERE TrainId = 'K-13' AND Day = '2015/12/14'; -- 5. SELECT Model FROM TRAIN, ASSIGNED_TO WHERE ConductorID = 'GP1029' AND TrainId = TRAIN.ID;
Solution to Problem 3.7 (Read, correct, and write SQL statements for the COFFEE database)
Solution to Question 1:
The answers to the rest of the questions are in the following code:
/* code/sql/HW_DBCoffee.sql */ -- Question 2: START TRANSACTION; INSERT INTO CUSTOMER VALUES ( 005, 'Bob Hill', NULL, 001); INSERT INTO COFFEE VALUES ( 002, "Peru", "Decaf", 3.00); -- The following statement raises an error. -- INSERT INTO PROVIDER -- VALUES (NULL, "contact@localcof.com"); -- ERROR 1048 (23000) at line 68: Column 'Name' -- cannot -- be -- null INSERT INTO SUPPLY VALUES ( "Johns & Co.", 121); -- The following statement raises an error. -- -INSERT INTO SUPPLY -- VALUES ("Coffee Unl.", 311, 221); -- ERROR 1136 (21S01): Column count doesn't match -- value -- count at row 1 -- Rest the changes: ROLLBACK; -- Question 3: START TRANSACTION; UPDATE CUSTOMER SET FavCoffee = 001 WHERE CardNo = 001; -- Rows matched: 1 Changed: 1 Warnings: 0 SELECT * FROM CUSTOMER; ROLLBACK; START TRANSACTION; UPDATE COFFEE SET TypeOfRoast = 'Decaf' WHERE Origin = 'Brazil'; -- Rows matched: 2 Changed: 2 Warnings: 0 SELECT * FROM COFFEE; ROLLBACK; START TRANSACTION; UPDATE PROVIDER SET Name = 'Coffee Unlimited' WHERE Name = 'Coffee Unl.'; -- Rows matched: 1 Changed: 1 Warnings: 0 SELECT * FROM PROVIDER; SELECT * FROM SUPPLY; ROLLBACK; START TRANSACTION; UPDATE COFFEE SET PricePerPound = 10.00 WHERE PricePerPound > 10.00; -- Rows matched: 1 Changed: 1 Warnings: 0 SELECT * FROM COFFEE; ROLLBACK; -- Question 4: START TRANSACTION; DELETE FROM CUSTOMER WHERE Name LIKE '%S%'; -- Query OK, 2 rows affected (0.01 sec) SELECT * FROM CUSTOMER; ROLLBACK; START TRANSACTION; DELETE FROM COFFEE WHERE Ref = 001; -- Query OK, 1 row affected (0.00 sec) SELECT * FROM COFFEE; SELECT * FROM SUPPLY; ROLLBACK; START TRANSACTION; DELETE FROM SUPPLY WHERE Provider = 'Coffee Unl.' AND Coffee = '001'; -- Query OK, 1 row affected (0.00 sec) SELECT * FROM SUPPLY; ROLLBACK; START TRANSACTION; DELETE FROM PROVIDER WHERE Name = 'Johns & Co.'; -- Query OK, 1 row affected (0.00 sec) SELECT * FROM PROVIDER; SELECT * FROM SUPPLY; ROLLBACK; -- Question 5: -- 1. SELECT Origin FROM COFFEE WHERE TypeOfRoast = 'Dark'; -- 2. SELECT FavCoffee FROM CUSTOMER WHERE Name LIKE 'Bob%'; -- 3. SELECT Name FROM PROVIDER WHERE Email IS NULL; -- 4. SELECT COUNT(*) FROM SUPPLY WHERE Provider = 'Johns & Co.'; -- 5. SELECT Provider FROM COFFEE, SUPPLY WHERE TypeOfRoast = 'Dark' AND Coffee = Ref;
Solution to Problem 3.8 (Write select queries for the DEPARTMENT table)
SELECT EMPLOYEE.Name FROM EMPLOYEE, DEPARTMENT WHERE DEPARTMENT.Name = "Storage" AND EMPLOYEE.Department = DEPARTMENT.ID;
SELECT Name FROM EMPLOYEE WHERE Hired <= ALL ( SELECT Hired FROM EMPLOYEE WHERE Hired IS NOT NULL);
SELECT EMPLOYEE.Name FROM EMPLOYEE, DEPARTMENT WHERE Hired <= ALL ( SELECT Hired FROM EMPLOYEE WHERE Hired IS NOT NULL AND DEPARTMENT.Name = "Storage" AND EMPLOYEE.Department = DEPARTMENT.ID) AND DEPARTMENT.Name = "Storage" AND EMPLOYEE.Department = DEPARTMENT.ID;
Solution to Problem 3.11 (Write select queries for a variation of the COMPUTER table)
SELECT Model FROM COMPUTER WHERE ID = 'A'; SELECT TYPE FROM PERIPHERAL WHERE ID = '14'; SELECT Model FROM PERIPHERAL WHERE TYPE = 'printer'; SELECT Model FROM PERIPHERAL WHERE Model LIKE 'IBM%'; SELECT Model FROM PERIPHERAL, CONNEXION WHERE Computer = 'A' AND Peripheral = PERIPHERAL.ID; SELECT COUNT(Computer) FROM CONNEXION, COMPUTER WHERE Model = 'Apple IIc Plus' AND Computer = COMPUTER.ID;
Solution to Problem 3.10 (Write select queries for the SocialMedia schema)
/* code/sql/HW_SocialMedia.sql */ -- … the title of all the videos ("My first video!", "My -- second video!", "My vacations"). SELECT TITLE FROM VIDEO; -- … the release date of the video whose title is "My first -- video!" ("2020-02-02"). SELECT Released FROM VIDEO WHERE Title = "My first video!"; -- … the ID of the account(s) where the "Name" attribute -- was not given ("2"). SELECT ID FROM ACCOUNT WHERE Name IS NULL; -- … the ID of the videos whose title contains the word -- "video" ("10", "20"). SELECT ID FROM VIDEO WHERE TITLE LIKE "%video%"; -- or SELECT ID FROM VIDEO WHERE Title REGEXP 'video'; -- … the number of thumbs up for the video with title "My -- vacations" ("1"). SELECT COUNT(*) FROM THUMBS_UP, VIDEO WHERE VIDEO.Title = "My vacations" AND VIDEO.ID = THUMBS_UP.Video; -- … the title of the oldest video ("My first video!"). SELECT Title FROM VIDEO WHERE Released <= ALL ( SELECT Released FROM VIDEO); -- or SELECT Title FROM VIDEO WHERE Released = ( SELECT Min(Released) FROM VIDEO); -- or even SELECT Title FROM VIDEO ORDER BY Released ASC LIMIT 1; -- … the names of the accounts who gave a thumbs up to the -- video with id 30 ("Bob Ross"). SELECT Name FROM ACCOUNT, THUMBS_UP WHERE THUMBS_UP.Video = 30 AND THUMBS_UP.Account = ACCOUNT.ID; -- … the ID of the account with the greatest number of -- subscribers ("2"). SELECT Subscribed FROM SUBSCRIBE GROUP BY Subscribed ORDER BY COUNT(Subscriber) DESC LIMIT 1;
Solution to Problem 3.12 (Improving a role-playing game with a relational model)
The following solves all the issues with your friend’s code design. As quests only rarely provide a special item, we added a relation to avoid having a Special-item
in the QUEST
table since that would be NULL
too often.
Solution to Problem 3.13 (A simple database for books)
Pb 3.13 – Solution to Q. 1
Here are possible ways of getting the required information:
Title
of all the books:
SELECT Title FROM BOOK;
Name
of the publishers.
SELECT DISTINCT Name FROM PUBLISHER;
Titles
and Published
dates of the books published since January 31, 2012.
SELECT Title, Published FROM BOOK WHERE Published > DATE'20120131';
"Gallimard"
(from any city).
SELECT FName, LName FROM AUTHOR, BOOK WHERE PublisherName = "Gallimard" AND Author = ID;
"New-York"
.
SELECT FName, LName FROM AUTHOR, BOOK WHERE NOT PublisherCity= "New-York" AND Author = ID;
ID
of the authors who published a book whose name starts with "Where"
.
SELECT Author FROM BOOK WHERE Title LIKE 'Where%';
SELECT SUM(Pages) FROM BOOK;
"Wolve"
.
SELECT MAX(PAGES) FROM BOOK, AUTHOR WHERE LName = "Wolve" AND Author = ID;
SELECT Title FROM BOOK WHERE Published >= DATE'18010101' AND Published <= DATE'19001231';
Pb 3.13 – Solution to Q. 2
We can use the following command:
UPDATE BOOK SET Title = "BANNED" WHERE Author = 3;
The pair (title, publication date) is the primary key in the BOOK
table, so if the author whose ID is 3 has published more than one book at a particular date, then our update will be rejected, as applying it would result in violating the entity integrity constraint.
Pb 3.13 – Solution to Q. 3
To delete the required rows, we can use:
DELETE FROM BOOK WHERE Author = 3; DELETE FROM AUTHOR WHERE ID = 3;
Note that trying to delete the rows in the AUTHOR
table before deleting the rows in the BOOK
table could cause a referential integrity violation, since the BOOK
table has a foreign key assigned to the AUTHOR
table’s Id
field.
Pb 3.13 – Solution to Q. 4
We could design that table as follows:
CREATE TABLE AWARD( Name VARCHAR(30), Year DATE, BookTitle VARCHAR(30), BookPubDate DATE, FOREIGN KEY (BookTitle, BookPubDate) REFERENCES BOOK(Title, Published), PRIMARY KEY (Name, Year) );
Note that there is no need to store the name of the author in this relation: this information can be recovered by looking in the BOOK
table for the name of the author of the awarded book.
Pb 3.13 – Solution to Q. 5
We obtain something as follows:
Note that having two attributes as the primary key makes the referencing of foreign keys more cumbersome.
Pb 3.13 – Solution to Q. 6
Two of the flaws that come to mind are:
BOOK
relation: two books with the same title cannot be published on the same day, which is a serious limitation. Using a primary key like ISBN would be much more appropriate.IS_THE_AUTHOR_OF
and PUBLISHED_BY
, that “maps” the book’s ISBN with author’s or editor’s primary key.Solution to Problem 3.14 (A database for website certificates)
The solution can be read from the following code:
/* code/sql/HW_Certificate.sql */ DROP SCHEMA IF EXISTS HW_Certificate; CREATE SCHEMA HW_Certificate; USE HW_Certificate; /* SN = Serial Number CN = Common Name CA = Certificate Authority */ CREATE TABLE ORGANIZATION ( SN VARCHAR(30) PRIMARY KEY, CN VARCHAR(30) ); CREATE TABLE CA ( SN VARCHAR(30) PRIMARY KEY, CN VARCHAR(30), Trusted BOOL ); CREATE TABLE CERTIFICATE ( SN VARCHAR(30) PRIMARY KEY, CN VARCHAR(30) NOT NULL, Org VARCHAR(30) NOT NULL, Issuer VARCHAR(30), Valid_Since DATE, Valid_Until DATE, FOREIGN KEY (Org) REFERENCES ORGANIZATION (SN) ON DELETE CASCADE, FOREIGN KEY (Issuer) REFERENCES CA (SN) ); INSERT INTO ORGANIZATION VALUES ( '01', 'Wikimedia Foundation'), ( '02', 'Free Software Foundation'); INSERT INTO CA VALUES ( 'A', "Let's Encrypt", TRUE), ( 'B', 'Shady Corp.', FALSE), ( 'C', 'NewComer Ltd.', NULL); INSERT INTO CERTIFICATE VALUES ( 'a', '*.wikimedia.org', '01', 'A', 20180101, 20200101), ( 'b', '*.fsf.org', '02', 'A', 20180101, 20191010), ( 'c', '*.shadytest.org', '02', 'B', 20190101, 20200101), ( 'd', '*.wikipedia.org', '01', 'C', 20200101, 20220101); -- CN of all certificates. SELECT CN FROM CERTIFICATE; -- (*.wikimedia.org | *.fsf.org | *.shadytest.org | -- *.wikipedia.org) -- The SN of the organizations whose CN contains -- "Foundation" SELECT SN FROM ORGANIZATION WHERE CN LIKE "%Foundation%"; -- (01 | 02) -- The CN and expiration date of all the -- certificates -- that -- expired (assuming we are the 6th of December -- 2019). SELECT CN, Valid_Until FROM CERTIFICATE WHERE Valid_Until < DATE '20191206'; -- (*.fsf.org, 2019-10-10) -- The CN of the CA that are not trusted. SELECT CN FROM CA WHERE Trusted IS NOT TRUE; -- (Shady Corp. | NewComer Ltd.) -- The CN of the certificates that are signed by a -- CA -- that -- is not trusted. SELECT CERTIFICATE.CN FROM CERTIFICATE, CA WHERE Trusted IS NOT TRUE AND CA.SN = CERTIFICATE.Issuer; -- (Shady Corp. | NewComer Ltd.) -- The number of certificates signed by the CA whose -- CN -- is -- "Let's encrypt". SELECT COUNT(CERTIFICATE.SN) AS "Number of certificates signed by Let's encrypt" FROM CERTIFICATE, CA WHERE CERTIFICATE.Issuer = CA.SN AND CA.CN = "Let's encrypt"; -- (2) -- A table listing the CN of the organizations along -- with -- the CN of their certificates. SELECT ORGANIZATION.CN AS Organization, CERTIFICATE.CN AS Certificate FROM ORGANIZATION, CERTIFICATE WHERE CERTIFICATE.Org = ORGANIZATION.SN; -- ( Wikimedia Foundation, *.wikimedia.org | Free Software -- Foundation, *.fsf.org | Free Software Foundation , -- *.shadytest.org | Wikimedia Foundation , -- *.wikipedia.org -- ) /* DELETE FROM CA WHERE SN = 'A'; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`HW_Certificate`.`CERTIFICATE`, CONSTRAINT `CERTIFICATE_ibfk_2` FOREIGN KEY (`Issuer`) REFERENCES `CA` (`SN`)) => Rejected, because an entry in CERTIFICATE references this tuple (referential integrity constraint). UPDATE ORGANIZATION SET CN = "FSF" WHERE SN = '02'; Query OK, 1 row affected (0.008 sec) Rows matched: 1 Changed: 1 Warnings: 0 => Ok, change ('02', 'Free Software Foundation'); into ('02', 'FSF'); in ORGANIZATION MariaDB [HW_Certificate]> UPDATE ORGANIZATION SET SN = "01" WHERE SN = '02'; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`HW_Certificate`.`CERTIFICATE`, CONSTRAINT `CERTIFICATE_ibfk_1` FOREIGN KEY (`Org`) REFERENCES `ORGANIZATION` (`SN`) ON DELETE CASCADE) => Rejected, because an entry in CERTIFICATE references this tuple (referential integrity constraint). This query would have been rejected even if this tuple was not referenced, since it would have violated the entity integrity constraint. DELETE FROM ORGANIZATION; => Deletes all the content of organization and of certificate. */
Solution to Problem 3.15 (A simple database for published pieces of work)
Pb 3.15 – Solution to Q. 1
The relational model for this code is:
Pb 3.15 – Solution to Q. 2
The solution to the next questions can be read from the following code:
/* code/sql/HW_Work.sql */ /* Determine if the following insertion statements would violate the the Entity integrity constraint, the Referential integrity constraint, if there would be some Other kind of error, or if it would result in successful insertion. */ START TRANSACTION; -- We don't want to perform the actual insertions. INSERT INTO EBOOK VALUES ( 0, NULL, 20180101, 0); /* Query OK, 1 row affected (0.003 sec) So, "Successful insertion". */ -- The following statement raises an error. -- INSERT INTO AUTHOR -- VALUES ("Mary B.", "mb@fai.fr", NULL); /* ERROR 1136 (21S01): Column count doesn't match value count at row 1 So, "Other kind of error". */ -- The following statement raises an error. -- INSERT INTO WORK -- VALUES ("My Life", "Claude A."); /* ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`HW_EXAM_1`.`WORK`, CONSTRAINT `WORK_ibfk_1` FOREIGN KEY (`Author`) REFERENCES `AUTHOR` (`Name`) ON DELETE CASCADE ON UPDATE CASCADE) So, "Referential integrity constraint" */ INSERT INTO BOOK VALUES ( 00000000, NULL, DATE '20001225', 90.9); /* Query OK, 1 row affected (0.000 sec) So, "Successful insertion". */ -- The following statement raises an error. -- INSERT INTO AUTHOR -- VALUES ("Virginia W.", "alt@isp.net"); /* ERROR 1062 (23000): Duplicate entry 'Virginia W.' for key 'PRIMARY' So, "Entity integrity constraint". */ ROLLBACK; -- We go back to the previous state. /* List the rows (i.e., A.2, W.1, etc.) modified by the following statements (be careful about the conditions on foreign keys!): */ START TRANSACTION; -- We don't want to perform the following operations. UPDATE AUTHOR SET Email = 'Deprecated' WHERE Email LIKE '%isp.net'; /* Query OK, 2 rows affected (0.010 sec) Rows matched: 2 Changed: 2 Warnings: 0 This changed A.1 and A.2 */ UPDATE WORK SET Title = "How to eat" WHERE Title = "What to eat"; /* Rows matched: 1 Changed: 1 Warnings: 0 SQL returns only the number of row changed in the WORK table, but other rows have been changed as well. This changed W.1, B.1, E.1. */ -- The following statement raises an error. -- DELETE FROM WORK; /* ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`HW_EXAM_1`.`BOOK`, CONSTRAINT `BOOK_ibfk_1` FOREIGN KEY (`Work`) REFERENCES `WORK` (`Title`) ON UPDATE CASCADE) Does not change any row. */ -- The following statement raises an error. -- DELETE FROM AUTHOR -- WHERE Name = "Virginia W."; /* ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`HW_EXAM_1`.`BOOK`, CONSTRAINT `BOOK_ibfk_1` FOREIGN KEY (`Work`) REFERENCES `WORK` (`Title`) ON UPDATE CASCADE) Does not change any row. */ ROLLBACK; -- We go back to the previous state. -- You can now assume that there is more data than -- what -- we -- inserted, if that helps you. Write a command that -- selects -- … -- We insert some dummy values for this next part. INSERT INTO WORK VALUES ( "My Life", "Paul B."), ( "What to eat, 2", "Virginia W."); INSERT INTO BOOK VALUES ( 15355627, "My Life", DATE '20180219', 15.00), ( 12912912, "What to eat, 2", DATE '20200101', 13); INSERT INTO EBOOK VALUES ( 15150628, "My Life", DATE '20190215', 10.89), ( 42912912, "What to eat, 2", DATE '20200115', 12); -- … the price of all the ebooks. SELECT Price FROM EBOOK; -- … the (distinct) names of the authors who have authored -- a piece of work. SELECT DISTINCT Author FROM WORK; -- … the name of the authors using fai.fr for their email. SELECT Name FROM AUTHOR WHERE Email LIKE '%fai.fr'; -- … the price of the ebooks published after 2018. SELECT Price FROM BOOK WHERE Published >= 20180101; /* Note that SELECT Price FROM BOOK WHERE Published > 2018; would return all the prices, along with a warning: Incorrect datetime value: '2018' */ -- … the price of the most expensive book. SELECT MAX(Price) FROM BOOK; -- … the number of pieces of work written by the author -- whose name is “Virginia W.”. SELECT COUNT(*) FROM WORK WHERE WORK.Author = "Virginia W."; -- … the email of the author who wrote the piece of work -- called “My Life”. SELECT Email FROM AUTHOR, WORK WHERE WORK.Title = "My Life" AND WORK.Author = AUTHOR.Name; -- the isbn(s) of the book containing a work written by the -- author whose email is "vw@isp.net". SELECT ISBN FROM BOOK, WORK, AUTHOR WHERE AUTHOR.Email = "vw@isp.net" AND WORK.Author = AUTHOR.Name AND BOOK.Work = WORK.Title; /* Write a command that updates the title of all the pieces of work written by the author whose name is “Virginia W. to”BANNED". Is there any reason for this command to be rejected by the system? If yes, explain which one. */ -- The following statement raises an error. /* UPDATE WORK SET Title = "BANNED" WHERE Author = "Virginia W."; */ /* Gives an error, since "Title" is the primary key in the WORK table, and Virginia W. has authored two pieces of work or more, they are both given the title "BANNED", which violates the unicity of value in primary keys. */ -- Write one or multiple commands that would delete the work -- whose title is “My Life”, as well as all of -- the -- books -- and ebooks versions of it. -- The following statement raises an error. -- DELETE FROM WORK -- WHERE Title = "My Life"; /* Fails ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`HW_EXAM_1`.`BOOK`, CONSTRAINT `BOOK_ibfk_1` FOREIGN KEY (`Work`) REFERENCES `WORK` (`Title`) ON UPDATE CASCADE) */ -- We have to first delete the corresponding publications: DELETE FROM BOOK WHERE WORK = "My Life"; DELETE FROM EBOOK WHERE WORK = "My Life"; -- And then we can delete the work: DELETE FROM WORK WHERE Title = "My Life"; /* And, no, we cannot delete "simply" from multiple tables in one command. Some workaround exists, cf. https://stackoverflow.com/q/1233451/ . */
Pb 3.15 – Solution to Q. 3
Finally, to answer the last question, here is a list of the possible limitations:
AUTHOR
and WORK
tables) is not a good idea: we cannot have two authors with the same name or two pieces of work with the same title!BOOK
and the EBOOK
tables are going to be the same, then we should probably have only one table called PUBLICATION
with a boolean to indicate whenever the publication is digital or on paper.ON DELETE CASCADE
and ON DELETE RESTRICT
is not really justified and makes the tables harder to use. We should have used the same update policy on both tables.Solution to Problem 3.16 (A simple database for authors of textbooks)
The answers can be found in the following snippet:
/* code/sql/HW_TEXTBOOK_AUTHORED_SOL.sql */ /* EXERCISE 1 Write a command that updates the email address of 'Gaddis', 'Tony' to "tgaddis@pearson.com" */ UPDATE AUTHOR SET Email = "tgaddis@pearson.com" WHERE LName = 'Gaddis' AND FName = 'Tony'; /* You can use SELECT * FROM AUTHOR; to check that the modification took place. */ /* EXERCISE 2 Write a command that inserts the textbook of your choice in the TEXTBOOK table. No value should be NULL, but you can invent the values. */ INSERT INTO TEXTBOOK VALUES ( 'Fundamentals of Database Systems', 9780133970777, 165.89); /* You can use SELECT * FROM TEXTBOOK; to check that the insertion was correctly made. */ /* EXERCISE 3 Write a command that makes 'Gaddis', 'Tony' the author of the textbook you just added to our database. */ INSERT INTO AUTHORED VALUES ( 9780133970777, 'Gaddis', 'Tony'); /* You can use SELECT * FROM AUTHORED; to check that the insertion was correctly made. EXERCISE 4 Write a command that makes "0.01" becomes the default value for the Price attribute of the TEXTBOOK relation. */ ALTER TABLE TEXTBOOK ALTER COLUMN Price SET DEFAULT 0.01; /* You can use DESCRIBE TEXTBOOK; to check that the Price attribute now has a default value. EXERCISE 5 Write a command that insert a textbook of your choice in the TEXTBOOK table, with the price set to the default value. */ INSERT INTO TEXTBOOK VALUES ( 'Proof Theory', 9780486490731, DEFAULT); /* You can use SELECT * FROM TEXTBOOK; to check that the insertion was correctly made. EXERCISE 6 Write a command that creates a table called EDITOR with 3 attributes, "Name", "Address" and "Website". The "Name" attribute should be the primary key. Then, insert two tuples in the EDITOR table, one should have the "Name" attribute set to "Pearson". */ CREATE TABLE EDITOR ( NAME VARCHAR(30) PRIMARY KEY, Address VARCHAR(255), Website VARCHAR(100) ); INSERT INTO EDITOR VALUES ( 'Pearson', NULL, 'http://pearsoned.com/'), ( 'Dover', NULL, 'https://store.doverpublications.com/'); /* You can use DESCRIBE EDITOR; to check that the table was actually created, and SELECT * FROM EDITOR; to check that the values were inserted. EXERCISE 7 Write a command that creates a table called PUBLISHED with 2 attributes, "Editor", and "Textbook". The "Editor" attribute should references the EDITOR table, and the "Textbook" attribute should reference the TEXTBOOK table. */ CREATE TABLE PUBLISHED ( Editor VARCHAR(30), FOREIGN KEY (Editor) REFERENCES EDITOR (NAME), Textbook CHAR(13), FOREIGN KEY (Textbook) REFERENCES TEXTBOOK (ISBN) ); /* You can use DESCRIBE PUBLISHED; to check that the table was actually created. EXERCISE 8 Write a command that makes "Pearson" the editor of the textbook whose ISBN is 9780133776744. */ INSERT INTO PUBLISHED VALUES ( "Pearson", 9780133776744); /* You can use SELECT * FROM PUBLISHED; to check that the table was actually created. EXERCISE 9 Answer the following short questions. In our model, as it is, … Can an author have authored more than one textbook? Yes. Can a textbook have more than one author? Yes. Can a textbook without ISBN be inserted in the TEXTBOOK relation? No, unless you create a "dummy" (fake) value for it, like 0000000000000, but this value can be used only once, since ISBN is the primary key. Can the price of a textbook be negative? Yes. We can actually test it: INSERT INTO TEXTBOOK VALUES ("Test", 0000000000000, -1); Can two author have the same first and last name? No. The query: INSERT INTO AUTHOR VALUES ('Smith', 'Bob', NULL), ('Smith', 'Bob', NULL); returns ERROR 1062 (23000): Duplicate entry 'Smith-Bob' for key 'PRIMARY' Can two textbooks have the same title? Yes, as long as they have different ISBN. The command INSERT INTO TEXTBOOK VALUES ("Test", 0000000000001, NULL), ("Test", 0000000000002, NULL); is processed just fine. Can two editiors have the same address? Yes. The command: INSERT INTO EDITOR VALUES ("Test 1", "123 Main St.", NULL), ("Test 2", "123 Main St.", NULL); is processed just fine. */