nick@artemis:~$ cd 3420_s25
nick@artemis:~/3420_s25$ mysql companys25
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

BWelcome to the MariaDB monitor.  Commands end with ; or \g.
BBYour MariaDB connection id is 310617
Server version: 10.3.38-MariaDB-0+deb10u1 Debian 10

BBCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

BBType 'help;' or '\h' for help. Type '\c' to clear the current input statement.

BMariaDB [companys25]> show tables;
+----------------------+
| Tables_in_companys25 |
+----------------------+
| Department	       |
| DepartmentLocations  |
| Dependent	       |
| Employee	       |
| EmployeeHours	       |
| Project	       |
| WorksOn	       |
+----------------------+
B7 rows in set (0.001 sec)
BB
BMariaDB [companys25]> describe Employee;
+----------+------------------+------+-----+---------+-------+
| Field	   | Type	      | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| Fname	   | varchar(30)      | YES  |	   | NULL    |	     |
| Minit	   | char(1)	      | YES  |	   | NULL    |	     |
| Lname	   | varchar(30)      | NO   |	   | NULL    |	     |
| SSN	   | char(12)	      | NO   | PRI | NULL    |	     |
| DOB	   | date	      | YES  |	   | NULL    |	     |
| Address  | varchar(255)     | YES  |	   | NULL    |	     |
| Salary   | int(10) unsigned | YES  |	   | NULL    |	     |
| SuperSSN | char(12)	      | YES  | MUL | NULL    |	     |
| Dnumber  | int(11)	      | NO   | MUL | NULL    |	     |
+----------+------------------+------+-----+---------+-------+
B9 rows in set (0.001 sec)
BB
wSalaryBintounsigned,>DateChangedldatetimeCdefault(current_timestamp);lary int,unsigned, Ne
BQuery OK, 0 rows affected (0.004 sec)
BB
BMariaDB [companys25]> showwcreate SalaryChanges;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SalaryChanges' at line 1
BBMariaDB [companys25]> show create tableySalaryChanges;
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table		| Create Table																																				  |
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SalaryChanges | CREATE TABLE `SalaryChanges` (
  `SSN` char(12) DEFAULT NULL,
  `OldSalary` int(10) unsigned DEFAULT NULL,
  `NewSalary` int(10) unsigned DEFAULT NULL,
  `DateChanged` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci `ENCRYPTION_KEY_ID`=100 |
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
B1 row in set (0.001 sec)
BB
BMariaDB [companys25]>
[1]+  Stopped		      mysql companys25
nick@artemis:~/3420_s25$ ls -l
total 76
-rw-r--r-- 1 nick nick	 177 Mar 10 13:47 all.sql
-rw-r--r-- 1 nick nick	 830 Mar  3 14:04 company_tables_postfk.sql
-rw-r--r-- 1 nick nick	2258 Mar  5 13:51 company_tables.sql
-rw-r--r-- 1 nick nick	1046 Mar 26 12:43 data.sql
-rw-r--r-- 1 nick nick	1283 Mar  5 14:02 department.sql
-rw-r--r-- 1 nick nick 19315 Mar  5 13:50 employee2.txt.sql
-rw-r--r-- 1 nick nick 19315 Mar  5 13:55 employee.sql
-rw-rw-r-- 1 nick nick	  13 Mar 12 15:01 hello.sql
-rw-r--r-- 1 nick nick	 978 Mar  5 14:08 project.sql
-rw-r--r-- 1 nick nick	 351 Mar 26 14:09 storedprocedures.sql
-rw-r--r-- 1 nick nick	   0 Mar 26 12:58 triggers.sql
-rw-r--r-- 1 nick nick	 340 Mar 26 13:58 views.sql
nick@artemis:~/3420_s25$ fg
mysql companys25

MariaDB [companys25]> source triggers.sql;
BQuery OK, 0 rows affected, 1 warning (0.000 sec)
BB
BBQuery OK, 0 rows affected (0.002 sec)
BB
BMariaDB [companys25]> select * from Employee order by rand() limit 1;
+----------+-------+---------+-------------+------------+----------------+--------+-------------+---------+
| Fname	   | Minit | Lname   | SSN	   | DOB	| Address	 | Salary | SuperSSN	| Dnumber |
+----------+-------+---------+-------------+------------+----------------+--------+-------------+---------+
| Cécilia  | H	   | Hebborn | 747-68-5804 | 2003-05-01 | 9 Dennis Court |  66990 | 668-57-3372 |	3 |
+----------+-------+---------+-------------+------------+----------------+--------+-------------+---------+
B1 row in set (0.002 sec)
BB
BMariaDB [companys25]> show triggers
    -> ;
+----------------+--------+----------+------------------------------------------------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| Trigger	 | Event  | Table    | Statement										      | Timing | Created		| sql_mode										    | Definer	     | character_set_client | collation_connection | Database Collation |
+----------------+--------+----------+------------------------------------------------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| OnSalaryChange | UPDATE | Employee | INSERT INTO SalaryChanges (SSN, OldSalary, NewSalary)
VALUES (NEW.SSN, OLD.Salary, NEW.Salary) | BEFORE | 2025-04-02 13:34:27.69 | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | nick@localhost | utf8mb4	       | utf8mb4_general_ci   | utf8mb4_general_ci |
+----------------+--------+----------+------------------------------------------------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
B1 row in set (0.002 sec)
BB
;MariaDB [companys25]> UPDATE Employee SET Salary = Salary * 1.10 WHERE SSN = "747-68-5804"
BQuery OK, 1 row affected (0.002 sec)
BBRows matched: 1  Changed: 1  Warnings: 0
BB
BMariaDB [companys25]> select * from Employee where SSNa=y"747-68-5804";
+----------+-------+---------+-------------+------------+----------------+--------+-------------+---------+
| Fname	   | Minit | Lname   | SSN	   | DOB	| Address	 | Salary | SuperSSN	| Dnumber |
+----------+-------+---------+-------------+------------+----------------+--------+-------------+---------+
| Cécilia  | H	   | Hebborn | 747-68-5804 | 2003-05-01 | 9 Dennis Court |  73689 | 668-57-3372 |	3 |
+----------+-------+---------+-------------+------------+----------------+--------+-------------+---------+
B1 row in set (0.001 sec)
BB
BMariaDB [companys25]> select * from SalaryChanges;
+-------------+-----------+-----------+---------------------+
| SSN	      | OldSalary | NewSalary | DateChanged	    |
+-------------+-----------+-----------+---------------------+
| 747-68-5804 |	    66990 |	73689 | 2025-04-02 13:35:32 |
+-------------+-----------+-----------+---------------------+
B1 row in set (0.001 sec)
BB
;selectE*pfromeEmployeeawhereSSSNr=m"747-68-5804";SNS=N"747-68-5804";4";

+----------+-------+---------+-------------+------------+----------------+--------+-------------+---------+
| Fname	   | Minit | Lname   | SSN	   | DOB	| Address	 | Salary | SuperSSN	| Dnumber |
+----------+-------+---------+-------------+------------+----------------+--------+-------------+---------+
| Cécilia  | H	   | Hebborn | 747-68-5804 | 2003-05-01 | 9 Dennis Court |  73689 | 668-57-3372 |	3 |
+----------+-------+---------+-------------+------------+----------------+--------+-------------+---------+
B1 row in set (0.000 sec)
BB
BMariaDB [companys25]> update Employee set Dnumber = 4 where SSN = "";7-68-58044
BQuery OK, 1 row affected (0.002 sec)
BBRows matched: 1  Changed: 1  Warnings: 0
BB
selectD* frompEmployee update Employee set Dnumber = 4 where SSN = "747-68-5804";
+----------+-------+---------+-------------+------------+----------------+--------+-------------+---------+
| Fname	   | Minit | Lname   | SSN	   | DOB	| Address	 | Salary | SuperSSN	| Dnumber |
+----------+-------+---------+-------------+------------+----------------+--------+-------------+---------+
| Cécilia  | H	   | Hebborn | 747-68-5804 | 2003-05-01 | 9 Dennis Court |  73689 | 668-57-3372 |	4 |
+----------+-------+---------+-------------+------------+----------------+--------+-------------+---------+
B1 row in set (0.001 sec)
BB
SalaryChanges;Employeeumberc= 4 from Employee where SSN = "747-68-5804";
+-------------+-----------+-----------+---------------------+
| SSN	      | OldSalary | NewSalary | DateChanged	    |
+-------------+-----------+-----------+---------------------+
| 747-68-5804 |	    66990 |	73689 | 2025-04-02 13:35:32 |
| 747-68-5804 |	    73689 |	73689 | 2025-04-02 13:36:40 |
+-------------+-----------+-----------+---------------------+
B2 rows in set (0.001 sec)
BB
;;DATEeEmployeeSSET=Salary6=-Salarym*E1.10yWHEREeSSNS=N"747-68-5804";4";
ourcertriggers.sql;laryChanges; rand() limit 1;
BQuery OK, 0 rows affected (0.001 sec)
BB
BBQuery OK, 0 rows affected (0.003 sec)
BB
upd3teDEmployeenset5Dnumberc= 4rfromrEmployeeawhere SSN = "747-68-5804";
BQuery OK, 1 row affected (0.002 sec)
BBRows matched: 1  Changed: 1  Warnings: 0
BB
selectD*rfromrEmployeeawhereeSSNp=o"747-68-5804";r = 3 where SSN = "747-68-5804";
+----------+-------+---------+-------------+------------+----------------+--------+-------------+---------+
| Fname	   | Minit | Lname   | SSN	   | DOB	| Address	 | Salary | SuperSSN	| Dnumber |
+----------+-------+---------+-------------+------------+----------------+--------+-------------+---------+
| Cécilia  | H	   | Hebborn | 747-68-5804 | 2003-05-01 | 9 Dennis Court |  73689 | 668-57-3372 |	3 |
+----------+-------+---------+-------------+------------+----------------+--------+-------------+---------+
B1 row in set (0.000 sec)
BB
selectD*rfromrSalaryChanges;=SSNf=o"747-68-5804";re SSN = "747-68-5804";
+-------------+-----------+-----------+---------------------+
| SSN	      | OldSalary | NewSalary | DateChanged	    |
+-------------+-----------+-----------+---------------------+
| 747-68-5804 |	    66990 |	73689 | 2025-04-02 13:35:32 |
| 747-68-5804 |	    73689 |	73689 | 2025-04-02 13:36:40 |
+-------------+-----------+-----------+---------------------+
B2 rows in set (0.000 sec)
BB
BMariaDB [companys25]> show create table SalaryChanges;
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table		| Create Table																																				  |
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SalaryChanges | CREATE TABLE `SalaryChanges` (
  `SSN` char(12) DEFAULT NULL,
  `OldSalary` int(10) unsigned DEFAULT NULL,
  `NewSalary` int(10) unsigned DEFAULT NULL,
  `DateChanged` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci `ENCRYPTION_KEY_ID`=100 |
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
B1 row in set (0.000 sec)
BB
sourceetriggers.sql;Dnumber8=5304";table SalaryChanges;
BQuery OK, 0 rows affected (0.001 sec)
BB
BBQuery OK, 0 rows affected (0.002 sec)
BB
BBQuery OK, 0 rows affected (0.004 sec)
BB
BBQuery OK, 0 rows affected (0.002 sec)
BB
BBQuery OK, 0 rows affected, 1 warning (0.000 sec)
BB
BBQuery OK, 0 rows affected, 1 warning (0.000 sec)
BB
BBQuery OK, 0 rows affected (0.004 sec)
BB
BBQuery OK, 0 rows affected (0.002 sec)
BB
updateeEmployeeSset=Dnumber8=5304";tableqSalaryChanges;
BQuery OK, 0 rows affected (0.002 sec)
BBRows matched: 1  Changed: 0  Warnings: 0
BB
BMariaDB [companys25]> select * from DeptChanges;
BEmpty set (0.000 sec)
BB
updateDEmployeenset5Dnumberc= 4 whereDSSNC=a"747-68-5804";
BQuery OK, 1 row affected (0.001 sec)
BBRows matched: 1  Changed: 1  Warnings: 0
BB
selectD* frompDeptChanges;ate Employee set Dnumber = 4 where SSN = "747-68-5804";
+-------------+------------+------------+---------------------+
| SSN	      | OldDnumber | NewDnumber | DateChanged	      |
+-------------+------------+------------+---------------------+
| 747-68-5804 |		 3 |	      4 | 2025-04-02 13:43:50 |
+-------------+------------+------------+---------------------+
B1 row in set (0.001 sec)
BB
updateDEmployeenset5Salary=80000whereDSSNC=a"747-68-5804";
BQuery OK, 1 row affected (0.001 sec)
BBRows matched: 1  Changed: 1  Warnings: 0
BB
BMariaDB [companys25]> select * from SalaryChanges;
+-------------+-----------+-----------+---------------------+
| SSN	      | OldSalary | NewSalary | DateChanged	    |
+-------------+-----------+-----------+---------------------+
| 747-68-5804 |	    73689 |	80000 | 2025-04-02 13:44:04 |
+-------------+-----------+-----------+---------------------+
B1 row in set (0.001 sec)
BB
BMariaDB [companys25]> source badtrigger.sql;
BQuery OK, 0 rows affected, 1 warning (0.000 sec)
BB
BBQuery OK, 0 rows affected (0.002 sec)
BB
eeaUNION selectnManagerSSNefrom Department)eorderrbySrand() limite1;ct SuperSSN from Employ
+----------+-------+----------+-------------+------------+-------------------+--------+-------------+---------+
| Fname	   | Minit | Lname    | SSN	    | DOB	 | Address	     | Salary | SuperSSN    | Dnumber |
+----------+-------+----------+-------------+------------+-------------------+--------+-------------+---------+
| Ophélie  | Z	   | Beauchop | 187-44-0427 | 1979-05-01 | 2128 Rowland Road | 213080 | 545-29-0171 |	    8 |
+----------+-------+----------+-------------+------------+-------------------+--------+-------------+---------+
B1 row in set (0.005 sec)
BB
BMariaDB [companys25]> select * from WorksOn whereeeSSN = "187-44-0427";
+-------------+---------+---------+
| eSSN	      | Pnumber | Hours	  |
+-------------+---------+---------+
| 187-44-0427 |	      1 | 14.5008 |
| 187-44-0427 |	      2 | 32.2966 |
| 187-44-0427 |	      7 | 38.1294 |
| 187-44-0427 |	     10 | 35.3529 |
+-------------+---------+---------+
B4 rows in set (0.001 sec)
BB
deleteDB [companys25]> select * from WorksOn where eSSN = "187-44-0427";
BQuery OK, 4 rows affected (0.001 sec)
BB
BMariaDB [companys25]>
MariaDB [companys25]> delete from Employee where SSN = "187-44-0427";
ERROR 1442 (HY000): Can't update table 'Employee' in stored function/trigger because it is already used by statement which invoked this stored function/trigger
BBMariaDB [companys25]> select *ffrom Employee where SSN = "187-44-0427";
+----------+-------+----------+-------------+------------+-------------------+--------+-------------+---------+
| Fname	   | Minit | Lname    | SSN	    | DOB	 | Address	     | Salary | SuperSSN    | Dnumber |
+----------+-------+----------+-------------+------------+-------------------+--------+-------------+---------+
| Ophélie  | Z	   | Beauchop | 187-44-0427 | 1979-05-01 | 2128 Rowland Road | 213080 | 545-29-0171 |	    8 |
+----------+-------+----------+-------------+------------+-------------------+--------+-------------+---------+
B1 row in set (0.001 sec)
BB
BMariaDB [companys25]> describe Employee;
+----------+------------------+------+-----+---------+-------+
| Field	   | Type	      | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| Fname	   | varchar(30)      | YES  |	   | NULL    |	     |
| Minit	   | char(1)	      | YES  |	   | NULL    |	     |
| Lname	   | varchar(30)      | NO   |	   | NULL    |	     |
| SSN	   | char(12)	      | NO   | PRI | NULL    |	     |
| DOB	   | date	      | YES  |	   | NULL    |	     |
| Address  | varchar(255)     | YES  |	   | NULL    |	     |
| Salary   | int(10) unsigned | YES  |	   | NULL    |	     |
| SuperSSN | char(12)	      | YES  | MUL | NULL    |	     |
| Dnumber  | int(11)	      | NO   | MUL | NULL    |	     |
+----------+------------------+------+-----+---------+-------+
B9 rows in set (0.001 sec)
BB
BMariaDB [companys25]> source triggers.sql
BQuery OK, 0 rows affected (0.002 sec)
BB
BBQuery OK, 0 rows affected (0.002 sec)
BB
BBQuery OK, 0 rows affected (0.003 sec)
BB
BBQuery OK, 0 rows affected (0.002 sec)
BB
BBQuery OK, 0 rows affected (0.002 sec)
BB
BBQuery OK, 0 rows affected (0.002 sec)
BB
BBQuery OK, 0 rows affected (0.003 sec)
BB
BBQuery OK, 0 rows affected (0.012 sec)
BB
BBQuery OK, 0 rows affected, 1 warning (0.000 sec)
BB
BBQuery OK, 0 rows affected (0.002 sec)
BB
B"123-45-6789");ys25]> INSERT INTO Employee (Fname, Lname, SSN) VALUES ("Nick", "Toothman",
ERROR 1364 (HY000): Field 'Dnumber' doesn't have a default value
BBMariaDB [companys25]> select * fromoEmployee where SSN = ;23-45-6789"9")"
BEmpty set (0.001 sec)
BB
BMariaDB [companys25]> show create table Employee;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table	   | Create Table																																																																																																							   |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Employee | CREATE TABLE `Employee` (
  `Fname` varchar(30) DEFAULT NULL,
  `Minit` char(1) DEFAULT NULL,
  `Lname` varchar(30) NOT NULL CHECK (`Lname` <> ''),
  `SSN` char(12) NOT NULL,
  `DOB` date DEFAULT NULL,
  `Address` varchar(255) DEFAULT NULL,
  `Salary` int(10) unsigned DEFAULT NULL CHECK (`Salary` > 30000),
  `SuperSSN` char(12) DEFAULT NULL,
  `Dnumber` int(11) NOT NULL,
  PRIMARY KEY (`SSN`),
  KEY `Dnumber` (`Dnumber`),
  KEY `SuperSSN` (`SuperSSN`),
  CONSTRAINT `Employee_ibfk_1` FOREIGN KEY (`Dnumber`) REFERENCES `Department` (`Dnumber`) ON UPDATE CASCADE,
  CONSTRAINT `Employee_ibfk_2` FOREIGN KEY (`SuperSSN`) REFERENCES `Employee` (`SSN`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `CONSTRAINT_1` CHECK (`SuperSSN` <> `SSN`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci `ENCRYPTION_KEY_ID`=100 |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
B1 row in set (0.000 sec)
BB
,)1VALUES6("Nick",e"Toothman",n"123-45-6789");S)(VALUES,("Nick",a"Toothman", "123-45-6789");D) VALUES ("Nick", "Toothman"n) VALUES ("Nick", "Toothmanu) VALUES ("Nick", "Toothmam) VALUES ("Nick", "Toothmb) VALUES ("Nick", "Toothe) VALUES ("Nick", "Tootr) VALUES ("Nick", "Too
,)-1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`companys25`.`Employee`, CONSTRAINT `Employee_ibfk_1` FOREIGN KEY (`Dnumber`) REFERENCES `Department` (`Dnumber`) ON UPDATE CASCADE)
BBMariaDB [companys25]> show triggers;
+-----------------------+--------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| Trigger		| Event	 | Table    | Statement																														 | Timing | Created		   | sql_mode										       | Definer	| character_set_client | collation_connection | Database Collation |
+-----------------------+--------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| AssignNewEmployeeDept | INSERT | Employee | BEGIN
IF NEW.SuperSSN IS NULL THEN
    SET NEW.SuperSSN = (select SuperSSN from Employee order by rand() limit 1);
