This post contains some of my notes on using MySQL taking when studying the Applied Databases module.

• MySQL is a database management system that manages many different database systems.

• One of the functions of a DBMS is to backup a database to a file which can be imported. When a database is imported it is recreated.

• MySQLWorkbench is a GUI for interfacing with the database.

• can also use the Command line interface to work with a database

• the workbench is basically a GUI for showing the commands that are run in the background.

• Show Databases will show the names of the different databases currently managed by Mysql

on MySQLWorkbench:

• Server > data import > import from Self contained file > browse to the location.

Windows has an app called MySQL 8.0 Command Line Client that can be used instead of the MySQLWorkbench. On a Mac, the commands can be entered at the mysql prompt.

• Command line:
• Windows using the windows command prompt.
• Mac using the Terminal application: cd /bin

Go go folder where mysql is installed which is usually the programs directory if MySQL has been installed in the standard way.

Navigate to c:/Program Files/MySQL/MySQL Server 8.0 /bin (windows) or cd /bin on Mac terminal using the cd change directory command.

• on command line mysql -u root -p then enter password when prompted.

• use \c to get out when stuck in a prompt

• mysql -u root -p to access mysql on command line in terminal

SHOW TABLES;

Tables_in_school

subject
teacher

2 rows in set (0.01 sec)

DESCRIBE <table>

describe subject;

describe teacher;

5 rows in set (0.00 sec)

DESCRIBE <table>;

• The Type shows the type of data for each Field as well as the maximum number of characters allowed.

• The Key column shows which field(s) is the primary key

SELECT * FROM;

• select * from teacher to show all data in the teacher table
• select * from subject to show all data in the subject table

SELECT * FROM teacher;

SELECT * FROM subject;

The WHERE condition

select name 
from subject 
where onleavingcert =1;

gets all subjects on the leaving cert

The select * from ... where condition;

select name, experience 
from teacher 
where level ="L";

This query shows the details of all teachers qualified to teach to leaving cert.

SELECT ... FROM ... WHERE .. NOT LIKE " ";

select * from subject 
where teacher NOT LIKE "Mr.%";

Shows details of all subjects taught by teachers whose title is not “Mr.”

SELECT ... FROM ... WHERE ... AND ...;

select * from teacher
where month(dob) IN(1,2,3) 
AND level = "J";

This shows all teachers born in the specified months and that can teach to junior cert only.

which gives the same results as:

select * from teacher 
where month(dob) between 1 and 3 
AND level = "J";

DISTINCT

SELECT DISTINCT ... FROM ...;

select distinct monthname(dob)
from teacher;

Shows all unique month names that teachers were born in

ORDER BY

SELECT ... FROM ... ORDER BY...

select * from teacher
order by experience desc, level;

Shows all details of teachers sorted by experience first, then level.

SELECT ... FROM ... WHERE... ORDER BY...;

select * from subject 
where name like "__l%" or name like"___l%" 
order by name;

Shows all details of all subjects whose 3rd or 4th letter is “l”, sorted by name.

SELECT ... FROM ... WHERE ... IN ( , , , ) ORDER BY ... ;

select * from teacher 
where experience in (10,15,20,25,30,35,40,45,50,55,60)
order by dob;

Shows the names of all teachers with the mentioned number of years experience, sorted from youngest to oldest.

select * from car;

SELECT ... FROM ... WHERE ... AND ( ...OR...);s

select registration, mileage from car 
where mileage > 130000 AND (colour= "Silver" or colour ="Black");

Shows details for all cars with mileage greater than 130000 and colour is silver or black.

 select * from person;

AVERAGE

SELECT ... AVG() FROM ... WHERE ... AND ...;

Apply the average to the rows selected where the conditions are met.

SELECT ROUND(AVG(AGE)) FROM PERSON WHERE SEX ="M" AND AGE <=24;

SELECT ROUND(AVG(AGE)) FROM PERSON WHERE SEX ="M" AND AGE >=24;

SELECT ... FROM ... WHERE ... LIKE (' ');

select * from person where name like ('___');

Shows details for persons with exactly 3 letters in their name.

SELECT DISTINCT ... FROM ...;

select distinct(make) from car;

SELECT ... FROM ... WHERE ... LIKE " " ORDER BY ...;

select * from car 
where registration like "%-G-%" 
order by mileage asc;

Shows all cars registered in Galway, sorted by mileage in ascending order

SELECT ... FROM ... WHERE ... NOT IN .. ORDER BY ... LIMIT;

