note.md

Thứ tự trong select

SELECT dept_id, SUM(salary) SALARY
  FROM employees
  WHERE xxx
  GROUP BY dept_id
  HAVING SUM(salary) >= 100
  ORDER BY SALARY DESC;

Other

• thứ tự ưu tiên trong where

  • các phép toán
  • concat
  • compare condition
  • is [not] null, like, [not] in
  • [not] between
  • [not] equal to
  • NOT
  • AND
  • OR

• Khi so sánh AND, nếu 1 bên null:

  • bên còn lại 1 bên true => kết quả là null
  • bên còn lại 1 bên false => kết quả là false
  • bên còn lại 1 bên null => kết quả là null

• Khi so sánh OR, nếu 1 bên null:

  • bên còn lại null => kết quả null
  • bên còn lại true => kết quả true
  • bên còn lại false => kết quả null

• NOT của null = null

• Null trong kết quả

  • với sort ASC, null sẽ ở dưới cùng của kết quả
  • với sort DESC, null sẽ ở trên cùng của kết quả
  • NULL FIRST và NULL LAST để thay đổi thứ tự này

• rowid

  • là địa chỉ vật lý của row
  • được gen tự động khi insert record
  • cách nhanh nhất để lấy 1 row là qua rowid
  • không thể thay đổi

• rownum

  • số sequence tăng lên khi lấy kết quả về
  • thay đổi khi chạy lại câu select

• scalar sub query

  • là sub query trả về 1 cột cho 1 row
  • nếu scalar sub query trả về null hoặc 0 row => main query trả về nothing
  • nếu scalar sub query trả về > 1 row, main query trả về lỗi

• correlate sub query

  • là câu sub query reference đến column của parent query
  • tên gọi khác: repeating sub query, synchronized sub query
  • Ex: select * from emp a where sal = (select max(sal) from emp b where b.dpt_id = a.dpt_id )

• DML: data manipulate language: select, update, insert, delete

• DDL: data definition language: create, drop, truncate

• DCL: data control language

