Learn SQL Queries Basics With SQL Exercises

- Data is visible like a collection of named tables
- Tables include columns and rows
- Row is the horizontal part of table, that represents collection of values marked by a private key
- All rows in one table have the same structure, order is not important
- A single row in one table must be unique, that's why you need a private key with unique value
- The best datatype for private key is autonumber, specially if you are an amateur
- Column is the vertical part of table, that represents collection of the same type of values, and every column has his unique name
- Where each column and row are crossing, there is a specific data called value

SQL provides end users to tell database which data they need to be shown. SQL commands are executed through database manager program, that shows data in the form of table, that's called the 'Target table'. Source table can be created with 'CREATE TABLE' command and all requirements may relate to multiple rows simultaneously. Some users have permanent requirements for data insight on the same way. With "CREATE VIEW" command we can create a view, that user will see as normal table. Our work will be based on two tables: tyres and customers.

CONSTANTS
Constants can be used for different datatypes:
INTEGER (numbers): max 10 digits (example: 32, 5472, -21)
FLOATING POINT: max 24 digits (example: 86D23, 4.21A+3, -78C-1)
DECIMAL: max 15 digits, 31 with decimals (example: 32.14, 9786.201, -3.1)
CHAR: one character only (example: g,t,m)
VARCHAR: variable length (example: David, Customers, I don't know what else to say.)
NULL: empty value (not 0)

COMPARISON OPERATORS
Only for datatypes that can't be compared - numeric values.
= equal
<> not equal
> higher
>= higher or equal
< smaller
<= smaller or equal

If we don't have a value in column, or it is not usefull for a row, we set value as NULL. NOT NULL is used, when we want to show rows, that don't include NULL values.

OPERATORS
SQL select statement can include one or more operators/predicants. If we want to compare two or more values, we can use AND (both must be appropriate)and OR (at least one must be appropriate)operators. Order is important: NOT must be befor AND, AND before OR. If we use brackets, we can change order.

Example:
SELECT * FROM TYRES
WHERE HEIGHT IN (30,45,70)

Word IN is used to compare one value with list of values. List of values must be bounded with brackets and values must be separated with commas. Values are constants that will be compared with values in column, that we put before IN operator.

Example:
SELECT * FROM TYRES
WHERE PRICE BETWEEN 100 AND 200

BETWEEN is used to compare one value with area of values, including boundaries.

Example:
SELECT * FROM TYRES
WHERE BRAND LIKE '%A'

LIKE is used when we are searching for a string, that includes known character or part of string. We can use it only for VARCHAR datatype. If we include '%', we don't know or don't care how many characters are before or after our known character or part of string.

Example:
SELECT * FROM TYRES
WHERE BRAND NOT LIKE '%A'

Before BETWEEN, LIKE, IN, NULL we can use NOT (negation) and get opposite results.

(+) summation
(-) subtraction
(*) multiplication
(/) division

They can be used in different combinations.Terms order:
sign for positive or negative number (+,-)
* and /
+ and -
comparisons
NOT
AND
OR

Example:
SELECT BRAND, MODEL, SIZE
FROM TYRES
WHERE PRICE < 100
ORDER BY BRAND, MODEL

If you don´t indicate the desired order of the results, they will appear in random order. That is not necessarily wrong, but in many cases user wants to see results in some kind of order. If we want that, we must include ORDER BY in SQL statement. Rows will be ordered by written specified requests. First specified request is the main request, others are subordinated. So, if you look at our example, our first request was Brand and second was Model. There are two 'Sava' Brands, order of those depends on Model. Default order is ASC (ascending) therefore, an indication is not required, but we must indicate DESC if we want descending order.

Example:
SELECT LASTNAME, SUM(PRICE)
FROM CUSTOMERS
GROUP BY LASTNAME

GROUP BY creates groups of selected rows, where rows with the same value in indicated column are in one group. Indicated column is the group indicator shown in our results. As you can see, in table customers there are two lastnames Bergant, but because of GROUP BY in our result is only one. Because we used SUM, prices from both are summed.

Example:
SELECT DISTINCT HEIGHT, WIDTH
FROM TYRES
WHERE WIDTH > 200

SELECT statement is searching one row after another, that satisfy the conditions. It is possible that there are two or more completely same rows therefore, we can use DISTINCT to exclude all duplicated rows. If we want to see all results (with duplicated) we can use term ALL, but it is default, so just skip DISTINCT and all results will be visible.

SCALAR FUNCTIONS
They are allways performed on one row.
Date, day, days , hour, microsecond, minute, month, second, time, timestamp, year, length, substr, value

GROUP FUNTIONS
They are performed on all rows, that satisfies our indicated conditions.
NUMERIC: sum (summation), avg (average)
ALL: min (minimum), max (maximum), count (number of rows)

Example of usage:
SELECT MIN(VISINA), MAX(VELIKOST), AVG(SIRINA)

With JOIN we can join multiple tables in one.
Example:
SELECT FIRSTNAME, LASTNAME, PAYMENT, BRAND
FROM CUSTOMERS, TYRES
WHERE PAYMENT = PRICE

The result is shown as two merged tables. In SELECT statement we choose which columns from both tables we want in our result and in FROM statement in which tables are indicated columns. In WHERE statement we indicated column for integration. If we forget to write this statement (WHERE), every row from first table will integrate with every row in second. In this case result would be longer (much more rows) and of course wrong. Columns with price value are in our case named different (price, payment), but in practice usually both columns have same name. In this case, we must qualify columns with table name.

Example:
Assuming, both columns are named PRICE and we want to get the same result as we did before (in first example).
SELECT FIRSTNAME, LASTNAME, CUSTOMERS.PRICE, BRAND
FROM CUSTOMERS, TYRES
WHERE CUSTOMERS.PRICE = TYRES.PRICE

Best way to qualify columns is that we give abbreviation to every table.
Example:
SELECT FIRSTNAME, LASTNAME, C.PRICE, BRAND
FROM CUSTOMERS C, TYRES T
WHERE C.PRICE = T.PRICE

CREATE TABLE

We want to create table customers as it is in all examples here.
CREATE TABLE KUPCI
( FIRSTNAME VARCHAR(15)
LASTNAME VARCHAR(20)
COUNTRY VARCHAR(20)
PAYMENT DECIMAL)

Allowed datatypes are:
INT, SMALLINT, FLOAT, DECIMAL, CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, DATE, TIME, TIMESTAMP, NOT NULL (column must have a value).

CREATE INDEX

We can't use INDEX in select statement, term UNIQUE is used for preventing duplicate entries.
Example:
CREATE UNIQUE INDEX CUSTOMER_ID
ON CUSTOMERS

Adds new column in table Customers (CUSTOMER_ID), that must be unique for every row.

CREATE VIEW

When defining a view, we could enter new names for columns, that will be visible only in view. It is recommended that view is set to read-only, if it includes one of the elements:
- more than one table or view in first FROM statement
- DISTINCT
- GROUP BY
- group function

Example:
CREATE VIEW CUSTOMERS_SIGHT
( WHO, HOW_MUCH, WHAT)
AS
SELECT FIRSTNAME, PAYMENT, BRAND
FROM CUSTOMERS, TYRES
WHERE PAYMENT = PRICE

If we want to see this view, we must write this statement after view is created:
SELECT * FROM CUSTOMERS_SIGHT

INSERT DATA

We indicate the table, where we want tu put values in and columns where we want to have thoose values in a single row.
Example:
INSERT INTO CUSTOMERS
VALUES ('Jim','Carrey','USA','46,90')

We can indicate columns that we want to fill with some values, other remains empty (null).

INSERT INTO CUSTOMERS
(FIRSTNAME, LASTNAME, COUNTRY)
VALUES ('Jim','Carrey','USA')

UPDATE AND DELETE

With UPDATE we can update values in choosen columns and/or rows in tables or views.
Example:
UPDATE CUSTOMERS
SET FIRSTNAME = 'Robert'

That updates 'Firstname' column values in every row with new value, that in this case is 'Robert'.

DELETE can be performed for multiple rows simultaneously. All rows that satisfies selected criteria will be erased. If there is no WHERE statement, all rows will be deleted.
primer:
DELETE FROM CUSTOMERS
WHERE COUNTRY = 'Slovenia'

ALTER AND DROP

We can add new columns to our tables. Column is added on the right side of table and must provide NULL value. Why? All rows that are allready in the table, don't have a value for new column. Tables, indexes and views can be erased/deleted from database.

Example of adding new column:
ALTER TABLE CUSTOMERS
ADD COMPANY VARCHAR(30)

Example of deleting/erasing:
DROP TABLE KUPCI

1) You want to see which tyres (brand,model) has width between 195 and 225.
2) You are searching for tyres, that fit to your car. Your car supports only size 15 and 16.
3) Find out who spent the most on buying tyres.
4) Who bought tyre Goodyear HydraGrip?
5) Which tyre is the most expensive?

Solved tasks will be available soon, till then try to find out yours.

If you prefer slovene, or you don't speak english, but speak slovene, here is slovene version of this lens: SQL osnove