SQL QuickStart Guide
Richard Harris
DevOps, Online & Mobile at TD Bank Group #devops #agile #cloud #java #js #csharp
Overview?
Types of SQL Statements?
Note: Some text consider the SELECT statement to be part of Data Query Language (DQL) rather than Data Manipulation (DML).
SQL Commands?
Contents
SQL Query Syntax?Quick Reference
Data Manipulation Language Syntax (DML)
SELECT [DISTINCT] [TOP (number)] column1, column2....columnN [AS column_alias]
[INTO TableBackup1]
FROM table_name1, table_name2, [AS table_alias]
WHERE CONDITION-1 {AND|OR} CONDITION-2
AND column_name IN (val-1, val-2, ...val-N)
AND column_name BETWEEN val-1 AND val-2
AND column_name LIKE {PATTERN}
[GROUP BY column_name]
[HAVING (CONDITION)]
[ORDER BY column_name {ASC|DESC}];
/* Fetch all fields */
SELECT * FROM table_name;
/* COUNT Clause: */
SELECT COUNT(columname)
FROM table_name
[ WHERE CONDITION ];
/* GROUP BY Clause: */
SELECT SUM(column_name)
FROM? ?table_name
WHERE? CONDITION
GROUP BY column_name;
/* HAVING Clause: */
SELECT SUM(column_name)
FROM? ?table_name
WHERE? CONDITION
GROUP BY column_name
HAVING (arithematic function condition);
/* IN Operator */
SELECT column_name(s)
FROM table_name
WHERE column_name
IN (value1,value2,..)
/* TOP - Return the first records, but not auto filtered / ordered,?
? ?so remember ORDER BY if you want the highest values.
?(i.e. Truncates SELECT results) */
SELECT TOP (number) [ PERCENT ] [ WITH TIES ]
expressions
FROM tables
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]];
/* INSERT INTO Statement: */
INSERT INTO table_name( column1, column2....columnN)?
[OUTPUT ID]
VALUES ( value1, value2....valueN );?
/* INSERT INTO Statement if you are adding VALUES for
all the columns of the table */
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);?
/* INSERT INTO Statement if you are adding multiple rows at once */
INSERT INTO TABLE_NAME
VALUES (value1,value2,value3,...valueN), (value1,value2,value3,...valueN), ...;?
/*? Populate one table using another table (using a Subquery) */
INSERT INTO first_table_name [(column1, column2, ... columnN)]?
? ?SELECT column1, column2, ...columnN?
? ?FROM second_table_name
? ?[WHERE CONDITION];?
/* Creates a backup Copy of a Table */
SELECT * INTO backup_table_name
FROM table_name;
/* Clone Table w/o copying data */
SELECT * INTO clone_table_name
FROM table_name?
WHERE 1 = 0;?
/* JOINs */?
SELECT table1.column1, table2.column2...?
FROM table1 AS t1
[INNER|LEFT|RIGHT|FULL] JOIN table2 AS t2
ON t1.common_field = t2.common_field;
/* INNER JOIN Alternate Syntax */
SELECT a.column_name, b.column_name...?
FROM table1 a, table1 b?
WHERE a.common_field = b.common_field;
/* UPDATE Statement: */
UPDATE table_name?
SET column1 = value1, column2 = value2....columnN=valueN?
[ WHERE CONDITION ];?
/* DELETE Statement: */?
DELETE FROM table_name?
[ WHERE CONDITION ];
/* The SQL TRUNCATE TABLE command is used to delete the data from a table as well as the space allocated for the records. The structure of table remains same. */?
TRUNCATE TABLE table_name;?
Data Definition Language Syntax (DDL)
/* CREATE & DROP Statements: */
/* DATABASE */
CREATE DATABASE database_name;?
DROP DATABASE database_name;
/* TABLE */
CREATE TABLE table_name(?
column1 datatype?[NOT NULL] [IDENTITY(1,1)],?
column2 datatype,
column3 datatype,?
.....?
columnN datatype,?
PRIMARY KEY( one or more columns )?
);?
DROP TABLE table_name;?
/* SQL Server > CREATE TABLE if it does NOT exist */?
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table_name]') AND type in (N'U'))
BEGIN
CREATE TABLE table_name
(??
?...
);?
END
GO
/* SQL Server > DROP TABLE if it exists already */?
IF OBJECT_ID ('dbo.table_name', 'U') IS NOT NULL?
? ?DROP TABLE table_name;??
GO??
CREATE TABLE table_name
(??
?...
);?
GO
/* List All Tables (MS SQL) */
SELECT *
FROM information_schema.tables
ORDER BY table_name;
/* Using VIEWs */
CREATE VIEW view_name AS?
SELECT column1, column2...
FROM table_name?
WHERE [condition];?
DROP VIEW view_name;
/* ALTER TABLE Statements: */
ALTER TABLE table_name {ADD|DROP COLUMN|ALTER COLUMN} column_name {data_type};??
/* ADD / DROP constraints on an existing table */
/* NOT NULL Contrainst */
ALTER TABLE table_name ALTER COLUMN column_name datatype NOT NULL;?
?
/* ADD PRIMARY KEY */
ALTER TABLE table_name?
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);?
?
/* DROP PRIMARY KEY */
ALTER TABLE table_name?
DROP CONSTRAINT MyPrimaryKey;
?
/* ADD UNIQUE Contstraint */
ALTER TABLE table_name?
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);?
?
/* ADD CHECK Constraint */
ALTER TABLE table_name?
ADD CONSTRAINT MyCheckConstraint CHECK (CONDITION);
?
/* DROP a UNIQUE Constraint */?
ALTER TABLE table_name??
DROP CONSTRAINT MyUniqueConstraint;
/* RENAME */
/* Rename Table - Oracle & MySQL */
ALTER TABLE table_name RENAME TO new_table_name;
/* Rename Table - SQL Server */?
sp_rename 'table_name', 'new_table_name';
/* Rename Column - Oracle */
ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;
/* Rename Column - MySQL */
ALTER TABLE table_name?CHANGE COLUMN column_name new_column_name {data_type}
/* Rename Column - SQL SERVER */
sp_rename 'table_name.column_name', 'new_column_name', 'COLUMN';
Data Control Language Syntax (DCL)
/* Data Control Language Syntax (DCL) */
USE database_name;?/* Select a Database in your SQL Schema */
BEGIN TRANSACTION;
COMMIT;
ROLLBACK;
?
SAVEPOINT SAVEPOINT_NAME;?
ROLLBACK TO SAVEPOINT_NAME;?
RELEASE SAVEPOINT savepoint_name; /* Not supported in SQL Server */
?
/* SQL Server */
SAVE TRANSACTION savepoint_name;
ROLLBACK TRANSACTION savepoint_name;
?
Examples
Using VIEWs
/* Simple View */?
CREATE VIEW Customers_View AS
SELECT CustomerID, ContactName
FROM? Customers
;
?
/* Change field names */
CREATE VIEW Games (INum, IDesc, OnHd, Price) AS
SELECT ItemNum, Description, OnHand, Price
FROM Item
WHERE Category=’GME’
;
INSERTing multiple records at once
CREATE TABLE student
(
? ? id INT PRIMARY KEY,
? ? name VARCHAR(50) NOT NULL,
? ? gender VARCHAR(50) NOT NULL,
? ? age INT NOT NULL,
? ? total_score INT NOT NULL,? ?
);
INSERT INTO student?
VALUES (1, 'Jolly', 'Female', 20, 500),?
(2, 'Jon', 'Male', 22, 545),?
(3, 'Sara', 'Female', 25, 600);
Logical Operators > IN Operator
/* Using the IN Condition with Character Values */?
/* (in this example, the equivalent to using 3 OR conditions) */?
SELECT *
FROM suppliers
WHERE supplier_name IN ('Microsoft', 'Oracle', 'Whole Foods');
/* Using the IN Condition with Numeric Values */
/* (in this exaple, the equivalent to using 4 OR conditions) */?
SELECT *
FROM customers
WHERE customer_id IN (5000, 7000, 8000, 9000);
/* Using the IN Condition with the NOT Operator */?
/* (in this example, the equivalent to 3 AND (column <> 'value') conditions) */?
SELECT *
FROM products
WHERE product_name NOT IN ('Pear', 'Banana', 'Bread');
Logical Operators > EXISTS Operator
/* EXISTS Example: Alternate between 'Maria' & 'Mary' in the ContactName */
IF EXISTS (SELECT * FROM Customers WHERE ContactName = 'Maria Anders')
BEGIN
?-- Do what needs to be done if exists
?UPDATE Customers SET ContactName = 'Mary Anders' WHERE CustomerID = 'ALFKI';
END
ELSE
BEGIN
?-- Do what needs to be done if not
UPDATE Customers SET ContactName = 'Maria Anders' WHERE CustomerID = 'ALFKI';
END
GROUP BY & HAVING
/* # Employees by Region */
SELECT REGION, COUNT(*) AS NUM_EMP
FROM EMPLOYEES
GROUP BY REGION;
/* Which Positions do we have multiple Employees? */
SELECT TITLE, COUNT(TITLE)
FROM EMPLOYEES?
GROUP BY (TITLE)
HAVING COUNT(TITLE) > 1;
Summary
Data Types?
SQL Data Type Categories: String (Text), Numeric, Date/Time, Other
Fundamental SQL Server Data Types: int, decimal / numeric, money, bit, datetime, date, time, timestamp, char, varchar, nvarchar, text, uniqueidentifier, varbinary, image [see reference links]
SQL Operators?[see 1.4]
Note: Most commonly you'll use Comparison & Logical Operators in the WHERE clause.
Note: The Comparison Operator '!=' is an alternative to '<>'.?
Expressions [see 1.5]
An Expression (Boolean, Numeric, Date) is a combination of one or more values, operators and SQL functions that evaluate to a value (typically found SELECT or WHERE).?
/* Boolean Expressions */?
SELECT * FROM EMPLOYEES WHERE SALARY = 10000;
/* Numeric Expressions */
SELECT (15 + 6) AS ADDITION;?
/* Date Expressions */?
SELECT CURRENT_TIMESTAMP;
SELECT? GETDATE();
Constraints [see 3.7 & 3.8]
Tips
Using SQL in Your Web Site
To build a web site that shows data from a database, you will need to use:
Quizes
Reference Links
W3Schools
Microsoft Docs?
codecademy.com?
SQL Tutorial?
Table of Contents?
Part 1: Overview
Part 2: DML
Part 3: DDL
Part 4: TCL?
Part 5: Other?
?? (Bookmark Tabs) = Essential References
Note: GRANT & REVOKE, INDEXes,?Triggers & Stored Procedures are covered in Database Management Systems QuickStart Guide .?
Note:?ER Model, Normalization, Design Method, UML, Systems Analysis Approach to Information-Level Requirements are covered in?Database Design Quickstart Guide .
Note: CASE Statement, MERGE, Additional Topics, and Recommended SQL Books are covered in the Further Reading Appendix.
Part 1: Overview
1. Introduction (including SQL Commands details)
What is SQL?
SQL, sometimes pronounced "sequel",?stands for Structured Query Language. It is a declarative language (the standard) used for communicating with and manipulating relational databases.
SQL is an ANSI (American National Standards Institute) and ISO (International Organization for Standardization) standard and is the standard language for Relational Database System. All the Relational Database Management Systems (RDMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server use SQL as their standard database language. It includes database creation, deletion, fetching rows, modifying rows, etc.?
SQL uses commands to create and update tables and to retrieve data from tables. The commands used to retrieve table data are usually called queries. Queries enable users to create database and table structures, retrieve data, perform various types of data manipulation and data administration and query the database in order to extract useful information.?
Statements are composed of clauses. These clauses are constructed out of keywords, field names, expressions, conditions / predicates, and operators.
The most basic form of an SQL query is SELECT-FROM-WHERE. In SQL retrieval commands, fields are listed in the SELECT clause, tables are listed in the FROM clause, and conditions are listed in the WHERE clause.
Popular SQL Extensions:?
SQL allows Users to:?
The general format conventions for SQL statements:?
The four fundamental functions of a database are (CRUD): Create, Read, Update and Delete.?
SQL Commands?
A Query is a statement in SQL which may include commands and actions, written to solicit an answer to a question or to perform an action.?
Types of SQL Statements:?
SQL Commands:?
Data Definition Language (DDL)
Data Manipulation Language (DML)
Data Control Language (DCL)
Transaction Control Language (TCL)
2. RDBMS Concepts
What is RDBMS?
RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.?
Key Concepts?
Terms used interchangeability:?
3. Syntax
All the SQL statements start with any of the keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE. A SQL statement is terminated with a semi-colon (;). The semicolon may not always be required in all contexts and all implementations, but it's usually a good idea to include it.
In SQL, commands are free-format. No rule says that a specific word must begin in a particular position on the line. While there are no special formatting rules in SQL, a common style is to have words that are part of the SQL language, called reserved words (or keywords), appear in all uppercase letters. All other words in commands appear in combination of uppercase and lowercase letters.?
SQL is case insensitive, which means SELECT and select have same meaning in SQL statements.?Single quotes are used for literal strings in SQL (i.e. condition character values).?
Tip: If you need to use a single quote in your string, you use two single quotes to represent the one.?
Tip: It's good practice to capitalize SQL keywords in your SQL queries.?
DML - Data Manipulation Language Syntax
/* SELECT Statement w/ DISTINCT, WHERE, AND/OR, IN, BETWEEN, LIKE, ORDER BY */
SELECT [DISTINCT] column 1, column 2....columnN
FROM table_name
WHERE CONDITION-1 {AND|OR} CONDITION-2
AND column_name IN (val-1, val-2, ...val-N)
AND column_name BETWEEN val-1 AND val-2
AND column_name LIKE {PATTERN}
GROUP BY column_name
ORDER BY column_name {ASC|DESC};
/* GROUP BY & HAVING Clauses: */
SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name
HAVING (arithmetic function condition);
/* COUNT Clause: */
SELECT COUNT(columname)
FROM table_name
[ WHERE CONDITION ];
/* INSERT INTO Statement: */
INSERT INTO table_name( column1, column2....columnN)?
VALUES ( value1, value2....valueN );?
/* UPDATE Statement: */
UPDATE table_name?
SET column1 = value1, column2 = value2....columnN=valueN?
[ WHERE CONDITION ];?
/* DELETE Statement: */?
DELETE FROM table_name?
[ WHERE CONDITION ];
DDL - Data Definition Language Syntax
/* CREATE & DROP Statements: */
CREATE DATABASE database_name;?
DROP DATABASE database_name;?
CREATE TABLE table_name(?
column1 datatype,?
column2 datatype,?
column3 datatype,?
.....?
columnN datatype,?
PRIMARY KEY( one or more columns )?
);?
DROP TABLE table_name;?
/* ALTER TABLE Statements: */
ALTER TABLE table_name {ADD|DROP COLUMN|ALTER COLUMN} column_name {data_type};??
/* RENAME */
/* Rename Table - Oracle & MySQL */
ALTER TABLE table_name RENAME TO new_table_name;
/* Rename Table - SQL Server */?
sp_rename 'table_name', 'new_table_name';
/* Rename Column - Oracle */
ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;
/* Rename Column - MySQL */
ALTER TABLE table_name?CHANGE COLUMN column_name new_column_name {data_type}
/* Rename Column - SQL SERVER */
sp_rename 'table_name.column_name', 'new_column_name', 'COLUMN';
Comments
Comments are used to explain sections of SQL statements, or to prevent execution of SQL statements. Single line comments start with --. Any text between -- and the end of the line will be ignored (will not be executed). Multiple line comments start with /* and end with */. Any text between /* and */ will be ignored. To ignore just a part of a statement, also use the /* */ comment.
Example > Comments
--Select all:
SELECT * FROM Customers;
--Ignore the end of a line?
SELECT * FROM Customers -- WHERE City='Berlin';
--Ignore a statement?
--SELECT * FROM Customers;
SELECT * FROM Products;
/*Select all the columns
of all the records
in the Customers table (multi-line comment):*/
SELECT * FROM Customers;
/* Ignore part of a line */
SELECT CustomerName, /*City,*/ Country FROM Customers;
4. Operators
What is an Operator in SQL??
An operator is a reserved word or a character used primarily in an SQL statement's WHERE clause to perform operation(s), such as comparisons and arithmetic operations. These Operators are used to specify conditions in an SQL statement and to serve as conjunctions for multiple conditions in a statement.?
Most commonly you'll use Comparison & Logical Operators in the WHERE clause. Comparisons can be done between numbers (numerical), text (alphabetical) and dates (chronological). Comparing numbers is straightforward, but when comparing text attributes, the values have to be put in a character field within quations marks.?
Note: The Comparison Operator '!=' is an alternative to '<>'.
Note: A field whose values you derive from existing fields is called a computed field or calculated field. Computed fields can involve addition (+), subtraction (-), multiplication (*), or division (/).??
Tip: When performing division operations on integer values, the results will always be integers and may not always be what you expect.?
IN Operator?
The SQL IN condition (sometimes called the IN condition) allows you to easily test if an expression matches any value in a list of values. It is used to help reduce the need for multiple OR conditions in a SELECT, INSERT, UPDATE, or DELETE statement.
NOT Operator
The NOT operator is used to negate the result of a conditional expression. In SQL, all expressions evaluate to true and false. If the expression is true, the row is selected; if it is false, it is discarded. Therefore, the NOT operator is used to find the rows that do NOT match a certain condition. In essense the NOT operator will cause the query to return the opposite results of a standard query.?
BETWEEN Operator?
The BETWEEN operator allows us to specify a range between one value and another. To check for a value within a certain range we can use the "greater than or equal to" (>=) and "less than or equal to" (<=) operators. The BETWEEN operator functions in the same way with the end points of range included in the condition. The BETWEEN operator can be used in conjunction with numbers, dates, and text.?
Tip: The best way to ensure operator precedence is to use brackets. They also make SQL easier to read.?
References
Examples > Comparison Operators
SELECT * FROM EMPLOYEES WHERE SALARY > 5000;
SELECT * FROM EMPLOYEES WHERE SALARY = 2000;
SELECT * FROM EMPLOYEES WHERE SALARY != 2000;?
SELECT * FROM EMPLOYEES WHERE SALARY <> 2000;?
Examples > Logical Operators?
SELECT * FROM EMPLOYEES WHERE AGE >= 25 AND SALARY >= 6500;
SELECT * FROM EMPLOYEES WHERE AGE >= 25 OR SALARY >= 6500;?
SELECT * FROM EMPLOYEES WHERE AGE IS NOT NULL;?
SELECT * FROM EMPLOYEES WHERE NAME LIKE 'Ko%';?
SELECT * FROM EMPLOYEES WHERE AGE IN ( 25, 26, 27 );?
SELECT * FROM EMPLOYEES WHERE AGE BETWEEN 25 AND 27;?
SELECT AGE FROM EMPLOYEES WHERE EXISTS (SELECT AGE FROM employees WHERE SALARY > 6500);?
SELECT * FROM EMPLOYEES WHERE AGE > ALL (SELECT AGE FROM employees WHERE SALARY > 6500);
SELECT * FROM EMPLOYEES WHERE AGE > ANY (SELECT AGE FROM employees WHERE SALARY > 6500);
SELECT EMPLOYEEID, LASTNAME, FIRSTNAME FROM EMPLOYEES WHERE FIRSTNAME = 'Andrew';?
SELECT EMPLOYEEID, LASTNAME, FIRSTNAME FROM EMPLOYEES WHERE NOT FIRSTNAME = 'Andrew';
/* EXISTS Example: Alternate between 'Maria' & 'Mary' in the ContactName */
IF EXISTS (SELECT * FROM Customers WHERE ContactName = 'Maria Anders')
BEGIN
?-- Do what needs to be done if exists
?UPDATE Customers SET ContactName = 'Mary Anders' WHERE CustomerID = 'ALFKI';
END
ELSE
BEGIN
?-- Do what needs to be done if not
UPDATE Customers SET ContactName = 'Maria Anders' WHERE CustomerID = 'ALFKI';
END
Examples > Logical Operators?> IN Operator
/* Using the IN Condition with Character Values */?
SELECT *
FROM suppliers
WHERE supplier_name IN ('Microsoft', 'Oracle', 'Whole Foods'); /* the equivalent to using 3 OR conditions */?
/* Using the IN Condition with Numeric Values */
SELECT *
FROM customers
WHERE customer_id IN (5000, 7000, 8000, 9000); /* the equivalent to using 4 OR conditions */?
/* Using the IN Condition with the NOT Operator */?
SELECT *
FROM products
WHERE product_name NOT IN ('Pear', 'Banana', 'Bread'); /* the equivalent to 3 AND (column <> 'value') conditions */?
5. Expressions
An expression is a combination of one or more values, operators and SQL functions that evaluate to a value. These SQL EXPRESSIONs are like formulae and they are written in query language. You can also use them to query the database for a specific set of data.?
Types of SQL Expressions:
Syntax
SELECT column1, column2, columnN?
FROM table_name?
WHERE [CONDITION|EXPRESSION];?
Examples
/* Boolean Expressions */
SELECT * FROM EMPLOYEES WHERE SALARY = 10000;?
/* Numeric Expressions */?
SELECT (15 + 6) AS ADDITION;?
SELECT COUNT(*) AS "RECORDS" FROM CUSTOMERS;??
/* Date Expressions */
SELECT CURRENT_TIMESTAMP;?
SELECT GETDATE();
Part 2: DML
1. SELECT Query?
The SELECT statement retrieves data from the database for viewing and is the most frequently used SQL statement. The SQL SELECT statement fetches the data from the database in the form of Result Sets.?
You many choose which columns to return by listing columns in your SELECT statement and you may list them in any order you like.?
The asterisk character (*) is used as an argument in the SELECT clause to signify that all the columns from the underlying table be retrieved. Avoid using this shorthand unless you truly need all the columns; otherwise we are asking the database system to provide information we don`t need, wasting processing power and memory.?
The FROM Clause?
The From Clause specifies the tables from which the SELECT statement will retrieve data. This clause usually refers to one or more tables, but can also include other queries. If you need to include information from more than one table, you separate the table names with commas.?
Syntax
SELECT column1, column2, columnN FROM table_name;?
/* Fetch all fields */
SELECT * FROM table_name;?
Example?
SELECT ID, NAME, SALARY FROM EMPLOYEES;?
/* Fetch all fields */
SELECT * FROM EMPLOYEES;?
2. WHERE Clause?
Use the WHERE clause keyword in combination with the SELECT statement to filter the results of a query. This sets one or more conditions that must be fulfilled by a record before it is included in the results (i.e. fetch only necessary records). Note, the WHERE clause is not only used in the SELECT statement, but it is also used in the UPDATE and DELETE statements.
Note: When a query involves a character field, such as CustomerName, you must enclose the value to which the field is being compared in single quotation marks.?
Syntax
SELECT column1, column2, columnN??
FROM table_name?
[WHERE condition]?
Example
SELECT ID, NAME, SALARY??
FROM EMPLOYEES
WHERE SALARY > 2000;?
SELECT ID, NAME, SALARY??
FROM EMPLOYEES
WHERE NAME= 'John';?
3. AND & OR Conjunctive Operators?
The SQL AND & OR operators are used to combine multiple conditions (compound condition) to narrow data in an SQL statement. These two operators are called conjunctive operators.
?For an action to be taken by the SQL statement, whether it be a transaction or a query, all conditions separated by the AND must be TRUE.?
For an action to be taken by the SQL statement, whether it be a transaction or query, only one (any) of the conditions separated by the OR must be TRUE.?
You can also precede a single condition with the NOT operator to negate a condition.
Combining the AND and OR Conditions
The SQL AND condition and OR condition can be combined to test for multiple conditions in a SELECT, INSERT, UPDATE, or DELETE statement.
When combining these conditions, it is important to use parentheses so that the database knows what order to evaluate each condition. Just like you learned in the order of operations in Math class!
Syntax?
SELECT column1, column2, columnN
FROM table_name?
WHERE [condition1] AND [condition2]...AND [conditionN];
SELECT column1, column2, columnN?
FROM table_name?
WHERE [condition1] OR [condition2]...OR [conditionN];?
Example?
SELECT ID, NAME, SALARY?
FROM EMPLOYEES
WHERE SALARY > 2000 AND age < 25;?
SELECT ID, NAME, SALARY??
FROM EMPLOYEES
WHERE SALARY > 2000 OR age < 25;?
Example > Combining the AND and OR Conditions
SELECT *
FROM suppliers
WHERE (state = 'California' AND supplier_id <> 900)
OR (supplier_id = 100);
UPDATE customers
SET favorite_website = 'techonthenet.com'
WHERE customer_id = 6000
OR (customer_id > 7000 AND last_name <> 'Johnson');
DELETE FROM products
WHERE category_id = 25
OR (product_id < 4 AND product_name <> 'Banana');
4. LIKE Operator using Wildcard Characters
The SQL LIKE operator is used to compare a value to a pattern using wildcard operators. The two wildcards most commonly used in conjunction with the LIKE operator: the percent sign (%) & the underscore (_). The percent sign represents zero, one or multiple characters. The underscore represents a single number or character. These symbols can be used in combination.
You can combine N number of conditions using AND or OR operators. The match condition can be any numeric or string value.??
Wildcard Characters in SQL Server
Syntax?
/* Here, XXXX could be any numeric or string value.? */
SELECT FROM table_name WHERE column LIKE 'XXXX%'??
SELECT FROM table_name?WHERE column LIKE '%XXXX%'?
SELECT FROM table_name?WHERE column LIKE 'XXXX_'?
SELECT FROM table_name?WHERE column LIKE '_XXXX'?
SELECT FROM table_name?WHERE column LIKE '_XXXX_'?
Example?
/* Wildcard Character Examples */
/* Customers with name starting with 'R', i.e. Richard */
SELECT * FROM Customers WHERE Name LIKE 'R%';
/* Customers with a City starting with "ber" */
SELECT * FROM Customers WHERE City LIKE 'ber%';
/* Customers with a City containing the pattern "es" */
SELECT * FROM Customers WHERE City LIKE '%es%';
/* Customers with a City starting with any character, followed by "ondon" */?
SELECT * FROM Customers WHERE City LIKE '_ondon';
/* Customers with a City starting with "L", followed by any character, followed by "n", followed by any character, followed by "on" */?
SELECT * FROM Customers WHERE City LIKE 'L_n_on';
/* Customers with a City starting with "b", "s", or "p" */?
SELECT * FROM Customers WHERE City LIKE '[bsp]%';
/* Customers with a City starting with "a", "b", or "c" */?
SELECT * FROM Customers WHERE City LIKE '[a-c]%';
/* Customers with a City NOT starting with "b", "s", or "p" */
SELECT * FROM Customers WHERE City LIKE '[!bsp]%';
SELECT * FROM Customers WHERE City NOT LIKE '[bsp]%';
5. INSERT Query?
The INSERT INTO statement provides us with an easy way to insert new data into an existing database.?There are two basic syntaxes of the INSERT INTO statement which are shown below.?
We can specify the column names in any order we prefer. Regardless of column order, SQL will perform in the same way as long as the order of the column names set matches the data set.?We can also exclude columns from our INSERT statement (as long as they do not have a NOT NULL constraint).?
You do not need to specify the column(s) name in the SQL query if you are adding VALUES for all the columns of the table. But make sure the order of the VALUES is in the same order as the columns in the table.?
Syntax?
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)]??
VALUES (value1, value2, value3,...valueN);?
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);?
Syntax > Populate one table using another table (using a Subquery)
INSERT INTO first_table_name [(column1, column2, ... columnN)]?
? ?SELECT column1, column2, ...columnN?
? ?FROM second_table_name?
? ?[WHERE condition];?
Example
INSERT INTO EMPLOYEES (ID,NAME,AGE,ADDRESS,SALARY)?
VALUES (1, 'Robert', 32, 'New York', 2000.00 );?
INSERT INTO EMPLOYEES
VALUES (7, 'Muffy', 24, 'Indore', 10000.00 );?
See Also:
6. UPDATE Query?
The SQL UPDATE Query is used to modify the existing records in a table. You can use the WHERE clause with the UPDATE query to update the selected rows, otherwise all the rows would be affected.?You can combine N number of conditions using the AND or the OR operators.?
Tip: We need to consider that for situations in which the condition from ther WHERE clause matches more than one record, all of the matching records will be changed in accordance with the instructions in the UPDATE statement.?
Syntax
UPDATE table_name?
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];?
Example?
UPDATE Customers ?
SET Address = '123 Main Street'?
WHERE ID = 6;?
UPDATE Suppliers?
SET?
ContactName='Selene Pereira',?
ContactTitle='Marketing Manager',?
Phone='(172) 555 5345'
WHERE SupplierID=10;?
7. DELETE Query?
The SQL DELETE Query is used to delete the existing records from a table. You can use the WHERE clause with a DELETE query to delete the selected rows, otherwise all the records would be deleted.?You can combine N number of conditions using AND or OR operators.?
Tip: It's a good idea to audition your DELETE statement & WHERE clause with a SELECT statement.??
Tip: Rather than actually delete a record, you may find that sometimes it is more suitable to have an Active flag (column) and just set it to False when User goes to "delete" the record.?
Syntax?
DELETE FROM table_name?
WHERE [condition];?
Example
DELETE FROM CUSTOMERS?
WHERE ID = 6;?
/* To DELETE all records, ommit the WHERE clause */
DELETE FROM CUSTOMERS;?
8. TOP, LIMIT, ROWNUM Clause?
TOP returns the top 'n' rows or top 'n' percentage of records, based on the SELECT clause.?
Tip: Most TOP queries simply don't make sense without the ORDER BY clause, since SQL otherwise returns what may seems like a meaningless set of records sorted by entry order.
Note: All the databases do not support the TOP clause. For example, MySQL supports the LIMIT clause to fetch a limited number of records, while Oracle uses the ROWNUM command to fetch a limited number of records.?
Syntax
SELECT TOP (number) [ PERCENT ] [ WITH TIES ]
expressions
FROM tables
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]];
Example
/* First 3 records */
SELECT TOP 3 * FROM CUSTOMERS;?
/* Returns highest 3 values */
/* TOP return the first records, not auto filtered / ordered, */
/* so remember ORDER BY if you want the highest values */
SELECT TOP 3 [Freight]? ? ??
? FROM [Orders]
? ORDER BY Freight DESC;
SELECT TOP 5 ProductID, ProductName, UnitPrice FROM Products
ORDER BY UnitPrice DESC;?
SELECT TOP 5 PERFECT ProductID, ProductName, UnitPrice FROM Products
ORDER BY UnitPrice DESC;?
References
9. ORDER BY Clause (Sorting Results)
When you query a relational database, there are no guarantees concerning the order in which the results will be displayed. The results might appear in the order in which the data was originally entered, but even this is not certain. Thus, if the order in which the data is displayed is important, you should specifically request that the results be displayed in a desired order. In SQL, you sort data using the ORDER BY clause.
The?SQL ORDER?BY clause?is?used?to?sort?the?data?in?ascending?(default) or?descending?order, based on one or more columns. Use the keyword ASC to sort the results in ascending order and DESC to sort by descending order.?
The ORDER BY has to be after any WHERE clause.?
Syntax?
SELECT column-list??
FROM table_name?
[WHERE condition]?
[ORDER BY column1, column2, .. columnN] [ASC | DESC];?
Example
SELECT * FROM CUSTOMERS?
ORDER BY NAME ASC;?
SELECT * FROM EMPLOYEES??
ORDER BY HIREDATE DESC;?
SELECT * FROM CUSTOMERS?
ORDER BY COMPANYNAME, CONTACTNAME;?
/* Fetch rows with their own preferred order */?
SELECT * FROM CUSTOMERS??
ORDER BY (CASE COMPANYNAME?
WHEN 'The Big Cheese'? ? THEN 1
WHEN 'Save-a-lot Markets'? ?THEN 2?
ELSE 100 END) ASC, COMPANYNAME ASC, CONTACTNAME ASC;?
10. GROUP BY?Clause
The GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups.?Use Groups to provide refined data aggregation (by using GROUP BY in combination with Built-In Functions).?
When rows are grouped, one line of output is produced for each group. Only statistics calculated for the group or fields whose values are the same for all rows in a group can be displayed in the grouped results.
Tip: It is important to note that the GROUP BY clause does not mean that the query results will be sorted. To display the query results in a particular order, you must use the ORDER BY clause.?
Syntax
SELECT column1, column2?
FROM table_name?
WHERE [ conditions ]?
GROUP BY column1, column2?
ORDER BY column1, column2?
Example
SELECT REGION, COUNT(*) AS NUM_EMP
FROM EMPLOYEES
GROUP BY REGION
SELECT categories.categoryname, AVG(products.unitprice) AS averageprice?
FROM categories INNER JOIN products ON categories.categoriyid=products.productid
GROUP BY categories.categoryname;?
11. HAVING Clause?
The HAVING clause is to groups what the WHERE clause is to rows.?
The WHERE clause places conditions on the selected records, whereas the HAVING clause places conditions on groups created by the GROUP BY clause.?The HAVING clause is usually includes an aggregate function.?
Note: You can include both a WHERE clause and a HAVING clause in the same query.?
Tip: You can have both a HAVING and WHERE clauses. One example of this is where your query has a JOIN and have aggregate and non-aggregate parts within your query.?
Syntax?
SELECT column1, column2?
FROM table1, table2?
WHERE [ conditions ]?
GROUP BY column1, column2?
HAVING [ conditions ]?
ORDER BY column1, column2
Example
SELECT ID, NAME, AGE, ADDRESS
FROM CUSTOMERS
GROUP BY age?
HAVING COUNT(age) >= 2;?
SELECT country?
FROM customers?
GROUP BY country?
HAVING COUNT(customerID >= 5);
12. Using JOINs (Selecting Records from Multiple Tables)
The SQL JOIN clause is used to combine records from two or more tables by matching values common to each.?
An inner join requires that both sets of records involved in the join include matching records. If we want to include records from either side of the sets that are not overlapping, we need to use an outer join, which doesn't require a match on both sides.?
A Join is performed using the JOIN operator (w/ ON keyword) or WHERE clause. Several operators can be used when joining tables, such as =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT. The most common operator is the equal to operator (=).?
Types of Joins
Qualifying Field Names?
When performing Joins, it is often necessary to qualify a field name to specify the particular field you are referencing. To qualify a field name, precede the name of the field with the name of the table, followed by a period. For example, the RepNum field in the Rep table is written as Rep.RepNum.
When there is potential ambiguity in listing field names, you must qualify the fields involved. It is permissible to qualify other fields as well, even if there is no possible confusion. Some people prefer to qualify all fields, which is certainly not a bad approach.
领英推荐
SELECT Orders.OrderNum, OrderDate, Customer.CustomerNum,
CustomerName, Item.ItemNum, Description, NumOrdered, QuotedPrice
FROM Orders, Customer, OrderLine, Item
WHERE Customer.CustomerNum=Orders.CustomerNum
AND Orders.OrderNum=OrderLine.OrderNum
AND OrderLine.ItemNum=Item.ItemNum
ORDER BY Orders.OrderNum
;
Syntax > Inner Join
/* Inner Join (WHERE =) */
SELECT table1.column1, table2.column2...?
FROM table1, table2
WHERE table1.common_field = table2.common_field;
/* Inner Join (JOIN ON) */
SELECT table1.column1, table2.column2...?
FROM table1?
[INNER] JOIN table2?
ON table1.common_field = table2.common_field;
Tip: When Querying multiple tables, you can create Table aliases without having to use the AS keyword, and then use these aliases (i.e. first letter of table name) within your SELECT statement.?
/* Inner Join (JOIN ON w/ Aliases) */
SELECT t1.column1, t2.column2...?
FROM table1 AS t1
INNER JOIN table2 AS t2
ON t1.common_field = t2.common_field;
/* Inner Join (WHERE = w/ Aliases) */
SELECT a.column_name, b.column_name...?
FROM table1 a, table1 b?
WHERE a.common_field = b.common_field;?
Example > Inner Join
/* Inner Join (=) */
SELECT ID, NAME, AGE, AMOUNT?
FROM CUSTOMERS, ORDERS?
WHERE? CUSTOMERS.ID = ORDERS.CUSTOMER_ID;?
/* Inner Join (ON) */
SELECT? ID, NAME, AMOUNT, DATE?
FROM CUSTOMERS?
INNER JOIN ORDERS?
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;?
/* 3 different tables: Products, Categories, and Suppliers */?
SELECT Products.ProductName, Categories.CategoryName, Products.UnitPrice, Suppliers.CompanyName
FROM Categories?
INNER JOIN Products ON Categories.CategoryID=Products.CategoryID?
INNER JOIN Suppliers ON Products.SupplierID=Suppliers.SupplierID
WHERE Categories.CategoryName='Beverages';
Syntax > Left Join?
SELECT table1.column1, table2.column2...?
FROM table1?
LEFT [OUTER] JOIN table2?
ON table1.common_field = table2.common_field;?
Example > Left Join
SELECT ID, NAME, AMOUNT, DATE?
FROM CUSTOMERS?
LEFT JOIN ORDERS?
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;?
Syntax > Right Join
SELECT table1.column1, table2.column2...?
FROM table1?
RIGHT [OUTER] JOIN table2?
ON table1.common_field = table2.common_field;?
Example > Right Join
SELECT? ID, NAME, AMOUNT, DATE?
FROM CUSTOMERS?
RIGHT JOIN ORDERS?
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;?
Syntax > Full Join
SELECT table1.column1, table2.column2...?
FROM table1?
FULL [OUTER] JOIN table2
ON table1.common_field = table2.common_field;?
Example > Full Join
SELECT? ID, NAME, AMOUNT, DATE?
FROM CUSTOMERS?
FULL JOIN ORDERS?
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;?
/* If your Database does not support FULL JOIN (MySQL does not support FULL JOIN),?then you can use UNION ALL clause to combine these two JOINS as shown below:? */
SELECT? ID, NAME, AMOUNT, DATE?
LEFT JOIN ORDERS?
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID?
UNION ALL?
SELECT? ID, NAME, AMOUNT, DATE?
FROM CUSTOMERS?
RIGHT JOIN ORDERS?
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
Syntax > Self Join
SELECT a.column_name, b.column_name...?
FROM table1 a, table1 b?
WHERE a.common_field = b.common_field;?
Syntax > Cartesian Join
SELECT table1.column1, table2.column2...?
FROM? table1, table2 [, table3 ]?
Example > Cartesian Join
SELECT? ID, NAME, AMOUNT, DATE?
FROM CUSTOMERS, ORDERS;
13. UNION Operator
The SQL UNION operator is used to combine the results of two or more SELECT statements without returning any duplicate rows.?
The two tables involved in a union must have the same structure, or be union compatible; in other words, they must have the same number of fields and their corresponding fields must have the same data types.
To use this UNION operator, each SELECT statement must have:?
The UNION ALL operator is used to combine the results of two SELECT?statements including duplicate rows. The same rules that apply to the UNION operator will apply to the UNION ALL operator.?
There are two other operators that are similar to the UNION operator:
Note: Oracle uses the MINUS operator rather than EXCEPT.
Syntax > UNION
SELECT column1 [, column2 ]?
FROM table1 [, table2 ]?
[WHERE condition]?
UNION?[ALL]
SELECT column1 [, column2 ]?
FROM table1 [, table2 ]?
[WHERE condition]?
Syntax > INTERSECT
SELECT column1 [, column2 ]?
FROM table1 [, table2 ]?
[WHERE condition]?
INTERSECT?
SELECT column1 [, column2 ]?
FROM table1 [, table2 ]?
[WHERE condition]
Syntax > EXCEPT
SELECT column1 [, column2 ]?
FROM table1 [, table2 ]?
[WHERE condition]?
EXCEPT?
SELECT column1 [, column2 ]?
FROM table1 [, table2 ]?
[WHERE condition]?
14. NULL Values?
A NULL value in a table is a value in a field that appears to have no value (i.e. left blank during the record creation). It is very important to understand that a NULL value (no value) is different than a zero value or an empty string.?
NOT NULL signifies that column should always accept an explicit value of the given data type.?
The NULL value can cause problems when?selecting?data.?You must use the IS NULL or IS NOT NULL operators to check for a NULL value.?You cannot test for NULL using the = operator.?
Examples > NULL
CREATE TABLE EMPLOYEES(?
? ?ID? ?INT? ? ? ? ? ? ? NOT NULL,?
? ?NAME VARCHAR (20)? ? ?NOT NULL,?
? ?AGE? INT? ? ? ? ? ? ? NOT NULL,?
? ?ADDRESS? CHAR (25),?
? ?SALARY? ?DECIMAL (18, 2),
? ?PRIMARY KEY (ID)?
);?
SELECT? ID, NAME, AGE, ADDRESS, SALARY?
FROM EMPLOYEES
WHERE SALARY IS NOT NULL;?
SELECT? ID, NAME, AGE, ADDRESS, SALARY?
FROM EMPLOYEES
WHERE SALARY IS NULL;?
/* MS SQL equivalent to "DROP TABLE IF EXISTS TableName" */
IF OBJECT_ID ('dbo.Test', 'U') IS NOT NULL?
? ?DROP TABLE Test;??
CREATE TABLE Test(
id INT NOT NULL IDENTITY(1,1),
data VARCHAR(50) NOT NULL,?
note varchar(100),
PRIMARY KEY (id)
);
INSERT INTO Test(data,note) VALUES('Testing 123',NULL);
15. Aliases using the AS keyword
You can rename a table or a column temporarily (for the purpose of a particular SQL query) by giving another name known as Alias via the AS keyword.?
Tip: With aliases you can also combine the data from two or more columns into one column, with the resulting column bearing the alias name. The joining of columns is also known as concatenation.?
Syntax?
/* Table Alias */
SELECT column1, column2....?
FROM table_name AS alias_name?
WHERE [condition];?
/* Column Alias */
SELECT column_name AS alias_name, column_name AS "Alias Name"?
FROM table_name?
WHERE [condition];
Examples
/* Table Alias */
SELECT C.ID, C.NAME, C.AGE, O.AMOUNT??
? ? ?FROM CUSTOMERS AS C, ORDERS AS O?
? ? ?WHERE? C.ID = O.CUSTOMER_ID;?
/* Column Alias */
SELECT? ID AS CUSTOMER_ID, NAME AS CUSTOMER_NAME?
? ? ?FROM CUSTOMERS?
? ? ?WHERE CITY IS NOT NULL;?
SELECT FirstName + ' ' + LastName as FullName
FROM Employees;?
16. SELECT INTO (Copy / Clone Tables)
There may be a situation when you just want to create an exact Copy / Clone of an existing Table to test or perform something without affecting the original table. To do so, use the SQL SELECT INTO Statement.?
SELECT INTO can be used to create a new, empty table using the schema of another (Clone but not Copy). Just add a WHERE clause that causes the query to return no data (i.e. WHERE 1 = 0)
Syntax?
/* Copy all columns into a new table */?
SELECT *
INTO newtable?
FROM oldtable
WHERE condition;
/* Copy only some columns into a new table */
SELECT column1, column2, column3, ...
INTO newtable?
FROM oldtable
WHERE condition;
/* Clone Table w/o copying data */
SELECT * INTO newtable
FROM oldtable
WHERE 1 = 0;
Examples
/* Creates a backup copy of Customers */
SELECT * INTO CustomersBackup1
FROM Customers;
/* Copies only a few columns into a new table */
SELECT CompanyName, ContactName INTO CustomersBackup2
FROM Customers;
/* Copies only the German customers into a new table: */
SELECT * INTO CustomersGermany?
FROM Customers
WHERE Country = 'Germany';
/* Clone Table w/o copying data */
SELECT * INTO CustomersClone1
FROM Customers
WHERE 1 = 0;
/* Copy from one database to another */
SELECT * INTO Northwind2.dbo.CustomersBackup1
FROM Northwind.dbo.Customers;
See Also:
17. Subqueries?
A Subquery or Inner query or a Nested query is a query within another SQL query and embedded within the WHERE clause. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.
?Subqueries are most frequently used with the SELECT statement, but?also?can?be?used in conjuction with INSERT / UPDATE / DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.?
When using Subqueries with the INSERT Statement, the INSERT statement?uses?the data?returned from the subquery?to?insert into another table. The selected data in the subquery can be modified with any of the character, date or number functions. When using a subquery with the UPDATE statement, you can update either a single or multiple columns.?
You can use the ANY operator to compare a value with any value in a list. You must place an =, <>, >, <, <= or >= operator before ANY in your query.?
You can write Subqueries that return multiple columns.
Subquery Rules:?
Note: The inner query is called a subquery and is evaluated first. After the subquery has been evaluated, the outer query can be evaluated.?
Tip: You can also use a Subquery in your FROM clause and even use it with a JOIN.?
Syntax
/* Subquries with the SELECT Statement */
SELECT column_name [, column_name ]?
FROM? ?table1 [, table2 ]?
WHERE? column_name OPERATOR?
? ? ? (SELECT column_name [, column_name ]?
? ? ? FROM table1 [, table2 ]?
? ? ? [WHERE]); ?
/* Subquries with the INSERT Statement */?
INSERT INTO table_name [ (column1 [, column2 ]) ]?
? ?SELECT [ *|column1 [, column2 ]
? ?FROM table1 [, table2 ]?
? ?[ WHERE VALUE OPERATOR ]?
;??
/* Subqueries with the UPDATE Statement */?
UPDATE table?
SET column_name = new_value?
[ WHERE OPERATOR [ VALUE ]
? ?(SELECT COLUMN_NAME?
? ?FROM TABLE_NAME)?
? ?[ WHERE) ]?
/* Subqueries with the DELETE Statement */
DELETE FROM TABLE_NAME?
[ WHERE OPERATOR [ VALUE ]?
? ?(SELECT COLUMN_NAME?
? ?FROM TABLE_NAME)?
? ?[ WHERE) ]?
/* Multiple Column Subquery */
SELECT column1, column2, column3, column4
FROM table1
WHERE (column2, column4) IN(
SELECT column2, MIN(column4)
FROM table1?
GROUP BY column2
);
Example
/* Subqueries with the SELECT Statement */
SELECT ord_num, ord_amount, ord_date, cust_code, agent_code
FROM orders
WHERE agent_code IN(
SELECT agent_code FROM agents
WHERE working_area='Bangalore'
);
/* Using ANY with a Multiple Row Subquery */
SELECT agent_code,agent_name,working_area,commission
FROM? agents
WHERE agent_code=ANY(
SELECT agent_code FROM customer
WHERE cust_country='UK'
);
/* Multiple Column Subquery */
SELECT ord_num, agent_code, ord_date, ord_amount
FROM orders
WHERE (agent_code, ord_amount) IN(
SELECT agent_code, MIN(ord_amount)
FROM orders?
GROUP BY agent_code
);
ANY and ALL Operators
Multiple row subquery returns one or more rows to the outer SQL statement. You may use the IN, ANY, or ALL operator in?outer query to handle a subquery that returns multiple rows.
The ANY and ALL operators are used with a WHERE or HAVING clause.?Both ALL & ANY must be preceded by comparison operators.
The ALL operator returns true if all of the subqueries values meet the condition.?The ANY operators returns true if any of the subquery values meet the condition.?
/* Using ANY with a Multiple Row Subquery */
SELECT agent_code,agent_name,working_area,commission
FROM? agents
WHERE agent_code=ANY(
SELECT agent_code FROM customer
WHERE cust_country='UK'
);
/* List Product Names if ANY Order has a Quantity > 100 of that Product (records in OrderDetails) */
/* Note: "WHERE ProductID = ANY(..." would have the result as "WHERE ProductID IN(..." */
SELECT ProductName
FROM Products
WHERE ProductID = ANY (SELECT ProductID
? ? ? ? ? ? ? ? ? ? ? ?FROM OrderDetails
? ? ? ? ? ? ? ? ? ? ? ?WHERE Quantity > 100);
/* Find the OrderID whose maximum Quantity among all product of */?
/* that Order is greater than average quantity of all Orders */
SELECT OrderID?
FROM [OrderDetails]
GROUP BY OrderID?
HAVING MAX(Quantity) > ALL (SELECT AVG(Quantity)?
? ? ? ? ? ? ? ? ? ? ? ? ? ? FROM [OrderDetails]
? ? ? ? ? ? ? ? ? ? ? ? ? ? GROUP BY OrderID);
18. DISTINCT Keyword (Handling Duplicates)
There?may?be?a?situation?when you have multiple duplicate records in a table.?While fetching such records, it sometimes makes more sense to fetch only unique records without any duplicate records. The SQL DISTINCT keyword is used in conjunction with the SELECT statement to eliminate all the duplicate records and by fetching only the unique records.?
Syntax?
SELECT DISTINCT column1, column2,.....columnN?
FROM table_name?
WHERE [condition]?
Example
SELECT DISTINCT salary FROM employees
ORDER BY salary;?
Part 3: DDL
1. CREATE Database?
Syntax?
CREATE DATABASE DatabaseName;?
Example
CREATE DATABASE testDB
/* View list of databases: */
/* SQL Server */?
SELECT name, database_id, create_date? FROM master.sys.databases??
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')
ORDER BY name;?
EXEC sp_databases; /* Shows Database Size */
/* MySQL */
SHOW DATABASES;?
/* Oracle */
SELECT TABLESPACE_NAME FROM USER_TABLESPACES;
SELECT NAME FROM v$database;
SELECT username AS schema_name FROM all_users ORDER BY username;
2. DROP Database (Delete)
Syntax?
DROP DATABASE DatabaseName;?
Example
DROP DATABASE testDB;?
3. USE Statement (Select Database)
When you have multiple databases in your SQL Schema, then before starting your operation, you would need to select a database where all the operations would be performed.?
The SQL USE statement is used to select any existing database in the SQL schema.?
Syntax
USE DatabaseName;?
Example
USE testDB;?
4. CREATE TABLE
Creating a basic table involves naming the table and defining its columns and each column's data type.?
Syntax
CREATE TABLE table_name(?
? ?column1 datatype?[NOT NULL] [IDENTITY(1,1)],?
? ?column2 datatype,
? ?column3 datatype,
? ?.....?
? ?columnN datatype,?
? ?PRIMARY KEY( one or more columns )?
);?
/* MS SQL SERVER */
/* CREATE TABLE if it does NOT exist */?
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table_name]') AND type in (N'U'))
BEGIN
CREATE TABLE table_name
(??
?...
);?
END
GO
/* DROP TABLE if it exists already */?
IF OBJECT_ID ('dbo.table_name', 'U') IS NOT NULL?
? ?DROP TABLE table_name;??
GO??
CREATE TABLE table_name
(??
?...
);?
GO
/* List All Tables (MS SQL) */
SELECT *
FROM information_schema.tables
ORDER BY table_name;
Examples
/*CREATE TABLE */?
CREATE TABLE EMPLOYEES(?
? ?ID? ?INT? ? ? ? ? ? ? NOT NULL,?
? ?NAME VARCHAR (20)? ? ?NOT NULL,?
? ?AGE? INT? ? ? ? ? ? ? NOT NULL,?
? ?ADDRESS? CHAR (25),?
? ?SALARY? ?DECIMAL (18, 2),?
? ?PRIMARY KEY (ID)?
);?
/*CREATE TABLE w/ Auto-Increment Column */?
CREATE TABLE EMPLOYEES(?
? ?ID? ?INT? ? ? ? ? ? ? NOT NULL IDENTITY(1,1),?
? ?NAME VARCHAR (20)? ? ?NOT NULL,?
? ?AGE? INT? ? ? ? ? ? ? NOT NULL,
? ?ADDRESS? CHAR (25),?
? ?SALARY? ?DECIMAL (18, 2),??
? ?PRIMARY KEY (ID)?
);?
Creating a Table from an Existing Table using the AS keyword
A copy of an existing table can be created using a combination of the CREATE TABLE statement and the SELECT statement. The new table has the same column definitions. All columns or specific columns can be selected. When you will create a new table using the existing table, the new table would be populated using the existing values in the old table.
Note: CREATE TABLE AS is N/A in SQL Server. Instead use SELECT INTO.?
See Also:
5. DROP TABLE (Delete)
You should be very careful while using the DROP command because once a table is deleted then all the information available in that table will also be lost forever.?
Syntax
DROP TABLE table_name;
Example?
DROP TABLE CUSTOMERS;?
6. ALTER TABLE Command?
The SQL ALTER TABLE command is used to add, delete or modify columns in an existing table. You can also use the ALTER TABLE command to add and drop various constraints on an existing table.?
Syntax
/* ADD COLUMN */
ALTER TABLE table_name ADD column_name datatype;?
/* DROP COLUMN */
ALTER TABLE table_name DROP COLUMN column_name;?
/* ALTER COLUMN Data Type */
ALTER TABLE table_name ALTER COLUMN column_name datatype;?
/* NOT NULL Contrainst */
ALTER TABLE table_name ALTER COLUMN column_name datatype NOT NULL;?
/* ADD PRIMARY KEY */
ALTER TABLE table_name?
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);?
/* DROP PRIMARY KEY */
ALTER TABLE table_name?
DROP CONSTRAINT MyPrimaryKey;
/* ADD UNIQUE Contstraint */
ALTER TABLE table_name?
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);?
/* ADD CHECK Constraint */
ALTER TABLE table_name?
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);
/* DROP Constraint */?
ALTER TABLE table_name??
DROP CONSTRAINT MyUniqueConstraint;
Examples
ALTER TABLE customers ADD sex CHAR(1);
ALTER TABLE customers DROP COLUMN sex;?
?
7. Constraints
Constraints are the rules enforced on the data columns of a table. They are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database. Constraints could be either on a column level or a table level.?
Constraints can be specified when a table is created with the CREATE TABLE statement or you can use the ALTER TABLE statement to create constraints even after the table is created.?
?The following are some of the most commonly used constraints available in SQL:
Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So, only create indexes on columns that will be frequently searched against.
Example > CREATE TABLE with Primary / Foreign Key, NOT NULL / DEFAULT / UNIQUE Constraint
/* PRIMARY KEY, NOT NULL, DEFAULT, UNIQUE,? */
CREATE TABLE Persons (
ID int NOT NULL PRIMARY KEY,
? ? LastName varchar(255) NOT NULL UNIQUE,
? ? FirstName varchar(255),
? ? Age int DEFAULT 0,
? ? City varchar(255) DEFAULT 'Toronto'
);
/* To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns: */?
CREATE TABLE Persons (
? ? ID int NOT NULL,
? ? LastName varchar(255) NOT NULL,
? ? FirstName varchar(255),
? ? Age int,
? ? CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);
/* FOREIGN KEY */
CREATE TABLE Orders (
? ? OrderID int NOT NULL PRIMARY KEY,
? ? OrderNumber int NOT NULL,
? ? PersonID int FOREIGN KEY REFERENCES Persons(ID)
);
/* To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns: */
CREATE TABLE Orders (
? ? OrderID int NOT NULL,
? ? OrderNumber int NOT NULL,
? ? PersonID int,
? ? PRIMARY KEY (OrderID),
? ? CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
? ? REFERENCES Persons(ID)
);
/* To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns: */
CREATE TABLE Persons (
? ? ID int NOT NULL,
? ? LastName varchar(255) NOT NULL,
? ? FirstName varchar(255),
? ? Age int,
? ? CONSTRAINT UC_Person UNIQUE (LastName, FirstName)
);
/* List all foreign keys (constraint) referencing a given table in SQL Server */
sp_help 'TableName';
Example > ALTER TABLE with Primary / Foreign Key, NOT NULL / DEFAULT / UNIQUE Constraint?
/* ALTER TABLE > PRIMARY KEY */
ALTER TABLE Persons
ADD PRIMARY KEY (ID);
/* To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns: */
/* Note: If you use the ALTER TABLE statement to add a primary key,?
? ?the primary key column(s) must already have been declared to not contain NULL values?(when the table was first created). */
ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);
/* DROP a PRIMARY KEY Constraint */?
ALTER TABLE Persons
DROP CONSTRAINT PK_Person;
/* ALTER TABEL > FOREIGN KEY */
ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(ID);
/* To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple column */?
ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(ID);
/* DROP a FOREIGN KEY Constraint */
ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;
/* ALTER TABLE > NOT NULL */?
ALTER TABLE Persons
ALTER COLUMN Age int NOT NULL;
/* ALTER TABLE > DEFAULT */?
ALTER TABLE Persons
ADD CONSTRAINT df_City
DEFAULT 'Toronto' FOR City;
/* Drop a DEFAULT constraint in Oracle */
ALTER TABLE Persons?
ALTER COLUMN salary DROP DEFAULT;
/* List DEFAULT Constraints in SQL Server */
/* Note: sys.default_constraints, sys.check_constraints, sys.key_constraints, sys.foreign_keys */
SELECT?
? ? TableName = t.Name,
? ? ColumnName = c.Name,
? ? dc.Name,
? ? dc.definition
FROM sys.tables t
INNER JOIN sys.default_constraints? dc ON t.object_id = dc.parent_object_id
INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND c.column_id = dc.parent_column_id
WHERE t.name = 'Persons'
ORDER BY t.Name
/* DROP a DEFAULT Constraint in SQL Server */?
ALTER TABLE Persons DROP CONSTRAINT df_city;
/* ALTER TABLE > UNIQUE */
/* Note: Named UNIQUE contraint (below) is better beause it prevents you from creating multiple?of the same UNIQUE key where as ADD UNIQUE can be run multiple times. */
ALTER TABLE Persons
ADD UNIQUE (LastName);?
/* To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns: */?
ALTER TABLE Persons
ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);
/* DROP a UNIQUE Constraint */
ALTER TABLE Persons
DROP CONSTRAINT UC_Person;
/* List all foreign keys (constraint) referencing a given table in SQL Server */
sp_help 'TableName';
Examples > CHECK Constraint?
/* This CHECK constraint ensures that the age of a person must be 18 or older: */
CREATE TABLE Persons (
? ? ID int NOT NULL,
? ? LastName varchar(255) NOT NULL,
? ? FirstName varchar(255),
? ? Age int CHECK (Age>=18 AND Age IS NOT NULL)
);
/* To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns: */?
CREATE TABLE Persons (
? ? ID int NOT NULL,
? ? LastName varchar(255) NOT NULL,
? ? FirstName varchar(255),
? ? Age int,
? ? City varchar(255),
? ? CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Toronto')
);
/* Create a CHECK constraint on the "Age" column when the table already exists: */
/* Note: Named constraints are better because the statement below allows for duplicate CHECK constraints */
ALTER TABLE Persons
ADD CHECK (Age>=18);
/* To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns: */
ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Toronto');
/* DROP a CHECK Constraint */
ALTER TABLE Persons
DROP CONSTRAINT CHK_PersonAge;
Example > INDEX Constraint?
/* Creates an index on a table. Duplicate values are allowed: */
CREATE INDEX idx_lastname
ON Persons (LastName);
/* Creates a unique index on a table. Duplicate values are not allowed: */
CREATE UNIQUE INDEX idx_lastname
ON Persons (LastName);
/* Create an index on a combination of columns */
CREATE INDEX idx_fname
ON Persons (LastName, FirstName);
/* DROP INDEX Statement */?
DROP INDEX Persons.idx_lastname;
8. TRUNCATE TABLE Command?
The SQL TRUNCATE TABLE command is used to delete the data from a table as well as the space allocated for the records. The structure of table remains same.?
Syntax?
TRUNCATE TABLE table_name;?
Example
TRUNCATE TABLE CUSTOMERS;?
9. Using VIEWs
A view is a predefined SQL SELECT statement that is stored in the database with an associated name. You can think of them like a type of virtual table. Views?can?be?created from a single table, multiple tables or another view.?
Views are used to give each user his or her own view of the data in a database. In SQL, a defining query creates a view. When you enter a query that references a view, it is merged with the defining query to produce the query that is actually executed.?
Views also provide a measure of security because omitting sensitive tables or fields from a view will render them unavailable to anyone who is accessing the database via that view.
The use of views provides several advantages:?
Views allow users to do the following:?
Tip: A view can also join two or more tables.?
Tip: You can use SQL to change the field names in a view by including the new field names in the CREATE VIEW command.
Syntax?
CREATE VIEW view_name AS?
SELECT column1, column2...
FROM table_name?
WHERE [condition];?
DROP VIEW view_name;
Example
/* Simple View */
CREATE VIEW Customers_View AS
SELECT CustomerID, ContactName
FROM? Customers;
/* Change field names */
CREATE VIEW Games (INum, IDesc, OnHd, Price) AS
SELECT ItemNum, Description, OnHand, Price
FROM Item
WHERE Category=’GME’
;
DROP VIEW Customers_View;
Performing Updates Using a View?
If a view satisfies all the these rules then you can update that view:
Note: Rows of data can also be inserted & deleted from a view. The same rules that apply to the UPDATE command also apply to the INSERT and DELETE command.?
Examples:?
UPDATE Customers_View?
SET ContactName = 'R.J."
WHERE ContactName='Richard';
DELETE FROM Customers_View?
WHERE ContactName = 'R.J.';
??
10. Temporary Tables?
There are RDBMS that support Temporary Tables. Temporary Tables are a great feature that lets you store and process intermediate results by using the same selection, update, and join capabilities that you can use with typical SQL Server tables. The temporary tables?could be?very useful in some cases to keep temporary data.?
The most important thing that should be known for temporary tables is that they will be deleted when the current client session terminates. By default, all the temporary tables are deleted by the RDBMS when your database connection gets terminated. Still if you want to delete them in between, then you can do so by issuing a DROP TABLE command.?
If you issue a list tables query, your temporary table will not be listed. If you log out of the RDMBS session and then issue a SELECT command, you will find your temporary table will not exist.
In SQL Server, the name of a temporary table must start with a hash (#).
Examples?
CREATE TABLE #SALESSUMMARY (?
product_name VARCHAR(50) NOT NULL?
,total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00?
,avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00?
,total_units_sold INT NOT NULL DEFAULT 0?
);?
INSERT INTO #SALESSUMMARY?
(product_name, total_sales, avg_unit_price, total_units_sold)?
VALUES ('cucumber', 100.25, 90, 2);?
SELECT * FROM #SALESSUMMARY;
/* List All Tables - Temp Table NOT listed */
SELECT *
FROM information_schema.tables
ORDER BY table_name;
DROP TABLE #SALESSUMMARY;
SELECT * FROM #SALESSUMMARY; /* Invalid object name '#SALESSUMMARY'. */?
References?
11. Using IDENTITY columns and SEQUENCEs?
IDENTITY
Note: Rather than use @@IDENTITY to get the last inserted ID value (SELECT @@IDENTITY after an INSERT), you can get an INSERT query to OUTPUT it. See examples found below.?
Tip: An integer key is useful for simplicity, but for security reasons, many people choose to use a value called a Universally unique identifier (UUID aka GUID).?
SEQUENCE?
Syntax?
IDENTITY [( seed, increment)]
CREATE TABLE table_name?
(
? ? column_name INT PRIMARY KEY IDENTITY(seed,increment),
...
);
CREATE SEQUENCE [dbo].[sequence_name]
?AS INT
?START WITH [seed]
?INCREMENT BY [increment]
?[MAXVALUE X]
;
Example > IDENTITY (Using an Auto-Increment Column)
CREATE TABLE Cars1
(
? ? id INT PRIMARY KEY IDENTITY(1,1),?
? ? name VARCHAR(50) NOT NULL,
? ? company VARCHAR(50) NOT NULL,
? ? power INT NOT NULL
);
/* Auto-assigned next ID value */?
INSERT INTO Cars1 VALUES ('Corrolla', 'Toyota', 1800);?
Example > SEQUENCE?
CREATE SEQUENCE [dbo].[SequenceCounter]
?AS INT
?START WITH 1
?INCREMENT BY 1;
CREATE TABLE Cars2
(
? ? id INT,
? ? name VARCHAR(50) NOT NULL,
? ? company VARCHAR(50) NOT NULL,
? ? power INT NOT NULL
);
CREATE TABLE Cars3
(
? ? id INT,
? ? name VARCHAR(50) NOT NULL,
? ? company VARCHAR(50) NOT NULL,
? ? power INT NOT NULL
);?
?/* NEXT VALUE FOR [dbo].[SequenceCounter] */?
INSERT INTO Cars2 VALUES (NEXT VALUE FOR [dbo].[SequenceCounter], '208', 'Peugeot', 5400);
INSERT INTO Cars3 VALUES (NEXT VALUE FOR [dbo].[SequenceCounter], 'C500', 'Mercedez', 5000);?
Example > @@IDENTITY Function (Obtain Auto-Increment Value)?
CREATE TABLE IdentityTest?
(
? ? id INT PRIMARY KEY IDENTITY(1,1),
? ? info VARCHAR(50) NOT NULL
);
SELECT MAX(id) FROM IdentityTest; /* NULL */
INSERT INTO IdentityTest(info) VALUES('Hello');
INSERT INTO IdentityTest(info) VALUES('World');
SELECT MAX(id) FROM IdentityTest;? /* 2 */
SELECT @@IDENTITY; /* 2 */
Example > IDENTITY Function?
/* Example: Original Customers.CustomerID was 5 Letter Code (varchar) */
SELECT IDENTITY( INT, 1, 1) AS NEW_ID,??
? ? ? [CompanyName]
? ? ? ,[ContactName]
? ? ? ,[ContactTitle]
? ? ? ,[Address]
? ? ? ,[City]
? ? ? ,[Region]
? ? ? ,[PostalCode]
? ? ? ,[Country]
? ? ? ,[Phone]
? ? ? ,[Fax]
INTO CustomerNewID
FROM [dbo].[Customers]
;
/* Displays Identity column w/ Seed and Increment values */?
sp_help 'CustomerNewID'?
Example > OUTPUT?
CREATE TABLE IdentityTest?
(
? ? id INT PRIMARY KEY IDENTITY(1,1),
? ? info VARCHAR(50) NOT NULL
);
INSERT INTO IdentityTest(info)
OUTPUT inserted.id
VALUES('Hello World');
INSERT INTO IdentityTest(info)
OUTPUT inserted.id
VALUES('Testing 1,2,3');
Part 4: TCL
1. Transactions?
Anytime we have an activity made up of steps that must happen together we'll use a transaction.?In database terminology, a transaction is a group of operations that are handled as one unit of work (i.e. INSERT INTO Sales & UPDATE Inventory tables at once).?Transactions group queries or statements into a block of activities, where, if one of the components fail for any reason, the whole group of statements is not executed, and anything that's partially done is rolled back. This helps to guarantee that an action isn't only partially applied, leaving the database in an inconsistent state.?
Tip: Transactions are commonly used to increase performance. If a long list of inserts or updates are performed as a unit they can be performed much faster than as individual statements.?
Properties of Transactions (ACID)?
Transactional Control Commands
Savepoints
SQL Server
Syntax
BEGIN TRANSACTION
...
COMMIT;
ROLLBACK;
SAVEPOINT SAVEPOINT_NAME;?
ROLLBACK TO SAVEPOINT_NAME;?
RELEASE SAVEPOINT savepoint_name; /* Not supported in SQL Server */
/* SQL Server */
SAVE TRANSACTION savepoint_name;
ROLLBACK TRANSACTION savepoint_name;
Example
CREATE TABLE TestTable(
id INT NOT NULL IDENTITY(1,1),
data VARCHAR(50) NOT NULL,?
PRIMARY KEY (id)
);
BEGIN TRANSACTION?
INSERT INTO TestTable VALUES
('Beautiful'),
('World'),
('Hello');
SAVE TRANSACTION SP1;??
DELETE FROM TestTable WHERE id=1;?
?
SAVE TRANSACTION SP2; ?
DELETE FROM TestTable WHERE id=2;
?
SAVE TRANSACTION SP3; ?
DELETE FROM TestTable WHERE id=3;?
?
ROLLBACK TRANSACTION SP3;?
COMMIT;
BEGIN TRANSACTION?
INSERT INTO TestTable VALUES('Ciao'); ?
ROLLBACK;
SELECT * FROM TestTable; /* id: 3, data: 'Hello' */
Part 5: Other
1. Built-In SQL Server Functions (Date, String, Math / Numeric, Advanced)?
SQL Server has the built-in functions. The most popular among the Math/Numeric Functions (aggregate) are: AVG, COUNT, MAX, MIN, and SUM. There are also various String (e.g. FORMAT, LEN, SUBSTRING, and REPLACE), Date (e.g. GETDATE, DATEFIDFF, and DATEPART), and Advanced Functions (e.g. CAST, CONVERT, and IIF).?
A few functions that I find particularly interesting for parsing / importing data: ISNULL, ISNUMERIC, ISDATE, LEN.
Sections
Key SQL Server String Functions?
Note: Trailing spaces at the end of a string are not included when calculating the length. However, leading spaces at the start of the string is included when calculating the length with the LEN() function.
Note: The DATALENGTH() function counts both leading and trailing spaces when calculating the length of the expression.
Tip: If replacing characters in a string with an empty string (''), you can use TRIM rather than REPLACE, which would also allow you to remove multiple characters at once (see example below).??
Examples > String Functions
/* CHARINDEX(substring, string, start); */
SELECT CHARINDEX('OM', 'Customer') AS MatchPosition; /* 5 */
SELECT CHARINDEX('mer', 'Customer', 3) AS MatchPosition; /* 6 */
/* CONCAT(string1, string2, ...., string_n); */
SELECT CONCAT('Microsoft', '.com'); /* Microsoft.com */
SELECT CONCAT('SQL', ' is', ' fun!'); /* SQL is fun! */
SELECT CONCAT('SQL', ' ', 'is', ' ', 'fun!'); /* SQL is fun! */
/* Concatination using + operator: string1 + string2 + string_n */
SELECT 'Microsoft' + '.com'; /* Microsoft.com */?
SELECT 'SQL' + ' ' + 'is' + ' ' + 'fun!'; /* SQL is fun! */?
/* DATALENGTH(expression); */
SELECT DATALENGTH('Microsoft.com'); /* 13 */?
SELECT DATALENGTH('2017-08'); /* 7 */?
/* FORMAT(value, format, culture); */?
DECLARE @d DATETIME = '12/01/2018';??
SELECT FORMAT (@d, 'd', 'en-US') AS 'US English Result',?
? ? ? ?FORMAT (@d, 'd', 'no') AS 'Norwegian Result';
SELECT FORMAT(123456789, '##-##-#####'); /* 12-34-56789 */
/* LEN(string) */
SELECT LEN('Microsoft.com'); /* 13 */?
SELECT LEN('? Microsoft.com? '); /* 15 */?
SELECT LEN('2017-08'); /* 7 */?
/* REPLACE(string, old_string, new_string); */?
SELECT REPLACE('SQL Tutorial', 'T', 'M'); /* SQL MuMorial (case-insensitive) */
SELECT REPLACE('SQL Tutorial' COLLATE Latin1_General_BIN, 'T', 'M'); /* SQL Mutorial (case-sensitive) */?
SELECT REPLACE('SQL Tutorial', 'SQL', 'HTML'); /* HTML Tutorial */
SELECT REPLACE('ABC ABC ABC', 'a', 'c'); /* cBC cBC cBC */
/* SUBSTRING(string, start, length); */
SELECT SUBSTRING('SQL Tutorial', 1, 3) AS ExtractString; /* SQL */
SELECT SUBSTRING(CustomerName, 1, 5) AS ExtractString /* First 5 letters of each Name */
FROM Customers;
/* TRIM([characters FROM ]string) */
SELECT TRIM('? ? ?SQL Tutorial!? ? ?') AS TrimmedString; /* SQL Tutorial! */?
SELECT TRIM('#! ' FROM '? ? #SQL Tutorial!? ? ') AS TrimmedString; /* SQL Tutorial */?
/* LEFT(string, number_of_chars); */?
SELECT LEFT('SQL Tutorial', 3) AS ExtractString; /* SQL */?
SELECT LEFT(CustomerName, 5) AS ExtractString /* First 5 letters of each Name */
FROM Customers;
/* LOWER(text); */?
SELECT LOWER('SQL Tutorial is FUN!'); /* sql tutorial is fun! */?
SELECT LOWER(CustomerName) AS LowercaseCustomerName
FROM Customers
Key SQL Server Math/Numeric Functions?
Tip: When using COUNT, if you specify * or the Primary Key column you'll get a result indicating the total number of rows in a table. But if you specify a non Primary Key column, the result will only count the rows where the listed column has data.?
Examples > Math Functions:?
/* AVG(expression) */
SELECT AVG(Price) AS AveragePrice FROM Products;
/* List Products with Price greater than the average Price value */
SELECT * FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);
/* COUNT(expression) */
SELECT COUNT(*) FROM Products;?
SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;?
SELECT COUNT(DISTINCT HeadOfState) FROM Country;?
/* MAX(expression) */
SELECT MAX(Price) AS LargestPrice FROM Products;
/* MIN(expression) */
SELECT MIN(Price) AS SmallestPrice FROM Products;
/* ROUND(number, decimals, operation) */?
/* Note: operation is Optional. If 0, it rounds the result to the number of decimal.
If a value other than 0, it truncates the result to that number of decimals. Default value is 0 */?
SELECT ROUND(235.415, 2) AS RoundValue; /* 235.420 */
SELECT ROUND(235.415, 2, 1) AS RoundValue; /* 235.410 */
SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;
SELECT SUM(UnitsInStock) FROM Products;?
/* Total Income from All Items Sold */?
SELECT SUM([UnitPrice]? * [Quantity] * (1-[Discount])) AS TotalIncome
FROM [OrderDetails];
SQL Server Date Functions?
Note: FORMAT (String Function) - Formats a value with the specified format.
Note: Coordinated Universal Time (UTC) replaced Greenwich Mean Time (GMT) as the World standard for time. The GETUTCDATE() function returns the current database system UTC date and time, in a 'YYYY-MM-DD hh:mm:ss.mmm' format. So depending where your server is located, you could have GETDATE() = 2020-06-22 01:38:44.100, but SELECT GETUTCDATE() = 2020-06-22 00:39:29.977.
Examples > Date Functions
/* 1. Example > Current Date & Time: */
/* Returns the current date and time: */
SELECT CURRENT_TIMESTAMP;
Result:?
2020-06-21 05:59:05.937
/* Return the current database system date and time: */
SELECT GETDATE();
Result:?
2020-06-21 05:57:18.710
/* Return the date and time of the SQL Server: */
SELECT SYSDATETIME() AS SysDateTime;
Result:?
2020-06-21 05:59:45.2850395
/* 2. Example > Add to Date: */
/*? DATEADD(interval, number, date) */
SELECT DATEADD(year, 1, '2017/08/25') AS DateAdd;
Result:?
2018-08-25 00:00:00.000
SELECT DATEADD(month, -2, '2017/08/25') AS DateAdd;
Result:?
2017-06-25 00:00:00.000
SELECT DATEADD(hour, 6, '2017/08/25 03:00:00') AS DateAdd;
Result:?
2017-08-25 09:00:00.000
/* 3. Example > Difference Between Two Dates: */
/* DATEDIFF(interval, date1, date2) */
SELECT DATEDIFF(year, '1984/10/14', CURRENT_TIMESTAMP) AS DateDiff;
Result:?
36
/* 4. Example > Get a Date from the specified parts (year, month, and day values): */
SELECT DATEFROMPARTS(2018, 10, 31) AS DateFromParts;
Result:
2018-10-31
/* 5. Example > Get Part of a Date: */
/* DATENAME returns the result as a string value */
SELECT DATENAME(HOUR, '2017/08/25 08:36') AS DatePartString;
Result:?
8
/* DATEPART returns the result as an integer value */
SELECT DATEPART(HOUR, '2017/08/25 08:36') AS DatePartInt;
Result:?
8?
/* Day of Week */?
SELECT DATEPART(DW, CURRENT_TIMESTAMP) AS DayOfWeek; /* Sunday = 1 */
SELECT DATEPART(WEEKDAY, CURRENT_TIMESTAMP) AS DayOfWeek;
Result:?
1
SELECT DATEPART(DW,'2020/06/26') /* DAYOFWEEK Friday = 6 */
Result:
6
/* Day of Year */
SELECT DATEPART(DAYOFYEAR,'2020/03/31');
SELECT DATEPART(DY,'2020/03/31');
Result:?
91
/* Week of Year */?
SELECT DATEPART(WEEK,'2020/03/31');
Result:?
14
/* 6. Example > DAY, MONTH, YEAR: */
/* Day of Month */
SELECT DAY('2017/08/25') AS DayOfMonth;
Result:?
25
/* Month Part for a date */?
SELECT MONTH('2017/08/25') AS Month;
Result:?
8
/* Year Part for a date */?
SELECT YEAR('2017/08/25') AS Year;
Result:?
2017?
/* 7. Example > Check for valid Date: */
SELECT ISDATE('2017-08-25');
Result:
1
SELECT ISDATE('2017');
Result:?
1
SELECT ISDATE('Hello world!');
Result:
0
/* 8. Example > String to Date: */
/* CAST(expression AS datatype(length)) */?
SELECT CAST('2017-08-25' AS datetime);
Result:?
2017-08-25 00:00:00.000
/* CONVERT(data_type(length), expression, style) */
SELECT CONVERT(datetime, '2017-08-25');
Result:?
2017-08-25 00:00:00.000
SELECT CONVERT(datetime, '2017-08-25');
Result:?
2017-08-25 00:00:00.000
/* Date to String: */
SELECT CONVERT(varchar, getdate(), 107);
Result:?
Jun 21, 2020
SELECT FORMAT (getdate(), 'MMM dd, yyyy');
Result:?
Jun 21, 2020
/* 9. Example > Format Date (Date to String): */
/* FORMAT(value, format, culture) */
/* Custom Date Format */
SELECT FORMAT (CURRENT_TIMESTAMP, 'yyyy-MM-dd hh:mm:ss');
Result:?
2020-06-21 07:10:35
/* Custom Date Format */
SELECT FORMAT (CURRENT_TIMESTAMP, 'dd/MM/yyyy');
Result:
21/06/2020
/* Return Current Time */
SELECT FORMAT (CURRENT_TIMESTAMP, 'HH:MM:ss');
Result:?
07:06:21
/* Abbreviated Month & Weekday Name */
SELECT FORMAT (CURRENT_TIMESTAMP, 'ddd MMM dd, yyyy');
Result:
Sun Jun 21, 2020
/* Full Name of Month & Weekday Name */
SELECT FORMAT (CURRENT_TIMESTAMP, 'dddd MMMM dd, yyyy');
Result:
Sunday June 21, 2020
Key SQL Server Advanced Functions?
Differences Between CAST and CONVERT
Both CAST & COVERT use expression & datatype(length), except CAST uses 1 parameter with AS keyword, where as COVERT uses multiple parameters including the added optional parameter style.?
The CAST function is ANSI standard and is compatible to use in other databases while the CONVERT function is a specific function of the SQL server.?
Since the CAST function is compatible with other databases, it is also described as portable though it has fewer features compared to the CONVERT function. The CONVERT function, meanwhile, can do some things that the CAST function cannot. The CAST function is used to convert a data type without a specific format. The CONVERT function does converting and formatting data types at the same time.
Examples > Advanced Functions
/* CAST(expression AS datatype(length)) */?
SELECT CAST(25.65 AS int); /* 25 */
SELECT CAST(25.65 AS varchar); /* 25.65 */?
SELECT CAST('2017-08-25' AS datetime); /* 2017-08-25 00:00:00.000 */?
/* CONVERT(data_type(length), expression, style) */
SELECT CONVERT(int, 25.65); /* 25 */?
SELECT CONVERT(varchar, 25.65); /* 25.65?
SELECT CONVERT(datetime, '2017-08-25'); /* 2017-08-25 00:00:00.000 */?
/* COALESCE(val1, val2, ...., val_n) */
SELECT COALESCE(NULL, NULL, NULL, 'Microsoft.com', NULL, 'Example.com'); /* Microsoft.com */
SELECT COALESCE(NULL, 1, 2, 'Microsoft.com'); /* 1 */
/* IIF(condition, value_if_true, value_if_false) */
SELECT IIF(500<1000, 'YES', 'NO'); /* YES */
SELECT IIF(500<1000, 5, 10); /* 5 */
SELECT OrderID, Quantity, IIF(Quantity>10, 'IN STOCK', 'ORDER') AS Restock
FROM OrderDetails;
/* ISNULL(expression, value) */?
SELECT ISNULL(NULL, 'Microsoft.com'); /* Microsoft.com */
SELECT ISNULL('Hello', 'Microsoft.com'); /* Hello */
SELECT ISNULL(NULL, ''); /* replace NULL with empty string */
/* ISNUMERIC(expression) */
SELECT ISNUMERIC(4567); /* 1 */?
SELECT ISNUMERIC('4567'); /* 1 */
SELECT ISNUMERIC('Hello world!'); /* 0 */?
SELECT ISNUMERIC('2017-08-25'); /* 0 */?
/* NULLIF(expr1, expr2) */?
SELECT NULLIF(25, 25); /* returns NULL */?
SELECT NULLIF('Hello', 'Hello'); /* returns NULL */?
SELECT ISNULL(NULLIF('Hello', 'Hello'),'SAME'); /* SAME */
SELECT NULLIF('Hello', 'world'); /* Hello */?
2. SQL Injection?
If you take user input through a web page and insert it into a SQL database, there is a chance that you are open to a security issue known as an SQL Injection attack.
Injection usually occurs when you ask a user for input, like their name and instead of a name they give you a SQL statement that you will unknowingly run on your database. Never trust user provided data, process this data only after validation.
Preventing SQL Injection
Further Reading
Free Database eBooks
Recommended Textbooks
CASE Statement (Conditional expressions)
The CASE statement goes through conditions and returns a value when the first condition is met (like an IF-THEN-ELSE statement).
Suggested Reading:
MERGE
In a typical data warehousing application, quite often during the ETL cycle (Extract, Transform, Load) that you need to perform INSERT, UPDATE and DELETE operations on a target table by matching the records from the source table. Beginning with SQL Server 2008, you can use MERGE command to perform these operations in a single statement. The MERGE statement is used to synchronize two tables by inserting, deleting, and updating the target table rows based on the join condition with the source table.
Suggested Reading:
Data Analyst | BI Analyst | Power BI | Tableau | SQL | Excel | Python | SAP S/4HANA | Signavio |
2 年The most useful article I've ever seen. Thx Richard.
Good job ??
RPA Developer | UiPath | Process Automation
3 年This is so useful for a newbie in SQL like me, thank you very much for sharing!
Middle Office Securities | Trade Support | Investment Operations Specialist | Settlement Specialist | Trade Processing
3 年Nice recap!