Types of SQL keys

We have following types of keys in SQL which are used to fetch records from tables and to make relationship among tables or views.

1. Super Key

   Super key is a set of one or more than one keys that can be used to identify a record uniquely in a table.Example :Primary key, Unique key, Alternate key are subset of Super Keys.

2. Candidate Key

   A Candidate Key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key.

3. Primary Key

   Primary key is a set of one or more fields/columns of a table that uniquely identify a record in database table. It can not accept null, duplicate values. Only one Candidate Key can be Primary Key.

4. Alternate key

   A Alternate key is a key that can be work as a primary key. Basically it is a candidate key that currently is not primary key.

5. Composite/Compound Key

   Composite Key is a combination of more than one fields/columns of a table. It can be a Candidate key, Primary key.

6. Unique Key

   Uniquekey is a set of one or more fields/columns of a table that uniquely identify a record in database table. It is like Primary key but it can accept only one null value and it can not have duplicate values.

7. Foreign Key

   Foreign Key is a field in database table that is Primary key in another table. It can accept multiple null, duplicate values.

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

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.

What is SDL(Storage Definition Language)?

This language is to specify the internal schema. This language may specify the mapping between two schemas

What is VDL(View Definition Language)?

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

Integrity rules

In SQL we have two integrity rules:

• Entity Integrity- states that PRIMARY KEY cannot have NULL values.
• Referential Inntegrity- states that foreign key can be either a NULL value or should be PRIMARY KEY value of other relation

Describe subquery

A subquery 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

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

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

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

Difference betwween UNION and UNION ALL

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

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

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

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

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

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

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

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

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

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:

1. Easy to read and understand.
2. Can be used by those having little or no programming experience.
3. It is a non-procedural language.
4. It is based upon relational algebra and relational tuple calculus.

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