Check this video to learn about every SQL query:
51. Top- N queries
Top-N queries give a process for restricting the number of rows delivered from organized assemblages of data. They are remarkably beneficial when users want to give the top or bottom number of rows from a table.
For example, the following query gives the 20 rows with 10 different values:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
SELECT price FROM sales_order ORDER BY price; PRICE ---------- 100 100 200 200 300 300 400 400 500 500 600 PRICE ---------- 600 700 700 800 800 900 900 1000 1000 20 rows selected. |
52. CORR Analytic Query
The CORR analytic function is utilized to determine the coefficient of correlation. This query is also used to calculate the Pearson correlation coefficient. The function calculates the following on rows in the table with no null values. This query always returns the values between +1 and -1, which describe the following:
Syntax: CORR(exp1, exp2) [ OVER (analytic_clause) ]
Example
1
2
3
4
5
6
7
|
SELECT empid, name, dno, salary, job, CORR(SYSDATE - joiningdate, salary) OVER () AS my_corr_val FROM employee; |
53. NTILE Analytic Query
The NTILE enables users to split a sequence set into a detailed number of relatively similar groups, or containers, rows sanctioning. If the number of rows in the collection is less than the number of containers defined, the number of containers will be decreased. The basic syntax is as displayed below:
NTILE(exp) OVER ([ partition_clause ] order_by)
Example
1
2
3
4
5
6
|
SELECT empid, name, dno, salary, NTILE(6) OVER (ORDER BY salary) AS container_no FROM employee; |
54. VARIANCE, VAR_POP, and VAR_SAMP Query
The VARIANCE, VAR_POP, and VAR_SAMP are aggregate functions. These are utilized to determine the variance, group variance, and sample variance of a collection of data individually. As aggregate queries or functions, they decrease the number of rows, therefore the expression “aggregate”. If the data isn’t arranged we change the total rows in the Employee table to a separate row with the aggregated values. For example, the following query is displaying the use of these functions:
1
2
3
4
5
6
7
8
|
SELECT VARIANCE(salary) AS var_salary, VAR_POP(salary) AS pop_salary, VAR_SAMP(salary) AS samp_salary FROM employee; VAR_SALARY POP_SALARY SAMP_SALARY ------------ ----------- ------------ 1479414.97 1588574.81 1388717.27 |
55. STDDEV, STDDEV_POP, and STDDEV_SAMP Queries
The STDDEV, STDDEV_POP, and STDDEV_SAMP aggregate queries or functions are applied to determine the standard deviation, population standard deviation, and cumulative sample standard deviation individually. As aggregate queries, they decrease the number of rows, therefore the expression “aggregate”. If the data isn’t arranged we convert all the rows in the EMPLOYEE table to a separate row. For example, the following query is displaying the use of all these functions.
1
2
3
4
5
6
7
8
|
SELECT STDDEV(salary) AS stddev_salary, STDDEV_POP(salary) AS pop_salary, STDDEV_SAMP(salary) AS samp_salary FROM employee; STDDEV_SALARY POP_SALARY SAMP_SALARY ---------- -------------- --------------- 1193.50 1159.588 1193.603 |
If there is more than one account after dropping nulls, the STDDEV function gives the result of the STDDEV_SAMP. Using an empty OVER clause converts the STDDEV query result into an analytic query. The absence of a partitioning indicates the entire output set is interpreted as a particular partition, so we accept the standard deviation of the salary and the primary data.
56. Pattern Matching
The pattern matching syntax adds various alternatives. Data must be treated precisely and in a proper form. The PARTITION BY and ORDER BY conditions of all SQL analytic queries is applied to split the data into accumulations and within each group. If no partitions are specified, it is considered the entire sequence set is one huge partition.
For example,
The MEASURES clause specifies the column result that will be provided for each match.
Syntax
1
2
3
|
MEASURES STRT.tstamp AS initial_tstamp, LAST(UP.tstamp) AS first_tstamp, LAST(DOWN.tstamp) AS finished_tstamp |
Example
1
2
3
4
|
DEFINE UP AS UP.products_sold > PREV(UP.products_sold), FLAT AS FLAT.products_sold = PREV(FLAT.products_sold), DOWN AS DOWN.products_sold < PREV(DOWN.products_sold) |
57. FIRST_VALUE
The simplest way to get analytic functions is to begin by studying aggregate functions. An aggregate function collects or gathers data from numerous rows into a unique result row. For instance, users might apply the AVG function to get an average of all the salaries in the EMPLOYEE table. Let’s take a look at how First_Value can be used. The primary explanation for the FIRST_VALUE analytic function is displayed below.
Syntax:
1
2
3
4
5
|
FIRST_VALUE { (expr) [NULLS ] | (expr [NULLS ]) } OVER (analytic clause) |
Example
1
2
3
4
5
6
|
SELECT eno, dno, salary, FIRST_VALUE(salary) IGNORE NULLS OVER (PARTITION BY dno ORDER BY salary) AS lowest_salary_in_dept FROM employee; |
The above query will ignore null values.
58. LAST_VALUE
The primary explanation for the LAST_VALUE analytic query or function is displayed below.
1
2
3
4
|
Syntax: LAST_VALUE { (expr) [ { NULLS ] | (expr [ NULLS ]) OVER (analytic clause) |
The LAST_VALUE analytic query is related to the LAST analytic function. The function enables users to get the last output from an organized column. Applying the default windowing to the output can be surprising. For example,
1
2
3
4
5
6
|
SELECT eno, dno, salary, LAST_VALUE(salary) IGNORE NULLS OVER (PARTITION BY dno ORDER BY salary) AS highest_salary_in_dept FROM employee; |
59. Prediction
The design sample foretells the gender and age of clients who are most expected to adopt an agreement card (target = 1). The PREDICTION function takes the price matrix correlated with the design and applies for marital status, and house size as predictors. The syntax of the PREDICTION function can also apply a piece of arbitrary GROUPING information when getting a partitioned model.
1
2
3
4
5
6
7
8
9
10
11
|
SELECT client_gender, COUNT(*) AS ct, ROUND(AVG(age)) AS average_age FROM mining_data_shop WHERE PREDICTION(sample COST MODEL USING client_marital_status, house_size) = 1 GROUP BY client_gender ORDER BY client_gender; CUST_GENDER CNT AVG_AGE ------------ ---------- ---------- F 270 40 M 585 41 |
60. CLUSTER_SET
CLUSTER_SET can get the data in one of the couple steps: It can use a mining type object to the information, or it can mine the data by performing an analytic clause that creates and uses one or more moving mining patterns.
This example enumerates the properties that have the biggest influence on cluster distribution for client ID 1000. The query requests the CLUSTER_DETAILS and CLUSTER_SET functions, which use the clustering model my_sample.
Example
1
2
3
4
5
6
7
8
|
SELECT S.cluster_id, prob, CLUSTER_DETAILS(my_sample, S.cluster_id, 7 USING T.*) kset FROM (SELECT v.*, CLUSTER_SET(my_sample, USING *) nset FROM mining_data WHERE client_id = 1000) T, TABLE(T.nset) Q ORDER BY 2 DESC; |
A cluster is a group table that distributes the corresponding data blocks i.e. all the tables are actually put together. For example, EMPLOYEE and DEPARTMENT tables are connected to the DNO column. If you cluster them, it will actually store all rows in the same data blocks.
.. and TEN More Advanced SQL Queries for our Users!
61. WITH (Common Table Expressions)
A common table expression (CTE) is a defined short result set that endures within the range of a particular statement and that can be called later within that statement, perhaps on many occasions. The following query is describing the CTE:
Syntax
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
WITH all_emp AS ( SELECT empId, BossId, FirstName, LastName FROM Emp WHERE BossId is NULL UNION ALL SELECT e.empId, e.BossId, e.FirstName, e.LastName FROM Emp e INNER JOIN all_emp r ON e.BossId = r.Id ) SELECT * FROM all_emp |
62. NANVL
This function is utilized to deliver an optional value n1 if the inserted value n2 is NaN (not a number), and gives n2 if n2 is not a number. This function is used only for type BINARY_FLOAT. The following query is displaying its use:
Example
1
2
|
SELECT bin_float, NANVL(bin_float,0) FROM my_demo_table; |
63. WIDTH_BUCKET
This function is used to obtain the bucket number. In this, it gives the value of the expression that would come under after being assessed. The following query is displaying its use:
Example
1
2
3
4
5
|
SELECT emp_id, first_name,last_name,dept_id,mgr_id, WIDTH_BUCKET(department_id,20,40,10) "Exists in Dept" FROM emp WHERE mgr_id < 300 ORDER BY "Exists in Dept" ; |
64. COSH
This function is used to deliver the hyperbolic cosine of a number. It accepts all numeric or non-numeric data types as an argument. The following query is displaying its use:
Example
1
|
SELECT COSH(0) "COSH of 0" FROM DUAL; |
65. SOUNDEX
The SOUNDEX function delivers a character string comprising the description of char. It allows users to match words that are spelled antagonistically, but sound similar in English. It does not support CLOB. The following query is displaying its use:
Example
1
2
3
4
|
SELECT last_name, first_name FROM hr.emp WHERE SOUNDEX(last_name) = SOUNDEX( 'SCOTTY'); |
66. TZ_OFFSET
The TZ_OFFSET gives the time zone offset identical to the case based on the date the statement is given. The following query is displaying its use:
Example
1
|
SELECT TZ_OFFSET( 'US/Eastern') FROM DUAL; |
67. CARDINALITY
CARDINALITY is utilized to obtain the number of components in a nested table. It is supported in different versions. The following query is displaying its use:
Example
1
2
|
SELECT product_id, CARDINALITY(ad_mydocs_get) FROM my_media_table; |
68. DUMP
DUMP is one of the important string/char functions. It is utilized to get a VARCHAR2 value. The value delivered defines the data type code. The following query is displaying its use:
Example
1
2
|
SELECT DUMP( 'pqr', 1033) FROM DUAL; |
69. PATH
PATH is applied simply with the UNDER_PATH and EQUALS_PATH requirements. It gives the corresponding path that points to the resource defined in the main state. The following query is displaying its use:
Example
1
2
|
SELECT ANY_PATH FROM RESOURCE_VIEW WHERE EQUALS_PATH(res, '/sys/schemas/OE/www.pqr.com')=3; |
70. UNISTR
UNISTR accepts an expression that determines character data and delivers it in the general character set. It gives support to the Unicode string literals by allowing users to define the Unicode value. The following query is displaying its use:
Example
1
|
SELECT UNISTR( 'pqr\00e4\00f3\00f9') FROM DUAL; |
Database Queries in SQL
These are some of the commonly used database queries in SQL.
- SELECT – database query used to extract data from a table.
- CREATE DATABASE – database query used to create a new database.
- DROP DATABASE – database query used to delete a database.
- CREATE TABLE – database query used to create a table in the specified database.
- ALTER TABLE – database query used to modify an existing table in the specified database
- DROP TABLE – database query used to delete an existing table in the specified database
- CREATE INDEX – Index creation query.
- CREATE VIEW – View creation query.
- DROP VIEW – View deletion query.
- CREATE PROCEDURE – Procedure creation query.
- CREATE FUNCTION – Function creation query.
- DROP PROCEDURE – Procedure deletion query.
- DROP FUNCTION – Function deletion query.
Example of Query in a Database
We will be looking at some SELECT examples of a query in a database, as this is the most common command.
SELECT * FROM employeeTable WHERE emp_no = ‘12’;
This query filters out results that do not match a specified search.
SELECT * FROM sys.objects WHERE object_id=object_id(‘<table name>’);
This database query will list all the columns of the table whose name matches the argument.
Database Queries Examples
The following are some database queries examples that deal with creating tables, in a bit more advanced fashion.
- Create a table with a primary key called “ID”.
CREATE TABLE table_name ( PRIMARY KEY(column_name) );
- Create a table with a non-unique index called “IDX” on column_name.
CREATE INDEX idx_name ON table_name (column_name);
- Create a view with the name “VIEW1” that can be used to query data from table1. The view is created on columns column1 and column2. It must return the same number of rows as the underlying table, and it must return the same data type. In this case, we will return the maximum value for each column in the underlying table when queried against the view. The following query will be used to populate our view:
CREATE VIEW view1 AS SELECT MAX(column1), MAX(column2) FROM table1;
Common Database Queries
Some more common database queries you will need are:
1) The Maximum Value for a Column.
SELECT MAX(column_name) FROM table_name
2) The Minimum Value for a Column.
SELECT MIN(column_name) FROM table_name
3) The Count of Rows in a Table.
SELECT COUNT(*) FROM table_name;
DBMS Queries with Examples
DBMS stands for DataBase Management System. Following are some DBMS queries with examples; these are very important for developers and database admins alike.
1. The List of Values for a Column.
SELECT column_name, column_commalist FROM table_name;
2. The Difference between two Column Values.
SELECT column_name(+) FROM table_name WHERE column_name(+) != value;
III. The List of Unique Values for a Column.
SELECT DISTINCT column_name FROM table_name;