[go: up one dir, main page]
Scribd
Upload
18 views10 pages

Module 2 SQL

The document contains SQL queries and explanations of aggregation, mathematical, string, date and window functions. It inserts sample data, performs aggregation on the data using functions like count, sum, avg, and group by. It also demonstrates window functions like rank, dense_rank, row_number.

Uploaded by

josep hook
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
18 views10 pages

Module 2 SQL

The document contains SQL queries and explanations of aggregation, mathematical, string, date and window functions. It inserts sample data, performs aggregation on the data using functions like count, sum, avg, and group by. It also demonstrates window functions like rank, dense_rank, row_number.

Uploaded by

josep hook
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 10

--Module 2

USE TRAINING;
--INSERT INTO PRODUCT VALUES(44,'PRODUCT1',25);
SELECT * FROM PRODUCT;

SELECT MIN(p_price) as min_price


,MAX(p_price) as max_price
,AVG(p_price) as avg_price
from product;

select * From PRODUCT


where p_price in (
select max(p_price) from PRODUCT
where p_price <800
)

SELECT MIN(p_price) as min_price


from product;

SELECT AVG(p_price) as avg_price


from product;

select count(*) as ct
from product;

select * From product;

select sum(p_price) as sum_price


from product where p_name='Laptop';
select sum(p_price) as sum_price
from product where p_name='Watch';

select p_name,
sum(p_price) from product
--where p_desc <> 'Microsoft'
group by (p_name);

select p_name,
sum(p_price) as sum_price
from product
group by p_name;

select p_name,
max(p_price) as max_price
from product
group by p_name;

---
--ALTER TABLE EXERCISE
--ALTER TABLE PRODUCT ADD MFG_DATE DATE DEFAULT '2001-01-01' NOT NULL;

--SELECT * FROM PRODUCT;


--ALTER TABLE PRODUCT DROP COLUMN MFG_DATE ;

--SELECT * FROM PRODUCT;

-- Dropping a constraint using ALTER command


--ALTER TABLE CUST_ORDER DROP DF__CUST_ORDE__o_dat__45F365D3;

------------------------------AGGREGARTION
FUNCTIONS---------------------------------

select COUNT(p_price) as ct
,SUM(p_price) as sum_price
From PRODUCT;

select * From product;

select avg
(p_price )
from PRODUCT;

select CAST(P_PRICE AS FLOAT) as casted_dt,


p_PRICE
FROM PRODUCT ;

--AVG Function (Avg P_PRICE of PRODUCTs in PRODUCT table)


SELECT P_NAME,
AVG(CAST(P_PRICE AS FLOAT)) as AVG_PRICE
FROM PRODUCT
GROUP BY P_NAME
ORDER BY AVG_PRICE DESC
;
--COUNT Function (Avg P_PRICE of PRODUCTs in PRODUCT table)
SELECT P_NAME,COUNT(P_ID) as CT_PRODUCTS
FROM PRODUCT
GROUP BY P_NAME ;

--MAX Function (MAX P_PRICE of PRODUCTs in PRODUCT table)


SELECT P_NAME,MAX(P_PRICE) as MAX_PRICE
FROM PRODUCT
GROUP BY P_NAME
ORDER BY MAX_PRICE ;

SELECT P_NAME,MIN(P_PRICE) as MAX_PRICE


FROM PRODUCT
GROUP BY P_NAME
ORDER BY MAX_PRICE;

--MIN Function (Avg P_PRICE of PRODUCTs in PRODUCT table)


SELECT P_NAME,MIN(P_PRICE) as MIN_PRICE
FROM PRODUCT
GROUP BY P_NAME
ORDER BY MIN_PRICE;
--SUM Function (Avg P_PRICE of PRODUCTs in PRODUCT table)
SELECT P_NAME, SUM(P_PRICE) as SUM_PRICE
FROM PRODUCT GROUP BY P_NAME
ORDER BY SUM_PRICE DESC;

--CHECKSUM_AGG Function (Avg P_PRICE of PRODUCTs in PRODUCT table)


SELECT P_NAME,
CHECKSUM_AGG(CAST(P_PRICE AS INT)) as CHK_PRICE
FROM PRODUCT
GROUP BY P_NAME
;

--CHECKSUM
SELECT P_PRICE,
CHECKSUM(CAST(P_PRICE AS VARCHAR(10)))
as C_PRICE
FROM PRODUCT
;

