CRUD
Create, Read, Update, Delete
SQL
Structured Query Language
DSL
Domain Specific Language
GPL
General Purpose Language
RDBMS
Relational Data Base Management System
RDSMS
Relational Data Stream Management System
DSMS
Data Stream Management System
an SQL statement is terminated with a
;
ASCII
American Standard Code for Information Exchange
single quotes are for
alias identifier
How do you pick columns?
SELECT
How do select rows?
WHERE
What is dynamic memory?
Dynamic memory allocation is when an executing program requests that the operating system give it a block of main memory. The program then uses this memory for some purpose. Usually the purpose is to add a node to a data structure.
SID
Security Identifier
ANSI
American National Standards Institute
ISO
Interational Organization for Standardization
SQL was developed at ___ by ___.
IBM, Chamberlain & Boyce
Who did the relational model?
Tedd Codd
SEQUEL
Structured English Query Language
What was Oracle’s first name?
Relational Software Inc,
A table has rows and columns, where rows represent ___ and columns represent the ___.
A table has rows and columns, where rows represent records and columns represent the attributes.
Tuple
Tuple − A single row of a table, which contains a single record for that relation is called a tuple.
In mathematics, a tuple is a finite ordered list (sequence) of elements. Mathematicians usually write tuples by listing the elements within parentheses “ {\displaystyle ({\text{ }})} (\text{ })” and separated by commas; for example, {\displaystyle (2,7,4,1,7)} (2, 7, 4, 1, 7) denotes a 5-tuple.
Schema
The term “schema” refers to the organization of data as a blueprint of how the database is constructed (divided into database tables in the case of relational databases).
A representation of a plan or theory in the form of an outline or model.
ALTER TABLE Statement is used to ___, ___, or ___ ___ in an existing table.
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
The ALTER TABLE statement is also used to add and drop various constraints on an existing table.
How do you create rows of data
INSERT INTO test (a,b) VALUES (10,’a’);
How do you create an ID Column?
INTEGER PRIMARY KEY
What is a Boolean data type?
This has three values: TRUE, FALSE, and UNKNOWN. Expressions that return aBoolean data type are known as Boolean expressions. Unlike other SQL Server data types, a Boolean data type cannot be specified as the data type of a table column or variable, and cannot be returned in a result set.
BOOLEAN
This has three values: TRUE, FALSE, and UNKNOWN. Expressions that return a Boolean data type are known as Boolean expressions. Unlike other SQL Server data types, a Boolean data type cannot be specified as the data type of a table column or variable, and cannot be returned in a result set.
What is the wild card?
% at beginnning or end
How to use underscore.
before a letter _a (means second letter is a)
WHERE clause allows you to get just the ___ you need
rows
DISTINCT
to find distinct values
four kinds of JOIN
Inner, Outer, Left, Right
Alias Identifier syntax
SELECT * FROM table_name [AS] alias_name
A table alias is also called a ___.
A table alias is also called a correlation name.
What is a CLAUSE?
???
junction table
???
LENGTH
SUBSTR
unpack data from a column
TRIM
how to view source
CTRL+U
INSERT INTO
The INSERT INTO statement is used to insert new records in a table.
UPPER and LOWER funtions
make capital or lower case
use of operator “=”
what are the two number types
integer and real
floating point numbers sacrifice
something for percision
TYPEOF
catenate/concatenate
CAST
MODULUS operator
ROUND
SQL Standard date & time formate
SQL Lite
COUNT
aggregate???
HAVING clause
TRANSACTION
TRIGGER
A database is a collection of information
NULL means?
Nothing here.
The COUNT() function returns the number of rows that matches a specified criteria.
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
HAVING syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
ORDER BY Syntax
SELECT column1, column2, …
FROM table_name
ORDER BY column1, column2, … ASC|DESC;
LENGTH
counts numbers or characters
SUBSTING (?, ?, ?)
Alias
AS
You can’t create a field w/o giving it a ___.
Data Type
Dates are tricky because ___.
Different formats.
Your database has a table called orders, containing a field called order_num. Which is a correctly formed query in MySQL?
select order_num from orders
What does zero-indexing mean in a database?
Database software typically starts counting rows at zero, irrespective of the id number stored in the row.
The following statement will return all orders other than the one with an id of 5 (in MySQL): select * from orders where order_id<>5
TRUE
What would the following query return? SELECT order_id FROM orders WHERE order_num<5 AND order_type=’online’ OR order_type=’cash’
Rows with both id<5 and order_type of ‘online’, plus all rows with order_type of ‘cash’ regardless of id.
What’s the best description of an SQL string function?
It is like a formula, applied to a field to change some or all characters within all rows returned, e.g. to make all characters upper case.
Assuming a table called cities contains a field called city, what will the following query return in MySQL? SELECT SUBSTRING(city,1,1) FROM cities WHERE city=”London”One or more rows containing the letter L.
One or more rows containing the letter L.
Using an alias on a field name can change the column heading in the result set.
TRUE
Databases store data, plus data about the data. One of the key kinds of latter data is data type. There are scores of data types. The database will not let you create a field without its data type.
TRUE
In SQL queries it’s rare to specify a WHERE clause in this format (MySQL): WHERE field_name = ‘2016-07-07’. Why?
Because we don’t know what kind of date field_name is: it might require a time as well as date, in which case we would get a false result.
Aggregation functions
SELECT
FROM
GROUP BY
SELECT DISTINCT
to removed repeated values
How do you find how many customers you have?
SELECT DISTINCT customers
FROM table1
Why is the following query poorly formed? SELECT *, count(*) FROM address
Because it mixes grouped and ungrouped results. Reading across the single row of results will suggest the count value belongs to that row.
Which of these queries is in the right order?
SELECT district, count(district) as ct FROM address WHERE district like ‘%B%’ GROUP BY district HAVING count(district)>8 ORDER BY ct DESC
SELECT DISTINCT is used to return unique values. You can use it to return unique combinations by listing the relevant fields after the word DISTINCT, separated by commas.
TRUE
INSERT INTO syntax
INSERT INTO table_name (column1, column2, column3, …)
VALUES (value1, value2, value3, …);
Example:
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES (‘Cardinal’, ‘Tom B. Erichsen’, ‘Skagen 21’, ‘Stavanger’, ‘4006’, ‘Norway’);
ALTER TABLE syntax
ALTER TABLE table_name
ADD column_name datatype;
What SQL statement extracts data from a database?
SELECT
Which SQL statement is used to update data in a database?
UPDATE
Which SQL statement is used to delete data from a database?
SELECT
LIMIT
JOIN
If you JOIN a 5 row table to a 6 row table, why might you only get 4 rows?
Because those are the only rows that are the same.
JOIN and ON are part of the ___ section of the query.
FROM
JOIN merges ___, UNION merges ___.
columns, rows
The IN operator allows you to specify multiple values in a ___ clause.
The IN operator allows you to specify multiple values in a WHERE clause.
IN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, …);
A field in one table that appears as a field in another table is probably ___.
A field in one table that appears as a field in another table is probably a foreign key .
There is usually only one primary key in a table. It is typically has data type set to ___ and is set to ___.
There is usually only one primary key in a table. It is typically has data type set to integer (int) and is set to auto-increment .
To make sense of an unfamiliar database, it’s helpful to visualise it by following the trail of ___.
To make sense of an unfamiliar database, it’s helpful to visualise it by following the trail of foreign keys .
Different types of join determine which rows are included from which tables. To return rows that exist in both tables you would use a(n) ___.
Different types of join determine which rows are included from which tables. To return rows that exist in both tables you would use a(n) INNER JOIN .
T/F. UNION will display rows from both queries, one under the other. Unique values are returned with UNION while all values are returned with UNION ALL.
TRUE
T/F. We can return the same results as JOIN using IN like so: SELECT * from table where field2 in (SELECT field2 from table2)
FALSE
A VIEW is a ___.
stored query
Which is the best definition of a view in SQL?
A stored query, which runs and returns a tabular result set when you click on it or refer to it in a query. Like a virtual table.
Which is the best definition of a function in SQL?
A stored calculation, which returns one or more values when you refer to it in a query.
Which is the best definition of a stored procedure in SQL?
A stored query, which returns a value or performs an action, when called in a query.
ACID compliant
normalized
