Scribd
Upload
57 views2 pages

Excel SQL Revision Sheet

The document provides a revision sheet for Excel and SQL, outlining key formulas, data handling techniques, chart creation, and pivot table usage for Excel, as well as basic queries, filtering, grouping, joins, aggregates, subqueries, and case statements for SQL. It serves as a quick reference guide for users to enhance their skills in both applications. The information is organized into sections for easy navigation and understanding.

Uploaded by

rajusakshenada
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
57 views2 pages

Excel SQL Revision Sheet

The document provides a revision sheet for Excel and SQL, outlining key formulas, data handling techniques, chart creation, and pivot table usage for Excel, as well as basic queries, filtering, grouping, joins, aggregates, subqueries, and case statements for SQL. It serves as a quick reference guide for users to enhance their skills in both applications. The information is organized into sections for easy navigation and understanding.

Uploaded by

rajusakshenada
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Excel + SQL Revision Sheet

Excel Revision

1. Common Excel Formulas:

- =IF(condition, value_if_true, value_if_false)

- =VLOOKUP(lookup_value, table_array, col_index, [range_lookup])

- =XLOOKUP(lookup_value, lookup_array, return_array)

- =SUM(range), =AVERAGE(range), =COUNT(range)

- =TEXT(date, "dd-mm-yyyy"), =TODAY(), =DATEDIF(start_date, end_date, "D")

2. Data Handling:

- Filter: Ctrl + Shift + L

- Conditional Formatting: Home > Conditional Formatting

- Sorting: Data > Sort A-Z / Z-A

- Create Table: Ctrl + T

3. Charts & Visuals:

- Use Insert > Recommended Charts

- Common: Pie, Bar, Line

4. Pivot Tables:

- Insert > Pivot Table

- Drag fields to Rows, Columns, Values

5. Dropdowns:

- Data > Data Validation > Allow: List > Enter values or select range
Excel + SQL Revision Sheet

SQL Revision

1. Basic Queries:

- SELECT column1, column2 FROM table;

- WHERE, ORDER BY, LIMIT

2. Filtering and Grouping:

- WHERE condition

- GROUP BY column HAVING condition

3. Joins:

- INNER JOIN: Shows matching records in both tables

- LEFT JOIN: Shows all from left + matched from right

4. Aggregates:

- COUNT(), SUM(), AVG(), MAX(), MIN()

5. Subqueries:

- SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

6. CASE Statement:

- SELECT name,

CASE

WHEN salary > 50000 THEN 'High'

ELSE 'Low'

END AS salary_group

FROM employees;

You might also like