Excersize -1

Pretty good! Now let's put together all the information we've learnt so far. Let's imagine a customer who walks in and wants to know if we have any cars that meet his needs.

Exercise

Select all columns of those cars that:

• were produced between 1999 and 2003,
• are not Volkswagens,
• their model begins with either 'P' or 'T',
• have their price set.

SQL Query for excersize

SELECT * FROM car WHERE production_year between 1999 and 2003 and brand != 'Volkswagen' and ( model LIKE 'P%' or model LIKE 'T%');

Output

VIN	BRAND	MODEL	PRICE	PRODUCTION_YEAR
1G1YZ23J9P5800003	Fiat	Punto	5700.00	1999

Basic Mathematical Operaion

Nice! We may now move on to our next problem: simple mathematics. Can you add or multiply numbers in SQL? Yes, you can! Take a look at the example:

SELECT *
FROM user
WHERE (monthly_salary * 12) > 50000;
In the above example, we multiply the monthly salary by 12 to get the annual salary by using the asterisk (*). We may then do whatever we want with the new value - in this case, we compare it with 50 000.

In this way, you can add (+), subtract (-), multiply (*) and divide (/) numbers.

Syntax:
SELECT * FROM table_name WHERE column_name + 10;
SELECT * FROM table_name WHERE column_name - 10;
SELECT * FROM table_name WHERE column_name * 10;
SELECT * FROM table_name WHERE column_name / 10;
SELECT * FROM table_name WHERE column_name % 10;

Exercise

Select all cars with the tax value over 2000. The tax value for all cars is 20% and you can write it as 0.20 in your query. Multiply the price by 0.20 to get the tax value.


SELECT * FROM car WHERE (price*0.20)>2000;

VIN	BRAND	MODEL	PRICE	PRODUCTION_YEAR
WPOZZZ79ZTS372128	Ford	Fusion	12500.00	2008
JF1BR93D7BG498281	Toyota	Avensis	11300.00	1999
1M8GDM9AXKP042788	Volkswagen	Golf	13000.00	2010

Comparision with NULL

Good job. Remember, NULL is a special value. It means that some piece of information is missing or unknown.

If you set a condition on a particular column, say AGE < 70, the rows where age is NULL will always be excluded from the results. Let's check that in practice.

In no way does NULL equal zero. What's more, the expression NULL = NULL is never true in SQL!
Exercise

Select all columns for cars whose price column is greater than or equal to zero.

Note that the Opel with an unknown price is not in the result.

Looking for NULL Value

Great! Remember, NULL is a special value. You can't use the equals sign to check whether something is NULL. It simply won't work. The opposite of IS NOT NULL is IS NULL.

SELECT id FROM user WHERE middle_name IS NULL;

This query will return only those users who don't have a middle name, i.e. their middle_name is unknown.

 Syntax:
SELECT * FROM table-name WHERE column_name IS  NULL;

Exercise

Select all cars whose price column is a NULL value.

Execute below code
SELECT * FROM car WHERE price IS  NULL;

VIN	BRAND	MODEL	PRICE	PRODUCTION_YEAR
GS723HDSAK2399002	Opel	Corsa	null	2007

 

Looking for NOT NULL value

In every row, there can be NULL values, i.e. fields with unknown missing values. Remember the Opel from our table with its missing price? This is exactly a NULL value. We simply don't know the price.

To check whether a column has a value, we use a special instruction IS NOT NULL.

SELECT id
FROM user
WHERE middle_name IS NOT NULL;

This code selects only those users who have a middle name, i.e. their column middle_name is known.



Syntax
SELECT * FROM table_name WHERE column_name IS NOT NULL;

Exercise

Select all cars whose price column isn't a null value.

SELECT * FROM car WHERE price IS NOT NULL;

VIN	BRAND	MODEL	PRICE	PRODUCTION_YEAR
LJCPCBLCX14500264	Ford	Focus	8000.00	2005
WPOZZZ79ZTS372128	Ford	Fusion	12500.00	2008
JF1BR93D7BG498281	Toyota	Avensis	11300.00	1999
KLATF08Y1VB363636	Volkswagen	Golf	3270.00	1992
1M8GDM9AXKP042788	Volkswagen	Golf	13000.00	2010
1HGCM82633A004352	Volkswagen	Jetta	6420.00	2003
1G1YZ23J9P5800003	Fiat	Punto	5700.00	1999

The UnderScore Sign

Nice! Now, sometimes we may not remember just one letter of a specific name. Imagine we want to find a girl whose name is... Catherine? Katherine?

SELECT *
FROM user
WHERE name LIKE '_atherine';

The underscore sign (_) replaces exactly one character. Whether it's Catherine or Katherine - the expression will return a row.

Syntax
SELECT * FROM tavle_name WHERE column_name LIKE '_alue';
SELECT * FROM tavle_name WHERE column_name LIKE 'va_ue';
SELECT * FROM tavle_name WHERE column_name LIKE 'valu_'; 






Exercise

Select all columns for cars whose brand is 'Volk_wagen' (the underscore replaces one unknown characters).

VIN	BRAND	MODEL	PRICE	PRODUCTION_YEAR
KLATF08Y1VB363636	Volkswagen	Golf	3270.00	1992
1M8GDM9AXKP042788	Volkswagen	Golf	13000.00	2010
1HGCM82633A004352	Volkswagen	Jetta	6420.00	2003