Selecting and Retrieving Data with SQL

Question 1

What is SQL?

Answer 1

The standard computer language of database management systems

SQL for Data Science Week 1

Question 2

How does SQL differ from other computer languages?

Answer 2

SQL is a descriptive and non-procedural language. You cannot write complete applications with SQL

SQL for Data Science Week 1

Question 3

What three things is SQL used for?

Answer 3

1. Read/retrieve data
2. Write data
3. Update data

SQL for Data Science Week 1

Question 4

Examples of professions that use SQL

Answer 4

1. Backend Developer
2. QA Engineer
3. DBA
4. Data Analyst
5. System Admin
6. Data Architect
7. ETL Developer
8. Systems Engineer
9. Data Scientist

SQL for Data Science Week 1

Question 5

Compare and contrast roles of database administrator and data scientist

Answer 5

DBA
1. Manages entire database
2. Gives permissions to users
3. Determines who has access to what data
4. Manages and creates tables
5. Uses SQL to query and retrieve data

Data Scientist
1. End user of a database
2. Primarily use SQL to query and retrieve data
3. May create their own table or test environment
3. Combine multiple data sources together
4. Write complex queries for analysis

SQL for Data Science Week 1

Question 6

Explain the importance of knowing which SQL syntax you’re using in a given database

Answer 6

Think of SQL as the interpreter between you and the database. There are slight syntax differences amongst systems. You must know the proper syntax to use to “talk” to the database.

SQL for Data Science Week 1

Question 7

How do you limit the number of records returned?

Answer 7

SELECT columns you want
FROM table
LIMIT number of records

Just writing along sentence so it will left justify

SQL for Data Science Week 1

Question 8

What is the different syntax amongst SQLite, Oracle and DB2 for limiting records?

Answer 8

SQLite
SELECT columns you want
FROM table
LIMIT n

Oracle
SELECT columns you want
FROM table
WHERE ROWNUM <=n

DB2
SELECT columns you want
FROM table
FETCH FIRST n ROWS ONLY

n=number of rows

SQL for Data Science Week 1

Question 9

Why use comments?

Answer 9

1. To reference when looking at code you have not reviewed in a while
2. To comment out a section of code you no longer want/need to use but aren’t ready to delete
3. To troubleshoot queries systematically
4. To make your code easier to share

SQL for Data Science Week 1

Question 10

How do you comment sql code?

Answer 10

• –Two dashes comments out a single line
• /* at the beginning of the section and */ at the end of the section comments out a section of sql

Don’t overdo comments. Add them where your sql doesn’t follow the normal flow. For example, you don’t need to explain what the select does and then what the from does and then what the group by does. These are self explanatory for those that use sql.

SQL for Data Science Week 1