# Exam PingCAP Certified SQL Developer [TiDB v6.x]

### 1.
Which technology does TiDB utilize for MVCC? (Select two items)

+ options
  + A. Snapshots
  + B. Timestamp Oracle
  + C. Add the version number to the Key of the Key-Value pair in TiKV
  + D. Past Image of data block cache
+ Answers: B, C

### 2. 
Which of the following TiDB instances is stateless?

+ options
  + A. Placement Driver server instance
  + B. TiKV server instance
  + C. TiDB server instance
  + D. MVCC server instance
+ Answers: C

### 3. 
True or False. TiKV cannot be used as a standalone database, it must be used as part of a TiDB cluster.

+ options
  + A. True
  + B. False
+ Answers: B

### 4. 
If any node running a TiKV server instance fails, the TiDB server instance loses access to the corresponding data, thereby disrupting the normal operation of the entire TiDB cluster. To recover normal functioning, the entire TiDB cluster of the failed node needs to be repaired.

+ options
  + A. True
  + B. False
+ Answers: B

### 5. 
What information does the Placement Driver servers provide to other TiDB components that can be used to guarantee the ordering of events for the entire TiDB cluster?

+ options
  + A. Raft Sequence
  + B. MVCC Version
  + C. TXN Sequence
  + D. TSO
+ Answers: D

### 6. 
Which of the following is the data replication unit in TiKV's Multi Raft Group?

+ options
  + A. Cell
  + B. Region
  + C. Block
  + D. RDD
+ Answers: B

### 7. 
If all TiDB servers lose power at the same time and their disks are damaged, the business data will be lost.

+ options
  + A. True
  + B. False
+ Answers: B

### 8. 
Which of the following database access protocols is TiDB Server compatible with?

+ options
  + A. MySQL
  + B. Oracle
  + C. DynamoDB
  + D. PostgreSQL
+ Answers: A

### 9. 
The correct description for the SQL statement `SELECT COUNT(*) FROM universe.planets` is? (Select two items)

+ options
  + A. Related tasks can only be performed on the TiDB server to which the user session is connected.
  + B. It can use multiple TiKV servers to execute in parallel.
  + C. This is a typical distributed SQL scenario in TiDB.
  + D. The `COUNT` operation will be executed only once on the TiDB server, regardless of the number of TiKV server nodes. And, TiKV servers can not help.
+ Answers: B, C

### 10. 
Which of the following characteristics are unique to TiDB compared to traditional relational database management systems (RDBMS)? (Select two items)

+ options
  + A. Components are decoupled, providing good expandability and scalability, both scale-in and scale-out.
  + B. Support ACID, ensuring strong transaction consistency.
  + C. Having a stateless front-end.
  + D. Clusters must rely on shared storage resources.
+ Answers: A, C

### 11. 
Which of the following is responsible for data persistence in TiKV server?

+ options
  + A. MariaDB
  + B. MySQL
  + C. TiRow
  + D. RocksDB
+ Answers: D

### 12. 
The TiKV server is capable of parsing and executing SQL statements.

+ options
  + A. True
  + B. False
+ Answers: B

### 13. 
Which of the following server instances is stateful?

+ options
  + A. Placement Driver server instance
  + B. TiRow server instance
  + C. TiDB server instance
  + D. MVCC server instance
+ Answers: A

### 14. 
Which of the following is not a component of the TiDB server?

+ options
  + A. Physical Optimizer
  + B. Local Executor
  + C. RocksDB Scheduler
  + D. Distributed Executor
+ Answers: C

### 15. 
Which of the following environments are not suitable for running a TiDB cluster (including test sandboxes)? (Select two items)

+ options
  + A. Windows
  + B. Linux
  + C. macOS
  + D. Solaris
+ Answers: A, D

### 16. 
True or False. Queries A and B will produce the same output.

