Addis Ababa University, School of Commerce | Updated: Feb, 2025 1
Chapter - 2
Understanding
Database Languages
SQL + DDL + DML
Addis Ababa University, School of Commerce | Updated: Feb, 2025 2
Practical SQL : DDL
• SQL Identifiers : the name of the database object.
• Identifiers are used to identify objects in the database such as tables, views,
and columns.
• An SQL identifier (name) must follow these rules:
• only contain upper or lower case characters, digits, and underscore ("_") character
• be no longer than 128 characters
• must start with a letter
• cannot contain spaces
• Some vendors may impose stricter limits or allow spaces by enclosing identifier in double
quotes or some other delimiter.
CREATE TABLE Employee_Details ✅
CREATE TABLE Employee Details ❌ (spaces are invalid)
Addis Ababa University, School of Commerce | Updated: Feb, 2025 3
Practical SQL : DDL : Create a New Database
Addis Ababa University, School of Commerce | Updated: Feb, 2025 4
Practical SQL : DDL : Create a New Database
Addis Ababa University, School of Commerce | Updated: Feb, 2025 5
Practical SQL : DDL : Create a Schema
Addis Ababa University, School of Commerce | Updated: Feb, 2025 6
Practical SQL : DDL : Create a Schema
Addis Ababa University, School of Commerce | Updated: Feb, 2025 7
Practical SQL : DDL : Create a Schema
Addis Ababa University, School of Commerce | Updated: Feb, 2025 8
Practical SQL : DDL : Create a Table
• Things to consider before you create your table are:
• The type of data
• The table name
• what column(s) will make up the primary key
• The names of the columns
Addis Ababa University, School of Commerce | Updated: Feb, 2025 9
Practical SQL : DDL : Create a Table
• Creating temp Table
• A temporary table is a table that exists temporarily in the tempdb system database.
• It behaves like a regular table but is automatically deleted when no longer needed typically
at the end of a session or when explicitly dropped.
• Types:
Type Prefix Visibility Lifetime
Local #
Current session only Deleted when session ends
Temporary
Global All sessions (while
## Deleted when last session ends
Temporary one is active)
Addis Ababa University, School of Commerce | Updated: Feb, 2025 10
Practical SQL : DDL : column constraints
• In databases, column constraints are rules applied to individual columns in a
table to enforce data integrity, accuracy, and consistency.
• They define what kind of data can be stored in a column and how it behaves
during insert and update operations.
• Importance of Column Constraints
• Ensure data validity (e.g., no nulls in required fields).
• Prevent duplicate data (e.g., with UNIQUE).
• Maintain consistency (e.g., CHECK for valid ranges).
• Automate default values (e.g., DEFAULT).
• Improve data quality and reduce errors
Addis Ababa University, School of Commerce | Updated: Feb, 2025 11
Practical SQL : DDL : column constraints
• Types of Constraints : Unnamed Constraints
• Constraints added without a specific name.
• SQL Server auto-generates a name.
• Quick to write but, harder to reference later.
Addis Ababa University, School of Commerce | Updated: Feb, 2025 12
Practical SQL : DDL : column constraints
• Types of Constraints : Named Constraints
• Given an explicit name for easier management (e.g., modification or removal).
Addis Ababa University, School of Commerce | Updated: Feb, 2025 13
Practical SQL : DDL : column constraints
• Types of Constraints : Table-wide Constraints
• Declared at the end of the CREATE TABLE statement.
• Often used for composite keys or constraints involving multiple columns.
Addis Ababa University, School of Commerce | Updated: Feb, 2025 14
Practical SQL : DDL : column constraints
• Common Constraints
• NOT NULL : Ensures a column cannot have NULL values.
• Usage: Mandatory fields like IDs or names.
Addis Ababa University, School of Commerce | Updated: Feb, 2025 15
Practical SQL : DDL : column constraints
• Common Constraints
• UNIQUE : Ensures all values in a column are different.
• Usage: Emails, usernames.
Addis Ababa University, School of Commerce | Updated: Feb, 2025 16
Practical SQL : DDL : column constraints
• Common Constraints
• CHECK : Validates data based on a condition.
• Usage: Range checks, such as valid age, price, or quantity.
Addis Ababa University, School of Commerce | Updated: Feb, 2025 17
Practical SQL : DDL : column constraints
• Common Constraints
• DEFAULT : Sets a default value if none is provided.
• Usage: Setting default values for optional data like country, status, timestamps.
Addis Ababa University, School of Commerce | Updated: Feb, 2025 18
Practical SQL : DDL : ALTER Constraints
• ALTER constraints refer to the process of modifying an existing
table to add, remove, or change constraints using the ALTER
TABLE statement.
• Importance of ALTER Constraints
• Flexibility: Modify or add constraints after table creation.
• Data integrity: Enforce rules on existing data structures.
• Maintainability: Adjust database design as requirements evolve.
Addis Ababa University, School of Commerce | Updated: Feb, 2025 19
Practical SQL : DDL : ALTER Constraints
• Add Unnamed Constraints
• SQL Server automatically names the constraint if no name is provided.
• Usage: When you don’t need to reference or drop the constraint by name later.
Addis Ababa University, School of Commerce | Updated: Feb, 2025 20
Practical SQL : DDL : ALTER Constraints
• Add Named Constraints
• Usage: Easier to reference
for modification or deletion
later.
Addis Ababa University, School of Commerce | Updated: Feb, 2025 21
Practical SQL : DDL : ALTER Constraints
•…
• Add a new column with a
named constraint using WITH
NOCHECK
• Purpose of WITH NOCHECK
• Skips validation of existing
data when applying a new
constraint.
• The constraint is enforced
only on future data.
• You can only use WITH NOCHECK
with CHECK or FOREIGN KEY
constraints.
• If you omit WITH NOCHECK, SQL
Server validates all existing rows
against the new constraint.
Addis Ababa University, School of Commerce | Updated: Feb, 2025 22
Practical SQL : DDL : ALTER Constraints
•…
• In SQL Server, UNIQUE constraints
must be added separately if you're
introducing both a new column and a
UNIQUE constraint.
• WITH NOCHECK + FOREIGN KEY
Constraint
• This allows you to add a foreign key
constraint without validating existing
data in the referencing table.
Addis Ababa University, School of Commerce | Updated: Feb, 2025 23
Practical SQL : DDL : Drop Constraint
•…
• Step 1: Locate the Unnamed
Constraint
• Step 2: Drop the Constraint Using Its
Name
Use
• sys.check_constraints for CHECK
Addis Ababa University, School of Commerce | Updated: Feb, 2025 24
Practical SQL : DDL : Drop Constraint
•…
• Step 1: Locate the Unnamed
Constraint
• Step 2: Drop the Constraint Using Its
Name
Use
• sys.default_constraints for DEFAULT
Addis Ababa University, School of Commerce | Updated: Feb, 2025 25
Practical SQL : DDL : Drop Constraint
•…
• Step 1: Locate the Unnamed
Constraint
• Step 2: Drop the Constraint Using Its
Name
Use
• INFORMATION_SCHEMA views for
UNIQUE / PK / FK
Addis Ababa University, School of Commerce | Updated: Feb, 2025 26
Practical SQL : DDL : Primary Key (PK) & Foreign Key (FK)
•…
• A Primary Key uniquely identifies
each record in a table.
• It cannot contain NULLs and must be
unique.
• A Foreign Key is a field (or combo of
fields) in one table that refers to the
Primary Key in another table.
• It is used to maintain referential
integrity between related tables.
Addis Ababa University, School of Commerce | Updated: Feb, 2025 27
Practical SQL : DDL : Primary Key (PK) & Foreign Key (FK)
•…
• Primary Key with IDENTITY
• Compound Primary Key
Addis Ababa University, School of Commerce | Updated: Feb, 2025 28
Practical SQL : DDL : Primary Key (PK) & Foreign Key (FK)
•…
• Add Primary Key After Table Creation
• Add Foreign Key After Table Creation
