SQL AVG() Syntax
1. SELECT AVG(column_name) FROM table_name
2. SELECT AVG(OrderPrice) AS OrderAverage FROM Orders
3. SELECT Customer FROM OrdersWHERE OrderPrice>(SELECT AVG(OrderPrice)
FROM Orders)
COUNT FUNCTION
The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:
1. SELECT COUNT(column_name) FROM table_name
2. SELECT COUNT(*) FROM table_name ( returns the number of records in a table)
3. SELECT COUNT(DISTINCT column_name) FROM table_name
4. SELECT COUNT(Customer) AS CustomerNilsen FROM Orders
WHERE Customer='Nilsen'
5. SELECT COUNT(*) AS NumberOfOrders FROM Orders
6. SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders
The FIRST() Function
The FIRST() function returns the first value of the selected column.
1. SELECT FIRST(column_name) FROM table_name
2. SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders
3. SELECT OrderPrice FROM Orders ORDER BY O_Id LIMIT 1
The LAST() Function
The LAST() function returns the last value of the selected column.
1. SELECT LAST(column_name) FROM table_name
2. SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders
3. SELECT OrderPrice FROM Orders ORDER BY O_Id DESC LIMIT 1
The MAX() Function
The MAX() function returns the largest value of the selected column.
1. SELECT MAX(column_name) FROM table_name
2. SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders
The MIN() Function
The MIN() function returns the smallest value of the selected column.
1. SELECT MIN(column_name) FROM table_name
2. SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders
The SUM() Function
The SUM() function returns the total sum of a numeric column.
1. SELECT SUM(column_name) FROM table_name
2. SELECT SUM(OrderPrice) AS OrderTotal FROM Orders
The GROUP BY Statement
The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns
1. SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
2. SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders
GROUP BY Customer,OrderDate
The HAVING Clause
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
1. SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000
2. SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer='Hansen' OR Customer='Jensen'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500
The UCASE() Function
The UCASE() function converts the value of a field to uppercase.
1. SELECT UCASE(column_name) FROM table_name
2. SELECT UPPER(column_name) FROM table_name
3. SELECT UCASE(LastName) as LastName,FirstName FROM Persons
The LCASE() Function
The LCASE() function converts the value of a field to lowercase.
1. SELECT LCASE(column_name) FROM table_name
2. SELECT LOWER(column_name) FROM table_name
3. SELECT LCASE(LastName) as LastName,FirstName FROM Persons
The MID() Function
The MID() function is used to extract characters from a text field.
1. SELECT MID(column_name,start[,length]) FROM table_name
2. SELECT MID(City,1,4) as SmallCity FROM Persons
The LEN() Function
The LEN() function returns the length of the value in a text field.
1. SELECT LEN(column_name) FROM table_name
2. SELECT LEN(Address) as LengthOfAddress FROM Persons
The ROUND() Function
The ROUND() function is used to round a numeric field to the number of decimals specified.
1. SELECT ROUND(column_name,decimals) FROM table_name
2. SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products
The FORMAT() Function
The FORMAT() function is used to format how a field is to be displayed.
1. SELECT FORMAT(column_name,format) FROM table_name
2. SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate
FROM Products
The TOP Clause
The TOP clause is used to specify the number of records to return.
The TOP clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance.
Note: Not all database systems support the TOP clause.
1. SELECT TOP number|percent column_name(s)
FROM table_name
2. SELECT TOP 2 * FROM Persons
3. SELECT TOP 50 PERCENT * FROM Persons
The LIKE Operator
The LIKE operator is used to search for a specified pattern in a column
1. SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern
2. SELECT * FROM Persons
WHERE City LIKE 's%'
3. SELECT * FROM Persons
WHERE City LIKE '%s'
4. SELECT * FROM Persons
WHERE City LIKE '%tav%'
5. SELECT * FROM Persons
WHERE City NOT LIKE '%tav%'
SQL Wildcards
SQL wildcards can substitute for one or more characters when searching for data in a database.
SQL wildcards must be used with the SQL LIKE operator.
1. SELECT * FROM Persons
WHERE City LIKE 'sa%'
2. SELECT * FROM Persons
WHERE City LIKE '%nes%'
3. SELECT * FROM Persons
WHERE FirstName LIKE '_la'
4. SELECT * FROM Persons
WHERE LastName LIKE 'S_end_on'
5. Now we want to select the persons with a last name that starts with "b" or "s" or "p" from the "Persons" table. EG SELECT * FROM Persons
WHERE LastName LIKE '[bsp]%'
6. SELECT * FROM Persons
WHERE LastName LIKE '[!bsp]%'
The IN Operator
The IN operator allows you to specify multiple values in a WHERE clause.
1. SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)
2. SELECT * FROM Persons
WHERE LastName IN ('Hansen','Pettersen')
The BETWEEN Operator
The BETWEEN operator selects a range of data between two values. The values can be numbers, text, or dates.
1. SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2
2. SELECT * FROM Persons
WHERE LastName
BETWEEN 'Hansen' AND 'Pettersen'
3. SELECT * FROM Persons
WHERE LastName
NOT BETWEEN 'Hansen' AND 'Pettersen'
SQL Alias(as)
You can give a table or a column another name by using an alias. This can be a good thing to do if you have very long or complex table names or column names.
An alias name could be anything, but usually it is short
1. SELECT column_name(s)
FROM table_name
AS alias_name
2. SELECT column_name AS alias_name
FROM table_name
SQL Joins
SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables.
SQL INNER JOIN Keyword
The INNER JOIN keyword return rows when there is at least one match in both tables.
1. SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
2. SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName
SQL LEFT JOIN Keyword
The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2)
1. SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
2. SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName
SQL RIGHT JOIN Keyword
The RIGHT JOIN keyword returns all the rows from the right table (table_name2), even if there are no matches in the left table (table_name1).
1. SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
2. SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName
SQL FULL JOIN Keyword
The FULL JOIN keyword return rows when there is a match in one of the tables.
The FULL JOIN keyword returns all the rows from the left table (Persons), and all the rows from the right table (Orders). If there are rows in "Persons" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Persons", those rows will be listed as well.
1. SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
2. SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName
The SQL UNION Operator
The UNION operator is used to combine the result-set of two or more SELECT statements.
Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.
1. SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name
2. SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
3. SELECT E_Name FROM Employees_Norway
UNION
SELECT E_Name FROM Employees_USA Note: This command cannot be used to list all employees in Norway and USA. In the example above we have two employees with equal names, and only one of them will be listed. The UNION command selects only distinct values.
4. SELECT E_Name FROM Employees_Norway
UNION ALL
SELECT E_Name FROM Employees_USA
The SQL SELECT INTO Statement
The SELECT INTO statement selects data from one table and inserts it into a different table.
The SELECT INTO statement is most often used to create backup copies of tables.
1. SELECT *
INTO new_table_name [IN externaldatabase]
FROM old_tablename
2. SELECT column_name(s)
INTO new_table_name [IN externaldatabase]
FROM old_tablename
3. SELECT *
INTO Persons_Backup
FROM Persons
4. SELECT *
INTO Persons_Backup IN 'Backup.mdb'
FROM Persons
5. SELECT LastName,FirstName
INTO Persons_Backup
FROM Persons
6. 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
The CREATE DATABASE statement is used to create a database.
1. CREATE DATABASE database_name
2. CREATE DATABASE my_db
SQL NOT NULL Constraint
The NOT NULL constraint enforces a column to NOT accept NULL values.
The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.
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
The UNIQUE constraint uniquely identifies each record in a database table.
The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.
A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.
Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
1. CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
2. ALTER TABLE Persons
ADD UNIQUE (P_Id)
3. ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID
SQL PRIMARY KEY Constraint
The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain unique values.
A primary key column cannot contain NULL values.
Each table should have a primary key, and each table can have only ONE primary key.
1. CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
2. 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)
)
3. ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)
4. ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
5. ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID
The ALTER TABLE Statement
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
1. ALTER TABLE table_name
ADD column_name datatype
2. ALTER TABLE table_name
DROP COLUMN column_name
3. ALTER TABLE Persons
ADD DateOfBirth date
SQL Dates
The most difficult part when working with dates is to be sure that the format of the date you are trying to insert, matches the format of the date column in the database.
As long as your data contains only the date portion, your queries will work as expected. However, if a time portion is involved, it gets complicated.
SQL Server comes with the following data types for storing a date or a date/time value in the database:
- 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
Note: The date types are chosen for a column when you create a new table in your database!
1. SELECT * FROM Orders WHERE OrderDate='2008-11-11'
2. If you want to keep your queries simple and easy to maintain, do not allow time components in your dates!
SQL Server Date Functions
The following table lists the most important built-in date functions in SQL Server:
GETDATE() Returns the current date and time
1. GETDATE()
2. SELECT GETDATE() AS CurrentDateTime
DATEPART() Returns a single part of a date/time
Where date is a valid date expression and datepart can be one of the following:
datepart | Abbreviation |
year | yy, yyyy |
quarter | qq, q |
month | mm, m |
dayofyear | dy, y |
Day | dd, d |
Week | wk, ww |
Weekday | dw, w |
Hour | Hh |
Minute | mi, n |
Second | ss, s |
Millisecond | Ms |
Microsecond | Mcs |
Nanosecond | Ns |
3. DATEPART(datepart,date)
4. SELECT DATEPART(yyyy,OrderDate) AS OrderYear,
DATEPART(mm,OrderDate) AS OrderMonth,
DATEPART(dd,OrderDate) AS OrderDay,
FROM Orders
WHERE OrderId=1
DATEADD() Adds or subtracts a specified time interval from a date
Where date is a valid date expression and number is the number of interval you want to add. The number can either be positive, for dates in the future, or negative, for dates in the past.
datepart can be one of the following:
datepart | Abbreviation |
year | yy, yyyy |
quarter | qq, q |
month | mm, m |
dayofyear | dy, y |
day | dd, d |
week | wk, ww |
weekday | dw, w |
hour | Hh |
minute | mi, n |
second | ss, s |
millisecond | Ms |
microsecond | Mcs |
nanosecond | Ns |
5. DATEADD(datepart,number,date)
6. SELECT OrderId,DATEADD(day,45,OrderDate) AS OrderPayDate
FROM Orders
DATEDIFF() Returns the time between two dates
Where startdate and enddate are valid date expressions and datepart can be one of the following:
datepart | Abbreviation |
year | yy, yyyy |
quarter | qq, q |
month | mm, m |
dayofyear | dy, y |
day | dd, d |
week | wk, ww |
weekday | dw, w |
hour | hh |
minute | mi, n |
second | ss, s |
millisecond | ms |
microsecond | mcs |
nanosecond | ns |
1. DATEDIFF(datepart,startdate,enddate)
2. SELECT DATEDIFF(day,'2008-06-05','2008-08-05') AS DiffDate
The FORMAT() function is used to format how a field is to be displayed.
1. SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate
FROM Products
SELECT STATEMENT
COMPARISON OPERATOR(=,<,>,<=,>=,!/<>)
LOGICAL OPERATORS(AND, OR)
EQUALITY AND INEQUALITY CONDITIONS(=,!=)
IN OPERATOR(used for multiply OR)
NOT IN operator(used for multiple AND)
RANGE CONDITIONS(BETWEEN OPERATOR AND NOT BETWEEN OPERATOR)
MATCHING CONDITION/PATTERNS( LIKE , NOT LIKE OPERATIOR)
WILD CARDS(%, _ )
CONCEPT OF NULL IN VALUE IN SQL
IS OPERATIOR-AVAILABLE TO COMPARE A VALUE WITH NULL (IS NULL, IS NOT NULL)
WHERE CLAUSE
ORDER BY CLAUSE- SORTING IN SQL( DESC)
STORED PROCEDURES
FUNCTIONS
SQL AVG() Syntax
COUNT FUNCTION
The FIRST() Function
The LAST() Function
The MAX() Function
The MIN() Function
The SUM() Function
The GROUP BY Statement
The HAVING Clause
The UCASE() Function
The LCASE() Function
The MID() Function
The LEN() Function
The ROUND() Function
The FORMAT() Function
The TOP Clause
The LIKE Operator
SQL Alias(as)
SQL Joins
SQL INNER JOIN
SQL LEFT JOIN
SQL RIGHT JOIN
SQL FULL JOIN
The SQL UNION Operator
The SQL SELECT INTO Statement
The CREATE DATABASE Statement
SQL NOT NULL Constraint
SQL UNIQUE Constraint
SQL PRIMARY KEY Constraint
The ALTER TABLE Statement
SQL Dates
GETDATE()
DATEPART()
DATEADD()
DATEDIFF()
UPDATE
DELETE
CREATE TABLE
DESCRIBE TABLE
INSERT INTO
No comments:
Post a Comment