SQL Freecodecamp

https://www.youtube.com/watch?v=HXV3zeQKqGY

32 flashcards · Programming Languages · by charlie42

1. What's a surrogate key?

   A primary key that doesn't map to any real world value

2. What's the opposite of surrogate key?

   Natural key

3. What's a foreign key?

   A key with values of a primary key of another table

4. What's a composite key?

   A primary key consisting of multiple columns that uniquely identify a row. Often both are a foreign key.

5. What do we mean when we say SQL is a hybrid language?

   Querying, schema definition, permission setting, and updating are four different sub-languages

6. List data types for numbers

   INT DECIMAL

7. What are the parameters for DECIMAL data type

   DECIMAL(M, N) M - total number of digits N - out of M, how many are after decimal point

8. What's the data type used for strings?

   VARCHAR(M) M - number of characters

9. What are other common data types besides strings and numbers?

   BLOB DATE TIMESTAMP

10. What BLOB stands for?

    Binary Large OBject

11. What format is used for DATE data type?

    YYYY-MM-DD

12. What format is used for TIMESTAMP data type?

    YYYY-MM-DD HH:MM:SS

13. DQL: Display all information from a table

    SELECT * FROM table;

14. When creating a table, other than PRIMARY KEY, how to specify that values in a column can't duplicate?

    UNIQUE key word

15. DQL: sort values from highest to lowest

    ORDER BY column DESC;

16. DQL: get only first n rows

    LIMIT 2;

17. DQL: filter by value

    WHERE column = 'value';

18. DQL: filter by several values

    WHERE column = 'value' AND another_column = 'value';

19. Symbol for comment

    --

20. Symbol for not equal to

    <>

21. DQL: filter by a column containing one of several possible values (e.g. name is either Claire or Kate or Mike)

    WHERE name IN ('Claire', 'Kate', 'Mike');

22. DQL: use an alias when printing a column

    SELECT column AS col FROM table;

23. DQL: print all unique values from a column

    SELECT DISTINCT name FROM table;

24. DQL: check how many non-null values there are in a column

    SELECT COUNT(name) FROM table;

25. DQL: check mean of a column

    SELECT AVG(age) FROM table;

26. DQL example: total sales done by each employee (same table)

    SELECT SUM(sales), employee_id FROM table GROUP BY employee_id

27. DQL: filter using a wildcard

    SELECT name FROM table WHERE name LIKE '%Jr%'

28. DQL: name two wildcard characters in SQL and what they're used for

    % - any number of characters _ - exactly one character

29. DQL: print all names from table employee and all names from the table client in the same list

    SELECT employee.name FROM employee UNION SELECT client.name FROM client;

30. DQL: print values from two tables with a common key

    SELECT table1.name, table2.address FROM table1 JOIN table2 ON table1.id = table2.id

31. DQL: What kind of JOIN is the default?

    Inner join

32. DQL: What's the syntax for filtering by a list of values that you got from another query

    WHERE column IN ( SELECT name FROM table2 WHERE condition; )