💠 Database Management System Concept

Objective: This lab-based Oracle SQL tutorial is designed to build a strong practical foundation in SQL programming using Oracle Database. Each lab includes step-by-step exercises, real-world scenarios, and SQL scripts for active learning.

🔍 Lab 1: Introduction & Basic Queries

Explore Oracle SQL*Plus
SELECT – Retrieve data from a table
Practice: Query employees and departments
Analytical SQL Questions for Employees & Departments

🔃 Lab 2: Sorting and Filtering Data

🔗 Lab 3: Joining Tables

📊 Lab 4: Grouping and Aggregation

🔁 Lab 5: Subqueries

➕ Lab 6: Set Operations

🔄 Lab 7: Pivoting Data

🎚 Lab 8: Limiting Rows

🏗 Lab 9: Creating and Modifying Tables

📁 Lab 10: Data Types

🔐 Lab 11: Constraints

✏️ Lab 12: Modifying Data

⏳ Lab 13: Temporary Tables

👁 Lab 14: Views

⚡ Lab 15: Indexes

📛 Lab 16: Synonyms

CREATE, DROP SYNONYM
Practice: Use synonyms for object access

🔢 Lab 17: Sequences

🔢 SQL & PL/SQL CONCEPTUAL EXERCISE

📛 PL SQL LAB Based Exercise

Section 1: SQL Subquery & Join Questions (30 Questions)

Overview of Subqueries
- Single-row vs Multi-row Subqueries
- Correlated vs Non-Correlated Subqueries
Overview of SQL Joins
- INNER, LEFT, RIGHT, FULL OUTER, CROSS
Combining Subqueries with Joins
Lab Questions 1–30

Section 2: PL/SQL Trigger Lab Exam (30 Questions)

Introduction to PL/SQL Triggers
Types of Triggers: BEFORE, AFTER, INSTEAD OF
Row-level vs Statement-level Triggers
Trigger Syntax and Examples
Lab Questions 1–30

Section 3: PL/SQL Stored Procedure

Q31 – Safe Delete Product with Exception Handling

Section 4: PL/SQL Cursor Lab Exam (30 Questions)

Introduction to Cursors
Implicit vs Explicit Cursors
Cursor Attributes (%ROWCOUNT, %FOUND, %NOTFOUND, %ISOPEN)
Cursor Loops and FOR Loops
Lab Questions 1–30

Section 5: Additional Resources

Best Practices for PL/SQL and SQL Queries
Common Errors and Debugging Tips
Recommended References and Tutorials

🧰 Tools Required

Oracle Database (XE or Standard)
SQL Developer or compatible tool
Sample schema: HR, SCOTT, or custom dataset

✅ Learning Outcomes

Master Oracle SQL fundamentals
Write optimized SQL queries
Design and manage Oracle database objects

📘 C Programming Language

🖥️ Learn C Programming From Scratch

Master C with practical examples, easy tutorials, and hands-on projects

📘 C Programming Syllabus

This is a detailed C programming syllabus designed for a 6-month semester. It is suitable for undergraduate students or beginners learning C programming.

📘 Module 1: Introduction to Programming (Weeks 1–2)

💡 Practice Problems for C Programming

Variables, Input, and Output: Write a C program to declare variables, take user input, and display the output.

Integer Data with Arithmetic Operators: Perform basic arithmetic operations using C.

Integer Division with Type Casting: Display division result as a floating-point using type casting.

Area of a Circle: Calculate area using π × r².

Celsius to Fahrenheit Conversion: Convert Celsius temperature to Fahrenheit.

Absolute Value: Display absolute value of any number.

Float & Double Manipulation: Handle and display float/double types.

Even / Odd Check: Check if a number is even or odd.

Positive / Negative / Zero: Check a number's sign.

Find Largest and Smallest: Determine the largest and smallest of three numbers.

🎥 Reference Videos: Playlist 1 Playlist 2

🧮 Module 2: Operators and Expressions (Week 3)

Practice Problems for C Programming

🔁 Module 3: Control Structures (Weeks 4–5)

Decision making:

Loops:

Jump statements:

🧵 Module 4: Arrays and Strings (Weeks 6–7)

1D Arrays
2D Arrays
Multidimensional Arrays
C String Programming Basics
5 beginner-friendly problems with code of String in C
String operations and functions (strcpy, strlen, etc.)

Array Practice Problems

String Practice Problems

🆕Top 5 Array and String Problems in C (ICPC Style) 🌟

🧩 Module 5: Functions (Weeks 8–9)

📍 Module 6: Pointers (Weeks 10–11)

📦 Module 7: Structures and Unions (Weeks 12–13)

📁 Module 8: File Handling (Weeks 14–15)

