Basics Flashcards
Create Database
CREATE DATABASE bob;
(To Verify) SHOW DATABASES;
USE bob;
SHOW TABLES;
(None)
CREATE TABLE Checks Check # NUMERIC(6) NOT NULL, Payee VARCHAR(20) NOT NULL, Amount DECIMAL(6,2) NOT NULL, Remarks VARCHAR(20) NOT NULL;
INSERT INTO Checks VALUES
(‘I’, ‘MA’, ‘Bell’, ‘150’, ‘Have sons next time’);
etc…
Arithmetic Operators
(*), (/), (%), (+), (-) Order of Operations is multiplication, division, modulo, addition, and subtraction.
Place a Condition on a Query
WHERE clause = more selective queries
- in order to find a particular item or group of items you need 1 or more conditions
- contained in the WHERE clause
Ex: name = ‘Brown’ or number of hours > 100 or where
Expression
- an expression returns a value
- anything that follows a clause is usually an expression
ex: SELECT Name, Address, Phone, FROM Addressbook;
or
where name = ‘Brown’
(Boolean)
How would you select different tables in a database?
select * from deposits
Note: no semicolon
Plus (+)
ex: add to prices
SELECT Item, Wholesale, (Wholesale + 0.15) FROM price;
How would you change order of columns?
SELECT payee, remarks, amount, check #, FROM checks;
Dostinct
SELECT DISTINCT amount FROM checks;
or
SELECT DISTINCT, amount, payee FROM checks;
Primary Keys
- column that uniquely identifies row
- must contain unique values
CREATE TABLE suppliers
( supplier_id int not null,
supplier_name char(50) not null,
contact_name char(50),
constraint suppliers_pk primary key
(supplier_id)
);
Basic Query Syntax
- commands are not case sensitive
- spacing does not matter
- semicolon tells program the query is complete
- SQL’s magic element is “keywords”, used in clauses, to create SQL statements (FROM, SELECT, ORDER BY)
Select
- does not work alone
- almost always followed by from (SELECT names FROM people;)
Select *
SELECT ALL
- returns info in it’s own order
Putting all the data into one place makes it useful to …
- Query the data
- Update the data
- Insert new data
- Delete old data
What is a database?
A container made up of columns and rows to help organize data in a constructive way
SQL stands for …
Structured Query Language
Declarative Language
– statements are declared and the system executes those statements
What tells the program the query is complete?
Semicolon ;
Does spacing matter?
No
Are commands case sensitive?
No
Is data case sensitive?
Yes, it may
How do you change the order of a column?
SELECT payee, remarks, amount, check # FROM checks; (whatever order you would like)
Query Expression
- returns a value
- anything folllowing a clause (ie SELECT or FROM) is usually an expression
ex: SELECT amount FROM checks;
Conditions
- contained in the WHERE clause
- in order to find a particular item or group of items in your database you need 1(+) condition(s)
Types of Databases
Relational, object oriented, and document based are a few of the most common,
Table
- has a name and a collection of columns
Column
- has a name and a restriction that restricts the size and category of data that can be stored in that column
Required Column
- each row contains data for at least all the required columns
Row
- a single record in a table, retrieved by asking questions of the data
ex: “Who are all my contacts with the last name Smith?”
Key
- keys belong to tables
- each table should/must have one column that can uniquely identify a row (Primary key)
- keys are what create relations between Rows in Tables (relational databases require keys)
Foreign Key
- links the Table’s key to a different Table’s Primary Key
- also allows rows of each Table to be linked together
Primary Key
- column that uniquely identifies row
- if more than one table uses the same primary key, you can merge the two tables
- more than likely you will want to merge a subset of each table
Tableau Server does what in a nutshell?
Frames the analytical process.
Data goes in a single section split by roles: (Tableau)
Dimensions & measures
A well designed Tableau dashboard should always be considered an…
interactive information-driven application. (IIDA)
Two important principles of visualization are…
Manage intricacy.
Look for relevancy.
What is a perfect database?
Non-existent
