PRACTICAL CHALLENGES ON QUERIES

SQL queries are used to retrieve data from a database so as to be able to analyze the data and make conclusions.

I am going over the code academy pro content https://www.codecademy.com/paths/data-science,they are offering scholarships for 90 days to anyone with a student email.You can choose any path you’re interested in just like I did with data science.

I will share some of the practical project questions I encountered and github links to their solutions. The following queries were done on users table.

1.There was an A/B test performed on users that used cute animal clipart to encourage users to sign up. We’d like to see how the group that was shown ‘bears’ is performing.Find the emails of the users who received the ‘bears’ test

SELECT email
 FROM users
 WHERE test = 'bears';

2.A total of 4 advertising campaigns were run over this period.There were two sets of ad copy (set 1 and set 2) and both were run on two search engine sites (AAA and BBB).
The resulting campaign values are:
  
     AAA-1
     AAA-2
     BBB-1
     BBB-2
  
 Find all the emails of all users who received a campaign on website BBB 

SELECT email
 FROM users
 WHERE campaign LIKE 'BBB%';

3.Find all the emails of all users who received ad copy 2 in their campaign.
Link to solution https://gist.github.com/c5363650426e4f17a52290bf3542bf89

4.Find the emails for all users who received both a campaign and a test.These users will have non-empty entries in the campaign and test columns.

SELECT email,campaign,test
 FROM users
 WHERE campaign IS NOT NULL
 AND test IS NOT NULL;

The following queries were done on orders table.

5.Start by getting a feel for the orders table:

SELECT *
FROM orders
LIMIT 10;

6.Use SELECT DISTINCT order_date FROM orders DESC;
DISTINCT to find out all the unique order_date values in this table
Link to the solution https://gist.github.com/452c4062079332257323b7b6ca39f28a

7.The special_instructions column stores the data where Davie’s Burgers customers leave a note for the kitchen or the delivery.Instead of selecting all the columns using *, write a query that selects only the special_instructions column.Limit the result to 20 rows.

SELECT special_instructions
FROM orders
LIMIT 20;

8.Return the special instructions that are not empty?
Link to solution https://gist.github.com/8b4e8c1fe5ae92bbed521019f82029c0

9.Sort the instructions in alphabetical order (A-Z).
Link to solution https://gist.github.com/e30fccdf2e3058cd142db693e294aa55

10.Search for special instructions that have the word ‘sauce’.

SELECT special_instructions
FROM orders 
WHERE special_instructions LIKE "%sauce%";

11.Search for special instructions that have the word ‘door’.
Link to solution https://gist.github.com/0615e7350467615538cae172c5728edf

12.Instead of just returning the special instructions, also return their order ids.
For more readability:
Rename id as ‘#’
Rename special_instructions as ‘Notes’

SELECT id AS '#',
special_instructions AS 'Notes'
FROM orders;

Go ahead and learn something as well,my progress log and and an ad from data analyzed in orders table.

• 

•