💾 Module 9: Dynamic Memory Allocation (Week 16)

Memory allocation functions: malloc(), calloc(), realloc(), free()
Memory leaks and handling

⚙️ Module 10: Preprocessor Directives (Week 17)

#define, #include, #ifdef, #ifndef
Macros and conditional compilation

🔀 Module 11: Miscellaneous Topics (Week 18)

Command-line arguments
Enumerations and typedefs

🛠️ Weeks 19–24: Project & Final Assessment

Mini project development
Internal assessments and lab tests
Final practical and written exams

🧪 Lab Work / Assignments

100-125 practical programs based on the above modules
Sample mini projects: Student Record System, Bank Management System, File Encryption Tool
Write a program in C to print the average of all prime numbers between 1 to n
Write a C program that takes the dimensions and elements of two matrices A (n × m) and B (m × p) from the user, checks whether multiplication is possible, and prints the resulting matrix C (A × B).Write a C program that takes the dimensions and elements of two matrices A (n × m) and B (m × p) from the user, checks whether multiplication is possible, and prints the resulting matrix C (A × B).
Write a program in C to rotate the elements of an array to the right by one position (last element becomes first)
Write a program in C to find the reverse of an integer using recursion.
Draw a flowchart that checks if a number is an armstrong number or not.
Write a C program to check whether a year is a leap year or not using a ternary operator.
Develop a C program to draw a pattern of characters like the one shown below:

A B C D
B C D E
C D E F
D E F G

Write a program that finds and prints all Armstrong numbers between 1 to n.
Write a recursive C program to find the reverse of a string using recursion.
Write a program in C to rotate the elements of an array to the left by two positions (first element becomes second last).
Write a C program that swaps two float numbers using functions.
Draw a flowchart that checks if a number is a prime number or not.
Write a C program that determines the largest of three numbers using a ternary operator.
Develop a C program to draw a pattern of characters like the one shown below:

             A
           B B
         C C C
       D D D D

SQL Insert command in details

Understanding the SQL INSERT Command

Let’s take a closer look at the SQL INSERT command below:

INSERT INTO customers
(customer_id, first_name, last_name, email, created_at)
VALUES (101, 'Ahsan', 'Arif', 'aa@x.com', '11-jan-4');

At first glance, this might appear to be a basic statement, but it plays a vital role in managing data within relational databases. Let’s break it down.

INSERT INTO customers

This tells the database to add a new record to the customers table—a central location where client information is stored. Keeping this data accurate and up to date is key for managing customer relationships effectively.

Column List

Within the parentheses, we list the fields where data will be inserted:

customer_id: A unique identifier for each customer, ensuring proper tracking and referencing.
first_name and last_name: These fields store the customer's name for personalization and service.
email: Critical for communication—used for updates, offers, and notifications.
created_at: A timestamp marking when the record was created, useful for historical tracking and analysis.

VALUES Clause

Here we provide the actual data:

101 as the customer ID
'Ahsan' as the first name
'Arif' as the last name
'aa@x.com' as the email
'11-jan-4' as the creation date

Why It Matters

Each insert like this keeps the customer database current. Accurate data entry is essential—it drives better insights, personalized services, targeted marketing, and streamlined operations.

Real-World Impact

Whether it's a retail brand tailoring promotions or a service team addressing customer needs, having precise and structured customer data enables more effective engagement and decision-making.

Final Thoughts

This simple command highlights the power of structured data. Every row added brings value—not just as stored information, but as an opportunity to understand and serve customers better. As you continue exploring SQL, remember that even the most basic operations are foundational to effective data management.

Thanks for reading—I hope this breakdown clarified how meaningful even a single SQL statement can be.

Introduction to the `INSERT` Command

Certainly! Below is a training module that focuses on the Oracle SQL `INSERT` command, specifically tailored for entering values into a `Customers` table.

Thank you for joining this training session today. We're going to dive into one of the fundamental concepts of SQL—specifically, the `INSERT` command. This command is essential for adding new records to our database tables, and today we will focus on how to use it effectively with a `Customers` table.

The `INSERT` command in Oracle SQL allows you to add new rows of data to a specified table. In our case, we’ll be inserting customer records into the `Customers` table. This table typically contains essential information about our customers, such as `Customer_ID`, `First_Name`, `Last_Name`, `Email`, and `Created_at`.

Basic Syntax

The basic syntax for the `INSERT` command is as follows:

```sql

INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);

```

Inserting Data into the Customer Table

Let’s consider our `Customer` table has the following structure:

- **Customer_ID**: Integer (Primary Key)

- **First_Name**: Varchar (Not Null)

- **Last_Name**: Varchar (Not Null)

- **Email**: Varchar (Unique)

