FDBS Unit - 1,2,3
FDBS Unit - 1,2,3
FDBS Unit - 1,2,3
INTRODUCTION
Overview:
A Database is a collection of related data organised in a way that data can be easily accessed,
managed and updated. Any piece of information can be a data, for example name of your school.
Database is actually a place where related piece of information is stored and various operations
can be performed on it.
What is Data?
Data is a collection of a distinct small unit of information. It can be used in a variety of forms like
text, numbers, media, bytes, etc. it can be stored in pieces of paper or electronic memory, etc.
Word ‘Data’ is originated from the word 'datum' that means 'single piece of information.' It is
plural of the word datum.
In computing, Data is information that can be translated into a form for efficient movement and
processing. Data is interchangeable.
A DBMS is software that allows creation, definition and manipulation of database, allowing users
to store, process and analyse data easily. DBMS provides us with an interface or a tool, to
perform various operations like creating database, storing data in it, updating data, creating
tables in the database and a lot more.
DBMS also provides protection and security to the databases. It also maintains data consistency
in case of multiple users.
Here are some examples of popular DBMS used these days:
MySql
Oracle
SQL Server
IBM DB2
PostgreSQL
Amazon SimpleDB (cloud based) etc.
Data Definition: It is used for creation, modification, and removal of definition that
defines the organization of data in the database.
Data Updation: It is used for the insertion, modification, and deletion of the actual data
in the database.
Data Retrieval: It is used to retrieve the data from the database which can be used by
applications for various purposes.
User Administration: It is used for registering and monitoring users, maintain data
integrity, enforcing data security, dealing with concurrency control, monitoring
performance and recovering information corrupted by unexpected failure.
Characteristics of DBMS:
It uses a digital repository established on a server to store and manage the information.
It can provide a clear and logical view of the process that manipulates data.
DBMS contains automatic backup and recovery procedures.
It contains ACID properties which maintain data in a healthy state in case of failure.
It can reduce the complex relationship between data.
It is used to support manipulation and processing of data.
It is used to provide security of data.
It can view the database from different viewpoints according to the requirements of the
user.
Advantages of DBMS:
Controls database redundancy: It can control data redundancy because it stores all the
data in one single database file and that recorded data is placed in the database.
Data sharing: In DBMS, the authorized users of an organization can share the data among
multiple users.
Easily Maintenance: It can be easily maintainable due to the centralized nature of the
database system.
Reduce time: It reduces development time and maintenance need.
Backup: It provides backup and recovery subsystems which create automatic backup of
data from hardware and software failures and restores the data if required.
multiple user interface: It provides different types of user interfaces like graphical user
interfaces, application program interfaces
Database Applications – DBMS:
Telecom: There is a database to keeps track of the information regarding calls made,
network usage, customer details etc. Without the database systems it is hard to maintain
that huge amount of data that keeps updating every millisecond.
Industry: Where it is a manufacturing unit, warehouse or distribution centre, each one
needs a database to keep the records of ins and outs. For example distribution centre
should keep a track of the product units that supplied into the centre as well as the
products that got delivered out from the distribution centre on each day; this is where
DBMS comes into picture.
Banking System: For storing customer info, tracking day to day credit and debit
transactions, generating bank statements etc. All this work has been done with the help of
Database management systems.
Sales: To store customer information, production information and invoice details.
Airlines: To travel though airlines, we make early reservations; this reservation
information along with flight schedule is stored in database.
Education sector: Database systems are frequently used in schools and colleges to store
and retrieve the data regarding student details, staff details, course details, exam details,
payroll data, attendance details, fees details etc. There is a hell lot amount of inter-related
data that needs to be stored and retrieved in an efficient manner.
Online shopping: You must be aware of the online shopping websites such as Amazon,
Flipkart etc. These sites store the product information, your addresses and preferences,
credit details and provide you the relevant list of products based on your query. All this
involves a Database management system.
Data redundancy: Data redundancy refers to the duplication of data, lets say we are
managing the data of a college where a student is enrolled for two courses, the same
student details in such case will be stored twice, which will take more storage than
needed. Data redundancy often leads to higher storage costs and poor access time.
Data inconsistency: Data redundancy leads to data inconsistency, let’s take the same
example that we have taken above, a student is enrolled for two courses and we have
student address stored twice, now let’s say student requests to change his address, if the
address is changed at one place and not on all the records then this can lead to data
inconsistency.
Data Isolation: Because data are scattered in various files, and files may be in different
formats, writing new application programs to retrieve the appropriate data is difficult.
Dependency on application programs: Changing files would lead to change in application
programs.
Atomicity issues: Atomicity of a transaction refers to “All or nothing”, which means either
all the operations in a transaction executes or none.
Data Security: Data should be secured from unauthorised access, for example a
student in a college should not be able to see the payroll details of the teachers, such
kind of security constraints are difficult to apply in file processing systems.
Advantage of DBMS over file system
Types of Databases:
Centralized Database
It is the type of database that stores data at a centralized database system. It comforts the users
to access the stored data from different locations through several applications. These
applications contain the authentication process to let user’s access data securely. An example of
a Centralized database can be Central Library that carries a central database of each library in a
college/university.
Advantages of Centralized Database
o It has decreased the risk of data management, i.e., manipulation of data will not affect
the core data.
o Data consistency is maintained as it manages data in a central repository.
o It provides better data quality, which enables organizations to establish data standards.
o It is less costly because fewer vendors are required to handle the data sets.
Disadvantages of Centralized Database
o The size of the centralized database is large, which increases the response time for
fetching the data.
o It is not easy to update such an extensive database system.
o If any server failure occurs, entire data will be lost, which could be a huge loss.
Distributed Database
Unlike a centralized database system, in distributed systems, data is distributed among different
database systems of an organization. These database systems are connected via communication
links. Such links help the end-users to access the data easily. Examples of the Distributed
database are Apache Cassandra, HBase, Ignite, etc.
We can further divide a distributed database system into:
o Homogeneous DDB: Those database systems which execute on the same operating
system and use the same application process and carry the same hardware devices.
o Heterogeneous DDB: Those database systems which execute on different operating
systems under different application procedures, and carries different hardware devices.
Advantages of Distributed Database
o Modular development is possible in a distributed database, i.e., the system can be
expanded by including new computers and connecting them to the distributed system.
o One server failure will not affect the entire data set.
Relational Database
This database is based on the relational data model, which stores data in the form of rows(tuple)
and columns(attributes), and together forms a table(relation). A relational database uses SQL for
storing, manipulating, as well as maintaining the data. E.F. Codd invented the database in 1970.
Each table in the database carries a key that makes the data unique from others. Examples of
Relational databases are MySQL, Microsoft SQL Server, Oracle, etc.
Properties of Relational Database
There are following four commonly known properties of a relational model known as ACID
properties, where:
A means Atomicity: This ensures the data operation will complete either with success or with
failure. It follows the 'all or nothing' strategy. For example, a transaction will either be committed
or will abort.
C means Consistency: If we perform any operation over the data, its value before and after the
operation should be preserved. For example, the account balances before and after the
transaction should be correct, i.e., it should remain conserved.
I mean Isolation: There can be concurrent users for accessing data at the same time from the
database. Thus, isolation between the data should remain isolated. For example, when multiple
transactions occur at the same time, one transaction effects should not be visible to the other
transactions in the database.
D means Durability: It ensures that once it completes the operation and commits the data, data
changes should remain permanent.
NoSQL Database
Non-SQL/Not Only SQL is a type of database that is used for storing a wide range of data sets. It
is not a relational database as it stores data not only in tabular form but in several different ways.
It came into existence when the demand for building modern applications increased. Thus,
NoSQL presented a wide variety of database technologies in response to the demands. We can
further divide a NoSQL database into the following four types:
Key-value storage: It is the simplest type of database storage where it stores every single item as
a key (or attribute name) holding its value, together.
Document-oriented Database: A type of database used to store data as JSON-like document. It
helps developers in storing data by using the same document-model format as used in the
application code.
Graph Databases: It is used for storing vast amounts of data in a graph-like structure. Most
commonly, social networking websites use the graph database.
Wide-column stores: It is similar to the data represented in relational databases. Here, data is
stored in large columns together, instead of storing in rows.
Cloud Database
A type of database where data is stored in a virtual environment and executes over the cloud
computing platform. It provides users with various cloud computing services (SaaS, PaaS, IaaS,
etc.) for accessing the database. There are numerous cloud platforms, but the best options are:
Amazon Web Services(AWS)
Microsoft Azure
Kamatera
PhonixNAP
ScienceSoft
Google Cloud SQL, etc.
Object-oriented Databases
The type of database that uses the object-based data model approach for storing data in the
database system. The data is represented and stored as objects which are similar to the objects
used in the object-oriented programming language.
Hierarchical Databases
It is the type of database that stores data in the form of parent-children relationship nodes. Here,
it organizes data in a tree-like structure.
Data get stored in the form of records that are connected via links. Each child record in the tree
will contain only one parent. On the other hand, each parent record can have multiple child
records.
Network Databases
It is the database that typically follows the network data model. Here, the representation of data
is in the form of nodes connected via links between them. Unlike the hierarchical database, it
allows each record to have multiple children and parent nodes to form a generalized graph
structure.
Structure of Database Management System:
DML Compiler –
It processes the DML statements into low level instruction (machine language), so that
they can be executed.
DDL Interpreter –
It processes the DDL statements into a set of table containing Meta data (data about
data).
Storage Manager –
Storage Manager is a program that provides an interface between the data stored in the
database and the queries received. It is also known as Database Control System. It maintains the
consistency and integrity of the database by applying the constraints and executes
the DCL statements. It is responsible for updating, storing, deleting, and retrieving data in the
database. It contains the following components
Authorization Manager:
It ensures role-based access control, i.e,. checks whether the particular person is
privileged to perform the requested operation or not.
Integrity Manager:
It checks the integrity constraints when the database is modified.
Transaction Manager:
It controls concurrent access by performing the operations in a scheduled way that it
receives the transaction. Thus, it ensures that the database remains in the
consistent state before and after the execution of transaction.
File Manager:
It manages the file space and the data structure used to represent information in the
database.
Buffer Manager:
It is responsible for cache memory and the transfer of data between the secondary
storage and main memory.
Disk Storage: It contains the following components:
Data Files –
It stores the data.
Data Dictionary –
It contains the information about the structure of any database object. It is the repository
of information that governs the metadata.
Indices –
It provides faster retrieval of data item.
In this type of architecture, the database is readily available on the client machine; any request
made by client doesn’t require a network connection to perform the action on the database.
For example, let’s say you want to fetch the records of employee from the database and the
database is available on your computer system, so the request to fetch employee details will be
done by your computer and the records will be fetched from the database by your computer as
well. This type of system is generally referred as local database system.
External Level
Conceptual Level
Conceptual level describes the structure of the whole database for a group of users.
It is also called as the data model.
Conceptual schema is a representation of the entire content of the database.
This schema contains all the information to build relevant external records.
It hides the internal details of physical storage.
External Level
External level is related to the data which is viewed by individual end users.
This level includes a no. of user views or external schemas.
This level is closest to the user.
External view describes the segment of the database that is required for a particular
user group and hides the rest of the database from that user group.
Data Models
Data Model is the modelling of the data description, data semantics, and consistency constraints
of the data. It provides the conceptual tools for describing the design of a database at each level
of data abstraction. Therefore, there are following four data models used for understanding the
structure of the database:
1) Relational Data Model: This type of model designs the data in the form of rows and columns
within a table. Thus, a relational model uses tables for representing data and in-between
relationships. Tables are also called relations. This model was initially described by Edgar F. Codd,
in 1969. The relational data model is the widely used model which is primarily used by
commercial data processing applications.
2) Entity-Relationship Data Model: An ER model is the logical representation of data as objects
and relationships among them. These objects are known as entities, and relationship is an
association among these entities. This model was designed by Peter Chen and published in 1976
papers. It was widely used in database designing. A set of attributes describe the entities. For
example, student_name, student_id describes the 'student' entity. A set of the same type of
entities is known as an 'Entity set', and the set of the same type of relationships is known as
'relationship set'.
4) Semi structured Data Model: This type of data model is different from the other three data
models (explained above). The semi structured data model allows the data specifications at
places where the individual data items of the same type may have different attributes sets. The
Extensible Markup Language, also known as XML, is widely used for representing the semi
structured data. Although XML was initially designed for including the mark up information to
the text document, it gains importance because of its application in the exchange of data.
DBMS (Database Management System) acts as an interface between the user and the database.
The user requests the DBMS to perform various operations such as insert, delete, update and
retrieval on the database. The components of DBMS perform these requested operations on the
database and provide necessary data to the users.
The components of DBMS can be divided into two parts: Functions and Services
DDL Compiler:
The DML commands such as insert, update, delete, retrieve from the
application program are sent to the DML compiler for compilation into object
code for database access.
The object code is then optimized in the best way to execute a query by the
query optimizer and then send to the data manager.
Data Manager:
The Data Manager is the central software component of the DBMS also knows as
Database Control System.
The Main Functions Of Data Manager Are:
1 Data Dictionary:
The DML complier converts the high level Queries into low level file access
commands known as compiled DML.
4 End Users:
1. The second class of users then is end user, who interacts with system from
online workstation or terminals.
2. Use the interface provided as an integral part of the database system software.
3. User can request, in form of query, to access database either directly by
using particular language, such as SQL, or by using some pre-developed
application interface.
4. Such request are sent to query evaluation engine via DML pre-compiler and DML
compiler
5. The query evaluation engine accepts the query and analyses it.
6. It finds the suitable way to execute the compiled SQL statements of the query.
7. Finally, the compiled SQL statements are executed to perform the specified
operation
8. Query Processor Units:
Interprets DDL statements into a set of tables containing metadata.
Translates DML statements into low level instructions that the
query evaluation engine understands.
Converts DML statements embedded in an application program into
procedure calls in the host language.
Executes low level instructions generated by DML compiler.
DDL Interpreter
DML Compiler
Embedded DML Pre-compiler
Query Evaluation Engine
1. Authorization Manager
2. Integrity Manager
3. Transaction Manager
4. File manager
5. Buffer Manager
5. Functions of DBMS:
a. DBMS free the programmers from the need to worry about the organization
and location of the data i.e. it shields the users from complex hardware level
details.
b. DBMS can organize process and present data elements from the database. This
capability enables decision makers to search and query database contents in order
to extract answers that are not available in regular Reports.
c. Programming is speeded up because programmer can concentrate on logic of
the application.
d. It includes special user friendly query languages which are easy to understand by
non programming users of the system.
DDL (Data Definition Language) : DDL or Data Definition Language actually consists of the
SQL commands that can be used to define the database schema. It simply deals with
descriptions of the database schema and is used to create and modify the structure of
database objects in the database.
CREATE – is used to create the database or its objects (like table, index, function, views,
store procedure and triggers).
Syntax: 1
Syntax: 2
Example 2:
CREATE TABLE Students (ROLL_NO int(3), NAME varchar(20), SUBJECT varchar(20),);
DROP – is used to delete objects from the database.
DROP is used to delete a whole database or just a table. The DROP statement destroys
the objects like an existing database, table, index, or view.
A DROP statement in SQL removes a component from a relational database management
system (RDBMS).
Syntax :
Examples:
DROP TABLE table_name;
table_name: Name of the table to be deleted.
ALTER TABLE is used to add, delete/drop or modify columns in the existing table. It is also
used to add and drop various constraints on the existing table.
ADD is used to add columns into the existing table. Sometimes we may require to add
additional information, in that case we do not require to create the whole database
again, ADD comes to our rescue.
DROP COLUMN is used to drop column in a table. Deleting the unwanted columns from the
table.
Syntax: ALTER TABLE table_name
DROP COLUMN column_name;
Example :
ROLL_NO NAME
1 Ram
2 Abhi
3 Rahul
4 Tanu
QUERY:
To ADD 2 columns AGE and COURSE to table Student.
ALTER TABLE Student ADD (AGE number (3), COURSE varchar (40));
OUTPUT:
ROLL_NO NAME AGE COURSE
1 Ram
2 Abhi
3 Rahul
4 Tanu
OUTPUT:
ROLL_NO NAME AGE
1 Ram
2 Abhi
3 Rahul
4 Tanu
TRUNCATE–is used to remove all records from a table, including all spaces allocated
for the records are removed.
TRUNCATE statement is a Data Definition Language (DDL) operation that is used to mark
the extents of a table for deallocation (empty for reuse). The result of this operation
quickly removes all data from a table, typically bypassing a number of integrity enforcing
mechanisms. The TRUNCATE TABLE mytable statement is logically (though not physically)
equivalent to the DELETE FROM mytable statement (without a WHERE clause).
Syntax :
Truncate is normally ultra-fast and its ideal for deleting data from a temporary table.
Truncate preserves the structure of the table for future use, unlike drop table where the
table is deleted with its full structure.
Table or Database deletion using DROP statement cannot be rolled back, so it must be
used wisely.
After running the above query Student_details table will be truncated, i.e, the data will be
deleted but the structure will remain in the memory for further operations.
QUERY:
DQL statements are used for performing queries on the data within schema objects. The purpose
of the DQL Command is to get some schema relation based on the query passed to it.
Example of DQL:
Sample Table:
This query will return all the rows in the table with fields’ column1, column2.
Query to fetch the fields ROLL_NO, NAME, AGE from the table Student:
Output:
DML (Data Manipulation Language): The SQL commands that deals with the
manipulation of data present in the database belong to DML or Data Manipulation
Language and this includes most of the SQL statements.
Examples of DML:
The INSERT INTO statement of SQL is used to insert a new row in a table. There are two
ways of using INSERT INTO statement for inserting rows:
1. Only values: First method is to specify only the value of data to be inserted without
the column names.
2. Column names and values both: In the second method we will specify both the
columns which we want to fill and their corresponding values as shown below:
Output:
The table Student will now look like:
ROLL_NO NAME ADDRESS PHONE Age
1 Ram Delhi XXXXXXXXXX 18
2 RAMESH GURGAON XXXXXXXXXX 18
3 SUJIT ROHTAK XXXXXXXXXX 20
4 SURESH Delhi XXXXXXXXXX 18
3 SUJIT ROHTAK XXXXXXXXXX 20
2 RAMESH GURGAON XXXXXXXXXX 18
5 HARSH WEST BENGAL XXXXXXXXXX 19
Output:
The table Student will now look like:
Notice that the columns for which the values are not provided are filled by null. Which is the
default values for those columns.
Inserting all columns of a table: We can copy all the data of a table and insert into in
a different table.
We have used the SELECT statement to copy the data from one table and INSERT INTO
statement to insert in a different table.
Inserting specific columns of a table: We can copy only those columns of a table which
we want to insert into in a different table.
Syntax:
We have used the SELECT statement to copy the data of the selected columns only from
the second table and INSERT INTO statement to insert in first table.
Copying specific rows from a table: We can copy specific rows from a table to insert into
another table by using WHERE clause with the SELECT statement. We have to provide
appropriate condition in the WHERE clause to select specific rows.
Output:
This query will insert all the data of the table LateralStudent in the table Student.
The table Student will now look like,
Output:
This query will insert the data in the columns ROLL_NO, NAME and Age of the table
LateralStudent in the table Student and the remaining columns in the Student table will
be filled by null which is the default value of the remaining columns. The table Student
will now look like,
INSERT INTO Student SELECT * FROM Lateral Student WHERE Age = 18;
Output:
This query will select only the first row from table Lateral Student to insert into the table
Student. The table Student will now look like,
ROLL_NO NAME ADDRESS PHONE Age
1 Ram Delhi XXXXXXXXXX 18
2 RAMESH GURGAON XXXXXXXXXX 18
3 SUJIT ROHTAK XXXXXXXXXX 20
4 SURESH Delhi XXXXXXXXXX 18
3 SUJIT ROHTAK XXXXXXXXXX 20
2 RAMESH GURGAON XXXXXXXXXX 18
7 SOUVIK DUMDUM XXXXXXXXXX 18
You need to provide multiple lists of values where each list is separated by ", ". Every list of value
corresponds to values to be inserted in each new row of the table.
Values in the next list tell values to be inserted in the next Row of the table.
Example:
Input :
INSERT INTO STUDENT (ID, NAME, AGE, GRADE, CITY) VALUES
(1,"AMIT KUMAR",15,10,"DELHI"),
(2,"GAURI RAO",18,12,"BANGALORE"),
(3,"MANAV BHATT",17,11,"NEW DELHI")
(4,"RIYA KAPOOR",10,5,"UDAIPUR");
The UPDATE statement in SQL is used to update the data of an existing table in database.
We can update single columns as well as multiple columns using UPDATE statement as
per our requirement.
Example Queries : Updating single column: Update the column NAME and set the value to
‘PRATIK’ in all the rows where Age is 20.
20; Output:
This query will update two rows(third row and fifth row) and the table Student will now
look like,
Output:
The above query will update two columns in the first row and the table Student will now
look like,
Note: For updating multiple columns we have used comma (,) to separate the names and
values of two columns.
Omitting WHERE clause: If we omit the WHERE clause from the update query then all of
the rows will get updated.
Output:
The table Student will now look like,
The DELETE Statement in SQL is used to delete existing records from a table. We can
delete a single record or multiple records depending on the condition we specify in the
WHERE clause.
Syntax:
DELETE FROM table_name WHERE some_condition;
Note: We can delete single as well as multiple records depending on the condition we
provide in WHERE clause. If we omit the WHERE clause then all of the records will be
deleted and the table will be empty.
Sample Table:
Example Queries:
Deleting single record: Delete the rows where NAME = ‘Ram’. This will delete only
the first row.
DELETE FROM Student WHERE NAME = 'Ram';
Output:
The above query will delete only the first row and the table Student will now look like,
Deleting multiple records: Delete the rows from the table Student where Age is 20. This
will delete 2 rows (third row and fifth row).
Delete all of the records: There are two queries to do this as shown below,
Output:
All of the records in the table will be deleted, there are no records left to display. The
table Student will become empty!
1.16.4. DCL (Data Control Language): DCL includes commands such as GRANT and REVOKE which
mainly deal with the rights, permissions and other controls of the database system.
TCL (transaction Control Language): TCL commands deal with the transaction within the
database.
A distributed database is basically a database that is not limited to one system; it is spread over
different sites, i.e, on multiple computers or over a network of computers. A distributed
database system is located on various sites that don’t share physical components. This may be
required when a particular database needs to be accessed by various users globally. It needs to
be managed such that for the users it looks like one single database.
Types:
1. Homogeneous Database:
In a homogeneous database, all different sites store database identically. The operating system,
database management system and the data structures used – all are same at all sites. Hence,
they’re easy to manage.
2.HeterogeneousDatabase:
In a heterogeneous distributed database, different sites can use different schema and software
that can lead to problems in query processing and transactions. Also, a particular site might be
completely unaware of the other sites. Different computers may use a different operating
system, different database application. They may even use different data models for the
database. Hence, translations are required for different sites to communicate.
1. Replication :
In this approach, the entire relation is stored redundantly at 2 or more sites. If the entire
database is available at all sites, it is a fully redundant database. Hence, in replication, systems
maintain copies of data.
This is advantageous as it increases the availability of data at different sites. Also, now query
requests can be processed in parallel.
However, it has certain disadvantages as well. Data needs to be constantly updated. Any change
made at one site needs to be recorded at every site that relation is stored or else it may lead to
inconsistency. This is a lot of overhead. Also, concurrency control becomes way more complex as
concurrent access now needs to be checked over a number of sites.
2. Fragmentation:
In this approach, the relations are fragmented (i.e., they’re divided into smaller parts) and each
of the fragments is stored in different sites where they’re required. It must be made sure that
the fragments are such that they can be used to reconstruct the original relation (i.e, there isn’t
any loss of data). Fragmentation is advantageous as it doesn’t create copies of data, consistency
is not a problem.
What is RDBMS
A relational database is a type of database that stores and provides access to data points that are
related to one another. Relational databases are based on the relational model, an intuitive,
straightforward way of representing data in tables. In a relational database, each row in the table
is a record with a unique ID called the key. The columns of the table hold attributes of the data,
and each record usually has a value for each attribute, making it easy to establish the
relationships among data points.
How relational databases are structured
The relational model means that the logical data structures—the data tables, views, and indexes
—are separate from the physical storage structures. This separation means that database
administrators can manage physical data storage without affecting access to that data as a logical
structure. For example, renaming a database file does not rename the tables stored within it.
The distinction between logical and physical also applies to database operations, which are
clearly defined actions that enable applications to manipulate the data and structures of the
database. Logical operations allow an application to specify the content it needs, and physical
operations determine how that data should be accessed and then carries out the task.
To ensure that data is always accurate and accessible, relational databases follow certain
integrity rules. For example, an integrity rule can specify that duplicate rows are not allowed in a
table in order to eliminate the potential for erroneous information entering the database.
What is table
The RDBMS database uses tables to store data. A table is a collection of related data entries and
contains rows and columns to store data.
A table is the simplest example of data storage in RDBMS.
Let's see the example of student table.
What is field
Field is a smaller entity of the table which contains specific information about every record in the
table. In the above example, the field in the student table consist of id, name, age, course.
What is column?
A column is a vertical entity in the table which contains all information associated with a specific
field in a table. For example: "name" is a column in the above table which contains all
information about student's name.
Name
Anusha
Madhu
Samskruthi
Ashwini
Sragavi
NULL Values
The NULL value of the table specifies that the field has been left blank during record creation. It
is totally different from the value filled with zero or a field that contains space.
Data Integrity
There are the following categories of data integrity exist with each RDBMS:
Domain integrity: It enforces valid entries for a given column by restricting the type, the format,
or the range of values.
Referential integrity: It specifies that rows cannot be deleted, which are used by other records.
DBMS Vs RDBMS
DBMS RDBMS
RDBMS applications store data in a tabular
DBMS applications store data as file.
form.
In RDBMS, the tables have an identifier called
In DBMS, data is generally stored in either a
primary key and the data values are stored in
hierarchical form or a navigational form.
the form of tables.
Normalization is not present in DBMS. Normalization is present in RDBMS.
RDBMS defines the integrity constraint for the
DBMS does not apply any security with
purpose of ACID (Atomocity, Consistency,
regards to data manipulation.
Isolation and Durability) property.
in RDBMS, data values are stored in the form of
DBMS uses file system to store data, so there tables, so a relationship between these data
will be no relation between the tables. values will be stored in the form of a table as
well.
RDBMS system supports a tabular structure of
DBMS has to provide some uniform methods
the data and a relationship between them to
to access the stored information.
access the stored information.
DBMS does not support distributed database. RDBMS supports distributed database.
DBMS is meant to be for small organization RDBMS is designed to handle large amount of
and deal with small data. it supports single data. it supports multiple users.
user.
Examples of DBMS are file systems xml etc.Example of RDBMS are mysql
UNIT –II
DATABASE DESIGN ER MODEL
2 .1 What is ER Diagram?
ER Diagram stands for Entity Relationship Diagram,
also known as ERD is a diagram that displays the
relationship of entity sets stored in a database.
Entity
An entity is an object or component of data. An entity is represented as rectangle in an ER
diagram.
For example: In the following ER diagram we have two entities Student and College and these
two entities have many to one relationship as many students study in a single college. We will
read more about relationships later, for now focus on entities.
1.1 Weak Entity
An entity that cannot be uniquely identified by its own attributes and relies on the relationship
with other entity is called weak entity. The weak entity is represented by a double rectangle. For
example – a bank account cannot be uniquely identified without knowing the bank to which the
account belongs, so bank account is a weak entity.
Attribute
An attribute describes the property of an entity. An attribute is represented as Oval in an ER
diagram. There are four types of attributes:
Key attribute
2.3.2.2 Composite attribute
Multivalued attribute
Derived attribute
1. Key Attribute:
A key attribute can uniquely identify an entity
from an entity set. For example, student roll
number can uniquely identify a student from
a set of students. Key attribute is represented
by oval same as other attributes however the
text of key attribute is underlined.
2. Composite Attribute:
3. Multivalued attribute:
An attribute that can hold multiple values is known as Multivalued attribute. It is represented
with double ovals in an ER Diagram. For example – A person can have more than one phone
numbers so the phone number attribute is Multivalued.
4. Derived attribute:
A derived attribute is one whose value is dynamic and derived from another attribute. It is
represented by dashed oval in an ER Diagram. For example – Person age is a derived attribute as
it changes over time and can be derived from another attribute (Date of birth).
Relationship
A relationship is represented by diamond shape in ER diagram; it shows the relationship among
entities. There are four types of relationships:
1. One to One
2. One to Many
3. Many to One
4. Many too Many
When a single instance of an entity is associated with more than one instances of another entity
then it is called one to many relationship. For example – a customer can place many orders but a
order cannot be placed by many customers.
When more than one instances of an entity is associated with more than one instances of another
entity then it is called many to many relationship. For example, a can be assigned to many projects
and a project can be assigned to many students.
Any defined object in the database which can be used to reference or store data is known as a
database object. Database objects can be made using the create command. These database
objects are used for holding and manipulating the data in the database.
1. View: Subsets of data from more than one table are logically represented.
2. Table: Composed of rows and columns, the table is the primary form of data representation.
3. Sequence: Sequence generates the primary key values.
4. Synonym: It is an alternative name for a specific object.
5. Index: It improves the performance of some particular queries.
Example: CREATE TABLE dept (deptno Number (2) , dname VARCHAR2(14) , loc VARCHAR2 (14) );
2. View :
This object is used in the database to create a type of view of the database. It is a logical view
based on one or more tables or even another view. A view does not contain any data of its own
but is used to view data in other tables. The view is based on a table, and that table is known as
the base table. The data dictionary stores the view as a SELECT statement.
Syntax:
Example:
3. Sequence:
This object in the database is used to create a sequence in the database. It is a user created
database object which can be shared by more than two users to create a unique integer in the
database. The most often use of sequence is to create a primary value in the database. This
primary value is unique for each row. Once the sequence is generated, it is decremented or
incremented using an Oracle routine.
An index provides direct and fast access to rows in a table. Its purpose is to reduce the
necessity of disk I/O by using an indexed path to locate data quickly. The index is used and
maintained automatically by the Oracle server. Once an index is created, no direct activity is
required by the user. Indexes are logically and physically independent of the table they index.
This means that they can be created or dropped at any time and have no effect on the base
tables or other indexes.
Syntax: CREATE INDEX index
ON table (column [, column]...);
Example: CREATE INDEX emp_last_name_idx
ON employees(last_name);
5. Synonym – This database object is used to create a indexes in database.It simplify access to
objects by creating a synonym(another name for an object). With synonyms, you can Ease
referring to a table owned by another user and shorten lengthy object names.To refer to a
table owned by another user, you need to prefix the table name with the name of the user
who created it followed by a period. Creating a synonym eliminates the need to qualify the
object name with the schema and provides you with an alternative name for a table, view,
sequence, procedure, or other objects. This method can be especially useful with lengthy
object names, such as views.
Attributes in DBMS:
An attribute is a property or characteristic of an entity. An entity may contain any number of
attributes. One of the attributes is considered as the primary key. In an Entity-Relation model,
attributes are represented in an elliptical shape.
Example: Student has attributes like name, age, roll number and many more. To uniquely
identify the student, we use the primary key as roll number as it is not repeated. Attributes can
also be subdivided into another set of attributes.
There are five such types of attributes: Simple, Composite, Single-valued, Multi-valued, and
Derived attribute.
These are explained as following below.
1. Simple attribute: An attribute which cannot be further subdivided into components is a
simple attribute.
Example: The roll number of a student, the id number of an employee.
2. Composite Attribute
An attribute which can be splitted into components is a composite attribute.
Example: The address can be further splitted into house number, street number, city, state,
country and pincode, the name can also be splitted into first name middle name and last name.
4. Multi-Valued Attribute :
The attribute which takes up more than a single value for each entity instance is multi-valued
attribute.
Example: Phone number of a student: Landline and mobile.
5. Dervied Attribute :
An attribute that can be derived from other attributes is derived attribute.
Example: Total and average marks of a student.
Entities are represented by means of rectangles. Rectangles are named with the entity set they
represent.
Attributes
Attributes are the properties of entities. Attributes are represented by means of ellipses. Every
ellipse represents one attribute and is directly connected to its entity (rectangle).
Composite: they are further divided in a tree like structure. Every node is then connected to its
attribute. That is, composite attributes are represented by ellipses that are connected with an
ellipse.
Relationship
The association among entities is called a relationship. For example, an employee works_at a
department, a student enrolls in a course. Here, Works_at and Enrolls are called relationships.
Relationship Set
A set of relationships of similar type is called a relationship set. Like entities, a relationship too
can have attributes. These attributes are called descriptive attributes.
Degree of Relationship
The number of participating entities in a relationship defines the degree of the relationship.
Binary = degree 2
Ternary = degree 3
n-ary = degree
Mapping Cardinalities
Cardinality defines the number of entities in one entity set, which can be associated with the
number of entities of other set via relationship set.
One-to-one − One entity from entity set A can be associated with at most one entity of
entity set B and vice versa.
One-to-many − One entity from entity set A can be associated with more than one
entities of entity set B however an entity from entity set B, can be associated with at
most one entity.
Many-to-one − More than one entities from entity set A can be associated with at most
one entity of entity set B, however an entity from entity set B can be associated with
more than one entity from entity set A.
Many-to-many − One entity from A can be associated with more than one entity from B
and vice versa.
Many-to-one − When more than one instance of entity is associated with the
relationship, it is marked as 'N:1'. The following image reflects that more than one
instance of an entity on the left and only one instance of an entity on the right can be
associated with the relationship. It depicts many-to-one relationship.
Many-to-many − The following image reflects that more than one instance of an entity
on the left and more than one instance of an entity on the right can be associated with
the relationship. It depicts many-to-many relationship.
Participation Constraints
Total Participation − Each entity is involved in the relationship. Total participation is
represented by double lines.
Partial participation − Not all entities are involved in the relationship. Partial
participation is represented by single lines.
Key
A key is an attribute or set of attributes which helps us in uniquely identifying the rows of a
table. It also helps in establishing relationship among tables. We will now see how this is done
with the help of examples.
Example: If we have the details of students of a classroom stored in Student table as follows:
Now, from this classroom, if we want to call a student whose name is ‘Andrew’ we don't know
which ‘Andrew’ to call as there are two students with the same name. Also, we if know the age
of student ‘Andrew’ we can’t distinguish between both the students because both are having
the same age. So, there must some value through which we can distinguish and uniquely
identify the students. The ‘Roll_no’ attribute will help us in uniquely identifying the rows in a
table. We can say that ‘Roll_no’ is the key here. Now, if we know the ‘Roll_no’ of the student
then there will be no confusion and we can easily select the student from here.
Super Key
Candidate Key
Primary Key
Alternate Key
Foreign Key
Super Key
A super key or simply key is a combination of all possible attribute which can uniquely identify
the rows(tuples) in a table. This means that a superkey may have some extra attribute which
isn't necessary for uniquely identifying the rows in the table.
Example: In the given Student Table we can have the following keys as the super key.
1. {Roll_no}
2. {Registration_no}
3. {Roll_no, Registration_no},
4. {Roll_no, Name}
5. {Name, Registration_no}
6. {Roll_no, Name, Registration_no}
All the above keys are able to uniquely identify each row. So, each of these keys is super key.
Here you can see that by using Roll_no only, we can uniquely identify the rows but if you are
making a super key, then you will try to find all the possible cases of keys that can be used to
identify data uniquely.
Candidate Key
A candidate key is a minimal super key or a super key with no redundant attribute. It is called a
minimal superkey because we select a candidate key from a set of super key such that selected
candidate key is the minimum attribute required to uniquely identify the table. It is selected
from the set of the super key which means that all candidate keys are super key. Candidate
Keys are not allowed to have NULL values.
If the subset of the candidate key is a super key, then that candidate key is not a valid
candidate key.
Example: In the above example, we had 6 super keys but all of them cannot become a
candidate key. Only those super keys would become a candidate key which have no redundant
attributes.
1. {Roll_no}: This key doesn't have any redundant or repeating attribute. So, it can be
considered as a candidate key.
2. {Registration_no}: This key also doesn't have any repeating attribute. So, it can be
considered as a candidate key.
3. {Roll_no, Registration_no}: This key cannot be considered as a candidate key because
when we take the subset of this key we get two attributes i.e Roll_no or
Registration_no. Each of these attributes is the candidate key. So, it is not a minimal
super key. Hence, this key is not a candidate key.
4. {Roll_no, Name}: This key cannot be considered as a candidate key because when we
take the subset of this key we get two attributes i.e. Roll_no or Name. Roll_no is a
candidate key. So, it is not a minimal super key. Hence, this key is not a candidate key.
5. {Name, Registration_no}: This key cannot be considered as a candidate key because
when we take the subset of this key we get two attributes i.e Registration_no or Name.
Registration_no is a candidate key. So, it is not a minimal super key. Hence, this key is
not a candidate key.
6. {Roll_no, Name, Registration_no}: This key cannot be considered as a candidate key
because when we take the subset of this key we get three attributes i.e Roll_no,
Registration_no and Name. Two of these attributes i.e Roll_no and Registration_no are
the candidate key. So, it is not a minimal superkey. Hence, this key is not a candidate
key.
So, from the above discussion, we conclude that we can have only 2 out of above 6
super keys as the candidate key. i.e. (Roll_no) and(Registration_no).
Primary Key
The primary key is the minimal set of attributes which uniquely identifies any row of a table. It
is selected from a set of candidate keys. Any candidate key can become a primary key. It
depends upon the requirements and is done by the Database Administrator (DBA). The primary
key cannot have a NULL value. It cannot have a duplicate value.
Example: In the above example, we saw that we have two candidate keys i.e (Roll_no) and
(Registration_no). From this set, we can select any key as the primary key for our table. It
depends upon our requirement. Here, if we are talking about class then selecting ‘Roll_no’ as
the primary key is more logical instead of ‘Registrartion_no’.
Alternate Key
All the candidate key which are not a primary key are called an alternate key.
Example: In the above example, since we have made ‘Roll_no’ as the Primary Key our Alternate
Key would be ‘Registration_no’.
Foreign Key:
The foreign key of a table is the attribute which establishes the relationship among tables. The
foreign key is the attribute which points to the primary key of another table.
Example: If we have two tables of Student and Course then we can establish a relationship
between these two tables using a foreign key. The ‘Course_id’ in the Student table is the
foreign key as it establishes the link between the Student and Course Table. So, if we need to
find the information about any course opted by any student then we can go the Course table
using the foreign key.
The table name and column names are helpful to interpret the meaning of values in each row.
The data are represented as a set of relations. In the relational model, data are stored as tables.
However, the physical storage of the data is independent of the way the data are logically
organized.
1. Attribute: Each column in a Table. Attributes are the properties which define a relation.
e.g., Student_Rollno, NAME,etc.
2. Tables – In the Relational model the, relations are saved in the table format. It is stored
along with its entities. A table has two properties rows and columns. Rows represent
records and columns represent attributes.
3. Tuple – It is nothing but a single row of a table, which contains a single record.
4. Relation Schema: A relation schema represents the name of the relation with its
attributes.
5. Degree: The total number of attributes which in the relation is called the degree of the
relation.
6. Cardinality: Total number of rows present in the Table.
7. Column: The column represents the set of values for a specific attribute.
8. Relation instance – Relation instance is a finite set of tuples in the RDBMS system.
Relation instances never have duplicate tuples.
9. Relation key – Every row has one, two or multiple attributes, which is called relation key.
10. Attribute domain – Every attribute has some pre-defined value and scope which is known
as attribute domain
There are many types of Integrity Constraints in DBMS. Constraints on the Relational database
management system is mostly divided into three main categories are:
1. Domain Constraints
2. Key Constraints
3. Referential Integrity Constraints
Domain Constraints
Domain constraints can be violated if an attribute value is not appearing in the corresponding
domain or it is not of the appropriate data type.
Domain constraints specify that within each tuple, and the value of each attribute must be
unique. This is specified as data types which include standard data types integers, real numbers,
characters, Booleans, variable length strings, etc.
Key Constraints
An attribute that can uniquely identify a tuple in a relation is called the key of the table. The value
of the attribute for different tuples in the relation has to be unique.
Example:
In the given table, CustomerID is a key attribute of Customer Table. It is most likely to have a
single key for one customer, CustomerID =1 is only for the CustomerName =” Google”.
Customer ID Customer Name Status
1 Google Active
2 Amazon Active
3 Apple In Active
Referential Integrity Constraints
Referential Integrity constraints in DBMS are based on the concept of Foreign Keys. A foreign key
is an important attribute of a relation which should be referred to in other relationships.
Referential integrity constraint state happens where relation refers to a key attribute of a
different or same relation. However, that key element must exist in the table.
Example:
Update Operation
You can see that in the below-given relation table CustomerName= ‘Apple’ is
updated from Inactive to Active.
Delete Operation
To specify deletion, a condition on the attributes of the relation selects the tuple to be
deleted.
1. Strong entity
2. Weak entity
Strong entity
A strong entity set is an entity that contains sufficient attributes to uniquely identify all its
entities
Simply strong entity is nothing but an entity set having a primary key attribute or a table
which consists of a primary key column
The primary key of the strong entity is represented by underlining it
Representation
The strong entity is represented by a single rectangle.
Relationship between two strong entities is represented by a single diamond.
weak entity is an entity set that does not have sufficient attributes for Unique
Identification of its records
Simply a weak entity is nothing but an entity which does not have a primary key
attribute
It contains a partial key called as discriminator which helps in identifying a group of
entities from the entity set
Discriminator is represented by underlining with a dashed line
Representation
Relational database means the data is stored as well as retrieved in the form of relations
(tables). Table 1 shows the relational database with only one relation called STUDENT which
stores ROLL_NO, NAME, ADDRESS, PHONE and AGE of students.
Student Database:
Roll _No NAME ADDRESS PHONE AGE
401 AKHILA VISHAKAPATNAM 9912204100 24
402 SAMSKRUTHI DUBAI 9704216317 25
403 ASHWINI REDDY LONDON 9346261029 28
404 SANDHYA BANGALORE 9951063698 26
405 SHRAGVI GOKARANA 9755858898 27
These are some important terminologies that are used in terms of relation.
Attribute: Attributes are the properties that define a relation. e.g.; ROLL_NO, NAME etc.
Tuple: Each row in the relation is known as tuple. The above relation contains 4 tuples, one of
which is shown as:
Degree: The number of attributes in the relation is known as degree of the relation.
The STUDENT relation defined above has degree 5.
Cardinality: The number of tuples in a relation is known as cardinality. The STUDENT relation
defined above has cardinality 4.
Column: Column represents the set of values for a particular attribute. The column ROLL_NO is
extracted from relation STUDENT.
Roll _No
401
402
403
404
405
Data Manipulation Language: It is used to manipulate data in the relations. e.g.; INSERT,
DELETE, UPDATE and so on.
Data Query Language: It is used to extract the data from the relations. e.g.; SELECT
So first we will consider the Data Query Language. A generic query to retrieve from a relational
database is:
1. SELECT [DISTINCT] Attribute_List FROM R1,R2….RM
2. [WHERE condition]
3. [GROUP BY (Attributes)[HAVING condition]]
4. [ORDER BY(Attributes)[DESC]];
Part of the query represented by statement 1 is compulsory if you want to retrieve from a
relational database. The statements written inside [] are optional. We will look at the possible
query combination on relation shown in Table 1.
Case 1: If we want to retrieve attributes ROLL_NO and NAME of all students, the query will be:
Case 2: If we want to retrieve ROLL_NO and NAME of the students whose ROLL_NO is greater
than 2, the query will be:
Roll_No Name
403 ASHWINI REDDY
404 SANDHYA
405 SHRAGVI
CASE 3: If we want to retrieve all attributes of students, we can write * in place of writing all
attributes as:
SELECT * FROM STUDENT WHERE ROLL_NO>2;
Note: ORDER BY AGE is equivalent to ORDER BY AGE ASC. If we want to retrieve the results in
descending order of AGE, we can use ORDER BY AGE DESC.
COUNT (PHONE)
5
SUM (AGE) = 74
In the same way, MIN, MAX and AVG can be used. As we have seen above, all aggregation
functions return only 1 row.
AVERAGE: It gives the average values of the tupples. It is also defined as sum divided by count
values.
Syntax:AVG(attributename)
OR
Syntax:SUM(attributename)/COUNT(attributename)
The above mentioned syntax also retrieves the average value of tupples.
MAXIMUM:It extracts the maximum value among the set of tupples.
Syntax:MAX(attributename)
MINIMUM:It extracts the minimum value amongst the set of all the tupples.
Syntax:MIN(attributename)
GROUP BY: Group by is used to group the tuples of a relation based on an attribute or group of
attribute. It is always combined with aggregation function which is computed on group
SELECT ADDRESS, SUM(AGE) FROM STUDENT GROUP BY (ADDRESS);
ADDRESS AGE
VISHAKAPATNAM 24
DUBAI 25
LONDON 28
BANGALORE 26
GOKARANA 27
UNIT –III
STRUCTURED QUERY LANGUAGE (SQL)
Structured Query Language is a standard Database language which is used to create, maintain
and retrieve the relational database. Following are some interesting facts about SQL.
SQL is case insensitive. But it is a recommended practice to use keywords (like SELECT,
UPDATE, CREATE, etc) in capital letters and use user defined things (liked table name,
column name, etc) in small letters.
We can write comments in SQL using “–” (double hyphen) at the beginning of any line.
SQL is the programming language for relational databases (explained below) like
MySQL, Oracle, Sybase, SQL Server, Postgre, etc. Other non-relational databases (also
called NoSQL) databases like MongoDB, DynamoDB, etc do not use SQL
Although there is an ISO standard for SQL, most of the implementations slightly vary in
syntax. So we may encounter queries that work in SQL Server but do not work in MySQL.
SQL Process
When you are executing an SQL command for any RDBMS, the system determines the best way
to carry out your request and SQL engine figures out how to interpret the task.
There are various components included in this process.
Characteristics of SQL
SQL is easy to learn.
SQL is used to access data from relational database management systems.
SQL can execute queries against the database.
SQL is used to describe the data.
SQL is used to define the data in the database and manipulate it when needed.
SQL is used to create and drop the database and table.
SQL is used to create a view, stored procedure, function in a database.
SQL allows users to set permissions on tables, procedures, and views.
What is SQL Operator: A set operator in SQL is a keyword that lets you combine the results of
two queries into a single query.
Sometimes when working with SQL, you’ll have a need to query data from two more tables. But
instead of joining these two tables, you’ll need to list the results from both tables in a single
result, or in different rows. That’s what set operators do.
Different types of SET operators:
There are a few different set operators that can be used, depending on your needs, and
which database vendor you’re using.
The different set operators are:
UNION
UNION ALL
MINUS
INTERSECT
EXCEPT
It uses two (or more) SELECT queries, with a set operator in the
middle. There are a few things to keep in mind though.
When selecting your columns, the number of columns needs to match between queries,
and the data type of each column needs to be compatible.
So, if you select three columns in the first query, you need to select three columns in the
second query. The data types also need to be compatible, so if you select a number and
two character types in the first query, you need to do the same in the second query.
Also, if you want to order your results, the ORDER BY must go at the end of the last query.
You can’t add ORDER BY inside each SELECT query before the set operator.
DEPARTMENT_ID DEPARTMENT_NAME
1 Executive
2 HR
3 Sales
4 Development
5 Support
6 Research
And this is the employee table.
This shows a list of 6 departments and 16 employees. The manager_id column in the employee table
refers to the employee_id column in the same table. I’ll show you how to use this later in this guide.
If you want to follow along and create the database yourself, here is the SQL:
CREATE TABLE employee (employee _id NUMBER(5) PRIMARY KEY , full_name VARCHAR2(100),
department _id NUMBER (5) REFERENCES department (department_id), job_roleVARCHAR2(100),
manager_id NUMBER(5) );
INSERT INTO department (department _id ,department _name ) VALUES (1, ‘Executive’);
INSERT INTO department (department _id ,department _name ) VALUES (2, ‘HR’);
INSERT INTO department (department _id ,department _name ) VALUES (3, ‘Sales’);
INSERT INTO department (department _id ,department _name ) VALUES (4, ‘Development’);
INSERT INTO department (department _id ,department _name ) VALUES (5, ‘Support’);
INSERT INTO department (department _id ,department _name ) VALUES (6, ‘Research’);
But looking at this, we can’t tell which record comes from each table. Often we don’t need to
know, but sometimes we do.
How can we do that? We can add a static value to each query to indicate which table it came
from, or what type of record it is. As long as the number and type of columns match, the query
will work.
SELECT ‘ Customer ‘ AS record_type , First_name , last_name FROM Customer UNION SELECT
‘Employee’ , First _name , Last_name FROM employee ;
Result :
In this query, I added in a value of ‘Customer’ to display for all records from the Customer
table. It has a column alias of record_type. The same column is labelled as ‘Employee’ from
the employee table. Looking at the results you can see which records came from which table.
You don’t need to specify the column aliases on the second table. Oracle will know that the
first columns match and use the alias already provided in the first query.
The UNION and JOIN keywords both combine results from two different tables or
UNION combines data into separate rows, and JOIN combines data into separate columns.
When performing a JOIN, there is a column that matches between the two tables, and
additional data may be displayed.
If we wanted to JOIN our employee and customer tables, our query might look like this:
First_name last_name
Stephen Jones
Mark Smith
Denise King
Paula Johnson
Richard Archer
Christina Jones
Michael McDonald
Richard Smith
We can use different JOIN types to display different records based on matches being found,
but in general, that’s how a join is different to a UNION.
MINUS:
Another set operator we can use is the MINUS keyword.
The MINUS set operator will return results that are found in the first query specified that
don’t exist in the second query.
Using our example data, we could use the MINUS set operator to find all names in the
customer table that don’t exist in the employee table.
First_name last_name
Mark Smith
Denise King
Richard Archer
If a result exists in the employee table as well as the customer table, it is not shown. Only
the results from the customer table that are not in the employee table are shown.
What About the EXCEPT Set Operator?
The EXCEPT keyword is another set operator you might see in your code or in online
examples.
EXCEPT is the same as MINUS – they both show results from one query that don’t exist in
another query.
However, MINUS is an Oracle-specific keyword, and EXCEPT is in other databases such as SQL
Server.
So, if you see EXCEPT anywhere, just know it’s the same as MINUS but for a different
database.
INTERSECT:
The INTERSECT keyword allows you to find results that exist in both queries. Two SELECT
statements are needed, and any results that are found in both of them are returned if
INTERSECT is used.
Using our example data, we could use the INTERSECT set operator to find all names in the
customer table that don’t exist in the employee table.
Result :
first_name last_name
Stephen Jones
Paula Johnson
This shows all names that are in both the customer and employee table.
If we want to order these, we add an ORDER BY at the end.
SELECT First_name , last_name FROM Customer INTERSECT SELECT first_name ,last_name
FROM employee ORDER BY First _name , Last_name ;
Result :
First_name last_name
Paula Johnson
Stephen Jones
The difference between UNION and INTERSECT is that UNION gets results from both queries
and combines them, while INTERSECT gets results that only exist in both queries.
So, if Query 1 returns records A and B, and Query 2 returns records B and C, UNION would
return A, B and C. INTERSECT would only return B.
Sub queries:
FROM
The SQL FROM clause is where you specify where your data is to be retrieved from. This will
often be a table in your database, but it can also be a view or a subquery.
The syntax of the FROM clause looks the same regardless of what you’re selecting from:
This example selects data from one table: the employee table. We can’t actually tell if it’s a
table or a view by looking at the name, but it doesn’t matter for the purpose of this query.
You can also use joins to SELECT data from multiple tables. I’ve written an entire post on
joins, as they are a pretty big feature of SQL, but a basic query with a join looks like this:
This query will show the employee’s first name and last name, as well as their department
name. It uses an INNER JOIN to match records based on each table’s department ID.
WHERE:
The WHERE clause of the SQL SELECT statement is where you determine which rows to
return from your table or view. It’s an optional clause, so if you don’t specify a WHERE clause
in your query, you’ll get all the records:
The criteria is a rule that is checked against each row. If the criteria is true for a row, the row is
included in the results. If it is false, the row is not included.
For example, this query shows all employees with a first_name of John.
The WHERE clause can get quite complicated. You can have multiple criteria using the
AND or OR keywords, you can use brackets to group criteria, you can have greater than
and less than, partial matches, and much more.;
GROUP BY :
The SQL GROUP BY clause is used when you are using aggregate functions and want to
specify what values you want to have those aggregate functions calculated by.
For example, this query will count all employees:
HAVING:
The HAVING clause lets you filter a SELECT query that uses a GROUP BY clause based on the
results after a GROUP BY.
It uses the same concept as a WHERE clause, except the WHERE clause filters data before the
grouping, and HAVING filters data after the grouping.
For example, this query shows the count of employees in each department where there is
more than 1 employee in the department.
SELECT dept _id , COUNT (*) FROM employee GROUP BY dept_id HAVING COUNT ( *)
>1;
ORDER BY
The ORDER BY clause lets you specify the order you want the query results to be displayed in.
The database does not guarantee the order that the results are returned in. Even if it looks
like there is an order (e.g. ordered by ID), if you run the same query at a later date, the order
could change.
So, if you want your data in a specific order, you can use the ORDER BY clause:
https://www.databasestar.com/sql-joins/