Chapter 3: SQL Flashcards
What is a Data Definition Language (DDL)?
A language that operates on the structure of the database
- schema for relations
- domain values with attributes
- integrity constraints
- set of indices for each relation
- security and authorization info for relations
- physical storage structure on the disk
What are some domain types in SQL
char(n) - fixed length character string with user specified length n
varchar(n) - variable length character strings wit user specified maximum length n
int - integer
smallint - small integer
numeric(p,d) - fixed point number with user specified precision of p digits, with n digits to the right of decimal point
real, double precision - floating point and double-precision floating numbers, machine dependent precision
float(n) - floating point number with user specified precision of at least n digits
What does the CREATE TABLE command do?
creates a table as follows
CREATE TABLE r (A1 D1, …, AnDn,
(integrity-constraint 1), …
(integrity-constraint k)
)
What are some integrity constraints in CREATE TABLE
not null
-a thing isn’t null
primary key (A1, …, An)
-declaration on an attribute automatically ensures not null (SQL-92 and onwards, needs to be explicitly declared in SQL-89)
What does the DROP TABLE command do?
deletes all information about the dropped relation from the database
What does the ALTER TABLE command do?
adds/drops attributes to an existing relation
- ALTER TABLE r ADD a d
- a is name of attribute being added to relation r and d is domain of a
- ALTER TABLE r DROP a
- a is the name of an attribute of relation r
What is the basic structure of an SQL query?
3) SELECT A1,A2,…,An
1) FROM r1,r2, …, rn
2) WHERE P
* *where is optional**
How do you force the elimination of duplicates/multiset in your query result?
SELECT DISTINCT instead of SELECT
- SQL allows multiset, and normal SELECT can return multiset
- select clause can also contain arithmetic
What is the purpose of the WHERE clause?
specifies conditions that the result must satisfy
-corresponds to selection predicate in relational algebra
How do you combine comparison results in the WHERE clause?
Use logical connectives AND, OR, and NOT
How does the BETWEEN comparison operator work?
WHERE thing between (lower bound) and (upper bound)
-is inclusive, greater than or equal to and less than or equal to
What is the purpose of the SELECT clause?
lists the attributes in the result of a query
-corresponds to the projection operation in relational algebra
What is the purpose of the FROM clause?
lists the relations involved in the query
-corresponds to the cartesian product operation of the relational algebra
What is the purpose of the rename operation and how is it done in SQL?
it allows for the renaming of relations and attributes within a query
-in SQL is the AS clause
Where are temp variables defined?
They are defined in the from clause by use of the as clause
-makes temp variables to be referenced for the query
What are the two string operator special characters?
'%' -matches any substring '_' -matches any character **used after keyword LIKE **to get actual _ and % need \ before, \% and \_
What are the set operators in SQL?
UNION, INTERSECT, and EXCEPT
-to make them multi-set use keyword ALL
What does the having clause do?
Acts as a filter for the temporary output generated by the group by clause,
allows you to selectively output results from group by
What does a SQL query with a having clause look like?
SELECT z, ag(a) FROM T1,..., Tn WHERE theta GROUP BY z HAVING ag1(a)
How can you check for null values in SQL?
use IS NULL
What are Nested Subqueries
They are queries within a query
- they are a matter of preference
- any NSQ can be written as a set operation or a join, and vice-versa
- they run at O(n^2) because there is an outer and inner loop
What does the SOME clause do?
checks is there is at least one thing that meets the desired situation
- (=some) = in
How do you test for empty relations?
Use EXISTS and NOT EXISTS
- exists returns true if the subquery has an output
- not exists returns true if the subquery has no output
What does UNIQUE do?
tests if a subquery has any duplicate tuples in its result
What is a correlation variable?
Variable from outer query that is utilized in a NSQ, this is done by referencing the outer query variable within the NSQ
What is a derived relation?
A relation produced by a NSQ within the from clause, the NSQ produces a relation which then becomes the relation utilized in the outer query
What is the purpose of the WITH clause?
It provides a way of defining temporary tables, allowing for more complex queries
How does referencing temporary tables made using the WITH clause work?
The temp tables made, and any commands within their queries, only have access to the tables created before the execution of the current line. (can’t reference tables made after current table only before)
What is a VIEW
A VIEW is a QUERY WITH A NAME otherwise called a PRESERVED QUERY.
-query is stored in a system table tied to the view name so when view is called the query tied to that view is ran.
How long does it take to create a view?
Since creating a view is the act of storing the view’s query in the system table, (VIEW_NAME, QUERY), it is very fast (takes no time). Utilizing and calling the view can take time though.
How do VIEWS and WITH statements differ?
VIEW
-makes a temporary table that is permanent and can be accessed outside of current query
-is public
WITH
-makes temporary table that is temporary, can only be accessed within the query
-is private
How does INSERT INTO (insertion) function?
- inserts new rows/tuples in table
- works row wise
- INSERT INTO table VALUES(col 1, col 2, …)
- any values that you don’t want to insert can specify as null
How does UPDATE (updates) function?
- updates values in tables
- works column wise
- UPDATE table SET column value stat WHERE more stuff
How can the CASE statement be used for conditional updates?
can use CASE stuff END in the set portion a an UPDATE to make if then else.
- UPDATE table SET attribute = CASE WHEN attribute condition THEN stuff ELSE stuff END
What can be done with VIEWS and is recommended against and why?
You can do updates, inserts and deletes in a view but it can get very complicated so it is not recommended.
