RDBMS unit-3 Semester -3 Important questions and answers (Answers are Based on UASC-KAkatiya University)

RDBMS Answer key 

Easy notes for students based on KU academics 

Unit - 3 

1Q: Explain about various sql commands with examples.

Ans :- 

SQL Commands :- 

• SQL commands are instructions. It is used to communicate with the database. It is also used to perform specific tasks, functions, and data queries.

• SQL can perform a variety of tasks such as creating a table, adding data to tables, dropping the table, modifying the table, and assigning permissions to users.

Types of SQL Commands :- 

SQL commands are classified into five types: DDL, DML, DCL, TCL, and DQL.

1. Data Definition Language (DDL) :-

• DDL alters the structure of the table by creating, deleting, or altering tables, among other things.

• All DDL commands are auto-committed, which means they save all changes to the database permanently.

DDL commands include the following:

• CREATE
• ALTER
• DROP
• TRUNCATE

2. Data Manipulation Language :- 

• DML commands are used to make changes to the database. It is in charge of all changes to the database.

• DML commands are not auto-committed, which means they cannot save all changes to the database permanently. They are reversible.

DML commands include the following:

• INSERT
• UPDATE
• DELETE

a) INSERT: The INSERT statement is a SQL query. It is used to insert data into the row of a table.

Syntax :- 

INSERT INTO TABLE_NAME    

(col1, col2, col3,.... col N)  

VALUES (value1, value2, value3, .... valueN);  

b) UPDATE: This command is used to update or modify the value of a column in the table.

Syntax :- 

UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE CONDITION]   

c) DELETE: It is used to remove one or more row from a table.

Syntax :- 

DELETE FROM table_name [WHERE condition];  

3. Data Control Language :-

DCL commands are used to grant and take back authority from any database user.

DCL Command include the following: 

• Grant
• Revoke

a) Grant :- 

       It is used to give user access privileges to a database.

b) Revoke :- 

       It is used to take back permissions from the user.

4. Transaction Control Language :- 

• TCL commands can only be used with DML commands such as INSERT, DELETE, and UPDATE.

• Because these operations are automatically committed in the database, they cannot be used when creating or dropping tables.

TCL commands include the following:

• COMMIT
• ROLLBACK
• SAVEPOINT

a) Commit :- Commit command is used to save all the transactions to the database.

Syntax :- 

COMMIT;  

b) Rollback :- Rollback command is used to undo transactions that have not already been saved to the database.

Syntax :- 

ROLLBACK;  

c) SAVEPOINT: It is used to roll the transaction back to a certain point without rolling back the entire transaction.

Syntax :- 

SAVEPOINT SAVEPOINT_NAME;  

5. Data Query Language :- 

DQL is used to fetch the data from the database. 

It uses only one command:

• SELECT 

SELECT  :-

This is the same as the relational algebra projection operation. It is used to choose an attribute based on the condition specified by the WHERE clause.

Syntax :- 

SELECT expressions    

FROM TABLES    

WHERE conditions;  

[ Note :- Students Can skip sub points of DDL, DML, DCL, TCL, and DQL for quick learning]

2Q: Operators in SQL.

Ans :-

An operator is a reserved word or character that is used in a SQL expression to query our database. A WHERE clause is used to query a database using operators. Operators are required to define a condition in SQL because they serve as a link between two or more conditions. Based on the operator's functionality, the operator manipulates the data and returns the result.

Generally, there are three types of operators that are used in SQL :

1. Arithmetic Operators

2. Comparison Operators

3. Logical Operators

Arithmetic SQL Operators :- 

Arithmetic operators perform arithmetic operations like addition, subtraction, division, and multiplication. These operators typically take numeric operands. The following operators are included in this category:

Comparison SQL Operators :- 

In SQL, comparison operators are used to determine the equality of two expressions. It determines whether one expression is the same as another. Comparison operators are commonly used in SQL queries' WHERE clause. A comparison operation can produce TRUE, FALSE, or UNKNOWN results. If one or both of the expressions are NULL, the operator returns UNKNOWN.

The table below illustrates the various types of SQL comparison operators:

Logical SQL Operators :- 

Logical operators are those that take two expressions as operands and output TRUE or FALSE. When working with complex SQL statements and queries, comparison operators come in handy, and they function similarly to logic gates.

 The table below lists the various logical operations available in SQL.

3Q: Explain Data Manipulation Languages (DML)

Ans :- 

DML: Data Manipulation Language :- 

DML, or Data Manipulation Language, is a subset of database operations used to insert, delete, and update data. A DML is frequently a sublanguage of a larger language, such as SQL; DML includes some of the language's operators. Because some users can perform both read and write selection, selecting read-only data is closely related to and sometimes considered a component of a DML.

Structured Query Language, or SQL, is a popular data manipulation language that is used to retrieve and manipulate data in a relational database. SQL allows you to perform database operations as well as create databases. SQL performs the required tasks by using specific commands such as Create, Drop, Insert, and so on.

Data Manipulation Language is abbreviated as DML. Represents a set of programming languages that are explicitly used to modify the database, such as:

• CRUD operations for creating, reading, updating, and deleting data.

• INSERT, SELECT, UPDATE, and DELETE commands are used.

• DML commands are frequently part of a larger database language, such as SQL (structured query language). These DMLs may have their own syntax for dealing with data in that language.

4Q: Explain DDL commands. 

Ans :- 

Data Definition Language (DDL) :- 

DDL is a computer language that is used to create and modify the layout of database objects in a database. These database objects are made up of various elements such as views, tables, indexes, schemas, and so on. Because it determines the fields and records in the database table, this is sometimes referred to as a data description language.