The limit clause

select * from person 
where month(dob) not in(1,2,3) 
order by month(dob) 
limit 4;

Shows all people not born in first 3 months of year, sorted by month order. Only show the first few records return

describe employees;

describe salaries;

The primary key on the salaries table is made up of two composite keys as there could be multiple instances of the employee number and the from_date but you would only have one instance of each emp_no and from_date combination.

select * from employees
limit 5;

select emp_no, first_name, upper(last_name) as last_name from employees limit 5;

STRING FUNCTIONS: length(), substr(), concat(), format()

select * from employees 
order by length(last_name), last_name, length(first_name), first_name 
limit 10;

This sorts based on the length of last_name, Alphabetical order of last_name, length of first_name, alphabetical order of first name.

The string function char_length() returns the length of a string measured in characters where a multibyte character counts as a single character.

Show all details of the first 10 employees returned from the database and an extra column called Initials that shows the employee’s initials.

concat(), substr()

select *, concat(substr(first_name,1,1), substr(last_name,1,1)) as Initials from employees limit 5;

The concat and substr string functions are used here to display a new column with the initials of employees. An alias is used to name the new column Initials.

The concat function could be used to create an email address using the first name and last name.

 select concat(first_name, '.',last_name,"@company.com") as email 
 from employees limit 5;

The format function.

format()

select format(max(mileage),0)
from car 
group by make;

Multiple ANDs

Using multiple ANDs here but as there are no ORs being used, brackets are not required for operator precedence .

select * from employees where gender = "F" 
and year(birth_date) between 1950 and 1959 
and hire_date >= "1988-09-1" 
and hire_date <="1991-02-28";

Aggregate GROUP BY functions

GROUP BY

AVG(), MAX()

select * from person;

select round(avg(age)) from person where sex="M";

Count, group by.

SELECT ... , COUNT(*) FROM ... GROUP BY ...;

Does a count based on the group by

select monthname(dob), count(*)from person 
group by monthname(dob);

Shows the number of people born in each month

SELECT AVG() AS ... FROM ... GROUP BY ... LIMIT...;

select emp_no, round(avg(salary),2) as average_salary from salaries group by emp_no limit 5;

select emp_no, round(max(salary),2) as average_salary from salaries group by emp_no limit 5;

The WHERE clause

SELECT ... FROM ... WHERE ... AND ... GROUP BY...;

 select emp_no,round(avg(salary),2) from salaries 
 where emp_no in (10001, 10021, 10033, 10087) 
 and salary > 80000 
 group by emp_no;

This shows the average salaries for these 4 employees, but only including salaries > 80,000 in the average salary calculation.

The HAVING clause

SELECT ... FROM ... GROUP BY ... HAVING AVG()>...;

SELECT EMP_NO, ROUND(AVG(SALARY) )
FROM SALARIES 
GROUP BY EMP_NO
HAVING AVG(SALARY) >90000;

+——–+————-+

This query shows the average salaries for employees but only showing the average salaries which are over 90000.

select monthname(birth_date), count(*) 
from employees
 group by monthname(birth_date);

CONTROL FLOW FUNCTIONS.

CONTROL FLOW FUNCTIONS Name Description

• CASE : Case operator
• IF() : If/else construct
• IFNULL() : Null if/else construct
• NULLIF() : Return NULL if expr1 = expr2

The IF clause

select *,IF(salary>50000,"big","small") as salary_type 
from salaries 
order by from_date 
limit 5;

A CASE statement allows for more options than an IF statement which only allows one.

An alias can be used for the names of the column for printing as otherwise the whole case when statement will show as the column name. However the alias does not change the actual table.

SELECT *, IF(MILEAGE>500000,"30%","") Discount 
FROM car;

Note as is not required when using an alias to rename a column that is returned. It will not actually change the table.

IF

select emp_no as ID, if (gender="M","Mr.","Ms.") as Title, first_name as Name, last_name as Surname, gender 
from employees 
order by emp_no limit 5;

CASE WHEN

select emp_no, max(salary), 
case 
    when max(salary) < 40000 then "30%" 
    when max(salary) < 60000 then "40%" 
    when max(salary) < 80000 then "50%" 
    else "60%" 
END as "Tax bracket" 
from salaries 
group by emp_no 
order by max(salary) 
limit 10;

select *, 
case 
    when engineSize <=1.0 then "Small" 
    when engineSize between 1.1 and 1.3 then "Medium" 
    when engineSize > 1.3 then "Large" 