• RR format: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Format-Models.html#GUID-6C75461E-2E18-4C35-9EB4-038A7E1C9C1F

  • The RR format calculates the century based on a 100-year window, but it still allows the user to enter the century manually if needed (i.e., it’s not strictly automatic). The century is derived using the last two digits of the year provided.
  • The RRRR format automatically calculates the century from the SYSDATE function. It also allows the user to enter the century explicitly, making it more flexible than the RR format. This format was introduced to ensure that dates like 99 can be interpreted correctly, falling within the appropriate century.
  • The RR date format replaced the YY deprecated format.
    • RR was introduced to overcome limitations of the YY format (which couldn't handle century transitions well).
    • While YY isn't officially deprecated, RR is preferred for handling 2-digit years correctly across centuries.

• Unique key columns can allow nulls, but the nulls must be unique (and in many databases, multiple nulls are allowed since null ≠ null).

• Every foreign key value must refer to a matching primary or unique key value.

• Views with the same name but different prefixes, such as DBA, ALL and USER, reference the same base tables from the data dictionary.

• BOOLEAN is not a valid SQL data type in Oracle SQL (at least not in standard table columns).

  • You can use BOOLEAN in PL/SQL blocks (procedures, functions), but not in table definitions.

• In a character sort, the values are case-sensitive.

• To determine the correct sequence of how Oracle evaluates a correlated subquery, let’s first understand what happens during execution:

  • A correlated subquery is a subquery that references a column from the outer query. It runs once for each row returned by the outer query.
    • Step 2: Fetch a candidate row from the outer query's table.
    • Step 4: Evaluate the inner query using values from that candidate row.
    • Step 1: Use the result of the inner query to evaluate the WHERE clause of the outer query.
    • Step 3: Repeat for the next candidate row.

• Substitution variables are global or per-application placeholders that represent variable values

• A table owner must grant the REFERENCES privilege to allow other users to create FOREIGN KEY constraints using that table.

• If the subquery returns 0 rows, then the value returned by the subquery expression is NULL

  • If a subquery returns NULL, the main query may still return rows.

• Oracle Database can automatically convert a value from one datatype to another.

• View

  • khi tạo view với: with check option, user ko thể thực hiện DML với những dữ liệu và view ko select được. Ví du: view tạo với điều kiện sal = 80, thì user ko thể insert 1 record có sal != 80
  • with read only: ngăn chặn toàn bộ insert/update/delete ảnh hưởng view
  • In Oracle SQL, using the FORCE option when creating a view allows its definition to be stored even if referenced base tables do not exist at the time.
  • A view causes no storage space consumption.
  • Views have no segment.

• The WHERE clause can be used to exclude rows before dividing them into groups.

• Dropping a partition key columns is not allowed.

• Constraints can be defined at both the column level and the table level, but not all constraints can be defined at both levels. For example, the PRIMARY KEY constraint can only be defined at the column level

• A logical delete (like setting a column as unused) and a physical delete (like dropping the column) are not the same—the former is reversible until physically dropped.

• AWR (Automatic Workload Repository) Compare Period report is the correct choice for identifying the specific change that caused a performance difference in the Oracle database. The AWR report allows you to compare two different time periods and analyze the performance metrics to pinpoint the root cause of the performance change.

• Oracle non equi join

  • Using table alias provides no significant performance improvement in queries
  • There is no significant performance difference between >= <= and BETWEEN

• USING is used only in equijoins

• First normal form

  • https://www.geeksforgeeks.org/dbms/normal-forms-in-dbms/
  • ‘First Normal Form’ (1NF) results in a relation with no multivalued attributes.
  • ‘Second Normal Form’ (2NF) results in a relation with no partial functional dependencies.
  • ‘Third Normal Form’ (3NF) results in a relation with no transitive functional dependencies (functional dependency between two non-keys attributes).
  • ‘Boyce-Codd Normal Form’ is achieved by removing the remaining function dependencies after 3NF.
  • ‘Fourth Normal Form’ results in a relation with no multivalued dependencies.
  • ‘Fifth Normal Form’ is achieved with removing of all the remaining anomalies.

• During execution, the Oracle server may read data from storage if the required data is not already in memory.

• The SESSION_PRIVS dictionary view in Oracle Database contains the current system privileges that are available in the user session. This view shows the system privileges that have been granted directly to the user or through roles.

• The NOT NULL constraint must be defined at the column level, not at the table level. This ensures that the ENAME column cannot contain NULL values, which is a requirement for data integrity.

• Single row functions can be character functions, numeric functions, date functions, and conversion functions.

• The minimum column width that can be specified for a varchar2 data type column is one.

• The value for a CHAR data type column is blank-padded to the maximum defined column width.

• You can drop or create an index without physically affecting the indexed table.

• CHECK (INSTR(email, '@') > 1); => có thể null

• Invisible indexes are still maintained during DML operations. They’re just ignored by the optimizer unless explicitly enabled for use.

• Subqueries can be categorized into two types:

  • A noncorrelated (simple) subquery obtains its results independently of its containing (outer) statement.
    • A noncorrelated subquery executes independently of the outer query. The subquery executes first, and then passes its results to the outer query
  • A correlated subquery requires values from its outer query in order to execute.
    • A correlated subquery typically obtains values from its outer query before it executes. When the subquery returns, it passes its results to the outer query

• Identify the true statements regarding multiple-row subqueries

  • They should not be used with the NOT IN operator in the main query if NULL is likely to be a part of the result of the subquery.
  • Oracle subquery with IN and NOT IN operators
    • The subquery that uses the IN operator often returns a list of zero or more values. After the subquery returns the result set, the outer query makes uses of them.
    • Using NOT IN with a subquery that returns even a single NULL results in no rows being returned, due to unknown comparison.
      • Example:
        • WHERE dept_id NOT IN (SELECT dept_id FROM departments)
      • If one of the dept_ids is NULL, the condition fails.
      • If any item in a list following a NOT IN operation evaluates to null, then all rows evaluate to FALSE or UNKNOWN, and no rows are returned

• In Oracle, VARCHAR2 must specify a length

  • CHAR data type without length defaults to CHAR(1)

• Projection means a SELECT query.

• The true statements about Structured Query Language (SQL) are:

  • It best supports relational databases.
  • It guarantees atomicity, consistency, isolation, and durability (ACID) features. (Understanding that the RDBMS enforces these for SQL transactions.)
  • If a table contains hierarchical data, you can select rows in a hierarchical order using the hierarchical query clause.
  • It provides independence for logical data structures being manipulated from the underlying physical data storage. (Understanding that this independence is a feature of the RDBMS that SQL interacts with, rather than being solely provided by the SQL language in isolation).

• câu sql error

  SELECT MIN(Salary) minsal, MAX(salary) maxsal
    FROM employees
    WHERE hire_date < SYSDATE - 365
    GROUP BY MIN(salary), MAX(salary);

  • GROUP BY MIN(salary), MAX(salary) is invalid — you cannot group by aggregate functions.
  • Invalid syntax — will cause error

• Identify the primary difference between the relational database (RDB) and object-oriented database (OODB) models.

  • OODB incorporates methods with data structure definition, whereas RDB does not allow this.

• correct syntax cho tạo index lúc tạo bảng cho PK:

  CREATE TABLE order_item(
      order_id NUMBER(3),
      item_id NUMBER(2),
      qty NUMBER(4),   --- Corrected syntax error (replaced period with comma)
      CONSTRAINT order_itm_id_pk
      PRIMARY KEY (order_id, item_id)
      USING INDEX (
      CREATE INDEX ord_itm_idx
      ON order_item (order_id, item_id)
      )
      );

• DML operations are not permitted on views that include group functions, a GROUP BY clause, the DISTINCT keyword, or the ROWNUM pseudocolumn.

  • INSERTs through a view are only allowed if certain conditions are met: the view must not include joins, group functions, etc., and all required columns (e.g., NOT NULL without default) must be present in the view.

• Identify the true statements regarding the usage of the WITH clause in complex correlated subqueries.

  • The WITH clause can hold more than one query.
  • If the query block name and the table name are the same, then the table name takes precedence.
  • The query name in the WITH clause is visible to other query blocks in the WITH clause as well as to the main query block
  • It can be used only with the SELECT clause.
    • In standard SQL, and most implementations (like Oracle, PostgreSQL, SQL Server), WITH is used to define subquery blocks to be referenced in a SELECT statement.
    • While some systems (like PostgreSQL or SQL Server) allow WITH to be used with INSERT, UPDATE, or DELETE, in Oracle SQL, it is typically used only with SELECT.

• Literal Components (Thành phần giá trị hằng): Trong SQL, literal là một giá trị cố định, được viết trực tiếp vào câu lệnh (ví dụ: chuỗi ký tự được đặt trong dấu nháy đơn '...', số 100, hoặc ngày tháng DATE '2025-01-01'). Đây là các giá trị không phải là tên cột hay tên bảng.

  • A literal is a fixed value that is written directly in the query — it is not a column name, table name, clause, or operator.
  • The terms literal and constant value are synonymous and refer to a fixed data value.

• Identify the tasks which can be performed using SQL functions built into Oracle Database.

  • Finding the number of characters in an expression (length)
  • Combining more than two columns or expressions into a single column in the output (concat)
  • Substituting a character string in a text expression with a specified string (REPLACE)
  • Displaying a date in a nondefault format (TO_CHAR)

• UNION vs UNION ALL

  • NULLS are not ignored during duplicate checking

• Store the query in a script and pass the substitution value to the script when executing it.

  • True – You can avoid prompting by passing values at runtime from the command line or script.
  • Example: bash @your_script.sql '01-JAN-2020'
  • Inside the script: sql SELECT employee_id, first_name, salary FROM employees WHERE hire_date > '&1';
  • Since the value is passed as an argument, there is no need for a prompt.

• Which of the following are true statements about the Oracle database Data Dictionary?

  • SYS owns all base tables and user accessible views in Data Dictionary
  • Base tables are the underlying tables that store information about the associated database. Only the DBA should write to and read these tables.
  • Data dictionary contains the privileges and roles each user has been granted

• Identify three true statements about system and object privileges. (Choose three)

  • Revoking an object privilege that was granted with the WITH GRANT OPTION clause has a cascading effect
  • Adding a foreign key constraint pointing to a table in another schema requires the REFERENCES object privilege
  • Adding a primary key constraint to an existing table in another schema requires a system privilege

• sub query

  • A subquery can retrieve zero or more rows.
  • A subquery can appear on either side of a comparison operator.
  • There is no limit on the number of subquery levels in the FROM clause of a SELECT statement.

• AVG ignore null

• Aggregate functions can not be nested to any number of levels

• The MAX and MIN functions can be used on columns with character data types

• To create a table in another user's schema, you must have the CREATE ANY TABLE system privilege

• CREATE TABLE statements do not commit a transaction if the TEMPORARY keyword is used

Đọc thêm

• date format