Unit – 2

Relational data Model and Language

Q1) Explain relational data model?

A1) Relational Data Model

The modelling of the data description, data semantics, and consistency constraints of the data is the Data Model. It offers conceptual resources at each level of data abstraction to explain the architecture of a database.

Data models are used to describe the design of a database at the logical level.

Some of the data models are: -

1. Entity-Relationship Model

2. Relational Model

3. Hierarchical Model

4. Network Model

5. Object Relational Model

Entity-Relationship(E-R) Model:

●      The logical structure of a database can be expressed by an E-R diagram.

●      Relationships are defined in this database model by dividing the object of interest into an entity and its characteristics into attributes.

●      Using relationships, various entities are related.

●      To make it easier for various stakeholders to understand, E-R models are established to represent relationships in pictorial form.

The Main Components of ER Diagram are:

a) Rectangles - Entity Sets

b) Ellipses - Attributes

c) Diamond - Relation among entity sets

d) Lines - Connects attributes to entity sets and entity sets to relationships.

Fig 1: E - R diagram

The above E-R diagram has two entities: Teacher and Department

Teacher entity has three attributes:

Teacher_id

Teacher_name

Teacher_Subject

Department  entity has two attributes:-

Dept_id

Dept_name

Relation Teaches in gives relationship between  Teacher, Department .

Relationship can be of the type:

1:1→One to one

1:M →One to many

M:1 →Many to one

M:M →Many to many

Relational Model:

●      Relational model uses a collection of tables to represent both data and the relationships among those data.

●      This model is useful for constructing a database that can then be converted into relational model tables.

●      Data is arranged in two-dimensional tables in this model and the relationship is preserved by storing a common field.

●      In the relational model, the basic structure of data is tables. In the rows of that table, all information relating to a specific category is stored.

Therefore, in the relational model, tables are also known as relations.

Sample relational database

1. Teacher Table

B.     Department Table

C.    Teaches in table

Hierarchical Model:

●      This model of the database organises information into a tree-like structure with a single root to which all the other information is connected. The hierarchy begins from the root data and extends to the parent nodes like a tree, adding child nodes.

●      A child node can only have a single parent node in this model.

●      A Hierarchical model uses tree structure to represent relationships among entities.

●      This model represents many real-world relationships effectively, such as a book index, recipes, etc.

●      Data is structured into a tree-like structure in the hierarchical model with a one-to-many relationship between two different data forms.

For instance, one department can have many classes, many professors and many students of course.

Fig 2: Hierarchical model

Network Model:

●      Network model uses two different data structures:

a) A record type is used to represent an entity set.

b) A set type is used to represent a directed relationship between two record types.

●      This is the Hierarchical model's extension. Data is arranged more like a graph in this model, and is allowed to have more than one parent node.

●      Data is more linked in this database model as more relationships are formed in this database model. Also, as the data is more linked, it is also simpler and quicker to access the information. To map many-to-many data relationships, this database model was used.

●      This was the most commonly used database architecture prior to the advent of the Relational Model.

Fig 3: Network model

Object Relational Model:

The object-oriented database derivation is the integrity of object-oriented

Programming language systems and consistent systems. The power of object-oriented databases comes from the cyclical treatment of both consistent data, as found in databases, and transient data, as found in executing programs.

Fig 4: Object - oriented model

Object-oriented databases use small, recyclable separated of software

Called objects. The objects themselves are stored in the object-oriented

Database. Each object contains of two elements:

1. Piece of data (e.g., sound, video, text, or graphics).

2. Instructions, or software programs called methods, for what to do with

The data.

There are two types of ORM:

a. Object-Oriented Data Model (OODM)

b. Object-Relational Data Model (ORDM)

These models are used to handle objects:

1) To provide support for complex objects.

2) To provide user extensibility for data types, operators and access methods.

3) There should be mechanism to support:

a) Abstract data type.

b) Data of type ‘procedure’.

c) Rules.

Q2) Describe integrity constraints?

A2) integrity constraints

Constraints on honesty are a set of rules. It is used to maintain information quality.

Integrity constraints ensure that it is important to perform data insertion, updating, and other procedures in such a way that data integrity is not compromised.

Therefore, to protect against unintended damage to the database, integrity constraints are used.

Fig 5: Types of integrity constraints

Domain Constraints

●      As a description of a valid set of values for an attribute, domain constraints can be specified.

●      The domain data type consists of a string, character, integer, time, date, currency, etc. In the corresponding domain, the value of the attribute must be available.

Fig 6: Example of domain constraints

Entity Integrity Constraints

●      The honesty restriction of the organization states that the primary key value should not be zero.

●      This is because the primary key value is used to define the relationship between individual rows, and if the primary key has a null value, then those rows cannot be identified.

●      A table may contain a null value other than the field for the primary key.

Fig 7: Example of entity constraints

Referential Constraints

●      Between two relations or tables, the referential integrity constraints are defined and used to preserve the consistency between the tuples in two relationships.

●      If an attribute of the foreign key of the relationship R1 has the same domain(s) as the primary key of the relationship R2, then the foreign key of R1 is said to refer to or refer to the primary key of the relationship R2.

●      Foreign key values in the R1 relationship tuple can either take the primary key values for a certain R2 relationship tuple, or they can take NULL values, but cannot be zero.

Fig 8: Example of integrity constraints

Key Constraints

●      Keys are the collection of entities used to uniquely define an object within its entity set.

●      A group of entities may have several keys, but the primary key will be one key. A primary key may contain in the relational table a unique and null value.

Fig 9: example of key constraints

Q3) What is relational algebra?

A3) Relational Algebra

Relational Algebra is procedural query language. It takes Relation as input and generates relation as output. Relational algebra mainly provides theoretical foundation for relational databases and SQL.

Basic Operations which can be performed using relational algebra are:

1. Projection

2. Selection

3. Join

4. Union

5. Set Difference

6. Intersection

7. Cartesian product

8. Rename

Consider following relation R (A, B, C)

1. Projection (π):

This operation is used to select particular columns from the relation.

Π(AB) R:

It will select A and B column from the relation R. It produces the output like:

Project operation automatically removes duplicates from the resultant set.

2.     Selection (σ):

This operation is used to select particular tuples from the relation.

σ(C<4) R:

It will select the tuples which have a value of c less than 4.

But select operation is used to only select the required tuples from the relation. To display those tuples on screen, select operation must be combined with project operation.

Π (σ (C<4) R) will produce the result like:

3.     Join 

A Cartesian product followed by a selection criterion is basically a joint process.

Operation of join, denoted by ⋈

The JOIN operation often allows tuples from different relationships to join different tuples

Types of JOIN:

Various forms of join operation are:

Inner Joins:

●      Theta join

●      EQUI join

●      Natural join

Outer join:

●      Left Outer Join

●      Right Outer Join

●      Full Outer Join

4.     Union

Union operation in relational algebra is the same as union operation in set theory, only the restriction is that both relations must have the same set of attributes for a union of two relationships.

Syntax:  table_name1 ∪ table_name2

For instance, if we have two tables with RegularClass and ExtraClass, both have a student column to save the student name, then,

∏Student(RegularClass) ∪ ∏Student(ExtraClass)

The above operation will send us the names of students who attend both normal and extra classes, reducing repetition.

5.     Set difference

Set Difference in relational algebra is the same operation of set difference as in set theory, with the limitation that the same set of attributes can have both relationships.

Syntax: A - B

Where the A and B relationships.

For instance, if we want to find the names of students who attend the regular class, but not the extra class, then we can use the following procedure:

∏Student(RegularClass) - ∏Student(ExtraClass)

6.  Intersection

The symbol ∩ is the definition of an intersection.

A ∩ B

Defines a relationship consisting of a set of all the tuples in both A and B. A and B must be union-compatible, however.

7. Cartesian product

This is used to merge information into one from two separate relationships(tables) and to fetch information from the merged relationship.

Syntax: A X B

For example, if we want to find the morning Regular Class and Extra Class data, then we can use the following operation:

σtime = 'morning' (RegularClass X ExtraClass)

Both RegularClass and ExtraClass should have the attribute time for the above query to operate.

8.Rename

Rename is a unified operation that is used to rename relationship attributes.

ρ (a/b)R renames the 'b' component of the partnership to 'a'.

Syntax: ρ(RelationNew, RelationOld)

Q4) What do you mean by tuple and domain  calculus?

A4) Tuple Relational Calculus(TRC)

●      To select the tuples in a relationship, the tuple relational calculus is defined. The filtering variable in TRC uses relationship tuples.

●      You may have one or more tuples as a consequence of the partnership.

Notation: {T | P (T)}   or {T | Condition (T)}

Where,

T - resulting tuple

P (T) - condition used to fetch T.

Example 1:

{ T.name | Author(T) AND T.article = 'database' } 

Output :

Select tuples from the AUTHOR relationship in this question. It returns a 'name' tuple from the author who wrote a 'database' post.

TRC (tuple relational calculus) can be quantified. We may use existential (∃) and universal quantifiers (∀) in TRC.

Example 2:

{ R| ∃T ∈ Authors(T.article='database' AND R.name=T.name)} 

Output : This question is going to generate the same result as the previous one.

Domain Relational Calculus

●      Domain relational calculus is known as the second type of relationship. The filtering variable uses the domain attributes in the domain relational calculus.

