My attempt (4) at learning a coding language (SQL) in my mid 30s

This attempt will be learning on SQL learning. I bought a course on udemy previously and decided it is time to complete it. 

The video informed me to keep track of the passwords during the installing of postgresql and then pgadmin4 which is the visual interface... I forget about them and ended up had to reinstall and install again... sigh

To create a database, right click at database then "Create".

To restore a database, right click on the "new database name" then "Restore". Find the correct .tar file, remember to check on the restore options tab if there are anything that need to be checked. 

Check on the tables in the database by clicking on the "new database name" >> "Schema" >> "Tables" >> (it will show the tables available). Look further into the table by >> "table name", it will show the column titles. 

Syntax:

1) SELECT - allow us to retrieve information from a table. 

eg SELECT column_name FROM table_name; >> Press "Run, the play button" or F5

You can select multiple column.

eg SELECT c1,c3 FROM table1;

 

For all column use a (*) but it is inefficient as it will query everything in the table.  This increases traffic and slows down the retrieval of results. 

You can select column to return based on your query order eg c3, c1. 

Syntax will still run even in lower case and without the semicolon but it is a good practice and notation to use upper case for the syntax to differentiate the query and to show the end of query, increasing the readability of the query by others. 

Use the scratch pad for working out complex queries. 

2) DISTINCT - return only distinct values in a column

eg SELECT DISTINCT (column) FROM table; 

use ( ) for better clarity though it will work without the ( ) too.

3) COUNT - return number of input rows that match a specific condition of a query.

eg SELECT COUNT(column) FROM table. / SELECT COUNT(*) 

it requires the ( ) otherwise it does not work. The table will have the same number of rows thus it does not matter if you had selected any particular column in a table. Better practice to use a a column name as it help us formulate what question we were trying to answer. 

eg SELECT COUNT(DISTINCT(column)) FROM table;

To count all the distinct rows int the column. 

4) WHERE - specify conditions on columns for the rows to be returned. 

eg SELECT column1, column2 

     FROM table

     WHERE  conditions (eg column_name = 'Red');

Conditions - comparison operators eg greater than > / equal to =. 

                  - logical operators AND / OR / NOT 

Need to use ' ' for str while there is no need for integer. eg column_name > 2.

5) ORDER BY - sort rows based on a column value, either ascending or descending. Alphabetically for string. 

eg SELECT column1,column2

     FROM table

     ORDER BY column1 ASC/DESC

sort will be on the last line as it is the last thing to do. 

to sort for more than one column: ORDER BY column1,column2.

can choose column1 ASC, column2 DESC. 

6) LIMIT - allow us to limit the number of rows returned for a query. Useful to only view the top few rows to get an idea of the table layout. 

eg LIMIT (number of rows to return)

7) BETWEEN - match a value against a range of values. same as >= low AND <= high. Both the low and high values are inclusive. 

Opposite will be NOT BETWEEN

to use with dates, date format must be YYYY-MM-DD 

Be mindful that if there is a timestamp then it will only include until that date at 00:00 hours so if we need to increase to the next day to get correct readings. 

8) IN - check if value is within a list of options

eg column_name IN (option1, option2,…..) 

Query will return the values that matches the values in the list of options.

9) LIKE - allow us to perform pattern matching against string data with wildcard characters like % or _. 

LIKE is case sensitive, can use ILIKE for case insensitive. 

% will return names that begin or end depending on % in front or behind while _ replaces one character with the underscore. 

eg WHERE name LIKE ‘A%’ 

This will return all names that start with A. If you want all names end with a then use ‘%a’. 

eg WHERE name LIKE ‘Supermarket_’

this will return Supermarket1, Supermarket2…

Can use multiple _ to replace as many characters as required. eg ver#__ can return ver#01, ver#02

I will continue the next lesson notes on group by and having. ^^ see ya!