This page provides exercises and solutions to help you practice SQL SELECT queries. 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.
Simple SQL SELECT Queries – Practice
- Create a query to display all the data from the Customers table.
- The following SELECT statement executes successfully (True / False)
FROM Customers SELECT last_name , first_name
- Create a query to display the internet package number, internet speed and monthly payment (Packages table).
- The following SELECT statement executes successfully (True / False)
SELECT strt_date, monthly_payment FROM packages
- The following SELECT statement executes successfully (True / False)
SeleCT last_NAME , fiRST_NamE , FROM customers
- Create a query to display the customer number, first name, last name, primary phone number, secondary phone number and package number (Customers table).
- Create a query to display first name, last name, join date, monthly discount, monthly discount after an addition of 20% and monthly discount after a reduction of 20% (Customers table).
- Create a query to display the package number, speed, strt_date (the date when the package became available), monthly payment, and monthly payment * 12, name the last column “YearlyPayment” (Packages table).
- Create a query to display the last name concatenated with the first name, separated by space, and main phone number concatenated with secondary phone number, separated by comma and space. Name the column heading FULL_NAME and CONTACT_DETAILS respectively. (Customers table)
- Create a query to display unique cities from the Customers table.
- Create a query to display unique states from the Customers table.
- Create a query to display unique combination of cities and states from Customers table.
- Create a query to display the last name concatenated with the state, separated by space. Name this column CUSTOMER_AND_STATE (Customers table)
- Create a query to display the first name, last name, monthly discount and city concatenated with street, separated by space. Name the column headings: FN, LN, DC and FULL_ADDRESS respectively (Customers table).
- Create a query to display unique monthly discounts in Customers table.
- Create a query to display unique packages (package_id) in Customers table.
Solutions – SQL Server
The following solutions apply to SQL Server, for solutions that apply to Oracle click here.
-- 1 SELECT * FROM customers -- 2 -- Error, FROM clause must follow SELECT Clause -- 3 SELECT pack_id , speed, monthly_payment AS 'MP' FROM packages -- 4 -- Query executes successfully : SELECT strt_date , monthly_paymentFROM packages -- 5 -- Error, comma cannot follow last column in the SELECT clause SeleCT last_NAME , fiRST_NamE , FROM customers -- 6 SELECT customer_id, first_name , last_name , main_phone_num , secondary_phone_num , pack_id FROM customers -- 7 SELECT first_name , last_name , join_date , monthly_discount , monthly_discount * 1.2 AS 'DC_N' , monthly_discount * 0.6 AS 'DC_L' FROM customers -- 8 SELECT pack_id , speed, strt_date, monthly_payment, monthly_payment * 12 AS 'Y_INCOME' FROM packages -- 9 SELECT first_name + ' ' + last_name AS 'FULL_NAME' , main_phone_num + ' , ' + secondary_phone_num AS 'CONTACT_DETAILS' FROM customers -- 10 SELECT DISTINCT city FROM customers -- 11 SELECT DISTINCT state FROM customers -- 12 SELECT DISTINCT state, city FROM customers -- 13 SELECT last_name + ' ' + state AS 'CUSTOMER_AND_STATE'FROM customers -- 14 SELECT first_name AS 'FN' ,last_name AS 'LN' , monthly_discount AS 'DC' ,city + ' ' + street AS 'FULL_ADDRESS' FROM customers -- 15 SELECT DISTINCT monthly_discount FROM customers -- 16 SELECT DISTINCT pack_idFROM customers
Solutions – Oracle
-- 1 SELECT * FROM customers -- 2 -- Error, FROM clause must follow SELECT Clause -- 3 SELECT pack_id , speed, monthly_payment AS "MP" FROM packages -- 4 -- Query executes successfully : SELECT strt_date , monthly_payment FROM packages -- 5 -- Error, comma cannot follow last column in the SELECT clause SeleCT last_NAME , fiRST_NamE , FROM customers -- 6 SELECT customer_id, first_name , last_name , main_phone_num , secondary_phone_num , pack_id FROM customers -- 7 SELECT first_name , last_name , join_date , monthly_discount , monthly_discount * 1.2 AS "DC_N" , monthly_discount * 0.6 AS "DC_L" FROM customers -- 8 SELECT pack_id , speed, strt_date, monthly_payment, monthly_payment * 12 AS "Y_INCOME" FROM packages -- 9 SELECT first_name || ' ' || last_name AS "FULL_NAME" , main_phone_num || ' , ' || secondary_phone_num AS "CONTACT_DETAILS" FROM customers -- 10 SELECT DISTINCT city FROM customers -- 11 SELECT DISTINCT state FROM customers -- 12 SELECT DISTINCT state, city FROM customers -- 13 SELECT last_name || ' ' || state AS "CUSTOMER_AND_STATE" FROM customers -- 14 SELECT first_name AS "FN" ,last_name AS "LN" , monthly_discount AS "DC" ,city || ' ' || street AS "FULL_ADDRESS" FROM customers -- 15 SELECT DISTINCT monthly_discount FROM customers -- 16 SELECT DISTINCT pack_id FROM customers