SQL Basics with SQLite and PostgreSQL (Questions) | Practice Test / Quiz / MCQs

Home > Databases > Quizzes > SQL Basics with SQLite and PostgreSQL (Questions)

SQL Basics with SQLite and PostgreSQL (Questions)

Fast practice, instant feedback. Timer auto-submits when time’s up.

Avg score: 0% • Most missed: “Name 10 valid operators in SQL.”

SQLite is an Embedded DBMS. This means that it is a Serverless DBMS with the ability to run within your applications.

PostgreSQL DBMS implements a Client-Server Model and requires a Database Server to set up and run over a network.

SQLite vs PostgreSQL: SQLite is very fast, thanks to its minimal design and simple operations. SQLite is for you if all you require is fast read operations. PostgreSQL is more suitable for complex operations.

SQL Basics with SQLite and PostgreSQL (Questions)

Time left 00:00

25 Questions

1. 'What is crow's foot notation?

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
A window function call always contains an OVER clause directly following the window function's name and argument(s). This is what syntactically distinguishes it from a normal function or non-window aggregate.
The OVER clause determines exactly how the rows of the query are split up for processing by the window function. The PARTITION BY clause within OVER divides the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s). For each row, the window function is computed across the rows that fall into the same partition as the current row. The rows considered by a window function are those of the virtual table produced by the query's FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any. For example, a row removed because it does not meet the WHERE condition is not seen by any window function. A query can contain multiple window functions that slice up the data in different ways using different OVER clauses, but they all act on the same collection of rows defined by this virtual table. ORDER BY can be omitted if the ordering of rows is not important. It is also possible to omit PARTITION BY, in which case there is a single partition containing all rows. Window functions are permitted only in the SELECT list and the ORDER BY clause of the query.Crow's foot diagrams represent entities as boxes, and relationships as lines between the boxes. Different shapes at the ends of these lines represent the relative cardinality of the relationship. Three symbols are used to represent cardinality: the ring represents ''zero''; the dash represents ''one''; the crow's foot represents ''many'' or ''infinite''. These symbols are used in pairs to represent the four types of cardinality that an entity may have in a relationship: - ring and dash → minimum zero, maximum one (optional); - dash and dash → minimum one, maximum one (mandatory); - ring and crow's foot → minimum zero, maximum many (optional); - dash and crow's foot → minimum one, maximum many (mandatory)'SELECT COUNT(*) FROM app WHERE price = 0;Attempting to insert into a table1, a value that does not exist in the table that table1 references will cause a foreign key constraint error: ERROR: insert or update on table ''weather'' violates foreign key constraint ''weather_city_fkey'' DETAIL: Key (city)=(Berkeley) is not present in table ''cities''.'

2. What is the syntax of a window function?

SELECT COUNT(*) FROM app WHERE price = 0;Crow's foot diagrams represent entities as boxes, and relationships as lines between the boxes. Different shapes at the ends of these lines represent the relative cardinality of the relationship. Three symbols are used to represent cardinality: the ring represents ''zero''; the dash represents ''one''; the crow's foot represents ''many'' or ''infinite''. These symbols are used in pairs to represent the four types of cardinality that an entity may have in a relationship: - ring and dash → minimum zero, maximum one (optional); - dash and dash → minimum one, maximum one (mandatory); - ring and crow's foot → minimum zero, maximum many (optional); - dash and crow's foot → minimum one, maximum many (mandatory)'SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
A window function call always contains an OVER clause directly following the window function's name and argument(s). This is what syntactically distinguishes it from a normal function or non-window aggregate.
The OVER clause determines exactly how the rows of the query are split up for processing by the window function. The PARTITION BY clause within OVER divides the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s). For each row, the window function is computed across the rows that fall into the same partition as the current row. The rows considered by a window function are those of the virtual table produced by the query's FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any. For example, a row removed because it does not meet the WHERE condition is not seen by any window function. A query can contain multiple window functions that slice up the data in different ways using different OVER clauses, but they all act on the same collection of rows defined by this virtual table. ORDER BY can be omitted if the ordering of rows is not important. It is also possible to omit PARTITION BY, in which case there is a single partition containing all rows. Window functions are permitted only in the SELECT list and the ORDER BY clause of the query.Attempting to insert into a table1, a value that does not exist in the table that table1 references will cause a foreign key constraint error: ERROR: insert or update on table ''weather'' violates foreign key constraint ''weather_city_fkey'' DETAIL: Key (city)=(Berkeley) is not present in table ''cities''.'

3. Find the number of records in the app table that have a price of 0.

