Trending December 2023 # Oracle Pl/Sql Object Types Tutorial With Examples # Suggested January 2024 # Top 12 Popular

You are reading the article Oracle Pl/Sql Object Types Tutorial With Examples updated in December 2023 on the website Hatcungthantuong.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested January 2024 Oracle Pl/Sql Object Types Tutorial With Examples

What is Object Type in PL/SQL?

Object-Oriented Programming is especially suited for building reusable components and complex applications. They are organized around “objects” rather than “actions” i.e. the programs are designed to work and interact with the entire object rather than the single action. This concept allows the programmer to populate and manipulate the details at object entities level.

Below picture depicts an example of the object type in which a bank account is considered an object entity. The object attributes include things which are holding some attribute values, for example in Bank Account; it is Account number, bank balance, etc. while object methods describe the things like calculating interest rate, generating bank statement, etc. which requires certain process to be completed.

In PL/SQL object-oriented programming is based on object types.

An object type can represent any real-world entity. We are going to discuss more object types in this chapter.

In this tutorial – you will learn,

Components of Object Types

PL/SQL object type contains mainly two components.

Attributes

Members/Methods

Attributes

Attributes are the column or field in which data are stored. Each attribute will be mapped to the datatype that defines the processing and storage type for that attribute. The attribute can be of any valid PL/SQL datatype, or it can be of another object type.

Members/Methods

Members or Methods are subprograms that is defined in the object type. They are not used to store any data. They are mainly used to define process inside the object type. For examples validating data before populating the object type. They are declared in the object type section and defined in the object type body section of the object type. Body section in object type is an optional part. If no members are present, then an object type will contain no body part.

Create Object in Oracle

An Object type cannot be created at subprogram level, They can be created only at the schema level. Once the object type is defined in the schema, then the same can be used in subprograms. The object type can be created using ‘CREATE TYPE’. The type body can be created only after creating its object type.

( . . ); / ( IS BEGIN END;‭ . . ‬ ); /

Syntax Explanation:

The above syntax shows the creation of ‘OBJECT’ with attributes and ‘OBJECT-BODY’ with methods.

The methods can also be overloaded in the object body.

Declaration Initialization of Object Type

Like other components in PL/SQL, object types are also needed to be declared before using them in the program.

Once the object type is created it can be used in subprogram declarative section to declare a variable of that object type.

Whenever any variable is declared in the subprogram as object type, at run-time a new instance of the object type will be created, and this newly created instance can be referred to the variable name. By this way, a single object type can store multiple values under different instances.

DECLARE BEGIN . . END; /

Syntax Explanation:

The above syntax shows the declaration of a variable as an object type in the declarative section.

Once the variable is declared as an object type in a subprogram, it will be atomically null i.e. the entire object itself a null. It needs to be initialized with values to use them in the program. They can be initialized using constructors.

Constructors are the implicit method of an object that can be referred with the same name as that of the object type. The below syntax shows the initialization of the object type.

DECLARE BEGIN END; /

Syntax Explanation:

The above syntax shows the initialization of the object type instance with a null value.

Now the object itself is not null as it has been initialized, but the attributes inside the object will be null as we have not assigned any values to these attributes.

Constructors

Constructors are the implicit method of an object that can be referred with the same name as that of the object type. Whenever the object is referred for the first time, this constructor will be called implicitly.

We can also initialize the objects using these constructor. The constructor can be defined explicitly by defining the member in object type body with the same name of the object type.

Example 1: In the following example we are going to use the object type member to insert the record into emp table with values (‘RRR’, 1005, 20000, 1000) and (‘PPP’, 1006, 20000, 1001). Once the data is inserted, we are going to display the same using object type member. We are also going to use the explicit constructor to populate the manager id by default with 1001 value for the second record.

We are going to execute it in below steps.

Step1:

Create Object type

Object type body

Step2: Creating an anonymous block to call created object type through implicit constructor for emp_no 1005.

Step3: Creating an anonymous block to call created object type through explicit constructor for emp_no 1006.

Step 1) Create Object type and Object type body

CREATE TYPE emp_object AS OBJECT( emp_no NUMBER, emp_name VARCHAR2(50), salary NUMBER, manager NUMBER, CONSTRUCTOR FUNCTION emp_object(p_emp_no NUMBER, p_emp_name VARCHAR2, p_salary NUMBER) RETURN SELF AS RESULT), MEMBER PROCEDURE insert_records, MEMBER PROCEDURE display_records); /

CREATE OR REPLACE TYPE BODY emp_object AS CONSTRUCTOR FUNCTION emp_object(p_emp_no NUMBER,p_emp_name VARCHAR2, p_salary NUMBER) RETURN SELF AS RESULT IS BEGIN Dbms_output.put_line(’Constructor fired..'); SELF.emp_name:=p_emp_name; SELF.salary:=p_salary; SELF.managerial:=1001; RETURN; END: MEMBER PROCEDURE insert_records IS BEGIN INSERT INTO emp VALUES(emp_noemp_name,salary,manager); END MEMBER PROCEDURE display_records IS BEGIN END: END: /

Code Explanation

Code line 1-9: Creating the ’emp_object’ object type with 4 attributes and 3 members. It contains the definition of constructors with only 3 parameters. (Actual implicit constructor will contain the number of parameters equal to the number of attributes present in the object type)

Code line 10: Creating the type body.

Code line 11-21: Defining the explicit constructor. Assigning the parameter value to the attributes and assigning the value for attribute ‘manager’ with the default value ‘1001’.

Code line 22-26: Defining the member ‘insert_records’ in which the attributes values are inserted into ’emp’ table.

Code line 27-34: Defining the member ‘display_records’ in which displaying the values of the object type attributes.

Output

Type created

Type body created

Step 2) Creating anonymous block to call created object type through implicit constructor for emp_no 1005

