This article is intended as a brief starting guide to SQL. It does not go into a lot of technical details but try to focus on syntax and examples to get you started on SQL. It is a brief introduction to SQL (from Oracle's world). But it is also similar to SQL Server TSQL and MySQL. This is a crash course or a jump start which starts with the introduciton to basic data types some DDL statements (CREATE, DROP) and then goes into DML statements (SELECT). You should not use it if you want a detailed in-depth overview of SQL. It is only meant as an introductory guide to SQL.
Data Types:
CHAR
used to declare a fixed text data type
limit of 250 length
Ex. LastName CHAR(10);
VARCHAR2
used to declare variable text data type
more efficient use of memory
will drop trailing spaces
Ex. LastName VARCHAR2(10);
NUMBER
used to declare numeric data type (for computation)
Ex. Age Number (8);
Salary Number (8,2);
DATE
used to declare date data type
no need to declare length
Ex. Birthday Date;
Create table
Create table
Example of CREATE TABLE:
CREATE TABLE Student (Student_ID NUMBER,Last_Name VARCHAR2(10),First_Name VARCHAR2(10),Credits_Taken NUMBER);
Display table structure:
DESC Student;
Insert data into the table:
INSERT INTO Student VALUES (22345, ‘Smith’, ‘John’, 45);
INSERT INTO Student VALUES (95874, ‘Jones’, ‘Jane’, 95);
Display information from a table:
SELECT * FROM Student;
SELECT Last_Name, Credits_Taken FROM Student;
will show only the given fields of all records
SELECT * FROM Student
WHERE Credits_Taken > 100;
will show records satisfying the condition
SELECT * FROM Student
ORDER BY Last_Name;
will show records sorted by Last_Name
Erase/ remove table from the system:
DROP TABLE Student;
MORE ON QUERYING THE DATA
Display the data in order:
To display the data in order (sorting), we use SELECT command and ORDER BY clause. By
default, Ascending order.
Example:
SELECT * FROM Employee
ORDER BY Lastname;
order by one key (field)
SELECT * FROM Employee
ORDER BY Lastname, Firstname;
order by more than one key (field)
SELECT * FROM Employee
ORDER BY Lastname DESC;
order by Descending order
Display the data on condition:
To display the data on condition, we use SELECT command and WHERE clause.
1. We use the following relational (comparison) operators with WHERE clause:
= equal
>= greater than & equal to
> greater than
<= less than & equal to
< less than
<> or =! not equal to
Example:
SELECT * FROM Employee
WHERE Salary > 3000;
SELECT * FROM Employee
WHERE Lastname = ‘Smith’;
Case sensitive comparison
SELECT * FROM Employee
WHERE Firstname != ‘Jon’;
2. Using AND, OR logic with WHERE:
Example:
SELECT * FROM Employee
WHERE Lastname = ‘Smith’
AND Firstname = ‘Jon’;
SELECT * FROM Employee
WHERE Year_Experience >=5
OR Salary >= 3000;
SELECT * FROM Employee
WHERE Age >= 30
AND Year_Experience >=5
AND State = VA;
-
Other comparison operators
a. BETWEEN … AND
Example:
SELECT * FROM Employee
WHERE Salary BETWEEN 1000 AND 3000;
-- same as --
SELECT * FROM Employee
WHERE Salary >= 1000 AND Salary <= 3000;
b. IN operator
Example:
SELECT * FROM Employee
WHERE State IN (‘DC’, ‘MD’,’VA’);
-- same as --
SELECT * FROM Employee
WHERE State = ‘DC’ OR State = ‘MD’ OR State =’VA’;
c. LIKE operator
Example:
SELECT * FROM Employee
WHERE Lastname LIKE ‘S%’;
returns all records with Lastname starts with “S”
SELECT * FROM Employee
WHERE Lastname LIKE ‘%S’;
returns all records with Lastname ends with “S”
SELECT * FROM Employee
WHERE Lastname LIKE ‘%S%’;
returns all records with Lastname contains “S”
d. IS NULL
used to test for null data (empty)
newly inserted cells contains null values
Example:
SELECT * FROM Employee
WHERE Tel_No IS NULL;
return records with empty Tel_No
e. NOT
Example:
SELECT * FROM Employee
WHERE State NOT IN (‘DC’, ‘MD’, ‘VA’);
SELECT * FROM Employee
WHERE Tel_No NOT NULL;
RULES OF PRECEDENCE (HEIRARCHY)
Which operators will be processed first
1. Within Parenthesis ()
2. Relational Operators (<,>,=,<=,>=,<>)
3. Not
4. And
5. Or
6. *, /, +, -
No comments:
Post a Comment