SQL

Question 1

Normalization

Answer 1

Normalization is a database design technique that reduces data redundancy and eliminates undesirable characteristics like Insertion, Update and Deletion Anomalies. Normalization rules divides larger tables into smaller tables and links them using relationships. The purpose of Normalisation in SQL is to eliminate redundant (repetitive) data and ensure data is stored logically.

I.e. 
1NF (First Normal Form)
2NF (Second Normal Form)
3NF (Third Normal Form)
BCNF (Boyce-Codd Normal Form)
4NF (Fourth Normal Form)
5NF (Fifth Normal Form)
6NF (Sixth Normal Form)

Question 2

DDL commands

Answer 2

Data Definition Language simply deals with descriptions of the database schema and is used to create, modify and delete the structures of database objects, but not the actual data itself.

• Create:
  used to create the database or its objects
• Drop:
  used to delete objects from the database.
• Alter:
  used to alter the structure of the database.
• Truncate:
  used to remove all records from a table, including all spaces allocated for the records are removed.
• Comment:
  used to add comments to the data dictionary.
• Rename:
  used to rename an object existing in the database.

Question 3

DQL commands

Answer 3

Data Query Language is a component of a SQL statement that allows the programmer to perform queries on the data within a schema from a database and, also, allows the programmer to impose order upon it.

Select:
used to retrieve data from the database.

Question 4

DML commands

Answer 4

Data Manipulation Language is the component of a SQL statement that controls access to data and to the database itself, and generally deals with the manipulation of data.

• Insert:
  used to insert data into a table.
• Update:
  used to update existing data within a table.
• Delete:
  used to delete records from a database table.

Question 5

DCL commands

Answer 5

Data Control Language includes commands which mainly deal with the rights, permissions, and other controls of the database system.

• Grant:
  gives users access privileges to the database.
• Revoke:
  withdraws the user’s access privileges given by using the GRANT command.

Question 6

TCL commands

Answer 6

Resources claim there to be another category of SQL clauses that deal with transactions in the database known as Transaction Control Language.

• Commit:
  Commits a Transaction.
• Rollback:
  Rollbacks a transaction in case of any error occurs.
• Savepoint:
  Sets a savepoint within a transaction.

-Set Transaction:
Specify characteristics for the transaction.

Question 7

ROWID (related to Normalization)

Answer 7

1. ROWID is nothing but Physical memory allocation
2. ROWID is permanent to that row which identifies the address of that row.
3. ROWID is 16 digit Hexadecimal number which is uniquely identifies the rows.
4. ROWID returns PHYSICAL ADDRESS of that row.
5. ROWID is automatically generated unique id of a row and it is generated at the time of insertion of row.
6. ROWID is the fastest means of accessing data.

Question 8

ROWNUM (related to Normalization)

Answer 8

1. ROWNUM is nothing but the sequence which is allocated to that data retrieval bunch.
2. ROWNUM is temporarily allocated sequence to the rows.
3. ROWNUM is numeric sequence number allocated to that row temporarily.
4. ROWNUM returns the sequence number to that row.
5. ROWNUM is an dynamic value automatically
   retrieved along with select statement output.
6. ROWNUM is not related to access of data.

Question 9

SQL functions

Answer 9

A function is a code snippet that can be executed on a SQL Server.

• There are 2 types of functions in SQL:
  1. User-Defined function: User-defined functions are create by a user.
  2. System Defined Function: System functions are built-in database functions.
• Rules for creating functions:
• A function must have a name and a function name can never start with a special character such as @, $, #, and so on.
• Functions only work with select statements.
• Functions can be used anywhere in SQL, like AVG, COUNT, SUM, MIN, DATE and so on with select statements.
• Functions compile every time.
• Functions must return a value or result.
• Functions only work with input parameters.
• Try and catch statements are not used in functions.

Question 10

aggregate functions

Answer 10

• COUNT counts how many rows are in a particular column.
• SUM adds together all the values in a particular column.
• MIN and MAX return the lowest and highest values in a particular column, respectively.
• AVG calculates the average of a group of selected values.

Question 11

joins

Question 12

left join

Question 13

inner join

Question 14

outer join

Question 15

right join

Question 16

full join / full outer join

Question 17

truncate vs delete

Question 18

having

Question 19

where

Question 20

varchar vs char