Querying and Reporting

Querying and reporting are important aspects of an SQL project as they demonstrate the power of SQL in retrieving and analyzing data. Here are some examples of querying and reporting operations that you can cover in your SQL PROJECT:

1. Basic Data Retrieval:

• Retrieve all users from the Users table:

SELECT * FROM Users;

• Get the course names and descriptions from the Courses table:

SELECT course_name, description FROM Courses;

2. Filtering Data:

• Retrieve users who have a Gmail email address:

SELECT * FROM Users WHERE email LIKE ‘%@gmail.com’;

• Get the lessons for a specific course:

SELECT lesson_title FROM Lessons WHERE course_id = 1;

3. Sorting Data:

• Retrieve users from the Users table in alphabetical order of their usernames:

SELECT * FROM Users ORDER BY username ASC;

• Get the lessons for a specific course in descending order of their lesson IDs:

SELECT lesson_title FROM Lessons WHERE course_id = 1 ORDER BY lesson_id DESC;

4. Aggregating Data:

• Calculate the total number of users in the Users table:

SELECT COUNT(*) AS total_users FROM Users;

• Determine the average completion rate of lessons:

SELECT AVG(completed) AS completion_rate FROM UserProgress;

5. Joining Tables:

• Retrieve the username and course name for users enrolled in courses:

SELECT Users.username, Courses.course_name

FROM Users

INNER JOIN Enrollment ON Users.user_id = Enrollment.user_id

INNER JOIN Courses ON Enrollment.course_id = Courses.course_id;

• Get the lesson title and associated quiz questions for a specific course:

SELECT Lessons.lesson_title, Quiz.question

FROM Lessons

INNER JOIN Quiz ON Lessons.lesson_id = Quiz.lesson_id

WHERE Lessons.course_id = 1;

6. Reporting:

• Generate a report showing the total number of users enrolled in each course:

SELECT Courses.course_name, COUNT(*) AS enrolled_users

FROM Courses

INNER JOIN Enrollment ON Courses.course_id = Enrollment.course_id

GROUP BY Courses.course_name;

• Create a report displaying the average completion rate for each lesson:

SELECT Lessons.lesson_title, AVG(UserProgress.completed) AS completion_rate

FROM Lessons

LEFT JOIN UserProgress ON Lessons.lesson_id = UserProgress.lesson_id

GROUP BY Lessons.lesson_title;