--https://stackoverflow.com/questions/7753302/what-is-the-difference-between-
checksum-agg-and-checksum#:~:text=CHECKSUM%20calculates%20a%20hash%20for,aggregated
%20checksum%20for%20each%20group.

--COUNT_BIG Function (Avg P_PRICE of PRODUCTs in PRODUCT table)


SELECT P_NAME,
COUNT_BIG(P_PRICE) as SUM_PRICE
FROM PRODUCT
GROUP BY P_NAME
;
--STDEV Function (Avg P_PRICE of PRODUCTs in PRODUCT table)
SELECT P_NAME,
STDEV(P_PRICE) as STDev_PRICE
FROM PRODUCT
GROUP BY P_NAME
;

--STDDEVP Function (Avg P_PRICE of PRODUCTs in PRODUCT table)


SELECT P_NAME, CAST(STDEVP(P_PRICE) AS DECIMAL (10,5)) as SUM_PRICE
FROM PRODUCT
GROUP BY P_NAME ;

--VAR Function (Avg P_PRICE of PRODUCTs in PRODUCT table)


SELECT P_NAME,VARP(P_PRICE) as VAR_PRICE
FROM PRODUCT
GROUP BY P_NAME;

--------------Mathematical FUNCTION

--ABS Function
SELECT ABS(-100);

--Log function

SELECT LOG(p_PRICE) from PRODUCT

--SQUARE FUNCTION
SELECT p_PRICE,
SQUARE(p_PRICE) from PRODUCT

--CEILING FUNCTION
SELECT CEILING(-4.5);
--FLOOR FUNCTION
SELECT FLOOR(-1.5);

SELECT FLOOR(P_PRICE) FROM PRODUCT;

--ROUND FUNCTION
SELECT ROUND(235,2)

--POWER FUNCTION
SELECT POWER(3,3)

------------------------------DATE FUNCTIONS---------------------------------
--------------Current date and time
SELECT CURRENT_TIMESTAMP;

SELECT GETUTCDATE();

SELECT GETDATE();

SELECT SYSDATETIME();

SELECT SYSDATETIMEOFFSET()

--------------Return date and time parts


SELECT DATENAME(yy, '2017-08-25') AS DatePartString; --return as character

SELECT DATEPART(yy, '2017-08-25') AS DatePartString; --return as INT

SELECT DAY('2017-08-25') AS DatePartString; --return as DAY OF DATE

SELECT MONTH('2017-08-25') AS DatePartString; --return as MONTH OF DATE

SELECT YEAR(
'2017-08-25'
) AS DatePartString; --return as YEAR OF DATE

-------------------------Return date differences


SELECT DATEDIFF(DAY,'2017-08-25','2018-08-25')
AS DatePartString; --return as day diff in DATEs

SELECT DATEDIFF(MONTH,'2017-08-25','2018-08-25') AS DatePartString; --return as


YEAR OF DATE

SELECT DATEDIFF(YEAR,'2017-08-25','2018-08-25') AS DatePartString; --return as


YEAR OF DATE

--------------------- Modify dates

SELECT DATEADD(MONTH,1,GETDATE()) AS NEW_DATE

SELECT EOMONTH('2026-04-06') AS NEW_DATE

SELECT SWITCHOFFSET(
GETDATE(),'-10:40'
) AS NEW_DATE
--Construct date and time from parts
SELECT DATEFROMPARTS(2023,05,27) as new_date

--TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )


SELECT TIMEFROMPARTS(02,02,03,2,2) as new_time
--DATETIMEOFFSET FROM PARTS

--DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions,


hour_offset, minute_offset, precision )

SELECT DATETIMEOFFSETFROMPARTS ( 2010, 12, 31, 14, 23, 23, 1, 12, 0, 7 ) AS Result;

--CHECK IF PARAMETER IS DATE


SELECT ISDATE('2017-01-01');
-------------
/*CREATE TABLE TMP_PRODUCT
(
p_id INT PRIMARY KEY ,
p_name VARCHAR(30) NOT NULL,
p_desc VARCHAR(40) NOT NULL,
p_price FLOAT CHECK(P_PRICE>0) NOT NULL,
p_expiry_date DATE DEFAULT getdate()+180 NOT NULL
);
INSERT INTO TMP_PRODUCT
SELECT * FROM PRODUCT;

SELECT * FROM TMP_PRODUCT;

UPDATE TMP_PRODUCT
SET P_PRICE=0

ALTER TABLE TMP_PRODUCT


ADD CONSTRAINT P_PRICE_CHECK
ON P_PRICE NOT NULL
;
BACKDROP

DROP CONSTRAINT CK__TMP_PRODU__p_pri__4BAC3F29;

DELETE FROM TMP_PRODUCT ;


--WHERE P_id=103;

TRUNCATE TABLE TMP_PRODUCT;


TRUNCATE =DROP + CREATE
*/
-------------------------------------------------STRING FUNCTIONS
--LTRIM Function
SELECT LTRIM(' NEW YORK');
--RTRIM Function
SELECT RTRIM('NEW YORK ');
--TRIM Function
SELECT TRIM(' NEW YORK ');