Attempting to insert into a table1, a value that does not exist in the table that table1 references will cause a foreign key constraint error: ERROR: insert or update on table ''weather'' violates foreign key constraint ''weather_city_fkey'' DETAIL: Key (city)=(Berkeley) is not present in table ''cities''.'SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
A window function call always contains an OVER clause directly following the window function's name and argument(s). This is what syntactically distinguishes it from a normal function or non-window aggregate.
The OVER clause determines exactly how the rows of the query are split up for processing by the window function. The PARTITION BY clause within OVER divides the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s). For each row, the window function is computed across the rows that fall into the same partition as the current row. The rows considered by a window function are those of the virtual table produced by the query's FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any. For example, a row removed because it does not meet the WHERE condition is not seen by any window function. A query can contain multiple window functions that slice up the data in different ways using different OVER clauses, but they all act on the same collection of rows defined by this virtual table. ORDER BY can be omitted if the ordering of rows is not important. It is also possible to omit PARTITION BY, in which case there is a single partition containing all rows. Window functions are permitted only in the SELECT list and the ORDER BY clause of the query.Crow's foot diagrams represent entities as boxes, and relationships as lines between the boxes. Different shapes at the ends of these lines represent the relative cardinality of the relationship. Three symbols are used to represent cardinality: the ring represents ''zero''; the dash represents ''one''; the crow's foot represents ''many'' or ''infinite''. These symbols are used in pairs to represent the four types of cardinality that an entity may have in a relationship: - ring and dash → minimum zero, maximum one (optional); - dash and dash → minimum one, maximum one (mandatory); - ring and crow's foot → minimum zero, maximum many (optional); - dash and crow's foot → minimum one, maximum many (mandatory)'SELECT COUNT(*) FROM app WHERE price = 0;

4. 'In Postgres, what happens when you attempt to violate the referential integrity of a table?

Attempting to insert into a table1, a value that does not exist in the table that table1 references will cause a foreign key constraint error: ERROR: insert or update on table ''weather'' violates foreign key constraint ''weather_city_fkey'' DETAIL: Key (city)=(Berkeley) is not present in table ''cities''.'SELECT COUNT(*) FROM app WHERE price = 0;SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
A window function call always contains an OVER clause directly following the window function's name and argument(s). This is what syntactically distinguishes it from a normal function or non-window aggregate.
The OVER clause determines exactly how the rows of the query are split up for processing by the window function. The PARTITION BY clause within OVER divides the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s). For each row, the window function is computed across the rows that fall into the same partition as the current row. The rows considered by a window function are those of the virtual table produced by the query's FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any. For example, a row removed because it does not meet the WHERE condition is not seen by any window function. A query can contain multiple window functions that slice up the data in different ways using different OVER clauses, but they all act on the same collection of rows defined by this virtual table. ORDER BY can be omitted if the ordering of rows is not important. It is also possible to omit PARTITION BY, in which case there is a single partition containing all rows. Window functions are permitted only in the SELECT list and the ORDER BY clause of the query.Crow's foot diagrams represent entities as boxes, and relationships as lines between the boxes. Different shapes at the ends of these lines represent the relative cardinality of the relationship. Three symbols are used to represent cardinality: the ring represents ''zero''; the dash represents ''one''; the crow's foot represents ''many'' or ''infinite''. These symbols are used in pairs to represent the four types of cardinality that an entity may have in a relationship: - ring and dash → minimum zero, maximum one (optional); - dash and dash → minimum one, maximum one (mandatory); - ring and crow's foot → minimum zero, maximum many (optional); - dash and crow's foot → minimum one, maximum many (mandatory)'

5. 'How are words in a column name separated?

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
A window function call always contains an OVER clause directly following the window function's name and argument(s). This is what syntactically distinguishes it from a normal function or non-window aggregate.
The OVER clause determines exactly how the rows of the query are split up for processing by the window function. The PARTITION BY clause within OVER divides the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s). For each row, the window function is computed across the rows that fall into the same partition as the current row. The rows considered by a window function are those of the virtual table produced by the query's FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any. For example, a row removed because it does not meet the WHERE condition is not seen by any window function. A query can contain multiple window functions that slice up the data in different ways using different OVER clauses, but they all act on the same collection of rows defined by this virtual table. ORDER BY can be omitted if the ordering of rows is not important. It is also possible to omit PARTITION BY, in which case there is a single partition containing all rows. Window functions are permitted only in the SELECT list and the ORDER BY clause of the query.With underscores rather than spaces, ie ''snake_case''. It's possible to use spaces if one uses brackets, but it's a very bad idea.'Crow's foot diagrams represent entities as boxes, and relationships as lines between the boxes. Different shapes at the ends of these lines represent the relative cardinality of the relationship. Three symbols are used to represent cardinality: the ring represents ''zero''; the dash represents ''one''; the crow's foot represents ''many'' or ''infinite''. These symbols are used in pairs to represent the four types of cardinality that an entity may have in a relationship: - ring and dash → minimum zero, maximum one (optional); - dash and dash → minimum one, maximum one (mandatory); - ring and crow's foot → minimum zero, maximum many (optional); - dash and crow's foot → minimum one, maximum many (mandatory)'SELECT COUNT(*) FROM app WHERE price = 0;

6. What is the syntax for a LEFT JOIN?

