SQL cheatsheet for your Interview!
Posted inData-Analytics Data-Science Interview-prep SQL

SQL cheatsheet for your Interview!

No Comments

Essential SQL Cheat Sheet: Quick Reference for Data Analysts

For this practice we are going to use WORLD database from mysql.com. you can download from here: https://dev.mysql.com/doc/index-other.html

 
After we import the database and choose the databse from mysql workbench, let’s see the tables inside the database. As we can see there are three tables in the world database -> City, Country and CountryLanguage. We will perform our practice queries on these tables:
 
 

 

 

1. Data Retrieval (SELECT Statements)

Used to fetch data from a database.

 

  • Basic SELECT: Retrieves all columns from a table
           SELECT * FROM table_name;
           select * from city/country/countrylanguage;
           I chose city, it will get you all columns from city table

 
 
 
  • SELECT Specific Columns: Fetch only specific columns. 

           SELECT column1, column2 FROM table_name;
           select Name from city; will give you the below result:    

    
 

2. Filtering Data (WHERE Clause)

Used to filter records based on conditions.

  • WHERE Clause: Retrieve rows based on conditions.
          SELECT * FROM table_name WHERE column1 = ‘value’;
 
  • WHERE with Multiple Conditions: Use AND/OR to combine conditions.
          SELECT * FROM table_name WHERE column1 = ‘value’ AND column2 > 100;
 
 
WHERE clause operators:
1. Arithmatic: + , – , * , / , % (modulus/ Remainder)
2. Comparison: =, != , > ,< , >=, <=
3. Logical: AND, OR , NOT , IN, BETWEEN , ALL , ANY
4. Bitwise:  & (bitwise and) , | (bitwise OR)
 
 

3. Sorting Data (ORDER BY)

Sort query results by one or more columns in ascending or descending order.

  • Ascending Order:
           SELECT * FROM table_name ORDER BY column1 ASC;
 
  • Descending Order:
          SELECT * FROM table_name ORDER BY column1 DESC;
 

 

4. Limiting Rows (LIMIT)

Retrieve only a specific number of rows.

  • Limit the Number of Results:
          SELECT * FROM table_name LIMIT 10;
  here we are getting names of India, West Bengal based on Population in decsending order, LIMIT 3 will only show top 3 records

5. Aggregate Functions

Used to perform calculations on data. Perform a calculation on a set of values, and return a single value.

  • COUNT: Counts the number of rows.
          SELECT COUNT(*) FROM table_name;
 There are 46 entries in West Bengal, India
 

  • SUM: Adds up the values in a column.
         SELECT SUM(column_name) FROM table_name;
 

  • AVG: Calculates the average value.
          SELECT AVG(column_name) FROM table_name;
 
 Similarly there are max(), min() etc.
 
 

6. Grouping Data (GROUP BY)

Group rows with similar values and perform aggregation.

  • GROUP BY with Aggregate Functions:
          SELECT column1, COUNT(*) FROM table_name GROUP BY column1;
 
 
Number of Names in each continent:
 
 

7. Filtering Groups (HAVING)

Filter grouped data after using GROUP BY.

  • Using HAVING to Filter Aggregated Results:
          SELECT column1, SUM(column2) FROM table_name GROUP BY column1 HAVING SUM(column2) > 1000;
 
 Where vs Having: 1.Where is used before Group By condition where as HAving is used after Group By.
  2. Where applies condition on Rows on another hand HAving applies condition on groups!
 
From the countrylanguage table find out countries having more than 7 languages:
 
 
By now you might be confused with the flow of queries, so let me simplify the flow:
 Select -> from -> Group by -> Order By.
 
select column (s)
from table_name
weher condition
group by
having
order by column (s) asc/desc
 

8. Joining Tables (JOIN)

Combine rows from two or more tables.

  • INNER JOIN: Returns rows when there’s a match in both tables.
          SELECT * FROM table1
          INNER JOIN table2 ON table1.column = table2.column;
 
    
  • LEFT JOIN: Returns all rows from the left table and matched rows from the right table.
          SELECT * FROM table1
          LEFT JOIN table2 ON table1.column = table2.column;
 
 
 
  • RIGHT JOIN: Returns all rows from the right table and matched rows from the left table.
          SELECT * FROM table1
          RIGHT JOIN table2 ON table1.column = table2.column;
 
Similarly we can perform right join.
 
Full join: In mySql full join command doesn’t work but we can use UNION. 
Left join union Right Join..
 
 
Left Exclusive join: This filters the results to show only rows where there is no match (i.e., where b.Name is NULL), effectively performing the left exclusive join.
 

Self Join: A self join is used when you need to join a table to itself, usually to compare rows within the same table. It can be helpful in a variety of situations where relationships within the same dataset need to be compared.

 
Self joins are powerful in cases where the relationships are internal to the table and no other tables can provide the necessary context.
 
 
 

 

                                                

 