END as size 
from car;

CASE WHEN

select first_name,  birth_date, 
case 
    when month(birth_date)in(1,2,3) then "q1 baby" 
    when month(birth_date)in (4,5,6) then "q2 baby" 
    when month(birth_date) in (7,8,9) then "q3 baby" 
    else "q4 baby" 
end as birthQ 
from employees limit 5;

select name, isStudent,age, 
case  
    when isStudent=1 and age >23 then "mature student" 
    when isStudent=1 and age <=23 then "ordinary student"
    else "Not" 
    end as "student status" 
    from person limit 5;

CASE WHEN

select *, 
case 
    when age < 20 then "under20" 
    when age < 30 then "under30" 
    when age < 40 then "under40" 
    else "over40" 
    end as "age-cat" 
    from person;

(use the correct order when writing the case when.)

DATE AND TIME FUNCTIONS.

DATEDIFF()

DATEDIFF

SELECT *, if(DATEDIFF(to_DATE,from_DATE) <365, "under 1 year","over 1 year") as Time 
FROM SALARIES 
LIMIT 5;

DATE AND TIME FORMAT FUNCTIONS

DATE_FORMAT

date_format(date, format)

A sample of the specifiers here:

Specifier Description

• %a : Abbreviated weekday name (Sun..Sat)
• %b : Abbreviated month name (Jan..Dec)
• %c : Month, numeric (0..12)
• %D : Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
• %d : Day of the month, numeric (00..31)
• %H : Hour (00..23)
• %h: Hour (01..12)
• %M: Month name (January..December)
• %m: Month, numeric (00..12)
• %p: AM or PM
• %S: Seconds (00..59)
• %T: Time, 24-hour (hh:mm:ss)
• %W: Weekday name (Sunday..Saturday)
• %Y: Year, numeric, four digits
• %y: Year, numeric (two digits)

The format strings should be in quotes.

select NAME, date_format(dob, "%a, %b %D %Y") FROM PERSON;

select first_name as first, gender as G 
from employees limit 3;

FUNCTIONS AND PROCEDURES

• When writing functions and procedures, the delimiter must be changed to // before writing a function or procedure. This allows the ; to be used as part of the code and not to signify the end of a statement.

• Before writing the function or procedure, write it as a query.

• A function is deterministic if it doesn’t chanhe any data.

select *, round(datediff(hire_date, birth_date)/365,1) as age 
from employees limit 5;

Using a function show all columns from the employees table, and a column entitled “Age” which is the age the employee was when he or she was hired. The age should be rounded to 1 digit after the decimal place.

DELIMITER //
CREATE FUNCTION getAges(hd date, bd date)
    -> RETURNS varchar(5)
    -> DETERMINISTIC
    -> BEGIN
    -> RETURN format(datediff(hd,bd)/365,1);
    -> END
    -> //

delimiter ;
select *, getAges(hire_date, birth_date) as Age_at_hiring
from employees
limit 5;

This procedure takes two parameters, one for a year and the other a month and returns all employees hired in specified year and month.

delimiter //
mysql> CREATE PROCEDURE hires(y integer, m integer)
    -> DETERMINISTIC
    -> BEGIN
    -> SELECT * FROM employees
    -> WHERE year(hire_date)=y AND month(hire_date)=m;
    -> END
    -> //

This procedure returns all employees hired in the specified year if the month parameter is NULL.

delimiter //
mysql> create procedure hires2(y integer, m integer)
    -> DETERMINISTIC 
    -> BEGIN
    -> IF m is NULL THEN
    -> SELECT * FROM EMPLOYEES WHERE YEAR(hire_date)=y;
    -> else
    -> select * from employees where year(hire_date) = y and month(hire_date)=m;
    -> end if;
    -> end
    -> //

mysql> delimiter ;
call hires2(1995,null);

call hires2(1995,3);

To check if a parameter is NULL IF M IS NULL THEN To check if a parameter is not NULL IF M IS NOT NULL THEN.

 select * from person;

Functions

describe person;

This function determines if a person is a student based on age and isStudent status.

delimiter //
create function st(a int(11) iss TINYINT(11))
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
if iss and a< 23 then
    return "Ordinary";
elseif iss and     a >=23 then 
    return "Mature";
else 
    return "";
end if;
end
//

delimiter ;

To call the function, pass in the columns from the persons tables, which takes the values for each field and passes them as the parameters to the function.

select st(16,0);

select *, st(age, isStudent) as "Student Type" from person;