●      The same operators as the tuple calculus are used in domain relational calculus. It utilises logical relations ∧ (and), ∨ (or) and ┓ (not).

●      To connect the variable, it uses Existential (∃) and Universal Quantifiers (∀).

Notation:

{ a1, a2, a3, ..., an | P (a1, a2, a3, ... ,an)}

Where,

a1,a2 - attributes

P - formula built by inner attributes

Example

{< article, page, subject > |  ∈ CSE ∧ subject = 'database'}

Output : This query will result from the relational CSE, where the topic is a database, to the post, page, and subject.

Q5) Write short notes on sql?

A5) SQL

Structured Query Language (SQL) is a database query language which is used to store, retrieve and modify data in the relational database. SQL comes in different versions and forms. All of these versions are based upon the ANSI SQL.

Currently, MySQL is the most common programme for database management systems used for relational database management. It is an open-source, Oracle Company - supported database programme. In comparison to Microsoft SQL Server and Oracle Database, the database management system is fast, scalable, and easy to use. It is widely used for building efficient and interactive server-side or web-based business applications in combination with PHP scripts.

Q6) Write the characteristics and advantages of sql?

A6) Characteristics of SQL

●      It is quick to learn SQL.

●      For accessing data from relational database management systems, SQL is used.

●      SQL will perform database queries against it.

●      To describe data, SQL is used.

●      SQL is used in the database to describe the data and manipulate it when appropriate.

●      To build and drop the database and the table, SQL is used.

●      SQL is used in a database to construct a view, a stored procedure, a function.

●      SQL allows users to set tables, procedures, and display permissions.

Advantages of SQL

●      SQL is easy to learn and use.

●      SQL is a non-procedural language. It means we have to specify only what to retrieve and not the procedure for retrieving data.

●      SQL can be embedded within other languages using SQL modules, pre-compilers.

●      Allows creating procedure, functions, and views using PL-SQL.

●      Allows users to drop databases, tables, views, procedure and cursors.

●      Allows users to set access permissions on tables, views and procedures.

Q7) What are the datatypes of sql ?

A7) SQL Data Types

In order to describe the values that a column can hold, SQL Datatype is used.

Each column is needed in the database table to have a name and data type.

Fig 10: sql data types

1. Binary data types

Three kinds of binary data types are given below:

2.     Numeric data types

The subtypes are given below:

3.     Extract numeric data types

The subtypes are given below:

4.     Character String data types

5.     Date and Time  data types

Q8) What are the types of sql commands?

A8) SQL commands

The Structured Query Language (SQL), as we all know, is a database language that allows one to perform certain operations on existing databases as well as build new databases. To complete the tasks, SQL employs commands such as Create, Drop, and Insert.

These SQL commands are primarily divided into four groups:

DDL:

DDL stands for Data Definition Language, and it is a part of SQL that allows a database user to build and restructure database objects, such as tables.

The following are some of the most basic DDL commands that will be addressed in the coming hours:

CREATE TABLE

ALTER TABLE

DROP TABLE

CREATE INDEX

ALTER INDEX

DROP INDEX

CREATE VIEW

DROP VIEW

DML:

DML, or Data Manipulation Language, is a part of SQL that allows you to manipulate data within relational database objects.

DML commands are divided into three categories:

INSERT

UPDATE

DELETE

DQL:

Data Query Language (DQL) is the most focused subject of SQL for modern relational database users, despite having only one instruction.

The following is the base command:

SELECT

A query is a request for information from a database. A query to the database is normally sent to it through an application interface or a command line prompt.

DCL:

In SQL, you can use data control commands to limit who has access to data in the database. These DCL commands are typically used to build objects related to user access and to manage privilege distribution among users.

The following are some data access commands:

ALTER PASSWORD

GRANT

REVOKE

CREATE SYNONYM

TCL:

Only DML commands like INSERT, DELETE, and UPDATE can be used with TCL commands. Since these operations are automatically committed to the database, they can't be used to create or drop tables.

Here are some examples of TCL commands:

COMMIT Saves database transactions

ROLLBACK Undoes database transactions

SAVEPOINT Creates points within groups of transactions in which to ROLLBACK

SET TRANSACTION Places a name on a transaction

Q9) Describe sql operator?

A9) SQL operator

An operator is a reserved word or character that is used in the WHERE clause of a SQL statement to perform operations like comparisons and arithmetic operations. These Operators are used to define conditions in SQL statements and to function as conjunctions for multiple conditions in a single statement.

●       Arithmetic operators

●       Comparison operators

●       Logical operators

●       Operators used to negate conditions

Arithmetic operator

Assume that 'variable a' and 'variable b' exist. In this case, 'a' has 20 and 'b' has 10.

Comparison operator:

Assume that 'variable a' and 'variable b' exist. In this case, 'a' has 20 and 'b' has 10.

Logical operator:

Assume that 'variable a' and 'variable b' exist. In this case, 'a' has 20 and 'b' has 10.

Q10) What are sql tables?

A10) SQL Tables

A SQL Table is a list of data that has been arranged into rows and columns. The table is referred to as a relation, and the row is referred to as a tuple in relational databases.

A table is a basic data storage format. A table may also be thought of as a simple way to portray relationships.

Example: Employee table

The table name is "Employee," and the column names are "EMP NAME," "ADDRESS," and "SALARY." A row is formed by the combination of data from multiple columns, such as "Ankit," "Lucknow," and 15000.

Operation on table:

1. Create table
2. Drop table
3. Delete table
4. Rename table

Create Table:

To create a table in the database, use SQL create table. To define the table, you must first define the table's name, as well as the table's columns and data types.

Syntax:

Create table "table_name"   

("column1" "data type",   

"column2" "data type",   

"column3" "data type",   

...   

"columnN" "data type");

Drop Table:

A SQL drop table is used to remove a table's description as well as all of its results. When this command is run, all of the data in the table is permanently lost, so be cautious when using it.

Syntax :

DROP TABLE "table_name";

Delete Table:

To delete rows from a table in SQL, use the DELETE expression. To delete a particular row from a table, we can use the WHERE condition. You don't need to use the WHERE clause if you want to remove all the records from the table.

Syntax:

DELETE FROM table_name WHERE condition; 

Q11) Write short notes on views and indexes?

A11) Views and indexes

In SQL, a view is a virtual table based on the SQL statement result-set.

A view, much like a real table, includes rows and columns. Fields in a database view are fields from one or more individual database tables.

You can add SQL, WHERE, and JOIN statements to a view and show the details as if the data came from a single table.

Create a view

Syntax

CREATE VIEW view_name AS

SELECT column1, column2, ...

FROM table_name

WHERE condition;

Up-to-date data still displays a view! Any time a user queries a view, the database engine recreates the data, using the view's SQL statement.

Create view example

A view showing all customers from India is provided by the following SQL.

CREATE VIEW [India Customers] AS

SELECT CustomerName, ContactName

FROM Customers

WHERE Country = 'India';

Updating a view

With the Build OR REPLACE VIEW command, the view can be changed.

Create or replace view syntax

CREATE OR REPLACE VIEW view_name AS

SELECT column1, column2, ...

FROM table_name

WHERE condition;

For the "City" view, the following SQL adds the "India Customers" column:

CREATE OR REPLACE VIEW [India Customers] AS

SELECT CustomerName, ContactName, City

FROM Customers

WHERE Country = 'India';

Dropping a view

With the DROP VIEW instruction, a view is removed.

Drop view syntax

DROP VIEW view_name;

"The following SQL drops the view of "India Customers":

DROP VIEW [India Customers];

Indexes

●      Special lookup tables are indexes. It is used to very easily extract data from the database.

●      To speed up select queries and where clauses are used, an Index is used. But it displays the data input with statements for insertion and update. Without affecting the data, indexes can be generated or dropped.

●      An index is much like an index on the back of a book in a database.

For example, when you refer to all the pages in a book that addresses a certain subject, you must first refer to an index that lists all the subjects alphabetically, and then refer to one or more particular page numbers.

●      Create index statement

It is used on a table to construct an index. It allows value to be duplicated

Syntax

CREATE INDEX index_name 

ON table_name (column1, column2, ...);

Example

CREATE INDEX idx_name 

ON Persons (LastName, FirstName); 

●      Unique index statement

It is used on a table to construct a specific index. Duplicate value does not make it.

Syntax

CREATE UNIQUE INDEX index_name 

ON table_name (column1, column2, ...); 

Example

CREATE UNIQUE INDEX websites_idx 

ON websites (site_name); 

●      Drop index statement

It is used to delete a table's index.

Syntax

DROP INDEX index_name; 

Example

DROP INDEX websites_idx;

Q12) Describe sql subqueries?

A12) SQL Subqueries

A MySQL subquery is a query nested within another query such as select, insert, update or delete. In addition, a MySQL subquery can be nested inside another subquery.

A MySQL subquery is called an inner query while the query that contains the subquery is called an outer query. A subquery can be used anywhere that expression is used and must be closed in parentheses.

A subquery is also known as a nested query. It is a SELECT query embedded within the WHERE or HAVING clause of another SQL query. The data returned by the subquery is used by the outer statement in the same way a literal value would be used.

Subqueries provide an easy and efficient way to handle the queries that depend on the results from another query. They are almost identical to the normal SELECT statements, but there are few restrictions.

The most important ones are listed below:

●      A subquery must always appear within parentheses.

