Most Important SQL Queries in 2024

118 Views

Here’s a revised version of your SQL query explanations and examples, including a brief introduction for each type of query:

SQL Queries and Examples

Retrieving Tables
To list all tables present in a specific database schema (My_Schema), you can use the following query:

sql

SELECT * FROM My_Schema.Tables;
Selecting Columns from a Table
- To retrieve a specific column, such as Student_ID from the STUDENT table:
  
  sql
  
  SELECT Student_ID FROM STUDENT;
- To display all columns from the STUDENT table:
  
  sql
  
  SELECT * FROM STUDENT;
Outputting Data Using a Constraint
To fetch records based on a specific condition, such as retrieving details where EMP_ID equals ‘0000’:

sql

SELECT EMP_ID, NAME FROM EMPLOYEE_TBL WHERE EMP_ID = '0000';
Outputting Sorted Data Using ORDER BY
- To sort the results by EMP_ID for employees in Seattle:
  
  sql
  
  SELECT EMP_ID, LAST_NAME FROM EMPLOYEE WHERE CITY = 'Seattle' ORDER BY EMP_ID;
- To sort the results in ascending or descending order, you can specify ASC (ascending) or DESC (descending):
  
  sql
  
  SELECT EMP_ID, LAST_NAME FROM EMPLOYEE_TBL WHERE CITY = 'INDIANAPOLIS' ORDER BY EMP_ID ASC;
  
  By default, ORDER BY sorts data in ascending order if neither ASC nor DESC is specified.
Outputting Data Using GROUP BY
- To group data by Name and Age, filter records where Age is greater than 40, and then sort the results by Name:
  
  sql
  
  SELECT Name, Age FROM Patients WHERE Age > 40 GROUP BY Name, Age ORDER BY Name;
- To group records by price, count the number of records for each price, and sort the output by price:
  
  sql
  
  SELECT COUNT(price), price FROM Orders WHERE price < 70 GROUP BY price ORDER BY price;
Note: The columns in the SELECT and GROUP BY clauses must match to avoid errors.
Data Manipulation Using COUNT
To count the number of customers in each country:

sql

SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;
Data Manipulation Using SUM
To calculate the total salary of employees under the age of 30:

sql

SELECT SUM(Salary) FROM Employee WHERE Emp_Age < 30;
Data Manipulation Using AVG
To find the average price of products:

sql

SELECT AVG(Price) FROM Products;
Listing All Views
To list all views available in the My_Schema schema:

sql

SELECT * FROM My_Schema.Views;
Creating a View
To create a view named Failing_Students that shows students with a GPA greater than 40:

sql

CREATE VIEW Failing_Students AS SELECT S_NAME, Student_ID FROM STUDENT WHERE GPA > 40;

These queries provide a foundational understanding of SQL operations for retrieving and manipulating data in a database.

SQL Queries for Managing Views and Database Objects

Query for Retrieving a View

To select and display all attributes from an existing view named Failing_Students, use the following query:

sql

SELECT * FROM Failing_Students;

Query for Updating or Creating a View

To create or replace a view named Product List that includes ProductID, ProductName, and Category from the Products table where Discontinued is No, use:

sql

CREATE OR REPLACE VIEW Product_List AS

SELECT ProductID, ProductName, Category

FROM Products

WHERE Discontinued = 'No';

Note: A view is a virtual table that provides a way to simplify complex queries and represent data from base tables. Operations on a view affect the underlying base tables.

Query for Dropping a View

To drop (delete) a view named V1, use:

sql

DROP VIEW V1;

Note: Ensure that no other views or objects depend on the view being dropped.

Query to Display User Tables

To display all user-defined tables in the database, use:

sql

SELECT * FROM Sys.objects WHERE Type = 'u';

Note: This query lists all tables owned by the current user.

Query to Display Primary Keys

To list all primary key constraints in the database:

sql

SELECT * FROM Sys.objects WHERE Type = 'PK';

Note: Primary keys ensure each row in a table is uniquely identifiable and do not allow NULL values.

Query for Displaying Unique Keys

