SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE COURSE(id INT, name TEXT, `limit` INT)
✓
CREATE TABLE ASSIGNMENTS(id INT, course_code INT)
✓
INSERT INTO COURSE VALUES (1, 'course1', 4), (2, 'course1', 2);
✓
INSERT INTO ASSIGNMENTS VALUES
  (1, 1), (2, 1), (3, 1),
  (4, 2), (5, 2);
✓
SELECT c.id, c.name, c.`limit`, 
       c.`limit` - COUNT(*) available 
FROM COURSE c INNER JOIN ASSIGNMENTS a
ON c.id = a.course_code
GROUP BY c.id, c.name


id
name
limit
available


1
course1
4
1

2
course1
2
0


SELECT c.*, 
       c.`limit` - (SELECT COUNT(*) FROM ASSIGNMENTS a WHERE a.course_code = c.id) available 
FROM COURSE c


id
name
limit
available


1
course1
4
1

2
course1
2
0