●      A subquery must return only one column. This means you cannot use SELECT * in a subquery unless the table you are referring to has only one column. You may use a subquery that returns multiple columns, if the purpose is row comparison.

●      You can only use subqueries that return more than one row with multiple value operators, such as the IN or NOT IN operator.

●      A subquery cannot be a UNION. Only a single SELECT statement is allowed.

Subqueries are most frequently used with the SELECT statement, however you can use them within a INSERT, UPDATE, or DELETE statement as well, or inside another subquery.

●      Subqueries with the SELECT Statement

The following statement will return the details of only those customers whose order value in the orders table is more than 5000 dollar. Also note that we’ve used the

Keyword DISTINCT in our subquery to eliminate the duplicate cust_id values from the

Result set.

1. SELECT * FROM customers WHERE cust_id IN (SELECT DISTINCT cust_id

FROM orders WHERE order_value &gt; 5000);

●      Subqueries with the INSERT Statement

Subqueries can also be used with INSERT statements. Here’s an example:

INSERT INTO premium_customers

SELECT * FROM customers

WHERE cust_id IN (SELECT DISTINCT cust_id FROM orders

WHERE order_value > 5000);

The above statement will insert the records of premium customers into a table

Called premium_customers, by using the data returned from subquery. Here the premium customers are the customers who have placed orders worth more than 5000 dollar.

●      Subqueries with the UPDATE Statement

You can also use the subqueries in conjunction with the UPDATE statement to update the single or multiple columns in a table, as follow:

UPDATE orders

SET order_value = order_value + 10

WHERE cust_id IN (SELECT cust_id FROM customers

WHERE postal_code = 75016);

The above statement will update the order value in the orders table for those customers who live in the area whose postal code is 75016, by increasing the current order value by 10 dollar.

Q13) Define aggregate functions?

A13) Aggregate functions

The data that you need is not always stored in the tables. However, you can get it by

Performing the calculations of the stored data when you select it.

Suppose we have table:

For example, you cannot get the total amount of each order by simply querying from

The order details table because the order details table stores only the quantity and price of each item. You have to select the quantity and price of an item for each order and calculate the order’s total.

To perform such calculations in a query, you use aggregate functions.

By definition, an aggregate function performs a calculation on a set of values and returns a single value.

MySQL provides aggregate functions like: AVG, MIN, MAX, SUM, COUNT.

Suppose we have employees table as shown below:

Mysql> select * from employees;

+------+--------+--------+

| e_id | e_name | salary |

+------+--------+--------+

| 1001 | Sam | 10000 |

| 1002 | Jerry | 11000 |

| 1003 | King | 25000 |

| 1004 | Harry | 50000 |

| 1005 | Tom | 45000 |

| 1006 | Johnny | 75000 |

| 1007 | Andrew | 5000 |

+------+--------+--------+

7 rows in set (0.00 sec)

1. AVERAGE:

Mysql> Select avg(salary) as “Average Salary” from employees;

+--------------------+

| Average Salary |

+--------------------+

| 31571.428571428572 |

+--------------------+

Mysql> Select min(salary) as “Minimum Salary” from employees;

+----------------+

| Minimum Salary |

+----------------+

| 5000 |

+----------------+

Mysql> Select max(salary) as “Maximum Salary” from employees;

+----------------+

| Maximum Salary |

+----------------+

| 75000 |

+----------------+

Mysql>Select count(*) as “Total” from employees;

+-------+

| Total |

+-------+

| 7 |

+-------+

SUM:

Mysql> Select sum(salary) as “Total Salary” from employees;

+--------------+

| Total Salary |

+--------------+

| 221000 |

+--------------+

Mysql> select e_id,salary*0.5 as “Half Salary” from employees;

+------+-------------+

| e_id | Half Salary |

+------+-------------+

| 1001 | 5000 |

| 1002 | 5500 |

| 1003 | 12500 |

| 1004 | 25000 |

| 1005 | 22500 |

| 1006 | 37500 |

| 1007 | 2500 |

+------+-------------+

Q14) Write the insert, update and delete operations?

A14) Insert

The syntax for the insertion of a new record into a table is quite simple:

INSERT INTO table_name (field1, field2, ...)

VALUES (value1, value2, ...);

Where

●      field1 and field2 are fields from table_name.

●      Values 1 and 2 are the values for fields 1 and 2, respectively. SQL allows you the flexibility to list the fields in the order you want, as long as the corresponding values are defined accordingly. The following code is thus identical to the question above:

INSERT INTO table_name (field2, field1, ...)

VALUES (value2, value1, ...);

A variant of INSERT makes it possible to insert many comma-separated records at once, as follows:

INSERT INTO table_name (field1, field2, ...)

VALUES (value3, value4, ...),

(value5, value6, ...),

(value7, value8, ...);

Update

To change the data that is already in the database, the SQL UPDATE statement is used. In the WHERE clause, the condition determines which row is to be changed.

Syntax

UPDATE table_name 

SET column1 = value1, column2 = value2, ... 

WHERE condition; 

Updating single record

Adjust the EMP NAME column and set the value in the row where SALARY is 500000 to 'Emma'.

Syntax

UPDATE table_name   

SET column_name = value  

WHERE condition;

Query

UPDATE EMPLOYEE  

SET EMP_NAME = 'Emma'  

WHERE SALARY = 500000; 

Updating multiple record

You can divide each field allocated by a comma if you want to update several columns. Adjust the EMP NAME column to 'Kevin' in the EMPLOYEE table, and CITY to 'Boston' where the EMP ID is 5.

Syntax

UPDATE table_name   

SET column_name = value1, column_name2 = value2   

WHERE condition; 

Query

UPDATE EMPLOYEE  

SET EMP_NAME = 'Kevin', City = 'Boston'  

WHERE EMP_ID = 5; 

Delete

To delete rows from a table, the SQL DELETE statement is used. The DELETE statement usually removes one or more records from a table.

Syntax

DELETE FROM table_name WHERE some_condition; 

Deleting single record

Remove the EMPLOYEE table row, where EMP NAME = 'Kristen'. Just the fourth row will be omitted here.

Syntax

DELETE FROM EMPLOYEE  

WHERE EMP_NAME = 'Kristen'; 

Deleting multiple record

Remove the row where AGE is 30 from the EMPLOYEE table. Two rows will be deleted by this (first and third row).

Syntax

DELETE FROM EMPLOYEE WHERE AGE= 30;

Q15) Explain joins?

A15) Joins

JOIN, as the name implies, is a word that means "to combine." In SQL, JOIN means "to join two or more tables together."

The JOIN clause in SQL is used to join records from two or more database tables.

Broadly, there are four types of methods for joining:

Inner Join

INNER JOIN selects records in both tables that have matching values as long as the condition is met in SQL. It returns a set of all rows from both tables that satisfy the condition.

Syntax:

SELECT table1.column1, table1.column2, table2.column1,.... 

FROM table1  

INNER JOIN table2 

ON table1.matching_column = table2.matching_column;

Left Join

The SQL left join returns all of the values from the left table as well as the values from the right table that match. It will return NULL if there is no matching join value.

Syntax:

SELECT table1.column1, table1.column2, table2.column1,.... 

FROM table1  

LEFT JOIN table2 

ON table1.matching_column = table2.matching_column; 

Right Join

RIGHT JOIN returns all the values from the rows of the right table as well as the matched values from the left table in SQL. It will return NULL if there is no match in both tables.

Syntax:

SELECT table1.column1, table1.column2, table2.column1,.... 

FROM table1  

RIGHT JOIN table2 

ON table1.matching_column = table2.matching_column; 

Full Join

A FULL JOIN in SQL is the product of combining left and right outer joins. The records from both tables are combined in the join tables. It places a NULL in the position of any matches that were not identified.

Syntax:

SELECT table1.column1, table1.column2, table2.column1,.... 

FROM table1  

FULL JOIN table2 

ON table1.matching_column = table2.matching_column;

Q16) Describe union , intersection and minus?

A16) Union

To combine the effects of two or more SELECT statements, use UNION. It can, however, remove redundant rows from the resultset. In the case of a union, the number of columns and datatype in both tables on which the UNION operation is performed must be the same.

Fig 11: unions

Example:

First table

Second table

Query will be : SELECT * FROM First

UNION

SELECT * FROM Second;

Intersection

The intersect procedure is used to join two SELECT statements together, but it only returns the records that are similar to both. The number of columns and datatype must be the same when using Intersect.

Fig 12: intersection

Consider the above two tables.

Query will be: SELECT * FROM First

INTERSECT

SELECT * FROM Second;

Minus

The Minus operation combines the results of two SELECT statements and returns only those in the final outcome, which is part of the first package.

Fig 13: minus

Consider the above two tables,

Query will be : SELECT * FROM First

MINUS

SELECT * FROM Second;

Q17) What do you mean by triggers?

A17) Triggers

A trigger is a stored database procedure which is automatically invoked if a special event occurs in the database. For instance, when a row is inserted into a specified table or when certain table columns are being modified, a trigger can be invoked.

In fact, triggers are written for execution in response to any of the following events.

●      A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)

●      A database definition (DDL) statement (CREATE, ALTER, or DROP).

●      A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

You may identify triggers for the table, display, schema, or database that the event is associated with.

Benefits of Triggers

●      Generating some derived column values automatically

●      Enforcing referential integrity

