[go: up one dir, main page]
Scribd
Upload
38 views8 pages

Aggregate and Mod3 SQL

This document shows SQL commands for altering tables, performing aggregation functions, working with dates and strings, and defining user functions. It inserts sample data, performs aggregation on the product table, demonstrates date functions, and defines scalar and table valued user functions.

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
38 views8 pages

Aggregate and Mod3 SQL

This document shows SQL commands for altering tables, performing aggregation functions, working with dates and strings, and defining user functions. It inserts sample data, performs aggregation on the product table, demonstrates date functions, and defines scalar and table valued user functions.

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/ 8

--Module 2

USE TRAINING;
--INSERT INTO PRODUCT VALUES(44,'PRODUCT1',25);
SELECT * FROM PRODUCT;
---
--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
ALTER TABLE CUST_ORDER
DROP
DF__CUST_ORDE__o_dat__45F365D3;

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

select * From PRODUCT;

select avg(
CAST(p_price AS INT)
) 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
;
--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_DESC,MIN(P_PRICE) as MAX_PRICE


FROM PRODUCT
GROUP BY P_DESC
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 MIN_PRICE
FROM PRODUCT
GROUP BY P_NAME
;

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

--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,2)) as SUM_PRICE
FROM PRODUCT
GROUP BY P_NAME ;

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


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

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

--ABS Function
SELECT ABS(-1)

--Log function
SELECT LOG10(p_PRICE) from PRODUCT

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

--CEILING FUNCTION
SELECT CEILING(1.2)

--FLOOR FUNCTION
SELECT FLOOR(1.2)
--ROUND FUNCTION
SELECT ROUND(1.22234,3)

--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(GETDATE()) AS NEW_DATE

SELECT SWITCHOFFSET(
GETDATE(),'+10:00'
) 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
--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('NEW YORK',2,1500);
--REPLACE FUNCTION
SELECT replace ('NEW YORK YORK','23','');
SELECT replace ('KEEP','EE','ET');

SELECT *, UPPER(EMAIL) AS UPPER_EMAIL FROM CUSTOMER;


--CONCAT FUNCTION
SELECT CONCAT('NEW', 'YORK');
SELECT C_NAME,EMAIL,CONCAT(C_NAME,' ',EMAIL) AS NAME_EMAIL FROM CUSTOMER;
--CONCAT_WS
SELECT CONCAT_WS(',','NEW', ' YORK');
--ASCII
SELECT ASCII('z');
--LENGTH
SELECT LEN('NEW YORK');
SELECT *, LEN(EMAIL) AS LEN_EMAIL FROM CUSTOMER;
-------------------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 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 *,
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 * FROM PRODUCT_LAPTOP;


SELECT *FROM PRODUCT_WATCH;

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

SELECT * FROM PRODUCT_RANK;

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

--LAST_VALUE FUNCTION
select *,
LAST_VALUE(p_price) OVER( PARTITION BY p_name ORDER BY P_PRICE
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS LV_PRICE_RANGE,
LAST_VALUE(p_price) OVER( PARTITION BY P_NAME ORDER BY P_PRICE
) AS LV_PRICE
FROM PRODUCT;

--LEAD & LAG FUNCTIONS

select *, LEAD(P_desc,2) OVER( PARTITION BY


P_NAME
ORDER BY P_PRICE ) AS RN1_DESC,
LEAD(P_PRICE,2) OVER( PARTITION BY
P_NAME
ORDER BY P_PRICE ) AS RN2_PRICE
FROM PRODUCT;

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


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

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

----NTILE
select *, NTILE(3) 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 FROM PRODUCT;

------CASE FUNCTION

SELECT *,
CASE
WHEN P_PRICE <500 THEN '0-500'
WHEN P_PRICE BETWEEN 500 AND 1000 THEN '500-1000'
ELSE '>1000'
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_desc='Samsung'
UNION
SELECT *,
dbo.sales_discount(p_price,0.10) as new_discounted_price
from PRODUCT WHERE
p_desc='Lenovo' ;

UNION
SELECT *, dbo.sales_discount(p_price,0.25) 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_PRODUCTs1 (@price float)


RETURNS TABLE
AS
RETURN (SELECT * FROM PRODUCT WHERE p_price>=@price)
;

select * from PRODUCT;


select * From dbo.expensive_PRODUCTs(1300);
--drop function expensive_PRODUCTs;

You might also like