Query data using SQL
COMMUNICATE WITH AND MANIPULATE DATABASES
I started to learn the SQL syntax through tutorials such as SQLBolt, learnsql.com etc. In this section of my portfolio, I will practice applying my hands-on learning to execute initial analysis and investigate further to answer one-off business questions.
Learning objectives:
• Understand the structure and design of relational databases
• Write SQL statements that edit existing data
Setup & getting started:
In this guide, I will be using MySQL database system, Workbench 8.0 Editor, on a Mac.
Add/embed csv files here…
After installing the MySQL server, setting up a new Connection, and familiarising myself with its interface, I created a new database/schema and gave it the name ‘london’. Then, I used the ‘Table Data Import Wizard’ to import my .csv formatted data.
After installing the MySQL
Tip: understand the structure and design of relational databases, tip note goes here, tip note goes here
Use cases:
For demonstration purposes, I will attempt to cover as many key SQL concepts, from basic statements to more complex queries in order to answer specific questions about data, as follows:
Note: Due to space limitation, I will screenshot only the first few rows/results returned.
1• SELECT WEHERE Clause with Aggregate Functions
Q: Find the number of all-time German customers aged 45-60 and their male/female ratio.
A: Using the COUNT() aggregate function returns the total number of rows that met criteria (134), with the ratio of 110 male : 24 female = 1:0.22
Use cases [Alex]:
Age >= 35 OR Gender = ‘Male’
Last_name LIKE ‘S%o%’ returns names starting with S and has O in it
First_name is NOT NULL
Full_Name IN (‘Jim’, ‘Michael’)
2• SQL AND, OR, NOT
Q:
A:
3• ORDER BY, GROUP BY, LIMIT, OFFSET, TOP
Q:
A:
4•
Q:
A:
• Joins (tables A&B)
[INNER] JOIN; common/overlapping records/values
LEFT JOIN; table A
RIGHT join; table B
FULL JOIN; tables A+B
• Unions
• Case statements
• Updating, Deleting data
• Partition by
• Data types
• Aliasing
• Creating views
• Having vs Group by statements
• Getdate
• Primary key vs Foreign key
Note: all shortcuts I demonstrate in this guide are based on using MS Excel 2019 on a Mac.
Note: all shortcuts I demonstrate in this guide are based on using MS Excel 2019 on a Mac.
Note: I came across an error about “Table name already exists”, and I managed to resolve it by adjusting/renaming the tables’ names from the Formulas > Name Manager
Tip: understand the structure and design of relational databases, tip note goes here, tip note goes here
Key learning points:
• A Database is a collection of data stored in a format that can be easily accessed, and in order to manage Databases, we use Database Management System (DBMS).
• We connect to DBMS and give it instructions for querying or modifying data. The DBMS will execute our instructions and send the results back.
• DBMS are classified into two main categories; relational database which represents a collection of related (two-dimensional) tables, and non-relational (NoSQL).
• We use SQL as the language to work with relational Database Management Systems (RDBMS).
• In SQL, like in math, multiplication and division have higher precedence over addition and subtraction. Also AND operator has a higher order than OR.