DECLARE guru_emp_det emp_object; BEGIN guru_emp_det:=emp_object(1005,’RRR',20000,1000); guru_emp_det.display_records; guru_emp_det.insert_records; COMMIT; END;

Code Explanation

Code line 37-45: Inserting the records using the implicit constructor. Call to constructor contains the actual number of attributes values.

Code line 38: Declares the guru_emp_det as object type of ’emp_object’.

Code line 41: Statement ‘guru_emp_det.display_records’ called the ‘diplay_records’ member function and the attributes values are displayed

Code line 42: Statement ‘guru_emp_det.insert_records’ called the ‘insert_records’ member function and the attributes values are inserted into the table.

Output

Employee Name: RRR

Employee Number: 1005

Salary: 20000

Manager : 1000

Step 3) Creating anonymous block to call created object type through explicit constructor for emp_no 1006

DECLARE guru_emp_det emp_object; BEGIN guru_emp_det:=emp_object(1006,'PPP',20000); guru_emp_det.display_records; guru_emp_det.insert_records; COMMIT; END; /

Output

Employee Name:PPP Employee Number:1006 Salary:20000 Manager:1001

Code Explanation:

Code line 46-53: Inserting the records using the explicit constructor.

Code line 46: Declares the guru_emp_det as object type of ’emp_object’.

Code line 50: Statement ‘guru_emp_det.display_records’ called the ‘display_records’ member function and the attributes values are displayed

Code line 51: Statement ‘guru_emp_det.insert_records’ called the ‘insert_records’ member function and the attributes values are inserted into the table.

Inheritance in Object Type

Inheritance property allows the sub-object type to access all the attribute and members of the super object type or parent object type.

The sub-object type is called inherited object type, and the super object type is called parent object type. The below syntax shows the how to create parent and inherited object type.

( . . )NOT FINAL; /

Syntax Explanation:

The above syntax show the creation of SUPER type.

( . ); /

Syntax Explanation:

The above syntax shows creation of SUB type. It contains all the members and attributes from the parent object type.

Example1: In the below example, we are going to use the inheritance property to insert the record with manager id as ‘1002’ for the following record (‘RRR’, 1007, 20000).

We are going to execute the above program in the following steps

Step1: Create SUPER type.

Step2: Create SUB type and body.

Step3: Creating an anonymous block to call the SUB type.

Step 1) Create SUPER type or Parent type.

CREATE TYPE emp_object AS OBJECT( emp_no NUMBER, emp_name VARCHAR2(50), salary NUMBER, manager NUMBER, CONSTRUCTOR FUNCTION emp_object(p_emp_no NUMBER,p_emp_name VARCHAR2(50), p_salary NUMBER)RETURN SELF AS RESULT), MEMBER PROCEDURE insert_records, MEMBER PROCEDURE display_records)NOT FINAL; /

Code Explanation:

Code line 1-9: Creating the ’emp_object’ object type with 4 attributes and 3 members. It contains the definition of constructors with only 3 parameters. It has been declared as ‘NOT FINAL’ so it is parent type.

Step 2) Create SUB type under SUPER type.

CREATE OR REPLACE TYPE sub_emp_object UNDER emp_object (default_manager NUMBER,MEMBER PROCEDURE insert_default_mgr); / CREATE OR REPLACE TYPE BODY sub_emp_object AS MEMBER PROCEDURE insert_default_mgr IS BEGIN INSERT INTO emp VALUES(emp_no,emp_name:salary,manager): END; END; /

Code Explanation:

Code line 10-13: Creating the sub_emp_object as inherited type with additional one attribute ‘default_manager’ and member procedure declaration.

Code line 14: Creating the body for the inherited object type.

Code line 16-21: Defining the member procedure which is inserting the records into “emp” table with the values from ‘SUPER’ object type, except for manager value. For manager value, it is using the ‘default_manager’ from ‘SUB’ type.

Step 3) Creating anonymous block to call the SUB type

DECLARE guru_emp_det sub_emp_object; BEGIN guru_emp_det:= sub_emp_object(1007,'RRR',20000,1000,1002); guru_emp_det.insert_default_mgr; COMMIT; END; /

Code Explanation:

Code line 25: Declaring ‘guru_emp_det’ as ‘sub_emp_object’ type.

Code line 27: Initializing the object with the implicit constructor. The constructor is having 5 parameters (4 attributes from PARENT type and 2 attributes from SUB type). The last parameter (1002)defines the value for default_manager attribute

Code line 28: Calling the member ‘insert_default_mgr’ to insert the records with the default manager id passed in the constructor.

Equality of PL/SQL Objects

The object instance that belongs to the same objects can be compared for equality. For this, we need to have the special method in the object type called ‘ORDER’ method.

This ‘ORDER’ method should be the function that returns numerical type. It takes two parameters as input, (first parameter: id of the self-object instance, second parameter: id of another object instance).

The id of the two object instance is compared, and the result is returned in numerical.

Positive value represents that the SELF object instance is greater than another instance.

Negative value represents that the SELF object instance is lesser than another instance.

Zero represents that the SELF object instance is equal to another instance.

If any of the instances is null, then this function will return null.

( RETURN INTEGER IS BEGIN RETURN -1; --any negative number will do RETURN 1; —any positive number will do ELSE RETURN 0; END IF; END; . . ); /

Syntax Explanation:

