IDS 200 – SQL & Database Application Basics

Coverage

• Database applications: design, deployment, and back-end access
• Languages used with databases: C++, Java, PHP
• SQL focus: History, versions, syntax, and four core language groups (DDL, DML, DQL, DCL)

Overview of SQL

• SQL = Structured Query Language; pronounced “sequel” or “S-Q-L”
• Acts as the command interface sent to a database engine
• Analogous to console commands in a game; execution handled by DBMS
• Learning resource: www.w3schools.com

Database Application Languages

• C++
– High-level with low-level (hardware-specific) access
– Platform-dependent; fastest execution due to direct memory access
• Java
– High-level; no direct memory access
– Platform-independent; easier, safer code
• Simple rule: C++ = speed, Java = ease & portability
• Other languages: Python, R (analysis); C# (Windows servers); PHP (web scripts)

PHP & Web Access

• PHP = server-side scripting language embedded in webpages
• Process: user requests page → server runs PHP → PHP queries DB → results converted to HTML → returned to user

SQL History & Versions

• Relational model proposed \approx 1970; SQL standardized 1986
• Vendors adopted early (IBM, Oracle)
• Later updates added features; now vendors vary in implementation
• Core/basic SQL remains consistent across systems

SQL Syntax Rules

• Commands follow strict element order (e.g., CREATE TABLE name ➔ fields)
• Order within clause groups may be flexible, but overall structure rigid for precision & security

SQL Language Groups

• DDL – define database objects
• DML – change data
• DQL – read/query data
• DCL – control user privileges
• Other sets: TCL, Session/System/Embedded control

Data Definition Language (DDL)

• CREATE TABLE: define name, fields, keys, formats
• ALTER TABLE ADD/DROP: modify columns
• TRUNCATE TABLE: clear data, keep structure
• DROP TABLE: remove table entirely

Data Manipulation Language (DML)

• INSERT INTO {table} VALUES (…)
• UPDATE {table} SET {column = value, …} WHERE {conditions}
• DELETE FROM {table} WHERE {conditions}

WHERE Clauses

• Specify record-matching criteria
• Operators: =, <, >, LIKE (patterns), IN (set membership)
• Cannot stand alone; used within other statements

Data Query Language (DQL)

• Core pattern: SELECT-FROM-WHERE
– SELECT columns
– FROM tables
– WHERE conditions
• Additional keywords:
– ORDER BY: sort results
– GROUP BY: aggregate by column values
– HAVING: filter groups
• Wildcards:
– * = “all” (columns or records)
– LIKE with \% for patterns (e.g., "A%")

Data Control Language (DCL)

• GRANT: assign privileges
• REVOKE: remove granted privileges
• DENY: explicitly forbid privileges (not in all DBMSs)
• Privileges can be scoped to specific tables, columns, or rows