SQL Notes – Introduction
What is SQL?
Structured Query Language (SQL) is a standard computer language for relational database management and data manipulation.
Used to query, insert, update and modify data.
Pronounced as “sequel” or S-Q-L.
Used to communicate with databases;
SQL is non-procedural language:
- Cannot write complete applications
- Simple, but powerful
How is SQL used?
Write data – add data to a table;
Update data – insert new data;
Who uses SQL?
- Backend Developer
- Data Architect
- QA Engineer
- ETL Developer
- Database Admin (DBA)
- Data analyst
- System Admin
- System Engineer
- Data Scientist
DBA or Data Scientist
|Manages/governs entire database||End user of a database|
|Gives permissions to users||Use SQL to query and retrieve data|
|Determines access to data|
|Manages and creates tables|
|Uses SQL to query and retrieve data|
How do Data Scientists use SQL?
May create their own table or test environments;
Combine multiple sources together;
Write complex queries for analysis.
Relational Database Management Systems
- SQL Server
- IBM DB2 Oracle
- Sybase ASE
- Microsoft SQL Server
- Apache Open Office Base
Write syntax depends on what DBMS you are using.
Each DBMS has its own dialect.
SQL can translate.
Tweak based on the dialect your DBMS speaks.
Thinking about your data
Understand the business process or subject matter the data is modeled after
Know the business rules
Understand how your data is organized and structured in the table(modeled).
Why this is worthwhile
Get more accurate results,
Speed up your work,
Have less rework.
|Database||A container to store organized data|
|Tables||A structured list of data or a specific type|
|Column||A single field in a table|
|Row||A record in a table|
What is data modeling?
Organizes and structures information into multiple, related tables.
Can represent a business process or show relationships between business processes.
Should closely represent real world.
Types of data modeling
Models for prediction built by data scientist.
Data model as data tables represented and organized in a database.
Evolution of data models
|1990||Extended relational(O/R DBMS)|
|2009||NoSQL – Not only SQL|
Relational vs. Transactional Models
|Relational Model||Transactional Model|
|Allows for easy querying and data manipulation in an easy, logical and intuitive way.||Operational database – insurance claims within a healthcare database|
Data model building blocks
|Entity||Person, place thing or event, Distinguishable, unique and distinct|
|Attribute||A characteristic of an entity|
|Relationship||Describes association among entities. One-to-many,Many-to-many,One-to-one|
- One-to-many: customer to invoices.
- Many-to-many: student to classes.
- One-to-one: manager to store.
- Show relationships
- Business process
- Represented visually
- Show links (Primary keys)
Some examples of ER Diagram