The above syntax shows the ORDER function that needs to be included in the type body for equality check.

The parameter for this function should be an instance of the same object type.

The above function can be called as “obj_instance_1.match(obj_instance_2)” and this expression will return the numerical value as shown, where obj_instance_1 and obj_instance_2 are the instance of object_type_name.

Example1: In the following example we are going to see how to compare two objects. We are going to create two instances and we are going to compare attribute ‘salary’ between them. We are going to do int two steps.

Step 1: Creating the Object type and body.

Step 2: Creating the anonymous block to call compare the object instance.

Step 1) Creating the Object type and body.

CREATE TYPE emp_object_equality AS OBJECT( salary NUMBER, ORDER MEMBER FUNCTION equals(c emp_object_equality)RETURN INTEGER); / CREATE TYPE BODY emp_object_equality AS ORDER MEMBER FUNCTION equals(c emp_object_equality)RETURN INTEGER IS BEGIN‭ ‬ IF salary<c.salary THEN RETURN -1; THEN RETURN 1; ELSE RETURN 0; END IF:‭ ‬ END; END; /

Code Explanation:

Code line 1-4: Creating the ’emp_object_equality’ object type with 1 attributes and 1 member.

Code line 6-16: Defining the ORDER function which compares the ‘salary’ attribute of SELF instance and parameter instance type. It returns negative if SELF salary is lesser or positive if SELF salary is greater and 0 if salaries are equal.

Code Output:

Type created

Step 2) Creating the anonymous block to call compare the object instance.

DECLARE l_obj_l emp_object_equality; l_obj_2 emp_object_equality; BEGIN l_obj_l:=emp_object_equality(15000); l_obj_2:=emp_object_equality(17000); THEN Dbms_output.put_line(’Salary of first instance is greater’): ELSIF l_obj_l.equalS(l_obj_2)<0 THEN Dbms_output.put_line(’Salary of second instance is greater’); ELSE Dbms_output.put_line(’Salaries are equal’); END IF; END; /

Output

Salary of second instance is greater

Code Explanation:

Code line 20: Declaring the l_obj_1 of emp_object_equality type.

Code line 21: Declaring the l_obj_2 of emp_object_equality type.

Code line 23: Initializing l_obj_1 with salary value as ‘15000’

Code line 24: Initializing l_obj_1 with salary value as ‘17000’

Code line 25-33: Print the message based on the return number from the ORDER function.

Summary

In this chapter, we have seen the object type and their properties. We have also discussed about Constructors, Members, Attributes, Inheritance and Equality in PL/SQL objects.

You're reading Oracle Pl/Sql Object Types Tutorial With Examples

Types Of Xml Parsers With Examples

Definition of XML Parsers How does XML Parsers Work?

Fig: XML parser Process

The Parser could be categorized as validating and non- validating

Validating Parser: It needs a Document type Declaration to parse and gives an error if the respective document doesn’t match with DTD and constraints.

Non-Validating: This Parser eliminates DTD and the parser checks for the well-formed document.

Types of XML Parsers with Examples

This section talks about various types of parsers used recently in parsing XML document. They are:

DOM Parser

SAX Parser

JDOM Parser

stAX Parser

Xpath Parser

The most important type is DOM and SAX which is explained detail in this article.

1. DOM Parser (Tree-Based)

Document Object Model is a W3C Standard and converts the XML document which is to parsed into a collection of objects and uses DOM API. It consists of a collection of nodes and is associated with other nodes in the tree. DOM is much easier to use as sorting and searching process is made chúng tôi DOM parser the content of the XML file is modified with Node and Node List. The Steps involved in Parsing with java:

Getting document builder objects

Taking XML document as input, parseit and return the class.

Getting values of the input id through attributes and sub-elements.

Display the results.

First is the XML file that generates the values which are going to be parse and java objects are constructed automatically.

Example

chúng tôi

Read.java

}

And here is the Output is shown for the XML File. Save XML and java file in the same folder during execution. In this article I have used java-jdk- 13.0.1 using command prompt. Save the respective folder in any drive and do the set path.

Output:

2. SAX Parser

SAX Is Simple API for XML and meant has Push Parseralso considered to be stream-oriented XML Parser. it is used in case of high- performance applications like where the XML file is too largeand comes with the community- based standard and requires less memory. The main task is to read the XML file and creates an event to do call functionor uses call back routines. The working of this parser is just like Event handler part of the java. it is necessary to register the handlers to parse the document for handing different events. SAX Parser uses three methods startElement() , endElement() , characters().

startElement(): Is used to identify the element, start element is identified.

endElement(): To stop the Supermarket tag in the example.

character(): Is used to identify the character in the node

The xml file is the same file used in DOM Parser chúng tôi and next step generate chúng tôi file

Example

Rsax.java

}

Output:

Conclusion

Therefore, we have discovered how to use XML parsers in Java with the powerful APIs in the applications.Also, we have seen the implementation of two parsers using java. When compared with DOM,sax parser uses arbitrary size to parse whereas DOM requires available memory to load the complete chúng tôi Parsers differs based on the performance.

Definition, Types, Precedence And Examples

What are Operators in C?

The C programming language utilizes operators as symbols representing precise operations to be executed on one or more operands. C provides a wide range of operators that can perform arithmetic, logical, and bitwise operations and operations on pointers and arrays. Operators are symbols that help in performing functions of mathematical and logical nature. The classification of C operators is as follows:

Arithmetic

Relational

Logical

Bitwise

Assignment

Conditional

Special

Even though there are many operators, the execution of these operations happens based on the precedence given to them. Precedence is the order in which the compiler executes the code comprising numerous operators.

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