Crow's foot diagrams represent entities as boxes, and relationships as lines between the boxes. Different shapes at the ends of these lines represent the relative cardinality of the relationship. Three symbols are used to represent cardinality: the ring represents ''zero''; the dash represents ''one''; the crow's foot represents ''many'' or ''infinite''. These symbols are used in pairs to represent the four types of cardinality that an entity may have in a relationship: - ring and dash → minimum zero, maximum one (optional); - dash and dash → minimum one, maximum one (mandatory); - ring and crow's foot → minimum zero, maximum many (optional); - dash and crow's foot → minimum one, maximum many (mandatory)'SELECT COUNT(*) FROM app WHERE price = 0;SELECT column_name FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
A window function call always contains an OVER clause directly following the window function's name and argument(s). This is what syntactically distinguishes it from a normal function or non-window aggregate.
The OVER clause determines exactly how the rows of the query are split up for processing by the window function. The PARTITION BY clause within OVER divides the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s). For each row, the window function is computed across the rows that fall into the same partition as the current row. The rows considered by a window function are those of the virtual table produced by the query's FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any. For example, a row removed because it does not meet the WHERE condition is not seen by any window function. A query can contain multiple window functions that slice up the data in different ways using different OVER clauses, but they all act on the same collection of rows defined by this virtual table. ORDER BY can be omitted if the ordering of rows is not important. It is also possible to omit PARTITION BY, in which case there is a single partition containing all rows. Window functions are permitted only in the SELECT list and the ORDER BY clause of the query.

7. What is the syntax for the ORDER BY clause?

Crow's foot diagrams represent entities as boxes, and relationships as lines between the boxes. Different shapes at the ends of these lines represent the relative cardinality of the relationship. Three symbols are used to represent cardinality: the ring represents ''zero''; the dash represents ''one''; the crow's foot represents ''many'' or ''infinite''. These symbols are used in pairs to represent the four types of cardinality that an entity may have in a relationship: - ring and dash → minimum zero, maximum one (optional); - dash and dash → minimum one, maximum one (mandatory); - ring and crow's foot → minimum zero, maximum many (optional); - dash and crow's foot → minimum one, maximum many (mandatory)'SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
A window function call always contains an OVER clause directly following the window function's name and argument(s). This is what syntactically distinguishes it from a normal function or non-window aggregate.
The OVER clause determines exactly how the rows of the query are split up for processing by the window function. The PARTITION BY clause within OVER divides the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s). For each row, the window function is computed across the rows that fall into the same partition as the current row. The rows considered by a window function are those of the virtual table produced by the query's FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any. For example, a row removed because it does not meet the WHERE condition is not seen by any window function. A query can contain multiple window functions that slice up the data in different ways using different OVER clauses, but they all act on the same collection of rows defined by this virtual table. ORDER BY can be omitted if the ordering of rows is not important. It is also possible to omit PARTITION BY, in which case there is a single partition containing all rows. Window functions are permitted only in the SELECT list and the ORDER BY clause of the query.SELECT * FROM table1 ORDER BY column1 DESC;SELECT COUNT(*) FROM app WHERE price = 0;

8. What is a transaction?

A transaction bundles multiple steps into a single, all-or-nothing operation. The intermediate states between the steps are not visible to other concurrent transactions, and if some failure occurs that prevents the transaction from completing, then none of the steps affect the database at all. For example, consider a bank database that contains balances for various customer accounts, as well as total deposit balances for branches. Suppose that we want to record a payment of $100.00 from Alice's account to Bob's account.There are several separate updates involved to accomplish this operation. Our bank's officers will want to be assured that either all these updates happen, or none of them happen. When multiple transactions are running concurrently, each one should not be able to see the incomplete changes made by others. A transaction is said to be atomic: from the point of view of other transactions, it either happens completely or not at all.SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
A window function call always contains an OVER clause directly following the window function's name and argument(s). This is what syntactically distinguishes it from a normal function or non-window aggregate.
The OVER clause determines exactly how the rows of the query are split up for processing by the window function. The PARTITION BY clause within OVER divides the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s). For each row, the window function is computed across the rows that fall into the same partition as the current row. The rows considered by a window function are those of the virtual table produced by the query's FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any. For example, a row removed because it does not meet the WHERE condition is not seen by any window function. A query can contain multiple window functions that slice up the data in different ways using different OVER clauses, but they all act on the same collection of rows defined by this virtual table. ORDER BY can be omitted if the ordering of rows is not important. It is also possible to omit PARTITION BY, in which case there is a single partition containing all rows. Window functions are permitted only in the SELECT list and the ORDER BY clause of the query.Crow's foot diagrams represent entities as boxes, and relationships as lines between the boxes. Different shapes at the ends of these lines represent the relative cardinality of the relationship. Three symbols are used to represent cardinality: the ring represents ''zero''; the dash represents ''one''; the crow's foot represents ''many'' or ''infinite''. These symbols are used in pairs to represent the four types of cardinality that an entity may have in a relationship: - ring and dash → minimum zero, maximum one (optional); - dash and dash → minimum one, maximum one (mandatory); - ring and crow's foot → minimum zero, maximum many (optional); - dash and crow's foot → minimum one, maximum many (mandatory)'SELECT COUNT(*) FROM app WHERE price = 0;

