Quick SQL 1

Posted on March 2, 2022
Tags: sql

1 Syntax

2 Syntax

Diagram(Stack(Optional(Sequence(Terminal('WITH','#sql-with'),OneOrMore(Sequence(NonTerminal('name'),'AS','(',NonTerminal('select','#sql-select'),')'),',')),'skip'),Sequence(Terminal('SELECT','#sql-select'),Optional('DISTINCT','skip'),Choice(0,'*',OneOrMore(Choice(0,Sequence(NonTerminal('column'),Optional(Sequence('AS',NonTerminal('output_name')))),Sequence(NonTerminal('expression','#sql-valueexpr'),Sequence('AS',NonTerminal('output_name')))),',')),Terminal('FROM','#sql-from'),OneOrMore(Choice(0,Sequence(NonTerminal('table_name'),Optional(Sequence('AS',NonTerminal('alias')))),Sequence('(',NonTerminal('select'),')',Sequence('AS',NonTerminal('alias')))),',')),OneOrMore(Choice(0,Skip(),Sequence(Choice(0,Sequence(',',Comment('oldsyntaxforcrossjoin')),Sequence(Choice(0,'CROSS','NATURAL'),'JOIN'),Sequence(Choice(0,Sequence(Optional('INNER'),'JOIN'),Sequence(Choice(0,'LEFT','RIGHT','FULL'),Optional('OUTER'),'JOIN')),Choice(0,Sequence('ON',NonTerminal('condition')),Sequence('USING','(',OneOrMore(NonTerminal('join_column'),','),')'),Sequence('NATURAL')))),Choice(0,Sequence(NonTerminal('table_name'),Optional(Sequence('AS',NonTerminal('alias')))),Sequence('(',NonTerminal('select'),')',Sequence('AS',NonTerminal('alias'))))))),Sequence(Optional(Sequence(Terminal('WHERE','#sql-where'),NonTerminal('condition')),'skip'),Optional(Sequence(Terminal('GROUPBY','#sql-groupby'),NonTerminal('expression','#sql-valueexpr')),'skip'),Optional(Sequence(Terminal('HAVING','#sql-having'),NonTerminal('condition')),'skip')),Sequence(Optional(Sequence(Choice(0,'UNION','INTERSECT','EXCEPT'),Choice(0,Skip(),'DISTINCT','ALL'),NonTerminal('select')),'skip'),Optional(Sequence(Terminal('ORDERBY','#sql-orderby'),OneOrMore(Sequence(NonTerminal('expression','#sql-valueexpr'),Choice(0,'ASC','DESC')),',')),'skip')),Sequence(Optional(Sequence(Terminal('LIMIT','#sql-limit'),Choice(0,NonTerminal('count'),'ALL')),'skip'),Optional(Sequence(Terminal('OFFSET','#sql-limit'),NonTerminal('start'),Optional('ROWS')),'skip')),Sequence(Optional(Sequence(Terminal('FETCH FIRST','#sql-limit'),NonTerminal('count'),Optional('ROWS'),'ONLY'),'skip'))))

ReLaX

3 Basics

3.1 Make and fill table

  • in mySQL we would do id INT PRIMARY KEY AUTO_INCREMENT
  • in postgres we would do id SERIAL PRIMARY KEY
CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, age INTEGER);
INSERT INTO employee 
    (name,age) 
    VALUES 
        ('bob',47);
        ('ann',32);

id          name        age     
----------  ----------  ----------
1           bob         47    
2           ann         32

3.2 NULL data

  • ('rex') is missing the age parameter, it fallsback to default value NULL
INSERT INTO employee (name,age) VALUES ('rex');

id          name        age     
----------  ----------  ----------
1           rex         NULL

3.3 Basic Query - Order, LessThan, Distinct

SELECT * FROM employee;
SELECT * FROM employee WHERE name='lancor';
SELECT * FROM employee WHERE age>=16 AND hinta<=36;
--ORDER defaults ASC smallest(top) to largest(bottom)
SELECT * FROM employee ORDER BY name; 
--largest(top) to smallest(bottom)
SELECT * FROM employee ORDER BY name DESC; 
--remove repeats
SELECT DISTINCT age FROM employee; 

