Hi clever people!
The following is part of a TEST QUESTIONNAIRE. Although in the following two part question it
seems silly to create a function and there are other, better ways to do this the test questionnaire
is about CREATING A FUNCTION. Please bear that in mind.
The following are the two tables followed by the two questions the test poses:
CREATE TABLE TestTable1
(
ID INT,
Name VARCHAR(255)
)
INSERT INTO TestTable1
VALUES(1, 'Test1'),
(2, 'Test2'),
(3, 'Test3'),
(4, 'Test4'),
(5, 'Test5'),
(6, 'Test6')
CREATE TABLE TestTable2
(
ID INT,
JobDescription VARCHAR(255),
[Hours] INT
)
INSERT INTO TestTable2
VALUES (1, 'Developer', 10),
(2, 'Web Developer', 8),
(3, 'Architect', 12)
Here are the questions:
a) Create A FUNCTION that takes in a name as parameter and returns the ID for the
corresponding name from TestTable1.
(For the sake of the test the function is called TestTableFunction but feel free to use your own if you want)
b) How would yo optimize the following query taking into account that any text could be passed
as a parameter to the function created above, not just 'Test2'?
SELECT *
FROM TestTable1 a
JOIN TestTable2 b
ON b.ID = a.ID
WHERE a.ID = TestTableFunction('Test2')
I'm looking for answers that are better than mine so I'm asking you clever people for help.
Kind regards