Descriptive Questions & Answer
Module-2
1). What is relational database? Components of relational database.
Relational database: A relational database is a collection of relations or two-dimensional tables.
Components of the Relational Model:
• Collections of objects or relations that store the data
• A set of operators that can act on the relations to produce other relations
• Data integrity for accuracy and consistency
2). Write down the purpose of data model.
Purpose of data model:
• Communicate
• Categorize
• Describe
• Specify
• Investigate
• Evolve
• Analyze
• Imitate
3). Write down the key components of ER Modeling.
Components of ER Modeling:
1. Entity: A thing of significance about which information needs to be known.
2. Attribute: Something that describes or qualifies an entity.
3. Relationship: A named association between entities showing optionality and degree.
4). Write down the guidelines of Primary key and Foreign key.
Guidelines for Primary Keys and Foreign Keys:
• You cannot use duplicate values in a primary key.
• Primary keys generally cannot be changed.
• Foreign keys are based on data values and are purely logical (not physical) pointers.
• A foreign key value must match an existing primary key value or unique key value, or else it must be null.
• A foreign key must reference either a primary key or a unique key column.
5). Write down the name of various SQL statements
|
SELECT
|
Data manipulation language (DML)
|
GRANT
|
Data control language (DCL)
|
|
INSERT
|
REVOKE
|
|
UPDATE
|
COMMIT
|
Transaction control
|
|
DELETE
|
ROLLBACK
|
|
MERGE
|
SAVEPOINT
|
|
CREATE
|
RENAME
|
Data definition language (DDL)
|
|
|
ALTER
|
TRUNCATE
|
|
DROP
|
COMMENT
|
1. Capabilities of SQL select statements.
Projection: Choose the columns in a table that are returned by a query.
Selection: Choose the rows in a table that are returned by a query.
Joining: Bring together data that is stored in different tables by specifying the link between them.
2. Writing SQL Statements.
Writing SQL Statements:
• SQL statements are not case-sensitive (unless indicated).
• SQL statements can be entered on one or many lines.
• Keywords cannot be split across lines or abbreviated.
• Clauses are usually placed on separate lines for readability and ease of editing.
• Indents should be used to make code more readable.
• Keywords typically are entered in uppercase; all other words, such as table names and
• Columns, are entered in lowercase.
3. Operator Precedence and Rules of Precedence?
Operator Precedence: If an arithmetic expression contains more than one operator, multiplication and division are evaluated first. If operators in an expression are of the same priority, then evaluation is done from left to right.
Rules of Precedence:
• Multiplication and division occur before addition and subtraction.
• Operators of the same priority are evaluated from left to right.
• Parentheses are used to override the default precedence or to clarify the statement
4. What is Defining a null Value?
Null Value: A null is a value that is unavailable, unassigned, unknown, or inapplicable. A null is not the same as a zero or a space. Zero is a number, and a space is a character.
5. Defining a column alias?
A column alias is
ü Renames a column heading
ü Is useful with calculations
ü Immediately follows the column name
1). Write down the elements of where clause?
Elements of where clause:
• Column name
• Comparison condition
• Column name, constant or list of values
2). Write down the comparison condition operators and their uses?
|
Operator
|
Meaning
|
|
=
|
Equal to
|
|
>
|
Greater than
|
|
>=
|
Greater than or equal to
|
|
|
|
|
<
|
Less than
|
|
<=
|
Less than or equal to
|
|
<>
|
Not equal to
|
|
BETWEEN …AND…
|
Between two values (inclusive)
|
|
IN(set)
|
Match any of a list of values
|
|
LIKE
|
Match a character pattern
|
|
IS NULL
|
Is a null value
|
3). Write down the characteristics and uses of like condition?
Characteristics:
• Use the LIKE condition to perform wildcard searches of valid search string values
• Search conditions can contain either literal characters or numbers
• You can combine pattern-matching characters
• You can use the ESCAPE identifier to search for the actual % and _ symbols
Uses: You may not always know the exact value to search for. You can select rows that match a character pattern by using the LIKE condition.
4). Write down the logical condition operators name and uses?
|
Operator
|
Meaning
|
|
AND
|
Returns TRUE if both component conditions are true
|
|
OR
|
Returns TRUE if either component condition is true
|
|
NOT
|
Returns TRUE if the following condition is false
|
5). Write down the Operator rules of precedence?
|
Operator
|
Meaning
|
|
1
|
Arithmetic operators
|
|
2
|
Concatenation operator
|
|
3
|
Comparison conditions
|
|
4
|
IS [NOT] NULL, LIKE, [NOT] IN
|
|
5
|
[NOT] BETWEEN
|
|
6
|
Not equal to
|
|
7
|
NOT logical condition
|
|
8
|
AND logical condition
|
|
9
|
OR logical condition
|
1. Use of SQL Functions?
ü Perform calculations on data
ü Modify individual data items
ü Manipulate output for groups of rows
ü Format dates and numbers for display
ü Convert column data types
2. Single-row Functions and multiple row functions?
Single-Row Functions: These functions operate on single rows only and return one result per row.
Multiple-Row Functions: Functions can manipulate groups of rows to give one result per group of rows.
3. General Function?
ü NVL
ü NVL2
ü NULLIF
ü COALESCE
ü CASE
ü DECODE
4. Character Functions?
Character functions can be divided into the following:
A. Case-manipulation functions
1. LOWER
2. UPPER
3. INITCAP
B. Character-manipulation functions
1. CONCAT
2. SUBSTR
3. LENGTH
4. INSTR
5. LPAD
6. RPAD
7. REPLACE
8. TRIM
5. Arithmetic with dates?
A. ROUND
B. TRUNC
C. MOD
1). What are Group Functions?
Group Functions: Group functions operate on sets of rows to give one result per group.
2). Mention the types of Group Functions.
1. AVG
2. COUNT
3. MAX
4. MIN
5. STDDEV
6. SUM
7. VARIANCE
3). Describe the three Fomats of Count Function.
a. COUNT (*)
b. COUNT (expr)
c. COUNT (DISTINCT expr)
4). What are the Guidelines of Group By clause?
1. Using a WHERE clause, you can exclude rows before dividing them into groups.
2. You must include the columns in the GROUP BY clause.
3. You cannot use a column alias in the GROUP BY clause.
4. If you include a group function in a SELECT clause, you cannot select individual results as well, unless the individual column appears in the GROUP BY clause. You receive an error message if you fail to include the column list in the GROUP BY clause.
5). What are the illegal queries using Group Function?
ü You cannot use the WHERE clause to restrict groups.
ü You use the HAVING clause to restrict groups.
ü You cannot use group functions in the WHERE clause.
1) Cross joins
2) Natural joins
3) USING clause
4) Full (or two-sided) outer joins
5) Arbitrary join conditions for outer joins
2. Creating Joins with the USING Clause.
1. Use the USING clause to match only one column when more than one column matches.
2. Do not use a table name or alias in the referenced columns.
3. The NATURAL JOIN and USING clauses are mutually exclusive.
4. If several columns have the same names but the data types do not match, the NATURAL JOIN clause can be modified with the USING clause to specify the columns that should be used for an equijoin.
3. Qualifying Ambiguous Column Names.
1. Use table prefixes to qualify column names that are in multiple tables.
2. Use table prefixes to improve performance.
3. Use column aliases to distinguish columns that have identical names but reside in different tables.
4. Using Table Aliases.
1. Use table aliases to simplify queries. 2. Use table aliases to improve performance.
5. Creating Joins with the ON Clause.
1. The join condition for the natural join is basically an equijoin of all columns with the same name.
2. Use the ON clause to specify arbitrary conditions or specify columns to join.
3. The join condition is separated from other search conditions.
4. The ON clause makes code easy to understand.
01. What is subquery? In which clauses, it is used?
A subquery is a SELECT statement that is embedded in a clause of another SELECT statement.
Subquery usage in this clause:
1. WHERE Clause
2. HAVING Clause
3. FROM Clause
02. Guidelines of using subqueries?
1. Enclose subqueries in parentheses.
2. Place subqueries on the right side of the comparison condition.
3. The ORDER BY clause in the subquery is not needed unless you are performing Top-N analysis.
4. Use single-row operators with single-row subqueries, and use multiple-row operators with
multiple-row subqueries.
03. Type of subqueries?
1. Single row subquery: Queries that return only one row from the inner SELECT statement.
2. Multiple row subquery: Queries that return more than one row from the inner SELECT statement.
1. What are function(executed) of the DML ?
A DML statement is executed when you:
ü Add new rows to a table
ü Modify existing rows in a table
ü Remove existing rows from a table
2. What are function of TRUNCATE statement ?
Removes all rows from a table, leaving the table empty and the table structure intact.
3. How many types of database transactions ?
1) Data manipulation language
2) Data definition language
3) Data control language
4. When does a transaction start and end ?
1. A COMMIT or ROLLBACK statement is issued.
2. A DDL statement, such as CREATE, is issued.
3. A DCL statement is issued.
4. The user exits iSQL*Plus.
5. A machine fails or the system crashes.
5. Advantage of COMMIT & ROLLBATK statement ?
1. Ensure data consistency
2. Preview data changes before making changes permanent
3. Group logically related operations
01: Database objects
a. Table
b. View
c. Sequence
d. Index
e. Synonym
02: Naming rules: Table names & column names
1. Must begin with a letter
2. Must be 1–30 characters long
3. Must contain only A–Z, a–z, 0–9, _, $, and #
4. Must not duplicate the name of another object owned by the same user
5. Must not be an Oracle server reserved word
03: Data types & Guidelines
Data type:
1. VARCHAR2 2. CHAR 3. NUMBER 4. DATE 5. LONG 6. CLOB 7. RAW & LONG RAW 9. BFILE 10. ROWID
Guidelines:
• A LONG column is not copied when a table is created using a subquery.
• A LONG column cannot be included in a GROUP BY or an ORDER BY clause.
• Only one LONG column can be used per table.
• No constraints can be defined on a LONG column.
• You might want to use a CLOB column rather than a LONG column.
05: Including constraints & Constraints Guidelines
Including constraints:
a. Constraints enforce rules at the table level.
b. Constraints prevent the deletion of a table if there are dependencies.
c. The following constraint types are valid:
1. NOT NULL 2. UNIQUE 3. PRIMARY KEY 4. FOREIGN KEY 5.CHECK
Guidelines:
· You can name a constraint, or the Oracle server generates a name by using the SYS_Cn format.
· Define a constraint at the column or table level.
· View a constraint in the data dictionary.
06: Use/Functions the alter table statement
• Add a new column
• Modify an existing column
• Define a default value for the new column
• Drop a column
