SQL Flashcards
(116 cards)
1
Q
What is DML and what are the corresponding commands?
A
Data Manipulation Language: INSERT, UPDATE and DELETE
2
Q
What is DDL and what are the corresponding commands?
A
Data Definition Language: CREATE, ALTER, COMMENT, DROP, RENAME, TRUNCATE
3
Q
What is DCL and what are the corresponding commands?
A
Data Control Language: GRANT, REVOKE
4
Q
How do you update a certain column value?
A
UPDATE table SET col =
5
Q
How to use a ‘ inside string quotes?
A
q’!Father’s day!’ or
q’[Father’s day]’
6
Q
How do you insert a new row of data into a table?
A
INSERT INTO table (col1, col2, col3) VALUES (val1, val2, val3)
or use a subquery instead of VALUES(….)
7
Q
What is a searched CASE expression?
A
When the selector is moved in the individual WHEN clauses:
SELECT job_id CASE WHEN job_id = .. THEN .. WHEN job_id = .. THEN .. etc.
8
Q
What is a relational database?
A
a collection of relations or two-dimensional tables controlled by the Oracle server.
9
Q
What are the components of a relational model?
A
- collections of objects or relations that store data
- a set of operators that can act on the relations to produce other relations
- data integrity for accuracy and consistency
10
Q
What is the DUAL table?
A
- automatically created by Oracle database
- one column DUMMY of type VARCHAR(1)
- one row with value x
Useful for computing a constant expression with the SELECT statement
11
Q
What is the NULL value used for?
A
Values that are unavailable, unassigned, unknown or inapplicable
12
Q
When does an alias require double quotation marks in Oracle?
A
If it is case-sensitive
13
Q
What is the default display of dates in Oracle?
A
DD-MON-RR
14
Q
How do you use the LIKE operator?
A
% represents zero or more characters
_ represents a single character
eg. WHERE name LIKE ‘_o%’
15
Q
TRUE AND NULL
A
NULL
16
Q
FALSE AND NULL
A
FALSE
17
Q
NULL AND NULL
A
NULL
18
Q
TRUE OR NULL
A
TRUE
19
Q
FALSE OR NULL
A
NULL
20
Q
NULL OR NULL
A
NULL
21
Q
Give an example of a row limiting clause
A
OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY;
FETCH FIRST 5 ROWS ONLY;
For MySQL, use LIMIT
22
Q
When do you use the double &&?
A
If you want to reuse a variable value without prompting the user each time (use double the first time, then single)
23
Q
When do you use the DEFINE command?
A
To create a variable and assign a value to it
24
Q
What does SET VERIFY ON do?
A
It forces SQL developer to display the text of a command after it replaces substitution variables with values
25
How do you find a substring of length 5 starting from the first character?
SUBSTR('HelloWorld', 1, 5)
26
What does TRUNC(96.926, 2) return?
96.92
Default number of decimals is 0
27
What year is 27-OCT-17 if the current year is 1995 in RR and YY format?
RR: 2017
YY: 1917
28
What year is 27-OCT-52 if the current year is 2048 in RR and YY format?
RR: 1952
YY: 2052
29
What do CURRENT_DATE and CURRENT_TIMESTAMP return?
The current date and the current date and time from the user session, respectively
30
What is 'fm' used for in formatting dates to strings?
Fill mode: to make sure that there is just one space between date elements, instead of the default, to make it look nicer
31
Give an example of a HH24:MI:SS AM format
15:45:32 PM
32
DD "of" MONTH
12 of OCTOBER
33
ddspth
fourteenth
34
What is 'fx' used for?
To denote that the character string must exactly match the character argument when converting a string to a date using TO_DATE(char), instead of ignoring extra blanks
TO_DATE('2025-05-20', 'FXYYYY-MM-DD')
35
Which functions are used to convert NULL values to actual values and can be used with any data type?
NVL(), NVL2(), NULLIF(), COALESCE()
36
How does NVL2(a, b, c) work?
If a is NULL, return c, else return b
37
What does COALESCE(list) do?
It returns the first non-null expression in the list
38
How to use the DECODE function?
DECODE('job_id', search1, res1, search2, res2, default)
39
What happens to NULL values in group functions?
They are ignored
40
Can you use a column alias in the GROUP BY clause?
No
41
If you include a group function in a SELECT clause, can you select an individual column as well?
Only if it appears in the GROUP BY clause: all the columns in the SELECT list that are not in group functions must be in the GROUP BY clause
But the GROUP BY columns does not have to be in the SELECT list
42
What does NATURAL JOIN do?
It joins two tables based on all columns with the same name in both tables. Error if the matching columns have different data types
43
When to use USING?
If several columns have the same names but the data types do not match, to specify the columns for the equijoin
44
What is a Cartesian product?
a join of every ow of one table to every row of another table
45
What does < ANY mean?
TRUE if less than the maximum
46
What does < ALL mean?
TRUE if less than the minimum
47
What are the rules of compound queries (UNION, INTERSECT, MINUS)?
- the number of columns being selected must be the same
- the data types must be the same data type group (numeric or character)
- column names don't have to be identical
- NULL values are not ignored during duplicate checking
- output is sorted in ascending order of the columns in the select clause by default
48
What does UNION ALL do?
It returns all rows from both queries, including duplicates
49
Can the component queries have individual ORDER BY clauses?
No, and the ORDER BY clause only recognizes columns of the first SELECT query
50
What does the TRUNCATE statement do?
Remove all rows from a table efficiently (faster than DELETE) and this cannot be undone
51
When does a database transaction start and end?
Starts when the first DML statement is executed.
Ends with:
- a COMMIT or ROLLBACK statement
- a DDL or DCL statement (automatic commit)
- the user exits SQL developer or SQL*Plus (automatic commit)
- when the session logs out (automatic commit)
- the system crashes (automatic rollback)
52
What happens to the state of the data after COMMIT?
- data changes are saved in the database
- previous state of the data is overwritten
- all sessions can view the results
- locks on the affected rows are released
- all savepoints are erased
53
What happens to the state of the data after ROLLBACK?
- data changes are undone
- previous state of the data is restored
- locks on the affected rows are released
54
What is read consistency?
- guarantees a consistent view of the data at all times
- changes made by one user do not conflict with changes made by another user
- on the same data, readers do not wait for writers and vice versa, writers wait for writers
55
What happens when the SELECT statement attempts to lock a row that is locked by another user with FOR UPDATE?
the database waits until the row is available and then returns the results of the SELECT
56
How do you lock a table so that others can only query the table but no other activities, while not waiting if another user has already locked the table?
LOCK TABLE table
IN EXCLUSIVE MODE
NOWAIT;
57
In an UPDATE clause, for which of the clauses can you use a subquery?
In the SET and WHERE clauses
58
How do you create a multiple-statement transaction?
START TRANSACTION
INSERT INTO
SELECT
FROM
WHERE
DELETE
COMMIT or ROLLBACK
59
Does a read operation place a lock on the table it is reading?
No
60
When can you use the FOR UPDATE clause in a SELECT statement?
Only after START TRANSACTION or with AUTOCOMMIT = 0
61
What are the database objects?
Table, view, sequence, index, synonym
62
What are the naming rules for tables and columns?
- start with a letter
- 128 chars long
- contain only letters, digits, $ or #
- no duplicate of another object owned by the user
- no server-reserved words
63
What are the different datetime data types?
- TIMESTAMP: date with fractional seconds
- INTERVAL YEAR TO MONTH: interval of years and months
- INTERVAL DAY TO SECOND: interval of days, hours, minutes, seconds
64
What are the different constraint types?
NOT NULL (only at column level), UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK
65
When should a constraint be defined that the table level?
When it applies to more than one column.
CREATE TABLE table (
col1 DATATYPE,
col2 DATATYPE,
CONSTRAINT constr PRIMARY KEY (col1));
66
what does ON DELETE CASCADE mean?
When a row in the parent table is deleted, the dependent rows in the child table are also deleted
67
How to write a foreign key constraint?
table level: CONSTRAINT constr FOREIGN KEY (col2) REFERENCES table (col1)
column level: col datatype CONSTRAINT constr_fkey REFERENCES table (col1)
68
How to use a CHECK constraint?
CONSTRAINT constr CHECK (col2 > 0)
69
How do you create a table using a subquery?
CREAT TABLE table AS
(SELECT, FROM, WHERE)
70
When do you use ALTER TABLE?
- add a new column
- modify an existing column definition
- define a default value for a new column
- drop a column
- rename a column
- change table to read-only status
71
How to add or modify a column?
ALTER TABLE table
ADD or MODIFY (col DATATYPE CONSTRAINT));
72
How to drop a column?
ALTER TABLE table
DROP (col);
cannot be recovered, and the table must have at least one column remaining after drop
73
When do you use the ONLINE keyword?
to indicate that DML operations on the table will be allowed while marking the columns UNUSED (can be dropped when the demand on system resources is lower, faster response time)
eg.
ALTER TABLE employees
SET UNUSED (old_email)
ONLINE;
74
How to prevent DML or DDL changes during table maintenance?
ALTER TABLE table READ ONLY;
75
How to remove a table and its data entirely?
DROP TABLE table PURGE;
- any views and synonyms remain, but are invalid
- pending transactions are committed
- can only done by user with DROP ANY TABLE privilege
76
Are NULL values allowed for UNIQUE keys?
No
77
Does a secondary key have to be unique?
No
78
How to create a secondary index to a table?
CREATE INDEX index ON table(col)
79
What is the data dictionary?
a collection of tables and views in the Oracle database that stores information about definitions of all objects, default values, privileges, user names, constraints
80
What are three most important views in the data dictionary?
USER_OBJECTS (owned or created)
ALL_OBJECTS (to which you have access)
DBA_OBJECTS (owned by all users)
81
What views contain table, column and constraint information?
user_tables, user_tab_columns and user_constraints
82
How do you reference another user's table?
use the owner's name as a prefix to the table
83
What is a sequence?
An object that can automatically generate unique numbers.
- shareable
- use to create primary key value
84
How to create a sequence?
CREATE SEQUENCE seq START_WITH nr INCREMENT BY nr MAXVALUE nr
CACHE nr NOCYCLE
SCALE;
85
How to use a sequence?
INSERT INTO table (key, col2, col3) VALUES (seq.NEXTVAL, val2, val3)
and view the current value of the sequence by
SELECT seq.CURRVAL FROM dual
86
When can you not use NEXTVAL and CURRVAL?
- in the SELECT list of a view
- in a SELECT statement with the DISTINCT keyword
- in a SELECT statement with the GROUP BY, HAVING, ORDER BY clause
- a subquery in a SELECT, DELETE, UPDATE statement
87
When can gaps in sequence values occur?
- a rollback
- a system crash
- the sequence is used in another table
88
What is a synonym?
a database object to give an alternative name to a table or other database object, to hide the identity of an underlying schema object
89
How to create a synonym for a table?
CREATE SYNONYM tab FOR table
90
When is a unique index created automatically?
when you define a primary key or unique constraint in a table definition
91
What are the advantages of views?
- restrict data access
- make complex queries easy
- data independence
- present different view of the same data
92
What is a simple view?
- derives data from 1 table
- no functions or groups of data
- DML operations through the view
93
What is a complex view?
- derives data from many tables
- functions or groups of data
- does not always allow DML operations through the view
94
How to create a view?
CREATE VIEW view
AS SELECT col1, col3, col6 FROM table WHERE ...;
95
When can you not remove a row from a view? What about modifying and adding data?
Cannot remove if the view contains
- group functions
- GROUP BY
- DISTINCT
- ROWNUM
and you cannot modify data in the view if it also contains expressions
and you cannot add data if the base table contains NOT NULL columns without default values that are not included in the view, because then that column is undefined.
96
What does WITH CHECK OPTION do?
CREATE OR REPLACE VIEW view AS SELECT * FROM table WHERE col = value
WITH CHECK OPTION CONSTRAINT check
to make sure that no updates or inserts can be done that violate the WHERE condition
97
Does dropping a view affect the tables on which the view was based?
No
98
How to add a constraint to a column?
ALTER TABLE table
ADD CONSTRAINT constr FOREIGN KEY (col) REFERENCES table(col1)
99
How to rename a column?
ALTER TABLE table
RENAME COLUMN col to renamedcol;
100
What does DISABLE primary key CASCADE do?
It deactivates the primary key constraint and disables all dependent foreign key constraints as well
101
What is the difference between ENABLE and VALIDATE?
ENABLE ensures all incoming data conforms to the constraint, VALIDATE ensures all existing data conforms to the constraint
102
What is the difference between DEFERRED and IMMEDIATE?
With DEFERRED, the system does not check whether the constraint is satisfied until a COMMIT happens, with IMMEDIATE, it is checked at the end of each statement
103
What positions do NULL values get when sorting ASC or DESC?
ASC: NULL last
DESC: NULL first
104
When can you create an index on a table in another schema?
When you have the CREATE ANY INDEX privilege
105
Can you access the data dictionary when the database is closed?
No, it becomes inaccessible because it is part of the data files
106
What is a temporary table?
It holds data that exists only for the duration of a transaction or session. Can be global (visible for all sessions, on disk) or private (in memory, name begins with ORA$PTT)
107
What is an external table?
The metadata is stored in the database and the actual data in an external directory
108
What privileges does the DBA have?
system privileges such as CREATE USER, CREATE ANY TABLE, SELECT ANY TABLE, DROP ANY TABLE, DROP USER
109
What system privileges can the DBA assign to users?
CREATE SESSION, TABLE, SEQUENCE, VIEW, PROCEDURE, DROP ANY TABLE
110
When can you further grant an object privilege to other users?
If the grant includes WITH GRANT OPTION
111
What happens when the owner revokes a privilege from a user who granted the privilege to other users?
The revoking cascades to all the privileges granted
112
How to perform an unconditional multitable INSERT
INSERT ALL
INTO table1 VALUES(...)
INTO table2 VALUES(...)
SELECT ...... FROM table WHERE ...
113
How to perform a conditional multitable INSERT
INSERT ALL
WHEN condition THEN
INTO table1 VALUES (...)
WHEN condition THEN
INTO table2 VALUES (...)
SELECT ... FROM table
114
What is the difference between CURRENT_DATE, CURRENT_TIMESTAMP AND LOCALTIMESTAMP
CURRENT_DATE: returns the current date from the user session dataype DATE
CURRENT_TIMESTAMP: returns current date and time from the user session datatype TIMESTAMP WITH TIMEZONE
LOCALTIMESTAMP: returns current date and time from the user session datatype TIMESTAMP
115
Which constraints are recovered after a flashback to before a DROP operation?
All constraints except referential integrity (foreign key) constraints that reference other tables are retrieved.
116
How do you grant all users access to two tables as an owner?
GRANT ALL ON employees TO PUBLIC;
GRANT ALL ON department TO PUBLIC;
