This page provides exercises and solutions to help you practice SQL Single-Row functions. These exercises are based on the ACDB database, and may be performed online or by running the sample schema scripts on your local database server. For additional exercises in other subjects, use this link.
 

String Functions

  1. Display the customer number, first name in lowercase and last name in uppercase for all customers whose customer number is in the range of 80 and 150.
  2. Generating Email Addresses
    1. For all customers – display the last name, first name and email address. The email address will be composed from the first letter of first name concatenated with three first letters of last name concatenated with the string “@mymail.com” (For example : Ram Kedem → RKED@mymail.com).
    2. For all customers – display the last name, first name and email address. The email address will be composed from the first letter of first name concatenated with three last letters of last name concatenated with the string “@mymail.com” (For example : Ram Kedem → RDEM@mymail.com).
  3. Display the last name and the length of the last name for all customers where last name’s length is greater than 9 characters.
  4. Phone Numbers :
    1. Display the first name, last name, main phone number and a new phone number using the REPLACE function. In the new phone number replace all occurrences of “515” with “$$$”.
    2. Display the first name, last name, main phone number and new phone number using the REPLACE function. In the new phone number replace all prefixes of “515” with “$$$” (only if the first 3 digits of the phone number contains the digits “515” replace those digits with “$$$”).

 

Numeric Functions

  1. From customers table, for all customers, display :
    1. first name.
    2. monthly discount.
    3. monthly discount after addition of 19.7%.
    4. monthly discount after addition of 19.7%, expressed as a whole number (ROUND).
    5. monthly discount after addition of 19.7%, round down to the nearest whole number (FLOOR).
    6. monthly discount after addition of 19.7%, round up to the nearest whole number (CEILING).

 

Date Functions

  1. From Customers table, for all customers, display the first name, join date, join date minus 10 days, join date plus one month and the date difference between join date and current date.
  2. Display the first name, birthdate and age for all customers whose older than 50.
  3. Display all the data from Customers table, for all customers whose birthdate is today.
  4. Display the first name, join date and the difference in years between join date and current date for all customers where today have passed exactly 5 years since they joined the company.

 

Conversion functions

  1. Display the first name concatenated with the join date, and last name concatenated with the monthly discount, for all customers. Solve this exercise using CAST.
  2. From Customers table, for all customers whose last name starts with a d or k, display:
    1. last name
    2. state in uppercase concatenated with customer number
    3. join date concatenated with birthdate
      Solve this exercise using CONVERT, and in the WHERE clause instead of using LIKE, try to define the filtering condition using SUBSTRING.

 

Null-Related Functions

  1. Phone numbers report:
    1. Display the first name, last name, birth date, main phone number and secondary phone number for all customers whose package number equals 27. Replace every null value in main phone number or in secondary phone number with ‘N/A’.
    2. Display the first name, last name, birth date, main phone number, secondary phone number for all customers who was born on 1972. Replace every null value in main phone number or in secondary phone number with ‘N/A’.

 

CASE Function

  1. From Customers table, for all customers, display the first name, last name, monthly discount and a discount grade based on these conditions :
    1. If the discount is between 0 and 10 – discount grade level is A.
    2. If the discount is between 11 and 20 – discount grade level is B.
    3. If the discount is between 21 and 30 – discount grade level is C.
    4. for any other value – discount grade level is D.

 

Solutions – SQL Server

These solutions apply to SQL Server, for solutions that apply to Oracle click here.

-- 1
SELECT customer_id, LOWER(first_name)  , UPPER(last_name)
FROM customers
WHERE customer_id BETWEEN 0 AND 150 

-- 2
-- A
SELECT first_name , last_name ,
			  LEFT(first_name , 1) + LEFT(last_name , 3) + '@mymail.com'
FROM customers 

-- B
SELECT first_name , last_name ,
			  LEFT(first_name , 1) + RIGHT(last_name , 3) + '@mymail.com'
FROM customers 

-- 3
SELECT last_name , LEN(last_name)
FROM customers
WHERE LEN(last_name) > 9 

-- 4
-- A
SELECT first_name , last_name , main_phone_num , REPLACE(main_phone_num , '515' , '$$$') AS 'New_Phone_Number'
FROM customers
WHERE main_phone_num LIKE '%515%'

-- B
 SELECT first_name , last_name , main_phone_num ,
				REPLACE(LEFT(main_phone_num, 3) , '515' , '$$$') + SUBSTRING(main_phone_num , 4 , 12) AS 'New_Phone_Number'
FROM customers
WHERE main_phone_num LIKE '%515%'

-- 5
SELECT	first_name ,
				monthly_discount,
				monthly_discount * 1.197 ,
				ROUND(monthly_discount * 1.197 , 2),
				FLOOR(monthly_discount * 1.197 ),
				CEILING(monthly_discount * 1.197 )
FROM customers 

-- 6
SELECT	first_name ,
				join_date,
				DATEADD(dd, -10 , join_date),
				DATEADD(mm , 1 , join_date),
				DATEDIFF(dd , join_date , getdate())
FROM customers 


-- 7
SELECT first_name , birth_date , DATEDIFF(yy , birth_date , getdate())
FROM customers
WHERE DATEDIFF(yy , birth_date , getdate()) > 50 

-- 8
SELECT first_name , birth_date
FROM customers
WHERE MONTH(birth_date) = MONTH(getdate())
              AND
			  DAY(birth_date) = DAY(getdate())