```
tidb> DESC planets;
+-----------------------+---------------+------+------+---------+-------+
| Field                 | Type          | Null | Key  | Default | Extra |
+-----------------------+---------------+------+------+---------+-------+
| id                    | bigint(20)    | NO   | PRI  | NULL    |       |
| name                  | char(20)      | NO   | MUL  |         |       |
| gravity               | decimal(8,1)  | NO   |      | 0.0     |       |
+-----------------------+---------------+------+------+---------+-------+

tidb> DESC moons;
+-----------+---------------+------+------+---------+-------+
| Field     | Type          | Null | Key  | Default | Extra |
+-----------+---------------+------+------+---------+-------+
| id        | bigint(20)    | NO   | PRI  | NULL    |       |
| name      | char(20)      | NO   | MUL  | NULL    |       |
| planet_id | bigint(20)    | NO   |      | NULL    |       |
+-----------+---------------+------+------+---------+-------+

tidb> SELECT min(gravity), max(gravity) FROM planets;
+--------------+--------------+
| min(gravity) | max(gravity) |
+--------------+--------------+
|          0.7 |         23.1 |
+--------------+--------------+
```
```(A)
SELECT p.name, p.gravity, m.name
 FROM planets p
 LEFT JOIN moons m
 ON p.id = m.planet_id
 WHERE p.gravity < 10;
```

```(B)
SELECT p.name, p.gravity, m.name
 FROM planets p
 LEFT JOIN moons m
 ON p.id = m.planet_id
 AND p.gravity < 10;
```

+ options
  + A. True
  + B. False
+ Answers: B

### 17. 
Under what circumstances would a table with a BIGINT data type as the primary key have a `_tidb_rowid` column?

+ Options:
  + A. This is highly unlikely.
  + B. Every table in TiDB has a `_tidb_rowid` column
  + C. Primary key declared as Non-Clustered.
  + D. No correct answers.
+ Answers: C

### 18. 
True or False. You should use prepared statements for every SQL query.

+ Options:
  + A. True
  + B. False
+ Answers: B

### 19. 
Which of the following correctly describes the UNION operation? (Select two items)

+ Options:
  + A. UNION operations can be used to deduplicate the results.
  + B. UNION ALL operations do not produce duplicates.
  + C. UNION ALL can combine two result sets with different numbers of columns, whereas UNION cannot.
  + D. UNION can combine two result sets with different numbers of columns, whereas UNION ALL cannot.
+ Answers: A

### 20. 
Assume we execute the following SQL statements. Does the final SELECT statement return the rows added by the `INSERT` statement?

```
BEGIN;
INSERT INTO test.u1 VALUES (100);
BEGIN;
ROLLBACK;
SELECT * FROM test.u1;
```
+ Options:
  + A. Yes
  + B. No
+ Answers: A

### 21. 
Which numeric data type would you choose to store user account balances in a table schema for a banking system?

+ options
  + A. BIGINT
  + B. DECIMAL
  + C. FLOAT
  + D. DOUBLE
+ Answers: B

### 22. 
True or False. Both LIKE and RLIKE expressions can be pushed down to TiKV. 

+ options
  + A. True
  + B. False
+ Answers: B

### 23. 
True or False. Based on the table definitions, the following SELECT statement will execute successfully.

```
tidb> DESC universe.planets;
+-----------------------+---------------+------+------+---------+-------+
| Field                 | Type          | Null | Key  | Default | Extra |
+-----------------------+---------------+------+------+---------+-------+
| id                    | bigint(20)    | NO   | PRI  | NULL    |       |
| name                  | char(20)      | NO   | MUL  |         |       |
+-----------------------+---------------+------+------+---------+-------+

tidb> DESC universe.moons;
+-----------+---------------+------+------+---------+-------+
| Field     | Type          | Null | Key  | Default | Extra |
+-----------+---------------+------+------+---------+-------+
| id        | bigint(20)    | NO   | PRI  | NULL    |       |
| name      | char(20)      | NO   | MUL  | NULL    |       |
+-----------+---------------+------+------+---------+-------+
```
```
SELECT name from universe.planets p join universe.moons m WHERE p.id = m.id;
```
+ options
  + A. True
  + B. False
+ Answers: B

### 24. 
True or False. The AUTO_INCREMENT attribute ensures that column values grow monotonically in the chronological order of the INSERT, regardless of DDL statements. 

+ options
  + A. True
  + B. False
+ Answers: B

### 25.
What is the result of the query `SELECT now() WHERE null = null`?

+ options
  + A. Errors.
  + B. Warning, and return an empty result.
  + C. System current timestamp.
  + D. Return an empty result.
  + E. System current datetime.
+ Answers: D

### 26.
Which of the following CREATE TABLE properties can mitigate the write hotspot of the primary key on the TiKV region?

