SQL rehearsal tool

Click on “Hide Answers”. Then on each line, try to answer the question on the left. If you do not remember, click on the same line on the “Show Answer” button. This makes the answer appear on the right hand side. Once you read the answer on the right hand side, click on “Hide Answer” to go on rehearsing.

 
 

Rehearse SQL Select

Show the columns first_name, last_name and phone_number in table clients, only when state is California
select first_name, last_name, phone_number
from clients
where state=’California’

 
 
Show values of the column city in revenues, showing each city only once
select distinct city from revenues
 
 
 
Get the number of rows in the table revenues
select count(*) from revenues
 
 
 
Get the number of cities in the table revenues (each city may appear only once)
select count(distinct city) from revenues
 
 
 
Show all columns in the table clients, showing first_name Gore on top of Miller
select *
from clients
order by last_name asc
 
 
 
Show all columns in the table clients, showing first_name Miller on top of Gore
select *
from clients
order by last_name desc
 
 
 
Show the columns first_name and last_name in the table clients, showing only the clients whose last_name starts with “ra”.
select first_name,last_name
from clients
where last_name like ‘ra%’
 
 
 
Show the columns first_name and last_name in the table clients, showing only the clients whose last_name ends with “ra”.
select first_name,last_name
from clients
where last_name like ‘%ra’
 
 
 
Show the first_name of the table salesperson, choosing the lines for which total_sales is greater or equal to 100000
select first_name
from salesperson
where total_sales >= 100000
 
 
 
Show the first_name and last_name of the table salesperson, choosing the lines for which total_sales is strictly smaller to 200000.
select first_name, last_name
from salesperson
where total_sales < 200000
 
 
 

Rehearse SQL Join

Show first_name and last_name of clients (clients table) side by side with the first_name and last_name of the corresponding salesperson (salespersons table). The id of the salesperson must match the column salesperson_id in the clients table.
select cl.first_name as clientfirstname,
cl.last_name as clientlastname,
sa.first_name as salesfirstname,
sa.last_name as saleslastname
from clients as cl
inner join salespersons as sa
on cl.salesperson_id = sa.id
 
 
 
Same as above, but do not show salesperson without any client.
select cl.first_name as clientfirstname,
cl.last_name as clientlastname,
sa.first_name as salesfirstname,
sa.last_name as saleslastname
from clients as cl
left outer join salespersons as sa
on cl.salesperson_id = sa.id
 
 
 
Same as above, but do not show clients not assigned to any salesperson.
select cl.first_name as clientfirstname,
cl.last_name as clientlastname,
sa.first_name as salesfirstname,
sa.last_name as saleslastname
from clients as cl
right outer join salespersons as sa
on cl.salesperson_id = sa.id
 
 
 
Same as above, but show clients without matching salespersons as well as salesperson without matching clients.
select cl.first_name as clientfirstname,
cl.last_name as clientlastname,
sa.first_name as salesfirstname,
sa.last_name as saleslastname
from clients as cl
full outer join salespersons as sa
on cl.salesperson_id = sa.id
 
 
 

Rehearse SQL Group By

Show for each state in table sales the name of the state, the sum of revenues, the number sales, the max, min and average revenue.
select state,
sum(revenue),
count(*),
max(revenue),
min(revenue),
avg(revenue)
from sales
group by state
 
 
 
Show state and sum of revenues for each state, taking into account only revenues for which year = 2014.
select state, sum(revenue)
from sales
where year=2014
group by state
 
 
 
Show state and sum of revenues for each state having sum of revenues strictly greater than 50000
select state, sum(revenue)
from sales
group by state
having sum(revenue)>50000
 
 
 

Rehearse SQL Update

Change phone number of customer (table customers) whose first_name is Aamir and last_name is Rao to 4934455599988.
update customers
set
phone_number=4934455599988
where first_name = ‘Aamir’
and last_name = ‘Rao’
 
 
 
Change email of customer (table customers) whose customer_id is 9987 to [email protected] In the same statement change phone number to 34455599988.
update customers
set email=’[email protected]
,phone_number=34455599988
where customer_id = 9987
 
 
 

Rehearse SQL Insert

Insert a row in the table customers. The row contains the following values: ‘Arjun’,
‘Sen’,
491234545456 and ‘[email protected]
insert into customers
values (
‘Arjun’,
‘Sen’,
491234545456,
[email protected]
)
 
 
 
 
Share on: