I am learning Postgresql, working through https://www.udemy.com/course/the-complete-sql-bootcamp/. So far it’s been pretty good: these are my notes for Section 2.
Standard updater is pgadmin https://www.postgresql.org/download/. You could also use DBeaver
Standard query: SELECT [column name] FROM [table name] ORDER BY [table to order by] WHERE [logic statment];
KEY WORDS
DISTINCT: Only show unique value, so if want a list of device types you could search your DB for unique device types so you wouldn’t get 2000 entries of Cisco:
SELECT DISTINCT device_type FROM devices;
SELECT DISTINCT (device_type) FROM devices;
COUNT: returns number of rows that match a specific condition of a query. Such as how many devices do we have, or how many device types do we have:
SELECT COUNT (*) FROM devices;
SELECT COUNT(DISTINCT(device_type)) from devices;
WHERE: Provides search criteria, such as select all IOS devices (The quote marks make a difference here, you want the single quote ones. ‘ ‘)
SELECT * FROM devices WHERE device_type = ‘IOS’;
Or someone wants a drink that costs $3 or less, but doesn’t like pepsi ( In this example we would use not, but all 3 of the AND | OR | NOT are options. Case of names matter.
SELECT * FROM drink WHERE price <= 3 NOT name = ‘Pepsi’;
ORDER BY: What table do you want to sort the result by: for example if you want to sort by the customer’s last name. You can do this by Ascending or Descending. This is done generraly at the end as apparently SQL does things in order.
SELECT * FROM customers ORDER BY last_name [ASC | DESC]
If there are multiple values you need to sort by such as sales men, who they sold to, value of the sale, you might have many people selling to company X, and you want to know at a glance who sold the most to which companies you would order the sales first by company, then by the sales
SELECT company_name, sales, sales_man FROM sales ORDER BY company_name, sales DESC
You can also mix and match ASC/DESC:
SELECT * FROM customers ORDER BY company_name ASC, sales, DESC;
LIMIT: Limit how many rows are returned by the query, the last query to be exicuted, even after ORDER BY. See 10 most recent payments
SELECT * from payment ORDER BY timestamp DESC LIMIT 10;
BETWEEN: Match a value between a range of values high and low:
SELECT * FROM payments WHERE value BETWEEN 100 and 500;
SELECT * FROM payments WHERE value NOT BETWEEN 100 and 105;
IN: You want to check for multiple possible options. So for example your customer hates X car types, you can just look for car types that aren’t in their list:
SELECT * FROM cars_in_stock WHERE type NOT IN (‘Highlander’, ‘Accent’, ‘Lumina’, ‘Viper’)
LIKE | ILIKE: Matching a general pattern: LIKE = case sensitive, ILIKE = NOT case sensitive. As always you can use the NOT with this also.
%: Matches any or no sequence of chars: A value ends with ‘z’: %z
_: Matches any single char: Match Star Wars Episode [any single char, like 1-9]: Star Wars Episode _
select * from fruit where name like ‘%Che%’; So this will return Cherry, however _Che% won’t return Cherry