DBMS Restart

Question 1

Which way do joins nest

Answer 1

left to right

Question 2

Name a data generating function and explain

Answer 2

generate_series(), returns a number of rows aka table functions. Can use FROM on this too

Question 3

WITH ORDINALITY

Answer 3

meaning that it will be output as a sequence numbering its values.

ex. SELECT * FROM generate_series() with ordinality.

Question 4

Type Casting in Postgres

Answer 4

CAST() or use :: operator

ex. CAST(sal as int)

Question 5

Bit Strings

Answer 5

Sequences of 0s and 1s. Bit String Data Types in Postgres are:
- bit(n)
- bit varying(n)

Question 6

bit(n)

Answer 6

With string length n being fixed at definition

Question 7

bit_varying(n)

Answer 7

String length n is the maximum value supported

Question 8

Bit String Operators supported

Answer 8

Concat, Bitwise And, Bitwise Or, ~, #, Bitwise shifts

Question 9

CREATE SEQUENCE

Answer 9

creates a sequence in columns in order

Question 10

ENUMerated types

Answer 10

Define our own types ex. ENUM(‘red’,’blue’,’green’)

Case sensitive + Order is important

Question 11

BLOB

Answer 11

Binary Large Object, Basic binary Datatype
ByteA supports binary strings in bits.

This can be a file like a WAV, PNG, EXE etc.

Basically just a file constructed of binary

Question 12

TOAST

Answer 12

The Oversized attribute storage techniques supported by pgsql automatically stores values bigger than a single DB page into a secondary storage region. Limits size of any column to 1gb.

Question 13

What risk comes with reading direct from server process

Answer 13

Security risks as scripts assume user is a superuser.

Question 14

Array[] Type Constructor

Answer 14

Popular in GUI elements, computations and visualizations. Support SQL must define, update, manage and query tables with arrays.

Question 15

How can you map arrays to tuples

Answer 15

using the ‘unnest(ARRAY[])’ command.

You can also map tuples to arrays.

Question 16

RANGE Type Constructor

Answer 16

Datatypes representing a spread of values by listing the two endpoints and their bounds (inclusive or exclusive)

Can be int, numeric, date, timestamp etc.

[] is used for inclusive
() used for exclusive

Question 17

What are RANGEs used for

Answer 17

Schedules for job arrivals, run predicates to determine intersection, intersecting data streams, coalescing of data.

Question 18

Range encoding

Answer 18

SELECT ‘[2,8]’ :: int 4 range;

Question 19

Create Range

Answer 19

SELECT numrange (1,12);

Question 20

Multirange encoding

Answer 20

SELECT ‘{[18,20),[25,65]}’ :: int4multirange;

Question 21

Range predicates

Answer 21

Contained, Contains, Follow/Precede

Question 22

Range Predicate: Overlap

Answer 22

Overlap of ranges is true when every range shares a common interval together. Overlap ex. dates may occur in a number of ways.

Question 23

Range Operation: Strictly Before

Answer 23

Write a query to check if range precedes another (without overlap)

Question 24

Reading and writing data in JSON

Answer 24

JSON is a data-interchanged format, from DBMS to another DBMS or client process. It is Language independent. Data building syntax easy to read and apply.

Question 25

What is JSON

Answer 25

Semi-structed datatype - any 2 docs can have similar but not necessarily must have identical structures.

Built on objects which are a composition of key and value pairs. Value can be any instances of generics such as number, string, bool, array, object or null.

Question 26

Generate JSON from tables

Answer 26

SELECT row_to_json(r) FROM (SELECT …) to ‘file.json’;

Question 27

JSON function

Answer 27

-> output value
-» outputs datatype
#> output value in key
@> output true/false (query)
?’x’ output T/F if exists
|| concat
-‘x’ output row without ‘x’