CREATE PROCEDURE

Write a procedure that accepts two parameters. A comparison operator < > = and an Age. If the comparison operator is >, the procedure returns all details of a person older than the age.

passing in a string of one or a varchar with the ‘>’, ‘<. or ‘=’ operator. make age the same data type as the age in the table

Note that the return keyword is not allowed in a procedure. It is allowed in a function. Using select. Can put the if statement in parentheses but not required.

delimiter //
create procedure comp(c varchar(1) , a int(11))
deterministic
begin   
    if (c = "<") then 
        select * from person where age < a;
    elseif (x = ">") then
        select * from person where age > a;
    else
        select * from person where age = a;
    end if;
end 
//

delimiter;

call comp("<",3);

Use drop procedure <procedure name> to delete a procedure and create it again if a change to the code is required.

drop procedure comp once the procedure is written, change the delimiter back to ;. Then the procedure can be called from memory to be used. call <procedure name>(arguments)

Normalisation

describe doctor_table;

describe patient_table;

show create table patient_table

CREATE TABLE `patient_table` (
  `ppsn` varchar(10) NOT NULL,
  `first_name` varchar(50) DEFAULT NULL,
  `surname` varchar(50) DEFAULT NULL,
  `address` varchar(200) DEFAULT NULL,
  `doctorID` int(11) DEFAULT NULL,
  PRIMARY KEY (`ppsn`),
  KEY `doctorID` (`doctorID`),
  CONSTRAINT `patient_table_ibfk_1` FOREIGN KEY (`doctorID`) REFERENCES `doctor_table` (`doctorid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

show create table doctor_table;

CREATE TABLE `doctor_table` (
  `doctorID` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `phone` int(11) DEFAULT NULL,
  PRIMARY KEY (`doctorID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

The doctor id in the patient table is a foreign key referring the doctorid in the doctor table. Therefore any integers we put in as doctor id in the patient table must already exist in the doctor table doctor id column.

select * from doctor_table;

select * from patient_table;

String function.

SUBSTR()

select substr(name,5) 
from doctor_table;

Here the substr function selects from position 5 so strips out the title to return just the name.

select substr(name,5) 
from doctor_table;

INNER JOIN

select pt.*, dt.* from patient_table pt 
inner join doctor_table dt
on pt.doctorID = dt.doctorid; 

This query shows all details of all patients with associated doctors.

INNER JOIN

SELECT ... FROM ... INNER JOIN .. ON ...=... WHERE... ORDER BY...;

SELECT dt.name, dt.phone, pt.ppsn, pt.first_name, pt.surname
FROM doctor_table dt
INNER JOIN patient_table pt
on pt.DoctorID = dt.Doctorid
where dt.name = "Dr. Jones"
order by pt.surname;

This query returns the details of patients treated by a particular doctor.

DISTINCT

Select distinct() from ... inner join ... on ... = ... ;

select distinct(dt.name)
from doctor_table dt
inner join patient_table pt
on dt.DoctorId = pt.Doctorid;

This query returns the unique names of doctors who are treating patients.

LEFT JOIN

NULL

 select pt.ppsn, pt.surname, pt.first_name, dt.name from patient_table pt  left join doctor_table dt on pt.Doctorid= dt.doctorid;

This query returns null for the doctor name where a patient does not have a doctor. Where the doctorID is NULL, the patient isn’t currently being treated by a doctor

LEFT JOIN vs INNER JOIN

INNER JOIN only returns rows where the condition is met while LEFT JOIN returns all rows from the first table with a NULL for the field where the condition is NOT met. INNER JOIN excludes rows where the condition is not met.

• to show all details of all doctors from one table with the details of their patients from the patients_table, use a left join as this will return all rows from the first table with a null value for rows in the second table where the condition is not met.
• an inner join would omit the rows where the condition is not met

LEFT JOIN

select pt.first_name, pt.surname, dt.name 
from patient_table pt 
left join doctor_table dt 
on pt.doctorid = dt.doctorid;

INNER JOIN

select pt.first_name, pt.surname, dt.name 
from patient_table pt 
inner  join doctor_table dt 
on pt.doctorid = dt.doctorid;

select dt.*, pt.surname 
from doctor_table as dt
left join patient_table pt
on dt.DoctorID = pt.DoctorID
order by dt.name, pt.surname;

select pt.first_name, pt.surname, dt.name from patient_table pt
inner join doctor_table dt 
on pt.doctorid = dt.doctorid 
where pt.first_name ="Alan";

LEFT JOIN

left join to return every row from the patient_table even if no doctor associated.

select pt.ppsn, pt.first_name, pt.surname, dt.name, dt.phone 
from patient_table pt 
left join doctor_table dt 
on pt.doctorid = dt.doctorid;

returns all patients from the patient_table with details of doctor they are attending if any.

SHOW CREATE TABLE;

show create table manufacturer;

manufacturer CREATE TABLE `manufacturer` (  
    `manu_code` varchar(3) NOT NULL,
    `manu_name` varchar(200) NOT NULL,
    `manu_details` varchar(400) DEFAULT NULL,
    PRIMARY KEY (`manu_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

show create table vehicles;

vehicle CREATE TABLE `vehicle` (
  `reg` varchar(20) NOT NULL,
  `manu_code` varchar(3) NOT NULL,
  `mileage` int(11) DEFAULT NULL,
  `price` decimal(8,2) NOT NULL,
  `colour` varchar(20) NOT NULL,
  `fuel` enum('petrol','diesel') DEFAULT NULL,
  PRIMARY KEY (`reg`),
  KEY `vehicle_ibfk_1` (`manu_code`),
  CONSTRAINT `vehicle_ibfk_1` FOREIGN KEY (`manu_code`) REFERENCES `manufacturer` (`manu_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

Relationships between tables through the foreign key:

The manfacturer table and the vehicles table are related through the manu_code field in the vehicles table which is a foreign key that references the manu_code field in the manufacturer table.

 select * from manufacturer;

select * from vehicle;

STRING FUNCTIONS:

CONCAT(), SUBSRT()

select manu_code, manu_name, concat(substr(manu_details,1,10),"...")
from manufacturer;

Length

select length(manu_details) 
from manufacturer;

select avg(length(manu_details)) from manufacturer;

select format(avg(length(manu_details)),0) as "Length" 
from manufacturer;

This shows the average length of the manu_name formatted to 0 decimal places.

select *, if(fuel="petrol","1,45","1.30") as cost 
from vehicle;

This query includes a column called “cost” which has the value 1.45 if the fuel is petrol otherwise has the value 1.30.

JOINING TABLES TO GET DATA FROM MULTIPLE TABLES

The vehicle table has a foreign key manu_code referencing the manu_code field in the manafacturer table.

 **KEY `vehicle_ibfk_1` (`manu_code`),**
  **CONSTRAINT `vehicle_ibfk_1` FOREIGN KEY (`manu_code`) REFERENCES `manufacturer` (`manu_code`)**

INNER JOIN

select m.manu_code, m.manu_name, v.reg
    from manufacturer m
    inner join vehicle v
    on m.manu_code = v.manu_code;

This query only returns rows where the condition is met, so only returns rows from both tables which have matching manufacturer code.

LEFT JOIN

A left join will return all rows from the first table, whereas an inner join will only return rows from both tables where the conditions are met. If the join condition isn’t met then a left join will still return all rows from the first table but with null values for the second table fields.

select m.manu_code, m.manu_name, v.reg
from manufacturer m
left join vehicle v
on m.manu_code = v.manu_code;

This query returns all rows from the first table, with Null values for the fields from the second table where the conditions are not met.

10 rows in set (0.01 sec)

LEFT JOIN vs INNER JOIN

SELECT m.manu_code, m.manu_name, v.reg from manufacturer m 
inner join vehicle v 
on m.manu_code = v.manu_code;

This query will return less rows that the same query using a left join as it excludes the rows where the condtions are not met.

STORED PROCEDURE

The show create table on vehicles shows that the vehicles table has a foreign key referencing the manufacturer table.

KEY `vehicle_ibfk_1` (`manu_code`),
  CONSTRAINT `vehicle_ibfk_1` FOREIGN KEY (`manu_code`) REFERENCES `manufacturer` (`manu_code`)

The manufacturer table has no foreign key constraint.

Write the code first for a query

 select v.reg, v.manu_code, m.manu_name, v.mileage, v.price
    -> from vehicle v
    -> inner join manufacturer m
    -> on v.manu_code = m.manu_code
    -> where v.price < 10000;

This query returns the registration, manufacturer code and name, mileage and price for each car with mileage where the codes match in both tables and where the price is less than 10000

PROCEDURE `price_less_than`(p decimal(8,2))
    DETERMINISTIC
BEGIN
select v.reg, v.manu_code,m.manu_name,v.mileage, v.price
from vehicle v
inner join manufacturer m
on v.manu_code = m.manu_code
where v.price <p
order by v.price;
END

This procedure returns the specified details for all vehicles where the price is less than a particular price provided as an argument to the procedure.

CALLING A PROCEDURE

call price_less_than(10000);

CRUD functions

• Create/Insert
• Read
• Update
• Delete

describe bus

SHOW create table bus

BUS   | CREATE TABLE `BUS` (
  `reg` varchar(15) NOT NULL,
  `maxPassengers` int(11) DEFAULT NULL,
  `fuel` enum('Diesel','Petrol','Electric') DEFAULT 'Diesel',
  PRIMARY KEY (`reg`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

describe driver;

show create table driver

CREATE TABLE `driver` (
  `licenceNo` varchar(20) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `busReg` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`licenceNo`),
  KEY `busReg` (`busReg`),
  **CONSTRAINT `driver_ibfk_1` FOREIGN KEY (`busReg`) REFERENCES `bus` (`reg`) ON DELETE CASCADE**
) ENGINE=InnoDB DEFAULT CHARSET=latin1

describe car;

show create table car;

CREATE TABLE `car` (
  `registration` varchar(15) NOT NULL,
  `make` varchar(20) DEFAULT NULL,
  `model` varchar(20) DEFAULT NULL,
  `colour` varchar(10) DEFAULT NULL,
  `mileage` int(11) DEFAULT NULL,
  `engineSize` float(2,1) DEFAULT NULL,
  PRIMARY KEY (`registration`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

describe person

show create table person

CREATE TABLE `person` (
  `personID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` enum('M','F') DEFAULT 'M',
  `dob` date DEFAULT NULL,
  `isStudent` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`personID`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 

INSERT

When entering values for all the fields there is no need to specify the fields.

INSERT into ... values ();

INSERT INTO TABLE (..,..,..,) VALUES (.., .. ,..);

INSERT into person values('John',23,'Male', true);

This will result in an error as there are 5 columns and it will not know which values are for which columns. Specify the associated values.

Column count doesn’t match value count at row 1

INSERT- error if no value supplied for a primary key which cannot be null (and it doesn’t auto-increment)

insert into driver(name) values ("Mary");

results in an error: Error code:1364 Field 'LicenceNo doesn’t have a default value.`

The describe driver command shows thay LicenceNo is a primary key and it cannot be NULL.

describe driver; shows that LicenceNo is a primary key and it cannot be NULL therefore

.

insert into driver(name, licenceNo) values ("Bob","RN2423");

This adds a new row with for the two columns not supplied with values.

INSERT - Error as foreign key constraint fails - when the foreign key doesn’t exist on the other table

insert into driver(name, licenceNo, busReg) values ("Gillian","W12X45","201-G-123");

Results in an error as the bus reg does not already exist in the bus table.

Cannot add or update a child row: a foreign key constraint fails (bus.driver, CONSTRAINT driver_ibfk_1 FOREIGN KEY (busReg) REFERENCES bus (reg) ON DELETE CASCADE)

INSERT - Error as duplicate entry for the primary key already used for another row.

Cannot have the same primary key as another row.

insert into bus(reg, maxPassengers, fuel) values("12-G-1323",34, "Diesel");

ERROR 1062 (23000): Duplicate entry ‘12-G-1323’ for key ‘PRIMARY’

INSERT - Error Data truncated …

insert into bus values("191-D-45890", 120, "Ethanol");

ERROR 1265 (01000): Data truncated for column ‘fuel’ at row 1

describe bus

The Type for the Fuel field is ENUM which is a list of permitted values that are enumerated explicitly at the table creation time. SeeSection 11.3.5 The Enum Type. There are only three possible values for fuel allowed which doesn’t include ethanol. The fuel Field is of Type (enum(“Diesel”,“Petrol”,“Electric”)).

### UPDATE ...SET ... CASE WHEN ...;

update person
set age = 
case
    when age is null then 18
    else age +1
end;

UPDATE ... SET ... WHERE ... = ...;

update car 
set make = "Ford Motors" 
where make = "Ford";

select * from car;

UPDATE ... SET ...=CONCAT() WHERE ... LIKE ... OR ... LIKE ...

The concat function joins two strings together which we want to do here on a condition.

update driver
set licenceNo = CONCAT("T-", licenceNo)
WHERE licenceNo like  "%F%"
OR licenceNo like "%R%";

This query updates the licenceNo for rows that meet the where condition. The concat function joins two strings together

select * from driver;