I saw a new feature Invisible Columns in MariaDB 10.3.x. What are practical use cases for DBA and web developer? When to use this feature? > Columns can be given an INVISIBLE attribute in a CREATE TABLE or `ALTER > TABLE` statement. These columns will then not be listed in the results > of a SELECT * statement, nor do they need to be assigned a value in an > INSERT statement, unless INSERT explicitly mentions them by name. > > Since SELECT * does not return the invisible columns, new tables or > views created in this manner will have no trace of the invisible > columns. If specifically referenced in the SELECT statement, the > columns will be brought into the view/new table, but the INVISIBLE > attribute will not. > > Invisible columns can be declared as NOT NULL, but then require a > DEFAULT value

From the PostgreSQL wiki, > MERGE is typically used to merge two tables, and was introduced in the 2003 SQL standard. The REPLACE statement (a MySQL extension) or *UPSERT* sequence attempts an UPDATE, or on failure, INSERT. This is similar to UPDATE, then for unmatched rows, INSERT. Whether concurrent access allows modifications which could cause row loss is implementation independent. Further PostgreSQL's [INSERT ... ON CONFLICT DO NOTHING/UPDATE](https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.5#INSERT_..._ON_CONFLICT_DO_NOTHING.2FUPDATE_.28.22UPSERT.22.29) is marketed as *UPSERT* and was added in 9.5 What then is MERGE? And how does it fit into the mix?

One definition of "entity" in the Wikipedia page for entity-relationship model as follows: > An entity is a thing that exists either physically or logically. An entity may be a physical object such as a house or a car (they exist physically), an event such as a house sale or a car service, or a concept such as a customer transaction or order (they exist logically — as a concept). I don't really understand what concept means in this definition and why a transaction or order is listed as a concept. A customer transaction or an order exists on paper (as bills) so can I say they exist physically? Besides, the event is also not a physical object so it should be a concept?

I'm currently in a battle with Entity Framework over trying to make a self-referencing relationship without having to add additional columns/properties. I have confirmed the foreign key I envision is possible in SQL Server, but it is difficult to research possible solutions because I keep finding generic single-column foreign keys. Is there some widely used term specific to this form of self-referencing, composite foreign key? create table LookupValues ( LookupName nvarchar(255) not null, ExternalRefCode nvarchar(255) default N'' not null, ParentExternalRefCode nvarchar(255), constraint PK_LookupValues primary key (LookupName, ExternalRefCode) ) alter table LookupValues add constraint CompositeFK foreign key (LookupName, ParentExternalRefCode) references LookupValues The idea behind it is that although there are multiple values for LookupName in this table, there will never be cross-value relationships in the parent/child relationships. e.g. a child from LookupName: A must belong to a parent from LookupName: A. I've tried searches like "self-referencing composite foreign key", "self-referencing foreign key using part of primary key", and "composite foreign key with common column in primary key", etc.

