Posts

Showing posts with the label SQL

Featured post

How to decode Tag-Length-Value Quickly

Basically, in TLV, the format is Tag, Length, and Value. In the list of protocols, TLV is one of the types. Transmitting the data depends on the protocol you used. However, many financial transactions still follow the TLV format. According to IBM, TLV data is three parts. The tag tells what type of data it is. The length field has a length of the value. The Value field has actual value.

Structure of TLV.TLV comprises three field values.  TagLengthValueEMV formulated different tags. They have their own meanings. Usually, the Tag and Length together takes 1 to 4 bytes.
The Best example for TLV.In the below example, you can find the sample TAG, LENGTH, and VALUE fields.

[Tag][Value Length][Value] (ex. "9F4005F000F0A001")
where

Tag Name = 9F40

Value Length (in bytes) = 05 
Value (Hex representation of bytes. Example, "F0" – 1-byte) = F000F0A001

In the above message, tag 9F40 has some meaning designed by EMV company. Here you can find a list of EMV Tags.
How to read the TLVTag: 1…

5 Strategies to Write Better SQL Queries

Image
SQL is also called structural Query Language. You can pronounce it as Sequel. You can learn SQL quickly if you watch some videos of Tera Tom (He is the guru of SQL).SQL is the language of data, and if you want to interact with data, you need to know it. 
Strategy Number: 01Use indexes in where clause of SQL. Let me elaborate more that the columns you are using in the WHERE clause should be already part of the Index columns of that database Table.SQL Query, Date of Birth is the index column:SELECT * FROM emp_sal_nonppi WHERE dob <= 2017-08-01;Strategy Number: 02Some people use a DISTINCT clause to eliminate duplicates. The idea behind this is we do not want duplicates. The same you achieve it by GROUP BY.GROUP BY to eliminate duplicates Sample SQL:SELECT E.empno, E.lastname FROM emp E,empprojact EP  WHERE E.empno = EP.empno  GROUP BY E.empno, E.lastname;Strategy Number: 03WHERE clause in SQL is useful to filter the criteria you need. Some people use the column they use in the WHERE cla…

SQL: 8 Frequently Used DATE Functions

Image
All projects depends in DATE, and without it there is no way of existing of project. 

The Quick tour helps you use this function effectively. You can learn Oracle SQL step by step for all Developers.

Below is the list of SQL DATE functions
SYSDATE - It returns current local DATE and TimeCURRENT_DATE - Return local DATE and TIME and adjusted to current session Time Zone.ROUND (DATE) - Rounds to nearest DAYTRUNC (DATE) - Truncates TimeMONTHS_BETWEEN(date1,date2) - Returns number of months between date1 and date2'LAST_DAY(DATE)- Returns last of the monthADD_MONTHS(date,integer_months) - Adds specified number of months to specified date.NEXT_DAY(date, day_of_week) - Returns to next day of the week that comes after the specified DATEAlso Read
The Best SQL Book for all Developers.

3 SQL Query Examples to Create Views Quickly

Image
There are three kinds of Views in SQL. The three views are Read-only, Force, and Updatable. Views real usage is to hide data. And you need to ensure base tables are present before you create a View.


You can call views as logical tables. The advantage of Views is you can show only some of the fields of base tables.
What is a View in SQLA view can be constructed with another view so it is called a nested view.You can create or replace an existing viewA view can be created without having base tables. This is possible with the FORCE option.#1: Read-Only Views
The standard syntax for the view is as follows:
CREATE OR replace VIEW invoice_summary AS
SELECT vendor_name count(*) AS invoice_count,
SUM(invoice_total) AS invoice_total_sum
FROM vendor
JOIN invoices
ON vendors.vendor_id*invoices.vendor_id
GROUP BY vendor_name;
Notes: You cannot update Read-only Views

#2: Force Views
CREATE FORCE VIEW products_list
AS
SELECT product_description,
product_price
FROM products;
Notes: Without base Table you can create …

PL/SQL Sample code and error handling mechanism

SAMPLE PL/SQLCREATE TABLE dummy( dummy_value VARCHAR2(1)); DECLARE -- Define local variable. my_string VARCHAR2(1) := ' '; my_number NUMBER; BEGIN -- Select a white space into a local variable. SELECT ' ' INTO my_string FROM dummy; -- Attempt to assign a single white space to a number. my_number := TO_NUMBER(my_string); EXCEPTION WHEN no_data_found THEN dbms_output.put_line('SELECT-INTO'||CHR(10)||SQLERRM); END; /Output and Error: The program returns the following output, which illustrates formatting user- defined exceptions. 
The CHR(10) inserts a line return and provides a clean break between the program's SQLCODE and SQLERRM messages:
RAISE my_error SQLCODE [1]  SQLERRM [User-Defined Exception]