# 2.0 Master SQL - SQL Basics ## 2.1 `SELECT` Fundamentals No notes were taken in this section. however, this section does a good job to describe how to use the SELECT statement to retreive all columns and retrieving specific columns. See [[SELECT]] ## 2.2 Aliases and Constants ### 2.2.1 [[Aliasing]] using the keyword [[AS]] to change the name of the column ==in the results set only== This is called [[Aliasing]] in SQL. ```sql SELECT title, price AS cost FROM books; ``` ### 2.2.2 Constants This is a new concept for me. You can add a constant value in a column, custom column ```sql SELECT title, price, 0.892 AS base_factor, ROUND((price * 0.892),2) AS new_price FROM books; ``` Results in | title | price | base_factor | new_price | | ----- | ----- | ----------- | --------- | | ABC | 10.99 | 0.892 | 9.80 | | BCD | 12.99 | 0.892 | 11.59 | | DEF | 8.99 | 0.892 | 8.02 | The data is created in the results set only, not in the table. ## 2.3 Expressions in `SELECT` >[! cue] [[SQL Expression]] >[! info] Expression >An expression in SQL is any valid combination of columns, constants and operators. ### 2.3.1 Example: What we want to do is take $1 from the price of each book before displaying them ```sql SELECT title, author, price -1 as price FROM books; ``` ### 2.3.2 Challenge >[! quote] Write a SQL query to display the `title`, `author`, `price`, and `proce * 100` as `cent_price` To solve: I know I am working with one table `books` and I am writing a DQL statement using `SELECT` ```sql SELECT FROM books; ``` I want a table with 4 columns; `title`, `author`, `price` and `cent_price` The `cent_price` column represents the results of an expression where the `price` is multiplied by 100. ```sql SELECT title, author, price, price * 100 AS cent_price FROM books; ``` Explanation. here the expression `price * 100` is used and the result is the value in `cent_price` - `100` is a constant value - `price` is a the specified column - `*` indicates a sum operation to be done. - `AS` indicates an aliasing of this column as `cent_price` ## 2.4 Selecting `DISTINCT` values SEE [[DISTINCT]] keyword No further notes ### 2.4.2 Challenge Write a SQL query to select the distinct values of the `order_date` from the `orders` table Objective: I want to know the dates on which orders were made. To solve: From the `orders` table, SELECT DISTINCT `order_date` and view results. ```sql SELECT DISTINCT order_date FROM orders; ``` `order_date` is a `DATE` datatype that seems to also have a time component. The course documentation say that it is using [[PostgreSQL]] as their environment. Now I am being hyper specific, I had to cast a DATE column to a char column with a specified format. ```postgresql SELECT DISTINCT TO_CHAR(order_date, 'yyyy-mm-dd') AS order_date FROM orders; ``` ## 2.5 Filtering with `WHERE` A lot of the material is basic See entries for [[WHERE]] ==Where always comes after FROM== >[! cue] Using Single/Double quotes >[! warning] Single and Double Quotes >`'` and `"` serve different purposes in [[PostgreSQL]] >- single quote is used for string literals >- double quotes used as identifiers. > - use double quotes to identify a column or table that has a space in it or some other special character or keyword >Confusing these will lead to unexpected results. #### Comparison Operators >[! cue] Valid comparison Operators for expressions | Operator | Descr | Atomic Note | | --------- | --------------------------------------------------------------------- | ------------------------- | | `=` | Equal | [[Equal]] | | `!=` | Not equal | [[Not Equal]] | | `>` | Greater than | [[Greater Than]] | | `<` | Less than | [[Less Than]] | | `>=` | Greater than or equal to | See [[Greater Than]] | | `<=` | Less than or equal to | See [[Less Than]] | | `BETWEEN` | Between a range of values, <br>inclusive of the bounded numbers given | [[BETWEEN]] | | `IN` | In a list of values designated with Parenthesis | [[IN]] | | `LIKE` | Matches a pattern (use wildcards) | [[LIKE]] | | `NOT` | Negates the condition | [[NOT]] | | `IS NULL` | Boolean test for fields with data that is NULL | [[WHERE]] ... [[IS NULL]] | Wildcards used with `LIKE` - `%Smith`, Ends with `Smith` - `Smith%`, Begins with `Smith` - `%Smith%`, has `Smith` anywhere in the string ## 2.6 Sorting with `ORDER BY` See: [[ORDER BY]] >[! cue] [[ORDER BY]] Use ORDER BY after WHERE ```SQL SELECT * FROM books ORDER BY price DESC, stock ASC; ``` ## 2.7 Limiting Results with `LIMIT` Using [[LIMIT]] strategically with [[ORDER BY]] you can generate lists like "Top 10" or "Last 10" Use limit like you would use head() or tail() in python LIMIT is always the last clause in a statement. ### Using [[OFFSET]] to exclude rows ```postgresql SELECT * FROM orders WHERE total > 200 ORDER BY total DESC LIMIT 2 OFFSET 1; ``` ## 2.8 Handling Null Values The operator (clause?) [[IS NULL]] is used with [[WHERE]] to find all data that is NULL - can be negated ```postgresql SELECT * FROM customers WHERE phone IS NOT NULL ``` >[! cue] [[COALESCE]] To update the results set with a custom string (instead of having a NULL) use the [[COALESCE]] function within the SELECT clause. ```postgresql SELECT name, COALESCE(phone, '- missing -') as phone, email FROM customers; ``` ## 2.9 Comments Some examples of comments ``` postgresql -- This is a single line comment -- you can add as many comments as you want. -- They are ignored by the database. SELECT * FROM customers; -- You can use them to comment -- out parts of your query. SELECT * FROM customers; -- WHERE id = 1; /* This is a multi line comment It can span multiple lines */ SELECT * FROM customers; /* SELECT * FROM customers; SELECT * FROM orders; SELECT * FROM books; */ SELECT * FROM orders WHERE /* country = 'USA' AND */ status = 'shipped'; ``` ## 2.10 Exercises and QUIZ ### 2.10.1 Quiz 17 questions on the concepts covered previously. 1. [[SELECT]] all columns wildcard use on a table. 2. What is the purpose of the [[WHERE]] clause in SQL? 3. Which of the following patterns would match names that end with `'Smith'` using the [[LIKE]] operator. 4. SQL syntax statements: select all that are in the correct syntax 5. What is wrong with the following sql statement? (Syntax question) 6. Know the uses if [[BETWEEN]] keyword in a [[WHERE]] clause 7. Testing what an expression will result in a given SQL statement. 8. This question is testing the purpose of [[Aliasing]] in SQL. 9. Determine the correct keyword to get unique values from a table in a SELECT statement. [[DISTINCT]]. 10. Another question dealing with [[DISTINCT]] 11. Using [[DISTINCT]] with [[ORDER BY]] statement reasoning 12. Understand [[WHERE]] filters using logical operators like [[AND]] and [[OR]] 13. What are acceptable operators for filtering with [[WHERE]] 14. Tests the use of wildcard using [[LIKE]] in a [[WHERE]] clause 15. Know how to use the [[ORDER BY]] clause for single and multiple sorting. 16. How can you use [[LIMIT]] clause with an [[ORDER BY]] clause. 17. How do you use [[LIMIT]] with [[DISTINCT]] in a SELECT statement ### 2.10.2 Projection Challenge With the given table, `customers` write a query to get the `name` (alias with `Full Name`), `email` (alias as `Email`) of all customers. ```postgresql SELECT name AS "Full Name", -- used double quotes due to space email AS "Email" FROM customers; ``` ### 2.10.3 `SELECT` Expression Write a query that returns the `title`, `final_price` of each book using the following expression ``` final_price = price + markup - discount ``` ```postgresql SELECT title, ROUND((price + markup - discount AS final_price), 2) FROM books; ``` ### 2.10.4 `SELECT` unique >[! question] >Write a query to get a unique list of `customer_id` values where the status is `pending` ```postgresql SELECT DISTINCT customer_id FROM orders WHERE status = 'pending'; ``` ### 2.10.5 Logical Operators >[! question] >Write a query that mtach the following conditions >- book is either fiction or mystery >- cost less than $20 >- published after 2018 OR has more than 10 copies in stock Table `books` has `id`, `title`, `genre`, `price`, `stock`, and `year` fields. ```postgresql SELECT * FROM books WHERE price < 20 AND LOWER(genre) IN ('fiction', 'mystery') AND (year > 2018 OR stock > 10); ``` ### 2.10.6 Sorting Challenge >[! question] >Write a query meeting the following crieteria >- The `genre` must be either Fiction or Science >- The `price` should be >= 10 and <=20 [[BETWEEN]] >- [[ORDER BY]] `stock` DESC, price ASC; ```postgresql SELECT * FROM books WHERE LOWER(genre) IN ('fiction', 'science') AND price BETWEEN 10 AND 20 ORDER BY stock DESC, price ASC; ``` ### 2.10.7 Sorting and Limiting >[! question] >Write a top 3 most recent orders that are either completed or shipped ```postgresql SELECT * FROM orders WHERE LOWER(status) IN ('completed', 'shipped') ORDER BY order_date DESC LIMIT 3; ``` ### 2.10.8 Sorting and Filtering >[! question] >Write a SQL query to return top 3 most expensive orders that don't have a `shipped_at` date ```postgresql SELECT * FROM orders WHERE shipped_at IS NULL ORDER BY total_amount DESC LIMIT 3; ``` <hr> >[!summary-top] Name of the summary >- [[COALESCE]], [[BETWEEN]], [[LIKE]], and [[WHERE]] ... [[IN]] are some of the best new keywords to know.