●      Event logging and storing information on table access

●      Auditing

●      Synchronous replication of tables

●      Imposing security authorizations

●      Preventing invalid transactions

Syntax

Create trigger [trigger_name]

[before | after] 

{insert | update | delete} 

On [table_name] 

[for each row] 

[trigger_body]

Q18) What do you mean by Procedures in SQL/PL SQL?

A18) Procedures in SQL/PL SQL

A procedure is a PL/SQL block that performs one or more specific tasks. Procedures in other programming languages are analogous to this.

A header and a body appear in the procedure.

Header: The procedure's name and the parameters or variables passed to the procedure are both listed in the header.

Body: Similar to a general PL/SQL block, the body has a declaration, execution, and exception section.

How to pass parameters in procedure:

When you want to create a procedure or function, you have to define parameters. There is three ways to pass parameters in procedure:

IN parameters: The IN parameter can be referenced by the procedure or function. The value of the parameter cannot be overwritten by the procedure or the function.

OUT parameters: The OUT parameter cannot be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.

INOUT parameters: The INOUT parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.

PL/SQL Create Procedure

Syntax:

CREATE [OR REPLACE] PROCEDURE procedure_name 

    [ (parameter [,parameter]) ] 

IS 

    [declaration_section] 

BEGIN 

    executable_section 

[EXCEPTION 

    exception_section] 

END [procedure_name]; 

PL/SQL Drop Procedure

Syntax:

DROP PROCEDURE procedure_name;

Q19) Write short notes on cursors?

A19) Cursors

A cursor is a form of temporary memory or workstation. It is allocated by the database server when a user performs DML operations on a table. Database Tables are stored in cursors.

Implicit and explicit cursors are the two types of cursors. These are clarified in the following paragraphs.

Implicit cursors: Implicit cursors are also known as SQL SERVER's Default Cursors. When a user performs DML operations, SQL SERVER allocates these cursors.

Explicit cursors: When a user needs an explicit cursor, the user creates it. For fetching data from a table in a row-by-row manner, explicit cursors are used.

Q20) What is Literals?

A20) Literals

Literals are notes or the concept of representing/expressing a meaning that does not change. Literals are equivalent to constants in MySQL. When declaring a variable or executing queries, we may use a literal one.

We will explain the various forms of literal statements in this section and how they can be used in MySQL statements.

The following are the literal forms:

Unit – 2

Relational data Model and Language

Q1) Explain relational data model?

A1) Relational Data Model

The modelling of the data description, data semantics, and consistency constraints of the data is the Data Model. It offers conceptual resources at each level of data abstraction to explain the architecture of a database.

Data models are used to describe the design of a database at the logical level.

Some of the data models are: -

1. Entity-Relationship Model

2. Relational Model

3. Hierarchical Model

4. Network Model

5. Object Relational Model

Entity-Relationship(E-R) Model:

●      The logical structure of a database can be expressed by an E-R diagram.

●      Relationships are defined in this database model by dividing the object of interest into an entity and its characteristics into attributes.

●      Using relationships, various entities are related.

●      To make it easier for various stakeholders to understand, E-R models are established to represent relationships in pictorial form.

The Main Components of ER Diagram are:

a) Rectangles - Entity Sets

b) Ellipses - Attributes

c) Diamond - Relation among entity sets

d) Lines - Connects attributes to entity sets and entity sets to relationships.

Fig 1: E - R diagram

The above E-R diagram has two entities: Teacher and Department

Teacher entity has three attributes:

Teacher_id

Teacher_name

Teacher_Subject

Department  entity has two attributes:-

Dept_id

Dept_name

Relation Teaches in gives relationship between  Teacher, Department .

Relationship can be of the type:

1:1→One to one

1:M →One to many

M:1 →Many to one

M:M →Many to many

Relational Model:

●      Relational model uses a collection of tables to represent both data and the relationships among those data.

●      This model is useful for constructing a database that can then be converted into relational model tables.

●      Data is arranged in two-dimensional tables in this model and the relationship is preserved by storing a common field.

●      In the relational model, the basic structure of data is tables. In the rows of that table, all information relating to a specific category is stored.

Therefore, in the relational model, tables are also known as relations.

Sample relational database

1. Teacher Table

B.     Department Table

C.    Teaches in table

Hierarchical Model:

●      This model of the database organises information into a tree-like structure with a single root to which all the other information is connected. The hierarchy begins from the root data and extends to the parent nodes like a tree, adding child nodes.

●      A child node can only have a single parent node in this model.

●      A Hierarchical model uses tree structure to represent relationships among entities.

●      This model represents many real-world relationships effectively, such as a book index, recipes, etc.

●      Data is structured into a tree-like structure in the hierarchical model with a one-to-many relationship between two different data forms.

For instance, one department can have many classes, many professors and many students of course.

Fig 2: Hierarchical model

Network Model:

●      Network model uses two different data structures:

a) A record type is used to represent an entity set.

b) A set type is used to represent a directed relationship between two record types.

●      This is the Hierarchical model's extension. Data is arranged more like a graph in this model, and is allowed to have more than one parent node.

●      Data is more linked in this database model as more relationships are formed in this database model. Also, as the data is more linked, it is also simpler and quicker to access the information. To map many-to-many data relationships, this database model was used.

●      This was the most commonly used database architecture prior to the advent of the Relational Model.

Fig 3: Network model

Object Relational Model:

The object-oriented database derivation is the integrity of object-oriented

Programming language systems and consistent systems. The power of object-oriented databases comes from the cyclical treatment of both consistent data, as found in databases, and transient data, as found in executing programs.

Fig 4: Object - oriented model

Object-oriented databases use small, recyclable separated of software

Called objects. The objects themselves are stored in the object-oriented

Database. Each object contains of two elements:

1. Piece of data (e.g., sound, video, text, or graphics).

2. Instructions, or software programs called methods, for what to do with

The data.

There are two types of ORM:

a. Object-Oriented Data Model (OODM)

b. Object-Relational Data Model (ORDM)

These models are used to handle objects:

1) To provide support for complex objects.

2) To provide user extensibility for data types, operators and access methods.

3) There should be mechanism to support:

a) Abstract data type.

b) Data of type ‘procedure’.

c) Rules.

Q2) Describe integrity constraints?

A2) integrity constraints

Constraints on honesty are a set of rules. It is used to maintain information quality.

Integrity constraints ensure that it is important to perform data insertion, updating, and other procedures in such a way that data integrity is not compromised.

Therefore, to protect against unintended damage to the database, integrity constraints are used.

Fig 5: Types of integrity constraints

Domain Constraints

●      As a description of a valid set of values for an attribute, domain constraints can be specified.

●      The domain data type consists of a string, character, integer, time, date, currency, etc. In the corresponding domain, the value of the attribute must be available.

Fig 6: Example of domain constraints

Entity Integrity Constraints

●      The honesty restriction of the organization states that the primary key value should not be zero.

●      This is because the primary key value is used to define the relationship between individual rows, and if the primary key has a null value, then those rows cannot be identified.

●      A table may contain a null value other than the field for the primary key.

Fig 7: Example of entity constraints

Referential Constraints

●      Between two relations or tables, the referential integrity constraints are defined and used to preserve the consistency between the tuples in two relationships.

●      If an attribute of the foreign key of the relationship R1 has the same domain(s) as the primary key of the relationship R2, then the foreign key of R1 is said to refer to or refer to the primary key of the relationship R2.

●      Foreign key values in the R1 relationship tuple can either take the primary key values for a certain R2 relationship tuple, or they can take NULL values, but cannot be zero.

Fig 8: Example of integrity constraints

Key Constraints

●      Keys are the collection of entities used to uniquely define an object within its entity set.

●      A group of entities may have several keys, but the primary key will be one key. A primary key may contain in the relational table a unique and null value.

Fig 9: example of key constraints

Q3) What is relational algebra?

A3) Relational Algebra

Relational Algebra is procedural query language. It takes Relation as input and generates relation as output. Relational algebra mainly provides theoretical foundation for relational databases and SQL.

Basic Operations which can be performed using relational algebra are:

1. Projection

2. Selection

3. Join

4. Union

5. Set Difference

6. Intersection

7. Cartesian product

8. Rename

Consider following relation R (A, B, C)

1. Projection (π):

This operation is used to select particular columns from the relation.

Π(AB) R:

It will select A and B column from the relation R. It produces the output like:

Project operation automatically removes duplicates from the resultant set.

2.     Selection (σ):

This operation is used to select particular tuples from the relation.

σ(C<4) R:

It will select the tuples which have a value of c less than 4.

But select operation is used to only select the required tuples from the relation. To display those tuples on screen, select operation must be combined with project operation.

Π (σ (C<4) R) will produce the result like:

3.     Join 

A Cartesian product followed by a selection criterion is basically a joint process.

Operation of join, denoted by ⋈

The JOIN operation often allows tuples from different relationships to join different tuples

Types of JOIN:

Various forms of join operation are:

Inner Joins:

●      Theta join

●      EQUI join

●      Natural join

Outer join:

●      Left Outer Join

●      Right Outer Join

●      Full Outer Join

4.     Union

Union operation in relational algebra is the same as union operation in set theory, only the restriction is that both relations must have the same set of attributes for a union of two relationships.

Syntax:  table_name1 ∪ table_name2

For instance, if we have two tables with RegularClass and ExtraClass, both have a student column to save the student name, then,