Explanation of Operators in C

Below is a detailed explanation of operators in C:

#1 Arithmetic Operators

These operators are responsible for performing arithmetic or mathematical operations like addition (+), subtraction (-), multiplication (*), division (/), the remainder of the division (%), increment (++), and decrement (–).

There are two types of arithmetic operators:

Unary Operators: This type of operator works with a single value (operand) like ++ and –.

Binary Operators: This type of operator works with two operands like +,-,*,/

Here is a tabular form of the number of arithmetic operators in C with the functions they perform.

Operator Function

+ Adds two values

– Subtract a second value from the first.

* Multiply two values

/ Divide numerator by the denominator

% Remainder of division

++ Increment operator – increases integer value by one.

— Decrement operator – decreases integer value by one

int main() { int a = 12, b = 6, c; c = a + b; printf(“a+b = %d n”, c); c = a – b; printf(“a-b = %d n”, c); c = a *b; printf(“a*b = %d n”, c); c = a / b; printf(“a/b = %d n”, c); c = a % b; printf(“Remainder when a divided by b = %d n”, c); return 0; }

Output:

#2 Relational Operators

The below table lists the relational operators in C with their functions.

Operator Function Example

== It will check if the two operands are equal 6 == 2 returns 0

!= It will check if the two operands are not equal. 6 != 2 returns 1

> It will check if the operand on the left is greater than the operand on the right

< It will check if the operand on the left is smaller than the right operand 6 < 2 returns 0

>= It will check if the left operand is greater than or equal to the right operand

<= It will check if the operand on the left is smaller than or equal to the right operand 6 <= 2 return 0

Example: C Program using relational operators

int main() { int a = 7, b = 7, c = 10; printf(“%d == %d = %d n”, a, b, a == b); printf(“%d == %d = %d n”, a, c, a == c); printf(“%d < %d = %d n”, a, b, a < b); printf(“%d < %d = %d n”, a, c, a < c); printf(“%d != %d = %d n”, a, b, a != b); printf(“%d != %d = %d n”, a, c, a != c); printf(“%d <= %d = %d n”, a, b, a <= b); printf(“%d <= %d = %d n”, a, c, a <= c); return 0; }

Output:

#3 Logical Operators

Logical Operators are to get True or False results.

The table below lists the logical operators used in C

Operator Function Example (if a=1 and b=0)

&& Logical AND (a && b) is false

|| Logical OR

! Logical NOT (!a) is false

Example: C Program using logical operators.

int main() { int a = 8, b = 8, c = 12, result; result = (a == b) && (c < b); printf(“(a == b) && (c < b) equals to %d n”, result); result = !(a != b); printf(“!(a == b) equals to %d n”, result); result = !(a == b); printf(“!(a == b) equals to %d n”, result); return 0; }

Output:

#4 Bitwise Operators

These operators are for bit-level operations on the operands. The operators first convert into bit-level and then perform the calculations.

Operator Function

& Bitwise AND

| Bitwise OR

^ Bitwise exclusive OR

~ Bitwise complement

<< Shift left

>> Shift right

Example: C program for Bitwise AND

int main() { int a = 10, b = 8; printf(“Output = %d”, a&b); return 0; }

Output:

Explanation:

00001010 & 00001000 = 00001000 = 8 (In decimal)

#5 Assignment Operators

These types of operators help us assign a value to a variable.

Operator Function Example

= It will assign values from right-side operands to left-side operands a=b

+= It will add the right operand to the left operand and assign the result to left a+=b is the same as a=a+b

-= It will subtract the right operand from the left operand and assign the result to the left operand a-=b is the same as a=a-b

*= It will multiply the left operand with the right operand and assign the result to the left operand a*=b is the same as a=a*b

/= It will divide the left operand with the right operand and assign the result to the left operand a/=b is the same as a=a/b

%= It will calculate the modulus using two operands and assign the result to the left operand a%=b is the same as a=a%b

#6 Conditional Operators

Also, known as Ternary Operator or? : Operator, these operators are useful for decision-making.

Syntax:

Expression 1? Expression 2: Expression 3 #7 Special Operators

Here are some special operators used in C

Operator Function

& This operator is used to get the address of the variable.

Example: &a will give an address of a.

* This operator works as a pointer to a variable.

Example: * a where * is a pointer to the variable a.

size of () This operator gives the size of the variable.

Example: The size of (char) will give us 1.

Example: C program using a special operator

int main() { int *ptr, q; q = 40; /* It assigns the address of q to ptr */ ptr = &q; /* display q’s value using ptr variable */ printf(“%d”, *ptr); return 0; }

Output:

C Operators Precedence

Generally, arithmetic, logical, and relational operators are used while coding in C. The precedence for these operators in arithmetic is greater than logical and relational. Note that all the operators in arithmetic also follow a different order of precedence. Let’s check which operators hold the highest precedence.

Order of Precedence in Arithmetic Operators

The increment and decrement (+ + and – -) operators hold the highest precedence in arithmetic operators. After that next precedence is for the unary minus ( – ) operator; next, three operators, /, *, and %, have equal precedence. The lowest precedence is for the operators like addition ( + ) and subtraction ( – ). In case of equal priority, the compiler takes charge while evaluating them. Remember the C operator associativity rule for all operators with the same precedence. Then the execution happens from left to right.

For example,

int main() { int a = 15, b = 20, c = 32, result; result = a * b – ++c; printf(“The result is: %d”, result); return 0; }

Output:

Explanation: Here, in the given equation, first, “++” executes; hence the value of c will be 33. Next, “* “holds the highest precedence after “++.” Hence after the execution of “a * b,” the result will be 300. Then the execution of “-” happens and results in 267.