+ options
  + A. LAST_INSERT_ID
  + B. AUTO_RANDOM
  + C. AUTO_INCREMENT
  + D. AUTO_SHARD
+ Answers: B

### 27. 
Before the TiDB DMR version v6.6, at which stage of a transaction (INSERT, UPDATE, DELETE) does a FOREIGN KEY constraint check for validation when data is modified?

+ options
  + A. FOREIGN KEY constraints are not enforced.
  + B. When DML commands are executed.
  + C. When the transaction is committed.
  + D. FOREIGN KEY constraints are not checked if tidb_disable_txn_auto_retry is not set to OFF.
+ Answers: A

### 28. 
Which of the following SQL statements ensure that the order of the output result set does not change after repeated execution when the table students has not been updated? (Select two items)

+ options
  + A. `SELECT name, _tidb_rowid FROM students WHERE score < 60;`
  + B. `SELECT name, _tidb_rowid FROM students WHERE score < 60 ORDER BY name;`
  + C. `SELECT name, _tidb_rowid FROM students WHERE score < 60 ORDER BY _tidb_rowid;`
  + D. `SELECT name, _tidb_rowid FROM students WHERE score < 60 LIMIT 3;`
  + E. `SELECT name, _tidb_rowid FROM students WHERE score < 60 ORDER BY name, _tidb_rowid;`
+ Answers: C, E

### 29. 
Which of the following can be used to declare integers with a maximum length of nine digits?

+ options
  + A. SMALLINT
  + B. INT
  + C. INT(9)
  + D. BIGINT(9)
  + E. No correct answers
+ Answers: E

### 30. 
Which of the following principles should be followed when selecting data types? (Select three items)

+ options
  + A. Brief and minimal resource consumption
  + B. Appropriate for the needs of the application
  + C. Complete, with no loss of data
  + D. Ubiquitous, a data type which is also compatible with other databases, such as PostgreSQL and Oracle.
+ Answers: ABC

### 31. 
What is the result of the query `SELECT 15.6E0 + 1 = 15.6`?

+ options
  + A. It may be 0
  + B. Must be 1
  + C. Will warn
  + D. Will report an error
+ Answers: A

### 32. 
What is the correct result for sorting characters "A","B","C","a","b","c","1","2","3","+" in ascending order when the collation is utf8mb4_general_ci?

+ options
  + A. 1 2 3 + A a B b C c
  + B. a A b B c C 1 2 3 +
  + C. 1 2 3 A a B b C c +
  + D. + 1 2 3 a A B b C c
+ Answers: D

### 33. 
What is the result of the SQL statement `SELECT NULL = NULL, '' = ' '`? (Note the whitespace)

+ options
  + A. 0, 0
  + B. 1, 1
  + C. 1, 0
  + D. NULL, 1
+ Answers: D

### 34. 
What SQL_MODE setting prevents the execution of the following SQL statement?

```
SELECT category_id, gravity, max(mass) FROM planets GROUP BY category_id;
```
+ options
  + A. ORACLE
  + B. ONLY_FULL_GROUP_BY
  + C. ANSI
  + D. No correct answers, the SQL has errors and cannot be executed.
+ Answers: BC

### 35. 
Which data type can be declared with a maximum length of nine bits?

+ options
  + A. SMALLINT
  + B. BINARY(9)
  + C. BIT(9)
  + D. BLOB(9)
  + E. No correct answers
+ Answers: C

### 36. 
True or False. The following CREATE TABLE command will successfully create table `t1`.

```
CREATE TABLE t1 (id INT PRIMARY KEY, steps ENUM(10, 20, 30));
```
+ options
  + A. True
  + B. False
+ Answers: B

### 37. 
What is the correct description of the MEDIUMTEXT data type?

+ options
  + A. There is no distinction between the MEDIUMTEXT data type and the CHAR data type.
  + B. The raft-entry-max-size is one of the factors influencing its maximum length.
  + C. The maximum length of MEDIUMTEXT is 2GB.
  + D. LIKE cannot perform fuzzy matching on columns of MEDIUMTEXT.
+ Answers: B

### 38. 
What is the correct description of the LONGTEXT data type?

