SQL Cheat Sheet

Almost all Data Scientist job interviews include a SQL test round. It’s pretty handy to refer a cheat sheet with all the important queries. There are a lot of cheat sheets floating around in the web, but I thought I will create one for myself. These are all basic command statements which are still used for most day to day functions by data scientists and analysts. Here’s a list of all the “important” commands – a quick reference before a job interview.

1. Create a database

create database db1;

2. Create a table

create table customers (
customerID int,
name varchar(255),
age int,
occupation varchar(255) 
);

3. Drop a table

drop table customers;

4. Truncate a table

truncate table customers;

5. Alter table

alter table customers
add salary int;
alter table customers
drop column age;
alter table customers
modify salary float;

6. Data insertion

insert into customers (customerID, name, age, occupation)
values (187, 'Jack', 54, 'Farmer');

7. Simple select

select distinct customerID 
from customers
where occupation ='Farmer' or occupation='Scientist' 
or (age>=40 and age<25) 
order by customerID desc;
select name from customers
where occupation is null;

8. Update table

update customers
set occupation = 'Organic Farmer'
where customerID = 187;

9. Delete records

delete from customer
where customerID = 187;

10. Advanced select

select customerID, max(age) from customers
group by customerID
having age>35
order by age desc;

11. Simple joins

select orders.orderID, customers.name
from orders
right join customers on orders.customerID = customers.customerID;

12. Advanced joins

select orders.orderID, customers.name, shippers.name
from ((orders
inner join customers on orders.customerID = customers.customerID)
left join shippers on orders.shipperID = shippers.shipperID);

References: www.w3schools.com

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s