# SQL **SQL - Structured Query Language** Allows users to query, manipulate, and transform data from a relational database. Most commonly, a data model is designed first, then data is deconstructed by the process of "normalization". This aims to turn your data in a basic form like into separate tables. E.g. one table for customers and one for orders ### RDBMS SQL is a language which has many **Relational Database Management Systems (RDBMS)** that all support the common SQL language standard but differ in the additional features and storage types it supports: * SQLite, MySQL, Postgres, Oracle, Microsoft (MS) SQL Server ***Table*** - collection of related data entries; it consists of **columns** and **rows** * **fixed # of labelled columns** (a.k.a. attributes/properties/fields) * **any # of rows of data** (a.k.a. record) (individual entries that exist on a table) * Each table is identified by a name (ie. 'Cars') ## SQL Statements * SQL keywords are NOT case sensitive: `select` === `SELECT` * Each statement ends in a ; (like Java) | SQL COMMAND | Description | | :-----------------: | ----------------------------- | | **SELECT** | selects data from db | | **UPDATE** | updates data in db | | **DELETE** | deletes data from db | | **INSERT INTO** | inserts new data into db | | **CREATE DATABASE** | creates new db | | **ALTER DATABASE** | modifies db | | **CREATE TABLE** | creates new table | | **ALTER TABLE** | modifies table | | **DROP TABLE** | deletes table | | **CREATE INDEX** | creates an index (search key) | | **DROP INDEX** | deletes an index | ### > SELECT * colloquially refered to as *queries*, which in itself is just a statement which declares what data we're looking for, where to find it in the db. ```sql /* SYNTAX */ select from ; /* ex. Cars table: */ select Company, Price, Color from Cars /* SELECT ALL */ select * from Cars; ``` ### > SELECT DISTINCT * gets distinct values in specified column(s) of a table ```sql /* ex. Car Garage table --> gets Company, but no-repeat values */ select distinct Company from Garage; ``` ### > WHERE * used to filter rows that are retrieved from table * can be paired with *select / update / delete* ```sql /* Syntax */ select * from Garage where ; ``` * The is built using operators (ex. follows above): | Operator | Description | Ex. | | :------: | :---------------------------------------------: | --------------------------------------------------------------------------- | | = | equal ('abc' \| 123) | `where Company='BMW'` | | > | greater than | `where Price>50000` | | < | less than | `where Price<50000` | | >= | greater than or = to | `where Price>=50000` | | <= | less than or = to | `where Price<=50000` | | <> | not equal ('abc' \| 123) | `where Color<>'Blue'` | | BETWEEN | between a certain range | `where Price BETWEEN 10000 AND 20000` | | LIKE | search for a pattern | `where Company LIKE 's%'` (starts with s) | | % | match sequence of chars (only w/ LIKE/NOT LIKE) | `where col_name LIKE '%AT%'` matches = ("AT", "ATTIC", "CAT", "BATS", ... ) | | - | used anywhere in a str to match a single char | `where col_name LIKE 'AN_'` matches=("AND", but not "AN") | | IN | specify multiple possible values for col | `where Company IN ('BMW', 'Tesla')` | * note: text columns must use `'single-quotes'` and numeric columns should not. * [Wildcard Characters for **LIKE**](https://www.w3schools.com/sql/sql_wildcards.asp) You can chain multiple conditions in a WHERE clause using logical operators: * **AND** ```sql /* displays a row from col1 if ALL 3 conditions are true */ select col1 from table_name where cond1 AND cond2 AND cond3; ``` * **OR** ```sql /* displays a row from col1 if ANY of the 3 conditions are true */ select col1 from table_name where cond1 OR cond2 OR cond3; ``` * **NOT** ```sql /* displays a row from col1 if cond1 is NOT TRUE but the rest are */ select col1 from table_name where NOT cond1 AND cond2 AND cond3; ``` Now, we can construct more complex clauses: ```sql between 1 and 10 not between 1.5 and 10.5 in (2, 4, 6, 8, 10) not in (1, 3, 5, 7, 9) ``` ### > ORDER BY * sorts the result-rows in **ASC** (default) or **DESC** order ```sql /* all cars sorted by Price in ASC order */ select * from Garage order by Price ASC; ``` * You can sort multiple columns too: ```sql /* orders by Price (asc) but if some rows have the same Price, it orders them by Name (desc) */ select * from Garage order by Price ASC, Name DESC ``` * clauses **LIMIT** and **OFFSET** are commonly used with the **ORDER BY** clause. ```sql /* limit - reduce the number of rows to return offset - where to begin counting # rows from */ SELECT a_column, another_column, … FROM mytable WHERE condition ORDER BY column ASC/DESC LIMIT num_limit OFFSET num_offset; ``` *** ## Multi-Table Queries with JOINs Up to now, we've been working with a single table, but entity data in the real world is often broken down into pieces and stored across multiple orthogonal tables using a process known as ***normalization*** ***Database Normalization*** * every entity is organized into its own table * process of organizing data in a database * This includes creating tables and establishing relationships between those tables Tables that share info about a single entity need to have a ***primary key*** that identifies *that* entity *uniquely* across the database; this is usually an ***auto-incrementing integer*** (but could be string/hash/...) ### $ JOINs * used to combine rows from 2 or more tables, based on a related column b/n them. ![How to Learn SQL JOINs | LearnSQL.com](https://learnsql.com/blog/learn-and-practice-sql-joins/2.png) ## > (INNER) JOIN * Returns records that have matching values in both tables ![Gif of how inner join iterates through the tables](https://dataschool.com/assets/images/how-to-teach-people-sql/innerJoin/innerJoin_3.gif) * matches rows from the first table and the second table which have the same key (as defined by the **ON** constraint) to create a result row with the combined columns from both tables. * You can write either ***`INNER JOIN`*** or ***`JOIN`*** ```sql SELECT col, another_table_col, … FROM mytable INNER JOIN another_table ON mytable.id = another_table.id ``` ## > LEFT (OUTER) JOIN * Returns all records from the left table, and the matched records from the right table ![Shows the Left join adding matches between the left and right table to the result table](https://dataschool.com/assets/images/how-to-teach-people-sql/leftJoin/leftJoin_1.gif) * The result is **NULL** in the consecutive tables when there is no match ```sql SELECT col, another_table_col, … FROM mytable LEFT JOIN another_table ON mytable.id = another_table.id ``` ## > RIGHT (OUTER) JOIN * Returns all records from the right table, and the matched records from the left table ![Shows how a right join iterates through the tables](https://dataschool.com/assets/images/how-to-teach-people-sql/leftJoin/leftJoin_3.gif) * The result is **NULL** in the consecutive tables when there is no match ```sql SELECT col, another_table_col, … FROM mytable RIGHT JOIN another_table ON mytable.id = another_table.id ``` ## > FULL (OUTER) JOIN * simply means that rows from both tables are kept, regardless of whether a matching row exists in the other table ![gif showing part one of a full outer join: the left join](https://dataschool.com/assets/images/how-to-teach-people-sql/fullOuter/fullOuter_1.gif) ```sql SELECT col, another_table_col, … FROM mytable FULL JOIN another_table ON mytable.id = another_table.id ``` *** ## NULLs * It's always good to reduce the possibility of ***`NULL`*** values in databases bc they require special attention when constructing queries, constraints and when processing the results. * Alternatives to ***NULL*** values include: * 0 (for numerical data) * '' (for text data) * But if your database needs to store incomplete data, then ***`NULL`*** values can be appropriate if the default values will skew later analysis ```sql /* Gets employees whose building is null */ SELECT * FROM employees WHERE Building IS NULL; /* optionally: not null */ WHERE Building IS NOT NULL; ``` *** ## Queries with expressions you can also use *expressions* to write more complex logic on column values in a query. The use of expressions save time but can also make the query harder to read; so it's recommended that you use a *descriptive alias* ***`AS`*** keyword adfter the SELECT part of the query. ```sql /* syntax */ SELECT col_expression AS expr_description, … FROM mytable; /* ex. */ SELECT speed / 2.0 AS half_speed FROM physics_data /* In addition to expressions, regular columns and even tables can also have aliases. */ SELECT column AS better_column_name, … FROM a_long_widgets_table_name AS mywidgets INNER JOIN widget_sales ON mywidgets.id = widget_sales.widget_id; ``` *** ## Queries with aggregates ***Aggregate Function*** - SQL performs a calculation on multiple values and returns a single value SQL provides many aggregate functions: !\[Screen Shot 2022-07-02 at 4.54.32 PM]\(/Users/dev/Library/Application Support/typora-user-images/Screen Shot 2022-07-02 at 4.54.32 PM.png) ***GROUPED AGGR. FUNCTIONS*** * In addition to aggregating across all the rows, you can instead apply the aggregate functions to individual groups of data within that group * This would then create as many results as there are unique groups defined as by the ***`GROUP BY`*** clause, which groups rows that have the same values into summary rows. ```sql SELECT AGG_FUNC(column_or_expression) AS aggregate_description, … FROM mytable WHERE constraint_expression GROUP BY column; ``` Note: * if you want to add a **WHERE** clause after a **GROUP BY** clause which is already preceded by a **WHERE** clause, use the **HAVING** clause (treat exactly like WHERE): ```sql where condition1 group by col1 having group_condition ``` *** ## Order of execution of a Query (SQL BEDMAS) 1. ***`FROM`*** , ***`JOIN`*** 2. ***`WHERE`*** 3. ***`GROUP BY`*** 4. ***`HAVING`*** 5. ***`SELECT`*** 6. ***`DISTINCT`*** 7. ***`ORDER BY`*** 8. ***`LIMIT / OFFSET`*** *** ## Inserting Rows ***SCHEMAS*** - describes the structure of each table, and the datatypes that each column of the table can contain. (think: mongoose schema) ### > INSERT INTO * Used to insert new rows in a table. Can be written 2 ways: 1. Specify **both** the ***column names*** and the ***values*** to be inserted ```sql /* syntax */ INSERT INTO table_name (col1, col2, ...) VALUES (value1, value2, ...); /* ex. */ INSERT INTO Cars (brand, color, price) VALUES ('Porsche', 'Red', 90000); ``` 2. If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the **order of the values** is in the same **order as the columns in the table**. ```sql /* syntax */ INSERT INTO table_name VALUES (value1, value2, ...); /* ex. */ INSERT INTO Cars VALUES ('Porsche', 'Red', 90000), ('Nissan', 'Navy', 75000); ``` Note: you don't need to insert the ID field as it's an **auto-incrementing field** and will be generated automatically when a new row is inserted into the table. *** ## Updating Rows ### > UPDATE (+ SET) * used to modify the existing records in a table * Similar to the ***`INSERT`*** statement, you have to specify exactly which table, columns, and rows to update. * the data you are updating has to follow the schema of those columns. ```sql /* syntax */ UPDATE mytable SET column_1 = new_value, column_2 = new_value WHERE condition_1 /* ex. Update the values of the ContactName and City fields for the row whose CustomerId=1 */ UPDATE Customers SET ContactName = 'Christian Bale', City= 'Gotham' WHERE CustomerID = 1; ``` *** ## Deleting Rows ### > DELETE * used to delete existing records in a table. * The ***`WHERE`*** clause specifies which record(s) should be deleted. * If you omit the ***`WHERE`*** clause, **all records** in the table will be deleted. ```sql /* syntax */ DELETE FROM mytable WHERE condition_1 /* clear table */ DELETE FROM mytable /* ex. Remove all rows whose Brand=Porsche */ DELETE FROM Cars WHERE Brand='Porsche'; ``` *** ## Creating Tables ### > CREATE TABLE * used to create a new table in a db * add a **IF NOT EXISTS** clause to avoid SQL errors. ```sql /* Basic Syntax */ CREATE TABLE IF NOT EXISTS table_name ( column1 datatype, column2 datatype, .... ); /* Extra Optional Syntax: */ CREATE TABLE IF NOT EXISTS table_name ( column1 datatype TableConstraint DEFAULT default_val, column2 datatype TableConstraint DEFAULT default_val, .... ); /* Ex. */ CREATE TABLE movies ( id INTEGER PRIMARY KEY, title TEXT, director TEXT, year INTEGER, length_minutes INTEGER ); ``` * [Data Types Reference](https://www.w3schools.com/sql/sql_datatypes.asp) * [Constraint Reference](https://sqlbolt.com/lesson/creating_tables) **Create Table Using Another Table** * You can also create a copy of an existing table using ***`CREATE TABLE`*** * The new table gets the same column definitions. All columns or specific columns can be selected. * If you create a new table using an existing table, the new table will be filled with the existing values from the old table ```sql CREATE TABLE new_table_name AS SELECT column1, column2,... FROM existing_table_name WHERE ....; ``` *** ## Altering Tables ### > ALTER TABLE * used to add, delete, or modify columns in an existing table. * also used to add and drop various constraints on an existing table. ### 1. *Adding Columns* - ADD * required: column **name** and **datatype** * optional: table **constraints** and **default** values. ```sql /* Basic Syntax */ ALTER TABLE mytable ADD new_column datatype; /* Optional Syntax */ ALTER TABLE mytable ADD new_column dataType optionalTableConstraint DEFAULT default_value; ``` ### 2. *Removing Columns* - DROP COLUMN * Dropping columns is as easy as specifying the column to drop, however, some databases (ie. SQLite) don't support this feature. * Instead, you may have to create a new table and migrate the data over ```sql /* Syntax */ ALTER TABLE mytable DROP COLUMN column_to_be_deleted; ``` ### 3. *Renaming Columns* - RENAME TO ```sql /* Syntax */ ALTER TABLE mytable RENAME TO new_table_name; ``` ### 4. *Alter Column Datatype* - (varies from rdbms to rdbms) *** ## Dropping Tables ### > DROP TABLE * used to remove an entire table including all of its data * differs from the **DELETE** statement in that **DROP TABLE** also removes the table schema from the db entirely * use with **IF EXISTS** to avoid errors ```sql DROP TABLE IF EXISTS mytable; ``` *** ### > UNION * used to combine the result-set of two or more ***`SELECT`*** statements * Every ***`SELECT`*** within ***`UNION`*** must have the same # of columns of the same data types and in the same order. ```sql /* syntax */ SELECT column_name FROM table1 UNION SELECT column_name FROM table2; ``` * The ***`UNION`*** operator selects only distinct values by default. To allow duplicate values, use ***`UNION ALL`***: ```sql /* syntax */ SELECT column_name FROM table1 UNION ALL SELECT column_name FROM table2; ``` ### > EXISTS * used to test for the existence of any record in a subquery ```sql SELECT column_name FROM table_name WHERE EXISTS ``` ### > SELECT INTO * copies all (or some) columns from one table into a new table: ```sql /* swap out '*' for 'col1, col2, ...' to copy only some columns into a new table */ SELECT * INTO new_table FROM old_table WHERE condition_1; ``` ### > CASE ... END * The ***`CASE`*** statement goes through conditions and returns a value when the first condition is met (like an if-then-else statement). * Once a condition is true, it will stop reading and return the result. * If no conditions are true, it returns the value in the ***`ELSE`*** clause ```sql /* Basic Syntax */ CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE result END; /* Ex. */ SELECT OrderID, Quantity, CASE WHEN Quantity > 30 THEN 'Quantity is greater than 30' WHEN Quantity = 30 THEN 'Quantity is 30' ELSE 'The quantity is under 30' END AS QuantityText FROM OrderDetails; ``` ### > CREATE DATABASE * used to create a new SQL db ```sql CREATE DATABASE db_name ``` ### > DROP DATABASE * used to drop an existing SQL database (use w/ IF EXISTS for safety) ```sql DROP DATABASE IF EXISTS existing_db_name ``` ### Constraints * SQL constraints are used to specify rules for the data in a table. * They're used to limit the type of data that can go into a table. (for accuracy and reliability reasons). * If there is any violation between the constraint and the data action, the action is aborted. * Constraints can be applied to a column or an entire table. * The following constraints are commonly used: | Constraint | Description | | :----------: | :-----------------------------------------------------------------: | | NOT NULL | ensures that column can't have NULL value | | UNIQUE | ensures that all values in a column are different | | PRIMARY KEY | combo of NOT NULL + UNIQUE. Uniquely identifies each row in a table | | FOREIGN KEY | prevents actions that would destroy links between tables | | CHECK | ensures that values in a column satisfy a specific condition | | DEFAULT | sets a default value for a column | | CREATE INDEX | create and retrieve data from the database very quickly | ```sql CREATE TABLE Persons ( ID int NOT NULL AUTO_INCREMENT, LastName text NOT NULL, FirstName text NOT NULL DEFAULT 'Joe', Age int PRIMARY KEY (ID) CHECK (Age>=18) ); ``` *** ### Single line commentz: `-- here` ```sql -- this and that and this ``` ### Multi line commentz: `/* here */` ```sql /* this and that and this */ ``` --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://dev-patel-1.gitbook.io/notes/languages/sql.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.