Section 1
Preview this deck
INT
Front
| 5 | 0 | ||
| 4 | 0 | ||
| 3 | 0 | ||
| 2 | 0 | ||
| 1 | 0 |
Active users
0
All-time users
0
Favorites
0
Last updated
6 years ago
Date created
Mar 1, 2020
Cards (59)
Section 1
(50 cards)
INT
Integer (no decimal) between -2147483648 to 2147483647
False
Views cannot be updated
View materialization
a physical table is created when the view is first queried
FLOAT(n, d)
Small number with a floating decimal point. The total maximum number of digits is n with a maximum of d digits to the right of the decimal point.
Simple Queries
SQL statements that retrieve data from only one table
PRIMARY KEY
constraint defines the primary key of the table
Join Queries
•Inner Joins •Outer Joins
Queries with Aggregate Functions
COUNT, SUM, AVG, VARIANCE, MIN/MAX, STDEV
SQL INSERT Statement
INSERT INTO PRODUCT VALUES ('980', 'Chateau Angelus, Grand Clu Classé, 1960', 'red', 6)
Column constraints
PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, DEFAULT, CHECK
UNIQUE
constraint defines an alternative key of a table
Nested Query example
SELECT SUPNAME FROM SUPPLIER WHERE SUPNR = (SELECT SUPNR FROM PURCHASE_ORDER WHERE PONR = '1560')
Queries with Set Operations
SELECT SUPNR FROM SUPPLIER EXCEPT SELECT SUPNR FROM SUPPLIES
Key Characteristics of SQL
- Set-oriented and declarative - Free form language - Case insensitive - Can be used interactively from a command prompt or executed by a program
DEFAULT
constraint sets a default value for a column
VARCHAR(n)
Holds a variables length string with a maximum size n
Queries with EXISTS
SELECT SUPNAME FROM SUPPLIER R WHERE EXISTS (SELECT * FROM SUPPLIES S WHERE R.SUPNR = S.SUPNR AND S.PRODNR = '0178')
relation
SQL table implements a ____ from the relational model
SMALLINT
Small integer (no decimal) between -32768 to 32767
SQL Views
- Part of the external data model - Defined by means of an SQL query and its content is generated upon its invocation by an application or other query - A virtual table without physical tuples - Allow for logical data independence which makes them a key component in the three-layer database architecture
Inner Join example
SELECT R.SUPNR, R.SUPNAME, R.SUPSTATUS, S.PRODNR, S.PURCHASE_PRICE FROM SUPPLIER R, SUPPLIES S WHERE R.SUPNR = S.SUPNR
SELECT STATEMENT FORMAT
SELECT component FROM component [WHERE component] [GROUP BY component] [HAVING component] [ORDER BY component]
BOOLEAN
True or False
TRUE
SQL does not eliminate duplicates
FOREIGN KEY
constraint defines a foreign key of a table
Outer Joins
- Joins that work even with NULL values - Can be used when we want to keep all the tuples of one, or both tables, in the result of the JOIN, regardless of whether or not they have matching tuples in the other table
Correlated Queries
- Whenever a condition in the WHERE clause of a nested query references some column of a table declared in the outer query •The nested query is then evaluated once for each tuple (or combination of tuples) in the outer query
CHAR(n)
Holds a fixed length string with size n
DATETIME
Date and time in format YYYY-MM-DD HH:MI:SS
multiset
Result of SQL SELECT statement
DOUBLE(n, d)
Large number with a floating decimal point. The total maximum number of digits is n with a maximum of d digits to the right of the decimal point.
DATE
Date in format YYYY-MM-DD
SQL DML statements
SQL SELECT, SQL INSERT, SQL DELETE, SQL UPDATE
Simple Query example
SELECT * FROM SUPPLIER
NOT NULL
constraint prohibits NULL values for a column
DROP
command can be used to drop or remove database objects
SQL DELETE Statement
DELETE FROM PRODUCT WHERE PRODNR = '1000'
Outer Join example
SELECT R.SUPNR, R.SUPNAME, R.SUPSTATUS, S.PRODNR, S.PURCHASE_PRICE FROM SUPPLIER AS R LEFT OUTER JOIN SUPPLIES AS S ON (R.SUPNR = S.SUPNR)
SQL VIEW Statement example
CREATE VIEW TOPSUPPLIERS AS SELECT SUPNR, SUPNAME FROM SUPPLIER WHERE SUPSTATUS > 50
Queries with GROUP BY/HAVING
SELECT PRODNR, SUM(QUANTITY) AS QUANTITY FROM PO_LINE GROUP BY PRODNR HAVING SUM(QUANTITY) > 15
SQL Schema
grouping of tables and other database objects such as views, constraints and indexes which logically belong together
TIME
Time in format HH:MI:SS
BLOB
Binary Large Object (e.g. image, audio, video)
ALTER
statement can be used to modify table column definitions
Queries with ALL/ANY
•The comparison condition v > ALL V returns TRUE if the value v is greater than all the values in the multiset V -If the nested query doesn't return a value, it evaluates the condition as TRUE •The comparison condition v > ANY V returns TRUE if the value v is greater than at least one value in the multiset V -If the nested query doesn't return a value, it evaluates the whole condition as FALSE
Query modification
RDBMS modifies queries that query views into queries on the underlying base tables
CHECK
constraint defines a constraint on the column values
Queries with ORDER BY
SELECT PONR, PODATE, SUPNR FROM PURCHASE_ORDER ORDER BY PODATE ASC, SUPNR DESC
Inner Joins
Joins that display data if there were a corresponding record in each table queried. Equality joins, non-equality joins, and self-joins are all classified as inner joins.
SQL UPDATE Statement
UPDATE SUPPLIER SET SUPSTATUS = DEFAULT
Section 2
(9 cards)