DDL commands of various types can be used to add, delete, or modify tables in a database. CREATE, ALTER, DROP, TRUNCATE, and RENAME are DDL commands. 

1. Create :- 

Create commands have a specific syntax and are frequently used to create a new table. 

The CREATE language statement is as follows:

[table name] CREATE TABLE ([column definitions]) [parameters for the table];

2. Alter :- 

An ALTER command has the ability to modify an existing database table. In a nutshell, this command will change the table.

3. Drop :-

The DROP command can delete any object, such as a table, index, or view. If we use the DROP command to delete an object, there is no way to recover it. 

The following is a DROP Command language statement:

TABLE table name DROP

4. Truncate :- 

The TRUNCATE command is similar to the DROP command. It is typically used when you want to remove all of the data from a table immediately. The DROP command, on the other hand, does not allow us to retrieve data, whereas TRUNCATE allows us to save the structure to be reused later. 

The TRUNCATE command language statement is as follows:

TABLE table name TRUNCATE;

5. Rename :- 

The rename command is used to change the name of an existing database object, such as a table or column, to something new. You won't have to worry about data loss while renaming the data because it will be secure. 

The rename command language statement is as follows:

CHANGE THE TABLE NAME 'current table name' TO 'new table name';

5Q: Explain about order by, group by, and having clauses.

Ans :- 

1. SQL – ORDER BY :- 

The SQL ORDER BY statement is used to sort the retrieved data in ascending or descending order based on one or more columns.

• ORDER BY sorts the data in ascending order by default.

• We can sort the data in descending order by using the keyword DESC and ascending order by using the keyword ASC.

2. SQL - GROUP BY :- 

The SQL GROUP BY Statement is used to group identical data into groups using some functions. If a particular column has the same values in multiple rows, it will group these rows.

• The GROUP BY clause is used in conjunction with the SELECT statement.
• The GROUP BY clause comes after the WHERE clause in the query.
• If any, the GROUP BY clause is placed before the ORDER BY clause in the query.

3. SQL - Having Clause :- 

One can use the HAVING Clause to specify conditions that control which group results appear in the results.

The WHERE clause applies conditions to the columns that have been chosen, whereas the HAVING clause applies conditions to groups formed by the GROUP BY clause.

6Q : Joins in SQL. 

Ans :- 

JOINS :-

SQL Join is used to fetch data from two or more tables that appear as single set of data. SQL Join is used for combining column from two or more tables by using values common to both tables. Join Keyword is used in SQL queries for joining two or more tables. Minimum required condition for joining table, is (n-1) where n, is number of tables. A table can also join to itself known as, Self Join. A SQL Join condition is used in the SQL WHERE Clause of select, update, delete statements. 

Examples :- 

1. SELECT Empno, Ename, Dname, Loc FROM Emp, Dept; 
2. SELECT Empno, Ename, Sal, Grade FROM Emp, SalGrade;
3. SELECT Empno, Ename, Dname, Loc, SalGrade FROM Emp, Dept, SalGrad

JOIN Condition :- 

• Many join queries contain WHERE clause, which compares two columns, each from a different table.
• The applied condition on join is called a JOIN CONDITION.
• To execute a join... Oracle combines pairs of rows, each containing one row from each table, for which the join condition evaluates to TRUE.
• The where clause of join query can also contain other conditions that refer to columns of only one table.

7Q: Basic structure of SQL.

Ans :- 

SQL query structure :- 

The basic structure of SQL queries consists of three clauses: select, from, and where. The select clause specifies what we want in the final result relation. The from clause specifies which relations we must access to obtain the result. The where clause specifies how the relation must be operated to produce the desired result.

• One must specify the attributes that you want to see in the result relation in the select clause.

• In the from clause, you must specify the list of relations that must be accessed in order to evaluate the query.

• The where clause includes a predicate that includes attributes of the relations listed in the from clause.

8Q: Explain the concept of sequence and its application in SQL with the syntax and examples.

Ans :- 

SQL - SEQUENCES :-

A sequence is a collection of integers 1, 2, 3,... that are generated and supported by some database systems in order to generate unique values on demand.

1. A sequence is a user-defined schema bound object that generates a sequence of numeric values.
2. Sequences are commonly used in many databases because many applications require each row in a table to contain a unique value, and sequences provide an easy way to generate them.
3. The sequence of numeric values is generated in ascending or descending order at defined intervals and can be configured to restart when max value is exceeded.

Syntax :- 

CREATE SEQUENCE sequence_name

START WITH initial_value

INCREMENT BY increment_value

MINVALUE minimum value

MAXVALUE maximum value

CYCLE|NOCYCLE ;

9Q : what are views and sequences.

Ans :- 

Views in DBMS :- 

Views are virtual tables that are created from the original table. Views make SQL queries easier to understand and provide secure access to underlying tables. Views in DBMS can be visualised as virtual tables formed by original database tables.

The view has two primary purposes:

1. Simplifying complex SQL queries.
2. Restricting users from accessing sensitive data.

Sequence in DBMS :- 

Sequences are a feature implemented by some DBMS products to provide users with a mechanism for generating unique values - the Sequence ensures that each call to it returns a unique value. This is especially important if the Sequence's output is used as a Primary Key. These can be created with a schema and then loaded onto the DBMS server.

10Q: what is view? differentiate view with the table.

Ans :- 

A view is a database object that allows generating a logical subset of data from one or more tables. A table is a database object or an entity that stores the data of a database. The view depends on the table. The table is an independent data object.

Click on image for clear view