31 July 2023
Guest Blog – Building an SQL Framework
Ivy Li is one of our Women’s Tech Hub members and has agreed to share some of her learnings with others, which we love.
In her words:
I do believe computer science should not be tricky for everyone because we use it every day and we should recognise it. What I am doing is to see if I can always introduce one tool in around 200 words
😁 I want to share how I build a framework in mind for SQL. The first thing is to understand why we need SQL.
😉 Using a simile, SQL is the design of warehouses, and the initial step of the supply chain between the organised warehouse and displayed shops.
💁♀️ So, how does SQL work? Becoming organised (Data Definition, Data Manipulation, Transaction Control, and Data Control) and then choosing the exact products amongst millions of stored products (Data Query) is what SQL does.
🤓 I need to understand more details about the 5 categories.
- Data Definition: design a storage unit with detailed requirements. Each database has some tables, each table has some columns with a certain maximum capacity that holds one data type (e.g., VARCHAR(size), INTEGER(size)). CREATE, INDEX, ALTER, DROP, TRUNCATE, RENAME
- Data Management: populate data in the tables. Load the data delivered from the production, add more with time, and replace the unqualified data. The process is dynamic. INSERT, UPDATE, DELETE
- Transaction Control: some repetitive tasks can be put together for efficiency and withdrawn in case of errors. COMMIT, ROLLBACK, SAVEPOINT
- Data Control: permission control for safety. Only specified users can access the database. GRANT, REVOKE
- Data Query: choose data with filters, adjustments, and simple mathematics. a. Select information for a specific date, people, or region. SELECT, DISTINCT, WHERE, IN, LIKE; b. Some tables have relationships and can be merged. JOIN, UNION, INTERSECT, EXCEPT; c. Aggregate data in groups. GROUP BY, SUM, MAX; d. Adjust raw columns for a new format. CONCAT, DATEADD, LENGTH, SUBSTRING; d. Conditional output. CASE WHEN, IS NULL and so on.
😚 In short, database administrators are responsible for the database design and management while data analysts find the right data using the last query language. Now that I know what SQL does and how it works, I can practice getting data using these functions.
If any of our members or women looking to get into or are already in the tech industry and wish to share their knowledge then please feel free to do a guest blog with us – we’re here to support the community.