lab12.sql

-- Add an assignment with NULL due_date
INSERT INTO assignments (course_id, title, status) 
VALUES ('COMP1238', 'Assignment with no date', 'Not Started');

SELECT * FROM assignments
LIMIT 10; 

SELECT count(*) FROM courses;

SELECT min(due_date) FROM assignments;

SELECT *
FROM  courses
WHERE course_name LIKE 'Intro%';

SELECT sqlite_version();

SELECT upper('ABCxyz');

SELECT length('abcde');

SELECT 7*5;

SELECT concat('ABC', '-', 'xyz');

SELECT date();

SELECT strftime('%Y', due_date) AS Year, * 
FROM assignments;

-- SUBSTRING(string, start, length)
SELECT DISTINCT SUBSTRING(course_id, 1, 4) 
FROM courses;

-- Count how many courses there are with each prefix like 'MATH' and 'COMP'
SELECT SUBSTRING(course_id, 1, 4) AS prefix, count(*)
FROM courses
GROUP BY prefix;

SELECT *
FROM  assignments
WHERE status != 'Completed'
ORDER BY due_date;

-- Use this query as a reference for the next step
SELECT course_id, title, status, due_date
FROM assignments
WHERE status != 'Completed'	
  AND course_id LIKE 'COMM%'
  AND due_date < '2024-12-31'
ORDER BY due_date;

-- Exercise Questions
1  SELECT concat (course_name,' - ', semester)
 FROM courses;

2  SELECT course_id, course_name, lab_time
 FROM courses
WHERE lab_time like '%Fri%';

3 SELECT *
FROM assignments
WHERE due_date > date ('now');

4 SELECT count(*)
 FROM assignments
 GROUP by status;

5 SELECT MAX(course_name) FROM courses;

6 SELECT upper(course_name) FROM courses;

7 SELECT title FROM assignments
WHERE due_date like '%09%';

8 SELECT * FROM assignments
WHERE due_date IS NULL