5-SQL-Keywords

SQL Keywords & Functions
  Introduction

These SQL keywords and functions help in modifying query results, filtering data, and working with date values.


-- 1️⃣ Aliasing (AS)

Aliasing means giving an alternate (temporary) name to a column or table in the result.

It improves readability of the output.


-- 2️⃣ DISTINCT

DISTINCT is used to return only unique values from a column.

It removes duplicate values.


-- 3️⃣ ORDER BY

 ORDER BY is used to sort the result set based on one or more columns.

  a) ASC (Ascending Order)

Default sorting order

Arranges data from A to Z

For numbers → smallest to largest

 Sorts employee names from A to Z.

  b) DESC (Descending Order)

Arranges data from Z to A

For numbers → largest to smallest

 Sorts salary from highest to lowest.


-- 4️⃣ LIMIT

LIMIT is used to restrict the number of rows returned by a query.

 Returns only first 5 rows.


-- 5️⃣ OFFSET

OFFSET is used to skip a specific number of rows before returning results.


-- 6️⃣ WHERE 

WHERE is used to filter records based on specified conditions.

 It is used to extract only those records that fulfill a specified condition.


-- 7️⃣ GROUP BY

GROUP BY is used to group rows that have the same values in specified columns into summary rows.


-- 8️⃣ HAVING

HAVING is used to filter groups based on a specified condition.


EXAMPLE:-

USE market_star_schema;

SELECT * FROM market_fact_full
ORDER BY sales;

SELECT * FROM cust_dimen
ORDER BY city ASC;

SELECT * FROM orders_dimen
ORDER BY order_date ASC;

SELECT * FROM market_fact_full
ORDER BY cust_id ASC, sales DESC;

-- find the highest sales amount order
SELECT * FROM market_fact_full
ORDER BY sales DESC
LIMIT 1;

-- find the order with second highest sales amount
SELECT * FROM market_fact_full
ORDER BY sales DESC
LIMIT 1 OFFSET 1;

-- find the five lowest amount sales orders
SELECT * FROM market_fact_full
ORDER BY sales
LIMIT 5;

-- find the fourth lowest profit value
SELECT * FROM market_fact_full
ORDER BY profit
LIMIT 1 OFFSET 3;

-- find the fourth highest sales value among the orders which has positive profit value
SELECT * FROM market_fact_full
WHERE profit>0
ORDER BY sales DESC
LIMIT 1 OFFSET 3;

-- how many distinct cities are present
SELECT COUNT(DISTINCT city) FROM cust_dimen;

-- find the distinct cities.
SELECT DISTINCT city FROM cust_dimen;