To list all unique key constraints in the database:

sql

SELECT * FROM Sys.objects WHERE Type = 'uq';

Note: Unique keys ensure that all values in a column are distinct, allowing only one NULL value per column.

Displaying Foreign Keys

To display all foreign key constraints in the database:

sql

SELECT * FROM Sys.objects WHERE Type = 'f';

Note: Foreign keys establish relationships between tables by referencing primary keys from other tables.

Displaying Triggers

To list all triggers defined in the database:

sql

SELECT * FROM Sys.objects WHERE Type = 'tr';

Note: Triggers automatically execute specified actions in response to certain events (e.g., insert, update, delete) on a table.

Displaying Internal Tables

To view metadata for internal tables, which are typically created by the database system and are not directly accessible:

sql

SELECT * FROM Sys.objects WHERE Type = 'it';

Note: Internal tables usually store system information and are not intended for direct user manipulation.

Displaying a List of Procedures

To list all stored procedures in the database:

sql

SELECT * FROM Sys.objects WHERE Type = 'p';

Note: Stored procedures are collections of SQL statements that perform specific tasks and can be executed with a single command.

These queries provide essential operations for managing views, constraints, and various database objects, ensuring effective database administration and data integrity.

21. Swapping the Values of Two Columns

To swap the values of two columns, such as Zip and Phone, in the Customers table:

sql

UPDATE Customers SET Zip = Phone, Phone = Zip;

22. Returning a Column of Unique Values

To retrieve a list of unique customer IDs from the Customers table:

sql

SELECT DISTINCT ID FROM Customers;

23. Making a Top 25 List with the `SELECT TOP` Clause

To get the top 25 records from the Customers table:

sql

SELECT TOP 25 * FROM Customers;

Note: The clause WHERE Customer_ID <> NULL is incorrect because NULL should not be compared using <>. Use IS NOT NULL instead if you want to exclude nulls.

24. Searching for SQL Tables with Wildcards

To find all customers whose names start with “Herb”:

sql

SELECT * FROM Customers WHERE Name LIKE 'Herb%';

25. Retrieving Records Between Two Dates

To get all records from the Orders table where the Date falls between two specified dates:

sql

SELECT * FROM Orders WHERE Date BETWEEN '2018-01-12' AND '2018-01-13';

Note: Use the ISO format (YYYY-MM-DD) for date literals to avoid ambiguity.

26. Finding the Intersection of Two Tables

To fetch records from the Customers table that have matching entries in the Orders table based on ID:

sql

SELECT Customers.ID

FROM Customers

INNER JOIN Orders ON Customers.ID = Orders.ID;

27. Combining Results with `UNION`

To combine the list of customer phone numbers with items from recent orders:

sql

SELECT Phone FROM Customers

UNION

SELECT Item FROM Orders;

Note: UNION removes duplicate rows by default. Use UNION ALL if you want to include duplicates.

28. Making Column Labels More Friendly

To alias the Item column to item_description for better readability:

sql

SELECT Item AS item_description FROM Orders;

29. Using `ALL` in Queries

To get items where the ID matches all IDs of orders with a quantity greater than 50:

sql

SELECT Item

FROM Orders

WHERE ID = ALL

    (SELECT ID FROM Orders WHERE Quantity > 50);

Note: The use of ALL is not always necessary. It depends on the specific requirement; in many cases, a simple IN clause might suffice.

30. Writing Developer-Friendly SQL

Adding comments to your SQL scripts helps with clarity and maintenance:

Single-line comment:

sql

-- This query retrieves all items from the Orders table SELECT Item FROM Orders;
Multi-line comment:

sql

/* This section of the script is used to retrieve customer details based on specific conditions. It is currently inactive but may be used in future development. */ SELECT * FROM Customers;
Commenting out code:

sql

-- SELECT * FROM Orders WHERE Quantity > 50;

Using these practices ensures your SQL queries are not only functional but also maintainable and easy to understand for future developers.

30. Commenting SQL Queries

Single-line comments use --:

sql

SELECT item -- single comment  

FROM Orders -- another single comment

