Data Manipulation Language

Data Manipulation Language (DML) is a subset of SQL (Structured Query Language) used to interact with databases like Oracle. It encompasses various operations for managing data within a database system such as querying, inserting, updating, and deleting data from tables.

SELECT Statement

• The SELECT statement is fundamental in DML, allowing users to retrieve data from one or more tables in a database. Key features include:
• Projection, selection, and joining capabilities for fetching specific data
• Options like DISTINCT to remove duplicate rows, UNIQUE to ensure unique results, and ALL to include all rows
• Pseudo columns such as ROWNUM for row numbers and ROWID for row identifiers
• Usage of ASTERISK (*) for selecting all columns from a table
• Manipulation of literals like numbers, strings, dates, and intervals within queries
• Application of operators following standard precedence rules for calculations

Views, Tables, and Synonyms

• Views: Virtual tables generated by executing a SELECT query, providing a structured representation of data from one or more tables.
• Tables: Physical storage structures in a database holding data, defined in the data dictionary and consisting of rows and columns.
• Synonyms: Alias names for database objects such as tables, views, sequences, and procedures, simplifying queries and enhancing security by concealing actual object names.
• DESCRIBE: SQL command displaying the structure of a table, including column names, data types, and constraints.

Filtering and Sorting

• Filtering data involves utilizing the WHERE clause with conditions like IN for matching values in a list, LIKE for pattern matching, IS NULL to identify null values, and NOT NULL to exclude null values
• Understanding the behavior of NULL values in comparisons and logical operations
• Sorting data using the ORDER BY clause to arrange results in ascending or descending order based on one or more columns