Essential SQL Commands Guide
Translated by
ScribdTranslationsEssential SQL Commands Guide
Translated by
ScribdTranslationsMAIN SQL COMMANDS
1.0 SELECT
SELECT Codice, Nome AS Soprannome, Stipendio*12 AS PagaAnnuale
FROM Employees
WHERE Division = 'Mag'
Next to this command are indicated
the columns that you want to display. The asterisk (*) selects all the columns specified in the FROM clause.
The DISTINCT command placed between SELECT and the column name removes all duplicate rows from the column.
The AS command
change the display name of the column, calculated columns can also be added by writing the operation
one and giving it a name with AS.
1.0.1 From
Indicate the table or tables where the Select command should operate.
1.0.2 Where
Indicate the logical condition with which they are filtered.
the columns. In square brackets [] you can indicate a parameter to ask for before the selection. In case there are d
One or more selected tables can be related by indicating the desired fields in an equality.
WHERE CodeName = Code
1.0.3 Group by
Group the rows with the same value indicated in the clause.
SELECT Department, SUM(Salary)
FROM Employees
GROUP BY Department;
1.0.4 Having
Give conditions to the groups created with Group by:
SELECT Department, SUM(Salary)
FROM Employees
GROUP BY Department;
HAVING SUM(salary) > 1000
1.0.5 Order by
Sort the records of the indicated column,
if you want a descending order, you write DESC after the name of the column. Instead of the name of the column, you p
you must indicate the corresponding number in the Select command.
ORDER BY salary DESC, LastName;
2.0 TABLE
2.0.1 Creation
With the CREATE command
TABLE you can create tables and define fields, this command must be executed in Queries.
The following instructions define the properties of the fields.
CREATE TABLE Employees (
ID smallint Primary key,
Last name char(30) not null,
City char(20) default 'Verona',
Salary decimal(9,2),
Distribution char(4) references RepartoMag(code);
2.0.2 Primary key
Define the primary key.
2.0.3 Not null
Makes the field mandatory.
2.0.4 Default
Define the default value for the field.
2.0.5 References
Define the foreign key and referential integrity with the indicated table (e.g. DepartmentMag)
2.0.6 Unique
Check for the presence of duplicates in one or more columns.
2.1.0 Modify
A table can be modified by adding (ADD) or removing (DROP) columns using the ALTER command.
TABLE.
ALTER TABLE Employees
ADD LastName char(29);
Or
DROP LastName;
2.1.1 Elimination
A table can be deleted with the command:
DROP TABLE Employees;
2.1.2 Index creation
The creation of an index combined with the Unique command facilitates the search for records.
CREATE UNIQUE INDEX EmployeeIndex
ON employees(LastName, FirstName);
3.0 DATA
Records can be inserted, modified, and deleted from the table with the following commands:
3.0.1 Insert
INSERT INTO employees (ID, Name, Surname)
VALUES (20, ;Mario, Rossi);
3.0.2 Update
UPDATE Employees
SET Cognome = ‘Rossi’, Nome = ‘Mario’
WHERE ID = 20
The SET instruction updates the data and WHERE specifies which data to modify.
3.0.4 Delete
DELETE FROM Employees
WHERE Name = 'Marco'
Delete the records with the name 'Marco'.
4.0 JOIN
4.1 LEFT JOIN…ON selects all records from the left table, in this case the Employees table.
SELECT [Link], [Link]
FROM Employees LEFT JOIN Department
ON [Link] = [Link];
4.2 RIGHT JOIN…ON selects all records from the right table, Department.
SELECT [Link], [Link]
FROM Employees RIGHT JOIN Department
ON [Link] = [Link];
If you want to view all the records from both tables, you need to combine the previous codes with the command
UNION.
5.0 AGGREGATION FUNCTIONS
Within the Select command, some aggregation calculations can be performed on the columns.
5.0.1 COUNT: Counts the records of the selected column.
SELECT COUNT(Name)
FROM Employees;
5.0.2 SUM: Sum all the records of the column, in this case those with Name greater than M.
SELECT SUM(Salary)
FROM employees
WHERE Name > 'M';
5.0.3 AVG: calculates the average.
SELECT AVG(Salary)
FROM employees, Department
WHERE Name > 'M' and Department = code;
5.0.4 MINeMAX calculates the minimum and maximum value.
6.0 QUERY
6.0.1 Table creation query: At the end of the Select command, write INTO and the name of the new Table.
SELECT Code, Name AS Nickname INTO Warehouse
FROM Employees
WHERE Reparto = 'Mag';
6.0.2 Append query: a row is added before the Select command with INSERT
INTO indicates where you want to append the resulting table.
INSERT INTO Warehouse (Code, Name)
SELECT Codice, Nome AS Soprannome
FROM Employees
WHERE Distribution = 'Mag';
6.0.3 Update query: the UPDATE command is used to modify data.
UPDATE Employees
SET Cognome = ‘Rossi’, Nome = ‘Mario’
WHERE ID = 20
6.0.4 Query to select higher or lower values.
SELECT TOP 5 Code, Name
FROM Employees
WHERE Distribution = 'Mag';
ORDER BY Name
7.0 RESEARCH CONDITIONS
7.0.1 Between
WHERE Salary BETWEEN 3000 AND 45000
7.0.2 In
Check the values of the column and select those that belong to the list.
WHERE City IN ('Verona', 'Milan', 'Genoa')
7.0.3 Like
Check the value of a field, ( _ ) indicates a single any character, while ( % ) indicates zero or more characters. E.g.
LIKE 'abd%' or '%abc' or '%abc%' or '_abc'
WHERE LastName LIKE 'R%'
7.0.4 Is Null
WHERE LastName IS (NOT) NULL
7.0.5 Nested queries
Interrogations are important for establishing selections with calculated fields.
SELECT Name, Surname
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM employees);