SELECT statement

INSERT statement

UPDATE statement

TRUNCATE statement

Operations A-D are not allowed

The result is 0.

The result is 1.

The result is 2.

An error is output.

The json and jsonb data types are available for storing JSON data.

Because the json type simply stores a copy of the input value, multiple JSON objects may contain the same key and value.

The jsonb type is much faster because it does not need to be reparsed each time it is processed.

The json type supports indexes.

The jsonpath type is provided to efficiently query the data that stores JSON.

It can replicate on a per-database basis.

It can replicate on a per-table basis.

All DDL commands are replicated.

It can be used to replicate between different major versions.

The tables to be replicated can be updated on the publisher side, but not on the subscriber side.

Execute 'DELETE * FROM test;'.

Execute 'DELETE ALL FROM test;'.

Execute 'DELETE FROM test;'.

Execute 'DROP ROWS FROM test;'.

You can also execute 'TRUNCATE test;', which is faster.

The id column of the dept table must have a primary key (PRIMARY KEY) or unique key (UNIQUE) constraint.

The dept_id column of the emp table must have a primary key (PRIMARY KEY) or unique key (UNIQUE) constraint.

If there are duplicates in the id column of the dept table, an error may occur.

If there are duplicates in the id column of the dept table, the result of update is unpredictable.

Unable to execute due to syntax error.

If the table specified with table_name does not exist, it is automatically created.

The column list specified with col1, col2 ... does not have to match the order of the columns in the actual table.

Any columns in the table that are not included in the column list col1, col2 ... will be filled with NULL.

If the number of data items in the value list specified with val1, val2 ... is larger than the number of columns in the column list specified with col1, col2 ..., then an error occurs.

If the number of data items in the value list specified with val1, val2 ... is less than the number of columns in the column list specified with col1, col2 ..., then NULL is assumed for those columns.

AT

IN

ON

USING

WITH

a
---
a

a
---
b

b
---
a

b
---
b

None of the above.

It is often executed with a savepoint name as a parameter, like SAVEPOINT name, but this parameter can be omitted.

The savepoint name, specified as in SAVEPOINT name, must be unique within a transaction. Reusing the same name will result in an error.

Executing ROLLBACK TO name will discard all updates made after the SAVEPOINT, while updates made before the savepoint are preserved.

Executing ROLLBACK will discard all updates made within the transaction, including those made before the SAVEPOINT.

Executing COMMIT will confirm all updates, both before and after the SAVEPOINT, and write them to the database.

Executing 'select dt1 + 10 from dt_sample;' will return the date that is 10 days after the date in dt1.

Executing 'select dt2 - dt1 from dt_sample;' will return the number of days elapsed between dt1 and dt2 as an integer.

Executing 'select ti1 + 100 from dt_sample;' will return the time that is 100 seconds after the time in ti1.

Executing 'select ts1 + 10 from dt_sample;' will return the date and time that is 10 days after the timestamp in ts1.

Executing 'select dt1 + ti1 from dt_sample;' will return a timestamp that combines the date from dt1 and the time from ti1.

The character_length function can be used to obtain the byte length of a string.

The substring function can retrieve a substring, such as the second to fifth characters of a string.

The lower function can be used to determine if a string is made up of all lowercase letters.

The trim function returns a string with whitespace removed from both ends of the string.

The replace function can replace characters in a string, for example, changing all a's to A's.

The value of count(*) is 6.

The value of count(val) is 6.

The value of sum(val) is 15.

The value of avg(val) is 2.5.

The value of max(val) is NULL.

To execute, you can use 'call sample_func(10)'.

To execute, you can use 'select sample_func(10)'.

When executed from a terminal, it displays 'Hello World!'.

sample_func(10) returns a value of 20.

When executed, an exception occurs and the command terminates abnormally.

There are no particular restrictions, and any user can execute it.

Only the owner of the function testfunc can execute it.

This function can be executed if the EXECUTE privilege for the function testfunc has been GRANTED.

It can be executed if the SELECT privilege on the table 'testa' has been GRANTED.

Both the EXECUTE privilege for the function testfunc and the SELECT privilege on the table 'testa' must be GRANTED.

Improving access performance by reducing the disk capacity required by the table.

Improving access performance by reducing the size of the index.

Improving access performance by performing a full scan within a partition instead of an index scan.

Reducing the processing time for operations such as bulk deletion of old data.

Speeding up the process when an UPDATE causes data to move between partitions.