-- 9
SELECT first_name , join_date , DATEDIFF(yy , join_date , getdate())
FROM customers
WHERE DATEDIFF(yy , join_date , getdate()) = 5
			 AND
			  MONTH(join_date) = MONTH(getdate())
              AND
			  DAY(join_date) = DAY(getdate())

-- 10
SELECT first_name +  ' / '  + CAST(DAY(join_date) AS VARCHAR) ,
              last_name +  ' / '   + CAST(monthly_discount AS VARCHAR)
FROM customers

-- 11
SELECT  last_name,
				UPPER(state) + ' / ' + CONVERT(varchar , customer_id , 102) ,
				CONVERT(varchar , birth_date , 103) + ' / ' + CONVERT(varchar , join_date ,  103)
FROM customers
WHERE SUBSTRING(last_name , 1 , 1) IN ('D' , 'K')

-- 12
-- A
SELECT	first_name ,
				last_name ,
				ISNULL(main_phone_num ,	'N / A'),
				ISNULL(secondary_phone_num,	'N / A'),
				ISNULL(fax,	'N / A')
FROM customers
WHERE pack_id = 27 

-- B
SELECT	first_name ,
				last_name ,
				birth_date ,
				ISNULL(main_phone_num ,	'N / A'),
				ISNULL(secondary_phone_num,	'N / A'),
				ISNULL(fax,	'N / A')
FROM customers
WHERE pack_id = 27
AND
              year(birth_date) = 1972

-- 13
SELECT first_name , last_name , monthly_discount ,
			  CASE WHEN monthly_discount BETWEEN 0 AND 10 THEN 'A'
				WHEN monthly_discount BETWEEN 11 AND 20 THEN 'B'
				WHEN monthly_discount BETWEEN 21 AND 30 THEN 'C'
				ELSE 'D'
			 END AS 'Grades'
FROM customers 


 

Solutions – Oracle

-- 1
SELECT customer_id, LOWER(first_name)  , UPPER(last_name)
FROM customers
WHERE customer_id BETWEEN 0 AND 150 

-- 2
-- A
SELECT first_name , last_name ,
			  SUBSTR(first_name , 1,1) || SUBSTR(last_name , 3) || '@mymail.com'
FROM customers 

-- B
SELECT first_name , last_name ,
			  SUBSTR(first_name , 1,1) || SUBSTR(last_name , -3) || '@mymail.com'
FROM customers 

-- 3
SELECT last_name , LENGTH(last_name)
FROM customers
WHERE LENGTH(last_name) > 9 

-- 4
-- A
SELECT first_name , last_name , main_phone_num , REPLACE(main_phone_num , '515' , '$$$') AS "New_Phone_Number"
FROM customers
WHERE main_phone_num LIKE '%515%'

-- B
 SELECT first_name , last_name , main_phone_num ,
				REPLACE(SUBSTR(main_phone_num, 1, 3) , '515' , '$$$') 
				|| SUBSTR(main_phone_num , 4 , 12) AS "New_Phone_Number"
FROM customers
WHERE main_phone_num LIKE '%515%'

-- 5
SELECT	first_name ,
				monthly_discount,
				monthly_discount * 1.197 ,
				ROUND(monthly_discount * 1.197 , 2),
				TRUNC(monthly_discount * 1.197 ),
				CEIL(monthly_discount * 1.197 )
FROM customers 

-- 6
SELECT	first_name ,
				join_date,
				join_date - 10 ,
				ADD_MONTHS(1 , join_date),
				SYSDATE - join_date 
FROM customers 
-- 7
SELECT first_name , birth_date , EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM birth_date)
FROM customers
WHERE EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM birth_date) > 50 

-- 8
SELECT first_name , birth_date
FROM customers
WHERE EXTRACT(MONTH FROM birth_date) = EXTRACT(MONTH FROM SYSDATE)
              AND
			  EXTRACT(DAY FROM birth_date) = EXTRACT( DAY FROM SYSDATE)

-- 9
SELECT first_name , join_date , EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM join_date)
FROM customers
WHERE EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM join_date) = 5
			 AND
			 EXTRACT(MONTH FROM SYSDATE) = EXTRACT(MONTH FROM join_date)
             AND
			 EXTRACT( DAY FROM SYSDATE) = EXTRACT( DAY FROM join_date)

-- 10
SELECT first_name ||  ' / '  || join_date ,
              last_name ||  ' / '   || monthly_discount
FROM customers

-- 11
SELECT  last_name,
				UPPER(state) || ' / ' || customer_id  ,
				birth_date || ' / ' || join_date
FROM customers
WHERE SUBSTR(last_name , 1 , 1) IN ('D' , 'K')

-- 12
-- A
SELECT	first_name ,
				last_name ,
				NVL(main_phone_num ,	'N / A'),
				NVL(secondary_phone_num,	'N / A'),
				NVL(fax,	'N / A')
FROM customers
WHERE pack_id = 27 

-- B
SELECT	first_name ,
				last_name ,
				birth_date ,
				NVL(main_phone_num ,	'N / A'),
				NVL(secondary_phone_num,	'N / A'),
				NVL(fax,	'N / A')
FROM customers
WHERE pack_id = 27
AND
              year(birth_date) = 1972

-- 13
SELECT first_name , last_name , monthly_discount ,
			  CASE WHEN monthly_discount BETWEEN 0 AND 10 THEN 'A'
				WHEN monthly_discount BETWEEN 11 AND 20 THEN 'B'
				WHEN monthly_discount BETWEEN 21 AND 30 THEN 'C'
				ELSE 'D'
			 END AS "Grades"
FROM customers