Databases are organized into tables.
Tables are organized into rows and columns.
RowsRecordsenregistrement.
Columnsfieldsattributs.
====> Two fields can’t have the same information.
SQL does not allow redundant information.
Some Operations are applied to a certain data type.
Diffirent data type are stored differently and take different space.
VARCHAR Communly Used in SQL to refer to Strings.
NUMERIC used to refer to floats.
Keywords used in SQL:
Select used to select the field We can select
multiple fields
Example :
SELECT names, id
FROM DB;
From THE TABLE IN WHICH THE FIELD IS LOCATED
* refer to ALL
AS used to set the query to a new field with new
name .
To get one field without repeated values we can use
DISTINCT after the SELECT.
CREATE VIEW nameOfThe_vw To create a view.
To limit the shown results use the LIMIT (num)
keyword.
count(field_name) used to count how many row in
that field.
The result will contain duplicates but you can use
DISTINCT to return the number of unique values.
WHEREkeyword that focus on data that matches
our qstion
Comparison operators can be used with WHERE :
>= |
<= |
< |
> |
= |
<>|
Where can be used with Strings.
Filtering with multiple criteria: AND / OR / BETWEEN
OR : one condition is enough.
AND: all the conditions must be satisfied.
BETWEEN: give all the result between 2 values.
Filtering text
There are 3 new keyword That gonna help us :
LIKE: used to search pattern in a field:
it has 2 wildcards:
“%” used to match 0, 1 or many
characters.
“_” used to match exactly one
character.
NOT LIKE: used to retrieve data that not match
our pattern.
it uses the same wildcards.
IN: used to search data based on different
values
NOT IN: used to search data that doesn’t
belong to the given values
easier and quicker than many OR
conditions.
NULL VALUES :
in SQL null represents a missing or unknown value.
We can use IS NULL and IS NOT NULL in filtering data
To select , identify and exclude missing values.
SUMMARIZING DATA:
AGRREGATE FUNCTIONS :
An aggregate function performs a calculation to several
values and return a single value.
COUNT() is an example of an aggregate function.
AVG() Return the average of the field’s values
SUM()Return the total of the field’s values.
MIN()Return the lowest value.
MAX()Return the highest value.
AVG and SUM are used only with NUMERIC field
COUNT and MIN and MAX can be used with non
numeric data types
SUMMURAZING SUBSETS
ROUND(n,rn) used to round a numeric val to
decimal number
rn is optional and it can be negative /positive /
and 0
it can only be used on numerical field.
NOTE:
Aggregate functions perform an action to the
field but the arithmetic functions like (+ / * -)
perform an action to the row
SORTING RESULTS:
ORDER BY Can be applied on one or more field separated by
‘,’
it sort in ascending order (0->100) or (A->Z).
you can use ASC for ascending and DESC for
descending.
you don’t have to Select the field that you are
sorting on.
GROUPING RESULTS:
New Keyword: GROUP BY.
GROUP BY used to order data in groups.
We can use GROUP BY with multiple fields.
We can combine GROUP BY with ORDER BY.
OODER BY is always written after GROUP BY
FILTERING GROUPED RESULTS:
We cant use WHERE with GROUP BY instead we can use HAVING
RENAME TABLE OR COLUMN:
Table
ALTER TABLE tableName
RENAME TO newname
Column
ALTER TABLE tableName
RENAME COLUMN oldName TO newname
Assingn unique value to id :
Create table tbNamen(
columnName SERIAL ;
);
INSERT INTO STATEMENT :
INSERT INTO TableName (Column1,column2)
VALUES(valC1,valC2)
If you want to insert multiple rows you can do this :
INSERT INTO TableName (Column1,column2)
VALUES(valC1,valC2), (valC1,valC2), (valC1,valC2), (valC1,valC2),
UPDATE STATEMENT :
The update statement is used to change the data inside rows
the alter statement is used to change table structure
UPDATE TableName
Set ColumnName1 = NewVal1 …,ColumnNameN = ValN
WHERE Condition
DELETE STATEMENT :
Used to delete unwanted data .
This will all the row which satisfy the condition
DELETE FROM tableName
WHERE Condition
TIP :
If you want to delete the first N rows just type :
DELETE top(N) FROM tableName ;
If you want to delete N percentage of the table
type :
DELETE top(N) percent FROM tableName ;
