1. What is SQL
(1) SQL stands for Structured Query Language (2) SQL is an ANSI standard (3) RDBMS (Relational Database Managerment System) is the basis for SQL like SQLServer, DB2, Oracle, mySQL, and Access eg.
2. SQL DML and DDL (1) The query and update commands from
DML (Data Manipulation Language) part of SQL: • SELECT-extracts data from a database • UPDATE-updates data in a database • DELETE-deletes data from a database • insert into-inserts new data into a database
(2) The most important DDL (Data Definition Language)
Statements in SQL: • create database-creates a new database • alter database-modifies a database • create table-creates a new table • alter table-modifies a table • drop table-deletes a table • CREATE INDEX -creates an index • drop index-deletes an index
3. SELECT Syntax and keywords (1) SELECT Syntax: SELECT column_name (s) FTOM table_name (2) DISTINCT: can be used to return only distinct values (3) WHERE: is used to filter records • Operators allowed in the WHERE clause: =, <>, >,<, >=, <=, AND, OR, BETWEEN, LIKE, IN (4) order by: is used to sort the result-set • Keyworks for order by: ASC, DESC
4. insert into Syntax • insert into table_name VALUES (value1, value2, value3 ,...) • insert into table_name (column1, column2, column3 ,...) VALUES (value1, value2, value3 ,...)
5. UPDATE Syntax • UPDATE table_name SET column1 = value1, column2 = value2,... WHERE some_column = some_value
6. DELETE Syntax • delete from table_name WHERE some_column = some_value
7. SQL Aggregate functions and Syntax (1) AVG ()-returns the average value • SELECT AVG (column_name) FROM table_name (2) COUNT () -returns the number of rows • select count (*) FROM table_name • select count (DISTINCT column_name) FROM table_name (3) FIRST ()-returns the first value • select first (column_name) FROM table_name (4) LAST ()-returns the last value • select last (column_name) FROM table_name (5) MAX ()-returns the largest value • select max (column_name) FROM table_name (6) MIN ()-returns the smallest value • select min (column_name) FROM table_name (7) SUM ()-return the sum • select sum (column_name) FROM table_name (8) group by-is used in conjunction with the aggregate functions to group the result-set by one or more columns • SELECT column_name, aggregate_function (column_name) FROM table_name WHERE column_name operator value group by column_name (9) HAVING-was added to SQL because the WHERE keyword cocould not be used with aggregate functions • SELECT column_name, agregate_function (column_name) FROM table_name WHERE column_name operator value group by column_name HAVING aggregate_function (column_name) operator value
8. SQL Scalar functions (1) UCASE ()-converts a field to upper case • SELECT UCASE (column_name) FROM table_name (2) LCASE () -converts a field to lower case • select lcase (column_name) FROM table_name (3) MID ()-extract characters from a text field • select mid (column_name, start [, length]) FROM table_name (4) LEN ()-returns the length of a text field • select len (column_name) FROM table_name (5) ROUND () -rounds a numeric field to the number of decimals specified • select round (column_name, decimals) FROM table_name (6) NOW ()-returns the current system date and time • select now () FROM table_name (7) FORMAT ()-formats how a field is to be diaplayed • select format (column_name, format) FROM table_name (8) TOP-is used to specify the number of records to return • select top number | percent column_name (s) FROM table_name (9) LIKE-is used to search for a specified pattern in a column • SELECT column_name (s) FROM table_name WHERE column_name LIKE pattern (10) IN-allows you to specify multiple values in a WHERE clause • SELECT column_name (s) FROM table_name WHERE column_name IN (value1, value2 ,...) (11) between and-selects a range of data between two values • SELECT column_name (s) FROM table_name WHERE column_name BETWEEN value1 AND value2
9. SQL Wildcard • %-a substitute for zero or more characters • _-a substitute for exactly one character • [charlist]-any single charater in charlist • [^ charlist] or [! Charlist]-any single character not in charlist
10. SQL Alias Syntax • SELECT column_name (s) FROM table_name
AS alias_name • SELECT column_name AS alias_name FROM table_name
11. SQL Joins • SQL joins are used to query data from two or more tables, base on a relationship between certain columns in these tablse • Different SQL JOINS: (1) JOIN or inner join-return rows when there is at least one match in both tables • SELECT column_name (s) FROM table_name1 inner join table_name2 ON table_name1.column_name = table_name2.column_name (2) left join-return all rows from the left table, even if there are no matches in the right table • SELECT column_name (s) FROM table_name1 left join table_name2 ON table_name1.column_name = table_name2.column_name (3) right join-return all rows from the right table, even if there are no matches in the left table • SELECT column_name (s) FROM table_name1 right join table_name2 ON table_name1.column_name = table_name2.column_name (4) full join-return rows when there is a match in one of the tables • SELECT column_name (s) FROM table_name1 full join table_name2 ON table_name1.column_name = table_name2.column_name
12. SQL UNION Operator (1) UNION operator is used to combine the result-set of two or more SELECT statements (2) Syntax • SELECT column_name (s) FROM table_name1 UNION SELECT column_name (s) FROM table_name2 • SELECT column_name (s) FROM table_name1 union all select column_name (s) FROM table_name2
13. SQL SELECT INTO Statement (1) SELECT INTO statement can be used to create backup copies of tables (2) Syntax: • SELECT * INTO new_table_name [IN externaldatabase] FROM old_tablename • SELECT column_name (s) INTO new_table_name [IN externaldatabase] FROM old_tablename