Order of Precedence in Relational/Logical Operators

For example,

Output: False

Misc Operators in C

The Misc operators or miscellaneous operators are conditional operators that include three operands. In these 3, the execution of the first operand happens first. Then the execution of the second operand, if it is non-zero, or a third operand executes to provide the necessary Output. Besides the operators discussed above, C programming language supports a few other special operators like sizeof and “?:”.

Operator Description Example

sizeof() Finds the size of a variable sizeof(b), if b is an integer, then the Output will be 4.

?: Conditional operator Condition? X: Y; here, if the condition is true, the result will be X, else Y.

& Address of a variable &a returns the actual address

* Pointer *a

Time and Space Complexity

Time and space complexity are the terms concerning the execution of an algorithm. The Time complexity is the time taken to run the algorithm as a function of the input. Space complexity is the space or memory the algorithm takes as an input function. These two terms depend on many terms like processor, operating system, etc.

Final Thoughts

C operators are the symbols used to perform relational, mathematical, bitwise, or logical operations. C language includes a lot of operators to perform various tasks as necessary in the program. Different kinds of operators are arithmetic, logical, and relational.

Frequently Asked Questions (FAQS)

Q1. What are the boolean operators in C?

Q2. What does ** mean in C?

Answer: The “**” in C is a double-pointer or pointer-to-pointer. Where * is a pointer that holds the address of the variable. ** mean the address of a variable already holding an address of a different variable.

Q3. What is the difference between prefix and postfix operators in C?

Answer: Prefix and postfix are the operators written before and after the operands. These operators are the increment (+ +) and decrement (- -) operators. For example, “++c” is the prefix operator, and “c++” is the postfix operator.

Q4. What is the Modulus operator?

Answer: The modulus operator is the arithmetic operator of C, and it works between two operands. The division of the numerator value by the denominator results in the remainder. In simpler words, the produced rest for the integer division is the modulus operator.

Q5. Does C language support operator overloading?

Answer: Operator overloading is a method of polymorphism where the programmer makes specific changes in the existing code without changing its meaning. Operator overloading is possible only in C++. Since polymorphism is possible only in an object-oriented language, C doesn’t support operator overloading.

How Decode() Function Works In Sql? Examples

Introduction to SQL DECODE()

DECODE function in Standard Query Language (SQL) is used to add procedural IF – THEN – ELSE like statements to a query. It compares a given expression with each search value one by one and returns a result on the basis of outcomes received from the comparison. A decode function basically performs the task of CASE statements. However, we should keep in mind that DECODE is a built-in function in ORACLE SQL databases and hence it is supported only in ORACLE 9i and above versions of ORACLE/ PL SQL. It is not recognized and supported in other database management servers such as PostgreSQL, SQL Server, MySQL etc. So, now we can use CASE statements to perform IF-THEN-ELSE logic in these databases.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax and Parameters:

The basic syntax for writing DECODE function in SQL is as follows:

DECODE (expression , search_1, result_1[, search_2, result_2], ...,[,search_n,result_n] [, default]);

expression: expression argument is the value which is to be searched and compared with.

search_1, search_2, …. search_n: These are the values to be searched for and then compared with the expression argument.

result_1, result_2, … , result_n: These arguments hold the result to be returned when the given comparison returns true. For example, if expression = search_1 then result will be result_1.

default: default argument holds the default value. It is more or less like the ELSE statement in IF-THEN-ELSE.

We can use the DECODE function as a part of the SELECT statement, ORDER BY etc.

How DECODE() Function works in SQL?

The first step is comparison of expression and search_1, if the expression = search_1 is TRUE then result_1 is returned. If it’s FALSE then DEFAULT value is returned. The DECODE function automatically converts or casts the expression to the data type of the first search argument or search_1. And it finally converts back the data_type of result to the data_type of the expression.

The functionality of DECODE in ORACLE with following flowchart.

Example:

Code:

SELECT DECODE(1, 1, 'One') FROM dual;

Output:

The simple illustration of the above mentioned decode function is as follows:

Code:

IF 1 = 1 THEN result = 'One' ENDIF; Examples of SQL DECODE()

Given below are the examples mentioned:

Let us first create a ‘college_details’ table which contains college id, college name, location and fees for demonstration purposes.

We can use the following SQL CREATE TABLE statement to perform the task.

Code:

CREATE TABLE college_details( college_id integer NOT NULL, college_name character varying(255) NOT NULL, college_location character varying(255) NOT NULL, fees numeric NOT NULL );

Output:

Having created the table, let us now input some random data in it to work with in the subsequent exercises. We can use the following insert statements.

Code:

INSERT INTO college_details VALUES (10001, 'Indian Institute of Technology Roorkee', 'Roorkee,India', 10000); INSERT INTO college_details VALUES (10002, 'Indian Institute of Technology Bombay', 'Mumbai,India', 10000); INSERT INTO college_details VALUES (10004, 'California Institute of Technology', 'California ,USA', 60520); INSERT INTO college_details VALUES (10003, 'Massachusetts Institute of Technology', 'Massachusetts,India', 51520); select * from college_details;

The data in the “college_details” table after performing the above mentioned INSERT operations looks something as shown below:

Output:

Example #1

Simple SQL query to illustrate use of DECODE function.

Code:

SELECT college_id, DECODE (college_id, 10003,'Massachusetts, USA', 10004, 'California, USA', 'India') FROM college_details;

Output:

In this example, we have performed a simple SQL task for categorizing colleges based on their location.

Simple illustration of above mentioned DECODE function is as follows:

