Using explode for Column-to-Row Transformation To convert a delimited string column into multiple rows: SELECT other_cols, exploded_col FROM source_table LATERAL VIEW explode(split(target_column, ',')) tmp AS exploded_col; The split() function breaks the string into an array, explode() turns each ar...
Fundamental Data Selection Retrieving Specific Columns To obtain student names from a pupil table: SELECT pupil_name FROM pupil; Fetching multiple attributes like name and gender: SELECT pupil_name, gender FROM pupil; Selecting all available attributes: SELECT * FROM pupil; The SELECT keyword initia...
1 Encapsulate a Global Object Create a new C++ class named sqlmange, and add sql to the .pro file. Use the C++ singleton pattern to ensure only one instance exists and provide a global access point. The following code implements the SQLManager class, which manages database connections and operations...
Analyzing Object Storage and Segments 1. Assessing Table Size and Row Counts To evaluate storage consumption across all schemas, join the data dictionary views regarding tables and segments. The following query calculates size in gigabytes and retrieves row estimates. -- Segment size analysis across...
Data Definition Language (DDL) DDL encompasses commands used to define and modify database schema structures. These operations affect the organization and properties of database objects rather than the data itself. Key commands include: CREATE: Establishes new database objects ALTER: Modifies existi...
Basic SQL Queries Simple Queries -- Query all data from the employee table: SELECT * FROM employee; -- * represents all columns -- Display specific columns: SELECT employee_id, name, salary FROM employee; -- Filter rows using WHERE clause: SELECT * FROM employee WHERE salary > 2000; -- Combine co...
This article demonstrates practical methods for querying data across multiple tables using join operations and subqueries. The examples utilize a sample database named XSCJ containing student, sc (student-course), and course tables. 1. Retrieve Student IDs, Names, Course Numbers, and Grades This que...
UPDATE Function Description Updates data in a table. The UPDATE statement modifies specified columns in all rows that meet the declared condition, with the condition set via the WHERE clause. Columns listed in the SET clause are updated, while unlisted columns retain their original values. Notes Thi...
In SQL, the NOT NULL constraint ensures that a column does not accept NULL values. This constraint is typically used when creating or modifying tables to ensure data integrity and accuracy. Here are some common uses of NOT NULL: Specifying NOT NULL Constraint When Creatign a Table: When creating a n...
PostgreSQL is known for its strict type system. Unlike some database that perform aggressive implicit type coercion, PostgreSQL often requires explicit instructions when transforming data from one format to another. Failing to handle these transitions correctly results in runtime exceptions that can...
