BASIC SQL
SELECT - extracts data from a database
UPDATE - updates data in a database
DELETE - deletes data from a database
INSERT INTO - inserts new data into a database
Operator Description
= Equal
<> Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN If you know the exact value you want to return for at least one of the columns
SQL DEFAULT Constraint
The DEFAULT constraint is used to insert a default value into a column.
The default value will be added to all new records, if no other value is specified.
SQL DEFAULT Constraint on CREATE TABLE
The following SQL creates a DEFAULT constraint on the "City" column when the "Persons" table is created:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)
SQL DEFAULT Constraint on ALTER TABLE
ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'SANDNES'
The COUNT() function
SELECT COUNT(*) FROM TABLENAME IT RETURNS THE NUMBER OF ROWS OF THE TABLE
SELECT DISTINCT COUNT(*) FROM TABLENAME RETURNS THE NUMBER OF DISTINCT ROWS IN THE TABLE
THE MAX FUNCTION
IT RETURNS THE MAX VALUE OF THE GIVEN COLUMN.
EG
SELECT MAX(SALARY) FROM EMP;
RETURNS THE MAXIMUM VALUE OF THE SALARY COLUMN
THE MIN FUNCTION
IT RETURNS THE MIN VALUE OF THE GIVEN COLUMN
EG
SELECT MIN(SALARY) FROM EMP;
RETURNS THE MINIMUM VALUE OF THE SALARY COLUMN
THE SUM FUNCTION
IT RETURNS THE SUM OF THE TOTAL DATA PRESENT IN THE GIVEN COLUMN IN A TABLE
EG
SELECT SUM(SALARY) FROM EMP;
IT SUMS UP THE TOTAL SALARY AND RETURNS THE SUMMATION OF SALARY.
The GROUP BY Statement
The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.
SQL GROUP BY Syntax
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