9. What does the _ character do?

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
A window function call always contains an OVER clause directly following the window function's name and argument(s). This is what syntactically distinguishes it from a normal function or non-window aggregate.
The OVER clause determines exactly how the rows of the query are split up for processing by the window function. The PARTITION BY clause within OVER divides the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s). For each row, the window function is computed across the rows that fall into the same partition as the current row. The rows considered by a window function are those of the virtual table produced by the query's FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any. For example, a row removed because it does not meet the WHERE condition is not seen by any window function. A query can contain multiple window functions that slice up the data in different ways using different OVER clauses, but they all act on the same collection of rows defined by this virtual table. ORDER BY can be omitted if the ordering of rows is not important. It is also possible to omit PARTITION BY, in which case there is a single partition containing all rows. Window functions are permitted only in the SELECT list and the ORDER BY clause of the query.SELECT COUNT(*) FROM app WHERE price = 0;The _ wildcard can be used in a LIKE operator pattern to match any single unspecified character.Crow's foot diagrams represent entities as boxes, and relationships as lines between the boxes. Different shapes at the ends of these lines represent the relative cardinality of the relationship. Three symbols are used to represent cardinality: the ring represents ''zero''; the dash represents ''one''; the crow's foot represents ''many'' or ''infinite''. These symbols are used in pairs to represent the four types of cardinality that an entity may have in a relationship: - ring and dash → minimum zero, maximum one (optional); - dash and dash → minimum one, maximum one (mandatory); - ring and crow's foot → minimum zero, maximum many (optional); - dash and crow's foot → minimum one, maximum many (mandatory)'

10. What are attributes of conceptual, logical, and physical models?

Crow's foot diagrams represent entities as boxes, and relationships as lines between the boxes. Different shapes at the ends of these lines represent the relative cardinality of the relationship. Three symbols are used to represent cardinality: the ring represents ''zero''; the dash represents ''one''; the crow's foot represents ''many'' or ''infinite''. These symbols are used in pairs to represent the four types of cardinality that an entity may have in a relationship: - ring and dash → minimum zero, maximum one (optional); - dash and dash → minimum one, maximum one (mandatory); - ring and crow's foot → minimum zero, maximum many (optional); - dash and crow's foot → minimum one, maximum many (mandatory)'SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
A window function call always contains an OVER clause directly following the window function's name and argument(s). This is what syntactically distinguishes it from a normal function or non-window aggregate.
The OVER clause determines exactly how the rows of the query are split up for processing by the window function. The PARTITION BY clause within OVER divides the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s). For each row, the window function is computed across the rows that fall into the same partition as the current row. The rows considered by a window function are those of the virtual table produced by the query's FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any. For example, a row removed because it does not meet the WHERE condition is not seen by any window function. A query can contain multiple window functions that slice up the data in different ways using different OVER clauses, but they all act on the same collection of rows defined by this virtual table. ORDER BY can be omitted if the ordering of rows is not important. It is also possible to omit PARTITION BY, in which case there is a single partition containing all rows. Window functions are permitted only in the SELECT list and the ORDER BY clause of the query.SELECT COUNT(*) FROM app WHERE price = 0;Conceptual Data Model (CDM): Includes high-level data constructs. Non-technical names. Uses general high-level data constructs. Logical Data Model (LDM): Includes entities (tables), attributes (columns/fields) and relationships (keys). Uses business names for entities & attributes. Is independent of technology (platform, DBMS). Physical Data Model (PDM): Includes tables, columns, keys, data types, validation rules, database triggers, stored procedures, domains, and access constraints. Uses more defined and less generic specific names for tables and columns, such as abbreviated column names, limited by the database management system (DBMS) and any company defined standards. Includes primary keys and indices for fast data access.

11. In SQLite, how many actual datatypes are there?

Crow's foot diagrams represent entities as boxes, and relationships as lines between the boxes. Different shapes at the ends of these lines represent the relative cardinality of the relationship. Three symbols are used to represent cardinality: the ring represents ''zero''; the dash represents ''one''; the crow's foot represents ''many'' or ''infinite''. These symbols are used in pairs to represent the four types of cardinality that an entity may have in a relationship: - ring and dash → minimum zero, maximum one (optional); - dash and dash → minimum one, maximum one (mandatory); - ring and crow's foot → minimum zero, maximum many (optional); - dash and crow's foot → minimum one, maximum many (mandatory)'SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
A window function call always contains an OVER clause directly following the window function's name and argument(s). This is what syntactically distinguishes it from a normal function or non-window aggregate.
The OVER clause determines exactly how the rows of the query are split up for processing by the window function. The PARTITION BY clause within OVER divides the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s). For each row, the window function is computed across the rows that fall into the same partition as the current row. The rows considered by a window function are those of the virtual table produced by the query's FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any. For example, a row removed because it does not meet the WHERE condition is not seen by any window function. A query can contain multiple window functions that slice up the data in different ways using different OVER clauses, but they all act on the same collection of rows defined by this virtual table. ORDER BY can be omitted if the ordering of rows is not important. It is also possible to omit PARTITION BY, in which case there is a single partition containing all rows. Window functions are permitted only in the SELECT list and the ORDER BY clause of the query.Only four. TEXT, INTEGER, REAL, and BLOB. For convenience, SQLite allows the programmer to use other common datatypes outside of the four mentioned above, e.g. INT, but these other datatypes are lumped into one of the four existing datatypes recognized by SQLite.SELECT COUNT(*) FROM app WHERE price = 0;

12. What is cardinality?

