SQL for non-programmers

Question 1

What does SQL stand for?

Answer 1

Structured Query Language

Question 2

What is SQL used for?

Answer 2

Programming language used to communicate with and manage relational databased

Question 3

SQL is based on _______-_______ commands

Answer 3

English-language
It avoids having to learn complex programming syntax

Question 4

What are the 3 things you need to utilize SQL?

Answer 4

1. Database management system
2. Tool to interact with Database
3. Data

Question 5

What is SQLite.org?

Answer 5

A free, open-source, and user-friendly system

Question 6

What is SQLiteStudio?

Answer 6

Free graphical user interface
Designed to make these databases more friendly and accessible

Question 7

What are 4 things that SQL allows you to do with data?

Answer 7

Define
Manipulate
Control
Query

Question 8

What are relational databases?

Answer 8

They are a type of database that organized data into tables, with each table consisting of rows and columns
Each row represing a unique record of data and each column represents an attribute or characteristics of that data

Question 9

How can tables be linked or related?

Answer 9

They can with common keys. Common keys are data that is common to each table and alow you to create bridges that connect one table to another.

Question 10

What are some of the relational database solutions on the market today?

Answer 10

MySQL
Microsoft SQL Server
PostgreSQL
SPLite

Relational databases are flexible, scalable solution for complex data

Question 11

How are relational databases designed/structured?

Answer 11

Attempt to minimize redundancies and maximize efficiency

Question 12

Relational databases are built using a process called normalization, explain

Answer 12

To organize data in a database so that each piece of data is only stored in one place. Makes it more efficient and accurate

To prevent data duplication and inconsistencies

Question 13

Define

DQL or Data Query Language

Answer 13

An SQL command family, allows you to retrieve data from a database for both broad population and data professionals alike

Most common command family and maybe only one you’ll need

Question 14

What are the four main command families?

Answer 14

DDL: Data Definition Language
DCL: Data Control Language
DML: Data Manipulate Language
DQL: Data Query Language

Question 15

What is the process of normalization used for?

Answer 15

To organize data more optimally and efficiently in a database

Question 16

What is the only statement in the DQL command family?

Answer 16

The SELECT statement

This makes the program very accessible

Need at least 2 pieces of info

Question 17

What requires a SELECT statement?

Answer 17

What data you want to select
From where do you want to select it

Question 18

How do you close a statement with the FROM command?

Answer 18

Use semicolon
Ex: FROM oliveoil.sales;

Question 19

What does the
SELECT *
command do?

Answer 19

It pulls everything from the table, we are usually not interested in pulling everything from the data, only a subset.

Question 20

Use LIMIT for?

Answer 20

LIMIT will limit the output to the amount of rows you tell it to. Ex:
LIMIT 10;

10 is the number of rows you are limiting it to

Question 21

When you are hit with many repeats of the same information, use
SELECT DISTINCT

Answer 21

This will only show you things that are unique from that column. Ex:
SELECT DISTINCT prod_category
FROM oliveoil.sales;

Question 22

What is the videos best practice #1?

Answer 22

• SQL keywords are uppercase (SELECT)
• Column and table names are lowecase (employee-name)

Question 23

Videos best practice #2?

Answer 23

• Column and table names with 2 or more words are separated by an underscore (employee_name)

Make sure you use the same naming convention on all tables and codes

Question 24

Videos best practice #3?

Answer 24

• Use whitespace intentionally, it improves readability and maintainability
• Use spaces to separate keywords, operators and punctuations

Use line brakes to separate clauses or column names in your query

Question 25

Video best practice #4?

Answer 25

• Use comments to explain sections of statements
• Use comments to clock areas of statements from being executed

These are ignored at execution

Question 26

What is the ORDER BY clause?

Answer 26

This is used to sort data in ascending or descencing order based on a column

Helpful to draw insights or conclusions

Helps find patterns by ordering data
Default order is ascending

Question 27

How do you specify if you want your ORDER BY clause to be descending or ascending?

Answer 27

Ascending is by default, but can specify with adding in caps ASC (from smallest to largest)
Descending you have to specify by adding DESC (from largest to smallest)
Ex:
ORDER BY order_total DESC;

Always select your query and press the play button

To open up a file, select the folder icon and change your extension to all files so they are all visible

Question 28

What is considered bad programming practice?

Answer 28

Ordering a query by it’s ordinal position. Ex:
ORDER BY 4 DESC;

It doesn’t really change the resulting data
Reference by name and not ordinal position

Question 29

What ORDER BY is also default?

Answer 29

Alphabetical and ascending

Question 30

5 Basic clauses we discuss

Answer 30

SELECT
FROM
WHERE
ORDER BY
LIMIT

Use this order to maintain good coding convention practice

Question 31

What is the WHERE clause used for?

Answer 31

To filter data based certain conditions
When using the WHERE clause, be careful because it is case sensitive, must use single quotes (‘ ‘) and must be written exactly as it is listed in database

Ex:
WHERE prod_category = ‘Bath Products’
use equal sign to address one conditon and encapsulate it with single quotes

Question 32

How to include multiple conditions using the WHERE clause?

Answer 32

Use IN and parentheses and commas but get rid of the equal sign when using multiple conditions
WHERE order_total IN (‘Bath Products’, ‘Gift Basket’)

Can also use AND to filter more conditions between diff columns. Ex:
WHERE prod_category = ‘Bath Products’
AND emp_name = ‘Clement Carry’

Question 33

How to exclude 1 category using WHERE clause

Answer 33

Use != which means not equal to

Question 33

How to exclude more than 1 category using WHERE clause

Answer 33

Use NOT IN, encapsulate the conditons in parenthesis, single quotes and commas
Ex: WHERE prod_category NOT IN (‘Olive Oil’, ‘Gift Basket’)

Question 34

When using the OR clause

Why should you carefully understand what is being asked?

Answer 34

You can exclude or include data you didn’t mean to
Use parentheses to separate individual conditions
If management wants all orders of bath products, all orders of employee Clement Carry and in both only bulk orders of greater than 5 items

WHERE prod_category = (‘Bath Products’
OR emp_name = ‘Clement Carry’)
AND quantity > 5
by putting the 2 conditions in parentheses, we have grouped them together

Question 34

How to filter with WHERE using a numerical value?

Answer 34

You do not need quotation signs, you can use IN, AND, equal, or other comprison operators like less than or more than. Then use the number you are looking for.
If looking for a range, use BETWEEN

BETWEEN will include the numbers you are stating. EX: if you say BETWEEN 2 AND 4, then it will also include the numbers 2 and 4 in the results