SQL Tutorial: Project - Exam System 🎯

Welcome to our SQL Tutorial for a beginner-friendly Exam System project! By the end of this comprehensive guide, you'll have a solid understanding of SQL and be able to create, manage, and query an Exam System database.

Introduction 📝

SQL (Structured Query Language) is a powerful language for managing and querying databases. In this project, we'll build an Exam System using SQL to store, retrieve, and manipulate student exam data.

Why SQL? 💡

SQL is a fundamental skill for developers and data analysts. It's used in various applications, including websites, mobile apps, and business intelligence tools. By learning SQL, you'll be able to work with and understand data more effectively.

Getting Started 💡

Creating the Database 📝

Let's create our Exam System database with the following tables:

Students (id, name, email)
Courses (id, name)
Exams (id, course_id, date)
StudentScores (id, student_id, exam_id, score)

Here's a simple SQL script to create these tables:

sql

CREATE DATABASE ExamSystem;

USE ExamSystem;

CREATE TABLE Students (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  email VARCHAR(255)
);

CREATE TABLE Courses (
  id INT PRIMARY KEY,
  name VARCHAR(255)
);

CREATE TABLE Exams (
  id INT PRIMARY KEY,
  course_id INT,
  date DATE,
  FOREIGN KEY (course_id) REFERENCES Courses (id)
);

CREATE TABLE StudentScores (
  id INT PRIMARY KEY,
  student_id INT,
  exam_id INT,
  score INT,
  FOREIGN KEY (student_id) REFERENCES Students (id),
  FOREIGN KEY (exam_id) REFERENCES Exams (id)
);

Quick Quiz

Question 1 of 1

What does the `CREATE DATABASE` command do in the provided SQL script?

Inserting Data 💡

Now that we have our tables, let's insert some data:

sql

INSERT INTO Students (id, name, email) VALUES
  (1, 'John Doe', 'john.doe@example.com'),
  (2, 'Jane Smith', 'jane.smith@example.com');

INSERT INTO Courses (id, name) VALUES
  (1, 'Computer Science'),
  (2, 'Mathematics');

INSERT INTO Exams (id, course_id, date) VALUES
  (1, 1, '2022-01-01'),
  (2, 2, '2022-01-15');

Quick Quiz

Question 1 of 1

Which SQL command is used to insert data into a table?

Querying Data 💡

Now that we have some data, let's query it:

sql

SELECT * FROM Students;
SELECT * FROM Courses;
SELECT * FROM Exams;

These queries will return all records from the respective tables. You can also filter, sort, and aggregate data using various SQL commands like WHERE, ORDER BY, and GROUP BY.

Finding Student Scores 💡

Finally, let's find the score of a specific student for a specific exam:

sql

SELECT StudentScores.score
FROM StudentScores
JOIN Students ON StudentScores.student_id = Students.id
JOIN Exams ON StudentScores.exam_id = Exams.id
WHERE Students.name = 'John Doe'
  AND Exams.date = '2022-01-01';

This query joins the Students, Exams, and StudentScores tables to find John Doe's score on the exam held on January 1, 2022.

Quick Quiz

Question 1 of 1

What is the purpose of the `JOIN` command in the provided SQL query?

That's it for our SQL Tutorial for an Exam System! You've now learned the basics of SQL and can create, manage, and query a database. Keep practicing and exploring SQL to improve your skills! 💪

Remember to share your feedback and let us know how this tutorial helped you! 📝