Crow's foot diagrams represent entities as boxes, and relationships as lines between the boxes. Different shapes at the ends of these lines represent the relative cardinality of the relationship. Three symbols are used to represent cardinality: the ring represents ''zero''; the dash represents ''one''; the crow's foot represents ''many'' or ''infinite''. These symbols are used in pairs to represent the four types of cardinality that an entity may have in a relationship: - ring and dash → minimum zero, maximum one (optional); - dash and dash → minimum one, maximum one (mandatory); - ring and crow's foot → minimum zero, maximum many (optional); - dash and crow's foot → minimum one, maximum many (mandatory)'SELECT COUNT(*) FROM app WHERE price = 0;The cardinality of a join between two tables is the numerical relationship between rows of one table and rows in the other. Common cardinalities include one-to-one , one-to-many , and many-to-many .SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
A window function call always contains an OVER clause directly following the window function's name and argument(s). This is what syntactically distinguishes it from a normal function or non-window aggregate.
The OVER clause determines exactly how the rows of the query are split up for processing by the window function. The PARTITION BY clause within OVER divides the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s). For each row, the window function is computed across the rows that fall into the same partition as the current row. The rows considered by a window function are those of the virtual table produced by the query's FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any. For example, a row removed because it does not meet the WHERE condition is not seen by any window function. A query can contain multiple window functions that slice up the data in different ways using different OVER clauses, but they all act on the same collection of rows defined by this virtual table. ORDER BY can be omitted if the ordering of rows is not important. It is also possible to omit PARTITION BY, in which case there is a single partition containing all rows. Window functions are permitted only in the SELECT list and the ORDER BY clause of the query.

13. What is inheritance?

SELECT COUNT(*) FROM app WHERE price = 0;Some databases borrow the idea of inheritance from OOP, and make it possible for one table (or other object) to inherit the characteristics of another table. Imagine a city table and a capital table. Every capital is also a city, so we can create our capital table like this: CREATE TABLE city ( name text, population int, altitude int -- (in ft) ); CREATE TABLE capital ( state char(2) ) INHERITS (city);Crow's foot diagrams represent entities as boxes, and relationships as lines between the boxes. Different shapes at the ends of these lines represent the relative cardinality of the relationship. Three symbols are used to represent cardinality: the ring represents ''zero''; the dash represents ''one''; the crow's foot represents ''many'' or ''infinite''. These symbols are used in pairs to represent the four types of cardinality that an entity may have in a relationship: - ring and dash → minimum zero, maximum one (optional); - dash and dash → minimum one, maximum one (mandatory); - ring and crow's foot → minimum zero, maximum many (optional); - dash and crow's foot → minimum one, maximum many (mandatory)'SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
A window function call always contains an OVER clause directly following the window function's name and argument(s). This is what syntactically distinguishes it from a normal function or non-window aggregate.
The OVER clause determines exactly how the rows of the query are split up for processing by the window function. The PARTITION BY clause within OVER divides the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s). For each row, the window function is computed across the rows that fall into the same partition as the current row. The rows considered by a window function are those of the virtual table produced by the query's FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any. For example, a row removed because it does not meet the WHERE condition is not seen by any window function. A query can contain multiple window functions that slice up the data in different ways using different OVER clauses, but they all act on the same collection of rows defined by this virtual table. ORDER BY can be omitted if the ordering of rows is not important. It is also possible to omit PARTITION BY, in which case there is a single partition containing all rows. Window functions are permitted only in the SELECT list and the ORDER BY clause of the query.

14. In the Postgres CLI, what is the command to see information about the current connection?

Crow's foot diagrams represent entities as boxes, and relationships as lines between the boxes. Different shapes at the ends of these lines represent the relative cardinality of the relationship. Three symbols are used to represent cardinality: the ring represents ''zero''; the dash represents ''one''; the crow's foot represents ''many'' or ''infinite''. These symbols are used in pairs to represent the four types of cardinality that an entity may have in a relationship: - ring and dash → minimum zero, maximum one (optional); - dash and dash → minimum one, maximum one (mandatory); - ring and crow's foot → minimum zero, maximum many (optional); - dash and crow's foot → minimum one, maximum many (mandatory)'SELECT COUNT(*) FROM app WHERE price = 0;SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
A window function call always contains an OVER clause directly following the window function's name and argument(s). This is what syntactically distinguishes it from a normal function or non-window aggregate.
The OVER clause determines exactly how the rows of the query are split up for processing by the window function. The PARTITION BY clause within OVER divides the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s). For each row, the window function is computed across the rows that fall into the same partition as the current row. The rows considered by a window function are those of the virtual table produced by the query's FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any. For example, a row removed because it does not meet the WHERE condition is not seen by any window function. A query can contain multiple window functions that slice up the data in different ways using different OVER clauses, but they all act on the same collection of rows defined by this virtual table. ORDER BY can be omitted if the ordering of rows is not important. It is also possible to omit PARTITION BY, in which case there is a single partition containing all rows. Window functions are permitted only in the SELECT list and the ORDER BY clause of the query.\conninfo

15. What comes right after the WHERE clause?

16. What is the syntax for the LIMIT clause?

