SQL 101

Welcome to SQL 101. From beginner to advanced, to interview questions, this repository aims to be your complete guide to learning and mastering SQL.

So please, let your journey into SQL database programming begin!

Beginner

1. Querying Data - SELECT, FROM, PRAGMA, WHERE, AND, ORDER BY, ASC, DESC

This workbook covers the basics of querying data with examples for each of the keywords listed as well as the three diffent types of fetch functions used in SQLite and how to connect to a database.

2. More Querying Data - COUNT, DISTINCT, IN, NOT, GROUP BY, MAX, MIN SUM, AVG

This workbook covers counting rows and unique values, grouping data and the in and not functions. Also finding the minimum, maximum, sum and average of columns or groups of data.

3. Create, Read, Update, Delete - CREATE, UPDATE, DELETE, NOT LIKE, LIKE, NOT NULL, NULL, IS, INSERT, DROP TABLE

This workbook covers the CRUD database staples. How to create data, read data, update that data and how to delete it.

4. More on Tables - PRIMARY KEY, AUTOINCREMENT, INT, INTEGER, BIGINT, CONSTRAINT, CHECK, UNIQUE, VARCHAR, TIMESTAMP, DEFAULT CURRENT_TIMESTAMP

This workbook covers primary keys and why they are important along with a few more useful things to know when creating tables, including tracking the date and time a row was added.

5. Joins - INNER JOIN, LEFT JOIN, LEFT OUTER JOIN, UNION ALL, UNION, AS, CROSS JOIN, NATURAL JOIN

This workbook covers the many ways you can join one or more tables together for a variety of reasons. It also covers the AS keyword and UNION functions for aliases and combined SELECT queries.

6. Data Types - STRICT, INT, TINYINT, SMALLINT, REAL,FLOAT, DOUBLE, TEXT, CHAR, VARCHAR, BOOLEAN, BIT, DATE, TIME, TIMESTAMP, BINARY, VARBINARY, BLOB, JSON, ARRAY, ENUM, SERIAL

This workbook covers the many data types used in SQL from common ones such as TEXT and INTEGER to more niche ones such as SERIAL and ARRAY.

7. Math Functions - ABS, ROUND, RANDOM, TOTAL, SQRT, SQUARE, CEILING, FLOOR, POWER, EXP, LOG, MOD, COS, SIN, TAN, PI

This workbook covers the most commonly used math functions, as well as many other useful ones, such as the trigonometry trio (Sin, Cos, Tan), Pi, Exp, Log and more.

8. String Functions - CONCAT, CONCAT_WS, LENGTH, CHAR_LENGTH, LEN, SUBSTR, SUBSTRING, RIGHT, LEFT, UPPER, LOWER, INITCAP, TRIM, LTRIM, RTRIM, REPLACE, INSTR, CHARINDEX, POSITION, LOCATE

This workbook covers functions to modify, add, search and measure strings in various ways. Also the use of triple quotations """ """ in writing SQL and the Pandas read_sql function.

9. Useful things to know - EXISTS, HAVING, LIMIT, SELECT TOP, FETCH FIRST, CASE, WHEN, THEN, ELSE, END

This workbook covers some useful functions like Case statements, the Having keyword, which is like a Where clause for Group By results and the exists keyword. Also some SQL best practices.

10. Altering Tables and Complex Table Relationships - ALTER TABLE, ADD COLUMN, DROP COLUMN, RENAME TO, RENAME COLUMN, ALTER COLUMN, SET NOT NULL, DROP NOT NULL, FOREIGN KEY

This workbook covers ways to modify tables after creating them, and more complex relationships, such as use of foreign keys and many to many relationships.

Intermediate

11. Views and Subqueries - CREATE VIEW, DROP VIEW

This workbook covers views, queries turned into objects for future reference and subqueries which are queries within a query.

Data Cleaning

These workbooks go through the functions used to clean data for use in either machine learning models or data analysis.

Enjoy and please suggest any improvements to the curriculum you can think of.