Classroom/College SQL Scripts Documentation

This documentation covers four SQL files: classroom.sql, classroom1.sql, joins.sql, and classroom2.sql. Each script demonstrates different aspects of database creation, data manipulation, and querying, primarily in a classroom/college context. Below, you'll find a detailed explanation, schema diagrams, and key usage details for each script.

---

classroom.sql

This file is empty and does not contain any SQL code.

• Purpose: Placeholder or template for future database operations.
• Usage: You can start writing your own SQL statements here for classroom-related database operations.

{
    "title": "Empty Template",
    "content": "This file is currently empty and ready for new SQL scripts."
}

---

classroom1.sql

This script sets up a simple classroom database with tables for students and a restricted driving table.

Key Actions

• Creates a database classroom.
• Defines a student table with fields: id, name, and age.
• Defines a DRIVE table with a strict constraint.
• Inserts two students into the student table.
• Selects all students.

SQL Code Breakdown

CREATE DATABASE IF NOT EXISTS classroom;
USE classroom;

CREATE TABLE IF NOT EXISTS student (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  age INT NOT NULL
);

CREATE TABLE DRIVE (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  CONSTRAINT CHECK ( age >= 18 and name = "aditya" )
);

INSERT INTO student VALUES (1, "ADITYA", 15);
INSERT INTO student VALUES (2, "VADIK", 15);

SELECT * FROM student;

Table Structures

Table	Columns	Constraints
student	id (INT, PK), name (VARCHAR), age (INT)	id is primary key, name and age NOT NULL
DRIVE	id (INT, PK), name (VARCHAR), age (INT)	id is primary key, age must be ≥18, name="aditya"

Entity Relationship Overview

erDiagram
    STUDENT {
        INT id PK
        VARCHAR name
        INT age
    }
    DRIVE {
        INT id PK
        VARCHAR name
        INT age
        CHECK age >= 18 AND name = "aditya"
    }

Loading

Noteworthy Constraints

• DRIVE Table: Only allows records where the driver is named "aditya" and is 18 or older.

Example Query

SELECT * FROM student;

Returns all students in the table.

---

joins.sql

This script demonstrates INNER JOIN operations between student and course tables in a college database context.

Key Actions

• Creates a college database.
• Sets up a student table with roll numbers and names.
• Sets up a course table with IDs and course names.
• Inserts sample data into both tables.
• Retrieves students enrolled in courses via an INNER JOIN.

SQL Code Breakdown

CREATE DATABASE IF NOT EXISTS college;
USE college;

CREATE TABLE IF NOT EXISTS student(
  rollno INT PRIMARY KEY,
  name VARCHAR(50)
);

INSERT INTO student (rollno, name) VALUES
  (101,"anil"),
  (102,"vadik"),
  (103,"aditya"),
  (104,"tejash"),
  (105,"divyanshu"),
  (106,"devyansh");

CREATE TABLE course(
  id INT PRIMARY KEY,
  course VARCHAR(50)
);

INSERT INTO course(id, course) VALUES
  (102,"english"),
  (105,"maths"),
  (103,"science"),
  (107,"computer science");

SELECT * FROM student INNER JOIN course ON student.rollno = course.id;

Table Structures

Table	Columns	Constraints
student	rollno (INT, PK), name (VARCHAR)	rollno is primary key
course	id (INT, PK), course (VARCHAR)	id is primary key

Entity Relationship Diagram

erDiagram
    STUDENT {
        INT rollno PK
        VARCHAR name
    }
    COURSE {
        INT id PK
        VARCHAR course
    }
    STUDENT ||--o| COURSE: "enrolled in"

Loading

INNER JOIN Explanation

• Returns only students who have a matching entry in course (i.e., rollno matches course id).

Example Output

rollno	name	id	course
102	vadik	102	english
103	aditya	103	science
105	divyanshu	105	maths

---

classroom2.sql

This script sets up a more advanced student table with marks, grades, and cities. It demonstrates complex SQL queries including aggregation, filtering, ordering, grouping, and HAVING clauses.

Key Actions

• Creates a college database.
• Defines a detailed student table with multiple properties.
• Inserts six students with various marks, grades, and cities.
• Runs several queries to extract useful data insights.

SQL Code Breakdown

CREATE DATABASE IF NOT EXISTS college;
USE college;

CREATE TABLE IF NOT EXISTS student(
  rollno INT PRIMARY KEY,
  name VARCHAR(50),
  marks INT NOT NULL,
  grade VARCHAR(1),
  city VARCHAR(50)
);

INSERT INTO student (rollno, name, marks, grade, city) VALUES
  (101,"anil",30,"F","Pune"),
  (102,"vadik",40,"E","Pune"),
  (103,"aditya",85,"B","Pune"),
  (104,"tejash",90,"A","Pune"),
  (105,"divyanshu",78,"D","Delhi"),
  (106,"devyansh",30,"F","Mumbai");

SELECT * FROM student WHERE marks BETWEEN 80 AND 90;
SELECT * FROM student WHERE CITY = "PUNE";
SELECT marks FROM student ORDER BY marks DESC LIMIT 3;
SELECT AVG(marks) FROM STUDENT;
SELECT * FROM student WHERE marks > 75 LIMIT 3;
SELECT CITY, count(rollno) FROM STUDENT GROUP BY city;
SELECT CITY FROM student WHERE grade = "A" GROUP BY city HAVING MAX(marks) >= 90;
SET SQL_SAFE_UPDATES = 0;
SELECT * FROM STUDENT;

Table Structure

Column	Type	Constraint	Description
rollno	INT	PRIMARY KEY	Student Roll Number
name	VARCHAR(50)	NOT NULL	Student Name
marks	INT	NOT NULL	Student Marks
grade	VARCHAR(1)		Student Grade
city	VARCHAR(50)		Student's City

Data Analysis Queries

• Marks Between 80 and 90: Students with marks in this range.
• Students from Pune: Filters by city.
• Top 3 Marks: Retrieves the highest three marks.
• Average Marks: Computes the class average.
• Top Performers: Students with marks above 75, limited to three.
• City-wise Count: Number of students from each city.
• Cities with 'A' Grade: Cities where at least one student has grade 'A' and a top mark of at least 90.

Example Aggregation Result Table

CITY	count(rollno)
Pune	4
Delhi	1
Mumbai	1

Student Table ER Diagram

erDiagram
    STUDENT {
        INT rollno PK
        VARCHAR name
        INT marks
        VARCHAR grade
        VARCHAR city
    }

Loading

---

Summary Table

Script	Purpose	Complexity
classroom.sql	Empty; template for new SQL scripts	Very Low
classroom1.sql	Simple DB with students and a restricted driving table	Low
joins.sql	Demonstrates joining students and courses	Medium
classroom2.sql	Advanced querying on students with marks, grades, cities	High

---

{
    "title": "Best Practice",
    "content": "Always use constraints and proper normalization to ensure data consistency in your database schema."
}

---

📝 Useful SQL Concepts Illustrated

• Database and Table Creation: How to create databases and tables with constraints.
• INSERT and SELECT: Adding and retrieving records.
• INNER JOIN: Combining data from related tables.
• Aggregations: Using AVG, COUNT, GROUP BY, and HAVING.
• Filtering: WHERE, BETWEEN, ordering and limiting results.
• CHECK Constraints: Enforcing custom rules on table data.

---

Feel free to extend these scripts for more advanced classroom/college data management scenarios!