SQL

Based on PostgreSQL database. Links: great articles with exercises

24 flashcards · Programming Languages · by lakesare

1. Name 4 properties of an ACID transaction.

   Atomicity - either entire transaction is run, or nothing changes. Consistency - constraints, foreign keys, etc, are not violated. Isolation - concurrent execution of transactions results in a system state that would be obtained if transactions were executed sequentially. Durability - after operation all is recorded to a durable medium.

2. How to order by multiple columns? (and how exactly would that be sorted internally)

   ORDER BY a DESC, b This sorts everything by a first, and then by b whenever the a fields for two rows are equal.

3. What is the default ORDER BY direction?

   ASC (1 -> 2 -> 3)

4. What is a scalar query?

   Query that returns: 1 column, and 0 or 1 records. [{ name: 'Angel' }] or []

5. Correlated vs uncorrelated subquery?

   correlated: subquery that gets executed many times, once for every value that the outer SELECT produces. uncorrelated: only gets executed once.

6. What does SELECT DISTINCT do?

   Removes duplicate records based on the Select-list, not on the data in the table. If nulls are present - it considers them a regular value, and returns at most ONE distinct null value. Or one distinct tuple of values, where null is considered a value.

7. Surrogate VS natural key?

   surrogate key (e.g. SERIAL PRIMARY KEY) natural key (e.g. a phone_number column). Better use surrogate keys, as natural keys can change.

8. What will this return? SELECT * FROM table1 CROSS JOIN table2;

   CROSS JOIN will create all possible combinations of rows between tables.

9. Rewrite using a JOIN: SELECT * FROM table1, table2

   SELECT * FROM table1 CROSS JOIN table2;

10. Display a number of movies each director has directed.

    SELECT director, COUNT(id) as numberOfMovies FROM movies GROUP BY director;

11. Name and explain all 5 types of JOINs.

    CROSS: cartesian product of 2 tables. Doesn't have ON clause. INNER: cross join, then evaluate ON. If false - discard. LEFT OUTER: inner join, then, if some rows in the left table didn't match any rows in the right table - add them with nulls as the right table column values. RIGHT OUTER: reverse left outer FULL OUTER: inner join, then preserve at least one version of both left and right rows (as in left outer and right outer joins) Links: perfect stackoveflow answer

12. When to use ' VS " in Postgres?

    select * from "user" WHERE "user".email = 'lakesare@gmail.com'; ' is for strings. " is for column and table names. Everything wrapped in " will be interpreted as a column or a table.

13. What does ON DELETE CASCADE do? Write an example.

    CREATE TABLE course ( title TEXT, user_id integer REFERENCES "user" (id) ON DELETE CASCADE ); If we delete a user with this user_id, course will get deleted too. Can only be defined in addition to REFERENCES constraint. Specifies how it should act.

14. What is PRIMARY KEY constraint equivalent to? product_no integer PRIMARY KEY,

    product_no integer UNIQUE NOT NULL It needs to be both UNIQUE and NOT NULL, because UNIQUE doesn't exclude null values automatically.

15. Name 4 ways to enforce referential integrity ON UPDATE/DELETE.

    course_id integer REFERENCES course (id) ON DELETE RESTRICT - stops the delete altogether (default) ON DELETE CASCADE - will delete all rows that reference this column too ON DELETE SET NULL ON DELETE SET DEFAULT Referential integrity makes it possible for REFERENCES-constrained column to always refer to existing value.

16. Name 3 column constraints in Postges, and 2 that can be derived.

    CHECK: price integer CHECK (price > 0) UNIQUE: ensures that column is unique with respect to other rows in table REFERENCES: course_id integer REFERENCES course (id), will ensure that course_id never contains nonexistent course id, enforces referential integrity Can be derived: NOT NULL: equivalent to CHECK (price IS NOT NULL) PRIMARY KEY: equivalent to UNIQUE NOT NULL

17. HAVING vs WHERE clause?

    The HAVING clause was added to sql just so we could compare aggregates to other values – just how the WHERE clause can be used with non-aggregates. WHERE clause can be used with SELECT, INSERT, and UPDATE statements, whereas HAVING can be used only with SELECT statement. WHERE filters rows before aggregation (GROUP BY), whereas HAVING filter groups after aggregations are performed. Aggregate function cannot be used in WHERE clause unless it is in a subquery contained in HAVING clause, whereas a…

18. Select how much bonus money each employee has, but only if that sum is > 1000.

    SELECT employee_id, sum(bonus) AS all_bonuses FROM employee_bonuses GROUP BY employee_id HAVING all_bonuses > 1000;

19. Views VS materialized views?

    View: View is just a named query. It doesn't store anything. When there is a query on view, it runs the query of the view definition. Actual data comes from table. CREATE VIEW mymatview AS SELECT * FROM mytab; Materialized view: Stores data physically and gets updated periodically. While querying MV, it gives data from MV. CREATE MATERIALIZED VIEW mymatview AS SELECT * FROM mytab;

20. Consider a table users, where many of user.email-s are null. What will these queries return? select DISTINCT "user".email from "user" select DISTINCT "user".email, "user".id from "user"

    select DISTINCT "user".email from "user" [{ email: null }, { email: 'a@a.com' }, ...] One null email will be returned, as if it's a distinct value. select DISTINCT "user".email, "user".id from "user" [{ email: null, id: 1 }, { email: null, id: 2 }, ...] All null email will be returned, because ids make this raws distinct. Overall - in DISTINCT clause NULL is regarded as a usual value.

21. Name 4 possible syntaxes for aggregate functions.

    aggregate_name (expression) E.g. COUNT user.email will count all non-null emails. Invokes the aggregate across all rows for which the given expression yields non-null values. aggregate_name (ALL expression) Same (ALL is the default). aggregate_name (DISTINCT expression) Invokes the aggregate for all distinct non-null values of the expressions found in the input rows. aggregate_name (*) Invokes the aggregate once for each input row regardless of null or non-null values; since no particular input…

22. If we want to define a table, how would you define an integer column "price" that can't be smaller than zero?

    price integer CHECK (price > 0)

23. What is a primary key?

    It's one of the composite constraints, sums up to being UNIQUE NOT NULL.

24. Name 2 cases where the keyword DISTINCT is used.

    select DISTINCT email from users => [null (once), hi@gmail, hello@gmail] select count (DISTINCT email) as hi from users => 2 __________ (explanation) 1. select DISTINCT "user".email from "user" Removes duplicate records based on the Select-list, not on the data in the table. If nulls are present - it considers them a regular value, and returns at most ONE distinct null value. Or one distinct tuple of values, where null is considered a value. 2. aggregate_name (DISTINCT expression) Invokes the a…