khalido.org

every blog needs subheader text

SQL basics

posted tagged: data sql

A relational database is just a collection of 2d data tables. The relational part means is that these 2d tables are connected to one another by sharing a data field, e.g a CRM table and a orders table will both have a customer ID.

Joins

join, or inner join - joins data found in both tables

However, we often have asymetric data in tables so:

  • left join - keeps all rows from the left table
  • right join - keeps all rows from the right table
  • full join - all rows from both tables are kept

The basic query, to select all the things, limited for sanity:

select * from table limit 5

A basic select query looks like

SELECT Car, sum(domestic_sales + international_sales) AS total_sales,
FROM mytable
	-- only looking at Hondas
	WHERE Car like "Honda%"
	-- having is similar to where but used for group by
	GROUP BY widget HAVING continent = "Asia"
	ORDER BY total_sales DESC
	limit 5;

Subqueries is a piece of cake, just add brackets. The query inside the brackets resolves to the avg sale price of a widget, and we can now use that to filter for widgets which cost more than the avg sale price.

select * from widgets
where widget_cost >
	(select avg(sale_price)
	from wigets);

Resources to learn sql from:

There are tons of great resources out there

Interactive easy to use sites to learn basic sql: