databaseterm/sql_presentation.html at main · itskashie/databaseterm · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>SQL Script Presentation - HealthSync Pro</title>
    <script src="https://cdn.tailwindcss.com"></script>
    <link href="https://fonts.googleapis.com/css2?family=Fira+Code:wght@400;500&family=Outfit:wght@400;700&family=Inter:wght@400;600&display=swap" rel="stylesheet">
    <script>
        tailwind.config = {
            theme: {
                extend: {
                    fontFamily: {
                        mono: ['Fira Code', 'monospace'],
                        outfit: ['Outfit', 'sans-serif'],
                        inter: ['Inter', 'sans-serif'],
                    },
                    colors: {
                        editor: {
                            bg: '#0d1117',
                            header: '#161b22',
                            text: '#c9d1d9',
                            keyword: '#ff7b72',
                            string: '#a5d6ff',
                            comment: '#8b949e',
                            function: '#d2a8ff',
                            number: '#79c0ff'
                        }
                    }
                }
            }
        }
    </script>
    <style>
        ::-webkit-scrollbar { width: 8px; }
        ::-webkit-scrollbar-track { background: #0d1117; }
        ::-webkit-scrollbar-thumb { background: #30363d; border-radius: 4px; }
        ::-webkit-scrollbar-thumb:hover { background: #484f58; }
        .code-container { background-color: #0d1117; }
        .sql-keyword { color: #ff7b72; font-weight: bold; }
        .sql-string { color: #a5d6ff; }
        .sql-comment { color: #8b949e; font-style: italic; }
        .sql-type { color: #ffa657; }
        .sql-number { color: #79c0ff; }
    </style>
</head>
<body class="bg-[#010409] text-editor-text font-inter antialiased">

    <!-- Sidebar Nav -->
    <div class="fixed left-0 top-0 h-full w-64 bg-[#0d1117] border-r border-[#30363d] hidden lg:flex flex-col p-6 z-50">
        <div class="flex items-center space-x-3 mb-12">
            <div class="w-8 h-8 bg-blue-600 rounded-lg flex items-center justify-center">
                <svg class="w-5 h-5 text-white" fill="none" stroke="currentColor" viewBox="0 0 24 24"><path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M4 7v10c0 2.21 3.582 4 8 4s8-1.792 8-4V7M4 7c0 2.21 3.582 4 8 4s8-1.792 8-4M4 7c0-2.21 3.582-4 8-4s8 1.792 8 4m0 5c0 2.21-3.582 4-8 4s-8-1.792-8-4"></path></svg>
            </div>
            <h1 class="font-outfit font-bold text-lg text-white">Full SQL Script</h1>
        </div>

        <nav class="space-y-4 flex-1">
            <a href="#ddl" class="block py-2 px-4 rounded-lg bg-blue-600/10 text-blue-400 font-medium hover:bg-blue-600/20 transition-all">01 Schema (DDL)</a>
            <a href="#dml" class="block py-2 px-4 rounded-lg text-slate-500 hover:text-white hover:bg-white/5 transition-all">02 Data (DML)</a>
            <a href="#queries" class="block py-2 px-4 rounded-lg text-slate-500 hover:text-white hover:bg-white/5 transition-all">03 Queries</a>
        </nav>
    </div>

    <!-- Main Content -->
    <main class="lg:ml-64 min-h-screen">

        <!-- Header -->
        <header class="p-8 lg:p-12 border-b border-[#30363d]">
            <div class="max-w-4xl">
                <h2 class="font-outfit text-4xl font-bold text-white mb-2">Technical Submission</h2>
                <p class="text-blue-500 font-mono text-sm mb-6 uppercase tracking-widest">Complete Project Codebase</p>
            </div>
        </header>

        <div class="p-8 lg:p-12 space-y-16">

            <!-- DDL SECTION -->
            <section id="ddl" class="scroll-mt-12">
                <h3 class="text-xl font-bold text-slate-300 mb-6 flex items-center">
                    <span class="w-2 h-2 rounded-full bg-blue-500 mr-3"></span>
                    1. Data Definition Language (DDL)
                </h3>
                <div class="rounded-xl overflow-hidden border border-[#30363d]">
                    <div class="p-6 code-container text-[11px] md:text-sm font-mono overflow-x-auto whitespace-pre leading-relaxed">
<span class="sql-comment">-- Comprehensive Healthcare Schema (3NF)</span>
<span class="sql-keyword">DROP TABLE IF EXISTS</span> SaleItems; <span class="sql-keyword">DROP TABLE IF EXISTS</span> Sales; <span class="sql-keyword">DROP TABLE IF EXISTS</span> PharmacyInventory;
<span class="sql-keyword">DROP TABLE IF EXISTS</span> Medicines; <span class="sql-keyword">DROP TABLE IF EXISTS</span> MedicalRecords; <span class="sql-keyword">DROP TABLE IF EXISTS</span> Appointments;
<span class="sql-keyword">DROP TABLE IF EXISTS</span> Patients; <span class="sql-keyword">DROP TABLE IF EXISTS</span> Doctors; <span class="sql-keyword">DROP TABLE IF EXISTS</span> Staff;
<span class="sql-keyword">DROP TABLE IF EXISTS</span> Departments; <span class="sql-keyword">DROP TABLE IF EXISTS</span> Branches;

<span class="sql-keyword">CREATE TABLE</span> Branches (
    BranchID <span class="sql-type">INTEGER PRIMARY KEY AUTOINCREMENT</span>,
    BranchName <span class="sql-type">TEXT NOT NULL</span>,
    Location <span class="sql-type">TEXT NOT NULL</span>,
    Contact <span class="sql-type">TEXT NOT NULL</span>
);

<span class="sql-keyword">CREATE TABLE</span> Departments (
    DeptID <span class="sql-type">INTEGER PRIMARY KEY AUTOINCREMENT</span>,
    DeptName <span class="sql-type">TEXT NOT NULL</span>,
    BranchID <span class="sql-type">INTEGER NOT NULL</span>,
    <span class="sql-keyword">FOREIGN KEY</span> (BranchID) <span class="sql-keyword">REFERENCES</span> Branches(BranchID)
);

<span class="sql-keyword">CREATE TABLE</span> Staff (
    StaffID <span class="sql-type">INTEGER PRIMARY KEY AUTOINCREMENT</span>,
    FullName <span class="sql-type">TEXT NOT NULL</span>,
    UserRole <span class="sql-type">TEXT NOT NULL CHECK</span>(UserRole <span class="sql-keyword">IN</span> (<span class="sql-string">'Doctor'</span>, <span class="sql-string">'Nurse'</span>, <span class="sql-string">'Admin'</span>)),
    Salary <span class="sql-type">REAL NOT NULL CHECK</span>(Salary > <span class="sql-number">0</span>),
    DeptID <span class="sql-type">INTEGER NOT NULL</span>,
    <span class="sql-keyword">FOREIGN KEY</span> (DeptID) <span class="sql-keyword">REFERENCES</span> Departments(DeptID)
);

<span class="sql-keyword">CREATE TABLE</span> Doctors (
    DoctorID <span class="sql-type">INTEGER PRIMARY KEY</span>,
    Specialization <span class="sql-type">TEXT NOT NULL</span>,
    LicenseNumber <span class="sql-type">TEXT UNIQUE NOT NULL</span>,
    <span class="sql-keyword">FOREIGN KEY</span> (DoctorID) <span class="sql-keyword">REFERENCES</span> Staff(StaffID)
);

<span class="sql-keyword">CREATE TABLE</span> Patients (
    PatientID <span class="sql-type">INTEGER PRIMARY KEY AUTOINCREMENT</span>,
    FirstName <span class="sql-type">TEXT NOT NULL</span>,
    LastName <span class="sql-type">TEXT NOT NULL</span>,
    DOB <span class="sql-type">DATE NOT NULL</span>,
    Gender <span class="sql-type">TEXT CHECK</span>(Gender <span class="sql-keyword">IN</span> (<span class="sql-string">'Male'</span>, <span class="sql-string">'Female'</span>, <span class="sql-string">'Other'</span>)),
    Address <span class="sql-type">TEXT NOT NULL</span>,
    Contact <span class="sql-type">TEXT UNIQUE NOT NULL</span>
);

<span class="sql-keyword">CREATE TABLE</span> Appointments (
    ApptID <span class="sql-type">INTEGER PRIMARY KEY AUTOINCREMENT</span>,
    PatientID <span class="sql-type">INTEGER NOT NULL</span>,
    DoctorID <span class="sql-type">INTEGER NOT NULL</span>,
    ApptDate <span class="sql-type">DATETIME NOT NULL</span>,
    Status <span class="sql-type">TEXT DEFAULT</span> <span class="sql-string">'Scheduled'</span>,
    <span class="sql-keyword">FOREIGN KEY</span> (PatientID) <span class="sql-keyword">REFERENCES</span> Patients(PatientID),
    <span class="sql-keyword">FOREIGN KEY</span> (DoctorID) <span class="sql-keyword">REFERENCES</span> Doctors(DoctorID)
);
                    </div>
                </div>
            </section>

            <!-- DML SECTION -->
            <section id="dml" class="scroll-mt-12">
                <h3 class="text-xl font-bold text-slate-300 mb-6 flex items-center">
                    <span class="w-2 h-2 rounded-full bg-green-500 mr-3"></span>
                    2. Data Manipulation Language (DML)
                </h3>
                <div class="rounded-xl overflow-hidden border border-[#30363d]">
                    <div class="p-6 code-container text-[11px] md:text-sm font-mono overflow-x-auto whitespace-pre leading-relaxed">
<span class="sql-comment">-- Population with Pakistani Context (50+ Records)</span>
<span class="sql-keyword">INSERT INTO</span> Branches (BranchName, Location, Contact) <span class="sql-keyword">VALUES</span>
(<span class="sql-string">'Jinnah Memorial Hospital'</span>, <span class="sql-string">'G.T. Road, Lahore'</span>, <span class="sql-string">'042-35870001'</span>),
(<span class="sql-string">'Iqbal Heights Clinic'</span>, <span class="sql-string">'Gulberg III, Lahore'</span>, <span class="sql-string">'042-35870002'</span>),
(<span class="sql-string">'Shaukat General Center'</span>, <span class="sql-string">'Johar Town, Lahore'</span>, <span class="sql-string">'042-35870003'</span>),
(<span class="sql-string">'Anarkali Health Unit'</span>, <span class="sql-string">'Old City, Lahore'</span>, <span class="sql-string">'042-35870004'</span>),
(<span class="sql-string">'Allama Medical Store'</span>, <span class="sql-string">'Garden Town, Lahore'</span>, <span class="sql-string">'042-35870005'</span>);

<span class="sql-keyword">INSERT INTO</span> Staff (FullName, UserRole, Salary, DeptID) <span class="sql-keyword">VALUES</span>
(<span class="sql-string">'Dr. Ahmed Hassan'</span>, <span class="sql-string">'Doctor'</span>, <span class="sql-number">150000</span>, <span class="sql-number">1</span>), (<span class="sql-string">'Dr. Fatima Zahra'</span>, <span class="sql-string">'Doctor'</span>, <span class="sql-number">140000</span>, <span class="sql-number">2</span>),
(<span class="sql-string">'Dr. Usman Malik'</span>, <span class="sql-string">'Doctor'</span>, <span class="sql-number">160000</span>, <span class="sql-number">5</span>), (<span class="sql-string">'Dr. Ayesha Khan'</span>, <span class="sql-string">'Doctor'</span>, <span class="sql-number">145000</span>, <span class="sql-number">4</span>),
(<span class="sql-string">'Dr. Salman Sheikh'</span>, <span class="sql-string">'Doctor'</span>, <span class="sql-number">155000</span>, <span class="sql-number">1</span>), (<span class="sql-string">'Nurse Zainab'</span>, <span class="sql-string">'Nurse'</span>, <span class="sql-number">50000</span>, <span class="sql-number">1</span>),
(<span class="sql-string">'Nurse Bilal'</span>, <span class="sql-string">'Nurse'</span>, <span class="sql-number">48000</span>, <span class="sql-number">2</span>), (<span class="sql-string">'Pharmacist Hamza'</span>, <span class="sql-string">'Pharmacist'</span>, <span class="sql-number">60000</span>, <span class="sql-number">3</span>),
(<span class="sql-string">'Admin Rashid'</span>, <span class="sql-string">'Admin'</span>, <span class="sql-number">45000</span>, <span class="sql-number">1</span>), (<span class="sql-string">'Pharmacist Hina'</span>, <span class="sql-string">'Pharmacist'</span>, <span class="sql-number">62000</span>, <span class="sql-number">3</span>);

<span class="sql-keyword">INSERT INTO</span> Patients (FirstName, LastName, DOB, Gender, Address, Contact) <span class="sql-keyword">VALUES</span>
(<span class="sql-string">'Ali'</span>, <span class="sql-string">'Raza'</span>, <span class="sql-string">'1985-05-15'</span>, <span class="sql-string">'Male'</span>, <span class="sql-string">'Model Town, Lahore'</span>, <span class="sql-string">'0300-1110001'</span>),
(<span class="sql-string">'Sana'</span>, <span class="sql-string">'Saeed'</span>, <span class="sql-string">'1990-08-22'</span>, <span class="sql-string">'Female'</span>, <span class="sql-string">'DHA Phase 5, Lahore'</span>, <span class="sql-string">'0300-1110002'</span>),
(<span class="sql-string">'Muhammad'</span>, <span class="sql-string">'Ahmad'</span>, <span class="sql-string">'1975-12-10'</span>, <span class="sql-string">'Male'</span>, <span class="sql-string">'Wapda Town, Lahore'</span>, <span class="sql-string">'0300-1110003'</span>),
(<span class="sql-string">'Iqra'</span>, <span class="sql-string">'Bashir'</span>, <span class="sql-string">'1988-03-30'</span>, <span class="sql-string">'Female'</span>, <span class="sql-string">'Sabzazar, Lahore'</span>, <span class="sql-string">'0300-1110004'</span>),
(<span class="sql-string">'Bilal'</span>, <span class="sql-string">'Haider'</span>, <span class="sql-string">'2000-01-01'</span>, <span class="sql-string">'Male'</span>, <span class="sql-string">'Sammanabad, Lahore'</span>, <span class="sql-string">'0300-1110005'</span>);

<span class="sql-keyword">INSERT INTO</span> Appointments (PatientID, DoctorID, BranchID, ApptDate) <span class="sql-keyword">VALUES</span>
(<span class="sql-number">1, 1, 1</span>, <span class="sql-string">'2026-06-01 10:00:00'</span>), (<span class="sql-number">2, 2, 2</span>, <span class="sql-string">'2026-06-01 11:00:00'</span>),
(<span class="sql-number">3, 1, 1</span>, <span class="sql-string">'2026-06-01 12:00:00'</span>), (<span class="sql-number">4, 3, 2</span>, <span class="sql-string">'2026-06-02 09:00:00'</span>);
                    </div>
                </div>
            </section>

            <!-- QUERY SECTION -->
            <section id="queries" class="scroll-mt-12 pb-24">
                <h3 class="text-xl font-bold text-slate-300 mb-6 flex items-center">
                    <span class="w-2 h-2 rounded-full bg-purple-500 mr-3"></span>
                    3. Analytical Queries (10 Verified Reports)
                </h3>
                <div class="rounded-xl overflow-hidden border border-[#30363d]">
                    <div class="p-6 code-container text-[11px] md:text-sm font-mono overflow-x-auto whitespace-pre leading-relaxed text-blue-300/80">
<span class="sql-comment">-- Q1: Appointment Details</span>
<span class="sql-keyword">SELECT</span> a.ApptID, p.FirstName || <span class="sql-string">' '</span> || p.LastName <span class="sql-keyword">AS</span> Patient, s.FullName <span class="sql-keyword">AS</span> Doctor
<span class="sql-keyword">FROM</span> Appointments a
<span class="sql-keyword">JOIN</span> Patients p <span class="sql-keyword">ON</span> a.PatientID = p.PatientID
<span class="sql-keyword">JOIN</span> Staff s <span class="sql-keyword">ON</span> a.DoctorID = s.StaffID;

<span class="sql-comment">-- Q2: Branch Revenue</span>
<span class="sql-keyword">SELECT</span> b.BranchName, <span class="sql-keyword">SUM</span>(s.TotalAmount) <span class="sql-keyword">AS</span> TotalRevenue
<span class="sql-keyword">FROM</span> Branches b
<span class="sql-keyword">LEFT JOIN</span> Sales s <span class="sql-keyword">ON</span> b.BranchID = s.BranchID
<span class="sql-keyword">GROUP BY</span> b.BranchName;

<span class="sql-comment">-- Q3: Low Stock</span>
<span class="sql-keyword">SELECT</span> b.BranchName, m.MedicineName, pi.Quantity
<span class="sql-keyword">FROM</span> PharmacyInventory pi
<span class="sql-keyword">JOIN</span> Medicines m <span class="sql-keyword">ON</span> pi.MedicineID = m.MedicineID
<span class="sql-keyword">JOIN</span> Branches b <span class="sql-keyword">ON</span> pi.BranchID = b.BranchID
<span class="sql-keyword">WHERE</span> pi.Quantity < span class="sql-number">150</span>;
                    </div>
                </div>
            </section>
        </div>
    </main>

</body>
</html>