front 1 SELECT | back 1 select a column from a table * for all columns |
front 2 DISTINCT | back 2 Only unique values |
front 3 ORDER BY | back 3 Put things in order (ascending, descending) |
front 4 WHERE | back 4 Search with a condition/filter, like an IF |
front 5 INSERT | back 5 Adds new row to table
|
front 6 UPDATE | back 6 Update data of existing rows in table, you can use SET to change the actual value and WHERE for the condition
|
front 7 DELETE | back 7 Delete a row FROM table WHERE condition |
front 8 GROUP BY | back 8 rows that have the same values into summary rows, use with something like COUNT |
front 9 JOIN | back 9 Combining two tables |
front 10 LEFT JOIN | back 10 All rows from Left table are included in set, fills entries without data with NULL |
front 11 RIGHT JOIN | back 11 All rows in Right table are included in set, fills entries without data with NULL |
front 12 INNER JOIN | back 12 All rows in only both tables are included in set |
front 13 OUTER/FULL JOIN | back 13 Combines all tables, kind of creates a mess |
front 14 NULL | back 14 No value |
front 15 SELECT TOP | back 15 Shows the top n results, same as LIMIT n |
front 16 List of (Statistical) Functions | back 16
|
front 17 What is an Index? | back 17 Speed up getting data from tables Assigning an index number to a set of values (column) in a table
|
front 18 Transactions | back 18 Group multiple SQL statements into a single logical unit of work that either succeeds or fails as a whole.
|
front 19 Views | back 19 Virtual tables stored by a SELECT statement, stored queries
|
front 20 Stored Procedures | back 20 Stored procedures are a set of SQL statements that are stored in the database and can be called by applications, like a script/transactions? |
front 21 Normalization | back 21 Normalization is the process of organizing data in a database so that it is structured according to a set of rules that eliminate redundancy and improve data consistency. |
front 22 LIKE | back 22 Used with WHERE to find patterns in a query with a wildcard |
front 23 Wildcards | back 23 % Represents zero or more characters _ Represents a single character Like Regex |
front 24 IN | back 24 Used to specify values in a WHERE clause, a lot like OR |
front 25 BETWEEN | back 25 query in a range of values |
front 26 Aliases | back 26 Give a table/column a temporary name, uses AS |
front 27 UNION | back 27 returns distinct values SELECTed from both tables |
front 28 HAVING | back 28 WHERE but for aggregate functions (COUNT) |
front 29 EXISTS | back 29 test for the existence of a record in a subquery |
front 30 ANY | back 30 Returns boolean if any one record/value matches the subquery |
front 31 ALL | back 31 Returns boolean if all records/values matches the subquery |
front 32 SELECT INTO | back 32 copies data from one table into a new table |
front 33 INSERT INTO SELECT | back 33 copies data from one table and inserts it into another table |
front 34 CASE | back 34 Uses WHEN - THEN conditions and ELSE as a default |
front 35 Null functions | back 35 IFNULL(), ISNULL(), COALESCE(), and NVL() |
front 36 COALESCE | back 36 returns the first non-null value in a list |
front 37 NVL | back 37 Oracle version of ISNULL |
front 38 Comment | back 38 -- single line /* */ multiline |
front 39 Operators | back 39 All the same as Python basically |
front 40 CREATE DATABASE, DROP DATABASE | back 40 creates a new SQL database, drops SQL database |
front 41 BACKUP DATABASE | back 41 create a full back up of an existing SQL database |
front 42 CREATE TABLE, DROP TABLE | back 42 create new table, drops table |
front 43 ALTER TABLE | back 43 add, delete, or modify columns in an existing table Ex: ALTER TABLE table_name ADD column_name |
front 44 Constraints | back 44 Used with CREATE TABLE or ALTER TABLE to specify some rules |
front 45 List of constraints | back 45
|
front 46 NOT NULL | back 46 Ensures that a column cannot have a NULL value |
front 47 UNIQUE | back 47 Ensures that all values in a column are different |
front 48 PRIMARY KEY | back 48 A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table |
front 49 FOREIGN KEY | back 49 Prevents actions that would destroy links between tables |
front 50 CHECK | back 50 Ensures that the values in a column satisfies a specific condition |
front 51 DEFAULT | back 51 Sets a default value for a column if no value is specified |
front 52 CREATE INDEX/INDEX | back 52 Used to create and retrieve data from the database very quickly |
front 53 AUTO INCREMENT | back 53 allows a unique number to be generated automatically when a new record is inserted into a table |
front 54 Dates | back 54 YYYY-MM-DD (HH:MM:SS) |
front 55 Injection | back 55 Can be used for hacking? Ex: OR 1=1 |
front 56 Hosting DBs | back 56 MS SQL Server, Oracle, MySQL, and MS Access |
front 57 Data types | back 57 char, varchar = string, int, smallint, tinyint, bigint, BLOB (big object) |