Found 12 Articles for SQLite

SQLite is a popular lightweight and server less database management system used in many applications. It is known for its ease of use, small footprint, and portability. However, just like any other database, it is important to have a backup of your SQLite database to protect against data loss. Python is a powerful programming language that is widely used for various tasks including data processing and management. In this tutorial, we will explore how to create a backup of a SQLite database using Python. We will walk through the process of connecting to a SQLite database, creating a backup file, ... Read More

We can find the duplicate TRANSACTION_ID in the ORDERS DB2 table using the below query:ExampleSELECT TRANSACTION_ID, COUNT(*) AS TRANSACTION_COUNT FROM ORDER GROUP BY TRANSACTION_ID HAVING COUNT(*) > 1The purpose of COUNT(*) is to count the number of rows. We will group the result based on the TRANSACTION_ID using GROUP BY function and to display the duplicate transaction ids, we will place a predicate using HAVING statement for COUNT(*) greater than one.For example, consider the below TRANSACTIONS DB2 table:TRANSACTION_IDTRANSACTION_STATUSIRN22345PAIDIRN22345PAIDIRN22345PAIDIRN56902PAIDIRN99781UNPAIDIRN56902PAIDThe query will give the below result:TRANSACTION_IDTRANSACTION_COUNTIRN223453IRN569022IRN997811Read More

Problem: Write a SQL query to count the number of orders which were placed today from the ORDERS DB2 table. (The date should not be hardcoded)SolutionWe can find the count of orders which are placed today using the below DB2 query:ExampleSELECT COUNT(ORDER_ID) AS ORDER_COUNT FROM ORDERS WHERE ORDER_DATE = CURRENT DATEIn this query, we have used the COUNT COLUMN function which will count the total number of ORDER_ID (primary key). In the WHERE clause, we will use the predicate for the ORDER_DATE column. The CURRENT DATE is a DB2 inbuilt function which will return the current system date.For example, if ... Read More

Problem: Write a SQL query to display 2 columns. First column should have ORDER_ID, the second column should give the value as YES/NO for free shipping based on ORDER_TOTAL > 500.SolutionThe query to display ORDER_ID and free shipping result based on the ORDER_TOTAL criteria can be written as below.ExampleSELECT ORDER_ID,    CASE WHEN ORDER_TOTAL > 500 THEN ‘YES’       ELSE ‘NO’ AS FREE_SHIPPING    END FROM ORDERSWe will use CASE expressions through which we can implement a logic to check the ORDER_TOTAL. If the ORDER_TOTAL is greater than 500 then we will get ‘YES’ for the free shipping ... Read More

Stored procedures are a pieces of the code in written in PL/SQL to do some specific task. Stored procedures can be invoked explicitly by the user. It's like a java program , it can take some input as a parameter then can do some processing and can return values.On the other hand,  trigger is a stored procedure that runs automatically when various events happen (eg update, insert, delete). Triggers are more like an event handler they run at the specific event. Trigger can not take input and they can’t return values.Sr. No.KeyTriggersStored procedures1Basic trigger is a stored procedure that runs automatically when ... Read More

SQL query is used to fetch data from the database. In some of the scenario you may need some perquisite data to call subsequent SQL query to fetch data from a table so instead of writing two seperate query we can write SQL query within the query.Therefore subQuery is a way to combine or join them in single query. Subqurey can have two types −Correlated subquery - In correlated subquery, inner query is dependent on the outer query. Outer query needs to be executed before inner queryNon-Correlated subquery - In non-correlated query inner query does not dependent on the outer query. ... Read More

In Hierarchical data model, relationship between table and data is defined in parent child structure. In this structure data are arranged in the form of a tree structure. This model supports one-to-one and one-to-many relationships.On the other hand, network model arrange data in graph structure. In this model each parents can have multiple children and children can also have multiple parents. This model supports many to many relationships also.Sr. No.KeyHierarchical Data ModelNetwork Data Model1Basic Relationship between records is of the parent child typeRelationship between records is expressed in the form of pointers or links.2        Data Inconsistency It can have data ... Read More

In Relational database tables are associated with each other and we used foreign key to maintain relationships between tables. We used join clause to retrieve data from associated tables. The join condition indicates how column in each table are matched against each other. There are two types of joins clause in SQL Inner join Outer joinOuter join is again divided into parts −LEFT OUTER JOIN - It will return all data of the left table and matched  records in both table RIGHT OUTER JOIN - it will return all the data of the right table and matched records in both tableSr. No.KeyInner joinOuter join1Basic It ... Read More

PL/SQL is a block structured language i.e the code of PL./SQL is written in the form of blocks. PL/SQL also contains the robustness, security and portability of the Oracle database.Each block of PL/SQL contains the following subparts −Declarations -  This section contains all the items that needs to be declared before the program such as variables, subprograms etc. This section contains the keyword DECLARE at its start. In general, Declarations is an optional subpart of the PL/SQL program.Executable Commands -  This section of the PL/SQL code contains the executable statements. It contains BEGIN and END at its starting and ending. ... Read More

Packages are SQL procedures, functions, variables, statements etc. that are grouped into a single unit. Many different applications can share the contents of a package, as it is stored in the database.Parts of a PackageThe following are the parts of a package in Oracle −Package SpecificationThe package specifications contains information about all the procedures, functions, variables, constants etc. stored inside it. It has the declaration of all the components but not the code.All the objects that are in the specification are known as public objects. If there is any object that is not available in the specification but is coded ... Read More