SQL Examples – How to use Functions

: DATE/TIME FUNCTIONS
: List all employess born in 1966
: MS Access and SQL Server
SELECT EMP_LNAME, EMP_FNAME, EMP_DOB, YEAR(EMP_DOB) AS YEAR
FROM EMPLOYEE
WHERE YEAR(EMP_DOB) = 1966;
: Oracle
SELECT EMP_LNAME, EMP_FNAME, EMP_DOB, TO_CHAR(EMP_DOB,'YYYY') AS YEAR
FROM EMPLOYEE
WHERE TO_CHAR(EMP_DOB,'YYYY') = '1966';
: List all employess born in November
: MS Access and SQL Server
SELECT EMP_LNAME, EMP_FNAME, EMP_DOB, MONTH(EMP_DOB) AS MONTH
FROM EMPLOYEE
WHERE MONTH(EMP_DOB)= 11;
: Oracle
SELECT EMP_LNAME, EMP_FNAME, EMP_DOB, TO_CHAR(EMP_DOB,'MM') AS MONTH
FROM EMPLOYEE
WHERE TO_CHAR(EMP_DOB,'MM') = '11';
: List all employees born in the 14th day of the month
: MS Access and SQL Server
SELECT EMP_LNAME, EMP_FNAME, EMP_DOB, DAY(EMP_DOB) AS DAY
FROM EMPLOYEE	
WHERE DAY(EMP_DOB)=14;
: Oracle
SELECT EMP_LNAME, EMP_FNAME, EMP_DOB, TO_CHAR(EMP_DOB,'DD') AS DAY
FROM EMPLOYEE
WHERE TO_CHAR(EMP_DOB,'DD') = '14';
: Oracle
: TO_DATE
: List the approximate age of the employees on the company's 10th anniversary date (11/25/2004)
SELECT EMP_LNAME, EMP_FNAME, EMP_DOB, '11/25/2004' AS ANIV_DATE, (TO_DATE('11/25/1994','MM/DD/YYYY') - EMP_DOB)/365 AS YEARS 
FROM EMPLOYEE
ORDER BY YEARS;
: How many days between thanksgiving and Christmas 2004?
SELECT TO_DATE('2004/12/25','YYYY/MM/DD') - TO_DATE('NOVEMBER 25, 2004','MONTH DD, YYYY') 
FROM DUAL;
: How many days are left to Christmas 2004?:
SELECT TO_DATE('25-Dec-2004','DD-MON-YYYY') - SYSDATE
FROM DUAL;
: List all products with their expiration date (two years from the purchase date).
SELECT P_CODE, P_INDATE, ADD_MONTHS(P_INDATE,24)
FROM PRODUCT
ORDER BY ADD_MONTHS(P_INDATE,24);
: List all employees that were hired within the last 7 days of a month.
SELECT EMP_LNAME, EMP_FNAME, EMP_HIRE_DATE
FROM EMPLOYEE
WHERE EMP_HIRE_DATE >= LAST_DAY(EMP_HIRE_DATE)-7;
: === NUMERIC FUNCTIONS
: ABS
: List absolute values
SELECT 1.95, -1.93, ABS(1.95), ABS(-1.93) FROM DUAL;
: ROUND
: List the product prices rounded to one and zero decimal places
SELECT P_CODE, P_PRICE, ROUND(P_PRICE,1) AS PRICE1, ROUND(P_PRICE,0) AS PRICE0
FROM PRODUCT;
: TRUNC
: List the product price rounded to one and zero decimal places and truncated.
SELECT P_CODE, P_PRICE, 
ROUND(P_PRICE,1) AS PRICE1,
ROUND(P_PRICE,0) AS PRICE0,
TRUNC(P_PRICE,0) AS PRICEX
FROM PRODUCT;
: CEIL AND FLOOR
: List the product price, smallest integer greater than or equal to the product price, and the largest integer equal or less than the product price.
SELECT P_PRICE, CEIL(P_PRICE), FLOOR(P_PRICE)
FROM PRODUCT;
: === STRING FUNCTIONS
: CONCATENATION
List all employee names (concatenated):
SELECT EMP_LNAME || ', ' || EMP_FNAME AS NAME
FROM EMPLOYEE;
: UPPER 
: List all employee names in all capitals (concatenated)
SELECT UPPER(EMP_LNAME) || ', ' || UPPER(EMP_FNAME) AS NAME
FROM EMPLOYEE;
: LOWER
: List all employee names in all lowercase (concatenated)
SELECT LOWER(EMP_LNAME) || ', ' || LOWER(EMP_FNAME) AS NAME
FROM EMPLOYEE;
: SUBSTR
: List the first three characters of all employee’s phone numbers
SELECT EMP_PHONE, SUBSTR(EMP_PHONE,1,3)
FROM EMPLOYEE;
: Generate a list of employee user ids using the first character of first name and first 7 characters of last name
SELECT EMP_FNAME, EMP_LNAME, 
SUBSTR(EMP_FNAME,1,1) || SUBSTR(EMP_LNAME,1,7)
FROM EMPLOYEE;
: LENGTH
List all employee’s last names and the length of their names, ordered descended by last name length
SELECT EMP_LNAME, LENGTH(EMP_LNAME) AS NAMESIZE
FROM EMPLOYEE
ORDER BY NAMESIZE DESC;
: === CONVERSION FUNCTIONS
: TO_CHAR
: List all product prices, quantity on hand and percent discount and total inventory cost using formatted values
SELECT P_CODE, TO_CHAR(P_PRICE,'$999.99') AS PRICE,
TO_CHAR(P_ONHAND,'9,999.99') AS QUANTITY,
TO_CHAR(P_DISCOUNT, '0.99') AS DISC,
TO_CHAR(P_PRICE*P_ONHAND, '$99,999.99') AS TOTAL_COST
FROM PRODUCT;
: List all employees date of birth using different date formats
SELECT EMP_LNAME, EMP_DOB, TO_CHAR(EMP_DOB, 'DAY, MONTH DD, YYYY') AS "DATE OF BIRTH"
FROM EMPLOYEE;
SELECT EMP_LNAME, EMP_DOB, TO_CHAR(EMP_DOB, 'YYYY/MM/DD') AS "DATE OF BIRTH"
FROM EMPLOYEE;
: TO_NUMBER
: USEFUL WHEN IMPORTING DATA IN TEXT FILES TO A DATABASE
SELECT TO_NUMBER('-123.99', 'S999.99'), TO_NUMBER(' 99.78-','B999.99MI')
FROM DUAL;
: === SEQUENCES
CREATE SEQUENCE CUS_CODE_SEQ START WITH 20010 NOCACHE;
CREATE SEQUENCE INV_NUMBER_SEQ START WITH 4010 NOCACHE;
SELECT * FROM USER_SEQUENCES;
INSERT INTO CUSTOMER
VALUES (CUS_CODE_SEQ.NEXTVAL, 'Connery', 'Sean', NULL, '615', '898-2007', 0.00);
INSERT INTO INVOICE
VALUES (INV_NUMBER_SEQ.NEXTVAL, 20010, SYSDATE);
INSERT INTO LINE
VALUES (INV_NUMBER_SEQ.CURRVAL, 1,'13-Q2/P2', 1, 14.99);
INSERT INTO LINE
VALUES (INV_NUMBER_SEQ.CURRVAL, 2,'23109-HB', 1, 9.95);
COMMIT;
SELECT * FROM CUSTOMER;
SELECT * FROM INVOICE;
SELECT * FROM LINE;
DELETE FROM INVOICE WHERE INV_NUMBER = 4010;
DELETE FROM CUSTOMER WHERE CUS_CODE = 20010;
COMMIT;
: USE DROP SEQUENCE sequence_name to delete a sequence.
DROP SEQUENCE CUS_CODE_SEQ;
DROP SEQUENCE INV_NUMBER_SEQ;
: CREATE SEQUENCES AGAIN AS THEY WILL BE USED IN STORED PROCEDURES
CREATE SEQUENCE CUS_CODE_SEQ START WITH 20010 NOCACHE;
CREATE SEQUENCE INV_NUMBER_SEQ START WITH 4010 NOCACHE;

Leave a Reply

Your email address will not be published. Required fields are marked *