Hi, I am a freshly baked SQL practitioner and was looking for some practical exercises to increase my knowledge but have a bit of a problem with how to write a code to achieve below results in SQL:
hello freshly baked sql practitioner. What have you tried so far?
For (1) and (2), there should be a table that contains which assignments are allowed, but there isn't. The actual Tasks table should not, and cannot safely, be used to determine all allowable assignments.
I think it is based on what we can see from data for example TaskTypeID 5 and 3 was done by the same agent so assuming this is the way to get any info about this. I agree that without allowed tasks it is not possible to define exactly all possibilities.
I will post as soon as I get on my laptop as I asked for help from my work one.
Exactly, but you cannot count on the current Tasks table having every possible valid combination of TaskTypes for all AgentTypes. You must have a separate table for those. Even if I were to use the current existing table to determine that, I would put the results into a new table, so that the new table can be used from here on.
Btw, a prefix of "tbl_" is from the '90s (or before) and is never done in modern modeling by anyone with any real expertise.
Thanks for "tbl_ " I will keep this in mind and give feedback once I will resolve those 3 points 
Now that I'm done whining -- sorry about that! -- I'm working on a detailed response with code.
I got to this point but do not know how to count and group it by AgentTypes...
So are you working with Microsoft SQL Server or another product?
mySQLworkbench -- the guy from the course i did pointed this one as a free one to use to learn
i tried this way but does not count:
For SQL Server, here's data setup and query 1.:
USE tempdb;
IF OBJECT_ID('dbo.tbl_Agents') IS NOT NULL DROP TABLE dbo.tbl_Agents;
CREATE TABLE dbo.tbl_Agents ( AgentID int NOT NULL, Name varchar(100) NOT NULL, AgentTypeID smallint NOT NULL );
TRUNCATE TABLE dbo.tbl_Agents;
INSERT INTO dbo.tbl_Agents VALUES
(578349, 'John Doe', 3),
(569347, 'Jane Doe', 1),
(548937, 'Bob Smith', 4),
(578934, 'Joe Jones', 2),
(562932, 'Billy Johnson', 5);
IF OBJECT_ID('dbo.tbl_AgentTypes') IS NOT NULL DROP TABLE dbo.tbl_AgentTypes;
CREATE TABLE dbo.tbl_AgentTypes ( AgentTypeID smallint NOT NULL, Name varchar(50) NOT NULL );
TRUNCATE TABLE dbo.tbl_AgentTypes;
INSERT INTO dbo.tbl_AgentTypes VALUES
(3, 'Sales'),
(1, 'PreSales'),
(4, 'Consultant'),
(2, 'Operations')
IF OBJECT_ID('dbo.tbl_TaskTypes') IS NOT NULL DROP TABLE dbo.tbl_TaskTypes;
CREATE TABLE dbo.tbl_TaskTypes ( TaskTypeID smallint NOT NULL, Name varchar(100) NOT NULL )
TRUNCATE TABLE dbo.tbl_TaskTypes;
INSERT INTO dbo.tbl_TaskTypes VALUES
(1, 'Selling'),
(2, 'Presentation'),
(3, 'Production'),
(4, 'Consult'),
(5, 'Operations')
IF OBJECT_ID('dbo.tbl_Tasks') IS NOT NULL DROP TABLE dbo.tbl_Tasks;
CREATE TABLE dbo.tbl_Tasks ( TaskID int NOT NULL, TaskTypeID smallint NOT NULL, AgentID int NOT NULL );
TRUNCATE TABLE dbo.tbl_Tasks;
INSERT INTO dbo.tbl_Tasks VALUES
(100001, 5, 578934),
(100002, 3, 578934),
(100003, 2, 569347),
(100004, 2, 578349),
(100005, 4, 548937),
(100006, 1, 578349),
(100007, 5, 578934),
(100008, 3, 578934),
(100009, 2, 569347),
(100010, 2, 578349),
(100011, 4, 548937),
(100012, 1, 578349)
--1.
SELECT AT.*, 'AgentTypeID currently has more than one Task type.'
FROM (
SELECT DISTINCT A.AgentTypeID
FROM dbo.tbl_Tasks T
INNER JOIN dbo.tbl_Agents A ON A.AgentID = T.AgentID
GROUP BY A.AgentTypeID, T.TaskTypeID
HAVING COUNT(*) > 1
) AS A
INNER JOIN dbo.tbl_AgentTypes AT ON AT.AgentTypeID = A.AgentTypeID
ORDER BY 1, 2wow that is a lot. thank you. but when i paste what you wrote i got an error. maybe it is because i am using this mysql and it is more mysql that sql server syntax ?
Yep, that's it. I didn't realize you were using mysql.
It's considered good etiquette / manners to provide data in the format above -- CREATE TABLE and INSERT statement(s) -- rather than as just a picture, since others can't run code against a picture :-).
this is a Microsoft SQL Server forum. MySQL is a different product and has it's own unique syntax hence the errors. You are better off going to a MySQL forum.
If you download the developer version of Microsoft SQL Server, we can help you. https://go.microsoft.com/fwlink/?linkid=866662
SELECT agt.Name AS 'Agent type', tt.Name AS 'Task type'
FROM tbl_Agents a
LEFT JOIN tbl_AgentTypes agt USING(AgentTypeID)
LEFT JOIN tbl_Tasks t USING(AgentID)
JOIN tbl_TaskTypes tt USING(TaskTypeID)
I will keep this in mind for the future