--LOWER Function
SELECT LOWER('NEW YORK');
--UPPER Function
SELECT UPPER('new york');
--REVERSE Function
SELECT REVERSE('NEW YORK');
--SUBSTRING Function
SELECT SUBSTRING('SHUBHAM',2,4);
--REPLACE FUNCTION
SELECT replace ('NEW NEW YORK','NEW','OLD');
SELECT replace ('KEEP','EE','ET');

SELECT *, UPPER(NEW_EMAIL) AS UPPER_EMAIL


FROM CUSTOMER;
--CONCAT FUNCTION

SELECT CONCAT('NEW ', ' YORK');

SELECT C_NAME,NEW_EMAIL,
CONCAT(C_NAME,'|',NEW_EMAIL)
AS NAME_EMAIL FROM CUSTOMER;

--CONCAT_WS
SELECT CONCAT_WS(',','NEW', ' YORK');

--ASCII
SELECT ASCII('z');
--LENGTH
SELECT LEN('NEW YORK');

SELECT LEN('AKSHAY');

SELECT *,
LEN(EMAIL) AS LEN_EMAIL
FROM CUSTOMER;

SELECT CHOOSE(1, 'First', 'Second', 'Third') Result;

-------------------SYSTEM Functions

SELECT CAST('11233' as INT)


as converted_datatype; --- ANSI SQL

SELECT *,
CAST(P_PRICE AS NUMERIC(7,3)) as NUM_PRICE
FROM PRODUCT;

--SELECT LEN(C_NAME) FROM CUSTOMER;

SELECT CAST('12.00' as FLOAT) as converted_datatype;

SELECT ISNULL('Hello', 'W3Schools.com');--- IS NULL

SELECT ISNULL(NULL, 'W3Schools.com');--- IS NULL


SELECT ISNULL(NULL, NULL);--- IS NULL

SELECT ISNUMERIC('test');
SELECT ISNUMERIC(12345);

SELECT CAST('test' AS INT);


SELECT TRY_CAST('test' AS INT);

----------ALTER COMMAND TO CHANGE DATATYPE


USE TRAINING;

ALTER TABLE PRODUCT


ALTER COLUMN P_DESC VARCHAR(100);

----------------WINDOW FUNCTIONS

--1. RANK & DENSE FUNCTION


USE TRAINING;
select * From PRODUCT
order by p_name, p_price desc ;

--<WIN FUNCTION> OVER(PARTITION BY / ORDER BY ) AS RN

SELECT * FROM (
SELECT
*,
--RANK() OVER(PARTITION BY P_NAME ORDER BY P_PRICE DESC ) AS RNK
--,DENSE_RANK() OVER(PARTITION BY P_NAME ORDER BY P_PRICE DESC ) AS DENSE_RNK,
--ROW_NUMBER() OVER(ORDER BY P_ID) as ROW_NUM,
CUME_DIST() OVER( ORDER BY P_PRICE ) AS CUM_DIST
FROM
PRODUCT) a
WHERE DENSE_RNK=3 and p_name='Laptop';

select *,
RANK() OVER( ORDER BY P_PRICE, p_desc,P_expiry_date ) AS RNK_ASC,
--RANK() OVER( ORDER BY P_PRICE DESC ) AS RNK_DESC,
DENSE_RANK() OVER( ORDER BY P_PRICE ) AS DENSE_RNK,
ROW_NUMBER() OVER( ORDER BY P_PRICE ) AS RN_NUMBER,
CUME_DIST() OVER( ORDER BY P_PRICE ) AS CUM_DIST
FROM
PRODUCT;

SELECT * FROM PRODUCT WHERE P_name='Laptop';


select *,
DENSE_RANK() OVER( ORDER BY P_PRICE DESC) AS RN FROM PRODUCT;

-- RANK WITH PARTITION BY CLAUSE


