2.
SQL (Structured Query Language)
2.1 Introduction
• SQL is a standard language used to manage and manipulate databases.
• Works with relational databases like MySQL, SQL Server, Oracle, PostgreSQL.
2.2 SQL Commands
1. DDL (Data Definition Language): Defines database structure
• CREATE TABLE → Create table
• ALTER TABLE → Modify table
• DROP TABLE → Delete table
Example:
CREATE TABLE Students (
ID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT
);
2. DML (Data Manipulation Language): Manage data
• INSERT INTO → Add data
• UPDATE → Modify data
• DELETE → Remove data
Examples:
INSERT INTO Students (ID, Name, Age) VALUES (1, 'Ali', 18);
UPDATE Students SET Age = 19 WHERE ID = 1;
DELETE FROM Students WHERE ID = 1;
3. DQL (Data Query Language): Retrieve data
• SELECT → Fetch data
Examples:
SELECT * FROM Students;
SELECT Name, Age FROM Students WHERE Age > 18;
4. DCL (Data Control Language): Control access
• GRANT → Give privileges
• REVOKE → Remove privileges
5. TCL (Transaction Control Language): Manage transactions
• COMMIT → Save transaction
• ROLLBACK → Undo transaction
• SAVEPOINT → Set savepoint
2.3 SQL Clauses
• WHERE → Filter records
• ORDER BY → Sort records
• GROUP BY → Group records
• HAVING → Filter groups
• DISTINCT → Remove duplicates
• LIKE → Search pattern
• BETWEEN → Range of values
• IN → Specify multiple values
2.4 SQL Functions
• COUNT() → Number of rows
• SUM() → Total of numeric column
• AVG() → Average value
• MAX() → Maximum value
• MIN() → Minimum value
• UPPER()/LOWER() → Convert text to upper/lower case
• NOW() → Current date & time
2.5 Advantages of SQL
• Efficient data management
• Easy retrieval and modification of data
• Works with large databases
• Standardized language across platforms
Oracle Database – Notes
1. Introduction
• Oracle Database is a relational database management system (RDBMS) developed by
Oracle Corporation.
• Used to store, manage, and retrieve data efficiently.
• Supports SQL and PL/SQL for database operations.
2. Features of Oracle
1. Multi-User Support: Multiple users can access the database simultaneously
2. Data Security: User authentication, roles, and privileges
3. Scalability: Handles large databases
4. Backup & Recovery: Protects data from loss
5. PL/SQL Support: Combines SQL with procedural programming
6. Data Integrity: Enforces rules to maintain correct data
3. Oracle Architecture
1. Instance: Memory structures and background processes managing the database
2. Database: Physical storage of data (tables, indexes, etc.)
3. Schema: Collection of database objects owned by a user
4. Tablespace: Logical storage unit grouping related objects
5. Datafiles: Physical files storing database data
4. Oracle Database Objects
• Table → Stores data in rows and columns
• View → Virtual table based on a query
• Index → Speeds up data retrieval
• Sequence → Generates unique numbers
• Synonym → Alias for a database object
• Trigger → Automatic action on a table event
• Stored Procedure → Predefined SQL + PL/SQL block
• Function → Returns a value after computation
5. SQL in Oracle
5.1 Data Definition Language (DDL)
• CREATE TABLE → Create a table
• ALTER TABLE → Modify table structure
• DROP TABLE → Delete table
Example:
CREATE TABLE Students (
ID NUMBER PRIMARY KEY,
Name VARCHAR2(50),
Age NUMBER
);
5.2 Data Manipulation Language (DML)
• INSERT INTO → Add records
• UPDATE → Modify records
• DELETE → Remove records
5.3 Data Query Language (DQL)
• SELECT → Retrieve records
Example:
SELECT Name, Age FROM Students WHERE Age > 18;
5.4 Data Control Language (DCL)
• GRANT → Assign privileges
• REVOKE → Remove privileges
5.5 Transaction Control Language (TCL)
• COMMIT → Save changes
• ROLLBACK → Undo changes
• SAVEPOINT → Set point to rollback
6. Constraints in Oracle
• PRIMARY KEY → Unique identifier for table rows
• FOREIGN KEY → Links tables together
• UNIQUE → Ensures all values in a column are unique
• NOT NULL → Column cannot have null values
• CHECK → Ensures column values satisfy a condition
7. PL/SQL in Oracle
• PL/SQL (Procedural Language for SQL): Extension of SQL with procedural constructs
Block Structure:
DECLARE
v_name VARCHAR2(50);
BEGIN
v_name := 'Ali';
DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
END;
Components:
• Variables
• Loops
• Conditions
• Cursors
• Exception Handling
8. Oracle Functions
• NVL(expr1, expr2) → Replaces NULL with a value
• SYSDATE → Current date and time
• UPPER()/LOWER() → Converts text to upper/lower case
• ROUND() → Rounds numbers
• COUNT(), SUM(), AVG(), MAX(), MIN() → Aggregate functions
9. Advantages of Oracle
• Handles large and complex databases efficiently
• Provides high security
• Supports multi-user environment
• Robust backup and recovery features
• Supports SQL and PL/SQL programming
10. Applications of Oracle
• Banking systems
• Airline reservation systems
• Hospital management systems
• University management systems
• Business intelligence and ERP systems
VB (Visual Basic) & SQL – Notes
1. VB (Visual Basic)
1.1 Introduction
• Visual Basic (VB) is a high-level, event-driven programming language developed by
Microsoft.
• Used to develop Windows applications, GUI apps, and database programs.
• Supports drag-and-drop GUI design and object-oriented programming concepts.
1.2 Features of VB
1. Event-Driven: Responds to user actions (clicks, key presses)
2. Rapid Application Development (RAD): Fast GUI creation
3. Object-Oriented: Supports classes and objects
4. Integration with Databases: Works well with Access, SQL Server
5. Easy Syntax: Similar to English language
1.3 Structure of VB Program
Private Sub Command1_Click()
MsgBox "Hello, World!"
End Sub
1.4 Common Controls
• TextBox → Input text
• Label → Display text
• Button → Trigger actions
• ListBox → Display list of items
• ComboBox → Drop-down list
• CheckBox → Select/Deselect option
• RadioButton → Choose one option from a group
• Frame → Group controls together
1.5 Variables & Data Types
• Integer → Whole numbers
• Long → Large integers
• Single → Floating-point numbers
• Double → Double-precision decimals
• String → Text
• Boolean → True / False
• Date → Date and time
1.6 Control Statements
If...Then...Else
If score >= 50 Then
MsgBox "Pass"
Else
MsgBox "Fail"
End If
Select Case
Select Case grade
Case "A"
MsgBox "Excellent"
Case "B"
MsgBox "Good"
Case Else
MsgBox "Needs Improvement"
End Select
1.7 Loops
For...Next
For i = 1 To 5
MsgBox i
Next i
While...Wend
While counter < 5
counter = counter + 1
Wend
Do...Loop
Do
counter = counter + 1
Loop While counter < 5
1.8 Advantages of VB
• Easy and fast GUI development
• Event-driven programming simplifies user interaction
• Good integration with databases
• Reduces coding effort
1.9 Applications of VB
• Desktop applications
• Database management apps
• Windows forms and controls
• Reporting and automation tools