Code:

IF college_id = 10003 THEN result = 'Massachusetts' ELSE IF college_id = 10004 THEN result = 'California' ELSE result = 'India' ENDIF; Example #2

SQL query to illustrate abbreviation of college names based on the available data using DECODE function.

SELECT college_id, DECODE(college_name,'Massachusetts Institute of Technology', 'MIT','California Institute of Technology','CalTech','IIT') as college_name FROM college_details ORDER BY college_id;

Output:

In the above example, we have performed the following IF-THEN-ELSE logic statements and then ordered the entire result set by college_id.

Code:

IF college_name = 'Massachusetts Institute of Technology' THEN result = 'MIT' ELSE IF college_name = 'California Institute of Technology' THEN result = 'Caltech' ELSE result = 'IIT' ENDIF; Example #3

SQL query to categories college fees into affordable and expensive for an Indian student, considering everything above $ 10000 as expensive.

Code:

SELECT college_id,fees, DECODE(fees,10000,'Affordable','Expensive') FROM college_details ORDER BY college_id;

Output:

In the above example, we have performed the following task using the DECODE function and have then ordered the result set by college_id.

Code:

IF fees = '10000' THEN result = 'Affordable' ELSE result = 'Expensive' ENDIF; Conclusion

DECODE function is used to perform procedural IF-THEN-ELSE logic in SQL. The function is a close relative of CASE statements. It is a built-in function in ORACLE / PL SQL database management servers.

How To Check If Polyline Object Intersects With Another Object Using Fabricjs?

We can create a Polyline object by creating an instance of fabric.Polyline. A polyline object can be characterised by a set of connected straight-line segments. Since it is one of the basic elements of FabricJS, we can also easily customize it by applying properties like angle, opacity, etc.

In order to check if a Polyline object intersects with another object, we use the intersectsWithObject method. This method checks whether the object that is passed to it, intersects with the polyline object.

Syntax intersectsWithObject(other: Object, absolute: Boolean, calculate: Boolean ): Boolean Parameters

other − This parameter accepts an Object which specifies the object we want to test.

absolute(optional) − This parameter accepts a String value which specifies whether to use coordinates without viewportTransform or not. This parameter is optional.

calculate(optional) − This parameter accepts a Boolean value which specifies whether to use coordinates of current position. This parameter is optional.

Example 1: Using intersectsWithObject method

Let’s see a code example to see the logged output when the intersectsWithObject method is used. The intersectsWithObject method returns true or false on checking if the polyline object intersects with another object.

Here, we have initialized two rectangle objects namely rectangleRed and rectangleBlue. Since our polyline object intersects with rectangleRed, a true value is returned.

var canvas = new fabric.Canvas(“canvas”); canvas.setWidth(document.body.scrollWidth); canvas.setHeight(250);

var polyline = new fabric.Polyline( [ { x: -20, y: -35 }, { x: 20, y: -35 }, { x: 40, y: 0 }, { x: 20, y: 35 }, { x: -20, y: 35 }, { x: -40, y: 0 }, ], { stroke: “red”, left: 100, top: 10, fill: “white”, strokeWidth: 2, strokeLineJoin: “bevil”, } );

var rectangleRed = new fabric.Rect({ width: 60, height: 20, top: 40, left: 80, fill: “red”, strokeWidth: 6, });

var rectangleBlue = new fabric.Rect({ width: 20, height: 40, top: 70, left: 200, fill: “blue”, });

canvas.add(polyline); canvas.add(rectangleRed); canvas.add(rectangleBlue);

console.log( “Does the polyline object intersect with rectangleRed?: “, polyline.intersectsWithObject(rectangleRed) ); console.log( “Does the polyline object intersect with rectangleBlue?: “, polyline.intersectsWithObject(rectangleBlue) );

Example 2: Using intersectsWithObject method with different objects

In this example, we have used the intersectsWithObject method along with different objects to prove that this method can work with any object.

var canvas = new fabric.Canvas(“canvas”); canvas.setWidth(document.body.scrollWidth); canvas.setHeight(250);

var triangle = new fabric.Triangle({ width: 90, height: 70, top: 40, left: 80, fill: “red”, strokeWidth: 6, });

var circle = new fabric.Circle({ radius: 40, top: 70, left: 200, fill: “blue”, });

canvas.add(polyline); canvas.add(triangle); canvas.add(circle);

console.log( “Does the polyline object intersect with triangle?: “, polyline.intersectsWithObject(triangle) ); console.log( “Does the polyline object intersect with circle?: “, polyline.intersectsWithObject(circle) );

Top 10 Sql Interview Questions With Implementation

In today’s world, technology has increased tremendously, and many people are using the internet. This results in the generation of so much data daily. This generated data is stored in the database and will maintain it. SQL is a structured query language used to read and write these databases. In simple words, SQL is used to communicate with databases. SQL allows you to perform any database-related task. It is accessible and economical for the majority of organizations. If you plan to give an SQL interview then this article is a must read for you! Checkout the top SQL interview questions

This article was published as a part of the Data Science Blogathon.

Q1. What is common table expression in SQL?

A Common Table Expression (CTE) is a query’s result set that lives temporarily within the execution scope of a statement like SELECT, INSERT, UPDATE, DELETE, or MERGE. The output of a CTE is not kept and only exists while the query is being executed. Making complex queries more readable and simple makes it easier for users to create and maintain them.

The following demonstrates the typical SQL Server syntax for a CTE:

WITH expression_name[(columns [,...])] AS (CTE_definition) SQL_statement;

Use WITH clause to define a common table expression.

