SQL Server is a relational database management system that Microsoft has developed. It is mainly designed and built to compete with the Oracle and MySQL database. SQL server is primarily used for storing and retrieving data. SQL Server's key interface tool is SQL Server Management Studio, which runs on both 64-bit and 32-bit environments.
In order to understand SQL Server completely, first, we need to learn the SQL language. SQL is the query processing language used to process data into relational databases. In the client server model, the database server is a program which offers multiple services. Accordingly, we have referred to a SQL server as a database server that uses SQL as a query language.
Take your career to next level in SQL Server DBA with HKR. Enroll now to get SQL Server DBA Certification Course Training!
The key usage for MS SQL Server is as follows:
SQL Server is a client-server based architecture and is aimed at end-users called clients who send queries to the MS SQL server that is installed on a specific computer. Once the processing input data is requested, the server will deliver the expected result. This server is vacant as a separate program and is in charge of managing any statements, instructions, or commands in the database.SQL server database engine will control data processing, storage, and security. So, it is the main component of the SQL server.
SQL Server looks very simple, but internally in the background, many processes run to respond to the requests. According to the architecture, the SQL Server primarily has three main components: Network Protocols, SQL OS, and database engine.
SQL OS: It is used for the first time in SQL 2005. In the past, this was only intended for small and medium-sized applications. Microsoft is upgrading SQL Server into SQL 2005 to fit the load of high-end enterprise databases. This is a layer that is located between the Windows OS and the database engine. Numerous OS services are managed by SQLOS, that includes I/O and memory management, threading, scheduling, synchronization, and exception handling.
[Related article: Exception Handling in SQL Server]
Database Engine: This is what the SQL Server architecture is all about. This is the second layer in the architecture which provides connectivity among user connections using the network protocol and the SQL server OS to actually execute. It demonstrates the logical architectures of the database objects like views, tables, triggers, and stored procedures that work with relation engine and physical architecture to meet customer requests. The database engine is made up of two components:
Storage Engine: It displays physical database architecture along with data storage and retrieval from buffer manager and storage system.
Relational engine: This is responsible for the evaluation of user requests and carries out the execution. It decides what is the most effective way to execute a query. This is also known as the Query Processor. The main tasks performed by relational engines are memory management, Query processing, distributed query processing, buffer management, and thread and task management.
Network Protocol: It is completely responsible for the client connectivity of the SQL Server database engine. In addition, it has another protocol called VIA. The VIA is the hardware protocol which has become obsolete by Microsoft. In the most recent SQL Server Configuration Manager, this protocol will not be seen.
MS SQL Server includes Business Intelligence (BI) and Data Management tools and services. SQL Server consists of the following tools and services for managing data:
[Related Article: SQL Server Data Tools]
SQL Server Instances
SQL Server installation is an instance. A number of instances can be installed on a specific machine, but the default will be only one. It is the accurate copy of databases, server files, and security credentials.
Top 20+ frequently asked SQL Server interview questions & answers for freshers & experienced professionals
The SQL Server instance has the following benefits:
In this tutorial, we have covered all the basics related to SQL Server like SQL Server usage, architecture, and SQL Server Instances. We hope you found this tutorial helpful. If you find any topic to be included in this tutorial do not forget to comment below.
SQL Server is a Microsoft-developed and promoted relational database management system or RDBMS. SQL Server is designed on top of SQL, a popular programming language for communicating with relational databases, much like other RDBMS applications. SQL Server is related to Transact-SQL, or T-SQL, which incorporates a collection of proprietary programming constructs to Microsoft's SQL implementation. For over 20 years, SQL Server has been running exclusively in the Windows environment. Microsoft made it available on Linux in 2016. In October 2016, SQL Server 2017 was typically available, running on both Windows and Linux.
Take your career to next level in SQL Server DBA with HKR. Enroll now to get SQL Server Certification Course Training!
Unlike most other desktop applications, the SQL Server can function like a client-server database system. These systems operate either on a central server or on many central servers. This enables the data to be accessed concurrently from the network by several users. The database can be accessed by users through an application. Using the database, the data is saved and made available. In SQL Server, some helpful features allow CRM applications to deliver their functionality. Working professionals can be in various nations and access the data through the browser and update it.
There were approximately 19 distinct models of SQL Server. There were, indeed, inevitably some recognizable ones. They are listed below.
The SQL Server Architecture is built on the model of the client/server architecture where users share and access the data on the server through client applications. Understanding internal design or architecture is the best method of learning technology.
General Architecture:
The General Architecture takes care of a few specifics, such as queries, protocols, servers, and relational engines. It also engages in splitting the queries into logical units and obtaining requests initiated by clients. It also engages operating with a query optimizer and the right plan of execution. The below diagram illustrates the overview of general architecture.
IMG
Memory Architecture: One of the memory architecture's main design objectives is to reduce reads and writes to the disk. It is a crucial part of the architecture of SQL Server. The below diagram illustrates the overview of memory architecture.
IMG
Data File Architecture: The most critical aspect of the Data File Architecture is the grouping of the database into database files used for allocation and administration purposes. A file does not become a member of more than one single group. The below diagram illustrates the overview of Data File Architecture.
IMG
Log File Architecture:
The logical operation is carried out by the transaction log of the SQL Server. Here, the transaction log serves as a log record string. The processes are all logged. The Log File Architecture depends heavily on the SQL Server Architecture. The below diagram illustrates the overview of log file architecture.
IMG
The latest SQL Server versions are launched every 18 months. Significant developments often do not make marketing headlines, but also deliver improvements in productivity and performance. In the consolidated updates, Microsoft began to incorporate product releases.
Database Engine: This section of the SQL Server builds and drives relational databases. The below diagram illustrates the overview of database engine architecture.
IMG
SQL Server Analysis Services: This section manages SQL Server data-analysis. They build Online Analytical Processing cubes which are highly developed programming objects that can organize data within the relational database. The below diagram illustrates the overview of SQL Server 2005 analysis services architecture.
IMG
SQL Server Reporting Services: It is a SQL Server component that, despite the database operating system, offers reporting. The below diagram illustrates the overview of SQL Server reporting services architecture.
IMG
SQL Server Integration Services: The SQL Server will perform the Extract, Transform, and Load or ETL process of the SQL Server. The below diagram illustrates the overview of SQL Server Integration Services architecture.
IMG
Keys function in the database tables as a blend of single or multiple fields. They are used as per the specifications to retrieve the records or data from the rows in a table. It is also feasible to use these keys to determine the relationship between tables. Keys categories include the following:
In the RDBMS system, SQL allows users to access data. It assists you to describe the data and allows you to define the data in a database and to manipulate that relevant data. You can build and drop databases and tables with the aid of SQL commands in the DBMS. SQL provides you with the ability to use the function in the database, create a view, and stored procedure. On tables, procedures, and views, you may indeed set permissions. There are five types of SQL queries used extensively.
Top 30+ frequently asked SQL Server interview questions & answers for freshers & experienced professionals
The Data Definition Language helps you describe the structure or schema of the database. The following are the types of DDL commands which will be used in SQL:
1) CREATE
CREATE statements are used to determine the schema of the database structure:
Syntax:
CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);
Examples:
Create database university;
Create table students;
Create view for_students;
2) DROP
Delete tables and databases from RDBMS by dropping commands.
Syntax:
DROP TABLE;
Examples:
Drop object_type object_name;
Drop database academy;
Drop table scholar;
3) ALTER
The Alters command enables you to manipulate the database structure.
Syntax:
To add a new column in the table.
ALTER TABLE table_name ADD column_name COLUMN-definition;
To modify an existing column in the table:
ALTER TABLE MODIFY(COLUMN DEFINITION....);
Example:
Alter table hkr99 add subject varchar;
4) TRUNCATE:
This command was used to erase all the table rows and free the space containing the table.
Syntax:
TRUNCATE TABLE table_name;
Example:
TRUNCATE table scholars;
The Data Manipulation Language (DML) lets you alter an instance of a database by inserting, modifying, and deleting its data. In a database, it is responsible for implementing all types of data modification. There are three fundamental constructs that allow users and database programs to fill in data and information:
In SQL, here are a few important DML commands:
1) INSERT
It is an SQL query statement that is used to insert data into the row of a table.
Syntax:
INSERT INTO TABLE_NAME (col1, col2, col3,.... col N)
VALUES (value1, value2, value3, .... valueN);
Or
INSERT INTO TABLE_NAME
VALUES (value1, value2, value3, .... valueN);
Example:
INSERT INTO students (RollNo, FIrstName, LastName) VALUES ('60', 'Tom', Erichsen');
2) UPDATE
This command is used to update or alter the value of a table column.
Syntax:
UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE CONDITION]
Example:
UPDATE students SET FirstName = 'Peter', LastName= 'Thompson' WHERE StudID = 10;
3) DELETE
To delete one or more rows from a table, this command is used.
Syntax:
DELETE FROM table_name [WHERE condition];
Example:
DELETE FROM students WHERE FirstName = 'Peter';
DCL (Data Control Language) contains commands that are useful for giving "rights & permissions." such as GRANT and REVOKE. Other permissions govern the database system parameters.
Examples of DCL commands:
The following are the DCL Commands:
1) Grant
This command is used to provide a database with user access privileges.
Syntax:
GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;
Example:
GRANT SELECT ON Users TO'Tom'@'localhost;
2) Revoke
It is useful to back permissions from the user.
Syntax:
REVOKE privilege_nameON object_name FROM {user_name |PUBLIC |role_name}
Example:
REVOKE SELECT, UPDATE ON student FROM BCA, MCA;
Inside the database, the transaction control language or TCL commands deal with the transaction.
1) Commit
This command is used to save the database with all the transactions.
Syntax:
Commit;
Example:
DELETE FROM Students WHERE RollNo =25;
COMMIT;
2) Rollback
You can undo transactions that have not already been saved to the database using the rollback command.
Syntax:
ROLLBACK;
Example:
DELETE FROM Students WHERE RollNo =25;
3) SAVEPOINT
You can set a savepoint inside a transaction with this command.
Syntax:
SAVEPOINT SAVEPOINT_NAME;
Example:
SAVEPOINT RollNo;
To extract data from the database, the Data Query Language (DQL) is used. Only one command will be used by DQL:
SELECT:
This command assists you select an attribute based on the condition that the WHERE clause describes.
Syntax:
SELECT expressions FROM TABLES WHERE conditions;
Example:
SELECT FirstName FROM Student WHERE RollNo > 15;
The SQL Server instance refers to the Database Engine instance. This is a copy of sqlservr.exe that can be executed on the operating system. Several system databases would be able to manage each of these instances. This means that multiple Database Engine instances can be run independently of the other instances on a computer.
You can install these instances at any time, and even after the SQL Server has been running for some time on the system. The instances allude primarily to the engine of the database and the supporting components. For client tools, they do not apply. The below diagram illustrates the overview of SQL Server 2014 instance architecture.
IMG
The sky is the limit when it comes to different examples. The reasons can differ throughout various organizations. These are some of the benefits provided by SQL Server Instances.
Applications for the client-server are applications that allow the user to access data stored on the server. It does not matter to the computer system. Both of these may be workstations and may run on the same type of operating system. The servers would hold databases and the workstations would allow the users to access the database. By using some other database program, a client/server database application can be built.
In this process, multiple database management activities are involved. They are as follows:
In many editions, SQL Server is available. Below are different editions with their characteristics:
Enterprise: It is the best edition and contains all the numerous SQL Server features available. It is intended for large organizations and administrators of businesses.
Standard: The characteristics available in this edition are lower than those of Business. When there is no need for advanced functionality, it finds use. These can be used by industries that do not require advanced features.
Workgroup: In the remote offices of large corporations, this edition discovers opportunities.
Web: This edition is used for web applications in particular.
Express: This is an entry-level edition. In addition to 1 GB of memory, only 1 CPU can be used and the maximum database capacity is 10 GB.
Developer: It is licensed to only a single user for testing, demo, and development, identical to the Enterprise edition. Without any need for reinstallation, the Developer edition can be upgraded to the Enterprise edition.
Datacenter: The recent major update for the new SQL Server 2008 R2 is the Datacenter Edition. In terms of memory, it has no limitations. it will provide assistance even for more than 25 instances.
Compact: This edition is free and it is available for the creation of mobile applications. 4 GB is the database's maximum allocated capacity.
Enterprise Evaluation: A completely functioning and free SQL Server instance is provided in the Evaluation Edition. It is perfect for studying and implementing ideas and will immediately expire after 6 months.
Business Intelligence: All of the features are incorporated in the Standard edition. PowerPivot and Power View are some advanced features that are also included. Many advanced online operations, indeed, are available.
If you'd like to know the version of your SQL, you have to implement the corresponding steps.
The following needs must be installed first in order to use SQL Server. Two types of installations exist:
Step 1: You need to download the Evaluation Edition from the official website of Microsoft. You will find some files depending on the bit-version selected:
ENUx86SQLFULL_x64_ENU_Core.box
ENUx86SQLFULL_x64_ENU_Install.exe
ENUx86SQLFULL_x64_ENU_Lang.box
(OR)
ENUx86SQLFULL_x86_ENU_Core.box
ENUx86SQLFULL_x86_ENU_Install.exe
ENUx86SQLFULL_x86_ENU_Lang.box
Step 2: You can choose "Install.exe" to retrieve the appropriate files from the directories you have downloaded.
Step 3: Then you can double-click on the program “SETUP”.
Step 4: On the left side of the SQL Server Installation Center screen, press Installation.
Step 5: The first preference you can find on the right-hand tab should be pressed.
Step 6: On the following screen that is displayed, you should press "OK".
Step 7: Select the “Next” button.
Step 8: You need to select the product key and click on Next to verify it.
Step 9: You have to select the checkbox to approve the license option and then click on Next.
Step 10: You must select the option to install the SQL Server feature and select Next.
Step 11: You can select the Checkbox for Database Engine Services and then click Next.
Step 12: You should enter a named instance, provide an appropriate name, and then click the Next button.
Step 13: On the screen that is displayed, simply click on Next.
Step 14: You can select the names of the service accounts and the start-up types for the services listed and then proceed to the Collation tab.
Step 15: Make sure that the right collation type is selected and then press Next.
Step 16: You must ensure that the selection of the authentication mode and the administrators are checked, and then press Data Folders.
Step 17: You have to select the directory locations specified above and then click Next.
Step 19: Simply press the 'Next' button.
Step 20: Select the installs above and then press the Install button.
You'd be able to start using SQL Server this way.
The following steps must be implemented to build a new database on the SQL server:
Step 1: Just open the Microsoft SQL Management Studio.
Step 2: You can connect to the database engine and use the credentials of the database administrator.
Step 3: This server node should be extended.
Step 4: You should right-click on the Databases and then select the new Database option.
Step 5: You need to enter a name and then press the OK button.
SQL Server is among the world's most familiar databases and can be used for multiple applications. SQL Server helps you to simultaneously handle multiple users and ensure that the properties of the selected isolation level are observed by transactions. Locking safeguards information and internal resources that allow a single-user system to function as a multi-user system. It can be used for replication, managing users, roles, and optimization tasks other than maintaining databases and maintaining backups. Since today's virtual environment is built on data that is properly structured with the help of databases, SQL Server learning can give profitable opportunities in leading MNCs and secure a great future ahead.
Related Article:
PostgreSQL is one of the powerful and open-source data management tools. This tool is based on the object-oriented database management system. This tool helps the user to extend all kinds of SQL language that offers many advanced features that store the data safely and supports the scalability of the most complicated data. We can pronounce this PostgreSQL as (Post-gres-ql). In the year 1986 at the University of California, Berkeley developed this PostgreSQL project. It has more than 30 years of an active database development used on many-core platforms. PostgreSQL supports almost all kinds of relational database management features that are not present in other query language tools such as SQL and MySQL.
Take your career to next level in PostgreSQL with HKR. Join PostgreSQL online training now
PostgreSQL was created by the computer science professor at the University of California named Michael Stonebraker. The original name of this tool was Postgres.
1986-> It was a Postgre project at university of California, Berkeley
1994-> First commercialized as an Illustra
1995-> open-source development started
1997-> ported to different languages like Japanese, they developed to support in Japan
1999-> First-time full-time developer and corporate support
2004-> Native window support
2006-> Supported by Sun.
Let us know the different versions of PostgreSQL:
1996-> PostgreSQL 6.0
1997-> PostgreSQL 6.1 and PostgreSQL 6.2
1998-> PostgreSQL 6.3 and PostgreSQL 6.4
1999-> PostgreSQL 6.5
2000-> PostgreSQL 7.0
2001 -> PostgreSQL 7.1
2002-> PostgreSQL 7.2 and PostgreSQL 7.3
2003-> PostgreSQL 7.4
2005 and 2006-> PostgreSQL 8.0
Currently we are using PostgreSQL 9.4.15
The major features of PostgreSQL are:
1) Portable:
2) Reliability:
3) Scalable:
These are the major features of PostgreSQL.
This is an important question that comes to mind when you begin your journey as a PostgreSQL; Let me give a brief idea about why it is PostgreSQL.
Till now we have discussed PostgreSQL definition, its history, and main features. Now I am going to discuss its advantages and disadvantages. Firstly know the advantages of PostgreSQL,
Advantages of PostgreSQL:
PostgreSQL is an open-source SQL standard compliant RDBMS tool. It supports all relational data types, views, and features of RDBMS.
Disadvantages of PostgreSQL:
These are the advantages and disadvantages of the PostgreSQL tool.
PostgreSQL supports rich data types. Users can create their data types. This PostgreSQL offers customized data type creations. With the help of the CREATE TYPE command, we can create new data types.
The following data types are available in PostgreSQL tool:
Let us discuss one by one,
Character Data types:
Character Data types in PostgreSQL support storing text values. These character data types help to create the same internal structures. There are 3 types of character data types available:
CHAR (n) -> in this we can add fixed-length and blank characters
VARCHAR (n) -> in this type we can create variable-length with a limit
Text-> in this type we can create data types with unlimited data lengths.
Numeric data types:
PostgreSQL supports two types of numeric data types.
Integers -> to store integer data types
Floating-point -> to store floating-point types.
Binary data types:
PostgreSQL supports storing binary data types; they can be octets or bytes. There are two types of binary data types available such as,
Whereas character string does not allow storing of zero octets and disallows any other sequence of octet values.
Network data types:
Many network applications in PostgreSQL allow users to store network information such as IP addresses and sensors.
There are three types of network data types available those are,
There are lots of advantages to using Network data types:
Text search data types:
This PostgreSQL supports two types of data which are mainly designed to create or support searching of the full text. Full text is a collection of natural-language documents.
There are two types of text search data types available such as,
Date/Time Data types:
The date/ time data type helps the user to store the time zones and date. By using these data type
You can also change the date/time formats including traditional PostgreSQL format, ISO
8601 and SQL compatible etc.
There are six types of Date/Time data types available:
Boolean Data types:
There are three types of Boolean data types available,
Geometric data types:
This Geometric PostgreSQL data type supports two-dimensional spatial objects. By using this data type we can perform operations like rotations, scaling and translation, etc.
There are 7 types of Geometric data type available,
Enumerated data types:
Enumerated data types are useful for the creation of rarely changing information such as Country code or branch id. This data type helps create a table with foreign keys to ensuring data integrity.
Range Data type:
This Range data type is used mainly for creating many business applications that require data in ranges.
UUID type:
Universally Unique Identifier (UUID) is of the 128-bit quantity which is used in the generation of an algorithm. This Identifier is mainly used for the distributed systems because it provides uniqueness within a single database. UUID is mainly written as a group of lower-case hexadecimal digits and here we use hyphens to separate the groups.
XML type:
This XML data type in PostgreSQL allows users to store XML data in data types, in the form of extensions. It adds extensions to text data types.
JSON data type:
This JSON PostgreSQL offers 2 data types such as,
JSON-> a simple extension of text data types with JSON validation
JSONB-> A binary representation of the JSON data.
Pseudo data types:
PostgreSQL has many special entries that are called Pseudo-types. But we can't use them as a column-data type. This type is used to declare or function's arguments or return type.
Data tables:
This data type of PostgreSQL is used in the creation of data tables. For example,
Data Filters:
The key benefit of using this data filter is more readable than the when case statement. Let us get into the brief detail,
This type is used to retrieve the data from the database. In the PostgreSQL query data, type uses the SELECT command to specify the queries.
Query1 UNION [ALL] query2
Query1 INTERSECT [ALL] query2
Query1 EXCEPT [ALL] query2
PostgreSQL function, also known as stored procedures, allows users to perform many operations and queries within the database. This function in PostgreSQL allows the database to reuse other applications.
The basic syntax of this function is as follows,
CREATE [OR REPLACE] FUNCTION function_name (arguments)
…………..
Function totalRecords () syntax is as follows,
CREATE OR REPLACE FUNCTION total records ()
……………
Few of important functions we use those are,
PostgreSQL COUNT Function
PostgreSQL MAX Function
PostgreSQL MIN Function
PostgreSQL AVG Function
PostgreSQL SUM Function
PostgreSQL ARRAY Function
PostgreSQL NUMERIC Function
PostgreSQL STRING Function
PostgreSQL Applications:
As I said earlier this PostgreSQL database tool is widely used in many industries. Let us discuss the application of PostgreSQL:
Financial Industry:
PostgreSQL database is an ideal tool for financial industries, as it is completely ACID-compliant which supports the creation of OLTP (online transaction processing). It allows users to work on database analytics. This PostgreSQL also offers integration with much software like MATLAB and R.
Government GIS data:
PostgreSQL database tool offers the most powerful GIS applications they are called "PostGIS". This GIS extension supports thousands of functions to work with geometric data in different available formats. In recent times, PostGIS is highly standard compliant.
In PostgreSQL, we can see two GIS tools such as QGIS and Geoserver which offer the easiest methods to handle the Geodata.
Manufacturing industry :
Nowadays many manufacturing industries have started using the PostgreSQL database tool, as it offers a feature that helps to speed up their business applications. This PostgreSQL optimizes supply chain management performance. One of the main advantages of using this database tool is that, helps to reduce the business operation cost.
If you have any doubts on PostgreSQL, then get them clarified from PostgreSQL Industry experts on our PostgreSQL Community!
Web development technologies:
Most of the web development companies prefer PostgreSQL to increase their scalability as they deal with thousands of requests per second at a time. To handle these requests, the PostgreSQL tool proves the best solution. Many features of PostgreSQL offer the best work experience with modern web frameworks like Node.js, ext.js, and Django.
Scientific data:
When you are working on research and science-related project, it needs to generate terabytes of data. It is very important to handle the data in an effective way. Data analytics and an effective engine help you to manage a large amount of data with no trouble.
Now it’s time to know the difference between PostgreSQL and MySQL
Step by step procedure to how to download and install the PostgreSQL tool,
Step 1: First go to the website PostgreSQL Download and select window as shown in the below figure,
Step 2: two options available,
But BigSQL currently installs pgAdmin version 3 which is deprecated. So it’s better to choose Enterprise DB which installs the latest version 4
Step3: Now you will be taken into installing your desired PostgreSQL version and OS. Choose the latest version and OS as per your environment-> click the Download button
The download will start like this,
Step4: Open the Exe file-> click the next on the install screen
Step 5: Change the installation directory, or if you don't want to leave it to default-> click next
Step 6: Choose the component you want to install, you can uncheck stack builder-> click next
Step7: you may change the location of data->click next
Step8: Enter user password -> click next
Step9: Leave the port number default-> click next
Step 10: Check the pre-installation summary-> click next
Step 11: Click the next button
Step 12: Once the installation is complete, you will see the stack builder,
Uncheck that option -> click finish
Step 13: To launch the PostgreSQL -> got o start menu -> search PgAdmin4
Step 14: Now you get into the PgAdmin homepage
Step 15: Click on the servers-> PostgreSQL 10 in the left tree
Step 16: Enter user password which is set during the installation-> click OK
Step 17: Now you will see the Dashboard
Then it will take into the PostgreSQL installation.
Conclusion :
PostgreSQL is a freely usable software and open-source database management tool to support many Relational database management features. This tool offers easy access to complicated data types, as it offers many powerful tools. It helps us to create our data type and its fully customized tool. And a very important thing is that no-cost is required for purchasing this software. I hope this article may help a few of you who want to pursue their career as a database developer.
As is said earlier, PL/SQL is one of the Oracle products and first developed by Oracle corporations in the year 1980. This product is a combination of procedural and RDBMS database language. You can also mention PL/SQL is a portable and high-level database transaction processing language. The PL/SQL offers a built-in and OS independent database programming environment. More importantly, PL/SQL is also known as the Command-line Sequential query language Plus interface. With the help of PL/SQL, it’s also possible to make direct calls from an external database programming language. The PL/SQL general syntax is created on the base of ADA and Pascal programming languages. This type of database product is available in ten times in-memory database and IBM database version2.
Below are the important key features of Oracle PL/SQL:
1. Oracle PL/SQL is tightly integrated with Sequential query language or SQL.
2. This language helps the developer to perform extensive error checking.
3. Oracle PL/SQL language supports to work with numerous data types.
4. This PL/SQL provides a wide variety of structural programming methods.
5. With the help of PL/SQL supports a structured programming facility through multiple database functions and procedures.
6. This PL/SQL offers development based web application servers and also generates web server pages.
7. As is said earlier PL/SQL is a combination of procedural and query database language. So this enables us to work with object-oriented programming. (Object-oriented is a procedural programming language).
The following are the very important advantages of Oracle PL/SQL:
1. SQL or sequential query language is the standard and popular database language. So PL/SQL integrates with this sequential query language database. The Oracle PL/SQL offers both dynamic and static query language. The static language supports Data management language operations and data transaction control system from PL/SQL database blocks. In a dynamic language, the SQL supports embedded dynamic database query language statements in PL/SQL.
2. The Oracle PL/SQL allows users to send an entire database of blocks of statements one at a time. This reduces networking data traffic and supports high-performance applications.
3. The Oracle PL/SQL offers high productivity to database programmers, so you perform operations like querying the data, data transmission, and data updation.
4. With the help of PL/SQL, users can save time on web designing and debugging features. Also, you can perform exception handling, data hiding, object-oriented data type management, and data encapsulation methods.
5. The applications which are written in PL/SQL are highly portable.
6. The Oracle PL/SQL also provides high-level data security.
7. Oracle PL/SQL supports object-oriented programming and developing web applications.
The architecture of Oracle PL/SQL explains the basic components and work nature. The below diagram will illustrate this:
The following three are the basic components of Oracle PL/SQL:
1. PL/SQL Block
2. PL/SQL engine
3. Database server
Let me explain them one by one;
This is one of the basic components that consists of only PL/SQL codes. This also comprises different data sections which will divide the PL/SQL codes logically. These types of logical codes are mainly used for declaring purpose, data processing, exception handling to handle errors. This block also contains only SQL instructions used to interact with the database servers. The below are important blocks of PL/SQL database:
a. Anonymous block
b. Database functions
c. Libraries
d. procedural –oriented
e. Package body management
f. package specifications and triggers
g. Type and type of body
This type of PL/SQL engine is one of the important components of Oracle PL/SQL and here actual data processing step has taken place. This component separates the PL/SQL data units and SQL is a part of the inputs. The whole PL/SQL data units will be handled by the PL/SQL engine. Here the SQL part will be transferred into the database server where the actual interaction takes place. It can be installed on both the database server and the application server.
This is the important component in the PL/SQL unit used to store the actual datasets. This PL/SQL database server uses the SQL used to establish a communication with the database server. It consists of an SQL executor that inputs the SQL statement and executes the same.
Now it’s time to know the major differences between SQL and PL/SQL:
First, let’s explain about SQL:
1. SQL:
a. SQL stands for sequential query language used to perform various database operations like DML and DDL.
b. SQL statement is declarative, and that specifies how to be done with database management.
c. SQL database statements are executed as a single statement at a time.
d. SQL is mainly used for data manipulation purposes.
e. Very easy to interacts with a database server.
Now PL/SQL turn:
2. PL/SQL:
a. PL/SQL is a type of block that consists of codes. These codes are used to write the whole program blocks/ functions and procedures.
b. PL/SQL is a procedural programming language that specifies how SQL statements need to be used.
c. PL/SQL executes the whole block at a time.
d. PL/SQL is mainly used to create any database applications.
e. This doesn’t establish any interaction with the database server.
f. PL/SQL is an extension of the query language (SQL) and so this contains only query statements.
In this section, we are going to explain the basic syntax which we are going to use in the Oracle PL/SQL. Let me explain them one by one:
1. Declaration statement: In this section, the SQL statement will starts with the keyword DECLARE. This is an optional keyword that defines all the variables, subprograms, other elements, and cursors to be used while writing the program codes.
2. SQL executable commands: In general, this statement is enclosed between the keywords like BEGIN and END. This one is considered a mandatory section as it consists of both PL and SQL query statements of the program code. This declaration statement consists of at least one executable line of program codes, and this can be used sometimes as a NULL command where there is nothing to execute.
3. Exception handling method: This section of the PL/SQL statement starts with the keyword EXCEPTION. It is used to handle the errors in the program while executing them
The following program code is syntax for PL/SQL statement:
DECLARE statement
BEGIN execution
END;
// Continuation statement, this is also known as a nested statement.
Oracle PL/SQL statement consists of various characters with special meaning: let me mention a few of them,
+, -, *, / = this is nothing but Addition, subtraction, multiplication, and division symbol.
% = this is an attribute indicator.
‘ = this symbol is a character string delimiter.
. = This is a component selector
(,) = This is an expression or list delimiter
: = this indicates a host indicator
“ = Quoted identifier symbol
= = This is a relational operator
First Oracle PL/SQL program: “Hello world”
So now it’s time to execute our First oracle PL/SQL statement:
DECLARE statement
Message varchar2 (10): = ‘Hello world!’;
BEGIN
Dbms_output. Put_line (message); //to display the output
END statement;
/ // this is to end the execution code
OUTPUT:
Hello World
PL/SQL procedure statement successfully completed.
Now it’s time to know the PL/SQL comments:
Comments in PL/SQL program are the explanatory statement, and with the help of these comment lines, anyone can better understand the source code. All programming languages follow some sort of comments. The PL/SQL program supports both single-line and multi-line comments.
For example:
DECLARE statement
--- Variable declaration
Message varchar1 (10): = “Hello world!” ;
BEGIN statement
/*
*
*/ PL/SQL executable statements //multiline comment
Dbms_output. Put_line (message);
END;
/
The Oracle PL/SQL supports variable types, constants, and parameters types. In this section, we are going to explain important data types that support Oracle PL/SQL programming.
Let me explain them one by one:
1. Scalar: this data type holds Number, DATE, or Boolean data, and this is also a single value with no internal component.
2. Large Object Boolean or (LOB): This is a type of pointer object which are used to store data items separately other than the data set. The data items can be of type text, video clips, images, and sound waveforms.
3. Composite data type: this type of data type consists of various internal components that can be accessed individually. For example collection of data records.
4. Reference type: This is a type of pointer which refers to other data value.
Now let me explain the subtypes of Scalar:
1. Numeric type: this type of data value holds arithmetic operations.
2. Character type: this is an alphanumeric data type that represents the single characters and string of characters.
3. Boolean: Boolean data type consists of logical operations.
4. Date time: this scalar data type consists of dates and times value.
The following are the important syntax which is used to represent numeric value:
1. PLS_INTEGER // this represents the integer data type
2. BINARY_OPERATOR // this represents the only binary set of operators.
3. BINARY_FLOAT // this is a single-precision IEEE format floating-point number.
4. BINARY_DOUBLE
5. NUMBER (prec, scale)
6. DECIMAL (prec, scale)
The below is a simple program:
DECLARE Statement
NUM1 INTEGER_VALUE;
NUM2 REAL_VALUE;
NUM3 DOUBLE_PRESECION;
BEGIN statement
NULL_VALUE;
END statement;
/
Output:
PL/SQL procedures are successfully completed.
A subtype is another data type; this is known as the base type. This subtype consists of the same valid operations. This PL/SQL user-defined several subtypes in packages like STANDARD, CHARACTER, and INTEGER.
SUBTYPE CHARACTER IS CHAR; //this consists of only character type
SUBTYPE INTEGER IS NUMBER (40, 0);
Programming example:
DECLARE statement
SUBTYPE name IS char (20); //subtype is character data type
SUBTYPE message IS varchar1 (100); // subtype is variable type
Salutation name;
Greetings message;
BEGIN statement
Salutation: = ‘Reader_name ‘;
Greetings: = ‘welcome to the world of Oracle PL/SQL’;
Dbms_output.put_line (‘Hello’ II salutation II greetings);
END statement;
/
Output:
Hello reader welcome to the world of Oracle PL/SQL
PL/SQL procedure successfully completed.
In this section, we are going to discuss variables, variables are nothing but a name given to the storage area that our program manipulates. Each PL/SQL variable type consists of a data type that specifies the length, size, and layout of the memory value. The name of Oracle PL/SQL consists of letters followed by dollar signs, underscores, numerals, letters, and number signs.
Note: Oracle PL/SQL should not exceed more than 30 characters.
Syntax to declare a variable:
Variable_name [CONSTANT_VALUE] datatype [NOT NULL VALUE] [: = I DEFAULT initial value]
Where variable name is a valid identifier used in PL/SQL, data type represents the type of data that is used to store data values.
Example:
Sales number (10, 2);
PI CONSTANT double precision: = 3.14; //pi value
Name varchar1 (20);
Address varchar1 (100);
Initializing a variable:
Here there are two ways you can initialize the variable:
1. The default keyword
2. The assignment operator
Example:
Counter binary_integer: = 0;
Greetings varchar2 (10) default ‘have a great year’;
Here there are two types of variables:
1. Local variables: these local variables are declared in the inner block of the program and not accessible to any outer blocks.
2. Global variables: here variables are declared in the outermost package or block.
An operator is a symbol, which is used to perform specific mathematical or logical operations. The Oracle PL/SQL contains built-in operators and supports the following important operators:
1. Arithmetic operators
2. Logical operators
3. String operators
4. Relational operators
5. Comparison operators
Let me explain them one by one:
1. Arithmetic operators: the following are a few examples for arithmetic operators,
+ = used to add two operands
_ = used to subtract operands
*= used to multiply any two operands
/ = divides numerators by de numerator value
**= this is an exponential operator and used to add powers
2. Relational operators: A relational operator is used to compare two expressions or values and returns the Boolean result.
For example:
=: checks if any two operands are equal or not, if yes then the condition will become true.
!=: checks if the values of any two operands are equal or not, if the given values are not equal, then the condition becomes true.
>: checks if the value of the left operand is greater than the value of the right operand, if yes, the condition will become true.
3. Comparison operator:
Comparison operators are used to comparing one expression to another. The examples are TRUE, FALSE, or NULL.
The comparison operators are LIKE, BETWEEN, IN, IS NULL.
4. Logical operators:
Following are the logical operators supported by Oracle PL/SQL. All these operators work on the Boolean operand and produce the Boolean results.
Example: AND, OR, NOT.
5. String operators:
Following are the string operators like fixed-length string, variable-length strings, and Character large objects.
The conditional statements are also known as decision-making statements and used to specify one or more conditional statements in the program and also helps to test them properly.
The below diagram explains the conditional statement:
Now we are going to explain the different conditional statements:
1. IF-THEN statement:
The IF statement composes the sequence of statements which are enclosed by the keywords like THEN and END IF. If the condition is true, the program will get executed, and if the condition is FALSE, the IF statement will terminate the program.
2. IF-THEN-ELSE statement:
this IF statement adds the keyword like ELSE by adding an alternative sequence to a statement. IF the condition is FALSE or reaches NULL, then these alternative sequence statements will be executed.
3. IF-THEN-ELSEIF statement:
This statement allows users to choose between several alternatives.
4. CASE statement:
This statement is like an IF conditional statement, the CASE statement selects one of the statements to be executed.
5. Searched CASE statement:
This searched CASE statement contains no selector, and WHEN clauses contain any search condition that produces Boolean values.
6. Nested IF-THEN-ELSE:
Here you can use one IF-THEN or IF-THEN-ELSEIF statement inside another IF-THEN or IF-THEN-ELSEIF statement.
In general, most programming languages offer various control structures and allows us to execute complicated paths in any program. The main purpose of using the loop statement allows us to execute a group of statements multiple times and supports different types of loop statements.
The following figure explains the loop structure:
1. PL/SQL BASIC LOOP statement: in this type of loop structure, the sequence of the conditional statement is enclosed between the LOOP and the END loop. At each loop iteration, the sequences of control statements are executed and then resume the loop top statement.
2. PL/SQL WHILE LOOP statement: this loop statement repeats the control statements and group of statements while executing the program if the given condition is true. This WHILE loop conditional statement tests the condition before executing the loop body.
3. PL/SQL FOR LOOP: this executes any statement multiple times and also abbreviates the codes that manage loop variables.
4. Nested Loops in PL/SQL: here you can make use of one or more loops inside any other loop, while and for loop conditional statements.
For example:
DECLARE statement
i number (1);
j number (1);
BEGIN
>
FOR i IN 1…..5 LOOP
>
FOR j IN 1…5 LOOP
Dbms_output.put_line (‘i is: ‘II i II’ and j is: ‘II j);
END loop inner_loop;
END loop outer_loop;
END;
/
The LOOP control statement changes the execution from its normal loop sequence. The Oracle PL/SQL supports the following loop control statements.
1. EXIT statement: This EXIT control statement completes both the loop and control conditional that is passes to the statements once after the END LOOP.
2. CONTINUE statement: this loop control statement causes the loop to skip the body and immediately resets the conditions.
4. GOTO statement: this control statement transfers the loop controls to the labeled statement. In general, it is advised not to use any GOTO statement.
Learn Business And Creative Skills. Get Started Today! Enroll to Learn Oracle Project Accounting Training.
Arrays are nothing but a special kind of data structure which is also known as VARRAY. As we already know that arrays are used to store the data values or elements. All VARRYAS on PL/SQL consists of contiguous memory locations. The below diagram illustrates the PL/SQL arrays:
Each data element in a varray holds an index and this also has a maximum size that can be modified dynamically.
The basic syntax to create Varray type:
CREATE OR REPLACE TYPE varray_type_name IS VARRAY (n) of
Where:
1. Varray_type_name is the valid attribute name.
2. “n” is the number of elements used in the varray.
3. element_type is the data type of the given elements in an array.
Oracle PL/SQL- Functions:
The Oracle PL/SQL function is nothing but some kind of actions can be performed while executing the program. A function is also the same as a procedure but it doesn’t return values.
Creating a Function in PL/SQL:
Here only the standalone functions can be created using the CREATE FUNCTION. The basic syntax is as follows:
CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN I OUT I IN OUT] type [,……..])]
RETURN return_datatype
{IS I AS}
BEGIN statement
END statement [FUNCTION_name];
Where:
1.function_name defines the name of the function.
2. [OR REPLACE] option allows you to modify an already existing function.
3. The optional parameter contains the list of names, modes, and types of the parameters. Where IN represents the value that will be called outside and OUT represents the parameter that is used to return any value outside of the function.
4. Every function must contain the return statement.
5. The RETURN clause defines the type of data that will be used to return from the function.
6. Function_body holds the executable part of the control statements.
7. Here the AS keyword is used instead of the IS keyword to create any standalone function.
Programming example:
CREATE OR REPLACE FUNCTION totalcustomers
RETURN number IS
Total number (3) := 0;
BEGIN
SELECT count (*) into total
FROM customers;
RETURN total1;
END;
/
A cursor is nothing but a pointer to the context area. The Oracle PL/SQL controls the Context area using cursors. A cursor contains the rows which are returned by a SQL statement. The set of rows that the cursor holds are known as the active set. There are two types of cursors available:
1. Implicit cursors
2. Explicit cursors
Let me explain them one by one:
1. Implicit cursors:
Implicit cursors are automatically created by Oracle development, whenever SQL statements are executed when there is no explicit cursors exist in the statement. One more important point to remember, programmers cannot control the implicit cursors and the information. Whenever any DML statements like INSERT, DELETE, and UPDATE is issued, and an implicit cursor is associated with the statement.
2. Explicit cursors:
These explicit cursors are programmer-defined cursor types to gain more control over the Context area. Here the explicit cursors are defined in the declaration section of the Oracle PL/SQL block. This is created on a SELECT statement and returns more than one row.
The syntax to create explicit cursors:
CURSOR cursor_name IS select_statement;
The following are the important cursor type:
1. % FOUND: this returns TRUE, if an INSERT, UPDATE, or DELETE statement affects one or more rows.
2. %NOTOUND: this is the opposite of %FOUND, and this returns the TRUE if an INSERT, UPDATE, or DELETE statement affects no rows in function.
3. %ISOPEN: this cursor type always returns the FALSE for any implicit cursors, this is because the ORACLE closes the SQL cursor automatically after once it executes the SQL statement.
4. %ROWCOUNT: this cursor type returns the number of rows that are affected by INSERT, DELETE, or UPDATE statement.
Conclusion :
From this article, you people may get an idea about what are the important concepts that will come under the Oracle PL/SQL technology. This is an Oracle product used to manage a Relational database management system and query language statements. I hope this article may help a few of you to learn and gain in-depth knowledge of this technology.
As a senior Technical Content Writer for HKR Trainings, Gayathri has a good comprehension of the present technical innovations, which incorporates perspectives like Business Intelligence and Analytics. She conveys advanced technical ideas precisely and vividly, as conceivable to the target group, guaranteeing that the content is available to clients. She writes qualitative content in the field of Data Warehousing & ETL, Big Data Analytics, and ERP Tools. Connect me on LinkedIn.
Batch starts on 23rd Mar 2024 |
|
||
Batch starts on 27th Mar 2024 |
|
||
Batch starts on 31st Mar 2024 |
|