sql workshop
DESCRIPTION
TRANSCRIPT
Undergraduate Economics Association
1
By: Spencer Petitti (VP of Editorial Content)
Week 6: SQL
Undergraduate Economics Association
SQL Workshop
1. What is SQL?2. What practical applications does it have?3. Syntax and Use
2
Undergraduate Economics Association
SQL
3
• A very high level programming language, used to access information stored on a database system
• SQL let you manipulate data easily through a series of commands
• Why use SQL?• Relational Databases vs. Flat Database
• http://office.microsoft.com/en-us/training/relational-means-data-in-multiple-tables-RZ010253998.aspx?section=3
Undergraduate Economics Association
SQL
4
• Main ideas:• Data is stored in tables
• Fields (Columns)• Records (Rows)
Undergraduate Economics Association
Practical Applications
5
1. MySQL, SQLite1. Relational Database Management Systems
2. Microsoft Access1. ACCDB file type
3. SAS, STATA1. .dta file type, not a database
Undergraduate Economics Association
Practical Applications
6
1. SQLite
Undergraduate Economics Association
Practical Applications
7
Undergraduate Economics Association
Syntax (selecting)
8
• The Basics (the SQL query):• SELECT
• Choosing your data to manipulate or view by field (column)
• FROM• Choosing your data by table
• WHERE• The parameters of your query, will define
what results you see• http://sqlzoo.net
Undergraduate Economics Association
Syntax (updating)
9
• A little more advanced:• UPDATE
• Choosing your data by table • SET
• Choosing your data to manipulate by field (column)
• WHERE• The parameters of your query, will define
what results you see• IF YOU DO NOT SET A WHERE CLAUSE, YOU
LOSE ALL DATA!• An update query will not show results without a select
query!
Undergraduate Economics Association
Syntax (inserting)
10
• A little more advanced:• Two ways:
• INSERT INTO• Choosing your data entry point manipulate
by table(field,field)• SELECT
• Choosing your data by field• FROM
• Choosing your data by table
OR• INSERT INTO• VALUES ('2012',’Sydney'),('2016',’Athens'),
(‘2020’,’Beijing’);
• An update query will not show results without a select query!
Undergraduate Economics Association
Primary Key and Foreign Key
11
• The Primary Key is a unique identifier that defines each row• Ex. Your BU ID number
• Data without an assigned Primary Key will create errors in your database• Cannot contain NULL values
• Foreign Keys point to Primary Keys in other tables, linking them together• Persons linked to Orders table, cannot create
new order without P_id link
Undergraduate Economics Association
Syntax
12
• Google is your friend• http://stackoverflow.com• “Introduction to Access SQL”, Microsoft website
• Also: Microsoft Office training courses• http://www.w3schools.com/sql
Undergraduate Economics Association
Upcoming:
13
• Email reports to [email protected]• The UEA will be compiling the information into a
release for the journal, each individual will be credited for their contribution