∏Student(RegularClass) ∪ ∏Student(ExtraClass)

The above operation will send us the names of students who attend both normal and extra classes, reducing repetition.

5.     Set difference

Set Difference in relational algebra is the same operation of set difference as in set theory, with the limitation that the same set of attributes can have both relationships.

Syntax: A - B

Where the A and B relationships.

For instance, if we want to find the names of students who attend the regular class, but not the extra class, then we can use the following procedure:

∏Student(RegularClass) - ∏Student(ExtraClass)

6.  Intersection

The symbol ∩ is the definition of an intersection.

A ∩ B

Defines a relationship consisting of a set of all the tuples in both A and B. A and B must be union-compatible, however.

7. Cartesian product

This is used to merge information into one from two separate relationships(tables) and to fetch information from the merged relationship.

Syntax: A X B

For example, if we want to find the morning Regular Class and Extra Class data, then we can use the following operation:

σtime = 'morning' (RegularClass X ExtraClass)

Both RegularClass and ExtraClass should have the attribute time for the above query to operate.

8.Rename

Rename is a unified operation that is used to rename relationship attributes.

ρ (a/b)R renames the 'b' component of the partnership to 'a'.

Syntax: ρ(RelationNew, RelationOld)

Q4) What do you mean by tuple and domain  calculus?

A4) Tuple Relational Calculus(TRC)

●      To select the tuples in a relationship, the tuple relational calculus is defined. The filtering variable in TRC uses relationship tuples.

●      You may have one or more tuples as a consequence of the partnership.

Notation: {T | P (T)}   or {T | Condition (T)}

Where,

T - resulting tuple

P (T) - condition used to fetch T.

Example 1:

{ T.name | Author(T) AND T.article = 'database' } 

Output :

Select tuples from the AUTHOR relationship in this question. It returns a 'name' tuple from the author who wrote a 'database' post.

TRC (tuple relational calculus) can be quantified. We may use existential (∃) and universal quantifiers (∀) in TRC.

Example 2:

{ R| ∃T ∈ Authors(T.article='database' AND R.name=T.name)} 

Output : This question is going to generate the same result as the previous one.

Domain Relational Calculus

●      Domain relational calculus is known as the second type of relationship. The filtering variable uses the domain attributes in the domain relational calculus.

●      The same operators as the tuple calculus are used in domain relational calculus. It utilises logical relations ∧ (and), ∨ (or) and ┓ (not).

●      To connect the variable, it uses Existential (∃) and Universal Quantifiers (∀).

Notation:

{ a1, a2, a3, ..., an | P (a1, a2, a3, ... ,an)}

Where,

a1,a2 - attributes

P - formula built by inner attributes

Example

{< article, page, subject > |  ∈ CSE ∧ subject = 'database'}

Output : This query will result from the relational CSE, where the topic is a database, to the post, page, and subject.

Q5) Write short notes on sql?

A5) SQL

Structured Query Language (SQL) is a database query language which is used to store, retrieve and modify data in the relational database. SQL comes in different versions and forms. All of these versions are based upon the ANSI SQL.

Currently, MySQL is the most common programme for database management systems used for relational database management. It is an open-source, Oracle Company - supported database programme. In comparison to Microsoft SQL Server and Oracle Database, the database management system is fast, scalable, and easy to use. It is widely used for building efficient and interactive server-side or web-based business applications in combination with PHP scripts.

Q6) Write the characteristics and advantages of sql?

A6) Characteristics of SQL

●      It is quick to learn SQL.

●      For accessing data from relational database management systems, SQL is used.

●      SQL will perform database queries against it.

●      To describe data, SQL is used.

●      SQL is used in the database to describe the data and manipulate it when appropriate.

●      To build and drop the database and the table, SQL is used.

●      SQL is used in a database to construct a view, a stored procedure, a function.

●      SQL allows users to set tables, procedures, and display permissions.

Advantages of SQL

●      SQL is easy to learn and use.

●      SQL is a non-procedural language. It means we have to specify only what to retrieve and not the procedure for retrieving data.

●      SQL can be embedded within other languages using SQL modules, pre-compilers.

●      Allows creating procedure, functions, and views using PL-SQL.

●      Allows users to drop databases, tables, views, procedure and cursors.

●      Allows users to set access permissions on tables, views and procedures.

Q7) What are the datatypes of sql ?

A7) SQL Data Types

In order to describe the values that a column can hold, SQL Datatype is used.

Each column is needed in the database table to have a name and data type.

Fig 10: sql data types

1. Binary data types

Three kinds of binary data types are given below:

2.     Numeric data types

The subtypes are given below:

3.     Extract numeric data types

The subtypes are given below:

4.     Character String data types

5.     Date and Time  data types

Q8) What are the types of sql commands?

A8) SQL commands

The Structured Query Language (SQL), as we all know, is a database language that allows one to perform certain operations on existing databases as well as build new databases. To complete the tasks, SQL employs commands such as Create, Drop, and Insert.

These SQL commands are primarily divided into four groups:

DDL:

DDL stands for Data Definition Language, and it is a part of SQL that allows a database user to build and restructure database objects, such as tables.

The following are some of the most basic DDL commands that will be addressed in the coming hours:

CREATE TABLE

ALTER TABLE

DROP TABLE

CREATE INDEX

ALTER INDEX

DROP INDEX

CREATE VIEW

DROP VIEW

DML:

DML, or Data Manipulation Language, is a part of SQL that allows you to manipulate data within relational database objects.

DML commands are divided into three categories:

INSERT

UPDATE

DELETE

DQL:

Data Query Language (DQL) is the most focused subject of SQL for modern relational database users, despite having only one instruction.

The following is the base command:

SELECT

A query is a request for information from a database. A query to the database is normally sent to it through an application interface or a command line prompt.

DCL:

In SQL, you can use data control commands to limit who has access to data in the database. These DCL commands are typically used to build objects related to user access and to manage privilege distribution among users.

The following are some data access commands:

ALTER PASSWORD

GRANT

REVOKE

CREATE SYNONYM

TCL:

Only DML commands like INSERT, DELETE, and UPDATE can be used with TCL commands. Since these operations are automatically committed to the database, they can't be used to create or drop tables.

Here are some examples of TCL commands:

COMMIT Saves database transactions

ROLLBACK Undoes database transactions

SAVEPOINT Creates points within groups of transactions in which to ROLLBACK

SET TRANSACTION Places a name on a transaction

Q9) Describe sql operator?

A9) SQL operator

An operator is a reserved word or character that is used in the WHERE clause of a SQL statement to perform operations like comparisons and arithmetic operations. These Operators are used to define conditions in SQL statements and to function as conjunctions for multiple conditions in a single statement.

●       Arithmetic operators

●       Comparison operators

●       Logical operators

●       Operators used to negate conditions

Arithmetic operator

Assume that 'variable a' and 'variable b' exist. In this case, 'a' has 20 and 'b' has 10.

Comparison operator:

Assume that 'variable a' and 'variable b' exist. In this case, 'a' has 20 and 'b' has 10.

Logical operator:

Assume that 'variable a' and 'variable b' exist. In this case, 'a' has 20 and 'b' has 10.

Q10) What are sql tables?

A10) SQL Tables

A SQL Table is a list of data that has been arranged into rows and columns. The table is referred to as a relation, and the row is referred to as a tuple in relational databases.

A table is a basic data storage format. A table may also be thought of as a simple way to portray relationships.

Example: Employee table

The table name is "Employee," and the column names are "EMP NAME," "ADDRESS," and "SALARY." A row is formed by the combination of data from multiple columns, such as "Ankit," "Lucknow," and 15000.

Operation on table:

1. Create table
2. Drop table
3. Delete table
4. Rename table

Create Table:

To create a table in the database, use SQL create table. To define the table, you must first define the table's name, as well as the table's columns and data types.

Syntax:

Create table "table_name"   

("column1" "data type",   

"column2" "data type",   

"column3" "data type",   

...   

"columnN" "data type");

Drop Table:

A SQL drop table is used to remove a table's description as well as all of its results. When this command is run, all of the data in the table is permanently lost, so be cautious when using it.

Syntax :

DROP TABLE "table_name";

Delete Table:

To delete rows from a table in SQL, use the DELETE expression. To delete a particular row from a table, we can use the WHERE condition. You don't need to use the WHERE clause if you want to remove all the records from the table.

Syntax:

DELETE FROM table_name WHERE condition; 

Q11) Write short notes on views and indexes?

A11) Views and indexes

In SQL, a view is a virtual table based on the SQL statement result-set.

A view, much like a real table, includes rows and columns. Fields in a database view are fields from one or more individual database tables.

You can add SQL, WHERE, and JOIN statements to a view and show the details as if the data came from a single table.

Create a view

Syntax

CREATE VIEW view_name AS

SELECT column1, column2, ...

FROM table_name

WHERE condition;

Up-to-date data still displays a view! Any time a user queries a view, the database engine recreates the data, using the view's SQL statement.

Create view example

A view showing all customers from India is provided by the following SQL.

CREATE VIEW [India Customers] AS

SELECT CustomerName, ContactName

FROM Customers

WHERE Country = 'India';

Updating a view

With the Build OR REPLACE VIEW command, the view can be changed.

Create or replace view syntax

CREATE OR REPLACE VIEW view_name AS