+ options
  + A. There is no distinction between the LONGTEXT data type and the VARCHAR data type.
  + B. The txn-entry-size-limit is one of the factors influencing its maximum length.
  + C. The maximum length of LONGTEXT is 4GB.
  + D. LIKE cannot perform fuzzy matching on columns of LONGTEXT.
+ Answers: B

### 39. 
What is the correct description of the CHAR data type?

+ options
  + A. `CHAR(10)` has a maximum capacity of 10 bytes.
  + B. `CHAR(10)` denotes a fixed length of 10 bytes.
  + C. `CHAR(10)` indicates the maximum number of characters allowed is 10.
  + D. `CHAR(10)` indicates a fixed length of 10 characters.
+ Answers: D

### 40.
What is the result of the SQL query `SELECT 'A ' = ' A'` (note the whitespaces)? 

+ options
  + A. 0
  + B. 1
  + C. Errors
  + D. Warnings
+ Answers: A

### 41. 
What is the correct result of sorting characters "A", "B", "C", "a", "b", "c", "1", "2", "3", "+" in descending order of when the collation is utf8mb4_binary?

+ options
  + A. c b a C B A 3 2 1 +
  + B. C B A c b a 3 2 1 +
  + C. 3 2 1 + C B A c b a
  + D. + 3 2 1 c b a c b a
+ Answers: A

### 42. 
What is the result of the query `SELECT 8/0` by default?

+ options
  + A. NaN
  + B. NULL
  + C. 0
  + D. Errors
+ Answers: B

### 43. 
True or False. When you need to implement paginated queries, for performance considerations, the best practice is to use the `ORDER BY ... LIMIT <offset>, <n>` syntax.

+ options
  + A. True
  + B. False
+ Answers: B

### 44. 
True or False. The commands EXPLAIN and EXPLAIN ANALYZE are identical in their function and can be used interchangeably.

+ options
  + A. True
  + B. False
+ Answers: B

### 45. 
What is the output of the SQL statement `SELECT CONCAT_WS(NULL, 'Hello', ' World'), CONCAT('Hello', NULL, ' World')`?

+ options
  + A. NULL, Hello World
  + B. NULL, NULL
  + C. Hello World, NULL
  + D. World, NULL
+ Answers: B

### 46. 
What is the output of `SELECT 'Welcome'||' to '||'TiDB!'` by default?

+ options
  + A. Errors.
  + B. It will trigger a warning and return a value of 0.
  + C. 'Welcome to TiDB!'
  + D. NULL
+ Answers: B

### 47. 
Which of the following is not a data type of TiDB?

+ options
  + A. DATETIME
  + B. TIME
  + C. INTERVAL
  + D. YEAR
+ Answers: C

### 48. 
Find the planet with the highest mass in each category_id, select two best answers.

```
tidb> DESC planets;
+-----------------------+---------------+------+------+---------+----------------+
| Field                 | Type          | Null | Key  | Default | Extra          |
+-----------------------+---------------+------+------+---------+----------------+
| id                    | bigint(20)    | NO   | PRI  | NULL    | auto_increment |
| name                  | char(20)      | NO   | MUL  |         |                |
| mass                  | float         | NO   |      | 0.0     |                |
| category_id           | int(11)       | NO   |      | NULL    |                |
+-----------------------+---------------+------+------+---------+----------------+
```
+ options
  + A.
    ```
    SELECT p.name, p.mass, category_id
    FROM planets p
    WHERE p.mass = MAX(mass)
    GROUP BY category_id;
    ```
  + B.
    ```
    SELECT p.name, p.mass
    FROM planets p
    JOIN (SELECT category_id, MAX(mass) as max_mass
          FROM planets
          GROUP BY category_id
          ) c
    ON p.category_id = c.category_id
    AND p.mass = c.max_mass;
    ```
  + C.
    ```
    SELECT p.name, p.mass
    FROM planets p
    WHERE p.mass = (SELECT MAX(mass)
                      FROM planets
                      WHERE category_id <=> p.category_id
                   );
    ```
  + D.
    ```
    SELECT p.name, p.mass
    FROM planets p
    JOIN (SELECT category_id, MAX(mass) as max_mass
          FROM planets
          ) c
    ON p.category_id = c.category_id
    GROUP BY category_id
    HAVING p.mass = c.max_mass;
    ```
+ Answers: B, C

