SQL Basics and Advanced

Cheatsheet Content

Introduction to SQL SQL is a standard language for accessing databases. Used to access and manipulate data in various database systems like MySQL, SQL Server, Access, Oracle, Sybase, DB2. SQL Syntax Example SELECT Company, Country FROM Customers WHERE Country = 'USA' What is SQL? SQL stands for Structured Query Language. SQL lets you access and manipulate databases. SQL is an ANSI (American National Standards Institute) standard. What Can SQL Do? Execute queries against a database. Retrieve data from a database. Insert records in a database. Update records in a database. Delete records from a database. Create new databases. Create new tables in a database. Create stored procedures in a database. Create views in a database. Set permissions on tables, procedures, and views. RDBMS RDBMS stands for Relational Database Management System. It is the basis for SQL and all modern database systems. Data in RDBMS is stored in database objects called tables. A table is a collection of related data entries, consisting of columns and rows. Database Tables A database often contains one or more tables, each identified by a name (e.g., "Customers" or "Orders"). Tables contain records (rows) with data. Example: "Persons" Table P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger The table contains three records and five columns (P_Id, LastName, FirstName, Address, City). SQL Statements Most database actions are performed with SQL statements. Example: Select all records from "Persons" table SELECT * FROM Persons Notes on SQL Statements SQL is not case sensitive. Semicolon after SQL Statements: Some database systems require a semicolon at the end of each SQL statement. It is the standard way to separate multiple SQL statements in database systems that allow them to be executed in the same call. MS Access and SQL Server 2000 do not require semicolons, but some database programs enforce them. SQL DML and DDL SQL can be divided into two parts: Data Manipulation Language (DML): For querying and updating data. Data Definition Language (DDL): For creating or modifying database structure. DML Part of 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. DDL Part of SQL Permits creating or deleting database tables, defining indexes (keys), specifying links between tables, and imposing constraints. Important DDL statements: CREATE DATABASE - creates a new database. ALTER DATABASE - modifies a database. CREATE TABLE - creates a new table. ALTER TABLE - modifies a table. DROP TABLE - deletes a table. CREATE INDEX - creates an index (search key). DROP INDEX - deletes an index. The SQL SELECT Statement Used to select data from a database. The result is stored in a result table, called the result-set. SQL SELECT Syntax SELECT column_name(s) FROM table_name And SELECT * FROM table_name Note: SQL is not case sensitive. SQL SELECT Example Using the "Persons" table: P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger To select "LastName" and "FirstName" columns: SELECT LastName,FirstName FROM Persons Result-set: LastName FirstName Hansen Ola Svendson Tove Pettersen Kari SELECT * Example To select all columns from the "Persons" table: SELECT * FROM Persons The SQL SELECT DISTINCT Statement Used to list only the different (distinct) values in a table column, especially when columns may contain duplicate values. The DISTINCT keyword returns only distinct (different) values. SQL SELECT DISTINCT Syntax SELECT DISTINCT column_name(s) FROM table_name SELECT DISTINCT Example Using the "Persons" table, to select distinct cities: SELECT DISTINCT City FROM Persons Result-set: City Sandnes Stavanger The WHERE Clause Used to filter records. Extracts only those records that fulfill a specified criterion. SQL WHERE Syntax SELECT column_name(s) FROM table_name WHERE column_name operator value WHERE Clause Example Using the "Persons" table, to select persons living in "Sandnes": SELECT * FROM Persons WHERE City='Sandnes' Quotes Around Text Fields SQL uses single quotes around text values (most database systems also accept double quotes). Numeric values should not be enclosed in quotes. Correct for text: SELECT * FROM Persons WHERE FirstName='Tove' Wrong for text: SELECT * FROM Persons WHERE FirstName=Tove Correct for numeric: SELECT * FROM Persons WHERE Year=1965 Wrong for numeric: SELECT * FROM Persons WHERE Year='1965' Operators Allowed in the WHERE Clause Operator Description $=$ Equal $<>$ Not equal (some versions may use $!=$) $>$ 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 The AND & OR Operators Used to filter records based on more than one condition. AND operator: Displays a record if both conditions are true. OR operator: Displays a record if either condition is true. AND Operator Example Using "Persons" table, to select persons with "Tove" as FirstName AND "Svendson" as LastName: SELECT * FROM Persons WHERE FirstName='Tove' AND LastName='Svendson' OR Operator Example Using "Persons" table, to select persons with "Tove" OR "Ola" as FirstName: SELECT * FROM Persons WHERE FirstName='Tove' OR FirstName='Ola' Combining AND & OR Parentheses can be used for complex expressions. Example: To select persons with LastName="Svendson" AND (FirstName="Tove" OR FirstName="Ola"): SELECT * FROM Persons WHERE LastName='Svendson' AND (FirstName='Tove' OR FirstName='Ola') The ORDER BY Keyword Used to sort the result-set. Sorts the result-set by a specified column(s). Sorts records in ascending order by default. Use DESC keyword for descending order. SQL ORDER BY Syntax SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC ORDER BY Example Using the "Persons" table, to sort by LastName: SELECT * FROM Persons ORDER BY LastName ORDER BY DESC Example Using the "Persons" table, to sort by LastName in descending order: SELECT * FROM Persons ORDER BY LastName DESC The INSERT INTO Statement Used to insert new records (rows) into a table. SQL INSERT INTO Syntax Form 1 (values only, in order of columns): INSERT INTO table_name VALUES (value1, value2, value3,...) Form 2 (specifies columns and values): INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...) SQL INSERT INTO Example To insert a new row into "Persons" table: INSERT INTO Persons VALUES (4,'Nilsen', 'Johan', 'Bakken 2', 'Stavanger') Insert Data Only in Specified Columns To insert data only into "P_Id", "LastName", "FirstName" columns: INSERT INTO Persons (P_Id, LastName, FirstName) VALUES (5, 'Tjessem', 'Jakob') The UPDATE Statement Used to update existing records in a table. SQL UPDATE Syntax UPDATE table_name SET column1=value1, column2=value2,... WHERE some_column=some_value Note: The WHERE clause specifies which records to update. Omitting it will update all records. SQL UPDATE Example To update the person "Tjessem, Jakob" in the "Persons" table: UPDATE Persons SET Address='Nissestien 67', City='Sandnes' WHERE LastName='Tjessem' AND FirstName='Jakob' SQL UPDATE Warning Be careful when updating records. If the WHERE clause is omitted, all records will be updated. The DELETE Statement Used to delete records (rows) from a table. SQL DELETE Syntax DELETE FROM table_name WHERE some_column=some_value Note: The WHERE clause specifies which records to delete. Omitting it will delete all records. SQL DELETE Example To delete the person "Tjessem, Jakob" from the "Persons" table: DELETE FROM Persons WHERE LastName='Tjessem' AND FirstName='Jakob' Delete All Rows It is possible to delete all rows in a table without deleting the table itself. The table structure, attributes, and indexes will remain intact. DELETE FROM table_name or DELETE * FROM table_name SQL ADVANCED The TOP Clause Used to specify the number of records to return. Useful for large tables to improve performance, as returning too many records can impact it. Note: Not all database systems support the TOP clause. SQL Server Syntax for TOP SELECT TOP number|percent column_name(s) FROM table_name SQL SELECT TOP Equivalent in MySQL and Oracle MySQL Syntax: SELECT column_name(s) FROM table_name LIMIT number Oracle Syntax: SELECT column_name(s) FROM table_name WHERE ROWNUM <= number SQL TOP Example Using the "Persons" table, to select the first two records: SELECT TOP 2 * FROM Persons SQL TOP PERCENT Example Using the "Persons" table, to select 50% of the records: SELECT TOP 50 PERCENT * FROM Persons SQL Wildcards Used when searching for data in a database. Substitute for one or more characters when searching for data. Must be used with the SQL LIKE operator. Wildcard Description Wildcard Description % A substitute for zero or more characters _ A substitute for exactly one character [charlist] Any single character in charlist [^charlist] or [!charlist] Any single character not in charlist SQL Wildcard Examples Using the "Persons" table: Using the % Wildcard To select persons living in a city that starts with "sa": SELECT * FROM Persons WHERE City LIKE 'sa%' The % sign can define wildcards both before and after the pattern. To select persons living in a city that contains "nes": SELECT * FROM Persons WHERE City LIKE '%nes%' Using the _ Wildcard To select persons with a first name that starts with any character, followed by "la": SELECT * FROM Persons WHERE FirstName LIKE '_la' To select persons with a last name that starts with "S", followed by any character, then "end", then any character, then "on": SELECT * FROM Persons WHERE LastName LIKE 'S_end_on' Using the [charlist] Wildcard To select persons with a last name that starts with "b" or "s" or "p": SELECT * FROM Persons WHERE LastName LIKE '[bsp]%' To select persons with a last name that does not start with "b" or "s" or "p": SELECT * FROM Persons WHERE LastName LIKE '[!bsp]%' The LIKE Operator Used in a WHERE clause to search for a specified pattern in a column. SQL LIKE Syntax SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern LIKE Operator Example Using the "Persons" table, to select persons living in a city that starts with "s": SELECT * FROM Persons WHERE City LIKE 's%' To select persons living in a city that ends with an "s": SELECT * FROM Persons WHERE City LIKE '%s' To select persons living in a city that contains the pattern "tav": SELECT * FROM Persons WHERE City LIKE '%tav%' To select persons living in a city that NOT contains the pattern "tav": SELECT * FROM Persons WHERE City NOT LIKE '%tav%' The IN Operator Allows specifying multiple values in a WHERE clause. SQL IN Syntax SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...) IN Operator Example Using the "Persons" table, to select persons with LastName "Hansen" or "Pettersen": SELECT * FROM Persons WHERE LastName IN ('Hansen','Pettersen') The BETWEEN Operator Used in a WHERE clause to select a range of data between two values. Values can be numbers, text, or dates. SQL BETWEEN Syntax SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2 BETWEEN Operator Example Using the "Persons" table, to select persons with LastName alphabetically between "Hansen" and "Pettersen": SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen' Note: Check how your database treats the BETWEEN operator, as behavior can vary (inclusive/exclusive of endpoints). Example 2 - NOT BETWEEN To display persons outside the range: SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Hansen' AND 'Pettersen' SQL Alias An alias name can be given to a table or a column. Useful for long or complex table/column names. An alias can be anything, but usually short. SQL Alias Syntax for Tables SELECT column_name(s) FROM table_name AS alias_name SQL Alias Syntax for Columns SELECT column_name AS alias_name FROM table_name Alias Example Assume "Persons" (p) and "Product_Orders" (po) tables. To list all orders by "Ola Hansen": SELECT po.OrderID, p.LastName, p.FirstName FROM Persons AS p, Product_Orders AS po WHERE p.LastName='Hansen' AND p.FirstName='Ola' Aliases make queries easier to write and read. SQL JOIN Used to query data from two or more tables based on a relationship between certain columns. The JOIN keyword queries data from multiple tables based on related columns. Tables are often related by keys. A primary key has a unique value for each row and binds data across tables without repetition. Different SQL JOINs JOIN (or INNER JOIN ): Returns rows when there is at least one match in both tables. LEFT JOIN (or LEFT OUTER JOIN ): Returns all rows from the left table, even if no matches in the right table. RIGHT JOIN (or RIGHT OUTER JOIN ): Returns all rows from the right table, even if no matches in the left table. FULL JOIN (or FULL OUTER JOIN ): Returns rows when there is a match in one of the tables (returns all rows from both tables, with NULL values where there's no match). SQL INNER JOIN Keyword Returns rows when there is at least one match in both tables. SQL INNER JOIN Syntax SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name = table_name2.column_name INNER JOIN is the same as JOIN . SQL INNER JOIN Example Using "Persons" and "Orders" tables (related by P_Id), to list all persons with any orders: SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.P_Id = Orders.P_Id ORDER BY Persons.LastName Rows in "Persons" without matches in "Orders" will NOT be listed. SQL LEFT JOIN Keyword Returns all rows from the left table ( table_name1 ), even if there are no matches in the right table ( table_name2 ). SQL LEFT JOIN Syntax SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name = table_name2.column_name In some databases, LEFT JOIN is called LEFT OUTER JOIN . SQL LEFT JOIN Example Using "Persons" and "Orders" tables, to list all persons and their orders (if any): SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons LEFT JOIN Orders ON Persons.P_Id = Orders.P_Id ORDER BY Persons.LastName Returns all rows from the left table ("Persons"), even if no matches in the right table ("Orders"). SQL RIGHT JOIN Keyword Returns all rows from the right table ( table_name2 ), even if there are no matches in the left table ( table_name1 ). SQL RIGHT JOIN Syntax SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name = table_name2.column_name In some databases, RIGHT JOIN is called RIGHT OUTER JOIN . SQL RIGHT JOIN Example Using "Persons" and "Orders" tables, to list all orders with containing persons (if any): SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons RIGHT JOIN Orders ON Persons.P_Id = Orders.P_Id ORDER BY Persons.LastName Returns all rows from the right table ("Orders"), even if no matches in the left table ("Persons"). SQL FULL JOIN Keyword Returns rows when there is a match in one of the tables (returns all rows from both tables, with NULL values where there's no match). SQL FULL JOIN Syntax SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name = table_name2.column_name SQL FULL JOIN Example Using "Persons" and "Orders" tables, to list all persons and their orders, and all orders with their persons: SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons FULL JOIN Orders ON Persons.P_Id = Orders.P_Id ORDER BY Persons.LastName Returns all rows from both the left table ("Persons") and the right table ("Orders"). If there are no matches in one table, NULL values are listed. The SQL UNION Operator Used to combine the result-set of two or more SELECT statements. Each SELECT statement within the UNION must have: The same number of columns. Similar data types for corresponding columns. Columns in the same order. SQL UNION Syntax SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2 Note: The UNION operator selects only distinct values by default. Use UNION ALL to allow duplicate values. SQL UNION ALL Syntax SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2 The column names in the result-set of a UNION are always equal to the column names in the first SELECT statement. SQL UNION Example Using "Employees_Norway" and "Employees_USA" tables, to list all different employees: SELECT E_Name FROM Employees_Norway UNION SELECT E_Name FROM Employees_USA This lists only distinct names. If two employees have the same name, only one will be listed. SQL UNION ALL Example To list all employees (including duplicates) in Norway and USA: SELECT E_Name FROM Employees_Norway UNION ALL SELECT E_Name FROM Employees_USA The SQL SELECT INTO Statement Used to create backup copies of tables. Selects data from one table and inserts it into a different table. Most often used for creating backup copies. SQL SELECT INTO Syntax To select all columns into a new table: SELECT * INTO new_table_name [IN externaldatabase] FROM old_tablename To select only specific columns into a new table: SELECT column_name(s) INTO new_table_name [IN externaldatabase] FROM old_tablename SQL SELECT INTO Example To make an exact copy of the "Persons" table into "Persons_Backup": SELECT * INTO Persons_Backup FROM Persons To copy to another database: SELECT * INTO Persons_Backup IN 'Backup.mdb' FROM Persons To copy only a few fields (LastName, FirstName): SELECT LastName,FirstName INTO Persons_Backup FROM Persons SQL SELECT INTO - With a WHERE Clause To create "Persons_Backup" with only persons living in "Sandnes": SELECT LastName,Firstname INTO Persons_Backup FROM Persons WHERE City='Sandnes' SQL SELECT INTO - Joined Tables Selecting data from more than one table is possible. To create "Persons_Order_Backup" from "Persons" and "Orders" tables: SELECT Persons.LastName,Orders.OrderNo INTO Persons_Order_Backup FROM Persons INNER JOIN Orders ON Persons.P_Id = Orders.P_Id The CREATE DATABASE Statement Used to create a database. SQL CREATE DATABASE Syntax CREATE DATABASE database_name CREATE DATABASE Example To create a database called "my_db": CREATE DATABASE my_db Database tables can be added with the CREATE TABLE statement. The CREATE TABLE Statement Used to create a table in a database. SQL CREATE TABLE Syntax CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, column_name3 data_type, ... ) The data type specifies what type of data the column can hold. CREATE TABLE Example To create a "Persons" table with P_Id (int), LastName, FirstName, Address, and City (all varchar(255)): CREATE TABLE Persons ( P_Id int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) ) The empty table can be filled with data using INSERT INTO . SQL Constraints Used to limit the type of data that can go into a table. Can be specified when a table is created ( CREATE TABLE ) or after ( ALTER TABLE ). Common Constraints NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK DEFAULT SQL NOT NULL Constraint Enforces a column to NOT accept NULL values. Ensures a field always contains a value; cannot insert or update without adding a value. SQL NOT NULL Constraint on CREATE TABLE To enforce NOT NULL on "P_Id" and "LastName" columns: CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) ) SQL UNIQUE Constraint Uniquely identifies each record in a database table. Both UNIQUE and PRIMARY KEY constraints guarantee uniqueness. A PRIMARY KEY automatically has a UNIQUE constraint. A table can have many UNIQUE constraints, but only one PRIMARY KEY constraint. SQL UNIQUE Constraint on CREATE TABLE To create a UNIQUE constraint on "P_Id" in MySQL: CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), UNIQUE (P_Id) ) For SQL Server / Oracle / MS Access: CREATE TABLE Persons ( P_Id int NOT NULL UNIQUE, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) ) To name a UNIQUE constraint or define on multiple columns: CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName) ) SQL UNIQUE Constraint on ALTER TABLE To add a UNIQUE constraint on "P_Id" to an existing table: ALTER TABLE Persons ADD UNIQUE (P_Id) To name a UNIQUE constraint or define on multiple columns: ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName) To DROP a UNIQUE Constraint To drop a UNIQUE constraint in MySQL: ALTER TABLE Persons DROP INDEX uc_PersonID For SQL Server / Oracle / MS Access: ALTER TABLE Persons DROP CONSTRAINT uc_PersonID SQL PRIMARY KEY Constraint Uniquely identifies each record in a database table. Primary keys must contain unique values and cannot contain NULL values. Each table should have a primary key, and only one. SQL PRIMARY KEY Constraint on CREATE TABLE To create a PRIMARY KEY on "P_Id" in MySQL: CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), PRIMARY KEY (P_Id) ) For SQL Server / Oracle / MS Access: CREATE TABLE Persons ( P_Id int NOT NULL PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) ) To name a PRIMARY KEY constraint or define on multiple columns: CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName) ) SQL PRIMARY KEY Constraint on ALTER TABLE To add a PRIMARY KEY on "P_Id" to an existing table: ALTER TABLE Persons ADD PRIMARY KEY (P_Id) To name a PRIMARY KEY constraint or define on multiple columns: ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName) Note: When using ALTER TABLE to add a primary key, the column(s) must already be declared as NOT NULL . To DROP a PRIMARY KEY Constraint To drop a PRIMARY KEY in MySQL: ALTER TABLE Persons DROP PRIMARY KEY For SQL Server / Oracle / MS Access: ALTER TABLE Persons DROP CONSTRAINT pk_PersonID SQL FOREIGN KEY Constraint A FOREIGN KEY in one table points to a PRIMARY KEY in another table. Prevents actions that would destroy links between tables. Prevents invalid data from being inserted into the foreign key column (must be a value from the referenced table). SQL FOREIGN KEY Constraint on CREATE TABLE Using "Orders" table, to create a FOREIGN KEY on "P_Id" referencing "Persons(P_Id)" in MySQL: CREATE TABLE Orders ( O_Id int NOT NULL, OrderNo int NOT NULL, P_Id int, PRIMARY KEY (O_Id), FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) ) For SQL Server / Oracle / MS Access: CREATE TABLE Orders ( O_Id int NOT NULL PRIMARY KEY, OrderNo int NOT NULL, P_Id int FOREIGN KEY REFERENCES Persons(P_Id) ) To name a FOREIGN KEY constraint or define on multiple columns: CREATE TABLE Orders ( O_Id int NOT NULL, OrderNo int NOT NULL, P_Id int, PRIMARY KEY (O_Id), CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) ) SQL FOREIGN KEY Constraint on ALTER TABLE To add a FOREIGN KEY on "P_Id" to an existing "Orders" table: ALTER TABLE Orders ADD FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) To name a FOREIGN KEY constraint or define on multiple columns: ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) To DROP a FOREIGN KEY Constraint To drop a FOREIGN KEY in MySQL: ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders For SQL Server / Oracle / MS Access: ALTER TABLE Orders DROP CONSTRAINT fk_PerOrders SQL CHECK Constraint Used to limit the value range that can be placed in a column. On a single column, allows only certain values. On a table, can limit values in certain columns based on other columns in the row. SQL CHECK Constraint on CREATE TABLE To create a CHECK constraint on "P_Id" (must be $>0$) in MySQL: CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CHECK (P_Id>0) ) For SQL Server / Oracle / MS Access: CREATE TABLE Persons ( P_Id int NOT NULL CHECK (P_Id>0), LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) ) To name a CHECK constraint or define on multiple columns: CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes') ) SQL CHECK Constraint on ALTER TABLE To add a CHECK constraint on "P_Id" (must be $>0$) to an existing table: ALTER TABLE Persons ADD CHECK (P_Id>0) To name a CHECK constraint or define on multiple columns: ALTER TABLE Persons ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes') To DROP a CHECK Constraint To drop a CHECK constraint in SQL Server / Oracle / MS Access: ALTER TABLE Persons DROP CONSTRAINT chk_Person SQL DEFAULT Constraint Used to insert a default value into a column. The default value is added to all new records if no other value is specified. SQL DEFAULT Constraint on CREATE TABLE To create a DEFAULT constraint on "City" (default 'Sandnes'): CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) DEFAULT 'Sandnes' ) Can also use system values like GETDATE() : CREATE TABLE Orders ( O_Id int NOT NULL, OrderNo int NOT NULL, P_Id int, OrderDate date DEFAULT GETDATE() ) SQL DEFAULT Constraint on ALTER TABLE To create a DEFAULT constraint on "City" to an existing table in MySQL: ALTER TABLE Persons ALTER City SET DEFAULT 'SANDNES' For SQL Server / Oracle / MS Access: ALTER TABLE Persons ADD CONSTRAINT df_City DEFAULT 'SANDNES' FOR City To DROP a DEFAULT Constraint To drop a DEFAULT constraint in MySQL: ALTER TABLE Persons ALTER City DROP DEFAULT For SQL Server / Oracle / MS Access: ALTER TABLE Persons DROP CONSTRAINT df_City Indexes The CREATE INDEX statement creates indexes in tables. Indexes allow faster data retrieval without reading the whole table. An index can be created to find data more quickly and efficiently. Users cannot see indexes; they only speed up searches/queries. Note: Updating a table with indexes takes more time (because indexes need updating). Create indexes only on frequently searched columns. SQL CREATE INDEX Syntax To create an index (duplicate values allowed): CREATE INDEX index_name ON table_name (column_name) SQL CREATE UNIQUE INDEX Syntax To create a unique index (duplicate values not allowed): CREATE UNIQUE INDEX index_name ON table_name (column_name) Note: Syntax for creating indexes varies across databases. CREATE INDEX Example To create an index named "PIndex" on "LastName" in "Persons" table: CREATE INDEX PIndex ON Persons (LastName) To create an index on a combination of columns: CREATE INDEX PIndex ON Persons (LastName, FirstName) The DROP INDEX Statement Indexes, tables, and databases can be deleted/removed with the DROP statement. The DROP INDEX statement deletes an index in a table. DROP INDEX Syntax for MS Access DROP INDEX index_name ON table_name DROP INDEX Syntax for MS SQL Server DROP INDEX table_name.index_name DROP INDEX Syntax for DB2/Oracle DROP INDEX index_name DROP INDEX Syntax for MySQL ALTER TABLE table_name DROP INDEX index_name The DROP TABLE Statement Used to delete a table. DROP TABLE table_name The DROP DATABASE Statement Used to delete a database. DROP DATABASE database_name The TRUNCATE TABLE Statement Deletes data inside the table, but not the table itself. TRUNCATE TABLE table_name The ALTER TABLE Statement Used to add, delete, or modify columns in an existing table. SQL ALTER TABLE Syntax To add a column: ALTER TABLE table_name ADD column_name datatype To delete a column (some databases may not allow): ALTER TABLE table_name DROP COLUMN column_name To change the data type of a column: ALTER TABLE table_name ALTER COLUMN column_name datatype SQL ALTER TABLE Example To add a column named "DateOfBirth" (date) to "Persons" table: ALTER TABLE Persons ADD DateOfBirth date The new column "DateOfBirth" will be of type date. Change Data Type Example To change "DateOfBirth" to year type: ALTER TABLE Persons ALTER COLUMN DateOfBirth year The "DateOfBirth" column will now hold a year in two-digit or four-digit format. DROP COLUMN Example To delete the "DateOfBirth" column: ALTER TABLE Persons DROP COLUMN DateOfBirth AUTO INCREMENT a Field Allows a unique number to be generated when a new record is inserted. Primary key fields are often auto-incremented. Syntax for MySQL AUTO_INCREMENT To define "P_Id" as auto-increment primary key: CREATE TABLE Persons ( P_Id int NOT NULL AUTO_INCREMENT, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), PRIMARY KEY (P_Id) ) MySQL uses AUTO_INCREMENT . Default starting value is 1, increments by 1. To start sequence with another value (e.g., 100): ALTER TABLE Persons AUTO_INCREMENT=100 When inserting, no need to specify "P_Id": INSERT INTO Persons (FirstName,LastName) VALUES ('Lars','Monsen') Syntax for SQL Server IDENTITY To define "P_Id" as auto-increment primary key: CREATE TABLE Persons ( P_Id int PRIMARY KEY IDENTITY, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) ) SQL Server uses IDENTITY . Default starting value is 1, increments by 1. To start at 10 and increment by 5: IDENTITY(10,5) . When inserting, no need to specify "P_Id": INSERT INTO Persons (FirstName,LastName) VALUES ('Lars','Monsen') Syntax for Access AUTOINCREMENT To define "P_Id" as auto-increment primary key: CREATE TABLE Persons ( P_Id PRIMARY KEY AUTOINCREMENT, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) ) MS Access uses AUTOINCREMENT . Default starting value is 1, increments by 1. To start at 10 and increment by 5: AUTOINCREMENT(10,5) . When inserting, no need to specify "P_Id": INSERT INTO Persons (FirstName,LastName) VALUES ('Lars','Monsen') Syntax for Oracle (Sequence Object) Create a sequence object: CREATE SEQUENCE seq_person MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10 CACHE 10 stores up to 10 values in memory for performance. To insert a new record, use nextval function: INSERT INTO Persons (P_Id,FirstName,LastName) VALUES (seq_person.nextval,'Lars','Monsen') SQL CREATE VIEW Statement A view is a virtual table based on the result-set of an SQL statement. Contains rows and columns like a real table, but fields are from one or more real tables. Can include SQL functions, WHERE , and JOIN statements. Presents data as if it were from one single table. SQL CREATE VIEW Syntax CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition Note: A view always shows up-to-date data, as the database engine recreates data using the view's SQL statement every time it's queried. SQL CREATE VIEW Examples Example: "Current Product List" view (non-discontinued products): CREATE VIEW [Current Product List] AS SELECT ProductID,ProductName FROM Products WHERE Discontinued=No Querying the view: SELECT * FROM [Current Product List] Example: "Products Above Average Price" view: CREATE VIEW [Products Above Average Price] AS SELECT ProductName,UnitPrice FROM Products WHERE UnitPrice > (SELECT AVG(UnitPrice) FROM Products) Querying the view: SELECT * FROM [Products Above Average Price] Example: "Category Sales For 1997" view (using another view): CREATE VIEW [Category Sales For 1997] AS SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales FROM [Product Sales for 1997] GROUP BY CategoryName Querying the view: SELECT * FROM [Category Sales For 1997] With condition: SELECT * FROM [Category Sales For 1997] WHERE CategoryName='Beverages' SQL Updating a View Syntax to update a view: SQL CREATE OR REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition Example: To add "Category" column to "Current Product List" view: CREATE VIEW [Current Product List] AS SELECT ProductID,ProductName,Category FROM Products WHERE Discontinued=No SQL Dropping a View You can delete a view with the DROP VIEW command. SQL DROP VIEW Syntax DROP VIEW view_name SQL Dates The most difficult part is ensuring date format matches the date column in the database. If a time portion is involved, it complicates queries. Tip: To keep queries simple, avoid time components in dates. MySQL Date Functions Function Description NOW() Returns the current date and time CURDATE() Returns the current date CURTIME() Returns the current time DATE() Extracts the date part of a date or date/time expression EXTRACT() Returns a single part of a date/time DATE_ADD() Adds a specified time interval to a date DATE_SUB() Subtracts a specified time interval from a date DATEDIFF() Returns the number of days between two dates DATE_FORMAT() Displays date/time data in different formats SQL Server Date Functions Function Description GETDATE() Returns the current date and time DATEPART() Returns a single part of a date/time DATEADD() Adds or subtracts a specified time interval from a date DATEDIFF() Returns the time between two dates CONVERT() Displays date/time data in different formats SQL Date Data Types MySQL Date Types DATE - format YYYY-MM-DD DATETIME - format: YYYY-MM-DD HH:MM:SS TIMESTAMP - format: YYYY-MM-DD HH:MM:SS YEAR - format YYYY or YY SQL Server Date Types DATE - format YYYY-MM-DD DATETIME - format: YYYY-MM-DD HH:MM:SS SMALLDATETIME - format: YYYY-MM-DD HH:MM:SS TIMESTAMP - format: a unique number SQL Working with Dates Example: "Orders" table: OrderId ProductName OrderDate 1 Geitost 2008-11-11 2 Camembert Pierrot 2008-11-09 3 Mozzarella di Giovanni 2008-11-11 4 Mascarpone Fabioli 2008-10-29 To select records with OrderDate of "2008-11-11": SELECT * FROM Orders WHERE OrderDate='2008-11-11' If OrderDate includes a time component (e.g., "2008-11-11 13:23:44"), the same query will return no results. This is because the query looks for dates with no time portion. NOW() Function Returns the current date and time. Syntax: NOW() Example: SELECT NOW(),CURDATE(),CURTIME() Result: 2008-11-11 12:45:34 2008-11-11 12:45:34 Example: Create "Orders" table with OrderDate defaulting to NOW() : CREATE TABLE Orders ( OrderId int NOT NULL, ProductName varchar(50) NOT NULL, OrderDate datetime NOT NULL DEFAULT NOW(), PRIMARY KEY (OrderId) ) When inserting a row, the current date and time are automatically inserted. CURDATE() Function Returns the current date. Syntax: CURDATE() Example: Create "Orders" table with OrderDate defaulting to CURDATE() : CREATE TABLE Orders ( OrderId int NOT NULL, ProductName varchar(50) NOT NULL, OrderDate datetime NOT NULL DEFAULT CURDATE(), PRIMARY KEY (OrderId) ) When inserting a row, the current date is automatically inserted. CURTIME() Function Returns the current time. Syntax: CURTIME() DATE() Function Extracts the date part of a date or date/time expression. Syntax: DATE(date) Example: SELECT ProductName, DATE(OrderDate) AS OrderDate FROM Orders WHERE OrderId=1 EXTRACT() Function Returns a single part of a date/time (year, month, day, hour, minute, etc.). Syntax: EXTRACT(unit FROM date) unit can be: MICROSECOND , SECOND , MINUTE , HOUR , DAY , WEEK , MONTH , QUARTER , YEAR , etc. Example: SELECT EXTRACT(YEAR FROM OrderDate) AS OrderYear, EXTRACT(MONTH FROM OrderDate) AS OrderMonth, EXTRACT(DAY FROM OrderDate) AS OrderDay FROM Orders WHERE OrderId=1 DATE_ADD() Function Adds a specified time interval to a date. Syntax: DATE_ADD(date,INTERVAL expr type) expr is the number of intervals, type can be MICROSECOND , SECOND , MINUTE , HOUR , DAY , WEEK , MONTH , QUARTER , YEAR , etc. Example: Add 45 days to OrderDate : SELECT OrderId,DATE_ADD(OrderDate,INTERVAL 45 DAY) AS OrderPayDate FROM Orders DATE_SUB() Function Subtracts a specified time interval from a date. Syntax: DATE_SUB(date,INTERVAL expr type) Example: Subtract 5 days from OrderDate : SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 5 DAY) AS SubtractDate FROM Orders DATEDIFF() Function Returns the time between two dates. Syntax: DATEDIFF(date1,date2) Only the date parts are used in the calculation. Example: SELECT DATEDIFF('2008-11-30','2008-11-29') AS DiffDate (Result: 1) Example: SELECT DATEDIFF('2008-11-29','2008-11-30') AS DiffDate (Result: -1) DATE_FORMAT() Function (MySQL) Displays date/time data in different formats. Syntax: DATE_FORMAT(date,format) format specifies the output format using various specifiers (e.g., %a for abbreviated weekday, %b for abbreviated month, %Y for four-digit year, etc.). Example: SELECT DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p') GETDATE() Function (SQL Server) Returns the current date and time from the SQL Server. Syntax: GETDATE() Example: SELECT GETDATE() AS CurrentDateTime The time part can go to milliseconds. Example: Create "Orders" table with OrderDate defaulting to GETDATE() : CREATE TABLE Orders ( OrderId int NOT NULL PRIMARY KEY, ProductName varchar(50) NOT NULL, OrderDate datetime NOT NULL DEFAULT GETDATE() ) DATEPART() Function (SQL Server) Returns a single part of a date/time (year, month, day, hour, minute, etc.). Syntax: DATEPART(datepart,date) datepart can be: year , quarter , month , dayofyear , day , week , weekday , hour , minute , second , millisecond , microsecond , nanosecond . Example: SELECT DATEPART(yyyy,OrderDate) AS OrderYear, DATEPART(mm,OrderDate) AS OrderMonth, DATEPART(dd,OrderDate) AS OrderDay FROM Orders WHERE OrderId=1 DATEADD() Function (SQL Server) Adds or subtracts a specified time interval from a date. Syntax: DATEADD(datepart,number,date) number can be positive (future dates) or negative (past dates). Example: Add 45 days to OrderDate : SELECT OrderId,DATEADD(day,45,OrderDate) AS OrderPayDate FROM Orders DATEDIFF() Function (SQL Server) Returns the time between two dates. Syntax: DATEDIFF(datepart,startdate,enddate) Example: Get number of days between two dates: SELECT DATEDIFF(day,'2008-06-05','2008-08-05') AS DiffDate Example (negative result): SELECT DATEDIFF(day,'2008-08-05','2008-06-05') AS DiffDate CONVERT() Function (SQL Server) General function for converting data into a new data type. Used to display date/time data in different formats. Syntax: CONVERT(data_type(length),data_to_be_converted,style) style specifies the output format (e.g., 101 for mm/dd/yy, 106 for dd mon yy). Example: SELECT CONVERT(VARCHAR(19),GETDATE()) SQL NULL Values Represent missing unknown data. By default, a table column can hold NULL values. If a column is optional, a new record or update can leave it without a value, storing NULL . NULL values are treated differently from other values. Used as a placeholder for unknown or inapplicable values. Note: It is not possible to compare NULL and 0; they are not equivalent. SQL Working with NULL Values Example: "Persons" table. Suppose "Address" column is optional. It is not possible to test for NULL values with comparison operators ($=, <>, $). Use IS NULL and IS NOT NULL operators instead. SQL IS NULL To select records with NULL values in "Address" column: SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NULL Tip: Always use IS NULL to look for NULL values. SQL IS NOT NULL To select records with no NULL values in "Address" column: SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NOT NULL SQL ISNULL(), NVL(), IFNULL() and COALESCE() Functions Example: "Products" table with UnitsOnOrder column that may contain NULL values. If UnitsOnOrder is NULL , the calculation UnitPrice * (UnitsInStock + UnitsOnOrder) will result in NULL . Microsoft's ISNULL() function specifies how to treat NULL values (e.g., ISNULL(UnitsOnOrder,0) makes NULL values zero). Oracle uses NVL() function (e.g., NVL(UnitsOnOrder,0) ). MySQL uses IFNULL() function (e.g., IFNULL(UnitsOnOrder,0) ) or COALESCE() function (e.g., COALESCE(UnitsOnOrder,0) ). SQL Server / MS Access example: SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0)) FROM Products Oracle example: SELECT ProductName,UnitPrice*(UnitsInStock+NVL(UnitsOnOrder,0)) FROM Products MySQL example: SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0)) FROM Products or SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0)) FROM Products Microsoft Access Data Types Data type Description Storage Text Use for text or combinations of text and numbers. 255 characters maximum Memo Used for larger amounts of text. Stores up to 65,536 characters. Note: You cannot sort a memo field. However, they are searchable Byte Allows whole numbers from 0 to 255 1 byte Integer Allows whole numbers between -32,768 and 32,767 2 bytes Long Allows whole numbers between -2,147,483,648 and 2,147,483,647 4 bytes Single Single precision floating-point. Will handle most decimals 4 bytes Double Double precision floating-point. Will handle most decimals 8 bytes Currency Use for currency. Holds up to 15 digits of whole dollars, plus 4 decimal places. Tip: You can choose which country's currency to use 8 bytes AutoNumber AutoNumber fields automatically give each record its own number, usually starting at 1 4 bytes Date/Time Use for dates and times 8 bytes Yes/No A logical field can be displayed as Yes/No, True/False, or On/Off. In code, use the constants True and False (equivalent to -1 and 0). Note: Null values are not allowed in Yes/No fields 1 bit Ole Object Can store pictures, audio, video, or other BLOBs (Binary Large Objects) up to 1GB Hyperlink Contain links to other files, including web pages Lookup Wizard Let you type a list of options, which can then be chosen from a drop-down list 4 bytes MySQL Data Types Three main types: text, number, and Date/Time types. Text types Data type Description CHAR(size) Fixed length string (letters, numbers, special characters). Max 255 characters. VARCHAR(size) Variable length string. Max 255 characters (if > 255, becomes TEXT ). TINYTEXT String, max 255 characters. TEXT String, max 65,535 characters. BLOB Binary Large Objects, max 65,535 bytes. MEDIUMTEXT String, max 16,777,215 characters. MEDIUMBLOB Binary Large Objects, max 16,777,215 bytes. LONGTEXT String, max 4,294,967,295 characters. LONGBLOB Binary Large Objects, max 4,294,967,295 bytes. ENUM(x,y,z,etc.) List of possible values (up to 65,535). If an invalid value is inserted, a blank value is used. Values sorted in order of entry. SET Similar to ENUM , but can contain up to 64 list items and store more than one choice. Number types Data type Description Storage TINYINT(size) -128 to 127 normal. 0 to 255 UNSIGNED . Max digits can be specified. 1 byte SMALLINT(size) -32768 to 32767 normal. 0 to 65535 UNSIGNED . Max digits can be specified. 2 bytes MEDIUMINT(size) -8388608 to 8388607 normal. 0 to 16777215 UNSIGNED . Max digits can be specified. 3 bytes INT(size) -2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED . Max digits can be specified. 4 bytes BIGINT(size) -9223372036854775808 to 9223372036854775807 normal. 0 to 18446744073709551615 UNSIGNED . Max digits can be specified. 8 bytes FLOAT(size,d) Small number with floating decimal point. size is max digits, d is digits right of decimal. 4 bytes DOUBLE(size,d) Large number with floating decimal point. size is max digits, d is digits right of decimal. 8 bytes DECIMAL(size,d) A DOUBLE stored as a string, allowing fixed decimal point. size is max digits, d is digits right of decimal. UNSIGNED attribute: Shifts range to start from zero instead of negative numbers. Date types Data type Description DATE() A date. Format: YYYY-MM-DD . Range: '1000-01-01' to '9999-12-31'. DATETIME() A date and time combination. Format: YYYY-MM-DD HH:MM:SS . Range: '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. TIMESTAMP() A timestamp. Stored as seconds since Unix epoch. Format: YYYY-MM-DD HH:MM:SS . Range: '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. TIME() A time. Format: HH:MM:SS . Range: '-838:59:59' to '838:59:59'. YEAR() A year in two-digit or four-digit format. Four-digit range: 1901 to 2155. Two-digit range: 70 to 69 (1970 to 2069). TIMESTAMP automatically sets to current date/time on INSERT or UPDATE . SQL Server Data Types Character strings Data type Description Storage char(n) Fixed-length character string. Maximum 8,000 characters. n varchar(n) Variable-length character string. Maximum 8,000 characters. varchar(max) Variable-length character string. Maximum 1,073,741,824 characters. text Variable-length character string. Maximum 2GB of text data. Unicode strings Data type Description Storage nchar(n) Fixed-length Unicode data. Maximum 4,000 characters. nvarchar(n) Variable-length Unicode data. Maximum 4,000 characters. nvarchar(max) Variable-length Unicode data. Maximum 536,870,912 characters. ntext Variable-length Unicode data. Maximum 2GB of text data. Binary types Data type Description Storage bit Allows 0, 1, or NULL . binary(n) Fixed-length binary data. Maximum 8,000 bytes. varbinary(n) Variable-length binary data. Maximum 8,000 bytes. varbinary(max) Variable-length binary data. Maximum 2GB. image Variable-length binary data. Maximum 2GB. Number types Data type Description Storage tinyint Allows whole numbers from 0 to 255. 1 byte smallint Allows whole numbers between -32,768 and 32,767. 2 bytes int Allows whole numbers between -2,147,483,648 and 2,147,483,647. 4 bytes bigint Allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807. 8 bytes decimal(p,s) Fixed precision and scale numbers. Allows numbers from $-10^{38}+1$ to $10^{38}-1$. p : Max total digits (1 to 38, default 18). s : Digits right of decimal (0 to p, default 0). 5-17 bytes numeric(p,s) Fixed precision and scale numbers (same as decimal). 5-17 bytes smallmoney Monetary data from -214,748.3648 to 214,748.3647. 4 bytes money Monetary data from -922,337,203,685,477.5808 to 922,337,203,685,477.5807. 8 bytes float(n) Floating precision number data from $-1.79E+308$ to $1.79E+308$. n : 4 or 8 bytes ( float(24) is 4-byte, float(53) is 8-byte, default 53). 4 or 8 bytes real Floating precision number data from $-3.40E+38$ to $3.40E+38$. 4 bytes Date types Data type Description Storage datetime From Jan 1, 1753 to Dec 31, 9999 with 3.33 milliseconds accuracy. 8 bytes datetime2 From Jan 1, 0001 to Dec 31, 9999 with 100 nanoseconds accuracy. 6-8 bytes smalldatetime From Jan 1, 1900 to Jun 6, 2079 with 1 minute accuracy. 4 bytes date Stores a date only. From Jan 1, 0001 to Dec 31, 9999. 3 bytes time Stores a time only to 100 nanoseconds accuracy. 3-5 bytes datetimeoffset Same as datetime2 with a time zone offset. 8-10 bytes timestamp Stores a unique number updated every time a row is created or modified. Value based on internal clock, not real time. Each table can have only one timestamp variable. 8 bytes Other data types Data type Description sql_variant Stores up to 8,000 bytes of data of various data types (except text, ntext, timestamp). uniqueidentifier Stores a globally unique identifier (GUID). xml Stores XML formatted data. Maximum 2GB. cursor Stores a reference to a cursor used for database operations. table Stores a result-set for later processing. SQL FUNCTIONS SQL has many built-in functions for performing calculations on data. SQL Aggregate Functions Return a single value, calculated from values in a column. Useful aggregate functions AVG() - Returns the average value. COUNT() - Returns the number of rows. FIRST() - Returns the first value. LAST() - Returns the last value. MAX() - Returns the largest value. MIN() - Returns the smallest value. SUM() - Returns the sum. SQL Scalar functions Return a single value, based on the input value. Useful scalar functions UCASE() - Converts a field to upper case. LCASE() - Converts a field to lower case. MID() - Extract characters from a text field. LEN() - Returns the length of a text field. ROUND() - Rounds a numeric field to the number of decimals specified. NOW() - Returns the current system date and time. FORMAT() - Formats how a field is to be displayed. The AVG() Function Returns the average value of a numeric column. SQL AVG() Syntax SELECT AVG(column_name) FROM table_name SQL AVG() Example Using "Orders" table, to find the average of "OrderPrice" fields: SELECT AVG(OrderPrice) AS OrderAverage FROM Orders To find customers with OrderPrice higher than the average: SELECT Customer FROM Orders WHERE OrderPrice > (SELECT AVG(OrderPrice) FROM Orders) SQL COUNT The COUNT() function returns the number of rows that match a specified criteria. SQL COUNT(column_name) Syntax Returns the number of values ( NULL values are not counted) of the specified column. SELECT COUNT(column_name) FROM table_name SQL COUNT(*) Syntax Returns the number of records in a table. SELECT COUNT(*) FROM table_name SQL COUNT(DISTINCT column_name) Syntax Returns the number of distinct values of the specified column. SELECT COUNT(DISTINCT column_name) FROM table_name Note: COUNT(DISTINCT) works with Oracle and MS SQL Server, but not MS Access. SQL COUNT(column_name) Example Using "Orders" table, to count orders from "Customer Nilsen": SELECT COUNT(Customer) AS CustomerNilsen FROM Orders WHERE Customer='Nilsen' SQL COUNT(*) Example To count all orders: SELECT COUNT(*) AS NumberOfOrders FROM Orders SQL COUNT(DISTINCT column_name) Example To count unique customers in "Orders" table: SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders The FIRST() Function Returns the first value of the selected column. SQL FIRST() Syntax SELECT FIRST(column_name) FROM table_name SQL FIRST() Example Using "Orders" table, to find the first "OrderPrice": SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders Tip: Workaround if FIRST() is not supported (e.g., in MySQL): SELECT OrderPrice FROM Orders ORDER BY O_Id LIMIT 1 The LAST() Function Returns the last value of the selected column. SQL LAST() Syntax SELECT LAST(column_name) FROM table_name SQL LAST() Example Using "Orders" table, to find the last "OrderPrice": SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders Tip: Workaround if LAST() is not supported (e.g., in MySQL): SELECT OrderPrice FROM Orders ORDER BY O_Id DESC LIMIT 1 The MAX() Function Returns the largest value of the selected column. SQL MAX() Syntax SELECT MAX(column_name) FROM table_name SQL MAX() Example Using "Orders" table, to find the largest "OrderPrice": SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders The MIN() Function Returns the smallest value of the selected column. SQL MIN() Syntax SELECT MIN(column_name) FROM table_name SQL MIN() Example Using "Orders" table, to find the smallest "OrderPrice": SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders The SUM() Function Returns the total sum of a numeric column. SQL SUM() Syntax SELECT SUM(column_name) FROM table_name SQL SUM() Example Using "Orders" table, to find the sum of all "OrderPrice" fields: SELECT SUM(OrderPrice) AS OrderTotal FROM Orders Aggregate functions often need an added GROUP BY statement. The GROUP BY Statement Used in conjunction with 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 condition GROUP BY column_name SQL GROUP BY Example Using "Orders" table, to find the total sum (total order) of each customer: SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer If GROUP BY is omitted when selecting a non-aggregated column and an aggregate function, the result will be incorrect (e.g., `SELECT Customer,SUM(OrderPrice) FROM Orders` would show the total sum for *all* orders for *each* customer, not per customer). GROUP BY More Than One Column Example: SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders GROUP BY Customer,OrderDate The UCASE() Function Converts the value of a field to uppercase. SQL UCASE() Syntax SELECT UCASE(column_name) FROM table_name Syntax for SQL Server (UPPER()) SELECT UPPER(column_name) FROM table_name SQL UCASE() Example Using "Persons" table, to convert "LastName" to uppercase: SELECT UCASE(LastName) as LastName,FirstName FROM Persons The LCASE() Function Converts the value of a field to lowercase. SQL LCASE() Syntax SELECT LCASE(column_name) FROM table_name Syntax for SQL Server (LOWER()) SELECT LOWER(column_name) FROM table_name SQL LCASE() Example Using "Persons" table, to convert "LastName" to lowercase: SELECT LCASE(LastName) as LastName,FirstName FROM Persons The MID() Function Used to extract characters from a text field. SQL MID() Syntax SELECT MID(column_name,start[,length]) FROM table_name column_name : Required. Field to extract from. start : Required. Starting position (starts at 1). length : Optional. Number of characters to return. If omitted, returns the rest of the text. SQL MID() Example Using "Persons" table, to extract the first four characters of "City": SELECT MID(City,1,4) as SmallCity FROM Persons The LEN() Function Returns the length of the value in a text field. SQL LEN() Syntax SELECT LEN(column_name) FROM table_name SQL LEN() Example Using "Persons" table, to select the length of "Address" values: SELECT LEN(Address) as LengthOfAddress FROM Persons The ROUND() Function Used to round a numeric field to the number of decimals specified. SQL ROUND() Syntax SELECT ROUND(column_name,decimals) FROM table_name column_name : Required. Field to round. decimals : Required. Number of decimals to be returned. SQL ROUND() Example Using "Products" table, to display product name and price rounded to nearest integer: SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products The NOW() Function Returns the current system date and time. SQL NOW() Syntax SELECT NOW() FROM table_name SQL NOW() Example Using "Products" table, to display products and prices per today's date: SELECT ProductName, UnitPrice, Now() as PerDate FROM Products The FORMAT() Function Used to format how a field is to be displayed. SQL FORMAT() Syntax SELECT FORMAT(column_name,format) FROM table_name column_name : Required. Field to be formatted. format : Required. Specifies the format. SQL FORMAT() Example Using "Products" table, to display products and prices with date in "YYYY-MM-DD" format: SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate FROM Products