SQL Notes

Question 1

What does SQL stand for?

Answer 1

Structured Query Language

Used to interact with data in tables

Question 2

How is SQL different to excel?

Answer 2

Unlike an excel spreadsheet, tables in a relational database can be connected to each other via unique identifiers called keys

For example a relational database could have a table for employees, departments and salaries

Each table contains a unique key for each employee

Question 3

How to use select function?

Answer 3

SELECT <columns> FROM <table></columns>

If want to get all columns use “*”

SELECT * FROM employees;

Question 4

How do you use the limit function?

Answer 4

SELECT * FROM employees LIMIT 5

Will give us the top 5 employees

Question 5

How to use the ORDER BY function?

Answer 5

Used if we want a subsection of rows sorted by a specific column

ORDER BY <column> ASC, would order in ascending order and DESC is descending</column>

Text is done alphabetically, Numbers done by value

Question 6

How would you order the first 5 employees by in ascending order by first name?

Answer 6

SELECT * FROM employees ORDER BY first_name LIMIT 5

Note that we did not include the ASC or DESC keywords here because SQL sorts in ascending order by default.

Question 7

Addition

Subtraction

Multiplication

Division

Answer 7

+

-

*

/

Question 8

How would you SELECT employee number and double each salary?

Answer 8

SELECT emp_no, salary, salary*2 FROM salaries;

Question 9

Greater than, greater than equal to, equal to, less than, less than or equal to?

Answer 9

>

> =

=

<

<=

Question 10

Example of how to use the “where function” to choose all columns from all employees, where they are males?

Answer 10

SELECT * FROM employees WHERE gender = ‘M’;

Question 11

How could you use AND to SELECT * FROM employees WHERE gender = ‘M’; to select for those hired after January 1st 1990?

Answer 11

SELECT * FROM employees WHERE gender = ‘M’; AND hire_date > ‘1990-01-01’

Question 12

How do you use OR?

Answer 12

Exact same concept as AND, just doesn’t have to fulfil both conditions

Question 13

When do you use %

Answer 13

Is a wildcard, so use it with to find things similar eg.

SELECT * FROM employees WHERE first_name LIKE ‘ANN%’

Can also use it to fill missing letters eg. osc%r would give Oscar

Question 14

How would you use COUNT to find the number of rows in the employee table?

Answer 14

SELECT COUNT(*) FROM employees;

Question 15

How do you use MIN and MAX?

Answer 15

Same function as excel

SELECT MIN(<column) FROM table;

Question 16

What does AVG do?

Answer 16

Exact same position as MIN or MAX just finds the average