#14 Basic SQL Statement

Basic SQL statement:
 CREATE TABLE table_name (column_def_list)
 DROP TABLE table_name
 SELECT column_list FROM table_list where {where_clause}
 INSERT source INTO target(columns) VALUES(value_list)
 UPDATE target SET col_val_pair_list {WHERE where_clause}
 DELETE FROM table {WHERE where_clause}

Create table:
 The CREATE TABLE statement is used to create a table in a database.
 SQL CREATE TABLE Syntax
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
)
 Explanation: Create a table with name ‘table_namE’ and have column1, column2

 Example:
CREATE TABLE Customer (First_Name char(50), Last_Name char(50), Address char(50), City char(50), Country char(25), DOB date);
 Note:
To specify a default value, add "Default [value]" after the data type declaration. In the above example, if we want to default column
"Address" to "Unknown" and City to “Hanoi", we would type in:
CREATE TABLE customer (First_Name char(50), Last_Name char(50),
Address char(50) default 'Unknown', City char(50) default ‘Hanoi', Country char(25), DOB date);

SELECT statement
 The SELECT statement is used to select data from a database.
 The result is stored in a result table, called the result-set.
 SQL SELECT Syntax
SELECT column_name(s)
FROM table_name and
SELECT * FROM table_name
 Note: SQL is not case sensitive. SELECT is the same as select.

Comparson Operators
Comparision Operators in conditions include:
> where Mark1 > 9;
< where Mark1 < 5;
>= where Mark1 >= 9;
<= where Mark1 <=5;
= where StudentName = ‘Minh’;
!= where StudentName != ‘Hoa’;
<> where StudentName <> ‘Tu’

Logic Operators:
Logic Operators in conditions include:
and
Where Mark1 >9 and StudentName = ‘Hao’;
Or
Where Mark1 > 9 or Mark2 > 9
Not: Phep toan phu dinh
Where Mark2 is not null;
Between
Where Mark1 between 5 and 10;
Like
Where StudentName like ‘%Hao’;
In
Where Mark1 in (8,9,10);

AND Operators:
 The AND operator displays a record if both the first condition and the second condition is true
 Example:
SELECT * FROM Marks
WHERE Mark1 >9 AND StudentName = ‘Hao’;

OR Operator:
 The OR operator displays a record if either the first condition or the second condition is true.
 Example:
SELECT * FROM Marks
Where Mark1 > 9 OR Mark2 > 9

Between Statement
 The BETWEEN operator selects a range of data between two values. The values can be numbers, text, or dates.
 SQL BETWEEN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2

LIKE Satement:
 The LIKE operator is used to search for a specified pattern in a column.
 SQL LIKE Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern
 LIKE ’pattern-to-match’Where the pattern can include special wildcard characters:
 % 0 or more arbitrary characters
 _ any one character
 Example:
SELECT * FROM Students
WHERE StudentName LIKE ’%hoa%’;

IN Operator:
 The IN operator allows you to specify multiple values in a WHERE clause.
 SQL IN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)

AS Clause:
 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.
 SQL Alias Syntax for Tables
SELECT column_name(s)
FROM table_name
AS alias_name

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.
 SQL Server Syntax
SELECT TOP number|percent column_name(s)
FROM table_name

ORDER BY Satement:
 The ORDER BY keyword is used to sort the resultset by a specified column.
 The ORDER BY keyword sort the records in ascending order by default.
 If you want to sort the records in a descending order, you can use the DESC keyword.
 SQL ORDER BY Syntax
SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC

DISTINCT
 In a table, some of the columns may contain duplicate values. This is not a problem, however, sometimes you will want to list only the different (distinct) values in a table.
 The DISTINCT keyword can be used to return only distinct (different) values.
 SQL SELECT DISTINCT Syntax
SELECT DISTINCT column_name(s)
FROM table_name

GROUP BY
 The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.
 SQL GROUP BY Syntax
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

SUBQUERY
 A SELECT query can be used within another SELECT condition and is then known as a sub query
 A sub query can return only one attribute having zero or more values
 The use of a view may provide a simpler query format than using techniques such as self-joins
 Operations with sub query: >, <, >=,<= , ALL, ANY, IN, NOT
IN, EXISTS, UNION, MINUS, INTERSEC..

 Example: list all Students that younger than ‘Hoa’
SELECT StudentName FROM Students
WHERE DateofBirth > ( SELECT DateofBirth FROM Students
WHERE StudentName = ‘Hoa’)

INSERT Statement:
 The INSERT INTO statement is used to insert a new row in a table.
 SQL INSERT INTO Syntax
INSERT INTO table_name
VALUES (value1, value2, value3,...)

 Note: There is an other way to insert data:
INSERT INTO table_name (column_name1, column_name2,…)
VALUES (value1, value2, value3,...)

DELETE Statement:
 The DELETE statement is used to delete rows in a table.
 SQL DELETE Syntax
DELETE FROM table_name
WHERE some_column=some_value
 Note:
Notice the WHERE clause in the DELETE syntax. The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!

UPDATE statement
 The UPDATE statement is used to update existing records in a table.
 SQL UPDATE Syntax
UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value
 Note:
Notice the WHERE clause in the UPDATE syntax. The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!


DROP statement
 The DROP INDEX statement is used to delete an index in a table.
 DROP INDEX Syntax for MS Access:
DROP TABLE table_name

Excercise:

1. List all students that have name is ‘Hoa’
2. List all students has highest mark in maths
3. Add student(s) into table Students
4. Update student marks: if mark<5 then update to 3
5. List student with name have prefix is [Ng]