In the columns, specify the names of all the columns that need to be retrieved in the CTE

Define CTE after AS that retrieves a table with specified columns.

Finally, write an SQL query using a common table expression.

Let’s see an example to define a Common Table Expression as students_data with id, name, and roll_no columns. And then, a query to return the names of students that starts with the letter A among them.

WITH students_data[(id,name,roll_no)] AS ( SELECT id, name,roll_no FROM students ) SELECT name FROM students_data WHERE name LIKE 'A%'; Q2. How to replace null values with default values in MYSQL?

Sometimes, while using MySQL, you don’t want NULL values to be returned as NULL. But sometimes, you want NULL values to return a different default value. There are some ways in MYSQL to replace these null values with default values.

There are four ways to replace it-

Using IFNULL() function

Using

COALESCE() function

Combination of IF() function and IS NULL operator

Combination of CASE expression and IS NULL operator

Let’s see them one by one.

1. Using IFNULL() function:

The IFNULL() function takes two expressions and returns the first arguments if the first expression is not null. The second parameter is returned if the first expression returns null.

Let’s see the syntax.

IFNULL(expression, alternate_value) #Example SELECT IFNULL(Name,'N/A') FROM students 2. Using COALESCE() function:

The COALESCE() method returns the first non-null arguments from the given list of expressions. The function gives a null result if the expression is empty. Moreover, a specified default value can be used to replace null entries in a table.

Simply, it returns the first non-null argument in the given list of expressions. If there are no non-null values, then NULL is returned.

Let’s see some examples to understand.

SELECT COALESCE('one', 'two', 'three') AS result #result #one SELECT COALESCE(NULL, 'one', 'two', 'three') AS result #result #one SELECT COALESCE(NULL, NULL, 'two', 'three') AS result #result #two SELECT COALESCE('A', NULL, 'B', NULL) AS result #result #A SELECT COALESCE(NULL, NULL, 'P', NULL, 'Q') AS result #result #P SELECT COALESCE(NULL, NULL, NULL, NULL, NULL) AS result #result #NULL 3. Combination of IF() function and IS NULL operator:

We can also use IF() function and IS NULL operator to replace null values with default values. It works like if the value is null, then replace it with a default value; else, return the original expression. Let’s see how it works with some examples.

To replace null values with ‘N/A’ in the names column of a students_data table.

SELECT IF(names IS NULL, 'N/A', names ) AS result FROM students_data 4. Combination of CASE expression and IS NULL operator:

This is almost similar to the previous one. Here we use the CASE operator instead of the IF() function. So first, we will take cases where there is a null value, and then we will replace it with the given default value. Else the original expression will be returned. Let’s take an example to understand in detail.

SELECT CASE WHEN names IS NULL THEN 'N/A' ELSE names END FROM students_data

This code is for the same previous example. To replace ‘N/A’ in the names column when there are null entries.

Q3. What is the SQL Syntax for Auto Increment?

Syntax:

CREATE TABLE table_name ( column_name datatype AUTO_INCREMENT, );

For example,

CREATE TABLE students_data ( id INT AUTO_INCREMENT, name varchar, phone_number INT ); Q4. What are the Different Rank Functions in SQL?

There are four rank functions in SQL

RANK()

DENSE_RANK()

ROW_NUMBER()

NTILE()

Let’s see them one by one in detail.

1. RANK()

This function will return a number that will be applied to each row in the output partition. Each row receives a rank equal to one plus the rank of the row before it. The RANK function assigns the same rank number to two values that it discovers to be identical within the same partition. The following ranking number will also include duplicate numbers in addition to the preceding rank. As a result, this method does not always assign the ranking of rows in numerical order.

Let’s see the syntax

SELECT column_name RANK() OVER ( PARTITION BY expression ORDER BY expression) AS result FROM table_name; 2. DENSE_RANK()

This is almost similar to that of the rank function. Here also, each row receives rank, adding one to the previous rank. If two rows are identical, then they receive the same rank, and the next row directly receives plus one to the current rank. For example, if the 1st and 2nd rows are identical, then both receive rank 1, and the next third row receives rank 2 instead of rank 3, as in the case of using the RANK() function. That’s the difference.

Let’s see the syntax

SELECT column_name DENSE_RANK() OVER ( PARTITION BY expression ORDER BY expression) AS result FROM table_name; 3.ROW_NUMBER()

The row number function differs from the rank and dense rank functions. Starting from 1, this gives ranks adding 1 to the previous row. No matter if any two rows are identical.

let’s see the syntax

SELECT column_name ROW_NUMBER() OVER ( PARTITION BY expression ORDER BY expression) AS result FROM table_name; 4. NTILE()

The NTILE() function is the one you want to use when you want to distribute groups of rows over a partition evenly. You must tell this ranking function how many groups you want the rows to be equally divided into. According to the specified requirement, each row group receives its rank.

let’s see the syntax

SELECT column_name NTILE(N) OVER ( PARTITION BY expression ORDER BY expression) AS result FROM table_name; Q5. Explain Normalization and Denormalization in SQL.

Normalization removes redundancy from the database, which means it is split across multiple tables instead of just one table. and non-redundant, consistent data is added. An improperly constructed database table is inconsistent and could cause problems when executing operations. Hence database normalization is an important step. An unnormalized table is transformed into a normalized table through this process.

Denormalization is used to aggregate data from several tables into one to be easily queried. Redundancy is added using it. In contradiction to normalization, denormalization reduces the number of tables. Denormalization is used when joins are expensive, and table queries are run frequently. Wastage of memory is the main drawback of denormalization.

Q6. What is the Difference Between SQL and MySQL?

