Tutorial

SQL tutorial

November 11th, 2019 - Vincent Luciani

The table below will help you to rehearse SQL's most useful functionalities.




Select, Count, Order


select the number of lines in table mytableselect count(*) from mytable
get the number of distinct values in the column town of mytableselect count(distinct town) from mytable
order results in alphabetical order, from a to z (the letter “a” appearing on the top of the result, “z” on the bottom)order by mycolumn asc
order results in alphabetical order, from z to a (the letter “z” appearing on the top of the result, “a” on the bottom)order by mycolumn desc
Table clients has columns state and contain one line per client
Show the states where your company have clients, showing the states with the most clients on the top.
SELECT state, COUNT(*)
FROM clients
GROUP BY state
ORDER BY COUNT(*) desc
ordered the state by the total revenue of sales transactions per stateSELECT state, sum(revenue)
FROM sales
GROUP BY state
ORDER BY sum(revenue) desc
list of customers which name starts with “ra”.where last_name like ‘ra%’
condition: last name is rao or mataowhere last_name in ("rao","matao")
condition: last name starts with "rr" then followed by two characters, then "o", then anything where last_name like "r__o%"
column value not equal to x <> x
column value is between x and y (x and y are numbers)between x and y

Joins


table 1 has column1 and column2
table 2 has columna and columnb
join both table using column1 and columna
show only when column1 is matching columna
select t1.column1 as mycolumn1,
t1.column2 as mycolumn2,
t2.columna as mycolumna,
2t.columnb as mycolumnb
from table1 as t1
inner join table2 as t2
on t1.column1=t2.columna
display, for each row in table sales, the revenue of each transaction divided by the maximum revenue of all transactions in the tableselect s1.revenue/s2.max_revenue from sales s1 inner join ( select max(revenue) as max_revenue from sales) s2
display, for each row in table sales, the revenue of each transaction divided by the maximum revenue of all transactions in the same stateselect s1.revenue/s2.max_revenue from sales s1 inner join ( select max(revenue) as max_revenue, state from sales) s2 where s1.state=s2.state
table 1 has column1 and column2
table 2 has columna and columnb
join both table using column1 and columna
show all entries from table1, even when no match
select t1.column1 as mycolumn1,
t1.column2 as mycolumn2,
t2.columna as mycolumna,
2t.columnb as mycolumnb
from table1 as t1
left outer join table2 as t2
on t1.column1=t2.columna
table 1 has column1 and column2
table 2 has columna and columnb
join both table using column1 and columna
show all entries from table2, even when no match
select t1.column1 as mycolumn1,
t1.column2 as mycolumn2,
t2.columna as mycolumna,
2t.columnb as mycolumnb
from table1 as t1
right outer join table2 as t2
on t1.column1=t2.columna
table 1 has column1 and column2
table 2 has columna and columnb
join both table using column1 and columna
show all entries from table1 and table2, even when no match
select t1.column1 as mycolumn1,
t1.column2 as mycolumn2,
t2.columna as mycolumna,
2t.columnb as mycolumnb
from table1 as t1
full outer join table2 as t2
on t1.column1=t2.columna
workaround when full outer join not working""select t1.column1 as mycolumn1,
t1.column2 as mycolumn2,
t2.columna as mycolumna,
2t.columnb as mycolumnb
from table1 as t1
left outer join table2 as t2
on t1.column1=t2.columna
union
select t1.column1 as mycolumn1,
t1.column2 as mycolumn2,
t2.columna as mycolumna,
2t.columnb as mycolumnb
from table1 as t1
right outer join table2 as t2
on t1.column1=t2.columna

group by


not forget in groupingwhen grouping by a column, must select this column + an agregator of any other column. Never select another column than the ones used in the grouping without any agregator
grouping: show sum of revenue per state in table sales
take into account only sales with year=2014 in the results
select state, sum(revenue)
from sales
where year=2014
group by state
grouping: show sum of revenue per state in table sales
show only the regions for which the total revenue is over 50000
select state, sum(revenue)
from sales
group by state
having sum(revenue)>50000

Update Insert Delete


set phone_number=123 in column customerupdate customers
set
phone_number=123
update : set more than one columnupdate mytable
set col1='value1'
,col2=123
double value of revenue in salesperson tableupdate salespersons
set revenue=revenue * 2
insert row in customers tableinsert into customers
values (
‘val1’,
‘val2’,
123
)
table customers has id autoincremented + column a and column b. Insert a row in this table cinsert into customers (column a, column b)
values (
'val1',
'val2')
delete rows from table revenues where year is 2010delete from revenues
where year=2010

Create and Modify Tables


create a table CREATE TABLE salesperson
(
.....
)
column id incrementing automatically and not taking null values and being a primary keyID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
Numbers that cannot have more than 7 digits in total. From these 7 digits, 2 at the most can be after the decimal point (“.”)columnname decimal(7,2),
30 characters maximum, no null valuecolumnname varchar(30) NOT NULL,
20 characters maximum, default value is "approved"columnname varchar(20) DEFAULT 'approved'
Add column month of type int in table revenues ALTER TABLE revenues
ADD month int
Same as above with a certain position. Column month must be after the column yearALTER TABLE revenues
ADD month int AFTER year
Change column name from oldname to newname in mytable. Column is of type intALTER TABLE mytable
CHANGE oldname newname int
Change the property of column mycolumn of table mytable to a column of maximum 70 characters that cannot take null valuesALTER TABLE mytable
CHANGE mycolumn mycolumn VARCHAR(70) NOT NULL
Take off column revenue_year from table revenuesALTER TABLE revenues
DROP revenu_year
Change the name of a table from revenues to sales_revenueALTER TABLE revenues
RENAME TO sales_revenu