SELECT column1, column2, ...

FROM table_name

WHERE condition;

For the "City" view, the following SQL adds the "India Customers" column:

CREATE OR REPLACE VIEW [India Customers] AS

SELECT CustomerName, ContactName, City

FROM Customers

WHERE Country = 'India';

Dropping a view

With the DROP VIEW instruction, a view is removed.

Drop view syntax

DROP VIEW view_name;

"The following SQL drops the view of "India Customers":

DROP VIEW [India Customers];

Indexes

●      Special lookup tables are indexes. It is used to very easily extract data from the database.

●      To speed up select queries and where clauses are used, an Index is used. But it displays the data input with statements for insertion and update. Without affecting the data, indexes can be generated or dropped.

●      An index is much like an index on the back of a book in a database.

For example, when you refer to all the pages in a book that addresses a certain subject, you must first refer to an index that lists all the subjects alphabetically, and then refer to one or more particular page numbers.

●      Create index statement

It is used on a table to construct an index. It allows value to be duplicated

Syntax

CREATE INDEX index_name 

ON table_name (column1, column2, ...);

Example

CREATE INDEX idx_name 

ON Persons (LastName, FirstName); 

●      Unique index statement

It is used on a table to construct a specific index. Duplicate value does not make it.

Syntax

CREATE UNIQUE INDEX index_name 

ON table_name (column1, column2, ...); 

Example

CREATE UNIQUE INDEX websites_idx 

ON websites (site_name); 

●      Drop index statement

It is used to delete a table's index.

Syntax

DROP INDEX index_name; 

Example

DROP INDEX websites_idx;

Q12) Describe sql subqueries?

A12) SQL Subqueries

A MySQL subquery is a query nested within another query such as select, insert, update or delete. In addition, a MySQL subquery can be nested inside another subquery.

A MySQL subquery is called an inner query while the query that contains the subquery is called an outer query. A subquery can be used anywhere that expression is used and must be closed in parentheses.

A subquery is also known as a nested query. It is a SELECT query embedded within the WHERE or HAVING clause of another SQL query. The data returned by the subquery is used by the outer statement in the same way a literal value would be used.

Subqueries provide an easy and efficient way to handle the queries that depend on the results from another query. They are almost identical to the normal SELECT statements, but there are few restrictions.

The most important ones are listed below:

●      A subquery must always appear within parentheses.

●      A subquery must return only one column. This means you cannot use SELECT * in a subquery unless the table you are referring to has only one column. You may use a subquery that returns multiple columns, if the purpose is row comparison.

●      You can only use subqueries that return more than one row with multiple value operators, such as the IN or NOT IN operator.

●      A subquery cannot be a UNION. Only a single SELECT statement is allowed.

Subqueries are most frequently used with the SELECT statement, however you can use them within a INSERT, UPDATE, or DELETE statement as well, or inside another subquery.

●      Subqueries with the SELECT Statement

The following statement will return the details of only those customers whose order value in the orders table is more than 5000 dollar. Also note that we’ve used the

Keyword DISTINCT in our subquery to eliminate the duplicate cust_id values from the

Result set.

1. SELECT * FROM customers WHERE cust_id IN (SELECT DISTINCT cust_id

FROM orders WHERE order_value &gt; 5000);

●      Subqueries with the INSERT Statement

Subqueries can also be used with INSERT statements. Here’s an example:

INSERT INTO premium_customers

SELECT * FROM customers

WHERE cust_id IN (SELECT DISTINCT cust_id FROM orders

WHERE order_value > 5000);

The above statement will insert the records of premium customers into a table

Called premium_customers, by using the data returned from subquery. Here the premium customers are the customers who have placed orders worth more than 5000 dollar.

●      Subqueries with the UPDATE Statement

You can also use the subqueries in conjunction with the UPDATE statement to update the single or multiple columns in a table, as follow:

UPDATE orders

SET order_value = order_value + 10

WHERE cust_id IN (SELECT cust_id FROM customers

WHERE postal_code = 75016);

The above statement will update the order value in the orders table for those customers who live in the area whose postal code is 75016, by increasing the current order value by 10 dollar.

Q13) Define aggregate functions?

A13) Aggregate functions

The data that you need is not always stored in the tables. However, you can get it by

Performing the calculations of the stored data when you select it.

Suppose we have table:

For example, you cannot get the total amount of each order by simply querying from

The order details table because the order details table stores only the quantity and price of each item. You have to select the quantity and price of an item for each order and calculate the order’s total.

To perform such calculations in a query, you use aggregate functions.

By definition, an aggregate function performs a calculation on a set of values and returns a single value.

MySQL provides aggregate functions like: AVG, MIN, MAX, SUM, COUNT.

Suppose we have employees table as shown below:

Mysql> select * from employees;

+------+--------+--------+

| e_id | e_name | salary |

+------+--------+--------+

| 1001 | Sam | 10000 |

| 1002 | Jerry | 11000 |

| 1003 | King | 25000 |

| 1004 | Harry | 50000 |

| 1005 | Tom | 45000 |

| 1006 | Johnny | 75000 |

| 1007 | Andrew | 5000 |

+------+--------+--------+

7 rows in set (0.00 sec)

1. AVERAGE:

Mysql> Select avg(salary) as “Average Salary” from employees;

+--------------------+

| Average Salary |

+--------------------+

| 31571.428571428572 |

+--------------------+

Mysql> Select min(salary) as “Minimum Salary” from employees;

+----------------+

| Minimum Salary |

+----------------+

| 5000 |

+----------------+

Mysql> Select max(salary) as “Maximum Salary” from employees;

+----------------+

| Maximum Salary |

+----------------+

| 75000 |

+----------------+

Mysql>Select count(*) as “Total” from employees;

+-------+

| Total |

+-------+

| 7 |

+-------+

SUM:

Mysql> Select sum(salary) as “Total Salary” from employees;

+--------------+

| Total Salary |

+--------------+

| 221000 |

+--------------+

Mysql> select e_id,salary*0.5 as “Half Salary” from employees;

+------+-------------+

| e_id | Half Salary |

+------+-------------+

| 1001 | 5000 |

| 1002 | 5500 |

| 1003 | 12500 |

| 1004 | 25000 |

| 1005 | 22500 |

| 1006 | 37500 |

| 1007 | 2500 |

+------+-------------+

Q14) Write the insert, update and delete operations?

A14) Insert

The syntax for the insertion of a new record into a table is quite simple:

INSERT INTO table_name (field1, field2, ...)

VALUES (value1, value2, ...);

Where

●      field1 and field2 are fields from table_name.

●      Values 1 and 2 are the values for fields 1 and 2, respectively. SQL allows you the flexibility to list the fields in the order you want, as long as the corresponding values are defined accordingly. The following code is thus identical to the question above:

INSERT INTO table_name (field2, field1, ...)

VALUES (value2, value1, ...);

A variant of INSERT makes it possible to insert many comma-separated records at once, as follows:

INSERT INTO table_name (field1, field2, ...)

VALUES (value3, value4, ...),

(value5, value6, ...),

(value7, value8, ...);

Update

To change the data that is already in the database, the SQL UPDATE statement is used. In the WHERE clause, the condition determines which row is to be changed.

Syntax

UPDATE table_name 

SET column1 = value1, column2 = value2, ... 

WHERE condition; 

Updating single record

Adjust the EMP NAME column and set the value in the row where SALARY is 500000 to 'Emma'.

Syntax

UPDATE table_name   

SET column_name = value  

WHERE condition;

Query

UPDATE EMPLOYEE  

SET EMP_NAME = 'Emma'  

WHERE SALARY = 500000; 

Updating multiple record

You can divide each field allocated by a comma if you want to update several columns. Adjust the EMP NAME column to 'Kevin' in the EMPLOYEE table, and CITY to 'Boston' where the EMP ID is 5.

Syntax

UPDATE table_name   

SET column_name = value1, column_name2 = value2   

WHERE condition; 

Query

UPDATE EMPLOYEE  

SET EMP_NAME = 'Kevin', City = 'Boston'  

WHERE EMP_ID = 5; 

Delete

To delete rows from a table, the SQL DELETE statement is used. The DELETE statement usually removes one or more records from a table.

Syntax

DELETE FROM table_name WHERE some_condition; 

Deleting single record

Remove the EMPLOYEE table row, where EMP NAME = 'Kristen'. Just the fourth row will be omitted here.

Syntax

DELETE FROM EMPLOYEE  

WHERE EMP_NAME = 'Kristen'; 

Deleting multiple record

Remove the row where AGE is 30 from the EMPLOYEE table. Two rows will be deleted by this (first and third row).

Syntax

DELETE FROM EMPLOYEE WHERE AGE= 30;

Q15) Explain joins?

A15) Joins

JOIN, as the name implies, is a word that means "to combine." In SQL, JOIN means "to join two or more tables together."

The JOIN clause in SQL is used to join records from two or more database tables.

Broadly, there are four types of methods for joining:

Inner Join

INNER JOIN selects records in both tables that have matching values as long as the condition is met in SQL. It returns a set of all rows from both tables that satisfy the condition.

Syntax:

SELECT table1.column1, table1.column2, table2.column1,.... 

FROM table1  

INNER JOIN table2 

ON table1.matching_column = table2.matching_column;

Left Join

The SQL left join returns all of the values from the left table as well as the values from the right table that match. It will return NULL if there is no matching join value.