WHERE id

ALL = (SELECT ID FROM Orders

WHERE quantity > 25);

Multi-line comments use /* */:

sql

/* This query below is commented so it won't execute */

/*

SELECT item FROM Orders

WHERE date ALL = (SELECT Order_ID FROM Orders

WHERE quantity > 50)

*/

31. Creating a New Database

To create a new database called AllSales:

sql

CREATE DATABASE AllSales;

32. Adding Tables to the New Database

To create the Customers table within the database:

sql

CREATE TABLE Customers (

ID varchar(80),

Name varchar(80),

Phone varchar(20)

);

33. Modifying and Deleting Tables

Adding a new column to an existing table:

sql

ALTER TABLE Customers ADD Birthday varchar(80);

Deleting a table:

sql

DROP TABLE table_name;

34. Indexing

Creating a table with a Primary Key and setting ID to auto-increment:

sql

CREATE TABLE Customers (

ID int NOT NULL AUTO_INCREMENT,

Name varchar(80) NOT NULL,

PRIMARY KEY (ID)

);

35. Advanced Performance Optimization

Selecting specific columns instead of using *:

sql

SELECT Name, Birthday, Phone, Address, Zip FROM Customers;

Avoiding SP_ prefix for stored procedures:

sql

-- Use only procedure name

EXEC ProcedureName;

Setting NOCOUNT ON to improve performance:

sql

SET NOCOUNT ON;

Using INNER JOIN for better performance:

sql

SELECT * FROM Orders

INNER JOIN Customers ON Orders.ID = Customers.ID;

36. Conditional Subquery Results

Using EXISTS to check for records:

sql

SELECT Name FROM Customers

WHERE EXISTS

    (SELECT Item FROM Orders

    WHERE Customers.ID = Orders.ID AND Price < 50);

37. Copying Data Between Tables

Inserting data into a new table:

sql

INSERT INTO Yearly_Orders

SELECT * FROM Orders

WHERE Date <= '2018-01-01';

38. Handling NULL Values

Using IFNULL to replace NULL values:

sql

SELECT Item, Price *

    (QtyInStock + IFNULL(QtyOnOrder, 0))

FROM Orders;

39. Using `HAVING` for Aggregate Functions

Using HAVING to filter aggregated results:

sql

SELECT COUNT(ID), Region

FROM Customers

GROUP BY Region

HAVING COUNT(ID) > 0;

40. String Functions

Using SUBSTRING_INDEX to extract parts of a string:

sql

SELECT SUBSTRING_INDEX("www.bytescout.com", ".", 2);

This will return www.bytescout, which is the portion of the string before the second dot.

These SQL statements and concepts cover essential and advanced operations in database management, from basic queries to optimization and handling special cases.

Check this video to learn about every SQL query:

Sure, here are 20 more useful SQL query examples to expand your toolkit:

Using ROW_NUMBER() to Assign Unique Sequential Numbers

The ROW_NUMBER() function assigns a unique sequential number to rows within a result set. It’s useful for pagination and ranking.

sql

SELECT eno,

       empname,

       salary,

       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num

FROM employee;

Using RANK() to Rank Rows with Ties

The RANK() function assigns ranks to rows within a partition of a result set, with ties receiving the same rank, and gaps left in the ranking sequence.

sql

SELECT eno,

       empname,

       salary,

       RANK() OVER (ORDER BY salary DESC) AS rank

FROM employee;

Using NTILE() to Divide Rows into Buckets

The NTILE() function divides the result set into a specified number of buckets, each bucket containing approximately the same number of rows.

sql

SELECT eno,

       empname,

       salary,

       NTILE(4) OVER (ORDER BY salary DESC) AS quartile

FROM employee;

Using SUM() with a Window Function

This query calculates a running total of salaries, showing the cumulative sum for each row.

sql

SELECT eno,

       empname,

       salary,

       SUM(salary) OVER (ORDER BY eno) AS running_total

FROM employee;

Using COUNT() with a Window Function

This query calculates a running count of employees.

sql

