Demos
| select the number of lines in table mytable | select count(*) from mytable |
| get the number of distinct values in the column town of mytable | select 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 state | SELECT 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 matao | where 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 |
| 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 table | select 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 state | select 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 |
| not forget in grouping | when 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 |
| set phone_number=123 in column customer | update customers set phone_number=123 |
| update : set more than one column | update mytable set col1='value1' ,col2=123 |
| double value of revenue in salesperson table | update salespersons set revenue=revenue * 2 |
| insert row in customers table | insert into customers values ( ‘val1’, ‘val2’, 123 ) |
| table customers has id autoincremented + column a and column b. Insert a row in this table c | insert into customers (column a, column b) values ( 'val1', 'val2') |
| delete rows from table revenues where year is 2010 | delete from revenues where year=2010 |
| create a table | CREATE TABLE salesperson ( ..... ) |
| column id incrementing automatically and not taking null values and being a primary key | ID 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 value | columnname 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 year | ALTER TABLE revenues ADD month int AFTER year |
| Change column name from oldname to newname in mytable. Column is of type int | ALTER 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 values | ALTER TABLE mytable CHANGE mycolumn mycolumn VARCHAR(70) NOT NULL |
| Take off column revenue_year from table revenues | ALTER TABLE revenues DROP revenu_year |
| Change the name of a table from revenues to sales_revenue | ALTER TABLE revenues RENAME TO sales_revenu |
| Create an index for the table clients on column first_name and last_name | create index ix_clients_1 on clients(first_name, last_name) |
| Create an unique key for the table clients on column email_address | create unique index ux_clients_email_address on clients (email_address) |
| Add a primary key to the table clients on column id | alter table clients add primary key(id) |
| Characteristics of indexes | Faster search, does not reject duplicates, does not reject null values, can be more than one per table |
| Characteristics of unique keys | Faster search, rejects duplicates, does not reject null values, can be more than one per table |
| Characteristics of primary keys | Faster 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 key | Without 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 salespersons | ALTER TABLE clients ADD FOREIGN KEY (salesperson_id) REFERENCES salespersons(id); |
| What does a foreign key prevent to do | Inserting 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 well | add “ON DELETE CASCADE” at the end of the statement creating the foreign key |
| Create a user on the same server as you | CREATE USER 'userlogin'@'localhost' IDENTIFIED BY 'helloThisIsMyPassword!' |
| Grant all privileges to user arjunj on the local machine to the table clients in the marketing database | GRANT ALL ON marketing.clients TO 'arjunj'@'localhost' |
| Grant all privileges to user arjunj on the local machine to all tables in the marketing database | GRANT 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 privilege | REVOKE INSERT ON marketing.clients FROM 'arjunj'@'localhost' |
| Delete the user | DROP USER 'arjunj'@'localhost' |
| Rename the user | RENAME USER 'arjunj'@'localhost' TO 'jarjun'@'localhost' |
| Reset the password | SET PASSWORD FOR 'jarjun'@'localhost'=PASSWORD('helloWorld123'); |
| 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 year | select 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 letter | select 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 3 | select cast(transaction_id as char(3)) trans_id_char from sales |
| Concatenate a column with a string | select 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 long | select lpad(mycolumn,6,'x') |
| Add several times the character ‘x’ at the end of the string until the string becomes 6 characters long | select lpad(mycolumn,6,'x') |
| Round-up with 2 decimals left after rounding | select round(mycolumn,2) |
| Show for each row in sales the difference between now and the transaction date | select 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 day | update 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 day | update 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 1 | select 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 sale | select 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 times | select 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 |