Indexes, Keys


Create an index for the table clients on column first_name and last_namecreate index ix_clients_1
on clients(first_name, last_name)
Create an unique key for the table clients on column email_addresscreate unique index ux_clients_email_address
on clients (email_address)
Add a primary key to the table clients on column idalter table clients
add primary key(id)
Characteristics of indexesFaster search, does not reject duplicates, does not reject null values, can be more than one per table
Characteristics of unique keysFaster search, rejects duplicates, does not reject null values, can be more than one per table
Characteristics of primary keysFaster search, faster search of ranges than with other keys, rejects duplicates, rejects null values, can be only one per table
Some DBMSes like mySQL order the rows physically in the table using the column(s) related to the primary key. As a result, we will retrieve a range of value in this (these) column(s) faster (for example with 'between 10 and 100').
Difference in order between primary keys and no primary keyWithout primary key, the physical order of rows in the table depends on the order in which the rows where inserted. With the primary key, the physical order is done based on the values in the column used as primary key
Add a foreign key on column salesperson_id from table clients. This column is related to the column id in the table salespersonsALTER TABLE clients
ADD FOREIGN KEY (salesperson_id) REFERENCES salespersons(id);
What does a foreign key prevent to doInserting a row in the child table with a value in the foreign key not corresponding to any value in the parent key.
Deleting a row in the parent table matching at least one row in the child table.
Add a rule that if a parent row is deleted, all corresponding child rows are deleted as welladd “ON DELETE CASCADE” at the end of the statement creating the foreign key

User Administration


Create a user on the same server as youCREATE USER 'userlogin'@'localhost' IDENTIFIED BY 'helloThisIsMyPassword!'
Grant all privileges to user arjunj on the local machine to the table clients in the marketing databaseGRANT ALL ON marketing.clients TO 'arjunj'@'localhost'
Grant all privileges to user arjunj on the local machine to all tables in the marketing databaseGRANT ALL ON marketing.* TO 'arjunj'@'localhost'
Grant the privilege to select information from the table clients GRANT SELECT ON marketing.clients TO 'arjunj'@'localhost'
Remove the privilegeREVOKE INSERT ON marketing.clients FROM 'arjunj'@'localhost'
Delete the userDROP USER 'arjunj'@'localhost'
Rename the userRENAME USER 'arjunj'@'localhost' TO 'jarjun'@'localhost'
Reset the passwordSET PASSWORD FOR 'jarjun'@'localhost'=PASSWORD('helloWorld123');

Advanced Select


Show the string “high” for revenues over 30000, “medium” for revenues between 20000 and 30000 and “low” when lower than 20000. We display this information together with the city and the yearselect city,
year,
case when revenue>30000 then 'high'
when revenue between 20000 and 30000 then 'medium'
else 'low'
end as level
from sales
Show only letters starting from the second letter in the column city, and show only three letterselect mid(city,2,3) as test from salesselect mid(city,2,3) as test from sales
Values in transaction_id are of type integer and you need to convert these values to strings of maximum size 3select cast(transaction_id as char(3)) trans_id_char from sales
Concatenate a column with a stringselect concat("string",columnname)
Take off all the spaces on the right hand side of the string.select rtrim(columnname)
Take off all the spaces on the right hand side of the string.select ltrim(columnname)
Add several times the character ‘x’ at the beginning of the string until the string becomes 6 characters longselect lpad(mycolumn,6,'x')
Add several times the character ‘x’ at the end of the string until the string becomes 6 characters longselect lpad(mycolumn,6,'x')
Round-up with 2 decimals left after roundingselect round(mycolumn,2)
Show for each row in sales the difference between now and the transaction dateselect now(),transation_date,DATEDIFF( now(), transaction_date ) from sales
We want to update the products’ price. The update is executed on a product only if it happened at least once that this product was sold more than 80 times in a dayupdate products
set product_price=product_price + 2
where id in
(
select distinct product_id from sales
where quantity > 80
)
Update on a product only if it happened at least twice that this product was sold more than 80 times in a dayupdate products p
set p.product_price=p.product_price + 1
where
(
select count(*) from sales s
where s.product_id=p.id
and s.quantity > 80
) >=2
Backup value of table1 into table2, column1 in table2 being an autoincrement. Table2 has also column2 (equal to value of columna in table1 multiplied by 1.3) and column3 (always equal to ""hello"")insert into table2
(column2,column3)
select
columna*1.3,
‘hello’
from table1
In a test environment, show product names for which the number of rows in sales is strictly greater than 1select p.product_name
from products p
where (
select count(*)
from sales s
where
p.id=s.product_id
) > 1
In a test environment, show for each product name the latest saleselect
p.product_name,
p.product_price,
s.quantity,
s.sales_date
from products p,
sales s
where
p.id=s.product_id
and s.sales_date=
(
select
max(sales_date)
from sales s2
where
s.product_id=
s2.product_id
)
Show for each product, the product name and the number of days during which we sold the product more than 45 timesselect
p.product_name,
(
select count(*)
from sales s
where
p.id=s.product_id
and s.quantity > 45
) as big_sales_count
from products p
This website is non commercial and does not register any of your personal data (no cookie, no statistics). The site and its content are delivered on an "as-is" and "as-available basis".
image/svg+xml