SELECT * FROM movies LIMIT 5;Crow's foot diagrams represent entities as boxes, and relationships as lines between the boxes. Different shapes at the ends of these lines represent the relative cardinality of the relationship. Three symbols are used to represent cardinality: the ring represents ''zero''; the dash represents ''one''; the crow's foot represents ''many'' or ''infinite''. These symbols are used in pairs to represent the four types of cardinality that an entity may have in a relationship: - ring and dash → minimum zero, maximum one (optional); - dash and dash → minimum one, maximum one (mandatory); - ring and crow's foot → minimum zero, maximum many (optional); - dash and crow's foot → minimum one, maximum many (mandatory)'SELECT COUNT(*) FROM app WHERE price = 0;SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
A window function call always contains an OVER clause directly following the window function's name and argument(s). This is what syntactically distinguishes it from a normal function or non-window aggregate.
The OVER clause determines exactly how the rows of the query are split up for processing by the window function. The PARTITION BY clause within OVER divides the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s). For each row, the window function is computed across the rows that fall into the same partition as the current row. The rows considered by a window function are those of the virtual table produced by the query's FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any. For example, a row removed because it does not meet the WHERE condition is not seen by any window function. A query can contain multiple window functions that slice up the data in different ways using different OVER clauses, but they all act on the same collection of rows defined by this virtual table. ORDER BY can be omitted if the ordering of rows is not important. It is also possible to omit PARTITION BY, in which case there is a single partition containing all rows. Window functions are permitted only in the SELECT list and the ORDER BY clause of the query.

17. In the SQLite CLI, what command will exit out of the CLI?

18. What is an INNER JOIN?

SELECT COUNT(*) FROM app WHERE price = 0;SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
A window function call always contains an OVER clause directly following the window function's name and argument(s). This is what syntactically distinguishes it from a normal function or non-window aggregate.
The OVER clause determines exactly how the rows of the query are split up for processing by the window function. The PARTITION BY clause within OVER divides the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s). For each row, the window function is computed across the rows that fall into the same partition as the current row. The rows considered by a window function are those of the virtual table produced by the query's FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any. For example, a row removed because it does not meet the WHERE condition is not seen by any window function. A query can contain multiple window functions that slice up the data in different ways using different OVER clauses, but they all act on the same collection of rows defined by this virtual table. ORDER BY can be omitted if the ordering of rows is not important. It is also possible to omit PARTITION BY, in which case there is a single partition containing all rows. Window functions are permitted only in the SELECT list and the ORDER BY clause of the query.Crow's foot diagrams represent entities as boxes, and relationships as lines between the boxes. Different shapes at the ends of these lines represent the relative cardinality of the relationship. Three symbols are used to represent cardinality: the ring represents ''zero''; the dash represents ''one''; the crow's foot represents ''many'' or ''infinite''. These symbols are used in pairs to represent the four types of cardinality that an entity may have in a relationship: - ring and dash → minimum zero, maximum one (optional); - dash and dash → minimum one, maximum one (mandatory); - ring and crow's foot → minimum zero, maximum many (optional); - dash and crow's foot → minimum one, maximum many (mandatory)'An INNER JOIN is a join that returns records from two different tables. The records must meet the criteria specificed in the JOIN clause's ON clause, like so: SELECT * FROM book JOIN author ON book.author_id = author.id; If a record does not match, then that record will be skipped.

19. What data types does Postgres support?

PostgreSQL supports the following data types: - BOOL (true, false, null) - Character types such as CHAR(n) , VARCHAR(n) , and TEXT. - Numeric types such as SMALLINT, INT, SERIAL, FLOAT(n), REAL, and NUMERIC(p,s) . - Temporal types such as DATE, TIME, TIMESTAMP, TIMESTAMPZ, and INTERVAL - UUID for storing Universally Unique Identifiers - ARRAY for storing array strings, numbers, etc. - JSON stores JSON data - HSTORE stores key-value pair Special types such as network address and geometric data.SELECT COUNT(*) FROM app WHERE price = 0;SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
A window function call always contains an OVER clause directly following the window function's name and argument(s). This is what syntactically distinguishes it from a normal function or non-window aggregate.
The OVER clause determines exactly how the rows of the query are split up for processing by the window function. The PARTITION BY clause within OVER divides the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s). For each row, the window function is computed across the rows that fall into the same partition as the current row. The rows considered by a window function are those of the virtual table produced by the query's FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any. For example, a row removed because it does not meet the WHERE condition is not seen by any window function. A query can contain multiple window functions that slice up the data in different ways using different OVER clauses, but they all act on the same collection of rows defined by this virtual table. ORDER BY can be omitted if the ordering of rows is not important. It is also possible to omit PARTITION BY, in which case there is a single partition containing all rows. Window functions are permitted only in the SELECT list and the ORDER BY clause of the query.Crow's foot diagrams represent entities as boxes, and relationships as lines between the boxes. Different shapes at the ends of these lines represent the relative cardinality of the relationship. Three symbols are used to represent cardinality: the ring represents ''zero''; the dash represents ''one''; the crow's foot represents ''many'' or ''infinite''. These symbols are used in pairs to represent the four types of cardinality that an entity may have in a relationship: - ring and dash → minimum zero, maximum one (optional); - dash and dash → minimum one, maximum one (mandatory); - ring and crow's foot → minimum zero, maximum many (optional); - dash and crow's foot → minimum one, maximum many (mandatory)'

20. What is a FULL OUTER JOIN?

