Found 118 Articles for SQL

Most SQL databases are relational. Relational Databases are tabular and have a pre-determined schema that organizes the data logically. Database management solutions have evolved from the classic relational paradigm to the more flexible and scalable NoSQL approach.Some say NoSQL stands for "non-SQL, " but many refer to it as SQL. NoSQL is a non-relational database management system for certain data models. These data models don't need a schema and are scalable. It offers a system-supported alternative to relational databases' tabular format for storing and retrieving data. NoSQL databases don't require a certain schema. You can store data without worrying about ... Read More

PL/SQL is a combination of SQL along with the procedural features of programming languagesFLOYD’s triangle − it is a triangle formed by natural numbers. It is the triangle formed by filling numbers in rows starting from 1 at the top-left corner. It is a right-angled triangle i.e. the count of numbers in each row is the same as the row number.In this problem, we are given a natural number N. Our task is to create Floyd’s triangle in PL/SQL.Let’s take an example to understand the problemInput: 16 Output: 1 2 3 4 5 6 7 8 9 10 11 12 ... Read More

In this problem, we are given a number N. Our task is to finding sum of first n natural numbers in PL/SQL.PL/SQL is a combination of SQL along with the procedural features of programming languages.PL/SQL has the following features −PL/SQL is tightly integrated with SQL.It offers extensive error checking.It offers numerous data types.It offers a variety of programming structures.It supports structured programming through functions and procedures.It supports object-oriented programming.It supports the development of web applications and server pages.PL/SQL has the following advantages −SQL is the standard database language and PL/SQL is strongly integrated with SQL. PL/SQL supports both static and ... Read More

In this problem, we are given three values: base, height and hypotenuse of a right triangle. Our task is to find the area and perimeter of the right triangle in PL/SQL.PL/SQL is a combination of SQL along with the procedural features of programming languagesLet's take an example to understand the problem, Input : height = 4, base = 3, hypotenuse = 5 Output : area = 6, perimeter = 12Explanation −1 + 22 + 333 + 4444 = 4800 Solution ApproachA simple approach to solve the problem is using the formula for the area and perimeter of the triangle.Area = ... Read More

In this post, we will understand the difference between ROLAP and MOLAP.ROLAPIt stands for Relational Online Analytical Processing.It is used for large volumes of data.The access time in ROLAP is slow.It stores data in the form of relation tables.The data in ROLAP is fetched from a data warehouse.It uses complex SQL queries.A static multidimensional view of the data is created in ROLAP.MOLAPIt stands for Multidimensional Online Analytical Processing.It is used for less/limited volumes of data.The access time is quick in MOLAP.Data is stored in a multidimensional array.Data is fetched from the MDDBs database.A sparse matrix is used in MOLAP.Dynamic multidimensional ... Read More

In this post, we will understand the difference between a view and a materialized view.ViewsIt is a logical and virtual copy of a table that is created by executing a ‘select query’ statement.This result isn’t stored anywhere on the disk.Hence, every time, a query needs to be executed when certain data is needed.This way, the most recently updated data would be available from the tables.The tuple/result of the query doesn’t get stored.Instead, the query expression is stored on the disk.The query expression is stored, due to which the last updated data is obtained.They don’t have a storage/update cost associated with ... Read More

DELETE is a Data Manipulation Language (DML) command. It is used to remove tuples/records from a relation/table. On the other hand, DROP is a Data Definition Language (DDL) command and is used to remove named elements of schema like relations/table, constraints or entire schema. Read this article to learn more about DELETE and DROP commands in SQL and how they are different from each other. What is DELETE in SQL? In SQL, the DELETE command is used to remove some or all the records from a database table. With the DELETE command, the WHERE clause is used to define the ... Read More

The OPTIMIZE FOR N ROWS is a DB2 clause which we can add in the query to give priority for the retrieval of the first few rows only. This clause will enable the optimizer to choose the access path that minimizes the response time for fetching first few rows.The OPTIMIZE FOR N ROWS clause is not effective on SELECT DISTINCT and COUNT function because DB2 will need the entire qualifying rows in order to fetch the DISTINCT rows or COUNT the number of rows. The OPTIMIZE FOR N ROWS clause gives DB2 a better opportunity to establish the access path.The ... 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