DDL and DML Practice

CSC-265 · Semester IV · Database Management System

beginner30 minAvailable

SQLin-browser engine · runs locallyddl-dml

Ctrl Enter to run21 lines

Write SQL, then press Run. Result tables, affected-row counts and error messages appear here.

Lab brief

Objective

Build a schema with DDL and maintain data with DML while watching the database enforce constraints on every operation.

Theory & concepts

DDL defines structure: CREATE TABLE with column types, PRIMARY KEY, UNIQUE, NOT NULL, DEFAULT, CHECK and FOREIGN KEY. DML changes the data: INSERT adds rows, UPDATE modifies them, DELETE removes them. Every DML statement is validated against the declared constraints, and violations are rejected with an explanation.

Rendering diagram…

Building the schema and watching constraints reject bad data

CREATE TABLE DEPARTMENT (
  Dno   INT PRIMARY KEY,
  Dname VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE EMPLOYEE (
  Eno  INT PRIMARY KEY,
  Name VARCHAR(50) NOT NULL,
  Dno  INT NOT NULL REFERENCES DEPARTMENT(Dno),
  Salary DECIMAL(10,2) CHECK (Salary >= 0)
);

Prerequisites

Lesson: SQL data definition and constraints
Lesson: SQL data manipulation: INSERT, UPDATE, DELETE

Instructions

1Run the CREATE TABLE statements to build DEPARTMENT and EMPLOYEE.
2Inspect the schema inspector and note every constraint you declared.
3Insert sample departments and employees, including one row that violates a constraint on purpose.
4Run UPDATE and DELETE statements and observe which succeed and which are rejected.
5Answer the closing questions explaining why each violation occurred.

Tasks

Define the schema
Create DEPARTMENT and EMPLOYEE with keys, NOT NULL, UNIQUE, CHECK and a foreign key.
Load data
Insert at least three departments and five employees.
Trigger violations
Try a duplicate key, a NULL name, a negative salary and a dangling foreign key, and record each error.
Update and delete
Change an employee's department and salary, then delete a department and observe the referential outcome.

Self-check

The schema matches the required types and constraints.
Every attempted violation is correctly predicted and explained.
Valid UPDATE and DELETE operations behave as expected.
The student explains how constraints are enforced by the DBMS.

Environment

SQL consoleschema inspectorconstraint violation explanationslive results