SQL SELECTION

Question 1

How to select all data from the table?

Answer 1

SELECT * FROM my_table_name;

Question 2

How to select only rows which are equal by the condition?

Answer 2

# use =
SELECT * FROM my_table_name WHERE city = 'Berlin';

Question 3

How to select only rows which aren’t equal by the condition?

Answer 3

# use <>
SELECT * FROM my_table_name WHERE city <> 'Berlin';

Question 4

How to select only specific columns from the table? ( 2 ways)

Answer 4

define it explicitly instead of using *

SELECT city, country FROM customers;
# another way
SELECT customers.city, customers.country FROM customers;

Question 5

How to select values by many conditions?

Answer 5

# use OR / AND
SELECT * FROM Products WHERE price = 18 OR price = 19;

SELECT * FROM Products WHERE price = 18 AND name = ‘nik’;

Question 6

Is it possible to return a result that is less or more than the given value?

Answer 6

Yes, use < or > or <=

SELECT * FROM Products WHERE price < 18

Question 7

How to select only NULL/ not NULL values?

Answer 7

SELECT * FROM Products WHERE price IS NOT NULL;

SELECT * FROM Products WHERE price IS NULL;

Question 8

How to select values by the last or first symbols?( 2 ways )

Answer 8

SELECT * FROM Customers WHERE city LIKE ‘B%n’
# => will return Berlin
SELECT * FROM Customers WHERE city LIKE ‘Be_n’
# => will return Bern
# ‘%’ - for many symbols, ‘_’ -> for only one symbol

Question 9

How to select values from the specific range?

Answer 9

# use BETWEEN
SELECT * FROM Products WHERE price BETWEEN 18 AND 22;

Question 10

How to select values which are included in the array? Or not included?

Answer 10

use IN or NOT IN
SELECT * FROM Products WHERE name IN (‘nik’, ‘alex’)
SELECT * FROM Products WHERE name NOT IN (‘nic’,’alex’)

Question 11

How to show a list of all databases?

Answer 11

SHOW DATABASES;

Question 12

How to set the order of selection?

Answer 12

SELECT * FROM Products ORDER BY price;

Question 13

How to set the order for several rows?

Answer 13

SELECT * FROM Products ORDER BY price, unit;

Question 14

How to change order direction?

Answer 14

ASC - from low to up
# DESC - from up to low
SELECT * FROM Products ORDER BY price ASC;
SELECT * FROM Products ORDER BY price DESC;

Question 15

How to get sum of the row?

Answer 15

SELECT SUM(price) FROM Products;

Question 16

How to get sum fo the row by a group of values?

Answer 16

SELECT productName, SUM(price) FROM Products GROUP BY productName;

Question 17

How to get average value of the row?

Answer 17

SELECT AVG(price) FROM Products;

Question 18

How to get MIN/MAX value of the row?

Answer 18

SELECT MIN(price) FROM Products;
SELECT MAX(price) FROM Products;

Question 19

How to get a number of no empty row values from the table?

Answer 19

SELECT COUNT(price) FROM Products;

Question 20

How to return only unique value?

Answer 20

SELECT DISTINCT productName FROM Products;

Question 21

How to return limited number of values?

Answer 21

SELECT * FROM Products LIMIT 1;
# the following return 10 values starting from 0
SELECT * FROM Products LIMIT 0,10;

Question 22

How to temporary change the name of the selected column/table? ( 2 ways )

Answer 22

SELECT name AS another_name FROM profession;
# another without AS
SELECT name another_name FROM profession;

Question 23

How to select values with sub-request?

Answer 23

SELECT * FROM Products WHERE CategoryID IN (SELECT CategoryID FROM Categories);