Joshua Addo [Link]/in/joshua-addo-snr [Link]@[Link].
gh
UNIVERSITY OF CAPE COAST
SCHOOL OF BUSINESS
DEPARTMENT OF ACCOUNTING
ACC204: COMPUTER APPLICATIONS IN ACCOUNTING
Introduction to SQL
SQL (Structured Query Language) is the standard language for interacting with
relational databases. It is designed to manage, manipulate, and query data stored in
relational database management systems (RDBMS).
SQL Sub-Languages Overview
SQL can be divided into several sub-languages, each tailored for specific aspects of
database management and manipulation:
1. DDL (Data Definition Language): Deals with the schema and structure of the database.
2. DML (Data Manipulation Language): Concerned with manipulating the data within the
schema.
3. DCL (Data Control Language): Manages access control and permissions.
4. TCL (Transaction Control Language): Handles transactions within the database.
DDL: Data Definition Language
DDL is used for defining and modifying the structure of a database schema. This
includes operations such as creating tables, altering table structures, and dropping
tables.
Key Commands
CREATE: Establishes a new table or database.
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(50),
age INT
);
1
Joshua Addo [Link]/in/joshua-addo-snr [Link]@[Link]
ALTER: Modifies an existing database structure, like adding a new column.
ALTER TABLE students ADD email VARCHAR(100);
DROP: Removes an existing table or database entirely.
DROP TABLE students;
Importance
Understanding DDL allows students to grasp how databases and tables are
constructed, providing the basis for storing and organizing data effectively within a
RDBMS.
DML: Data Manipulation Language
DML focuses on the manipulation of data within existing structures. This includes
inserting, updating, deleting, and retrieving data from tables.
Key Commands
SELECT: Retrieves data from a database, allowing filters and specific data
retrieval.
SELECT name, age FROM students WHERE age > 18;
INSERT: Adds new data records to a table.
INSERT INTO students (student_id, name, age) VALUES (1,
'Alice', 20);
UPDATE: Modifies existing records within a table.
UPDATE students SET age = 21 WHERE student_id = 1;
2
Joshua Addo [Link]/in/joshua-addo-snr [Link]@[Link]
DELETE: Removes records from a table.
DELETE FROM students WHERE student_id = 1;
Importance
DML commands are crucial for the day-to-day interaction with the database, such as
updating records or retrieving data for reports.
DCL: Data Control Language
DCL manages who has access to different aspects of the database, including
providing and revoking rights and permissions.
Key Commands
GRANT: Assigns user privileges to database objects.
GRANT SELECT ON students TO user1;
REVOKE: Removes user privileges from database objects.
REVOKE SELECT ON students FROM user1;
Importance
Using DCL helps maintain security and control over who can access or modify data
within the database, critical for protecting sensitive information.
TCL: Transaction Control Language
TCL commands manage the changes made by DML operations, ensuring data
integrity by grouping multiple tasks into a single, atomic transaction.
Key Commands
COMMIT: Saves all changes made during the current transaction.
ROLLBACK: Undoes changes made during the current transaction.
3
Joshua Addo [Link]/in/joshua-addo-snr [Link]@[Link]
SAVEPOINT: Sets a point within a transaction to which you can later roll back.
Importance
Understanding TCL is essential for ensuring data consistency and integrity, especially
in environments where multiple users or applications might be changing data
simultaneously.
Conclusion
A deep dive into the sub-languages of SQL equips students with a comprehensive
understanding of how databases are designed, manipulated, and maintained securely.
Hands-on practice with these commands will further solidify your knowledge and
prepare you for advanced database management tasks.
Remember, the key to mastering SQL lies in understanding the purpose and
appropriate application of its diverse commands across real-world scenarios. Happy
learning!