9. Inserting Data (INSERT INTO)

Add new rows into a table.

  • Insert Single Row:
           INSERT INTO table_name (column1, column2) VALUES (‘value1’, ‘value2’);
           Insert multiple rows: INSERT INTO table_name (column1, column2) VALUES (‘value1’, ‘value2’), (‘value3’, ‘value4’);
 
 

10. Updating Data (UPDATE)

Modify existing rows in a table.

  • Update a Specific Row:
          UPDATE table_name SET column1 = ‘new_value’ WHERE column2 = ‘condition’;

11. Deleting Data (DELETE)

Remove rows from a table.

  • Delete Specific Rows:
          DELETE FROM table_name WHERE column1 = ‘condition’;
 

12. Table Creation (CREATE TABLE)

Create a new table with specific columns and data types.

  • Basic Table Creation:
          CREATE TABLE table_name (
           column1 datatype,
           column2 datatype,
           column3 datatype
           );
 

13. Table Alteration (ALTER TABLE)

Modify the structure of an existing table. (To change the schema)

  • Add a New Column:
          ALTER TABLE table_name ADD column_name datatype;
  • Drop a Column:
          ALTER TABLE table_name DROP COLUMN column_name;
 
  Truncate: It deletes data of a table whereas drop deletes the full table.
   TRUNCATE TABLE table_name;
 
 

14. Indexing

Indexes improve the speed of data retrieval.

  • Create Index:
          CREATE INDEX index_name ON table_name (column_name);
 

15. Subqueries

Queries within queries.

  • Subquery in SELECT:
          SELECT column1, (SELECT MAX(column2) FROM table_name2) AS max_value FROM table_name1;
 

16. Aliasing

Use aliases to simplify queries.

  • Column Alias:
          SELECT column1 AS ‘alias_name’ FROM table_name;
  • Table Alias: 
           SELECT t.column1 FROM table_name AS t;
 

17. Distinct Values (DISTINCT)

Select only distinct (unique) values.

  • Distinct in SELECT:
          SELECT DISTINCT column_name FROM table_name;
 

18. Set Operations

Combine the results of two or more SELECT queries.

  • UNION: Combines the results of two queries and removes duplicates.
    SELECT column1 FROM table1
    UNION
    SELECT column1 FROM table2;
  • UNION ALL: Combines the results of two queries and keeps duplicates.
    SELECT column1 FROM table1
    UNION ALL
    SELECT column1 FROM table2;

19. Conditional Statements (CASE)

Return values based on conditions.

  • CASE Statement:
    SELECT column1,
    CASE
    WHEN condition THEN 'result1'
    ELSE 'result2'
    END
    FROM table_name;

20. Data Export

Export query results to a file.

  • Export Data to CSV:
    SELECT * FROM table_name INTO OUTFILE '/path/to/file.csv'
    FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

21. Date Functions

Work with dates and times.

  • Get Current Date:
    SELECT CURRENT_DATE;
  • Format Date:
    SELECT DATE_FORMAT(column, '%Y-%m-%d') FROM table_name;

22. Handling NULL Values

Work with NULLs in your queries.

  • Check for NULL:
    SELECT * FROM table_name WHERE column IS NULL;
  • Replace NULL with a Value:
    SELECT IFNULL(column, 'default_value') FROM table_name;

23. String Functions

Manipulate text data.

  • Concatenate Strings:
    SELECT CONCAT(column1, ' ', column2) FROM table_name;
  • Substring Extraction:
    SELECT SUBSTRING(column, 1, 5) FROM table_name;

24. Data Type Conversion

Convert between data types.

  • Cast to a Specific Type:
     
    SELECT CAST(column AS datatype) FROM table_name;

25. Transactions

Manage multiple SQL operations in a single transaction.

  • Start a Transaction:
     
    START TRANSACTION;
  • Commit Changes:
    COMMIT;
  • Rollback Changes:
    ROLLBACK;
 There was a spelling mistake in the city table, It was West Bengali instead of West Bengal. So corrected that with the help of UPDATE and transactions. here’s the snippet.
One more thing is, sometimes SQL’s safe mode doesn’t let you change or update few parameters. In that case turn off the safe mode SET SQL_SAFE_UPDATES=0; [1 to turn on again]
 

 
 
 Almost completed the basic SQL part, now will delve into some advanced topics like Sub-queries, Stored procedures, Triggers, Window functions, Rank functions etc.. 
 
 
            
           
 
 
 
 
 
 
 
 
           
 
 
 
 
 
       
 
 
 
 
 

Comments

No comments yet. Why don’t you start the discussion?

Leave a Reply

Your email address will not be published. Required fields are marked *