Basic SQL Flashcards
(146 cards)
1
Q
SQL stands for
A
Structured Query Language
2
Q
SQL is a comprehensive database language, meaning
A
It has statements for data definitions, queries, and updates
3
Q
DDL stands for
A
Data Definition Language
4
Q
DML stands for
A
Data Manipulation Language
5
Q
SQL standards are divided into
A
Core specification PLUS specialized extensions
6
Q
SQL core
A
supposed to be implemented by all RDBMS veodors that are SQL complaint
7
Q
SQL extensions
A
can be implemented as optional modules to be purchased independently for specific DB applications such as data mining, spatial data, temporal data, data warehousing, online analytical processing, etc
8
Q
Table row and column correspond to relational model terms
A
relation, tuple, attribute
9
Q
CREATE statement can create
A
schemas, tables, domains, as well as views, assertions, triggers
10
Q
SQL schema is
A
a DB structure
11
Q
SQL schema is identified by
A
schema name, and includes authorization identifier to indicate the user or account who owns the schema, as well as descriptors for each element in the schema
12
Q
Schema elements include
A
tables, constraints, views, domains, and other constructs (authorization grants) that describe the schema
13
Q
How to create schema (2 ways)
A
CREATE schema statement that can include all schema element definitions
Can be assigned name and authorization identifier and the elements can be defined later
14
Q
Create schema called COMPANY owned by user with authorization identifier Jsmith
A
CREATE SCHEMA COMPANY AUTHORIZATION ‘Jsmith’;
15
Q
catalog
A
collection of schemas in SQL environment
16
Q
SQL environemnt
A
installation of an SQL-compliant RDBMS on a computer system
17
Q
a catalog always contains a special schema called
A
INFORMATION_SCHEMA
18
Q
INFORMATION_SCHEMA provides
A
information on all the schemas in the catalog and all the element descriptors in these schemas
19
Q
CREATE TABLE command used for
A
specify new relation by giving it name and specifying its attributes and initial constraints
20
Q
Order how attributes are specified when creating table
A
attribute name, data type to specify domain of values, any constraints (NOT NULL)
21
Q
When are key, entity integrity, and referential integrity constraints specified
A
within CREATE TABLE statement after the attributes are declared or can be added later using the ALTER TABLE command
22
Q
can explicitly attach schema name to relation name, separated by a period
A
CREATE TABLE COMPANY.EMPLOYEE rather than CREATE TABLE EMPLOYEE
23
Q
base relations are created through
A
CREATE TABLE statements
24
Q
base relations
A
relation and its tuples are created and stored as a file by the DBMS
25
virtual relations
created through CREATE VIEW statement, which may or may not correspond to an actual physical file
26
attributes in base table considered to be ordered
in sequence in which they are specified in the CREATE TABLE statement, but rows aren't considered to be ordered within a relation
27
Example code of creating a table
```
CREATE TABLE DEPARTMENT
( Dnam VARCHAR(15) NOT NULL,
Number INT NOT NULL,
Mgr_ssn CHAR(9) NOT NULL,
Mgr_start_date DATE,
PRIMARY KEY (Dnumber),
UNIQUE (Dname),
FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE (Ssn) );
```
28
basic data types available for attributes
numeric, character, string, bit string, Boolean, date, time
29
Numeric datatypes include
Integer numbers: INT, and floating point (real) numbers: FLOAT, REAL, DOUBLE PRECISION), DECIMAL,
30
DECIMAL(i,j)... what does I and J stand for
what would DECIMAL(5,2) mean
i is the precision (total number of decimal digits)
j is the scale (number of digits after the decimal point) -default zero
3 digits before the decimal, 2 after decimal
31
Character string data types
CHAR, VARCHAR, CLOB
32
CHAR(n)
fixed length... n is the number of characters.
33
If value of Smith for an attribute of type CHAR(10)
it's padded with 5 blank characters to become 'Smith '
34
VARCHAR(n)
varying length. n is number of max characters
35
if str1 comes before str2 in alphabetic order it is considered
to be less than str2
36
concatenation of strings
‘abc’ || ‘XYZ’ results in a single string ‘abcXYZ’.
37
CLOB (CHARACTER LARGE OBJECT)
specify columns that have large text values, such as documents. CLOB maximum length can be specified in kilobytes (K), megabytes (M), or gigabytes (G)
38
CLOB(20M) specifies
maximum length of 20 megabytes
39
bit-string data
either of fixed length n—BIT(n)—or varying length—BIT VARYING(n), where n is the maximum number of bits
40
write an example of a bit string
Literal bit strings are placed between single quotes but preceded by a B to distinguish
them from character strings; for example, B‘10101’
41
BLOB
BINARY LARGE OBJECT
specify columns that have large binary values, such as images
can be specified in kilobits (K), megabits (M), or gigabits (G)
42
BLOB(30G)
specifies a maxi- mum length of 30 gigabits
43
Boolean
TRUE/FALSE/UNKNOWN (because SQL has 3valued logic)
44
DATE number of position
10
45
date components
YYYY-MM-DD
46
TIME number positions
>=8
47
time components
HH:MM:SS
48
comparison with dates and time
49
Literal date values represented by
DATE '2008-09-27' or TIME '09:12:47'
50
Domain can be declared and domain name used with the attribute specification. show:
CREATE DOMAIN SSN_TYPE AS CHAR(9);
| Can use SSN_TYPE in place of CHAR(9) when defining table
51
NOT NULL
may be specified if NULL is not permitted for a particular attribute
52
primary keys required not to
be null
53
How to define default value for an attribute
append the clause DEFAULT to attribute definition
54
default value included in any new tuple if
explicit value not provided for that tattribute
55
if no default clause specified
default value is null for attributes that don't have a NOT NULL constraint
56
Example of setting default for attribute
Dno INT NOT NULL DEFAULT 1,
57
CHECK clause
can restrict attribute or domain values
58
CHECK clause... department numbers are restricted to integer numbers between 1 and 20
Number INT NOT NULL CHECK (Dnumber >0 AND Dnumber
59
check clause can also be used with create domain statement.e example
CREATE DOMAIN D_NUM AS INTEGER
| CHECK (D_NUM >0 AND D_NUM
60
Primary key clause
specifies 1+ attributes that make up primary key of relation
61
Unique clause
specifies alternate (secondary) keys
62
referential integrity specified via
FOREIGN KEY clause
63
referential integrity constraint can be violated when
tuples are deleted/inserted, or when a foreign key or primary key attribute is modified
64
default action SQL takes for integrity violations
reject update operation that will cause violation, known are the RESTRICT option
65
referential triggered action clause
can be attached to any foreign key constraints to deal with referential integrity issues
66
options for referential triggered action clause
SET NULL, CASCADE, SET DEFAULT and must be qualified with either ON DELETE or ON UPDATE
67
if tuple for supervising employee is deleted, the value of Super_ssn is automatically set to NULL for all employee tuples that were referencing the deleted tuple
CONSTRAINT EMPSUPERFK
FOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(Ssn)
ON DELETE SET NULL ON UPDATE CASCADE,
68
CONSTRAINT EMPSUPERFK
FOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(Ssn)
ON DELETE SET NULL ON UPDATE CASCADE,
if value of Ssn for supervior is updated...
new value is cascaded through to Super_ssn for all employee tuples referencing the updated employee tuple
69
CASCADE ON DELETE
delete all the referencing tuples
70
CASCADE ON UPDATE
Change the value of referencing FK attributes to the updated (new) primary key value for all the referencing tuples
71
table constraints can be specified through CHECK clauses at end of CREATE TABLE statement
CREATE TABLE DEPARTMENT CHECK (Dept_create_date
72
SQL is not a set of tuples, but
a multiset (bag) of tuples
73
WHERE ;
condition is a conditional Boolean expression that identifies tuples to be retrieved by the query
74
comparison operators
= = >
75
Retrieve the birth date and address of the employee(s) whose name is ‘John B. Smith’.
SELECT Bdate, Address
FROM EMPLOYEE
WHERE Fname=‘John’ AND Minit=‘B’ AND Lname=‘Smith’;
76
projection attributes
attributes specified by the SELECT clause that are to be retrieved
77
selection condition
specified by the WHERE clause ... must be true for any retrieved tuple
78
iterator looping over each query
iterator in the SQL query ranging or looping over each individual tuple in the EMPLOYEE table and evaluating the condi- tion in the WHERE clause. Only those tuples that satisfy the condition (those tuples for which the condition evaluates to TRUE after substituting their corresponding attribute values) are selected.
79
Retrieve the name and address of all employees who work for the ‘Research’ department.
SELECT FROM
WHERE
Fname, Lname, Address
EMPLOYEE, DEPARTMENT Dname=‘Research’ AND Dnumber=Dno;
80
Join condition
Dnumber = Dno ... combines 2 tuples, one from DEPARTMENT and one from EMPLOYEE whenever the value of Dnumber in DPARTMENT is equal to the value of Dno in EMPLOYEE
81
For every project located in ‘Stafford’, list the project number, the controlling department number, and the department manager’s last name, address, and birth date.
SELECT FROM
WHERE
Pnumber, Dnum, Lname, Address, Bdate PROJECT, DEPARTMENT, EMPLOYEE Dnum=Dnumber AND Mgr_ssn=Ssn AND Plocation=‘Stafford’;
82
when must we qualify attribute name with the relation name
when a multi table query refers to two or more attributes with the same name - to prevent ambiguity
83
Retrieve the name and address of all employees who work for the ‘Research’ department.
WRITE AS QUALIFIED
```
SELECT
EMPLOYEE.Fname, EMPLOYEE.LName,
EMPLOYEE.Address
FROM EMPLOYEE, DEPARTMENT
WHERE DEPARTMENT.DName=‘Research’ AND DEPARTMENT.Dnumber=EMPLOYEE.Dno;
```
84
For each employee, retrieve the employee’s first and last name and the first and last name of his or her immediate supervisor.
SELECT E.Fname, E.Lname, S.Fname, S.Lname FROM EMPLOYEE AS E, EMPLOYEE AS S WHERE E.Super_ssn=S.Ssn;
85
How do we create an alias
```
FROM EMPLOYEE AS E
or
FROM EMPLOYEE E
or
SELECT Salary AS Sal
```
86
Rename relation attributes within the query by giving them aliases
FROM EMPLOYEE AS E(Fn, Mi, Ln, Ssn, Bd, Addr, Sex, Sal, Sssn, Dno)
87
SELECT E.Fname, E.Lname, S.Fname, S.Lname FROM EMPLOYEE AS E, EMPLOYEE AS S WHERE E.Super_ssn=S.Ssn;
Think of E and S as
two different copies of the EMPLOYEE relation; the first, E, represents employees in the role of supervisees or subordinates; the second, S, represents employees in the role of supervisors
88
missing WHERE clause indicates
no condition on tuple selection; hence, all tuples of the relation specified in the FROM clause qualify and are selected for the query result
89
Missing WHERE if more than one relation is specified in the FROM clause
CROSS PRODUCT—all possible tuple combinations—of these relations is selected
90
Select all EMPLOYEE Ssns
SELECT Ssn
| FROM EMPLOYEE;
91
all combinations of EMPLOYEE Ssn and DEPARTMENT Dname
SELECT Ssn, Dname
FROM EMPLOYEE, DEPARTMENT;
we get the actual CROSS PRODUCT (except for duplicate elimination, if any)
92
* stands for
all the attributes
93
retrieve all the attribute values of any EMPLOYEE who works in DEPARTMENT number 5
SELECT *
FROM EMPLOYEE
WHERE Dno=5;
94
retrieves all the attributes of an EMPLOYEE and the attributes of the DEPARTMENT in which he or she works for every employee of the ‘Research’ department
SELECT *
FROM EMPLOYEE, DEPARTMENT
WHERE Dname=‘Research’ AND Dno=Dnumber;
95
CROSS PRODUCT of the EMPLOYEE and DEPARTMENT relations
SELECT *
| FROM EMPLOYEE, DEPARTMENT;
96
A SQL table with a key is restricted to being a
set, since he key value must be distinct in each tuple
97
If we want to eliminate duplicate tuples from result of SQL query use
DISTINCT in the SELECT clause, meaning that only distinct tuples should remain in the result
98
Specifying SELECT with neither ALL nor DISTINCT—as in our previous examples— is equivalent to
SELECT ALL
99
Retrieve the salary of every employee
SELECT ALL Salary
| FROM EMPLOYEE;
100
retrieve all distinct salary values
SELECT DISTINCT Salary
| FROM EMPLOYEE;
101
Set operations by SQL
```
set union (UNION)
set difference (EXCEPT)
set intersection (INTERSECT)
```
102
relations resulting from these set operations are sets of
tuples. that is, duplicate tuples are eliminated from the result
103
set operations apply only to
union-compatible relations, so we must make sure that the two relations on which we apply the operation have the same attributes and that all the attributes appear in the same order in both relations
104
Make a list of all project numbers for projects that involve an employee whose last name is ‘Smith’, either as a worker or as a manager of the department that controls the project.
Using Union
(SELECT DISTINCT Pnumber
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE Dnum=Dnumber AND Mgr_ssn=Ssn AND Lname=‘Smith’ )
```
UNION
( SELECT DISTINCT Pnumber
FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE Pnumber=Pno AND Essn=Ssn
AND Lname=‘Smith’ );
```
105
Corresponding multiset operations
UNION ALL, EXCEPT ALL, INTERSECT ALL
106
UNION ALL, EXCEPT ALL, INTERSECT ALL
| their results are
multisets- duplicates not eliminated
107
String pattern matching
comparison condition on only parts of a character string
108
what to use for string pattern matching
LIKE or ILIKE (not case sensitive)
109
Partial strings are specified using two reserved characters:
% replaces an arbitrary number of zero or more characters, and the underscore (_) replaces a single character.
110
Retrieve all employees whose address is in Houston, Texas.
SELECT Fname, Lname
FROM EMPLOYEE
WHERE Address LIKE ‘%Houston,TX%’;
111
Find all employees who were born during the 1950s.
SELECT Fname, Lname FROM EMPLOYEE
| WHERE Bdate LIKE‘_ _5_ _ _ _ _ _ _’;
112
If an underscore or % is needed as a literal character in the string, the character should be
preceded by an escape character, which is specified after the string using the keyword ESCAPE.
113
______represents the literal string ‘AB_CD%EF’
‘AB\_CD\%EF’ ESCAPE ‘\’
114
If an apostrophe (’) is needed, it is represented as
two consecutive apostrophes (”) so that it will not be interpreted as ending the string
115
using arithmetic in queries ... can be applied to
addition (+), subtraction (–), multiplication (*), and division (/) can be applied to numeric values or attributes with numeric domains
116
Show the resulting salaries if every employee working on the ‘ProductX’ project is given a 10 percent raise.
SELECT E.Fname, E.Lname, 1.1 * E.Salary AS Increased_sal
FROM EMPLOYEE AS E, WORKS_ON AS W, PROJECT AS P
WHERE E.Ssn=W.Essn AND W.Pno=P.Pnumber AND
P.Pname=‘ProductX’;
117
Retrieve all employees in department 5 whose salary is between $30,000 and $40,000.
SELECT *
FROM EMPLOYEE
WHERE (Salary BETWEEN 30000 AND 40000) AND Dno = 5;
118
The condition (Salary BETWEEN 30000 AND 40000) in Q14 is equivalent to
((Salary >= 30000) AND (Salary
119
ORDER BY
SQL allows the user to order the tuples in the result of a query by the values of one or more of the attributes that appear in the query result
120
Retrieve a list of employees and the projects they are working on, ordered by department and, within each department, ordered alphabetically by last name, then first name.
SELECT D.Dname, E.Lname, E.Fname, P.Pname
FROM DEPARTMENT D, EMPLOYEE E, WORKS_ON W, PROJECT P
WHERE D.Dnumber= E.Dno AND E.Ssn= W.Essn AND W.Pno= P.Pnumber
ORDER BY D.Dname, E.Lname, E.Fname;
121
default order by order
ascending
122
how to see results in descending order
DESC
123
how to specify ascending order explicitly
ASC
124
if we want descending alphabetical order on Dname and ascending order on Lname, Fname, the ORDER BY clause of Q15 can be written as
ORDER BY D.Dname DESC, E.Lname ASC, E.Fname ASC
125
INSERT
add a single tuple to relation
126
what needed for insert
specify relation name and list of values for the tuple, with values listed in same order in which corresponding attributes were specified in the CREATE TABLE command
127
Add a new tuple to the EMPLOYEE relation
INSERT INTO EMPLOYEE
| VALUES (‘Richard’,‘K’,‘Marini’,‘653298653’,‘1962-12-30’,‘98 Oak Forest, Katy, TX’, ‘M’, 37000, ‘653298653’, 4 );
128
Second form of Insert statement
allows the user to specify explicit attribute names that correspond to the values provided in the INSERT command
129
For example, to enter a tuple for a new EMPLOYEE for whom we know only the Fname, Lname, Dno, and Ssn attributes
INSERT INTO EMPLOYEE (Fname, Lname, Dno, Ssn)
| VALUES (‘Richard’, ‘Marini’, 4, ‘653298653’);
130
restriction on when inserting only some variables in a tuple
values must include all attributes with NOT NULL specification and no default value. Attributes with NULL allowed or DEFAULT values are the ones that can be left out
131
inserting multiple tuples into a relation
possible to insert into a relation multiple tuples separated by commas in a single INSERT command. The attribute values forming each tuple are enclosed in parentheses.
132
INSERT INTO EMPLOYEE (Fname, Lname, Ssn, Dno)
VALUES (‘Robert’, ‘Hatcher’, ‘980760540’, 2);
outcome
rejected if referential integrity checking is provided by DBMS.
133
INSERT INTO EMPLOYEE (Fname, Lname, Dno)
VALUES (‘Robert’, ‘Hatcher’, 5);
outcome
rejected if NOT NULL checking is provided by DBMS
134
create a temporary table that has the employee last name, project name, and hours per week for each employee working on a project
CREATE TABLE
( Emp_name Proj_name Hours_per_week
WORKS_ON_INFO VARCHAR(15), VARCHAR(15), DECIMAL(3,1) );
INSERT INTO WORKS_ON_INFO ( Emp_name, Proj_name, Hours_per_week )
SELECT E.Lname, P.Pname, W.Hours
FROM PROJECT P, WORKS_ON W, EMPLOYEE E
WHERE P.Pnumber=W.Pno AND W.Essn=E.Ssn;
135
DELETE
removes tuples from a relation
136
delete includes
WHERE clause, similar to that used in an SQL query, to select the tuples to be deleted
137
where are tuples exclusively deleted from
only one table at a time
138
a missing where when using delete
all tuples in the relation are to be deleted; however, the table remains in the database as an empty table (must DROP TABLE to remove table definition
139
delete all employees with last name Brown
DELETE FROM EMPLOYEE
| WHERE Lname = 'Brown';
140
delete everyone from dept 5
DELETE FROM EMPLOYEE
| WHERE Dno =5;
141
delete everything from EMPLOYEE TABLE
DELETE FROM EMPLOYEE; (table definition remains)
142
UPDATE
used to modify attribute values of one or more selected tuples
143
WHERE clause in the UPDATE command
selects the tuples to be modified from a single relation
144
change the location and controlling department number of project number 10 to ‘Bellaire’ and 5, respectively
UPDATE PROJECT
| SET Plocation = ‘Bellaire’, Dnum = 5 WHERE Pnumber=10;
145
give all employees in the ‘Research’ department a 10 percent raise in salary,
UPDATE EMPLOYEE
SET Salary = Salary * 1.1
WHERE Dno = 5;
146
can you set NULL or DEFAULT as new attribute
YES
