-
Create Table Syntax
CREATE TABLE table_name ( field_name 1 data_type1 field_condiitons1,Field_name2 data_type2 field_conditions2,field_nameN data_typeN field_conditionsN,-----------------------------------------------------------(imaginary line)
- Table_condition1,
- Table_condition2,
-
To make a simple primary key
Field Condition (simple PK)CONSTRAINT con_naem PRIMARY KEY
Table condition (simple PK)CONSTRAINT con_naem PRIMARY KEY(field)
-
To make a secondary index
CREATE INDEX index_name
ON table_name(field1 order1, field2 order2, .., fieldN OrderN)
-
To create a unique index
- CREATE UNIQUE INDEX index_name
- ON table_name
- (field 1 order 1, field 2 order 2...)
-
To alter a table
- ALTER TABLE Employee
- ADD (Gender Char(1) Null)
-
To add a foreign key
- ALTER TABLE Employee
- ADD CONSTRAINT EmployeeStateInfoFK Foreign Key(State)
REFERENCES Stateinfo(State)
-
To Drop a column
- ALTER TABLE Employee
- DROP COLUMN Fname
-
Basic SELECT Syntax
- SELECT
- FROM
- WHERE
- GROUP BY
- HAVING
- ORDER BY
-
Basic INSERT Syntax
- INSERT INTO Employee(Fname, Lname..)
- VALUES ('anna','Peters')
-
Basic UPDATE Syntax
- UPDATE Employee
- SET Jobtitle='President', salary=60000
- WHERE EID='az123'
-
To Create a View
- CREATE VIEW viewname AS
- SELECT
- FROM
- WHERE
- ORDER BY
-
IN Operator
- SELECT *
- FROM Person
- WHERE Upper(State) In ('LA','TX','AZ')
-
LIKE Operator
- SELECT *
- FROM Person
- WHERE Lower(LName) Like 'jo%'
-
Concatenation Operator
- SELECT Fname||' '||LName AS FullName,ID,Salary
- FROM Person
-
To Round to 2 places before the decimal
- SELECT FName||' '||LName as FULLName,
- ROUND(Salary/12,-2) AS Monthly
- FROM Person
-
3 Syntax Rules for Aggregation
- Everythign in the SELECT and ORDER BY clause must be an aggregation function or be mentioned in the GROUP BY clause
- No aggregation functions in the where clause
- Only aggregation functions should appear in the HAVING clause
|
|