Crow's foot diagrams represent entities as boxes, and relationships as lines between the boxes. Different shapes at the ends of these lines represent the relative cardinality of the relationship. Three symbols are used to represent cardinality: the ring represents ''zero''; the dash represents ''one''; the crow's foot represents ''many'' or ''infinite''. These symbols are used in pairs to represent the four types of cardinality that an entity may have in a relationship: - ring and dash → minimum zero, maximum one (optional); - dash and dash → minimum one, maximum one (mandatory); - ring and crow's foot → minimum zero, maximum many (optional); - dash and crow's foot → minimum one, maximum many (mandatory)'SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
A window function call always contains an OVER clause directly following the window function's name and argument(s). This is what syntactically distinguishes it from a normal function or non-window aggregate.
The OVER clause determines exactly how the rows of the query are split up for processing by the window function. The PARTITION BY clause within OVER divides the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s). For each row, the window function is computed across the rows that fall into the same partition as the current row. The rows considered by a window function are those of the virtual table produced by the query's FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any. For example, a row removed because it does not meet the WHERE condition is not seen by any window function. A query can contain multiple window functions that slice up the data in different ways using different OVER clauses, but they all act on the same collection of rows defined by this virtual table. ORDER BY can be omitted if the ordering of rows is not important. It is also possible to omit PARTITION BY, in which case there is a single partition containing all rows. Window functions are permitted only in the SELECT list and the ORDER BY clause of the query.A FULL OUTER JOIN is a combination of a LEFT JOIN and a RIGHT JOIN. It returns all rows in both tables that match the query's WHERE clause, and in cases where the ON condition can't be satisfied for those rows it puts NULL values in for the unpopulated fields. In contrast, the CROSS JOIN clause is used to combine each row from one table with each row from another table in the result set, returning all possible combinations for the records in the two tables.SELECT COUNT(*) FROM app WHERE price = 0;

21. 'What is an ORDBMS?

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
A window function call always contains an OVER clause directly following the window function's name and argument(s). This is what syntactically distinguishes it from a normal function or non-window aggregate.
The OVER clause determines exactly how the rows of the query are split up for processing by the window function. The PARTITION BY clause within OVER divides the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s). For each row, the window function is computed across the rows that fall into the same partition as the current row. The rows considered by a window function are those of the virtual table produced by the query's FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any. For example, a row removed because it does not meet the WHERE condition is not seen by any window function. A query can contain multiple window functions that slice up the data in different ways using different OVER clauses, but they all act on the same collection of rows defined by this virtual table. ORDER BY can be omitted if the ordering of rows is not important. It is also possible to omit PARTITION BY, in which case there is a single partition containing all rows. Window functions are permitted only in the SELECT list and the ORDER BY clause of the query.SELECT COUNT(*) FROM app WHERE price = 0;Some databases like PostgreSQL are ''Object Oriented Database Management Systems'' (ORDBMS), not simply a ''Relational Database Management Systems'' (RDBMS). This means they're a hybrid between a strictly relational model (SQL) and a strictly object-oriented model (NoSQL).'Crow's foot diagrams represent entities as boxes, and relationships as lines between the boxes. Different shapes at the ends of these lines represent the relative cardinality of the relationship. Three symbols are used to represent cardinality: the ring represents ''zero''; the dash represents ''one''; the crow's foot represents ''many'' or ''infinite''. These symbols are used in pairs to represent the four types of cardinality that an entity may have in a relationship: - ring and dash → minimum zero, maximum one (optional); - dash and dash → minimum one, maximum one (mandatory); - ring and crow's foot → minimum zero, maximum many (optional); - dash and crow's foot → minimum one, maximum many (mandatory)'

22. What is an OUTER JOIN?

SELECT COUNT(*) FROM app WHERE price = 0;SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
A window function call always contains an OVER clause directly following the window function's name and argument(s). This is what syntactically distinguishes it from a normal function or non-window aggregate.
The OVER clause determines exactly how the rows of the query are split up for processing by the window function. The PARTITION BY clause within OVER divides the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s). For each row, the window function is computed across the rows that fall into the same partition as the current row. The rows considered by a window function are those of the virtual table produced by the query's FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any. For example, a row removed because it does not meet the WHERE condition is not seen by any window function. A query can contain multiple window functions that slice up the data in different ways using different OVER clauses, but they all act on the same collection of rows defined by this virtual table. ORDER BY can be omitted if the ordering of rows is not important. It is also possible to omit PARTITION BY, in which case there is a single partition containing all rows. Window functions are permitted only in the SELECT list and the ORDER BY clause of the query.Crow's foot diagrams represent entities as boxes, and relationships as lines between the boxes. Different shapes at the ends of these lines represent the relative cardinality of the relationship. Three symbols are used to represent cardinality: the ring represents ''zero''; the dash represents ''one''; the crow's foot represents ''many'' or ''infinite''. These symbols are used in pairs to represent the four types of cardinality that an entity may have in a relationship: - ring and dash → minimum zero, maximum one (optional); - dash and dash → minimum one, maximum one (mandatory); - ring and crow's foot → minimum zero, maximum many (optional); - dash and crow's foot → minimum one, maximum many (mandatory)'Unlike an INNER JOIN, an OUTER JOIN will combine rows from different tables even if the join condition is not met. In a LEFT JOIN, every row in the left table is returned in the result set, and if the join condition is not met, then NULL values are used to fill in the columns from the right table. In a RIGHT JOIN, every row in the right table is returned in the result set, and if the join condition is not met, then NULL values are used to fill in the columns from the left table.