- **Created_at**: Date (Default)

Now, to insert a new customer record, we would write an SQL command as follows:

```sql

INSERT INTO Customers (Customer_ID, First_Name, Last_Name, Email, created_at)

VALUES (1, 'Mr', 'Rahim', 'mr@tc.com', '01-jan-24');

```

Inserting Multiple Records

You may often need to input multiple records at once. Oracle SQL allows this by extending the `INSERT` statement like so:

```sql

INSERT INTO Customers(Customer_ID, First_Name, Last_Name, Email, created_at)

VALUES

(2, 'Md', 'Karim', 'mk@tc.com', '01-feb-24'),

(3, 'Mis', 'Lucky', 'ml@tc.com', '01-mar-23');

```

Important Considerations

1. **Primary Key Constraint**: Remember that if `Customer_ID` is a primary key, each value must be unique. Attempting to insert a duplicate value will result in an error.

2. **Data Types**: Ensure the values you provide match the data types defined in the table schema. For example, an integer is expected for numeric fields.

3. **NULL Values**: If your table allows NULL values for certain columns, you can choose to omit them from your `INSERT` statement, like so:

```sql

INSERT INTO Customers (Customer_ID, First_Name, Last_Name)

VALUES (4, 'Mis', 'Berly');

```

4. **Using Default Values**: If any column has a default value defined, it will automatically be used if you do not specify a value for that column during the insert.

Conclusion

The `INSERT` command is a powerful tool for populating your database. By understanding how to use it effectively, you'll be able to maintain and manage your customer records efficiently.

Remember, practice is key! I encourage you to try inserting records into your `Customers` table and explore different scenarios like bulk inserts or handling errors.

If you have any questions or need further clarification on any point, please feel free to ask. Happy querying!

Understanding the Oracle `CREATE TABLE` Command

For details : TrustCoding

Today, I’d like to talk to you about a fundamental aspect of database management in Oracle: the `CREATE TABLE` command. This command is essential for defining and establishing the structure of our data within a relational database.

Introduction to the `CREATE TABLE` Command

The `CREATE TABLE` command allows us to create a new table in our Oracle database. A table acts as a structured collection of data, organized in rows and columns, much like a spreadsheet. Each table reflects a particular entity, such as customers, products, or orders, and serves as a fundamental building block for data storage.

Basic Syntax

The basic syntax of the `CREATE TABLE` command looks like this:

CREATE TABLE table_name (

column1_name datatype [constraint],

column2_name datatype [constraint],

...

);

**table_name**: This is the name you choose for your new table.

**column1_name, column2_name**: These are the names of the columns you want to include in your table.

**datatype**: Each column requires a data type, such as `VARCHAR2` for variable-length strings, `NUMBER` for numeric values, or `DATE` for date values.

**constraint**: Constraints are optional rules applied to the columns, such as `PRIMARY KEY`, `FOREIGN KEY`, `UNIQUE`, or `NOT NULL`, which help maintain data integrity.

Example of Using `CREATE TABLE`

Let's look at an example to clarify this further. Suppose we want to create a table for storing customer information. Here’s how the command might look:

CREATE TABLE customers (

customer_id NUMBER PRIMARY KEY,

first_name VARCHAR2(50) NOT NULL,

last_name VARCHAR2(50) NOT NULL,

email VARCHAR2(100) UNIQUE,

created_at DATE DEFAULT SYSDATE

);

In this example, we’ve created a table named `customers` with five columns. The `customer_id` is defined as the primary key, ensuring that each customer has a unique identifier. The `email` column has a unique constraint, preventing duplicate email addresses. Additionally, the `created_at` column automatically records the date and time the record was created.

Key Considerations

When using the `CREATE TABLE` command, there are a few important considerations:

1. **Naming Conventions**: Choose clear and descriptive names for your tables and columns, which helps in understanding the data structure.

2. **Data Types**: Selecting the appropriate data types for your columns is crucial. This affects storage efficiency and performance.

3. **Constraints and Relationships**: Properly defining constraints ensures the integrity of your data. Consider how tables relate to each other and use foreign keys for relational integrity.

4. **Indexes**: After creating a table, you might want to create indexes to improve query performance for specific columns, particularly those frequently used in searches.

Conclusion

In conclusion, the Oracle `CREATE TABLE` command is a powerful tool that lays the foundation for our databases. It allows us to define the structure of our data clearly, ensuring that we can efficiently store, retrieve, and manage that data. Whether you're a beginner or looking to refine your database skills, mastering the `CREATE TABLE` command is essential.

Thank you for your attention, and I hope this overview has given you a better understanding of how to construct tables in Oracle databases. If you have any questions, feel free to ask!

For details : TrustCoding