SQL Interview Question part-1
1. What do you understand by SQL?
2. What are different phases of normalization?
3. Explain different kinds of keys used in Database?
4. What is the difference between primary and unique key?
Answer:
Primary key and Unique key enforce Uniqueness of the column on which they are defined.
But by default PK creates Clustered index on Col and Unique creates Non-Clustered index.
PK does not allow Null, whereas Unique Key allows one NULL only.
5. What are constraints?
6. Explain different types of constraints with an example?
1.0 DELETE
The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows . If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.
Note that this operation will cause all DELETE triggers on the table to fire.
2.0 TRUNCATE
TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUNCATE is faster and doesn't use as much undo space as a DELETE.
3.0 DROP
The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.
DELETE
TRUNCATE
DROP
you need to COMMIT or ROLLBACK
few Rows also can be delete
the table structure remains the same,
The operation cannot be rolled back
TRUNCATE is faster than Delete
Used to remove whole table
the table structure remains the same,
The operation cannot be rolled back.
the table structure is also deleted.
JOINS
JOIN: Return rows when there is at least one match in both tables
LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
FULL JOIN: Return rows when there is a match in one of the tables
SQL INNER JOIN Keyword
The INNER JOIN keyword return rows when there is at least one match in both tables.
SQL INNER JOIN Syntax
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
Example:
1
SELECT selection_list
2
FROM table_A
3
INNER JOIN table_B ON join_condition
4
WHERE row_conditions.
We can simplify the inner join as
1 SELECT productID,productName,categoryName
2
FROM products, categories
3
WHERE products.categoryID = categories.categoryID
SQL LEFT JOIN (LEFT OUTER JOIN) Keyword
The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).
SQL LEFT JOIN Syntax
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
PS: In some databases LEFT JOIN is called LEFT OUTER JOIN.
SQL RIGHT JOIN Keyword
PS: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.
The RIGHT JOIN keyword returns all the rows from the right table (table_name2), even if there are no matches in the left table (table_name1).
SQL RIGHT JOIN Syntax
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
SQL FULL JOIN Keyword
The FULL JOIN keyword return rows when there is a match in one of the tables.
SQL FULL JOIN Syntax
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
4.0 The SQL UNION Operator
The UNION operator is used to combine the result-set of two or more SELECT statements.
Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.
i. SQL UNION Syntax
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
Note: The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL. That’s why union is slower than UNION ALL.
ii. SQL UNION ALL Syntax
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
PS: The column names in the result-set of a UNION are always equal to the column names in the first SELECT statement in the UNION.
5.0 Oracle/PLSQL: Oracle System Tables
Below is an alphabetical listing of the Oracle system tables that are commonly used.
System Table
Description
ALL_ARGUMENTS
Arguments in object accessible to the user
ALL_CATALOG
All tables, views, synonyms, sequences accessible to the user
ALL_COL_COMMENTS
Comments on columns of accessible tables and views
ALL_CONSTRAINTS
Constraint definitions on accessible tables
ALL_CONS_COLUMNS
Information about accessible columns in constraint definitions
ALL_DB_LINKS
Database links accessible to the user
ALL_ERRORS
Current errors on stored objects that user is allowed to create
ALL_INDEXES
Descriptions of indexes on tables accessible to the user
ALL_IND_COLUMNS
COLUMNs comprising INDEXes on accessible TABLES
ALL_LOBS
Description of LOBs contained in tables accessible to the user
ALL_OBJECTS
Objects accessible to the user
ALL_OBJECT_TABLES
Description of all object tables accessible to the user
ALL_SEQUENCES
Description of SEQUENCEs accessible to the user
ALL_SNAPSHOTS
Snapshots the user can access
ALL_SOURCE
Current source on stored objects that user is allowed to create
ALL_SYNONYMS
All synonyms accessible to the user
ALL_TABLES
Description of relational tables accessible to the user -- It list the percentage of Used space as well.
ALL_TAB_COLUMNS
Columns of user's tables, views and clusters
ALL_TAB_COL_STATISTICS
Columns of user's tables, views and clusters
ALL_TAB_COMMENTS
Comments on tables and views accessible to the user
ALL_TRIGGERS
Triggers accessible to the current user
ALL_TRIGGER_COLS
Column usage in user's triggers or in triggers on user's tables
ALL_TYPES
Description of types accessible to the user
ALL_UPDATABLE_COLUMNS
Description of all updatable columns
ALL_USERS
Information about all users of the database
ALL_VIEWS
Description of views accessible to the user
DATABASE_COMPATIBLE_LEVEL
Database compatible parameter set via init.ora
DBA_DB_LINKS
All database links in the database
DBA_ERRORS
Current errors on all stored objects in the database
DBA_OBJECTS
All objects in the database
DBA_ROLES
All Roles which exist in the database
DBA_ROLE_PRIVS
Roles granted to users and roles
DBA_SOURCE
Source of all stored objects in the database
DBA_TABLESPACES
Description of all tablespaces
DBA_TAB_PRIVS
All grants on objects in the database
DBA_TRIGGERS
All triggers in the database
DBA_TS_QUOTAS
Tablespace quotas for all users
DBA_USERS
Information about all users of the database
DBA_VIEWS
Description of all views in the database
DICTIONARY
Description of data dictionary tables and views
DICT_COLUMNS
Description of columns in data dictionary tables and views
GLOBAL_NAME
global database name
NLS_DATABASE_PARAMETERS
Permanent NLS parameters of the database
NLS_INSTANCE_PARAMETERS
NLS parameters of the instance
NLS_SESSION_PARAMETERS
NLS parameters of the user session
PRODUCT_COMPONENT_VERSION
version and status information for component products
ROLE_TAB_PRIVS
Table privileges granted to roles
SESSION_PRIVS
Privileges which the user currently has set
SESSION_ROLES
Roles which the user currently has enabled.
SYSTEM_PRIVILEGE_MAP
Description table for privilege type codes. Maps privilege type numbers to type names
TABLE_PRIVILEGES
Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee
TABLE_PRIVILEGE_MAP
Description table for privilege (auditing option) type codes. Maps privilege (auditing option) type numbers to type names
COMMAND TYPE
Command names
Data Definition Language (DDL) Statements
· Create, alter, and drop schema objects
· Grant and revoke privileges and roles
[CAD GR]
These are AUTOCOMMIT
6.0 Data Manipulation Language (DML) Statements
· CALL
· DELETE
· EXPLAIN PLAN
· INSERT
· LOCK TABLE
· MERGE
· SELECT
· UPDATE
[UDI-SM]
NOT AUTOCOMMIT
7.0 Transaction Control Statements (TCL)
· COMMIT
· ROLLBACK
· SAVEPOINT
· SET TRANSACTION
Used to manage changes done by DML command
8.0 Session Control Statements
· ALTER SESSION
· SET ROLE
9.0 Data Control Language (DCL)
GRANT
REVOKE
What are the predefined oracle exceptions?
Following is the predefined oracle exceptions
No_data_found
Too_many_rows
Zero_divide
Login_denied
Program_error
Timeout_on_resource
Invalid_cursor
Cursor_already_open
Dup_val_on_index
Explain user defined exceptions in oracle.
User defined exception in oracle are defined by the user. They need to be explicitly raised by the user using the RAISE statement. Oracle is not aware of these exceptions unless declared.
Example:
DECLARE
Trans EXCEPTION;
BEGIN
IF TO_CHAR(SYSDATE, ‘DY’)= ‘SUN’ THEN
RAISE Trans;
END IF
EXCEPTION
WHEN trans THEN
DBMS_OUTPUT.PUT_LINE(‘No transactions today’);
END;
What are SQL functions in oracle?
There are two types of functions –
Single row that operates row by row . Functions that fall under single functions are Date, Numeric, Character, Conversion and miscellaneous function
Group function operates on multiple rows. Functions that fall under group functions are avg, max, min, count, and sum.
CURSOR
A cursor is a variable that runs through the tuples of some relation. This relation can be a stored table, or it can be the answer to some query.
There are different types of cursors but broadly classified into two :
Implicit cursor
Explicit cursor.
Every query that we issue on a database is an implicit cursor for eg. a select, an update, a delete etc.
On the other hand explicit cursor is a user defined cursor eg: cursor explicit_cur.
No comments:
Post a Comment