END IF;
IF NEW.Dnumber IS NULL THEN
    SET NEW.Dnumber = (select Dnumber from Department order by rand() limit 1);
END IF;
END | BEFORE | 2025-04-02 13:53:16.57 | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | nick@localhost | utf8mb4		  | utf8mb4_general_ci	 | utf8mb4_general_ci |
| OnSalaryChange	| UPDATE | Employee | BEGIN
IF OLD.Salary <> NEW.Salary THEN
    INSERT INTO SalaryChanges (SSN, OldSalary, NewSalary)
    VALUES (NEW.SSN, OLD.Salary, NEW.Salary);
END IF;
END											    | BEFORE | 2025-04-02 13:53:16.55 | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | nick@localhost | utf8mb4		  | utf8mb4_general_ci	 | utf8mb4_general_ci |
| OnDeptChange		| UPDATE | Employee | BEGIN
IF OLD.Dnumber <> NEW.Dnumber THEN
    INSERT INTO DeptChanges (SSN, OldDnumber, NewDnumber)
    VALUES (NEW.SSN, OLD.Dnumber, NEW.Dnumber);
END IF;
END											| BEFORE | 2025-04-02 13:53:16.56 | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | nick@localhost | utf8mb4	      | utf8mb4_general_ci   | utf8mb4_general_ci |
| OnEmployeeChange	| DELETE | Employee | DELETE FROM Employee WHERE SSN = OLD.SSN																										 | BEFORE | 2025-04-02 13:46:03.45 | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | nick@localhost | utf8mb4	       | utf8mb4_general_ci   | utf8mb4_general_ci |
+-----------------------+--------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
B4 rows in set (0.002 sec)
BB
oothman",["123-45-6789show1createOtableoEmployee;e, Lname, SSN, Dnumber) VALUES ("Nick", "To
I"123-45-6789");sourceFtriggers.sql1SSN)5VALUES;("Nick", "Toothman",

BQuery OK, 0 rows affected (0.002 sec)
BB
BBQuery OK, 0 rows affected (0.003 sec)
BB
BBQuery OK, 0 rows affected (0.004 sec)
BB
BBQuery OK, 0 rows affected (0.005 sec)
BB
BBQuery OK, 0 rows affected (0.002 sec)
BB
BBQuery OK, 0 rows affected (0.002 sec)
BB
BBQuery OK, 0 rows affected (0.005 sec)
BB
BBQuery OK, 0 rows affected (0.003 sec)
BB
BBQuery OK, 0 rows affected (0.002 sec)
BB
BBQuery OK, 0 rows affected (0.002 sec)
BB
oothman",["123-45-6789",N-1);rINTOrEmployee (Fname, Lname, SSN, Dnumber) VALUES ("Nick", "To
BQuery OK, 1 row affected (0.005 sec)
BB
oothman",["123-45-6789source;triggers.sqlee (Fname, Lname, SSN, Dnumber) VALUES ("Nick", "To
oothman",T"123-45-6789show1createetable,Employee; VALUES ("Nick", "To
elect * from Employee where SSN = "123-45-6789";
+-------+-------+----------+-------------+------+---------+--------+-------------+---------+
| Fname | Minit | Lname	   | SSN	 | DOB	| Address | Salary | SuperSSN	 | Dnumber |
+-------+-------+----------+-------------+------+---------+--------+-------------+---------+
| Nick	| NULL	| Toothman | 123-45-6789 | NULL | NULL	  |   NULL | 397-42-6772 |	 2 |
+-------+-------+----------+-------------+------+---------+--------+-------------+---------+
B1 row in set (0.000 sec)
BB
BMariaDB [companys25]> select * from Department;
+---------+--------------------------+-------------+------------------+
| Dnumber | Dname		     | ManagerSSN  | ManagerStartDate |
+---------+--------------------------+-------------+------------------+
|	1 | Human Resources	     | 250-88-7489 | 2024-06-27	      |
|	2 | Accounting		     | 423-05-9847 | 2024-05-28	      |
|	3 | Services		     | 216-34-0074 | 2024-06-29	      |
|	4 | Engineering		     | 216-34-0074 | 2024-05-09	      |
|	5 | Research and Development | 216-34-0074 | 2024-05-23	      |
|	6 | Product Management	     | 203-38-0435 | 2025-01-07	      |
|	7 | People Management	     | 250-88-7489 | 2025-01-22	      |
|	8 | Legal		     | 423-05-9847 | 2024-05-02	      |
|	9 | Support		     | 552-26-1423 | 2024-11-26	      |
|      10 | Marketing		     | 203-38-0435 | 2024-11-18	      |
+---------+--------------------------+-------------+------------------+
B10 rows in set (0.001 sec)
BB
BMariaDB [companys25]> exit
BBye
Bnick@artemis:~/3420_s25$ php -a
Interactive mode enabled

php >
nick@artemis:~/3420_s25$ cd
nick@artemis:~$ cd public_html/3420/lab8_s25/
nick@artemis:~/public_html/3420/lab8_s25$ ls -l
total 40
-rw-r--r-- 1 nick nick 2380 Nov	 7 13:27 config.php
-rw-r--r-- 1 nick nick	439 Nov	 7 11:55 delete.php
-rw-r--r-- 1 nick nick	 56 Nov	 7 12:49 index.php
-rw-r--r-- 1 nick nick 1236 Nov	 7 12:50 insert.php
-rw-r--r-- 1 nick nick	364 Nov	 7 11:40 main.php
-rw-r--r-- 1 nick nick	163 Nov	 7 11:23 nav.php
-rw-r--r-- 1 nick nick 2283 Nov	 7 13:24 select.php
-rw-r--r-- 1 nick nick	276 Nov	 7 11:23 style.css
-rw-r--r-- 1 nick nick	689 Nov	 7 11:23 tablemaker.php
-rw-r--r-- 1 nick nick	439 Nov	 7 12:50 update.php
nick@artemis:~/public_html/3420/lab8_s25$ php index.php

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <title>CMPS 3420 Project</title>
    <link rel="stylesheet" href="style.css">
</head>
<body>
    <h1>CMPS 3420 Project</h1>
    <nav>
<a href="select.php">select</a><a href="insert.php">insert</a><a href="update.php">update</a><a href="delete.php">delete</a></nav>
</body>
</html>nick@artemis:~/public_html/3420/lab8_s25$ phppconfig.php

nick@artemis:~/public_html/3420/lab8_s25$ php -a
Interactive mode enabled

("config.php");

_connection();t_pdo
php > $db;
php > print_r($db);
PDO Object
(
)
php > var_dump($db);
object(PDO)#1 (0) {
}
php > $query = $db->prepare("select * from hello where id = :id");
php > var_dump($query);
object(PDOStatement)#2 (1) {
  ["queryString"]=>
  string(34) "select * from hello where id = :id"
}
php > $id = 80;
id",>$id,ePDO::PARAM_INT);d
var_dump($query);(":id", $id, PDO::PARAM_INT);
object(PDOStatement)#2 (1) {
  ["queryString"]=>
  string(34) "select * from hello where id = :id"
}
$query->execute();
1
$rows =$$query->fetchAll(PDO::FETCH_ASSOC);
php > $rows;
rows);var_dump($py
array(1) {
  [0]=>
  array(2) {
    ["id"]=>
    string(2) "80"
    ["data"]=>
    string(4) "😼"
  }
}
print_r($rows);
Array
(
    [0] => Array
	(
	    [id] => 80
	    [data] => 😼
	)

)
php > echo $rows;
PHP Notice:  Array to string conversion in php shell code on line 1

Notice: Array to string conversion in php shell code on line 1
Array
[0];>$rows;
PHP Notice:  Array to string conversion in php shell code on line 1

Notice: Array to string conversion in php shell code on line 1
Array
["id"];ows[0];
80
a"];>$rows[0]["id"];
😼
place withra string";
echo>$query;
Replace with a string
php > $data = "Nick";
;hp > echo "Hello $data");
Hello Nick
'Hho>"Hello $data";
Hello $data
php > exit
nick@artemis:~/public_html/3420/lab8_s25$ php select.php

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <title>CMPS 3420 Project</title>
    <link rel="stylesheet" href="style.css">
