90 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

  1. 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;
  2. 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;
  3. 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';
  4. 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.

  5. 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.

  6. Data Manipulation Using COUNT

    To count the number of customers in each country:

    sql

    SELECT COUNT(CustomerID), Country FROM Customers
    GROUP BY Country;
  7. 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;
  8. Data Manipulation Using AVG

    To find the average price of products:

    sql

    SELECT AVG(Price) FROM Products;
  9. Listing All Views

    To list all views available in the My_Schema schema:

    sql

    SELECT * FROM My_Schema.Views;
  10. 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

  1. 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;
  1. 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.

  1. 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.

  1. 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.

  1. 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.

  1. 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.

  1. 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.

  1. 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.

  1. 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.

  1. 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:

  1. 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;
  1. 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;
  1. 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;
  1. 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;
  1. 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;
  1. 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;
  1. 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;
  1. 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;
  1. 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%';
  1. Using TRIM() to Remove Whitespace

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

sql

SELECT TRIM(empname) AS trimmed_name
FROM employee;
  1. 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;
  1. 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;
  1. Using CONCAT() to Concatenate Strings

CONCAT() concatenates multiple strings into one.

sql

SELECT CONCAT(empname, ' - ', job) AS emp_job
FROM employee;
  1. 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;
  1. 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;
  1. 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;
  1. 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;
  1. 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;
  1. 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;
  1. 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;
  1. 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;
  1. 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;
  1. 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;
  1. 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';
  1. 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
);
  1. 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
);
  1. 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');
  1. 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');
  1. Using DISTINCT to Remove Duplicate Rows

DISTINCT removes duplicate rows from the result set.

sql

SELECT DISTINCT job
FROM employee;
  1. 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:

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.

  1. SELECT – database query used to extract data from a table.
  2. CREATE DATABASE – database query used to create a new database.
  3. DROP DATABASE – database query used to delete a database.
  4. CREATE TABLE – database query used to create a table in the specified database.
  5. ALTER TABLE – database query used to modify an existing table in the specified database
  6. DROP TABLE – database query used to delete an existing table in the specified database
  7. CREATE INDEX – Index creation query.
  8. CREATE VIEW – View creation query.
  9. DROP VIEW – View deletion query.
  10. CREATE PROCEDURE – Procedure creation query.
  11. CREATE FUNCTION – Function creation query.
  12. DROP PROCEDURE – Procedure deletion query.
  13. 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.

  1. Create a table with a primary key called “ID”.
CREATE TABLE table_name (
PRIMARY KEY(column_name)
);
  1. Create a table with a non-unique index called “IDX” on column_name.
CREATE INDEX idx_name ON table_name (column_name);
  1. 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;