Question

HOW TO:

1. Write the SQL statement to show workers with names that have the letter ? before the last character.
2. Write the SQL statement to show all staff where the 2nd letter of their last names is ′?′
3. Write the SQL statement to show all staff where the 1stletter of their first names is ′?′ and 3rd letter ′ℎ'

DROP TABLE IF EXISTS staff;
CREATE TABLE staff (
staffNo VARCHAR(4) UNIQUE,
fName VARCHAR(16),
lName VARCHAR(16),
position VARCHAR(16),
sex VARCHAR(1),
dob DATETIME,
salary DOUBLE DEFAULT 0,
branchNo VARCHAR(4),
CONSTRAINT pk_staff PRIMARY KEY (staffNo)
);
INSERT INTO staff (staffNo,fName,lName,position,sex,DOB,salary,branchNo)
VALUES
('SL21','John','White','Manager','M','1945-10-01',30000,'B005'),
('SG37','Ann','Beech','Assistant','F','1960-11-10',12000,'B003'),
('SG14','David','Ford','Supervisor','M','1958-03-24',18000,'B003'),
('SA9','Mary','Howe','Assistant','F','1970-02-19',9000,'B007'),
('SGS','Susan','Brand','Manager','F','1940-06-03',24000,'B003'),
('SL41','Julie','Lee','Assistant','F','1965-06-13',9000,'B005'),
('SB0', NULL, 'LN0', 'Manager', 'M', '2000-01-01 00:00:00', 9999,
'B000'),
('SB1', 'FN1', NULL, 'Manager', 'M', NULL, 9999, 'B000'),
('SB2', 'FN2', 'LN2', NULL, NULL, NULL, 9999, 'B000'),
('SB3', 'FN3', 'LN3', NULL, NULL, NULL, 9999, NULL),
('SB4', 'FN4', NULL, 'Manager', NULL, NULL, 9999, NULL),
('SB5', 'FN5', NULL, 'Supervisor', NULL, NULL, NULL, 'B001'),
('SB6', 'FN6', NULL, 'Assistant', NULL, NULL, 9999, 'B002'),
('SB7', 'FN7', NULL, 'Manager', NULL, NULL, NULL, 'B003'),
('SB8', 'FN8', NULL, 'Manager', NULL, NULL, 9999, 'B005'),
('SB9', 'FN9', NULL, 'Assistant', NULL, NULL, NULL, NULL);

Answer 1

Dear Student ,

As per the requirement submitted above , kindly find the below solution.

This demonstration is using MySQL.

Question 1:

SQL Query :

SELECT * FROM staff where fName like '%i_'

Screen in MySQL:

*****************************

Question 2:

SQL Query :

SELECT * FROM staff where lName like '_e%';

Screen in MySQL:

*****************************

Question 3:

SQL Query :

SELECT * FROM staff where fName like 'e_h%';

Screen in MySQL:no first name in the table starts with e and third is h.

*****************************

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.