SELECT eno,

       empname,

       salary,

       COUNT(*) OVER (ORDER BY eno) AS running_count

FROM employee;

Using PERCENTILE_CONT() for Median Calculation

PERCENTILE_CONT() calculates the median (50th percentile) value.

sql

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary

FROM employee;

Using GROUP_CONCAT() to Aggregate Values into a List

GROUP_CONCAT() aggregates values from multiple rows into a single comma-separated string.

sql

SELECT department,

       GROUP_CONCAT(empname ORDER BY empname) AS employees

FROM employee

GROUP BY department;

Using CASE to Create Conditional Columns

The CASE statement creates new columns based on conditional logic.

sql

SELECT eno,

       empname,

       salary,

       CASE

           WHEN salary > 3000 THEN 'High'

           WHEN salary BETWEEN 2000 AND 3000 THEN 'Medium'

           ELSE 'Low'

       END AS salary_category

FROM employee;

Using ILIKE for Case-Insensitive Pattern Matching (PostgreSQL)

ILIKE is used for case-insensitive pattern matching in PostgreSQL.

sql

SELECT empname

FROM employee

WHERE empname ILIKE '%john%';

Using TRIM() to Remove Whitespace

TRIM() removes leading and trailing whitespace from a string.

sql

SELECT TRIM(empname) AS trimmed_name

FROM employee;

Using REPLACE() to Substitute Substrings

REPLACE() substitutes all occurrences of a substring within a string.

sql

SELECT REPLACE(empname, 'John', 'Jonathan') AS updated_name

FROM employee;

Using CAST() for Type Conversion

CAST() is used to convert one data type to another.

sql

SELECT CAST(salary AS VARCHAR) AS salary_str

FROM employee;

Using CONCAT() to Concatenate Strings

CONCAT() concatenates multiple strings into one.

sql

SELECT CONCAT(empname, ' - ', job) AS emp_job

FROM employee;

Using LEFT() to Extract a Substring from the Left

LEFT() extracts a specified number of characters from the left of a string.

sql

SELECT LEFT(empname, 5) AS short_name

FROM employee;

Using RIGHT() to Extract a Substring from the Right

RIGHT() extracts a specified number of characters from the right of a string.

sql

SELECT RIGHT(empname, 5) AS end_name

FROM employee;

Using SUBSTRING() to Extract Substrings

SUBSTRING() extracts a substring from a string, starting at a specified position.

sql

SELECT SUBSTRING(empname, 1, 4) AS sub_name

FROM employee;

Using DATEADD() to Add Time Intervals (SQL Server)

DATEADD() adds a specified time interval to a date.

sql

SELECT DATEADD(day, 10, GETDATE()) AS future_date;

Using DATEDIFF() to Calculate Date Differences

DATEDIFF() calculates the difference between two dates.

sql

SELECT DATEDIFF(day, hire_date, GETDATE()) AS days_employed

FROM employee;

Using DATEPART() to Extract Parts of a Date

DATEPART() extracts a specific part (e.g., year, month) from a date.

sql

SELECT DATEPART(year, hire_date) AS hire_year

FROM employee;

Using EXTRACT() to Extract Date Parts (PostgreSQL)

EXTRACT() extracts parts of a date in PostgreSQL.

sql

SELECT EXTRACT(MONTH FROM hire_date) AS hire_month

FROM employee;

Using TO_DATE() to Convert Strings to Dates (Oracle)

TO_DATE() converts a string to a date format.

sql

SELECT TO_DATE('2024-08-24', 'YYYY-MM-DD') AS formatted_date

FROM dual;

Using GROUP BY with Multiple Columns

You can group by multiple columns for more granular aggregation.

sql

SELECT department, job, COUNT(*) AS count

FROM employee

GROUP BY department, job;

Using HAVING to Filter Aggregated Data

HAVING filters groups based on aggregate functions.

sql

SELECT department, COUNT(*) AS count

FROM employee

GROUP BY department

HAVING COUNT(*) > 5;

Using UNION ALL to Combine Results Including Duplicates

UNION ALL combines the results of two or more queries, including duplicates.

