Some basic questions about SQL

1.        What is SQL?

SQL stands for Structured Query Language. SQL is a simple and powerful language used to create, access, and manipulate data and structure in a database. SQL is like plain English, easy to understand and write. SQL is a non-procedural language.

Features of SQL:

·         Easy to read and understand.

·         Can be used by those having little or no programming experience.

·         It is a non-procedural language.

·         It is based upon relational algebra and relational tuple calculus.

·         SQL was designed by Donald D. Chamberlin and Raymond F. Boyce.

2.  What are various categories or statements in SQL?

   Oracle divides SQL statements into various categories, which are:

·         DDL (Data Definition Language)

·         DML (Data Manipulation Language) 

·         DCL (Data Control Language)

·         TCL (Transaction Control Language)

·         Embedded SQL statements

3. What are DDL statements?

DDL (Data Defintion Language) are those statements which are used to define, alter, or drop database objects. These are:

CREATE- used to create schema objects

ALTER- used to alter schema objects

DROP- used to delete schema objects

RENAME- used to rename schema objects

4. What are DML statements?

DML (Data Manipulation Language) statements enable users to query or manipulate data in existing schema objects. These are:

DELETE- used to remove rows from tables or views

INSERT- used to add new rows of data into tables or views

SELECT- used to retrieve data from one or more tables

UPDATE- used to change column values in existing rows of a table or view

5. What are DCL statements?

DCL (Data Control Language) are used to grant or revoke privileges from a user. These are:

·         GRANT- used to grant a privilege

·         REVOKE- used to revoke a privilege

·         COMMENT- used to add a comment to the data dictionary

6. What are TCL statements?

TCL (Transaction Control Language) statements manage the change made by DML statements, and group DML statements into transactions. These are:

·         COMMIT- used to make a transaction's changes permanent

·         ROLLBACK- used to undo changes in a transaction, either since the transaction started or since a savepoint

·         SAVEPOINT- used to set point to which a transaction can be rolled back

·         SET TRANSACTION- used to establish properties for a transaction

7. What are Embedded SQL statements?

Embedded SQL statements are used to incorporate DDL, DML and TCL statements within the body of a procedural  language program. These are:

·         DEFINE- used to define cursors

·         OPEN-  used to allocate cursors

·         DECLARE- used to assign variable names

·         EXECUTE- used to execute SQL statements

·         FETCH- used to retrieve data from database

8.  What are DQL statements?

DQL (Data Query Language) statement is used to query data from the database.

SELECT- used to get rows and/or columns from tables or views

  

9. Authentication and Authorization

Authentication

Authentication is the process of verifying the identity of a user using some credentials like username and password. Authentication merely ensures that the individual is who he or she claims to be, but says nothing about the access rights of the individual.

Authorization

The process of granting or denying access to a network resource.  Authorization determines the parts of the system to which a particular identity has access.

Authentication is required before Authorization.

For e.g. If an employee authenticates himself with his credentials on a system, authorization will determine if he has the control over just publishing the content or also editing it

10. Difference between TRUNCATE and DELETE

Both result in deleting of the rows in the table

·         TRUNCATE call cannot be rolled back  and all memory space for that table is released back to the server while     DELETE call can be rolled back

·           TRUNCATE call is DDL command while DELETE call is DML command

·           TRUNCATE call is faster than DELETE call

11. Difference between UNION and UNION ALL

    UNION will remove the duplicate rows from the result set while UNION ALL does not.

12. What is a Candidate Key?

A table may have more than one combination of columns that could uniquely identify the rows in a table, each combination is a Candidate Key

13. What is PL/SQL?

PL/SQL is Oracle's Procedural Language extension to SQL. The language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance), and so, brings state-of-the-art programming to the Oracle database server and a variety of Oracle tools

14. What is a cascading update?

Referential integrity constraints require that foreign key value in one table correspond to primary key values in another. If the value of the primary key is changed, that is, updated, the value of the foreign key must immediately be changed to match it. Cascading updates will set this change to be done automatically by the DBMS whenever necessary

15. Describe subquery

A sub query is a query that is composed of two queries. The first query (inner query) is within the WHERE clause of the outer query. In some cases the inner query provides results for the outer query to process. In other cases, the outer query results provide results for the inner query

16. Integrity rules

·         In SQL we have two integrity rules:

·         Entity Integrity- states that PRIMARY KEY cannot have NULL values.

·         Referential Integrity- states that foreign key can be either a NULL value or should be PRIMARY KEY                           value of other relation

17. What is VDL (View Definition Language)?

It specifies user views and their mappings to the conceptual schema.

18. What is SDL (Storage Definition Language)?

This language is to specify the internal schema. This language may specify the mapping

between two schemas

19. What is normalization?

It is a process of analysing the given relation schemas based on their Functional Dependencies (FDs) and primary key to achieve the properties:

Minimizing redundancy.

Minimizing insertion, deletion and update anomalies.

20. What is 1 NF (Normal Form)?

The domain of attribute must include only atomic (simple, indivisible) values.

21. What is functional dependency?

A functional dependencies is denoted by X--->Y between two sets of attributes X and Y that are subsets of R. This means that the value of X component of a tuple uniquely determines the value of component Y