ts1 is available only from the database to which it was connected at the time of creation and cannot be used from any other database in the database cluster.

To create a new object such as a table in ts1, you must have the appropriate privileges for ts1.

To access the tables created in ts1, you must have the appropriate privileges for ts1.

ts1 is dedicated to creating new objects only; you cannot move existing objects to ts1.

For an existing table, only an index can be created on ts1.

It displays the contents of the table 'bar' in the database 'foo'.

It displays the contents of the table 'bar' in the schema 'foo'.

It displays the contents of the table 'bar' owned by the user 'foo'.

Both the USAGE privilege on 'foo' and the SELECT privilege on 'bar' are required for execution.

If executed by the user 'foo', 'SELECT * FROM bar;' will always yield the same result.

If the same user A continues to execute 'select currval('testseq');', it will return 1.

If another user B connects to the database and executes 'select currval('testseq');', it will return 1.

It returned 2 when another user C connected to the database and executed 'select nextval('testseq');'. Immediately after this, if user A executes 'select currval('testseq');', it will return 2.

It returned 3 when another user D connected to the database and executed 'select nextval('testseq')' at exactly the same time as user C in option C. Immediately after this, if user C executes 'select currval('testseq');', it will return 2.

If another user E connects to the database and sequentially executes 'select setval('testseq', 10); select nextval('testseq');', the second SELECT statement 'nextval' will return 11.

Materialized views can be indexed, but views cannot be indexed.

Materialized views consume disk space depending on the amount of data, but views do not consume disk space even as data increases.

Executing the same SELECT statement on a view and a materialized view with the same definition always returns the same result.

When executing "SELECT * FROM view_name" on a view and a materialized view with the same definition, it is usually faster on the materialized view.

Some views can be updated by INSERT/UPDATE/DELETE depending on their definition, but INSERT/UPDATE/DELETE operations cannot be performed on materialized views.

A SELECT statement can be executed on a view in the same way as on a table.

Depending on the database version and the definition of the view, it may be possible to perform INSERT or UPDATE operations.

An index can be created on a column in a view.

If a table that serves as the basis for a view is dropped with DROP TABLE, the view becomes invalid.

When dropping a view with DROP VIEW, specifying the CASCADE option allows the table that the view is based on to be dropped as well.

Column a of table x

Column b of table x

Column c of table x

Column d of table y

Column e of table y

Column f of table y

create table test (primary key id integer, val varchar(50));

create table test (id integer primary key, val varchar(50));

create table test (id integer, val varchar(50), constraint id primary key);

create table test (id integer, val varchar(50), primary key (id));

create table test (id integer, val varchar(50), primary key = id);

The 'integer' type is a 4-byte integer.

The 'unsigned int' type can be used to handle non-negative integers.

The 'long int' type can be used to handle 8-byte integers.

The 'byte' type can be used to handle one-byte integers.

The 'numeric' type can handle decimal numbers with more than 40 digits, including the decimal point.

An error occurs because the SQL syntax is wrong.

Although it is recognized as a data deletion SQL command, nothing is deleted because there are no rows that match the WHERE clause.

A prompt asking whether to delete the data is displayed.

All rows in the table 'table_name' are deleted and the table becomes empty.

The table 'table_name' is dropped.

GROUP BY

HAVING

JOIN

ORDER BY

WHERE

select * from emp, dept join dept_id;

select * from emp e, dept d where e.dept_id = d.dept_id;

select * from emp natural join dept;

select * from emp join dept using (dept_id);

select * from emp e join dept d where e.dept_id = d.dept_id;

Table x does not exist.

You do not have SELECT privilege on table x.

Table x does not have a current_timestamp column.

Table x exists but contains no rows of data.

You should not specify a from clause when retrieving current_timestamp.

Ensure each transaction is completed as quickly as possible.

Once a lock is acquired, hold it for as long as possible.

Aim to execute as many transactions as possible simultaneously.

When locking multiple resources, maintain the same order of acquiring locks for each transaction.

Avoid acquiring unnecessary locks.

You can specify the transaction isolation level by using 'SET TRANSACTION ISOLATION LEVEL xxx' at the start of the transaction.

You can specify one of four transaction isolation levels: SERIALIZABLE, REPEATABLE READ, READ COMMITTED, or READ UNCOMMITTED.

The default transaction isolation level is specified in the configuration parameters; if not specified, REPEATABLE READ is the default.

