Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

CMSC 127 > SQL > Flashcards

SQL Flashcards

1
Q

SQL stands for ?

A

Structured Query Language

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

SQL was originally called as

A

SEQUEL (Structured English QUEry Language)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

First and Latest version of SQL

A

SQL - 86, SQL:2023

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Comprehensive DB language: Has statements for data definitions,
queries, and updates

A

SQL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

in SQL, what are the following equivalents:

Relation :
Attribute:
Tuple:

A

Relation : Table
Attribute: Column
Tuple: Row

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Elements include tables, types, constraints, views, domains, and other constructs that describe the _________

A

schema

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

In other systems, a schema is called a ____________ (just like in MariaDB)

A

database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

TRUE or FALSE:

In MariaDB, schema and database are synonymous

A

TRUE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

A named collection of schemas

A

Catalog

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

A catalog contains a special schema called ________________ which provides information on all schemas in the catalog and their corresponding elements

A

INFORMATION_SCHEMA

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Used to specify a new relation by giving it a name and specifying its attributes and initial constraints

A

CREATE TABLE command

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Relations declared via CREATE TABLE are called _____________________ which means that the table and its rows are actually created and stored as (or in) a file by the DBMS

A

base tables (or base relations)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Example CREATE Query:

A

CREATE TABLE EMPLOYEE (
Ssn CHAR(9) NOT NULL,
Fnam VARCHAR(15) NOT NULL,
Minit CHAR,
Lname VARCHAR(15) NOT NULL,
Bdate DATE,
Dnumber INT NOT NULL,
PRIMARY KEY (Ssn)
);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

The basic data types are the following:

A
  1. Numeric
  2. Character string
  3. Bit string
  4. Boolean
  5. Date
  6. Time
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Integer numbers of various sizes, Floating-point (real) numbers of various precision (e.g. FLOAT, REAL, DOUBLE PRECISION)

A

Numeric

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Formatted numbers can be declared as DECIMAL( i, j ) where i, the _________, is the total # of digits and j, the __________, is the # of digits after the decimal point;

A

precision, scale

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

___________ and _____________ are synonyms for DECIMAL

A

DEC and NUMERIC

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

Fixed length CHAR(n) or CHARACTER(n) where n is the # of characters or Varying length VARCHAR(n) (or CHAR VARYING(n) or
CHARACTER VARYING(n)) where n is the max # of characters

A

Character String

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

In a VARCHAR(n), n corresponds to what?

A

the Maximum number of Characters in a string

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

These are variable-length as well with maximum length specified in kilobytes (K), megabytes (M), or gigabytes (G)

A

CHARACTER LARGE OBJECT or CLOB

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Fixed-length BIT(n) where n is the # of bits OR Varying length BIT VARYING(n) where n is the max # of bits

A

Bit String

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

These are variable-length as well with maximum length specified in K, M, or G

A

BINARY LARGE OBJECT or BLOB

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

Traditional values of TRUE or FALSE

A

Boolean

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

TRUE or FALSE:
A Boolean value in SQL can only be TRUE or FALSE.

A

False. It can also be UNKOWN due to presence of NULL values

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
has 10 positions with components YEAR, MONTH, and DAY in the form YYYY-MM-DD
DATE
26
has at least 8 positions with components HOUR, MINUTE, and SECOND in the form HH:MI:SS
Time
27
Includes DATE and TIME fields plus a minimum of six positions for decimal fractions of seconds and an optional TIME ZONE qualifier
Timestamp
28
Specifies a relative value that can be used to increment or decrement an absolute value of a date, time, or timestamp
INTERVAL
29
A _________ can be declared, and its name can be used with the attribute specification instead of specifying the data type
Domain (ex. CREATE DOMAIN SSN_TYPE AS CHAR(9);)
30
May be specified if NULL is not permitted for a particular attribute. Always implicitly specified for attributes that are part of the primary key of each relation
NOT NULL Constraint
31
Included in any new row if an explicit value is not provided for the attribute
Default Values
32
How to define Default Values:
the DEFAULT clause is added to the attribute definition. ex. Mgr_ssn CHAR(9) NOT NULL DEFAULT '123456789',
33
Used to restrict attribute or domain values
Check Constraint ex. Dnumber INT NOT NULL CHECK (Dnumber > 0 and Dnumber < 100
34
The ______________clause specifies one or more attributes that make up the primary key of a relation
PRIMARY KEY
35
Clause can follow the attribute definition directly if primary key contains a ________attribute only
single
36
Declare multiple attributes as primary key
PRIMARY KEY (state, registration_no)
37
The ______________ clause specifies one or more attributes that make up alternate (a.k.a. unique or candidate) keys of a relation
UNIQUE
38
The ________________ .. ___________clause specifies referential integrity constraints
FOREIGN KEY, REFERENCES ex. Mgr_ssn CHAR(9) NOT NULL REFERENCES EMPLOYEE(Ssn) OR FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn); //given that Mgr_ssn is already initialized above
39
Primary Key, Unique Key, Foreign Key notation in Database Scheme
PRI, UNI, MUL respectively
40
Referential integrity constraint violation can occur when tuples are __________ or ___________, or when a foreign key or primary key attribute value is ______________;
inserted, deleted, updated
41
Default action for Foreign Key Violation is
reject/RESTRICT
42
Database designers can opt not to follow the default action and use the ___________, _____________, and _____________ options which must be qualified by ON DELETE or ON UPDATE
SET NULL, CASCADE, SET DEFAULT
43
The ______________ clause may optionally be used to assign a name to the constraint. Must be unique within a schema. Useful in case the constraint must be dropped later
CONSTRAINT
44
The _________ clause can be used to specify row-based constraints. Each row is individually checked whenever inserted or modified
CHECK
45
Adds a single tuple (row) to relation.
INSERT Statement INSERT INTO VALUES (, , ..., );
46
TRUE or FALSE: Values should be listed in the same order how the corresponding attributes were specified in the CREATE TABLE command
TRUE. Unless an attribute list is provided INSERT INTO
(, , ..., ) VALUES (, , ..., );
47
Adds multiple tuples (rows) to relation. Order of values depends on whether Variation #1 or #2 was used in the top portion of the INSERT statement
Insert Multiple. (Here variation 2 was used) INSERT INTO
[(, , ..., )] VALUES (t1[], t1[], ..., t1[]), (t2[], t2[], ..., t2[]), ... (tm[], tm[], ..., tm[])
48
Basic statement for retrieving information from a database but they can get complex
SELECT statement
49
Basic form of a Select Statement
select-from-where block SELECT FROM
WHERE ;
50
In the Query: SELECT Fname, Lname, Salary FROM EMPLOYEE, DEPARTMENT WHERE EMPLOYEE.Dnumber = DEPARTMENT.Dnumber AND Dname = 'Engineering'; The statement "EMPLOYEE.Dnumber = DEPARTMENT.Dnumber" is an example of a ?
Join Condition
51
In the Query: SELECT Fname, Lname, Salary FROM EMPLOYEE, DEPARTMENT WHERE EMPLOYEE.Dnumber = DEPARTMENT.Dnumber AND Dname = 'Engineering'; The statement Dname = 'Engineering' is an example of a?
Selection Condition
52
Is used to remove ambiguity of queries
Prefixing
53
Best practice is to prefix with aliases whether there's ambiguity or none for better query comprehension
TRUE
54
are also useful to remove ambiguity in relation names
Aliases
55
WHERE clause is optional and without one, all tuples of the relation specified in the FROM clause are ___________ for the query result
selected
56
If there are more than one relation in the FROM clause, the ______________ (all possible tuple combinations) of these relations is selected
cross product
57
Specify every selection and join condition needed because not doing so may result in incorrect and/or very large output
TRUE
58
Used to retrieve all the attribute values of the selected tuples
Use of Asterisk (*)
59
Allows the use of a temporary, more readable name to an attribute / column within a query; It only exists for the duration of the query
Attribute (Column) Alias
60
TRUE or FALSE: An Attribute Alias becomes permanent once assigned
FALSE. It only exists for the duration of the query
61
TRUE or FALSE: Tables in SQL may allow two or more tuples that are Identical
TRUE. tables in SQL may allow two or more tuples that are identical, i.e., it may not be a set of tuples but rather a multiset
62
SQL does not automatically remove duplicate tuples in the results of queries due to:
1. Duplicate elimination is an expensive operation 2. User may want to see duplicate tuples in the result of query 3. Usually, when using aggregate function/s against tuples, it is undesirable to remove duplicates
63
_____________ keyword is used to explicitly remove duplicate tuples from result
DISTINCT
64
__________, _____________, and ____________ operators perform a set union, set intersection, and set difference operation, respectively. That is, duplicates are eliminated
UNION, INTERSECT, EXCEPT
65
Adding ___________ keyword to the set operators will perform their corresponding multiset operation (i.e., duplicates are NOT eliminated)
ALL
66
Given the Tables: test1 test 2 1 1 2 2 2 4 3 5 What is the output of this query? SELECT id FROM test1 UNION SELECT id from test2;
id 1 2 3 4 5
67
Given the Tables: test1 test 2 1 1 2 2 2 4 3 5 What is the output of this query? SELECT id FROM test1 UNION ALL SELECT id from test2;
id 1 2 2 3 1 2 4 5
68
Given the Tables: test1 test 2 1 2 2 2 2 3 3 4 What is the output of this query? SELECT id FROM test1 INTERSECT SELECT id from test2;
id 2 3
69
Given the Tables: test1 test 2 1 2 2 2 2 3 3 4 What is the output of this query? SELECT id FROM test1 INTERSECT ALL SELECT id from test2;
id 2 2 3
70
The _________ comparison operator can be used for string pattern matching
LIKE
71
______________ (matches zero or more characters) and (blank) (matches exactly one character) may be used in the pattern string
Wildcards %, _ (underscore)
72
In case there is a need to match the % and/or _ characters, the ___________ clause is used. Default escape character is ___________.
ESCAPE, \ (backslash)
73
Substring Pattern Matching where we retrieve the department names ending in "ing" from a table named DEPARTMENT
SELECT Dname from DEPARTMENT WHERE Dname LIKE '%ing';
74
Substring Pattern Matching where we use the ESCAPE clause
SELECT val from demo1 WHERE val LIKE 'UNDER$_%' ESCAPE '$' here the dollar sign is assigned as an escape character for the _ Default Version would be: SELECT val from demo1 WHERE val LIKE 'UNDER\_%';
75
Comparison operator that returns TRUE if value being compared is one of the elements in the given set of values
IN Operator ex. SELECT Dnumber, Dname FROM DEPARTMENT WHERE Dnumber IN (1,3);
76
Comparison operator that returns TRUE if compared value is between two values (inclusive)
BETWEEN ... AND Operator ex. SELECT Dnumber, Dname FROM DEPARTMENT WHERE Dnumber BETWEEN 2 AND 4;
77
The _____________ clause allows the user to order the tuples in the query result by the values of one or more attributes
ORDER BY
78
The ordering can be specified as ascending (_____) or descending (______). If not specified, the default is ________.
ASC, DESC (default is ASC)
79
Adds zero or more tuples (rows) depending on the result of the query
INSERT Statement (when used for Copying Tables/Rows)
80
Sample INSERT Statement for Copying INSERT INTO .... SELECT
INSERT INTO employee1 SELECT Ssn, Salary FROM Employee;
81
Creates a new table then adds zero or more tuples (rows) depending on the result of the query. Not all table components (e.g. some constraints) are copied in the new table and will have to be added manually after
CREATE TABLE .. SELECT ex. CREATE TABLE employee2 SELECT Ssn, Salary FROM EMPLOYEE;
82
Removes zero or more rows from relation depending on the number of tuples selected
DELETE FROM
WHERE ; ex. DELETE FROM EMPLOYEE WHERE Dnumber = 1 AND Super_ssn IS NOT NULL;
83
Modifies attribute values of zero or more tuples in relation
UPDATE
SET = , = , ..., = WHERE ; ex. UPDATE EMPLOYEE SET Minit = NULL, Salaray = Salary * 1.1 WHERE Dnumber = 1;
84
When attribute(s) with NULL values are involved in comparisons, result is ____________
unknown (maybe true or false)
85
TRUE or FALSE: SQL uses a binary-valued logic with values of TRUE and FALSE
FALSE. SQL uses a three-valued logic with values of TRUE, FALSE, and UNKNOWN
86
What will the boolean expression evaluate into? TRUE and UNKNOWN
UNKNOWN
87
What will the boolean expression evaluate into? FALSE and UNKNOWN
FALSE
88
What will the boolean expression evaluate into? TRUE or UNKNOWN
TRUE
89
What will the boolean expression evaluate into? FALSE or UNKNOWN
UNKNOWN
90
What will the boolean expression evaluate into? NOT UNKNOWN
UNKNOWN
91
Referred to as subqueries in our Lab handout(s). Query contained inside another SQL query
Nested Queries ex. SELECT * FROM table_Name WHERE attribute IN (another Select Query)
92
Can be used in WHERE, FROM, SELECT clauses. Allows use of tuples of values in comparisons by placing them in parentheses
Nested Queries
93
__________ function is typically used with correlated nested queries; Returns TRUE if query returns at least one row
EXISTS
94
Correlated Nested Query for select all the department without employee
SELECT d.Dnumber, d.Dname FROM DEPARTMENT d WHERE NOT EXISTS ( SELECT Fname FROM EMPLOYEE WHERE Dnumber = d.Dnumber);
95
INNER JOIN, LEFT JOIN, AND NATURAL JOIN are examples of ?
EQUIJOIN
96
If we intend to see the non-matching ones as well, we can use an _________
outer join
97
Every tuple in the left table must be in the result; Non-matching tuples will have NULL values for attributes of the right table.
Left Outer Join ex. SELECT l.descr "LEFT", rdescr "RIGHT" FROM demo_left l LEFT OUTER JOIN demo_right r ON (l.id = r.id);
98
TRUE or FALSE: RIGHT JOIN and RIGHT OUTER JOIN are synonymous
TRUE. The outer keyword is optional. Both clauses perform the same operation, that is returning all rows from the right table and matching rows from the left table, with NULLs for unmatched columns from the left table
99
Every tuple in the right table must be in the result; Non-matching tuples will have NULL values for attributes of the left table.
Right Outer Join
100
Every tuple in the left and right tables must be in the result; Non- matching tuples will have NULL values for attributes of the non- matching side.
Full Outer Join
101
TRUE or FALSE: MariaDB does not have a full outer join implementation but can be done with doing a UNION ALL of the left and right outer joins
TRUE. Full Outer Join can be achieved via the UNION ALL of the left and right outer join ex. select * from emp right join dept on emp.deptno = dept.deptno UNION select * from emp left join dept on emp.deptno = dept.deptno;
102
Same as joining tables without the join condition/s; Results in a cross product of the tuples.
Cross Join
103
Can be used when a value can be different based on certain conditions
CASE Construct
104
Used to summarize information from multiple tuples into a single-tuple summary
Aggregate Functions
105
Built-in aggregate functions:
1. COUNT 2. SUM 3. MAX 4. MIN 5. AVG
106
TRUE or FALSE: Aggregate Functions eliminate duplicate tuples
FALSE. Aggregate Functions DOES NOT eliminate duplicate tuples considered for aggregation
107
TRUE or FALSE: Aggregate Functions DOES eliminate NULLs before aggregating
TRUE. Except for COUNT(*)
108
Allows subgroups of tuples to be formed in a relation; The subgroups are based on some attribute values
Grouping Using GROUP BY
109
TRUE or FALSE: Aggregate function(s) is (are) applied to the subgroup/s
true
110
SELECT Dno, AVG(Salary) AvgSal FROM EMPLOYEE GROUP BY Dno;
Will return the average salary of all employees grouped by Dno
111
Can appear in conjunction with GROUP BY to provide conditions on which subgroups' summary information will be included in the result
HAVING Clause ex. SELECT Dno, AVG(Salary) AvgSal FROM EMPLOYEE GROUP BY Dno HAVING `AvgSal` > 1000;
112
Allows a user to define a table that will only be used in a particular query;
WITH Construct A.k.a. Common Table Expression (CTE)
113
SQL Queries Order of Precedence
[ WITH
] SELECT FROM
[ WHERE ] [ GROUP BY ] [ HAVING ] [ ORDER BY ];
114
Sample Recursive Query
WITH RECURSIVE nextweek (dt) AS (SELECT CURDATE () + INTERVAL 1 DAY AS dt UNIION SELECT dt + INTERVAL 1 DAY FROM nextweek WHERE dt + INTERVAL 1 DAY < CURDATE() + INTERVAL 8 DAY) Generate all dates in the next 7 days (not including today)
115
Can be used to specify action(s) to perform when certain events occur and when certain conditions are satisfied
Triggers
116
Used to enforce application-based constraints (remember from Relational Model Constraints topic)
Triggers (CREATE TRIGGER statement)
117
Example use of CREATE Trigger
CREATE TRIGGER monite_salary BEFORE INSERT ON EMPLOYEE FOR EACH ROW BEGIN IF NEW.Salary > (SELECT Salary FROM EMPLOYEE WHERE Ssn = NEW.Super_ssn) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "Salary Value Error"; END IF;
118
Single table derived from other tables
View ex. CREATE VIEW EMP_PROJ AS SELECT QUERY
119
View does not necessarily exist in physical form; Also referred to as ___________
virtual table
120
Two implementation strategies for Views:
1. Query Modification 2. View Materialization
121
Involves transforming the view query into a query on the underlying base tables. No physical table. Inefficient for views defined via complex queries that are time- consuming to execute
Query Modification
122
Involves physically creating a temporary or permanent view table (called a _________________)
View Materialization, materialized view table
123
Efficient strategy for automatically updating the view table when the base tables are updated must be developed
View Materialization
124
Views can be used to hide certain attributes or tuples from unauthorized users
Views as Authorization Mechanisms
125
Used to change the definition of base table or of other named schema elements
ALTER Statement Examples: ALTER TABLE .. ADD COLUMN .. ; ALTER TABLE .. DROP COLUMN .. ; ALTER TABLE .. ALTER .. ; ALTER VIEW .. AS .. ; ALTER SCHEMA .. ;
126
Used to remove base tables or other named schema elements
DROP Statement ex: DROP TABLE .. ; DROP VIEW .. ; DROP TRIGGER .. ; DROP SCHEMA .. ;
127
Empties a table completely, often quicker than DELETE (all rows)
TRUNCATE TABLE Statement Syntax: TRUNCATE TABLE
;
128

CMSC 127 (8 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2025 Bold Learning Solutions. Terms and Conditions