Background: You are a database specialist for SchoolHub, the school management system we've been developing. The school administration relies on you to extract important information from the database. They've given you a set of tasks to accomplish using SQL. Essentially, you need to build queries that will form a small "report" about the school.

Imagine this scenario: It's mid-year review time, and various stakeholders (principal, counselors, department heads) have questions like: - "How many students are currently enrolled, and how are they distributed by grade?"

• "List all students who are not currently enrolled (have left or transferred) so we can follow up on their records."
• "We need an updated directory of all teachers, including which department they belong to."
• "Are there any duplicate student names or other anomalies we should be aware of?" (perhaps to check data integrity; maybe a bit advanced, but you might check for duplicate full names using DISTINCT or COUNT, etc.)
• "Provide a list of all courses offered and which teacher is teaching each." (This last one might require a Courses table and possibly a relationship, which is a sneak peek to joins in a later unit, but we could simulate with available data or note it's a future capability.)

Your project is to write SQL queries that answer these kinds of questions. We'll focus on what can be done with the single-table queries (SELECT, WHERE, DISTINCT) and basic structure we have now, possibly supplemented by a simple Courses table to demonstrate a multi-table scenario at a basic level.

Project Tasks Overview:

1. Student Enrollment Report: Create a query (or set of queries) that shows the number of enrolled students per grade level. This could involve using COUNT() (which is a new SQL aggregation, but let's stretch a bit). Alternatively, if sticking strictly to what we've learned, we could fetch all enrolled students and sort/group them by grade, and count manually or visually. However, since counting is so useful, you might use:

(This introduces GROUP BY and COUNT from the next unit, but it might be allowed to use in a project since it's very pertinent. If not, you could at least retrieve all enrolled students sorted by GradeLevel and then note the counts for each grade from the result. The better approach is the above query though, which outputs each grade with the count.) This gives an overview of enrollment by grade. Ensure your output has each grade 9-12 with the correct count. If one grade has zero, it might not show up if no rows (or you could left join with a grades reference if we had one, but we'll keep it simple).

• Inactive Students List: Write a query to list all students who are not currently enrolled (IsEnrolled = 0). Include their name, and maybe their last known grade level (if they left, they had a grade when they left, which is what's in their record).

• SELECT FirstName, LastName, GradeLevel

• This is straightforward. It provides the administration a list of students who have left or are not active, so they can, for example, archive those files or follow up on transfers. Verify it shows the ones you expected (if you only marked one as 0, you'll see that one).

• Teacher Directory: Produce a query that shows each teacher's full name and department. Sort the list by department, then last name.

• SELECT FirstName + ' ' + LastName AS TeacherName, Department

• (In MySQL or other dialect, use CONCAT or just list them separated by space in the SELECT if needed.)

This gives a nicely sorted directory of teachers. Check that every teacher you entered is listed correctly under their department. The stakeholders can use this for quick reference or to verify department assignments.

• Data Consistency Check (Duplicate Names): For internal data audit, let's ensure we don't have two different students with the exact same first and last name (which could cause confusion). This is a bit advanced because we'd typically use grouping and count to find duplicates. But you can approach it with DISTINCT as a rough check:

• First, see how many distinct full names exist vs total students.

• SELECT COUNT(*) AS TotalStudents, COUNT(DISTINCT FirstName + ' ' + LastName) AS UniqueNames

• (Again using SQL Server syntax for concatenation; adjust accordingly. This introduces another aggregate concept but combined with distinct it's manageable.)

If TotalStudents > UniqueNames, that means some name is duplicated.

• To actually list which names are duplicated: that would require concepts like GROUP BY and HAVING count > 1. If you know them, you can do:

• SELECT FirstName, LastName, COUNT(*) as NameCount

• This would list any name that appears more than once, along with how many times. In a small dataset, you might not have any, which is fine (the result is empty meaning all student names are unique). If you do have duplicates (maybe you entered two "John Smith"s intentionally to test), they'd show. This is more of a data maintenance query than something asked by admin, but it's a good demonstration of ensuring data quality. (Feel free to skip or mark as an "enrichment" step if not comfortable with GROUP BY yet, as it's unit 3 content. It's included to show how DISTINCT and aggregates can be used for quality checks.)

• Courses and Enrollment (Stretch Goal): The principal also wants to know what courses are offered this semester and who teaches each, as well as how many students are in each course. Now, we haven't fully designed the courses and enrollment system in our schema yet (that would involve multiple tables and joins, which are Unit 7). But we can simulate a bit:

• Create a Courses table if not already:

• CREATE TABLE Courses ( CourseID INT, CourseName VARCHAR(100), TeacherID INT

);

• Insert a few courses, linking a teacher via TeacherID:

• INSERT INTO Courses (CourseID, CourseName, TeacherID) VALUES

(101, 'Algebra I', 1), (102, 'Biology I', 2), (103, 'World History', 3);

• Assume TeacherID references the Teachers table (John Smith teaches Algebra, Jane Doe teaches Biology, Emily Davis teaches History in this example).

• Now to answer "who teaches each course":

We would normally do a JOIN between Courses and Teachers to get teacher names. Without going deep, you can achieve this by either a subquery or performing two queries (one to get teacher ID and then looking up teacher name). But since we haven't covered JOINs yet, perhaps we keep it simple: just manually correlate from what we know. Or, we can do a pseudo-join with a nested select:

• SELECT CourseName, (SELECT LastName FROM Teachers WHERE Teachers.TeacherID = Courses.TeacherID) AS TeacherLastName

• This will output each course and the last name of its teacher by looking up the Teachers table for each row. It's a subquery approach to a join. If allowed, try it. If not, you can simply SELECT * FROM Courses; and then mentally or in a report map teacher IDs to names via the teacher directory.

• Counting students per course is tricky without an Enrollment table linking students to courses. If we had one, we'd use similar grouping. We might skip this since it's beyond current scope. Or, if we want, we could imagine each student has a CourseID (not realistic, as each student takes multiple courses, but let's say "homeroom" course or something). That complicates schema beyond our current state.

• The key takeaway: the courses part foreshadows that to answer some questions, we will need multiple tables and joins (coming in later units). For now, we at least set up the idea of a Courses table and see who teaches what via the TeacherID foreign key.

In summary, the project tasks you completed involve: - Creating necessary tables and data (DDL/DML from prior unit and this unit’s lab tasks). - Writing multiple queries to get specific answers (DQL), each addressing a particular need. - Optionally using simple aggregate functions (COUNT) and grouping for summary info, which is a slight peek at the next unit but very relevant for real tasks. - Checking your results and adjusting any data as needed (for example, if no 12th graders were in data but you need to test that scenario, you might insert one).

Let's go through the project in a stepwise manner, as if we are building a report:

Step 1: Set up/Validate the Database Schema

At this point, ensure you have the core tables needed: - Students (with sample data, including some enrolled and some not). - Teachers (with sample data). - Courses (optional for extended query, with a link to Teachers).

If any table is missing, create it (DDL). If any data is missing (e.g., you want a scenario of an unenrolled student, or a specific duplicate name to test, etc.), insert it (DML). This is the foundation. In our case, let's say: - Students table has, for example, 5 students as earlier (Alice, Bob, Charlie, Diana, Evan). - We updated Evan to IsEnrolled = 0 to simulate a student who left. - Teachers table has 3 teachers (John, Jane, Emily). - Courses table has 3 courses, tied to those teachers.

Step 2: Query 1 – Enrollment by Grade

We write the query to count enrolled students per grade:

Run it in the lab. Suppose it returns:

GradeLevel | NumStudents 9 | 1 10 | 2 11 | 1

(This would mean in our data maybe one 9th grader enrolled, two 10th graders, one 11th grader, and none for 12th in the sample.) If one grade is missing entirely (like no seniors), it won't show up. That's fine. If we wanted to ensure 12 shows as 0, we'd need a reference table of all grades, which is beyond our scope, so we interpret the absence as 0. We verify this matches our knowledge: yes, we had e.g., Evan (grade 9, but left, so not counted), Alice & Bob (grade 10, enrolled), Charlie or Diana (11, enrolled), and maybe no 12. Good.

Step 3: Query 2 – List of Unenrolled Students

We write:

In our data, that should return Evan Williams, grade 9. If we had others left, they'd show too. We verify that's correct (Evan is the one we set to 0). Good. We note that as a list for the admin.

Step 4: Query 3 – Teacher Directory

Write:

The output might be:

TeacherName | Department Emily Davis | History John Smith | Mathematics Jane Doe | Science

(notice they sorted by department alphabetically, "History", "Mathematics", "Science"; within each, if multiple, by last name). This matches our teacher input. We can present this as requested.

Step 5: Query 4 – Duplicate Name Check (internal)

Optional but educational. We do:

We suspect no duplicates, so likely no rows returned. If none, that's a good sign (or we intentionally add a duplicate to test, e.g., add another "Alice Johnson", then rerun and see "Alice Johnson | 2"). If a duplicate appears, note the name – it means either two actual different people share a name (which could happen in real life, not necessarily an error), or an accidental double entry. The school might want to assign ID or middle name to differentiate. Either way, we've demonstrated a data audit query.

Step 6: Query 5 – Courses and Teachers

We have a Courses table with teacher IDs. We attempt a join or subquery:

This should output:

CourseName | Teacher Algebra I | Smith Biology I | Doe World History | Davis

assuming those teacher IDs matched John Smith, Jane Doe, Emily Davis as given. If this works, great – we effectively joined the two tables to get teacher names. If the subquery method is confusing or unsupported in the lab environment, we could just SELECT * FROM Courses; to get:

CourseID | CourseName | TeacherID 101 | Algebra I | 1 102 | Biology I | 2 103 | World History | 3

Then cross-reference with teacher table mentally (which we had from the directory). But let's assume the subquery approach is acceptable.

We realize listing courses and their teacher is fully within our capability now. If we wanted to count students per course, we'd need an Enrollment table linking Students to Courses (which presumably might be created in a future unit about JOINs and relationships). We skip that for now or simply note: "Feature to be added once course enrollment tracking is implemented."

Step 7: Review and Refine

Now we have a set of queries answering each requirement: - Enrollment counts by grade (with possible knowledge of how to do that in SQL). - List of who left. - Teacher directory. - Data health check. - Course offerings with instructors.

We run through the Final Project Checklist to ensure we've done everything:

• Database Schema Complete: Did we create or have all needed tables (Students, Teachers, etc.)? Are all necessary columns present with correct data types? (Yes, we reviewed and added Courses).

• Data Populated: Do we have sample data that covers scenarios like at least one student per grade (except maybe senior)? At least one student with IsEnrolled=0? At least one teacher per department scenario? (We might add more if needed to test extremes. For instance, if we had no senior, maybe add a dummy to see how query behaves.)

• Queries Written: We have written all queries for the requested information. They all run without syntax errors in the lab. (Double-checked each).

• Correctness of Output: Cross-verify each query's output with the raw data:

• The sum of counts in Query1 should equal the number of enrolled students in the Students table.

• Query2's listed students should each have IsEnrolled=0 in the table.

• Query3's list should match exactly the Teachers table content sorted as specified.

• Query4 either shows known duplicates or none; if none, consider if we want to simulate one for demonstration (optional).

• Query5's course-teacher mapping should match the TeacherID link accurately.

• Clarity and Formatting: We used aliases like "TeacherName" for readability. We sorted results where a sorted presentation was asked for. We capitalized SQL keywords for professionalism. Queries are indented well.

• No Unintended Effects: Our SELECT queries don't modify data (good). Any DDL/DML we did was intentional for setup. We didn't accidentally drop or alter something incorrectly. If we did test DDL like altering a table, we ensured it didn't break a query (e.g., if we added an Email column, none of our queries depended on it so it's fine).

• Documentation: We commented our queries or at least can explain what each does (in a project report, we might include a brief description before each query output – which is essentially what we're doing in this write-up).

• Backup (lab perspective): Not as crucial here, but always prudent to save your final SQL script of these queries. We have them recorded in this project doc.

Now, let's compare against the Student-Friendly Rubric to self-assess:

• Understanding and Usage of SQL Concepts (40%): Did we use SELECT, FROM, WHERE, DISTINCT appropriately to get the needed data? Excellent: We wrote a variety of queries using these clauses correctly. We even extended with a couple of aggregates for counting, showing a bit of advanced initiative. Needs Improvement: (would have been if queries were incorrect or used wrong clauses). Our queries all function as intended – we demonstrate proficiency.

• Accuracy of Results / Functionality (30%): Do the queries answer the questions and produce the correct results? We tested each and they match the expected outputs from our data. For example, the count of enrolled per grade is correct, the list of unenrolled is accurate, etc. If something was off, we debugged it. So the report is functional and correct. If any query gave an unexpected result initially (perhaps a logic error like using OR when we meant AND, etc.), we caught and fixed it.

• Query Organization & Readability (20%): We formatted our SQL neatly (each query is structured with clauses on new lines, proper indentation). We used aliases for readability. We added comments to describe complex parts where needed (like explaining a subquery or a HAVING clause for duplicates). This makes our SQL script easy to read for someone else (or for grading). Variable names and aliases are clear (e.g., "NumStudents" as alias for count, "TeacherName" alias). No confusing or arbitrary things.

• Use of Best Practices (10%): We avoided SELECT * in the final report queries except where justified (the enrollment count query selects specific grouping columns, etc.; teacher directory selects needed fields; we did use * for a quick check or demonstration maybe, but not in final outputs). We filtered data in the SQL instead of pulling everything and filtering manually, demonstrating understanding of where conditions. We included the semicolons and followed SQL syntax rules carefully. We were cautious with changes (we didn't, for example, accidentally count unenrolled as enrolled or drop important data). If we tried something like using a subquery or join, we did so carefully verifying it didn't produce cross joins or duplicates. Essentially, we adhered to the guidance given throughout the unit.

Now that we've completed the Unit 2 project, let's reflect on what we've learned and how it prepares us for the SQL Coding Specialist exam:

• We have a solid grasp of basic query syntax. Writing SELECT ... FROM ... WHERE ... is becoming second nature. In the exam (and real life), this is fundamental. We can confidently extract data based on conditions.

• We understand different data types and ensured our schema uses them appropriately (int for IDs, varchar for text, date for DOB, etc.). This aligns with exam objectives about recognizing data types.

• We differentiated among SQL command categories. In this project, we mostly wrote DQL (SELECTs), but setting up and tweaking the database involved DDL (creating tables) and DML (inserting/updating data). For the exam, being clear on what type of command is needed for a task is crucial (for example, "How do you add a new row?" — that's an INSERT, which is DML, not a SELECT).

• Through hands-on practice, we've seen how important it is to verify results. On the exam, there might be questions where you are given a scenario and need to choose the correct query. Our practice of interpreting questions (like the admin requests) and formulating queries is excellent preparation.

• We encountered a taste of aggregates (COUNT) and grouping, which are coming in the next unit. It didn't trip us up; in fact, it empowered our report. This forward peek means we'll likely find the upcoming content (Unit 3 on more SQL capabilities) approachable since we've already applied some of it.

• We also learned the importance of careful logic (AND vs OR, where to put conditions, etc.). A single mistake like using OR incorrectly can change the meaning entirely. Now we double-check logic, which is a good habit for both exam and work.

• The reflection on duplicates and data quality isn't explicitly an exam objective at this stage, but it teaches us to think critically about results. On an exam, you might be asked, "Why would you use DISTINCT?" or "How can you find something that occurs more than once?" We have an applied understanding of that now.

• Lastly, by building a mini-project from scratch, we gained confidence. If a certification exam question says "Given a table structure and a problem, write the SQL query to solve it," we know we can break it down: figure out what needs to be filtered (WHERE), what to select, whether distinct is needed, etc. We've essentially done mini versions of such tasks throughout this project.

Take a moment to celebrate how far you've come in just one unit of SQL: you went from not knowing SQL at all to writing non-trivial queries against a schema you designed. If any part felt challenging, that's normal – with practice it's getting easier. Look back at the objectives at the start of Unit 2 (basics of SQL, data types, DQL vs DML vs DDL, simple queries) – we've met each one: - Basics of SQL: ✅ (we conversed with a database meaningfully) - Data types: ✅ (we chose and used them in our tables) - DQL vs DML vs DDL: ✅ (we explicitly did examples of each and know the difference) - Simple queries with SELECT/FROM/WHERE/DISTINCT: ✅ (we did many, with confidence)

As you prepare for the certification exam, keep practicing writing queries for different scenarios. Try to explain each query in words – if you can do that, you truly understand it. And remember, the database is your friend: you can always run queries to test your thinking, just like we did in the lab.

By completing this project, you're not only ready for exam questions on Unit 2 content but also well-positioned to tackle the next units, where we'll build on this foundation with more complex SQL features (like sorting, joining tables, aggregating data, etc.). Keep up the great work, and happy querying!