REPEATABLE READ is an isolation level that prevents non-repeatable reads and also eliminates phantom reads.

READ UNCOMMITTED is an isolation level that allows reading of uncommitted data, but does not result in dirty reads.

You can use || to concatenate strings. For example, 'abc' || 'xyz' becomes 'abcxyz'.

You can use + to concatenate strings. For example, 'abc' + 'xyz' becomes 'abcxyz'.

LIKE, which is used for string comparison, is case-insensitive. Therefore, 'ABC' LIKE 'abc' is true.

In string comparison with LIKE, _ and % are wildcards. For example, 'abcde' LIKE 'a_c%' is true.

SIMILAR TO, which is used for comparison with regular expressions, supports wildcards _ and %, unlike POSIX regular expressions. For example, 'abcde' SIMILAR TO 'a_c%' is true.

character_length is a function that returns the length of a string in characters. For example, character_length('josé') returns 4.

byte_length is a function that returns the length of a string in bytes. For example, in a database with a UTF-8 character set, byte_length('josé') returns 5.

substring is a function that returns a substring. For example, substring('12345678' from 3 for 2) returns '34'.

replace is a function that replaces strings. For example, replace('12341234', '2', 'x') returns '1x341x34'.

trim is a function that removes whitespace from both ends of a string. For example, trim(' abc ') returns 'abc'.

You can use // to obtain the integer quotient by discarding the remainder in division. For example, 5 // 3 results in 1.

You can use % to find the remainder of the division. For example, 5 % 3 results in 2.

** can be used to calculate the power. For example, 5 ** 3 results in 125.

You can use || to determine the logical OR. For example, true || false is true.

You can use + to concatenate strings. For example, 'abc' + 'def' results in 'abcdef'.

Use the rounddown function to truncate a decimal part to an integer.

Use the trunc function to truncate the decimal part to an integer.

Use the roundup function to round up a decimal part to an integer.

Use the ceil function to round up a decimal part to an integer.

Use the round function to round the decimal part to an integer.

Use the int function to round a decimal part to an integer.

Both count(*) and count(x) return the number of rows in the sample_table.

The create function fails because a semicolon is required after declare on the second line.

PL/pgSQL is used to define a function.

Create a partitioned table using the CREATE PARTITIONED TABLE command.

A directory to be used as a tablespace is specified in the CREATE TABLESPACE command, but the directory must already exist and be empty.

SELECT * FROM bar; will result in an error as it fails to find the table foo.bar.

BEFORE statement -> BEFORE row -> AFTER statement -> AFTER row

Add a column to a view.

When a table has a FOREIGN KEY constraint

Name of the table

integer is a 4-byte integer that can safely contain up to 10 digits.

real and double precision are floating point real numbers, and double precision can perform more accurate operations.

In either method, the parent table is created using the usual CREATE TABLE parent... and no special options are needed to use partitions.

When creating a partition, the command is
CREATE TABLE partition1 PARTITION OF parent...
for declarative partitioning, and
CREATE TABLE partition1 () INHERITS (parent)
for inheritance, both using CREATE TABLE.

The command used to create it is in the format of
CREATE MATERIALIZED VIEW mview AS SELECT ...  FROM table1, table2...

Rather than executing the SELECT statement used in the AS clause during creation,
SELECT * FROM mview
is usually faster.

A PROCEDURE is created with the CREATE PROCEDURE command, and a FUNCTION is created with the CREATE FUNCTION command.

While PROCEDURE is defined in the SQL standard, FUNCTION is a PostgreSQL-specific extension.

The server machine's hardware clock is faulty.

The timed command is not executed on the server machine, so the time is not accurately maintained.

Reduce the disk space occupied by data.

Significantly improve the performance of queries.

Place the new database created by CREATE DATABASE in a tablespace different from the default one.

Both are created with the CREATE VIEW command.

If the view definitions are the same, a SELECT from either will always return the same result.

Materialized views require disk space according to the amount of data they contain, while regular views do not require disk space.

To modify a query that defines a view, the CREATE OR REPLACE VIEW command is used for a regular view, whereas a special command called REFRESH MATERIALIZED VIEW is used for a materialized view. 

DELETE FROM xxx, yyy WHERE xxx.id = yyy.id;

DELETE FROM xxx JOIN yyy USING xxx.id = yyy.id;

DELETE FROM xxx WHERE id IN (SELECT id FROM yyy);

DELETE FROM xxx WHERE EXISTS (SELECT id FROM yyy);