sql

SELECT empname FROM employee WHERE salary > 3000

UNION ALL

SELECT empname FROM employee WHERE job = 'Manager';

Using EXISTS to Check for the Existence of Rows

EXISTS checks if a subquery returns any rows.

sql

SELECT empname

FROM employee

WHERE EXISTS (

    SELECT 1

    FROM department

    WHERE department.dept_id = employee.dept_id

);

Using NOT EXISTS to Check for Non-Existence

NOT EXISTS checks if a subquery returns no rows.

sql

SELECT empname

FROM employee

WHERE NOT EXISTS (

    SELECT 1

    FROM department

    WHERE department.dept_id = employee.dept_id

);

Using IN to Filter Based on a Set of Values

IN filters results based on a set of values.

sql

SELECT empname

FROM employee

WHERE job IN ('Manager', 'Analyst');

Using NOT IN to Exclude a Set of Values

NOT IN excludes results based on a set of values.

sql

SELECT empname

FROM employee

WHERE job NOT IN ('Manager', 'Analyst');

Using DISTINCT to Remove Duplicate Rows

DISTINCT removes duplicate rows from the result set.

sql

SELECT DISTINCT job

FROM employee;

Using WITH (Common Table Expressions – CTEs)

WITH defines a common table expression for use in a query.

sql

WITH EmployeeCTE AS (

    SELECT eno, empname, salary

    FROM employee

    WHERE salary > 3000

)

SELECT *

FROM EmployeeCTE

ORDER BY salary DESC;

These examples cover a range of SQL functionalities from analytical functions to date manipulations, providing useful tools for various database querying tasks.

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:

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

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

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:

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.

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

MEASURES  STRT.tstamp AS initial_tstamp,
          LAST(UP.tstamp) AS first_tstamp,
          LAST(DOWN.tstamp) AS finished_tstamp

Example

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:

FIRST_VALUE 
  { (expr) [NULLS ]
  | (expr [NULLS ])
  }
  OVER (analytic clause)

Example

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.

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,

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.

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

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

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

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

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

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

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

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

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

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

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

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;

Most Important SQL Queries in 2024

SQL Queries and Examples

SQL Queries for Managing Views and Database Objects

21. Swapping the Values of Two Columns

22. Returning a Column of Unique Values

23. Making a Top 25 List with the SELECT TOP Clause

24. Searching for SQL Tables with Wildcards

25. Retrieving Records Between Two Dates

26. Finding the Intersection of Two Tables

27. Combining Results with UNION

28. Making Column Labels More Friendly

29. Using ALL in Queries

30. Writing Developer-Friendly SQL

30. Commenting SQL Queries

31. Creating a New Database

32. Adding Tables to the New Database

33. Modifying and Deleting Tables

34. Indexing

35. Advanced Performance Optimization

36. Conditional Subquery Results

37. Copying Data Between Tables

38. Handling NULL Values

39. Using HAVING for Aggregate Functions

40. String Functions

51. Top- N queries

52. CORR Analytic Query

Example

53. NTILE Analytic Query

Example

54. VARIANCE, VAR_POP, and VAR_SAMP Query

55. STDDEV, STDDEV_POP, and STDDEV_SAMP Queries

56. Pattern Matching

Syntax

Example

57. FIRST_VALUE

Syntax:

Example

58. LAST_VALUE

59. Prediction

60. CLUSTER_SET

Example

61. WITH (Common Table Expressions)

Syntax

62. NANVL

Example

63. WIDTH_BUCKET

Example

64. COSH

Example

65. SOUNDEX

Example

66. TZ_OFFSET

Example

67. CARDINALITY

Example

68. DUMP

Example

69. PATH

Example

70. UNISTR

Example

Database Queries in SQL

Example of Query in a Database

Database Queries Examples

Common Database Queries

DBMS Queries with Examples

Recent Posts

Recent Categories

23. Making a Top 25 List with the `SELECT TOP` Clause

27. Combining Results with `UNION`

29. Using `ALL` in Queries

39. Using `HAVING` for Aggregate Functions