SQLMySQLStands for Structured Query LanguageStands for “My Structured Query Language”Language used for managing relational databasesOpen-source relational database management system (RDBMS)Not a specific database system, but a language implemented by various DBMSsA specific DBMS that utilizes SQL as its query languageProvides a set of commands for creating, modifying, and querying databasesOffers a software platform for creating and managing databasesSupports data storage, retrieval, and manipulation using SQLSupports data storage, retrieval, and manipulation using SQLImplemented by multiple DBMSs such as MySQL, Oracle, PostgreSQL, etc.Developed by MySQL AB, now owned by Oracle CorporationWidely used in various database systemsWidely used in web applications and compatible with multiple operating systemsCan be used with different DBMSs based on the specific implementationCan only be used with the MySQL database management system

Q6. What are the usages of SQL?

Creating and managing databases and their structures.

Inserting, updating, and deleting data within tables.

Querying and retrieving specific information from databases.

Filtering and sorting data based on specific criteria.

Aggregating and summarizing data using functions like SUM, COUNT, and AVG.

Joining multiple tables to combine data from different sources.

Creating views to present customized or filtered perspectives of data.

Implementing constraints to ensure data integrity and enforce rules.

Indexing columns to improve query performance and data retrieval.

Granting and managing user permissions to control access rights and data security.

Q7. What are the different subsets of SQL?

SQL encompasses several subsets or variations that are specific to different database management systems (DBMS) or have specialized purposes. Here are some notable subsets of SQL:

MySQL: SQL variant specific to the MySQL database management system.

PostgreSQL: SQL variant specific to the PostgreSQL database management system.

Oracle SQL: SQL variant specific to the Oracle Database system.

Microsoft T-SQL: SQL variant specific to Microsoft SQL Server, known as Transact-SQL.

SQLite: SQL variant specific to the lightweight, embedded database engine SQLite.

ANSI SQL: The American National Standards Institute (ANSI) SQL is a standardized version of SQL that sets the foundation for most SQL implementations. Different DBMSs may adhere to various versions of ANSI SQL, such as ANSI SQL-92, ANSI SQL:1999, ANSI SQL:2003, etc.

PL/SQL: A procedural extension to SQL used in Oracle Database for creating stored procedures, functions, and triggers.

NoSQL: Although not SQL in the traditional sense, NoSQL databases (e.g., MongoDB, Cassandra, Couchbase) represent a different approach to database management, often focusing on high scalability, schema flexibility, and distributed architectures, and they utilize their query languages that are different from traditional SQL.

Q8. What are some common clauses used with SELECT query in SQL?

When using the SELECT query in SQL, there are several common clauses that can be used to refine and customize the query results. Here are some frequently used clauses:

SELECT: Specifies the columns to be retrieved from the database table(s).

FROM: Identifies the table(s) from which to retrieve the data.

WHERE: Filters the rows based on specified conditions, allowing for data retrieval based on specific criteria.

DISTINCT: Removes duplicate values from the result set, returning only unique values.

ORDER BY: Sorts the result set in ascending (default) or descending order based on one or more columns.

GROUP BY: Groups the result set by one or more columns, often used in conjunction with aggregate functions.

HAVING: Filters the grouped rows based on specified conditions, similar to the WHERE clause but applied after the GROUP BY clause.

LIMIT: Specifies the maximum number of rows to be retrieved from the result set.

OFFSET: Specifies the number of rows to skip from the beginning of the result set before starting to return rows.

JOIN: Combines data from multiple tables based on related columns, allowing for retrieval of data from multiple sources.

UNION: Combines the result sets of two or more SELECT statements into a single result set.

IN: Tests whether a value matches any value in a specified list.

NOT IN: Tests whether a value does not match any value in a specified list.

LIKE: Performs pattern matching to retrieve rows based on specified patterns using wildcard characters.

BETWEEN: Retrieves rows with values within a specified range.

Q9. What is a view in SQL?

In SQL, a view is a virtual table derived from a query’s result. It allows you to encapsulate complex queries into a named, reusable object. A view can be used just like a regular table, enabling you to query its data or perform other operations on it.

Here’s the syntax for creating a view in SQL:

CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;

Let’s break down the syntax:

CREATE VIEW view_name: This statement is used to create a view with a specific name, referred to as view_name. You can choose any suitable name for your view.

AS: This keyword indicates that the view definition is starting.

SELECT column1, column2, ...: Here, you specify the columns you want to include in the view. You can select specific columns or use * to select all columns.

FROM table_name: Specifies the table from which you want to retrieve data for the view. You can include joins or subqueries to define more complex queries.

WHERE condition: This part is optional and allows you to include a condition to filter the rows in the view based on specific criteria.

Q10. What is an Index in SQL?

In SQL, an index is a database object that improves the speed of data retrieval operations on database tables. It works like a table of contents, organizing and storing a sorted copy of selected columns from a table. By creating an index on one or more columns, the database engine can locate and retrieve data more efficiently, reducing the need for full-table scans. Indexes enable faster searching, sorting, and joining of data, resulting in improved query performance. However, indexes incur overhead during data modifications (insert, update, and delete operations) as they need to be updated to reflect the changes. Therefore, choose indexes carefully and balance them to optimize database performance.

Conclusion

Social media app users frequently share photographs and posts, which involves databases that can update information and simultaneously display content to millions of users. There are many tables in the database, and each table contains rows of data. SQL helps to maintain these databases. We learned some important topics in SQL in this article.

The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.

Update the detailed information about Oracle Pl/Sql Object Types Tutorial With Examples on the Hatcungthantuong.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!