SQL Notes Flashcards
What does SQL stand for?
Structured Query Language
Used to interact with data in tables
How is SQL different to excel?
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
How to use select function?
SELECT <columns> FROM <table></columns>
If want to get all columns use “*”
SELECT * FROM employees;
How do you use the limit function?
SELECT * FROM employees LIMIT 5
Will give us the top 5 employees
How to use the ORDER BY function?
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
How would you order the first 5 employees by in ascending order by first name?
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.
Addition
Subtraction
Multiplication
Division
+
-
*
/
How would you SELECT employee number and double each salary?
SELECT emp_no, salary, salary*2 FROM salaries;
Greater than, greater than equal to, equal to, less than, less than or equal to?
>
> =
=
<
<=
Example of how to use the “where function” to choose all columns from all employees, where they are males?
SELECT * FROM employees WHERE gender = ‘M’;
How could you use AND to SELECT * FROM employees WHERE gender = ‘M’; to select for those hired after January 1st 1990?
SELECT * FROM employees WHERE gender = ‘M’; AND hire_date > ‘1990-01-01’
How do you use OR?
Exact same concept as AND, just doesn’t have to fulfil both conditions
When do you use %
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
How would you use COUNT to find the number of rows in the employee table?
SELECT COUNT(*) FROM employees;
How do you use MIN and MAX?
Same function as excel
SELECT MIN(<column) FROM table;
