SQL (for Data Science)

Created	@Nov 28, 2020 12:22 PM
Tags

General SQL Commands

SQL is needed to react or converse with any RDBMS.

The many used SQL commands are:

create table

CREATE TABLE <name> (
	---------------------
	---------------------
);

insert into

INSERT INTO <table_name> (...) VALUES (...);

select

SELECT <stuff> FROM <table_nme>;

/*Some examples are as follows*/

1. SELECT DISTINCT name FROM celebs;

2. SELECT name AS 'Title' FROM movies;

3. SELECT * FROM movies WHERE rating > 10;

4. SELECT * FROM movies WHERE rating IS NOT NULL;

5. SELECT * FROM movies WHERE name BETWEEN 'A' AND 'J';

6. SELECT * FROM movies WHERE rating > 10 AND release_date BETWEEN 
		1970 AND 1979;

7. SELECT * FROM movies WHERE rating > 10 OR name LIKE '%S_xy%' 
		ORDER BY release_date DESC /*or ASC*/ LIMIT 10;

8. SELECT name,
			CASE
					WHEN rating > 8 THEN 'Fantastic'
					ELSE 'Avoid'
			END AS 'Judgement'
		FROM movies;

alter table

ALTER TABLE <name> ADD COLUMN <details>;

/*
<details> may include stuff like id TEXT PRIMARY KEY (or DISTINCT, 
NOT NULL) etc.  
*/

update

UPDATE <table_name> SET <data_values> WHERE <conditions>;

delete from

DELETE FROM <table_name> WHERE <condition>;

Aggregate Functions

1. AVG()
2. MAX() / MIN()
3. SUM()
4. ROUND( ,<num>)
5. group by 
6. having /* similar to where but for groups*/

Intermediate SQL

Joining

SELECT table1.name, count(*)
FROM table1
JOIN table2
  ON table1.id = table2.id;

SELECT table1.name, count(*)
FROM table1
LEFT JOIN table2
  ON table1.id = table2.id;

SELECT shirts.shirt_color,
   pants.pants_color
FROM shirts
CROSS JOIN pants;

Union

SELECT *
FROM table1
UNION
SELECT *
FROM table2;

With

WITH previous_results AS (
   SELECT ...
   ...
   ...
   ...
)
SELECT *
FROM previous_results
JOIN customers
  ON _____ = _____;

Installing SQLite

Go Here

List of SQL Commands

Table of Contents

General SQL Commands

Intermediate SQL

Installing SQLite