select *,
DENSE_RANK() OVER( PARTITION BY P_NAME ORDER BY P_EXPIRY_DATE ) AS
RN ,
CAST(CUME_DIST() OVER( PARTITION BY P_NAME ORDER BY P_PRICE ) AS NUMERIC(10,4))*100
AS "CD%"
FROM PRODUCT
;

SELECT * ,
DENSE_RANK() OVER (PARTITION BY p_name ORDER BY p_PRICE DESC) AS RN
FROM PRODUCT;

select * From product;

--FIRST_VALUE FUNCTION
select *,
FIRST_VALUE(p_desc) OVER( PARTITION BY P_NAME ORDER BY P_PRICE DESC ) AS RN FROM
PRODUCT
;

select *,
LAST_VALUE(p_price) OVER( PARTITION BY P_NAME ORDER BY P_PRICE DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS RN_PRICE,
LAST_VALUE(p_desc) OVER( PARTITION BY P_NAME ORDER BY P_PRICE DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS RN_desc FROM
PRODUCT
;

select *,
LAST_VALUE(p_price)
OVER( PARTITION BY P_NAME ORDER BY P_PRICE DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS RN
FROM
PRODUCT
;

--LAST_VALUE FUNCTION
select *, LAST_VALUE(p_price) OVER( PARTITION BY p_name ORDER BY P_PRICE DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS DESC_PRICE,
LAST_VALUE(p_desc) OVER( PARTITION BY p_name ORDER BY P_PRICE DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS DESC_LAST_VALUE,
FIRST_VALUE(p_price) OVER( PARTITION BY P_NAME ORDER BY P_PRICE DESC
) AS FV_PRICE,

FIRST_VALUE(p_desc) OVER( PARTITION BY P_NAME ORDER BY P_PRICE DESC


) AS FV_DESC
FROM PRODUCT;

--LEAD & LAG FUNCTIONS

select * from PRODUCT order by p_name, p_price desc;

select *,
LEAD(P_PRICE,1) OVER( PARTITION BY P_NAME ORDER BY P_PRICE DESC ) AS RN1,
LEAD(P_PRICE,2) OVER( PARTITION BY
P_NAME
ORDER BY P_PRICE DESC ) AS RN2
FROM PRODUCT;

select *, LAG(P_PRICE,1) OVER( PARTITION BY


P_NAME
ORDER BY P_PRICE ASC ) AS RN1,
LAG(P_PRICE,2) OVER( PARTITION BY
P_NAME
ORDER BY P_PRICE ASC ) AS RN2
FROM PRODUCT;

select *,
LAG(P_ID,1) OVER( ORDER BY p_id ) AS RN
FROM PRODUCT;

----NTILE
select *,
NTILE(2) OVER(
PARTITION BY P_NAME ORDER BY P_PRICE DESC) AS RN FROM PRODUCT;

select *, PERCENT_RANK()
OVER( PARTITION BY P_NAME ORDER BY P_PRICE ) AS RN,
CUME_DIST() OVER( PARTITION BY P_NAME ORDER BY P_PRICE ) AS CUM_DIST
FROM PRODUCT;

------CASE FUNCTION

SELECT *,
CASE
WHEN P_PRICE <500 THEN 'LOW'
WHEN P_PRICE BETWEEN 500 AND 1000 THEN 'MEDIUM'
ELSE 'HIGH'
END
AS P_PRICE_CATEGORY
FROM PRODUCT ;

SELECT *,IIF(P_PRICE>500 , 'HIGH', 'LOW/MEDIUM') as P_PRICE_category


from PRODUCT;

---------------USER DEFINED FUNCTIONS

--Scalar function : return single value


--Table Valued functin return a table

select * From PRODUCT;

create function sales_discount (@price float, @discount float)


RETURNS INT
AS
BEGIN
RETURN @price*(1-@discount)
END;

SELECT *, dbo.sales_discount(p_price,0.15) as new_discounted_price


from PRODUCT WHERE P_NAME='Laptop'
UNION
SELECT *, dbo.sales_discount(p_price,0.20) as new_discounted_price
from PRODUCT WHERE P_NAME='Watch';

--dropping a function
DROP function sales_discount;

--TABLE VALUED FUNCTION


--Find expensive PRODUCTs from product table
create function expensive_PRODUCTs (@price float)
RETURNS TABLE
AS
RETURN (
SELECT * FROM PRODUCT WHERE p_price>=@price
)
;

select * From dbo.expensive_PRODUCTs(355.55);

--drop function expensive_PRODUCTs;

USE TRAINING;

You might also like