SQL Question Answer

Description

This document is a collection of questions with short and simple answers useful for learning SQL as well as for interviews.

What is DBMS ?

DBMS Stands for "Database Management System." The DBMS is a software package with computer programs. A DBMS is a database program. Technically speaking, it is a software system that uses a standard method of cataloging, retrieving, and running queries on data. The DBMS manages incoming data, organizes it, and provides ways for the data to be modified or extracted by users or other programs. A database is an integrated collection of data records, files, and other objects. DBMSs may use a variety of database models, such as the relational model , hierarchical model and network model. Some DBMS examples are, MySQL, PostgreSQL, Microsoft Access, SQL Server, FileMaker, Oracle, RDBMS, dBASE, Clipper, and FoxPro.

What is RDBMS ?

RDBMS, stands for Relational Database Management System is an information system that presents information as rows contained in a collection of tables, each table possessing a set of one or more columns . The data can be accessed or reassembled in many different ways without having to change the table forms. E. F. Codd described the elements of a relational database as relation, attributes, domains and the relation operators.

The term relation is used to describe these tables which is a more precise way of defining than the traditional data processing term "file" or "table".

What is the difference between DBMS and RDBMS ?

RDBMS can be made a server. It connects more systems but DBMS works only woke in the host system, RDBMS have Normalization. Normalization means to refinind the redundant and maintain the stabilization. the DBMS hasn't normalization concept.

DBMS is a broad area of database maintenance that also involves other database models as network model, object-oriented database model, and relation model. Here the relation model is called RDBMS. RDBMS stores data in the form of tables(collection of rows and columns). Normalization makes information nonredundant.

DBMS is a single user database, it does not support E.F.Codd rules and does not allow a relationship between tables. RDBMS is a multi-user database, it supports E.F.Codd rules and it allows relationship between tables

What is an entity?

An entity is any object, place, person, concept, activity about which an enterprise records data. It is an object which can have instances or occurrences. Each instance should be capable of being uniquely identified. Each entity has certain properties or attributes associated with it and operations applicable to it.

What is an Attributes?

Attributes are data elements that describe an entity. If the attribute of an entity has more attributes that describe it, then it is not an attribute of that entity, but another entity. Attributes can either be listed next to the entities, or placed in circles and attached to the entities.

Explain one-to-one relationship with an example?

For one occurrence of the first entity there can at most one related occurrences of the second entity and for one occurrence of the second entity, there can be at most one related occurrence of the second entry.

For example, the Order and Purchase table, one Order Requisition raises one purchase order and one purchase order is raised by one order requisition.

Explain one-to-many relationship with an example?

In one-to-many relationships, data into two tables with a primary key and foreign key relationship can be implemented. For one occurrence of the first entity there can exist many related occurrences of the second entity and for every occurrence of the second entity there exists only one associated occurrence of the first. For example, the Employees and Departments table, where the row in the Departments table is referenced by one or more rows in the Employees table. So, in a one-to-many relationship - one employee works in at most one department but one department can have many employees.

Explain many-to-many relationship with an example?

For one occurrence of the first entity many related occurrences of the second entity may exist and also for every occurrence of the second entity many associated occurrences of first entity may exist.
For example, suppose we have two table order and item - from here we can explain this relationship such a way that, one order may contain many items and one item can be contained in many orders.

What is normalization?

Normalization is the process of refining the data model built by the Entity-Relationship diagram. The Normalization technique logically groups the data over a number of tables, which are independent and contain no duplicate data. The entities or tables resulting from Normalization contain simple data items, with relationships being represented by replication of key data item(s). Normalization involves in dividing large tables into smaller tables and defining relationships between them. The need for normalization is to improve database design, ensures minimum redundancy of data, reduces the need to reorganize data when a design is modified or enhanced, removes anomalies for database activities. The first step towards normalization is to convert the E-R( Entity - Relationship) Model into Tables or Relations. The next step is to examine the tables for redundancy and if necessary., change them to non-redundant forms. This non-redundant model has then converted a database definition, which achieves the objective of the Database Design Phase.

How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?

One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.
Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

What is UDF (User Defined Function)?

A function is a subprogram that returns a value and must have a return value. A function has the similar structure of a procedure - the specification and the body. The function specification begins with the keyword FUNCTION and ends with the RETURN clause, which specifies the data type of the result value.

What is Stored Procedure?

The procedure can be stored in the database as stored programs and can be invoked whenever required. This avoids reparsing when multiple users invoke it. This also provides security and integrity control by allowing access on the subprogram and not on the database objects directly. The store procedure has a declarative part, an executable part, and an optional exception-handling part. The declarative part can contain declarations of types, cursors, constants, variables, exceptions and nested subprograms.