### 49.
Before the TiDB DMR version v6.4.0, the `DROP DATABASE` operation can be undone using the `FLASHBACK` command, provided it is executed before the GC safe point window is closed.

+ options
  + A. True
  + B. False
+ Answers: B

### 50. 
True or False. In TiDB's pessimistic locking mode, `AUTOCOMMIT` is disabled, and `tidb_enable_metadata_lock`= OFF, when session A performs DML operations on a table, session B can also perform DDL operations on the same table. 

+ options
  + A. True
  + B. False
+ Answers: A

### 51. 
What is the correct description of a Global Temporary Table? (Select all correct answers)

+ options
  + A. It is visible to all connected sessions.
  + B. It cannot have the same name as a permanent table.
  + C. Data will be automatically purged once the transaction is complete.
  + D. Data is only visible within the current transaction.
  + E. Table information can be queried from information_schema.tables.
+ Answers: A, B, C, D, E

### 52. 
You need to create a `PLACEMENT POLICY` that supports three replicas, with the data primarily stored in Seattle and remotely synced to Tokyo. Choose the correct statement.

```
tidb> SHOW PLACEMENT LABELS;
+--------+-------------------------+
| Key    | Values                  |
+--------+-------------------------+
| region | ["tokyo", "seattle"]    |
+--------+-------------------------+
```
+ options
  + A. `CREATE PLACEMENT POLICY P1 PRIMARY_CONSTRAINT="seattle" REGIONS="tokyo" FOLLOWERS=3;`
  + B. `CREATE PLACEMENT POLICY P1 PRIMARY_REGION="seattle" REGIONS="tokyo, seattle" FOLLOWERS=3;`
  + C. `CREATE PLACEMENT POLICY P1 PRIMARY_REGION="seattle" CONSTRAINTS="seattle, tokyo" FOLLOWERS=2;`
  + D. `CREATE PLACEMENT POLICY P1 PRIMARY_REGION="seattle" REGIONS="seattle, tokyo" FOLLOWERS=2;`
+ Answers: D

### 53. 
True or False. After enabling the CACHE feature for a table, no other DDL operations that change the structure of the table can be performed, except for NOCACHE.

+ options
  + A. True
  + B. False
+ Answers: A

### 54. 
Which of the following statements enabled the columnar replica feature for table `t1`?


+ options
  + A. ALTER TABLE t1 ENABLE TIFLASH REPLICA 1;
  + B. ALTER TABLE t1 SET HTAP REPLICA 1;
  + C. ALTER TABLE t1 ENABLE HTAP REPLICA 1;
  + D. ALTER TABLE t1 SET TIFLASH REPLICA 1;
+ Answers: D

### 55. 
True or False. When the SQL statements `REPLACE INTO` and `INSERT ... ON DUPLICATE KEY UPDATE` operate on a table with a Primary Key, their functions are the same.

+ options
  + A. True
  + B. False
+ Answers: B

### 56. 
True or False. Data lost after executing `TRUNCATE TABLE` and `DROP TABLE` can be salvaged by FLASHBACK operations, provided that it is done before the GC safe point

+ options
  + A. True
  + B. False
+ Answers: A

### 57. 
Which of the following transaction isolation levels does TiDB support? (Select two items)

+ options
  + A. Read Committed
  + B. Repeatable Read
  + C. Phantom Read
  + D. Serializable
+ Answers: A, B

### 58. 
Which of the following statements about indexes is a correct description?

+ options
  + A. It is advisable to have more secondary indexes in order to enhance query efficiency.
  + B. The more secondary indexes there are, the better, to enhance the efficiency of DML.
  + C. Create indexes cautiously, and only when necessary.
  + D. As long as the column has an index and is referenced in the WHERE clause, the query will be able to leverage the index to improve efficiency.
+ Answers: C

### 59. 
Which of the following statements about the advantages of Prepared Statements is NOT correct?

+ options
  + A. Parse a command once and execute it multiple times.
  + B. Help the CBO to determine the optimal execution plans.
  + C. Decrease CPU overhead.
  + D. Reduce client-server network traffic.
  + E. Mitigated SQL injection attacks.
+ Answers: B

### 60. 
True or False. Multiple SQL statements can be pre-compiled in a single prepared statement.

+ options
  + A. True
  + B. False
+ Answers: B