Syntax:

SELECT table1.column1, table1.column2, table2.column1,.... 

FROM table1  

LEFT JOIN table2 

ON table1.matching_column = table2.matching_column; 

Right Join

RIGHT JOIN returns all the values from the rows of the right table as well as the matched values from the left table in SQL. It will return NULL if there is no match in both tables.

Syntax:

SELECT table1.column1, table1.column2, table2.column1,.... 

FROM table1  

RIGHT JOIN table2 

ON table1.matching_column = table2.matching_column; 

Full Join

A FULL JOIN in SQL is the product of combining left and right outer joins. The records from both tables are combined in the join tables. It places a NULL in the position of any matches that were not identified.

Syntax:

SELECT table1.column1, table1.column2, table2.column1,.... 

FROM table1  

FULL JOIN table2 

ON table1.matching_column = table2.matching_column;

Q16) Describe union , intersection and minus?

A16) Union

To combine the effects of two or more SELECT statements, use UNION. It can, however, remove redundant rows from the resultset. In the case of a union, the number of columns and datatype in both tables on which the UNION operation is performed must be the same.

Fig 11: unions

Example:

First table

Second table

Query will be : SELECT * FROM First

UNION

SELECT * FROM Second;

Intersection

The intersect procedure is used to join two SELECT statements together, but it only returns the records that are similar to both. The number of columns and datatype must be the same when using Intersect.

Fig 12: intersection

Consider the above two tables.

Query will be: SELECT * FROM First

INTERSECT

SELECT * FROM Second;

Minus

The Minus operation combines the results of two SELECT statements and returns only those in the final outcome, which is part of the first package.

Fig 13: minus

Consider the above two tables,

Query will be : SELECT * FROM First

MINUS

SELECT * FROM Second;

Q17) What do you mean by triggers?

A17) Triggers

A trigger is a stored database procedure which is automatically invoked if a special event occurs in the database. For instance, when a row is inserted into a specified table or when certain table columns are being modified, a trigger can be invoked.

In fact, triggers are written for execution in response to any of the following events.

●      A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)

●      A database definition (DDL) statement (CREATE, ALTER, or DROP).

●      A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

You may identify triggers for the table, display, schema, or database that the event is associated with.

Benefits of Triggers

●      Generating some derived column values automatically

●      Enforcing referential integrity

●      Event logging and storing information on table access

●      Auditing

●      Synchronous replication of tables

●      Imposing security authorizations

●      Preventing invalid transactions

Syntax

Create trigger [trigger_name]

[before | after] 

{insert | update | delete} 

On [table_name] 

[for each row] 

[trigger_body]

Q18) What do you mean by Procedures in SQL/PL SQL?

A18) Procedures in SQL/PL SQL

A procedure is a PL/SQL block that performs one or more specific tasks. Procedures in other programming languages are analogous to this.

A header and a body appear in the procedure.

Header: The procedure's name and the parameters or variables passed to the procedure are both listed in the header.

Body: Similar to a general PL/SQL block, the body has a declaration, execution, and exception section.

How to pass parameters in procedure:

When you want to create a procedure or function, you have to define parameters. There is three ways to pass parameters in procedure:

IN parameters: The IN parameter can be referenced by the procedure or function. The value of the parameter cannot be overwritten by the procedure or the function.

OUT parameters: The OUT parameter cannot be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.

INOUT parameters: The INOUT parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.

PL/SQL Create Procedure

Syntax:

CREATE [OR REPLACE] PROCEDURE procedure_name 

    [ (parameter [,parameter]) ] 

IS 

    [declaration_section] 

BEGIN 

    executable_section 

[EXCEPTION 

    exception_section] 

END [procedure_name]; 

PL/SQL Drop Procedure

Syntax:

DROP PROCEDURE procedure_name;

Q19) Write short notes on cursors?

A19) Cursors

A cursor is a form of temporary memory or workstation. It is allocated by the database server when a user performs DML operations on a table. Database Tables are stored in cursors.

Implicit and explicit cursors are the two types of cursors. These are clarified in the following paragraphs.

Implicit cursors: Implicit cursors are also known as SQL SERVER's Default Cursors. When a user performs DML operations, SQL SERVER allocates these cursors.

Explicit cursors: When a user needs an explicit cursor, the user creates it. For fetching data from a table in a row-by-row manner, explicit cursors are used.

Q20) What is Literals?

A20) Literals

Literals are notes or the concept of representing/expressing a meaning that does not change. Literals are equivalent to constants in MySQL. When declaring a variable or executing queries, we may use a literal one.

We will explain the various forms of literal statements in this section and how they can be used in MySQL statements.

The following are the literal forms:

Unit – 2

Relational data Model and Language

Q1) Explain relational data model?

A1) Relational Data Model

The modelling of the data description, data semantics, and consistency constraints of the data is the Data Model. It offers conceptual resources at each level of data abstraction to explain the architecture of a database.

Data models are used to describe the design of a database at the logical level.

Some of the data models are: -

1. Entity-Relationship Model

2. Relational Model

3. Hierarchical Model

4. Network Model

5. Object Relational Model

Entity-Relationship(E-R) Model:

●      The logical structure of a database can be expressed by an E-R diagram.

●      Relationships are defined in this database model by dividing the object of interest into an entity and its characteristics into attributes.

●      Using relationships, various entities are related.

●      To make it easier for various stakeholders to understand, E-R models are established to represent relationships in pictorial form.

The Main Components of ER Diagram are:

a) Rectangles - Entity Sets

b) Ellipses - Attributes

c) Diamond - Relation among entity sets

d) Lines - Connects attributes to entity sets and entity sets to relationships.

Fig 1: E - R diagram

The above E-R diagram has two entities: Teacher and Department

Teacher entity has three attributes:

Teacher_id

Teacher_name

Teacher_Subject

Department  entity has two attributes:-

Dept_id

Dept_name

Relation Teaches in gives relationship between  Teacher, Department .

Relationship can be of the type:

1:1→One to one

1:M →One to many

M:1 →Many to one

M:M →Many to many

Relational Model:

●      Relational model uses a collection of tables to represent both data and the relationships among those data.

●      This model is useful for constructing a database that can then be converted into relational model tables.

●      Data is arranged in two-dimensional tables in this model and the relationship is preserved by storing a common field.

●      In the relational model, the basic structure of data is tables. In the rows of that table, all information relating to a specific category is stored.

Therefore, in the relational model, tables are also known as relations.

Sample relational database

1. Teacher Table

B.     Department Table

C.    Teaches in table

Hierarchical Model:

●      This model of the database organises information into a tree-like structure with a single root to which all the other information is connected. The hierarchy begins from the root data and extends to the parent nodes like a tree, adding child nodes.

●      A child node can only have a single parent node in this model.

●      A Hierarchical model uses tree structure to represent relationships among entities.

●      This model represents many real-world relationships effectively, such as a book index, recipes, etc.

●      Data is structured into a tree-like structure in the hierarchical model with a one-to-many relationship between two different data forms.

For instance, one department can have many classes, many professors and many students of course.

Fig 2: Hierarchical model

Network Model:

●      Network model uses two different data structures:

a) A record type is used to represent an entity set.

b) A set type is used to represent a directed relationship between two record types.

●      This is the Hierarchical model's extension. Data is arranged more like a graph in this model, and is allowed to have more than one parent node.

●      Data is more linked in this database model as more relationships are formed in this database model. Also, as the data is more linked, it is also simpler and quicker to access the information. To map many-to-many data relationships, this database model was used.

●      This was the most commonly used database architecture prior to the advent of the Relational Model.

Fig 3: Network model

Object Relational Model:

The object-oriented database derivation is the integrity of object-oriented

Programming language systems and consistent systems. The power of object-oriented databases comes from the cyclical treatment of both consistent data, as found in databases, and transient data, as found in executing programs.

Fig 4: Object - oriented model

Object-oriented databases use small, recyclable separated of software

Called objects. The objects themselves are stored in the object-oriented

Database. Each object contains of two elements:

1. Piece of data (e.g., sound, video, text, or graphics).

2. Instructions, or software programs called methods, for what to do with

The data.

There are two types of ORM:

a. Object-Oriented Data Model (OODM)

b. Object-Relational Data Model (ORDM)

These models are used to handle objects:

1) To provide support for complex objects.

2) To provide user extensibility for data types, operators and access methods.

3) There should be mechanism to support:

a) Abstract data type.

b) Data of type ‘procedure’.

c) Rules.

Q2) Describe integrity constraints?

A2) integrity constraints

Constraints on honesty are a set of rules. It is used to maintain information quality.

Integrity constraints ensure that it is important to perform data insertion, updating, and other procedures in such a way that data integrity is not compromised.

Therefore, to protect against unintended damage to the database, integrity constraints are used.

Fig 5: Types of integrity constraints

Domain Constraints

●      As a description of a valid set of values for an attribute, domain constraints can be specified.

●      The domain data type consists of a string, character, integer, time, date, currency, etc. In the corresponding domain, the value of the attribute must be available.

Fig 6: Example of domain constraints

Entity Integrity Constraints

●      The honesty restriction of the organization states that the primary key value should not be zero.

●      This is because the primary key value is used to define the relationship between individual rows, and if the primary key has a null value, then those rows cannot be identified.

●      A table may contain a null value other than the field for the primary key.