23. What is the syntax for the BETWEEN clause?

SELECT * FROM movies WHERE year BETWEEN 1980 AND 1990;SELECT COUNT(*) FROM app WHERE price = 0;Crow's foot diagrams represent entities as boxes, and relationships as lines between the boxes. Different shapes at the ends of these lines represent the relative cardinality of the relationship. Three symbols are used to represent cardinality: the ring represents ''zero''; the dash represents ''one''; the crow's foot represents ''many'' or ''infinite''. These symbols are used in pairs to represent the four types of cardinality that an entity may have in a relationship: - ring and dash → minimum zero, maximum one (optional); - dash and dash → minimum one, maximum one (mandatory); - ring and crow's foot → minimum zero, maximum many (optional); - dash and crow's foot → minimum one, maximum many (mandatory)'SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
A window function call always contains an OVER clause directly following the window function's name and argument(s). This is what syntactically distinguishes it from a normal function or non-window aggregate.
The OVER clause determines exactly how the rows of the query are split up for processing by the window function. The PARTITION BY clause within OVER divides the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s). For each row, the window function is computed across the rows that fall into the same partition as the current row. The rows considered by a window function are those of the virtual table produced by the query's FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any. For example, a row removed because it does not meet the WHERE condition is not seen by any window function. A query can contain multiple window functions that slice up the data in different ways using different OVER clauses, but they all act on the same collection of rows defined by this virtual table. ORDER BY can be omitted if the ordering of rows is not important. It is also possible to omit PARTITION BY, in which case there is a single partition containing all rows. Window functions are permitted only in the SELECT list and the ORDER BY clause of the query.

24. What does the LIMIT clause do?

The LIMIT clause is used to narrow, or limit, a result set to the specified number of rows.SELECT COUNT(*) FROM app WHERE price = 0;Crow's foot diagrams represent entities as boxes, and relationships as lines between the boxes. Different shapes at the ends of these lines represent the relative cardinality of the relationship. Three symbols are used to represent cardinality: the ring represents ''zero''; the dash represents ''one''; the crow's foot represents ''many'' or ''infinite''. These symbols are used in pairs to represent the four types of cardinality that an entity may have in a relationship: - ring and dash → minimum zero, maximum one (optional); - dash and dash → minimum one, maximum one (mandatory); - ring and crow's foot → minimum zero, maximum many (optional); - dash and crow's foot → minimum one, maximum many (mandatory)'SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
A window function call always contains an OVER clause directly following the window function's name and argument(s). This is what syntactically distinguishes it from a normal function or non-window aggregate.
The OVER clause determines exactly how the rows of the query are split up for processing by the window function. The PARTITION BY clause within OVER divides the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s). For each row, the window function is computed across the rows that fall into the same partition as the current row. The rows considered by a window function are those of the virtual table produced by the query's FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any. For example, a row removed because it does not meet the WHERE condition is not seen by any window function. A query can contain multiple window functions that slice up the data in different ways using different OVER clauses, but they all act on the same collection of rows defined by this virtual table. ORDER BY can be omitted if the ordering of rows is not important. It is also possible to omit PARTITION BY, in which case there is a single partition containing all rows. Window functions are permitted only in the SELECT list and the ORDER BY clause of the query.

25. In the Postgres CLI, how do you connect to a database?

⚡ Recently practiced quizzes in this topic

Live quiz activity

DBMS Basics Practice Test: SQL - Queries, Constraints and Triggers Database Fundamentals Transact-SQL (T-SQL) Practice Test PostgreSQL Fundamentals Test SQL Server Vocab Redis Practice Test (NoSQL) DBMS Basics Practice Test: Indexing and Hashing Database Management System (DBMS) Practice Test: All Topics SQL Fundamentals Test SQL MCQs

25 Questions

❤ If you liked Fatskills, consider supporting us by checking out The Life Manuals You Never Got.

About | Explore | User Guide | Topics | Subjects | Doubt Solver | Career Aptitude Test | Answers | Free Tools | OSHA Basics Quiz | What Should We Know?
Privacy | Terms |

Without work one finishes nothing. - Ralph Waldo Emerson
© 2026 Fatskills.com

All trademarks, logos and brand names are the property of their respective owners. All company, product and service names used in this website are for identification purposes only. Use of these names, trademarks and brands does not imply endorsement.

25 Questions

❤ If you liked Fatskills, consider supporting us by checking out The Life Manuals You Never Got.

About | Explore | User Guide | Topics | Subjects | Doubt Solver | Career Aptitude Test | Answers | Free Tools | OSHA Basics Quiz | What Should We Know? Privacy | Terms |

Without work one finishes nothing. - Ralph Waldo Emerson© 2026 Fatskills.com

All trademarks, logos and brand names are the property of their respective owners. All company, product and service names used in this website are for identification purposes only. Use of these names, trademarks and brands does not imply endorsement.

About | Explore | User Guide | Topics | Subjects | Doubt Solver | Career Aptitude Test | Answers | Free Tools | OSHA Basics Quiz | What Should We Know?
Privacy | Terms |

Without work one finishes nothing. - Ralph Waldo Emerson
© 2026 Fatskills.com