[The docs](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-ver16#comparing-coalesce-and-isnull) insist that ISNULL is a function, but COALESCE is not. Specifically, they say > The ISNULL function and the COALESCE expression If I put on my Lisp hat, I can think of COALESCE as a macro and everything makes sense. However, I've never needed such thinking in T-SQL. So, I must ask. In T-SQL: 1. What is the definition of a function? 2. What is the definition of an expression? 3. How can I tell the difference between a function and an expression?

From the official documentation (emphasis mine): [Chapter 22. Managing Databases](https://www.postgresql.org/docs/9.6/static/managing-databases.html) > **Every instance of a running PostgreSQL server manages one or more databases.** Databases are therefore the topmost hierarchical level for organizing SQL objects ("database objects"). Chapter 22. Managing Databases -> 22.1 Overview > More accurately, a database is a collection of schemas and the schemas contain the tables, functions, etc. So the full hierarchy is: server, database, schema, table (or some other kind of object, such as a function). [Chapter 18. Server Setup and Operation -> 18.2. Creating a Database Cluster](https://www.postgresql.org/docs/9.6/static/creating-cluster.html) > **A database cluster is a collection of databases that is managed by a single instance of a running database server.** I don't quite understand the difference between the PostgreSQL concepts of a cluster and a server, but it seems to me that they are the same thing?

I work with a system which uses a lot of tables whose primary keys include 1. a foreign key to another table and 2. a column indicating the date that record became/becomes "active". Example tables: employee (primary key is employee_id) employee_id INT hire_date DATE employee_salary (primary key is employee_id, start_date) employee_id INT (foreign keyed to employee.employee_id) start_date DATE salary TINYINT When viewing an employee in our application UI, we would display all the information from employee and whichever employee_salary record had a start date most recent before the date we are interested in. In my working group, we would refer to employee as a "base" table and employee_salary as a "repeating" table, since we consider the salary to merely be an aspect of the "base" entity, the employee. We also say that the employee_salary table uses an "effective date". But these don't seem to be terms in widespread use. Are there standard terms for 1. tables that represent an entity, and 2. tables that represent a changing aspect of one of those entities?

I know what a statement is and I know what a batch is, but what is a "statement batch"? T-SQL's documentation uses the term in [two](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/throw-transact-sql?view=sql-server-ver16#remarks) [places](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-oacreate-transact-sql?view=sql-server-ver16#remarks) that I've found. > If a TRY...CATCH construct is not available, the statement batch is terminated. > Any error that occurs in a THROW statement causes the statement batch to be terminated. > The created OLE object is automatically destroyed at the end of the Transact-SQL statement batch.

While learning about "data layout", I came across the term *"materialize"*, which does not have a clear definition and explanation. What is materialize and what does materialize do? This question is not related to materialized views, and is not specific to any particular DBMS. ### Context The source that I am learning from: [Youtube Video from Prof. Dr. Jens Dittrich : Mapping Relations to Devices](https://www.youtube.com/watch?v=K1VVIbvIo9U&t=332s) . **At timepoint 9:24** we have this term *materialize*. He said that is a process from physical page to storage devices. And an additional question at this time point: Since physical pages should also belong to main memory, if we have a *main-memory database*, so the data layout only exists in main memory, will there be a *materialize* process?

This just came up in a question on Reddit, and I'm wondering * What is a PARTITIONED OUTER JOIN in Oracle? (definition) * What does a simple example look like? (use) * How would you write it in terms of PostgreSQL or Standard SQL that otherwise lacks PARTITIONED OUTER JOIN? (equivalence)

I have gone through the Wikipedia definition several times: > A database engine (or "storage engine") is the underlying software component that a database management system (DBMS) uses to **create, read, update and delete** (CRUD) data from a database. What I don't understand is what is left to do, isn't CRUD all that the databases do? **If the database engine performs these functions, what does the rest of the database do?**

I usually describe intermediate tables as "join tables", but I recently heard them referred to as "bridge tables". Is there a difference between the two terms? I've researched the topic but have yet to find a satisfactory answer. It seems like "join table" might be a generic term used to refer to either a "bridge table" or a "fact table". Is there any truth to that?

In this Azure SQL intro page , they state: >A consistent SQL experience from edge to cloud >
>Get a unified experience across your entire SQL portfolio and a full range of deployment options from edge to cloud. What does edge mean here?

I came across the term Transaction Path Analysis which I am not familiar with. A Google search resulted in only a few results, most of which seem to assume everyone knows what it means. From these my understanding is that it is about tracking what columns are used by queries to access data, in preparation for deciding indexes. This seems like common sense, but I'm surprised I can't find more specifics on it. Is there a concise definition for transaction path analysis, a process for completing one, or any alternative more commonly used phrases that mean the same thing?

In the book *Getting Started with SQL*, Thomas Nield talks about a technique he calls the ***zero/null case trick***: > There is a simple but powerful tool that can apply different filtering > conditions to different aggregations. We can create separate total > counts when a tornado was present versus not present in two separate > columns: > > SELECT year, month, > SUM(CASE WHEN tornado = 1 THEN precipitation ELSE 0 END) as tornado_precipitation, > SUM(CASE WHEN tornado = 0 THEN precipitation ELSE 0 END) as non_tornado_precipitation > FROM station_data > WHERE year >= 1990 > GROUP BY year, month > > What we have effectively done is get rid of the WHERE conditions when > tornado = 1 or tornado = 0, and then move those conditions to CASE > expressions inside the SUM() functions. If the condition is met, the > precipitation value is added to the sum. If not, a 0 is added, having > no effect. We do these for both columns, on for when a tornado was > present and one for when a tornado was not present respectively. > > You can make the CASE expression have as many condition/value pairs as > you need, giving you the ability to do highly particular interceptions > of values with your aggregations. You can also use this trick to > emulate crosstabs and pivot tables, expressing aggregations into > separate columns rather than in rows. A common example of this is > doing current year/previous year analysis, as you can express separate > years with different columns. ---------------- As a novice, that technique seems like it could come in really handy for summarizing data. I want to look up that technique online to get more information. The author of that book calls the technique the "zero/null case trick". But when I google that term, I don't get many results. Question: Is there a generally accepted name for that technique? (that would yield more results when searching online)

I am not a specialist in databases, and I only need to clarify a terminological question that I have. Assume we have a table containing the field F. F gets its values from the set S, a collection of values of a certain type. (Let's imagine that the elements of S are characters.) Is there a common way to refer to S as a whole? Something like the **alphabet of F** or the **inventory of F**.

The three-level ANSI SPARC Database Architecture suggests three data abstraction levels, namely, *external*, *conceptual*, and *internal* levels. If I understand correctly, the *external* level represents the users' views, the *conceptual* level is the conceptual diagram (ER model), and the *internal* level is the relational model. My problem is that I found in the literature that besides those, there is a fourth level (the lowest one), which is the *physical* level. I want to understand what is it concretely? Is it at this point that we define the type of index, access paths, and things related to physical access to data?

I am currently familiarizing myself with SQL Server security. During the process, I came across the following text passage. >Windows-level principals are generally domain and local server user accounts that authenticate to SQL Server using Windows authentication. *Source: Pro SQL Server 2008 Administration.* What does the author mean by "domain and local server user accounts", specifically "server user accounts"? Does it mean the user account for the operating system on which, for example, SQL Server Engine is running?

What is the difference between a connection and a session and how they are related?

I am trying to learn the basics of databases, but I am finding the terminology especially impenetrable compared to other things I have learned. Most of the database tutorials and documentation I have looked at explain everything in terms of other database terminology. Most examples only show code but not actual output, so I am often unclear as to what the expected output is. I am usually even less clear on what the intended use case is. For example, take the term "scalar function" which I recently come across. It is defined here similarly to other definitions I found. https://raima.com/database-terminology/ : > Scalar Function – Either a built-in SQL function or a user-defined > function that returns a single value computed only from the values of > any required arguments at the time the function is called. Why is that special? How is that different from just a function that outputs one value? Why is the fact that the arguments are "required arguments" a noteworthy distinction? Why, in practical terms, would it be important to specify that a scalar function is to be used? I would appreciate both an answer to this specific question and references to database (preferably Postgres) learning materials that make a point to explain each technical term in the least technical way possible.