Question: – 4(a) Discuss the DIVISION operation. How is it represented, and what are the requirements of the numerator and denominator relations? Explain with an example.
Answer: –
The division operator is used when we have to evaluate queries which contain the keyword ALL.
Some instances where division operator is used are:
- Which person has account in all the banks of a particular city?
- Which students have taken all the courses required to graduate?
In above specified problem statements, the description after the keyword 'all' defines a set which contains some elements and the final result contains those units which satisfy these requirements.
Select all records from a table
A special character asterisk * is used to address all the data(belonging to all columns) in a query. SELECT statement uses * character to retrieve all records from a table, for all the columns.
SELECT * FROM student;The above query will show all the records of student table, that means it will show complete dataset of the table.
| s_id | name | age | address |
|---|---|---|---|
| 101 | Adam | 15 | Chennai |
| 102 | Alex | 18 | Delhi |
| 103 | Abhi | 17 | Banglore |
| 104 | Ankit | 22 | Mumbai |
Question: – What is view? How is it different from table? How views can be created, altered and destroyed?
Answer: – A database view is a searchable object in a database that is defined by a query. Though a view doesn’t store data, some refer to a views as “virtual tables,” you can query a view like you can a table. A view can combine data from two or more table, using joins, and also just contain a subset of information. This makes them convenient to abstract, or hide, complicated queries.
The reasons why views are created are:
- When Data security is required .
- When Data redundancy is to be kept to the minimum while maintaining data security .
Types of views :
- Read-only View : Allows only SELECT operations.
- Updateable View : Allows SELECT as well as INSERT , UPDATE and DELETE operations.
Creating a View :
The ORDER BY clause cannot be used while creating a view. The columns of the table are related to the view using a one-to-one relationship.
Syntax:
CREATE <OR REPLACE> VIEW <ViewName> AS SELECT <ColumnName1 >, <ColumnName2> FROM <TableName> WHERE <ColumnName> = < Expression List> <WITH READ ONLY> ;
This statements creates a view based on query specified in SELECT statement.
OR REPLACE option recreates the view if it is already existing maintaning the privileges granted to view viewname.
WITH READ ONLY option creates readonly view.
Example :
Creating a view stu based on student table and than update it.
Run SQL Command Line–>
SQL>create view stu as select enroll,name from student;
View Created.
<———————————————->
SQL>select * from stu;
ENROLL NAME
———– ———-
4866 ABCD
4546 BDSG
Updateable Views :
Views can also be used for data manipulation . Views on which data manipulation can be done are called Updateable Views.
When an updateable view name is given in an Insert Update, or Delete SQL statement, modifications to data in the view will be immediately passed to the underlying table.
For a view to be updateable, it should meet the following criteria:
- Views defined from Single table
- If the user wants to INSERT records with the help of a view, then the PRIMARY KEY column(s) and all the NOT NULL columns must be included in the view .
- The user can UPDATE, DELETE records with the help of a view even if the PRIMARY KEY column and NOT NULL column(s) are excluded from the view definition .
Example :
SQL>update stu set name=’xyz’ where enroll=4866;
1 Row updated.
<————————————————————>
SQL>select * from stu;
ENROLL NAME
———– ———-
4866 xyz
4546 BDSG
Destroying a View :
The drop command drops the specified view.
Syntax :
DROP VIEW Viewname;
Example:
SQL>drop view stu;
View dropped.
Benefits of a Database View
There are many benefits to using views. Listed below are some of the one that come to mind:
- Enforce Business Rules– Use views to define business rules, such as when an items is active, or what is meant by “popular.” By placing complicated or misunderstood business logic into the view, you can be sure to present a unified portrayal of the data. This increases use and quality.
- Consistency– Simplify complicated query logic and calculations by hiding it behind the view’s definition. Once defined they calculations are reference from the view rather than being restated in separate queries. This makes for less mistakes and easier maintenance of code.
- Security– Restrict access to a table, yet allow users to access non-confidential data via views. For example, you can restrict access to the employee table, that contains social security numbers, but allow access to a view containing name and phone number.
- Simplicity– Databases with many tables possess complex relationships, which can be difficult to navigate if you aren’t comfortable using Joins. Use views to provide a “flattened” view of the database for reporting or ad-hoc queries.
- Space– Views take up very little space, as the data is stored once in the source table. Some DBMS all you to create an index on a view, so in some cases views do take up more space than the definition.
Disadvantages of Views
- Performance– What may seem like a simple query against a view could turn out to be a hugely complex job for the database engine. That is because each time a view is referenced, the query used to define it, is rerun.
- Modifications– Not all views support INSERT, UPDATE, or DELETE operations. In general, in order to support these operations, the primary key and required fields must be present in the view. Complex multi-table views are generally read only.
Question: – 5(a) Discuss different types of update operations on a relation with an example.
Answer: – The following are the different types of update operation in relation to the integrity constraints which must be satisfied in a relational database model.
- The SQL CREATE DATABASEstatement is used to create a new SQL database.
- The basic syntax of this CREATE DATABASE statement is as follows −
- CREATE DATABASE DatabaseName;
- Always the database name should be unique within the RDBMS.
- The SQL DROP DATABASEstatement is used to drop an existing database in SQL schema.
The basic syntax of DROP DATABASE statement is as follows −
DROP DATABASE DatabaseName;
Always the database name should be unique within the RDBMS.
- Creating a basic table involves naming the table and defining its columns and each column’s data type.
- The SQL CREATE TABLEstatement is used to create a new table.
The basic syntax of the CREATE TABLE statement is as follows −
CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ….. columnN datatype, PRIMARY KEY( one or more columns ));
CREATE TABLE is the keyword telling the database system what you want to do. In this case, you want to create a new table. The unique name or identifier for the table follows the CREATE TABLE statement.
Then in brackets comes the list defining each column in the table and what sort of data type it is. The syntax becomes clearer with the following example.
A copy of an existing table can be created using a combination of the CREATE TABLE statement and the SELECT statement.
- The SQL DROP TABLEstatement is used to remove a table definition and all the data, indexes, triggers, constraints and permission specifications for that table.
NOTE − You should be very careful while using this command because once a table is deleted then all the information available in that table will also be lost forever.
The basic syntax of this DROP TABLE statement is as follows −
DROP TABLE table_name;
- The SQL INSERT INTOStatement is used to add new rows of data to a table in the database.
There are two basic syntaxes of the INSERT INTO statement which are shown below.
INSERT INTO TABLE_NAME (column1, column2, column3,…columnN) VALUES (value1, value2, value3,…valueN);
Here, column1, column2, column3,…columnN are the names of the columns in the table into which you want to insert the data.
You may not need to specify the column(s) name in the SQL query if you are adding values for all the columns of the table. But make sure the order of the values is in the same order as the columns in the table.
The SQL INSERT INTO syntax will be as follows −
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,…valueN);
- The SQL UPDATEQuery is used to modify the existing records in a table. You can use the WHERE clause with the UPDATE query to update the selected rows, otherwise all the rows would be affected.
The basic syntax of the UPDATE query with a WHERE clause is as follows −
UPDATE table_nameSET column1 = value1, column2 = value2…., columnN = valueNWHERE [condition];
You can combine N number of conditions using the AND or the OR operators.
- The SQL DELETE Query is used to delete the existing records from a table.
You can use the WHERE clause with a DELETE query to delete the selected rows, otherwise all the records would be deleted.
The basic syntax of the DELETE query with the WHERE clause is as follows −
DELETE FROM table_nameWHERE [condition];
You can combine N number of conditions using AND or OR operators.
Question: – 5(b) What are various type of inner join operations? Why is theta join required?
Answer: –
Different Types of SQL JOINs
Here are the different types of the JOINs in SQL:
- (INNER) JOIN: Returns records that have matching values in both tables
- LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table
- RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table
- FULL (OUTER) JOIN: Return all records when there is a match in either left or right table
Theta Join:
In theta join we apply the condition on input relation(s) and then only thoseselected
rows are used in the cross product to be merged and included in the output. It means
that in normal cross product all the rows of one relation are mapped/merged with all
the rows of second relation, but here only selected rows of a relation are made cross
product with second relation.
If R and S are two relations then is the condition, which is applied for select
operation on one relation and then only selected rows are cross product with all the
rows of second relation. For Example there are two relations of FACULTY and
COURSE, now we will first apply select operation on the FACULTY relation for
selection certain specific rows then these rows will have across product with
COURSE relation, so this is the difference in between cross product and theta join.
We will now see first both the relation their different attributes and then finally the
cross product after carrying out select operation on relation.
This article is contributed by Tarun Jangra. Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.