What is the difference between Function and Stored Procedure?

The function can be used in the SQL select statement but the procedure can not be used. A function is created using CREATE FUNCTION command and a procedure created by CREATE PROCEDURE command. A function must have a return clause and is used to return the control to the caller. Whereas a return statement can also be used in procedures, but it should not contain any expression and it returns control to the caller before the normal end of the procedure is reached.

What is PL / SQL?

PL /SQL ( Procedural Language / SQL ) is an extension to nonprocedural SQL. This language includes many features and designs of programming language. It combines the data manipulating power of SQL with the data processing power of procedural languages. PL / SQL allows the use of all the SQL data manipulation, cursor control, and transaction control commands, as well as all the SQL functions, operators, and Pseudocolumns. It provides performance improvements through blocking of RDBMS calls and support sub-programming features such as procedures and functions.

What are cursors?

A cursor is a PL/SQL construct that allows the user to name a private work areas and access their stored information. The PL / SQL construct to identify each and every work area used is called a cursor.
There are two types of rthe cursor - implicit and explicit. Implicit Cursors are declared by PL / SQL implicitly for all statements of DML and for single row queries i.e. the SELECT statements are used directly within the BEGIN and END block. The explicit Cursors are declared explicitly, along with other identifiers to be used in a block, and manipulated through proper statements written into the block’s executable actions. Explicit cursors are used for queries only, and allow multiple rows to be processed from the queries. The manipulation of the cursor is done through the DECLARE, OPEN, FETCH and CLOSE statements. Declaring a cursor, the cursor name is given and also defines the structure of the query to be performed on it. The CURSOR statement is used to declare a cursor. After doing the execution a cursor can be opened. After opening the cursor the rows returned by the query are available for fetching. After opening the cursor the current row is loaded into variables and the cursor is pointing to the current row. The retrieval of data is done through FETCH statement and each fetch causes the cursor to move its pointer to the next row in the active set. By closing a cursor, a working set of rows are released produced by last opening the cursor.

What is Trigger?

A trigger is a stored PL/SQL program unit associated with a specific database table. Unlike the stored procedures ( or functions ) which have to be explicitly invoked, these triggers implicitly get fired (executed) whenever the table is affected by any SQL operation.
The triggers are executed when an INSERT, UPDATE, or DELETE statement is issued against the associated table. These types of procedures are called database triggers. A trigger can include SQL and PL/SQL statements to execute as a unit and can invoke other stored procedures. However, procedures and triggers differ in the way that they are invoked. While a procedure is explicitly executed by a user or an application, a trigger is implicitly fired(executed) when a triggering INSERT, UPDATE, or DELETE statement is issued, no matter what user is connected or what application is being used.
A database trigger has three parts - triggering event, trigger constraint (optional) and trigger action.

What is View?

In RDBMS , a view is a virtual table because it does not exist in its own right, but appears to the user as if it did. A base table is a real table that exists in physical storage. A view does not have its own physically stored data but derives the data from the table it is associated with. It manipulates data in the underlying base table.
Views restrict access to the database. SELECTing from a view can display a restricted portion of the database.
As a view does not store any data, the redundancy problem does not arise. VIEWS allows users to make simple queries to retrieve the results from complicated queries.

What is Index?

Indexes provide a fast access path to columns that are indexed. Indexes are used to reference records in all the SQL statements, not just the query.
Indexes are stored separately from the actual data. Indexes are used to speed up the processes and ensure that no duplicate values are entered into a column. With every data manipulation, the appropriate index is automatically updated. If data is static and heavily queried, more indexes will help. If data is dynamic, lots of updates to the index columns will slow down the updates. It will be better and faster if data is loaded first and then an index is created.
Index do not have to activate or deactivated.
When using multiple columns, up to 16 columns or a maximum of 255 characters of column space can be indexed.

What is a Transaction?

A transaction is a sequence of one or more SQL statements that together forms a logical unit of work. Each statement in the transaction performs a part of the task, but all of them are required to complete the task. All statements forming a transaction must be executed for the database to be in a consistent state. A transaction occurs when the database is modified. The two SQL operations that support transaction processing are - The COMMIT operation and The ROLLBACK operation.
The COMMIT signifies a successful end-of-transaction and tells the DBMS that a logical unit of work has been successfully completed. The database is in a consistent state again and all the updates made by that transaction have been permanent.
The ROLLBACK indicate the unsuccessful end-of-transaction and tells DBMS that something has gone wrong, and the database may be in an inconsistent state. All the updates made by the transaction so far is undone.

Hope this question and answer turned useful for you. Subscribe to our RSS FEED since hundreds of similar questions are in pipeline. Thanks for reading.

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: SQL Injection