SELECT * FROM employee WHERE id IN (1,5,7,4);

SELECT name, 
	CASE WHEN (monthlymaintenance > 100) THEN
		'expensive'
	ELSE
		'cheap'
	END as cost
	from cd.facilities;

3.4 Update data

UPDATE employee SET name='anna', age=9 WHERE id=2;
UPDATE employee SET age=age+1 WHERE id=2;

UPDATE Tuotteet SET age=age+1;

3.5 Delete data

  • Note: Drop != Delete, Delete clears data, Drop destroys the table
DELETE FROM employee WHERE id=5;

DELETE FROM employee; --deletes all rows, results in empty table

3.6 Drop

DROP TABLE employee;

4 DateTime

4.1 TIMESTAMP

  • You can also set default as CURRENT_TIMESTAMP which means if you insert with no field, it will just auto-fill using the current time.
TYPE: timestamp(40)	
2023-01-04 03:04:45.245619
SELECT mytime FROM employee WHERE mytime = '2023-01-04 03:04:45.245619';

5 Foreign Key

erDiagram hero ||--o{ heroInventory : heroHash hero { int id int heroHash } heroInventory { int id int heroHash string stuff }
CREATE TABLE hero(
	id INT PRIMARY KEY AUTO_INCREMENT,
	heroHash INT
);

CREATE TABLE heroInventory (
	id INT PRIMARY KEY AUTO_INCREMENT,
	heroHash INT,
	FOREIGN KEY(heroHash) REFERENCES hero(heroHash),
	stuff VARCHAR
);

6 Counting Scenarios

6.1 Count Num Rows

SELECT COUNT(*) FROM employee; --Counts all rows

COUNT(*)
----------
5

6.2 Count Non-Null Rows

SELECT COUNT(age) FROM employee;

COUNT(age)
----------
4

6.3 Count Unique Occurences

SELECT COUNT(DISTINCT age) FROM employee;

COUNT(DISTINCT age)
----------
2

7 String Scenarios

7.1 Concat

select surname || ' aka ' || firstname as name from cd.members 
--"BOB" "JONES"
--outputs "BOB aka JONES"

7.3 Regex matching

select memid, telephone from cd.members where telephone similar to '%[()]%';

7.4 Padding string

select lpad(cast(zipcode as char(5)),5,'0') zip from cd.members order by zip 

--input: 43
--output: 00043

8 Grouping

8.1 Group same names then count each

select substr (mems.surname,1,1) as letter, count(*) as count 
    from cd.members mems
    group by letter
    order by letter          

letter	count
B	5
C	2
D	1
F	2
G	2

9 1-by-1 table = value

select firstname, surname, joindate
	from cd.members
	where joindate = (select max(joindate) from cd.members);

--(select max(joindate) from cd.members)  is a 1-by-1 table

10 Recursion

WITH RECURSIVE counter AS (
    SELECT 1 AS i
    UNION ALL
    SELECT i+1 FROM counter WHERE i < 5
    )    SELECT i FROM counter;

11 INNERJOIN

--select those that recommend themselves
select distinct recs.firstname as firstname, recs.surname as surname
	from 
		cd.members mems
		inner join cd.members recs
			on recs.memid = mems.recommendedby
order by surname, firstname;

11.1 transitivity

  • 3rd degree transitive relation
Herietta  --recBY-> Matthew  --recBY-> Gerald --recBY-> Darren
Ramnaresh --recBY-> Florence --recBY-> Ponder --recBY-> Burton
Douglas   --recBy-> David    --recBY-> Janice --recBY-> Darren
select distinct recs.firstname as rootrecommender, abc2.firstname as thirdhoprecommended
	from 
		
		(cd.members mems
		inner join cd.members recs
			on recs.memid = mems.recommendedby)  --first hop
			inner join cd.members abc
				on mems.memid = abc.recommendedby --second hop
					inner join cd.members abc2
						on abc.memid = abc2.recommendedby --third hop
;    
--using SELECT, we can only choose information about those with
--level 3 nested recommendations.
--BUT THAT also means out of level 3 we can choose 1,2,3 recommendations