</head>
<body>
    <h1>CMPS 3420 Project</h1>
    <nav>
<a href="select.php">select</a><a href="insert.php">insert</a><a href="update.php">update</a><a href="delete.php">delete</a></nav>    <h2>SQL SELECT using input from form</h2>
    <form action="select.php" method="GET">
	<label>ID to search for: <input type="number" name="search_id"></label><br>
	<label>Data to search for: <input type="text" name="search_data"></label><br>
	<input type="submit" name="search"><br>
    </form>
</body>
</html>
nick@artemis:~/public_html/3420/lab8_s25$ php -a
Interactive mode enabled

=harray();T array()
php > $_GET["search"] = true;
_id"]c=+80;[se
dec("select.php");

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <title>CMPS 3420 Project</title>
    <link rel="stylesheet" href="style.css">
</head>
<body>
    <h1>CMPS 3420 Project</h1>
    <nav>
<a href="select.php">select</a><a href="insert.php">insert</a><a href="update.php">update</a><a href="delete.php">delete</a></nav>    <h2>SQL SELECT using input from form</h2>
    <form action="Standard input code" method="GET">
	<label>ID to search for: <input type="number" name="search_id"></label><br>
	<label>Data to search for: <input type="text" name="search_data"></label><br>
	<input type="submit" name="search"><br>
    </form>
searching by id...<br><table><tr><th>id</th><th>data</th></tr><tr><td>80</td><td>😼</td></tr></table></body>
</html>
php > exit
nick@artemis:~/public_html/3420/lab8_s25$ php select.php
PHP Parse error:  syntax error, unexpected '}' in /home/fac/nick/public_html/3420/lab8_s25/select.php on line 23
nick@artemis:~/public_html/3420/lab8_s25$ php select.php
PHP Parse error:  syntax error, unexpected '}' in /home/fac/nick/public_html/3420/lab8_s25/select.php on line 23
nick@artemis:~/public_html/3420/lab8_s25$ php select.php

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <title>CMPS 3420 Project</title>
    <link rel="stylesheet" href="style.css">
</head>
<body>
    <h1>CMPS 3420 Project</h1>
    <nav>
<a href="select.php">select</a><a href="insert.php">insert</a><a href="update.php">update</a><a href="delete.php">delete</a></nav>    <h2>SQL SELECT using input from form</h2>
    <form action="select.php" method="GET">
	<label>ID to search for: <input type="number" name="search_id"></label><br>
	<label>Data to search for: <input type="text" name="search_data"></label><br>
	<input type="submit" name="search"><br>
    </form>
</body>
</html>
nick@artemis:~/public_html/3420/lab8_s25$ exit
exit