A collection of SQL programs covering DDL, DML, DCL commands, Joins, Views, Triggers, and more, executed as part of the Database Management System Laboratory.
How to Use · Learning Path · Experiment 1 · Experiment 2 · Experiment 3 · Experiment 4 · Experiment 5 · Experiment 6 · Experiment 7 · Experiment 8 · Experiment 9 · Experiment 10
Tip
Tip: For comprehensive theory notes and exam resources, check out Mega's Notes in the main repository. These collaboratively curated notes provide the theoretical foundation necessary for mastering these practical experiments.
Warning
Data Integrity: When executing DML commands (INSERT, UPDATE, DELETE), always remember to COMMIT your transactions to permanently save changes. Exercise extreme caution when using DELETE or DROP statements without a WHERE clause to avoid irreversible data loss.
These scripts are tailored for Oracle Database environments.
1. Prerequisites Ensure you have access to one of the following tools:
- Oracle SQL*Plus (Command Line Interface)
- Oracle SQL Developer (GUI Tool)
- Oracle LiveSQL (Online Compiler)
2. Execution Guide
To execute a script file (e.g., filename.sql) in SQL*Plus, use the @ command followed by the file path:
-- Syntax: @<path_to_file>/<filename>.sql
@D:/DBMS_Lab/Experiment-1/01_Basic_SQL_Queries.sqlBeginner Level:
- Start with Experiment 1 & 2 to understand table creation, data types, and constraints.
- Practice basic DML (Insert, Update, Delete) and DQL (Select).
Intermediate Level:
- Explore Experiment 3, 4 & 7 to master Joins, Subqueries, and Views.
- Understand how to query data from multiple sources efficiently.
Advanced Level:
- Study Experiments 5, 8, 9 & 10 for PL/SQL programming (Procedures, Triggers, Cursors).
- Learn DDL/DML/TCL (Exp 5 & 6) for database transaction and schema management.
Implementation of Data Definition Language (DDL) and Data Manipulation Language (DML) commands to create and manage database schemas.
Date: July 14, 2020
| # | Program | Description | Source Code |
|---|---|---|---|
| 1 | 01_Basic_SQL_Queries.sql | Creating tables and inserting data | View |
| — | Lab Report | Detailed experiment report | View |
Applying integrity constraints and utilizing SQL built-in functions for data analysis.
Date: July 21, 2020
| # | Program | Description | Source Code |
|---|---|---|---|
| 1 | 02_Restricting_and_Sorting_Data.sql | Primary Key, Foreign Key, NOT NULL, Check etc. | View |
| — | Lab Report | Detailed experiment report | View |
Combining data from multiple tables using Joins and performing Set operations (Union, Intersect, Minus).
Date: July 28, 2020
| # | Program | Description | Source Code |
|---|---|---|---|
| 1 | 03_Joins_and_Relationships.sql | Inner, Left, Right, Full Joins & Set Ops | View |
| — | Lab Report | Detailed experiment report | View |
Creating virtual tables (Views) and writing complex queries using Subqueries.
Date: August 04, 2020
| # | Program | Description | Source Code |
|---|---|---|---|
| 1 | 04_Aggregate_Functions_and_Views.sql | Creating Views and Nested Queries | View |
| — | Lab Report | Detailed experiment report | View |
Advanced DDL operations including altering tables and managing constraints.
Date: August 14, 2020
| # | Program | Description | Source Code |
|---|---|---|---|
| 1 | 05_DDL_and_Constraints.sql | Altering tables, constraints, renaming | View |
| — | Lab Report | Detailed experiment report | View |
Performing Data Manipulation and Transaction Control operations.
Date: August 21, 2020
| # | Program | Description | Source Code |
|---|---|---|---|
| 1 | 06_DML_and_TCL.sql | Insert, Update, Delete, Commit, Rollback | View |
| — | Lab Report | Detailed experiment report | View |
Advanced data retrieval using various types of subqueries.
Date: August 28, 2020
| # | Program | Description | Source Code |
|---|---|---|---|
| 1 | 07_Subqueries.sql | Single-row and Multi-row subqueries | View |
| — | Lab Report | Detailed experiment report | View |
Introduction to Procedural SQL (PL/SQL) stored procedures.
Date: September 09, 2020
| # | Program | Description | Source Code |
|---|---|---|---|
| 1 | 08_PLSQL_Procedures.sql | Creating and executing stored procedures | View |
| — | Lab Report | Detailed experiment report | View |
Automating database actions using Row-Level Triggers.
Date: November 24, 2020
| # | Program | Description | Source Code |
|---|---|---|---|
| 1 | 09_1_Table_Creation.sql | Setup: Creating EMP and PROJECTS tables | View |
| 2 | 09_2_Trigger_Insert.sql | Trigger: Update hours on Insert | View |
| 3 | 09_3_Trigger_Update.sql | Trigger: Update hours on Update | View |
| 4 | 09_4_Trigger_Delete.sql | Trigger: Update hours on Delete | View |
| — | Lab Report | Detailed experiment report | View |
Handling row-by-row processing using Explicit and Implicit Cursors.
Date: December 02, 2020
| # | Program | Description | Source Code |
|---|---|---|---|
| 1 | 10_1_Explicit_Cursor.sql | Explicit Cursor example (Join) | View |
| 2 | 10_2_Implicit_Cursor.sql | Implicit Cursor example (For Update) | View |
| — | Lab Report | Detailed experiment report | View |
How to Use · Learning Path · Experiment 1 · Experiment 2 · Experiment 3 · Experiment 4 · Experiment 5 · Experiment 6 · Experiment 7 · Experiment 8 · Experiment 9 · Experiment 10
CSC502 & CSL503 · Semester V · Computer Engineering
University of Mumbai · Curated by Amey Thakur