Fig 7: Example of entity constraints

Referential Constraints

●      Between two relations or tables, the referential integrity constraints are defined and used to preserve the consistency between the tuples in two relationships.

●      If an attribute of the foreign key of the relationship R1 has the same domain(s) as the primary key of the relationship R2, then the foreign key of R1 is said to refer to or refer to the primary key of the relationship R2.

●      Foreign key values in the R1 relationship tuple can either take the primary key values for a certain R2 relationship tuple, or they can take NULL values, but cannot be zero.

Fig 8: Example of integrity constraints

Key Constraints

●      Keys are the collection of entities used to uniquely define an object within its entity set.

●      A group of entities may have several keys, but the primary key will be one key. A primary key may contain in the relational table a unique and null value.

Fig 9: example of key constraints

Q3) What is relational algebra?

A3) Relational Algebra

Relational Algebra is procedural query language. It takes Relation as input and generates relation as output. Relational algebra mainly provides theoretical foundation for relational databases and SQL.

Basic Operations which can be performed using relational algebra are:

1. Projection

2. Selection

3. Join

4. Union

5. Set Difference

6. Intersection

7. Cartesian product

8. Rename

Consider following relation R (A, B, C)

1. Projection (π):

This operation is used to select particular columns from the relation.

Π(AB) R:

It will select A and B column from the relation R. It produces the output like:

Project operation automatically removes duplicates from the resultant set.

2.     Selection (σ):

This operation is used to select particular tuples from the relation.

σ(C<4) R:

It will select the tuples which have a value of c less than 4.

But select operation is used to only select the required tuples from the relation. To display those tuples on screen, select operation must be combined with project operation.

Π (σ (C<4) R) will produce the result like:

3.     Join 

A Cartesian product followed by a selection criterion is basically a joint process.

Operation of join, denoted by ⋈

The JOIN operation often allows tuples from different relationships to join different tuples

Types of JOIN:

Various forms of join operation are:

Inner Joins:

●      Theta join

●      EQUI join

●      Natural join

Outer join:

●      Left Outer Join

●      Right Outer Join

●      Full Outer Join

4.     Union

Union operation in relational algebra is the same as union operation in set theory, only the restriction is that both relations must have the same set of attributes for a union of two relationships.

Syntax:  table_name1 ∪ table_name2

For instance, if we have two tables with RegularClass and ExtraClass, both have a student column to save the student name, then,

∏Student(RegularClass) ∪ ∏Student(ExtraClass)

The above operation will send us the names of students who attend both normal and extra classes, reducing repetition.

5.     Set difference

Set Difference in relational algebra is the same operation of set difference as in set theory, with the limitation that the same set of attributes can have both relationships.

Syntax: A - B

Where the A and B relationships.

For instance, if we want to find the names of students who attend the regular class, but not the extra class, then we can use the following procedure:

∏Student(RegularClass) - ∏Student(ExtraClass)

6.  Intersection

The symbol ∩ is the definition of an intersection.

A ∩ B

Defines a relationship consisting of a set of all the tuples in both A and B. A and B must be union-compatible, however.

7. Cartesian product

This is used to merge information into one from two separate relationships(tables) and to fetch information from the merged relationship.

Syntax: A X B

For example, if we want to find the morning Regular Class and Extra Class data, then we can use the following operation:

σtime = 'morning' (RegularClass X ExtraClass)

Both RegularClass and ExtraClass should have the attribute time for the above query to operate.

8.Rename

Rename is a unified operation that is used to rename relationship attributes.

ρ (a/b)R renames the 'b' component of the partnership to 'a'.

Syntax: ρ(RelationNew, RelationOld)

Q4) What do you mean by tuple and domain  calculus?

A4) Tuple Relational Calculus(TRC)

●      To select the tuples in a relationship, the tuple relational calculus is defined. The filtering variable in TRC uses relationship tuples.

●      You may have one or more tuples as a consequence of the partnership.

Notation: {T | P (T)}   or {T | Condition (T)}

Where,

T - resulting tuple

P (T) - condition used to fetch T.

Example 1:

{ T.name | Author(T) AND T.article = 'database' } 

Output :

Select tuples from the AUTHOR relationship in this question. It returns a 'name' tuple from the author who wrote a 'database' post.

TRC (tuple relational calculus) can be quantified. We may use existential (∃) and universal quantifiers (∀) in TRC.

Example 2:

{ R| ∃T ∈ Authors(T.article='database' AND R.name=T.name)} 

Output : This question is going to generate the same result as the previous one.

Domain Relational Calculus

●      Domain relational calculus is known as the second type of relationship. The filtering variable uses the domain attributes in the domain relational calculus.

●      The same operators as the tuple calculus are used in domain relational calculus. It utilises logical relations ∧ (and), ∨ (or) and ┓ (not).

●      To connect the variable, it uses Existential (∃) and Universal Quantifiers (∀).

Notation:

{ a1, a2, a3, ..., an | P (a1, a2, a3, ... ,an)}

Where,

a1,a2 - attributes

P - formula built by inner attributes

Example

{< article, page, subject > |  ∈ CSE ∧ subject = 'database'}

Output : This query will result from the relational CSE, where the topic is a database, to the post, page, and subject.

Q5) Write short notes on sql?

A5) SQL

Structured Query Language (SQL) is a database query language which is used to store, retrieve and modify data in the relational database. SQL comes in different versions and forms. All of these versions are based upon the ANSI SQL.

Currently, MySQL is the most common programme for database management systems used for relational database management. It is an open-source, Oracle Company - supported database programme. In comparison to Microsoft SQL Server and Oracle Database, the database management system is fast, scalable, and easy to use. It is widely used for building efficient and interactive server-side or web-based business applications in combination with PHP scripts.

Q6) Write the characteristics and advantages of sql?

A6) Characteristics of SQL

●      It is quick to learn SQL.

●      For accessing data from relational database management systems, SQL is used.

●      SQL will perform database queries against it.

●      To describe data, SQL is used.

●      SQL is used in the database to describe the data and manipulate it when appropriate.

●      To build and drop the database and the table, SQL is used.

●      SQL is used in a database to construct a view, a stored procedure, a function.

●      SQL allows users to set tables, procedures, and display permissions.

Advantages of SQL

●      SQL is easy to learn and use.

●      SQL is a non-procedural language. It means we have to specify only what to retrieve and not the procedure for retrieving data.

●      SQL can be embedded within other languages using SQL modules, pre-compilers.

●      Allows creating procedure, functions, and views using PL-SQL.

●      Allows users to drop databases, tables, views, procedure and cursors.

●      Allows users to set access permissions on tables, views and procedures.

Q7) What are the datatypes of sql ?

A7) SQL Data Types

In order to describe the values that a column can hold, SQL Datatype is used.

Each column is needed in the database table to have a name and data type.

Fig 10: sql data types

1. Binary data types

Three kinds of binary data types are given below:

2.     Numeric data types

The subtypes are given below:

3.     Extract numeric data types

The subtypes are given below:

4.     Character String data types

5.     Date and Time  data types

Q8) What are the types of sql commands?

A8) SQL commands

The Structured Query Language (SQL), as we all know, is a database language that allows one to perform certain operations on existing databases as well as build new databases. To complete the tasks, SQL employs commands such as Create, Drop, and Insert.

These SQL commands are primarily divided into four groups:

DDL:

DDL stands for Data Definition Language, and it is a part of SQL that allows a database user to build and restructure database objects, such as tables.

The following are some of the most basic DDL commands that will be addressed in the coming hours:

CREATE TABLE

ALTER TABLE

DROP TABLE

CREATE INDEX

ALTER INDEX

DROP INDEX

CREATE VIEW

DROP VIEW

DML:

DML, or Data Manipulation Language, is a part of SQL that allows you to manipulate data within relational database objects.

DML commands are divided into three categories:

INSERT

UPDATE

DELETE

DQL:

Data Query Language (DQL) is the most focused subject of SQL for modern relational database users, despite having only one instruction.

The following is the base command:

SELECT

A query is a request for information from a database. A query to the database is normally sent to it through an application interface or a command line prompt.

DCL:

In SQL, you can use data control commands to limit who has access to data in the database. These DCL commands are typically used to build objects related to user access and to manage privilege distribution among users.

The following are some data access commands:

ALTER PASSWORD

GRANT

REVOKE

CREATE SYNONYM

TCL:

Only DML commands like INSERT, DELETE, and UPDATE can be used with TCL commands. Since these operations are automatically committed to the database, they can't be used to create or drop tables.

Here are some examples of TCL commands:

COMMIT Saves database transactions

ROLLBACK Undoes database transactions

SAVEPOINT Creates points within groups of transactions in which to ROLLBACK

SET TRANSACTION Places a name on a transaction

Q9) Describe sql operator?

A9) SQL operator

An operator is a reserved word or character that is used in the WHERE clause of a SQL statement to perform operations like comparisons and arithmetic operations. These Operators are used to define conditions in SQL statements and to function as conjunctions for multiple conditions in a single statement.

●       Arithmetic operators

●       Comparison operators

●       Logical operators

●       Operators used to negate conditions

Arithmetic operator

Assume that 'variable a' and 'variable b' exist. In this case, 'a' has 20 and 'b' has 10.

Comparison operator:

Assume that 'variable a' and 'variable b' exist. In this case, 'a' has 20 and 'b' has 10.